[EBS]查詢使用者的職責權限

SELECT FU.USER_NAME, FR.RESPONSIBILITY_NAME, FR.LANGUAGE, FURG.START_DATE, FURG.END_DATE
  FROM FND_USER_RESP_GROUPS_DIRECT FURG,
       FND_USER FU,
       FND_RESPONSIBILITY_TL FR
WHERE FU.USER_NAME = :USER_NAME
   AND FURG.USER_ID = FU.USER_ID
   AND FURG.RESPONSIBILITY_ID = FR.RESPONSIBILITY_ID

[Form Personalization] Function not available to this responsibility.Change responsibilities or contact your System Administrator

當你想使用Personalization要去執行一支Report\ Requests, 一般會設定成

Launch SRS Form

不過若你會遇到:

Function not available to this responsibility.Change responsibilities or contact your System

Administrator

那應該是你使用的這個responsibility對應的MENU少了一支FORM:

FND_FNDRSRUN (Submit: Requests) function

把他加進你的MENU就可以使用Launch SRS Form

了

[INV] ITEM SUPPLY/DEMAND 客製運用

DECLARE

   -- 測試料號
   CURSOR C1 IS
   SELECT MSI.INVENTORY_ITEM_ID,MSI.ORGANIZATION_ID SHIP_FROM_ORG_ID
     FROM MTL_SYSTEM_ITEMS MSI
    WHERE MSI.ORGANIZATION_ID = 104
      AND MSI.SEGMENT1 = 'MK001';
  
   V_SEQ_NUM        NUMBER;
   V_CUTOFF_DATE_J  NUMBER;
   V_TOTAL_TEMP     VARCHAR2(30);
   V_AVAIL_QTY_TEMP VARCHAR2(30);

BEGIN
   -- 取得 CLIENT 端 PROFILE 值
   FND_GLOBAL.APPS_INITIALIZE(1168, 50270, 401);
  
   -- 設定存貨組織
   INV_GLOBALS.SET_ORG_ID(84);

   SELECT TO_CHAR (SYSDATE, 'J')
     INTO V_CUTOFF_DATE_J
     FROM DUAL;

   SELECT MTL_DEMAND_INTERFACE_S.NEXTVAL
     INTO V_SEQ_NUM
     FROM DUAL;

   V_TOTAL_TEMP := 0;
   V_AVAIL_QTY_TEMP := 0;

   FOR I IN C1 LOOP
       IF (INV_TM.LAUNCH('INXDSD',
          'GROUP_ID=' ||
          TO_CHAR(V_SEQ_NUM) ||
          ' ' ||
          'ORGANIZATION_ID=' ||
          TO_CHAR(I.SHIP_FROM_ORG_ID) ||
          ' ' ||
          'INVENTORY_ITEM_ID=' ||
          TO_CHAR(I.INVENTORY_ITEM_ID) ||
          ' ' ||
          'ONHAND_SOURCE=' ||
          '1'||
          ' ' ||
          'CUTOFF_DATE="' ||
          TO_CHAR(V_CUTOFF_DATE_J) ||
          '" ' ||
          'MRP_STATUS=' ||
          '1' ||
          ' ' ||
          'ONHAND_FIELD=V_TOTAL_TEMP ' ||
          'AVAIL_FIELD=V_AVAIL_QTY_TEMP',
          V_TOTAL_TEMP,
          V_AVAIL_QTY_TEMP) = FALSE) THEN
      
          DBMS_OUTPUT.PUT_LINE('FAILED');
       ELSE
          DBMS_OUTPUT.PUT_LINE('SUCCESS');
          DBMS_OUTPUT.PUT_LINE(V_TOTAL_TEMP);
          DBMS_OUTPUT.PUT_LINE(V_AVAIL_QTY_TEMP);
       END IF;
   END LOOP;
END;

取得結果：
SELECT * FROM  MTL_SUPPLY_DEMAND_TEMP
WHERE SEQ_NUM = 0;

[PL/SQL] 數值金額轉英文範例

很久以前google搜尋修改的數值金額轉英文範例，給友需要的朋友

create or replace function number2eng_sf(in_number in number ) return varchar2 is

type eArray is table of varchar2(255);

l_str eArray := eArray('',
' Thousand ', ' Million ',
' Billion ', ' Trillion ',
' Quadrillion ', ' Quintillion ',
' Sextillion ', ' Septillion ',
' Octillion ', ' Nonillion ',
' Decillion ', ' Undecillion ',
' Duodecillion ');

l_num varchar2(50) default trunc(in_number);
h_number number;
v_total varchar2(4000);

begin
   h_number := round(in_number*100)/100;
   for i in 1 .. l_str.count loop
       exit when l_num is null;

       if (substr(l_num, length(l_num)-2, 3) != 0 ) then
          v_total := to_char(to_date(substr(l_num, length(l_num)-2, 3),'J' ),'Jsp' )||l_str(i)||v_total;
       end if;
      
       l_num := substr( l_num, 1, length(l_num)-3 );
   end loop;
 
   if v_total is null then
      v_total := 'Zero' ;
   end if;

   if trunc( h_number ) > 1 then
      v_total := v_total || ' Dollars ';
   else
      v_total := v_total || ' Dollar ';
   end if;

   if to_char( h_number ) like '%.%' then
      l_num := substr(h_number,instr(h_number,'.')+1);
      if length(l_num) = 1 then
         l_num := l_num || '0';
      end if;
     
      if l_num > 0 then
         if l_num > 1 then
            v_total := v_total ||'And '||l_num||' Cents ';
         else
            v_total := v_total ||'And '||l_num||' Cent ';
         end if;
      end if;
   end if;

   return v_total;
end;