[INV] Item update，利用API更新

因為要開發在另一套BPM系統，所以這次借用API方式更新，網路上常用有兩種API:
ego_item_pub.process_items 和 inv_item_grp.update_item

目前測試兩種都可行，ego_item_pub也可以處理eng item，inv_item_grp我沒試過行不行。
錯誤的話Status會Return：E，基本上若是Return：U 就要debug了，一般只有S：成功或失敗，
偏偏我家的環境出現一個U，抓了半天才發現有人寫的db trigger object失效了＠＠"。

限制更新欄位跟interface一樣：
PRIMARY_UNIT_OF_MEASURE
...

TEST：
1. EGO_ITEM_PUB
DECLARE
   L_ITEM_TABLE      EGO_ITEM_PUB.ITEM_TBL_TYPE;
   X_ITEM_TABLE      EGO_ITEM_PUB.ITEM_TBL_TYPE;
   V_MSG_CNT         NUMBER(20);
   V_RETURN          VARCHAR2(1);
   V_MSG_LIST        ERROR_HANDLER.ERROR_TBL_TYPE;
BEGIN
   FND_GLOBAL.APPS_INITIALIZE(123, 50200, 401);
 
   L_ITEM_TABLE(1).TRANSACTION_TYPE := 'UPDATE';
   L_ITEM_TABLE(1).INVENTORY_ITEM_ID := 18000; -- INVENTORY_ITEM_ID;
   L_ITEM_TABLE(1).ORGANIZATION_ID := 80;     -- ORGANIZATION_ID;
   L_ITEM_TABLE(1).ATTRIBUTE1 := 'TEST1';
 
   EGO_ITEM_PUB.PROCESS_ITEMS (P_API_VERSION => 1.0,
   P_INIT_MSG_LIST => FND_API.G_TRUE,
   P_COMMIT => FND_API.G_TRUE,
   P_ITEM_TBL => L_ITEM_TABLE,
   X_ITEM_TBL => X_ITEM_TABLE,
   --P_ROLE_GRANT_TBL => EGO_ITEM_PUB.G_MISS_ROLE_GRANT_TBL,
   X_RETURN_STATUS => V_RETURN,
   X_MSG_COUNT => V_MSG_CNT
   );

   DBMS_OUTPUT.PUT_LINE ('RETURN STATUS: ' || V_RETURN);
   DBMS_OUTPUT.PUT_LINE ('ERROR MESSAGES: ');
 
   ERROR_HANDLER.GET_MESSAGE_LIST (X_MESSAGE_LIST => V_MSG_LIST);
 
   FOR I IN 1 .. V_MSG_LIST.COUNT
   LOOP
      DBMS_OUTPUT.PUT_LINE (V_MSG_LIST(I).MESSAGE_TEXT);
   END LOOP;

END;


2. INV_ITEM_GRP
DECLARE
  L_ITEM_REC      INV_ITEM_GRP.ITEM_REC_TYPE;
  L_ERROR_TBL     INV_ITEM_GRP.ERROR_TBL_TYPE;
  X_ITEM_REC      INV_ITEM_GRP.ITEM_REC_TYPE;
  L_RETURN_STATUS VARCHAR2(40);
BEGIN
  L_ITEM_REC.INVENTORY_ITEM_ID := 18000;
  L_ITEM_REC.ORGANIZATION_ID   := 80;
  L_ITEM_REC.PLANNER_CODE      := 'EAD';
  INV_ITEM_GRP.UPDATE_ITEM(P_COMMIT           => FND_API.G_TRUE,
                           P_LOCK_ROWS        => FND_API.G_TRUE,
                           P_VALIDATION_LEVEL => FND_API.G_VALID_LEVEL_FULL,
                           P_ITEM_REC         => L_ITEM_REC,
                           X_ITEM_REC         => X_ITEM_REC,
                           X_RETURN_STATUS    => L_RETURN_STATUS,
                           X_ERROR_TBL        => L_ERROR_TBL);
  IF L_ERROR_TBL.COUNT() > 0 THEN
    FOR I IN 1 .. L_ERROR_TBL.COUNT LOOP
      DBMS_OUTPUT.PUT_LINE(L_ERROR_TBL(I).MESSAGE_TEXT);
      DBMS_OUTPUT.PUT_LINE(L_ERROR_TBL(I).MESSAGE_NAME);
    END LOOP;
  END IF;
END;

很久沒有更新了...

當初，申請這個blog的目的只是為了紀錄EBS客製開發過程常用到的資訊，好讓一些和我一樣會遇到相同問題的朋友做為參考。
因為當時有關EBS客製開發的中文支援並不好找，問顧問有的收費還很誇張，加上自己英文很破，有些熱心的原文網站的範例也都不完整，需要自己測試。這幾年發現網路上這類的文章，已經足夠大家參考了，有的還非常專業，說文解字的，所以很久都沒有上來分享自己工作上一些常用的資訊了。
還有就是這幾年負責的都是各項年度專案的開發，也不在侷限於EBS客製這一快，大都是整合EBS、BPM、WEB、Reporting Service和手持裝置的整合開發，所以也很少有機會分享自己的小小心得。

[EBS] EBS r11無法在IE11瀏灠器下執行使用

EBS r11無法在IE11瀏灠器下執行使用。
解決方法：
開啟IE11瀏灠器的功能選單內 [工具] > [相容性檢視設定]，加入EBS網址，重開IE既可。

[FORM] FRM-40654: Record has been updated. Requery block to see change

不知道大家會不會常遇到FORM在修改資料時，就給你出現一句：
FRM-40654: Record has been updated. Requery block to see change

怎查都沒有lock時，一般都是欄位值前後有空白造成的，解決方法很簡單，
找出對應的table，把空白值update掉就好了。

[OM] create Deliveries時出錯 The action can not be performed because the selected records could not be locked.

在做create Deliveries時出錯
Error: The action can not be performed because the selected records could not be locked.

這應該是個bug，一般是WSH_DELIVERY_DETAILS裡的欄位多了空格尤其是ITEM_DESCRIPTION，將空格去掉，重新執行就可以解決了。

[OM] Customer Profile Amount 新增

剛好遇到有很多客戶需要批次建Customer Profile Amount ，下面是個範例，可以參考看看：

DECLARE
   P_CUSTOMER_PROFILE_REC    HZ_CUSTOMER_PROFILE_V2PUB.CUSTOMER_PROFILE_REC_TYPE;
   P_CUST_ACCT_RELATE_REC    HZ_CUST_ACCOUNT_V2PUB.CUST_ACCT_RELATE_REC_TYPE;
   P_CUST_PROFILE_AMT_REC    HZ_CUSTOMER_PROFILE_V2PUB.CUST_PROFILE_AMT_REC_TYPE;
  
   P_PROFILE_CLASS_ID NUMBER;
  
   V_CUST_ACCOUNT_ID           NUMBER;
   V_ACCOUNT_NUMBER            VARCHAR2(20);
   V_PARTY_ID                  NUMBER;
   V_PARTY_NUMBER              VARCHAR2(20);
   V_PROFILE_ID                NUMBER;
   V_RETURN_STATUS             VARCHAR2(1);
   V_MSG_COUNT                 NUMBER;
   V_MSG_DATA                  VARCHAR2(2000);
   V_CUST_ACCOUNT_PROFILE_ID   NUMBER;
   V_CUST_ACCT_PROFILE_AMT_ID  NUMBER;

BEGIN
   FND_GLOBAL.APPS_INITIALIZE(USER_ID         => 3631,
                                 RESP_ID      => 50279,
                                 RESP_APPL_ID => 660);
                                
   -- CREATE CUSTOMER PROFILE RECORD
   P_CUSTOMER_PROFILE_REC.PARTY_ID := 123433;
   P_CUSTOMER_PROFILE_REC.CUST_ACCOUNT_ID :=152127;
   P_CUSTOMER_PROFILE_REC.SITE_USE_ID := 7459;
   P_CUSTOMER_PROFILE_REC.CREATED_BY_MODULE := 'TCA_V2_API';
  
   HZ_CUSTOMER_PROFILE_V2PUB.CREATE_CUSTOMER_PROFILE (
   fnd_api.g_true, --'T',
   P_CUSTOMER_PROFILE_REC ,
   fnd_api.g_true, --'T',
   V_CUST_ACCOUNT_PROFILE_ID ,
   V_RETURN_STATUS ,
   V_MSG_COUNT,
   V_MSG_DATA );

   -- CREATE CUSTOMER PROFILE AMOUNT RECORD
   P_CUST_PROFILE_AMT_REC.CUST_ACCOUNT_PROFILE_ID := V_CUST_ACCOUNT_PROFILE_ID;
   P_CUST_PROFILE_AMT_REC.CUST_ACCOUNT_ID := 152127;
   P_CUST_PROFILE_AMT_REC.SITE_USE_ID := 7459;
   P_CUST_PROFILE_AMT_REC.CURRENCY_CODE := 'TWD';
   P_CUST_PROFILE_AMT_REC.TRX_CREDIT_LIMIT := '';
   P_CUST_PROFILE_AMT_REC.OVERALL_CREDIT_LIMIT := '';
   P_CUST_PROFILE_AMT_REC.CREATED_BY_MODULE := 'TCA_V2_API';
  
   HZ_CUSTOMER_PROFILE_V2PUB.CREATE_CUST_PROFILE_AMT (
   fnd_api.g_true, --'T',
   fnd_api.g_true, --'T',
   P_CUST_PROFILE_AMT_REC ,
   V_CUST_ACCT_PROFILE_AMT_ID ,
   V_RETURN_STATUS ,
   V_MSG_COUNT ,
   V_MSG_DATA
   );

   COMMIT;
END;

Form Personalization 裡判斷DB原始值

有時候我們需要在Form Personalization 裡，取得某欄位原始值來做判斷時，可以利用：

${ITEM.BLOCK_NAME.ITEM_NAME.DATABASE_VALUE}

範例：
NVL(${ITEM.W_JOBS.START_QUANTITY.DATABASE_VALUE},0) != 0

[EBS] FNDLOAD

ERP系統要新開一家公司，所有程式、權限環境同另一家公司，所以借助FNDLOAD。

關於FNDLOAD網路上已經也很多參考範例，不免俗還是貼一下：

利用prodmgr登入

Lookups
FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/aflvmlu.lct **.ldt FND_LOOKUP_TYPE APPLICATION_SHORT_NAME='**' LOOKUP_TYPE='**'
FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/aflvmlu.lct **.ldt

Value Set
FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct **.ldt VALUE_SET FLEX_VALUE_SET_NAME='**'
FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afffload.lct **.ldt

Concurrent Program
FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct **.ldt PROGRAM CONCURRENT_PROGRAM_NAME='**' APPLICATION_SHORT_NAME='**'
FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct **.ldt

Menu
FNDLOAD apps/ 0 Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct **.ldt MENU MENU_NAME='**'
FNDLOAD apps/ 0 Y UPLOAD $FND_TOP/patch/115/import/afsload.lct **.ldt

Responsibility
FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afscursp.lct **.ldt FND_RESPONSIBILITY RESP_KEY='**'
FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afscursp.lct **.ldt

Functions/Forms
FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct **.ldt FUNCTION FUNCTION_NAME='**' APPLICATION_SHORT_NAME='**'
FNDLOAD apps/dlseh 0 Y UPLOAD $FND_TOP/patch/115/import/afsload.lct **.ldt

Profile
FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afscprof.lct **.ldt PROFILE FND_PROFILE_OPTION_VALUES PROFILE_NAME='**' APPLICATION_SHORT_NAME='**'
FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afscprof.lct **.ldt

Request Group
FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcpreqg.lct **.ldt REQUEST_GROUP REQUEST_GROUP_NAME='**' APPLICATION_SHORT_NAME='**'
FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afcpreqg.lct **.ldt

Attachement
FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afattach.lct **.ldt FND_ATTACHMENT_FUNCTIONS FUNCTION_NAME='**'
FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afattach.lct **.ldt

Personalization
FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/affrmcus.lct **.ldt FND_FORM_CUSTOM_RULES FUNCTION_NAME='**' APPLICATION_SHORT_NAME='**'
FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/affrmcus.lct **.ldt

其實他對應的.lct 是可以改的程式碼，我是都直接改寫，還有匯出來的檔案再改成新公司的程式編碼再匯入，Concurrent Program只要匯出Concurrent Program既可，他會一併匯出對應的Value Set，建議依照模組匯出較好處理。例如：

FNDLOAD apps/dorp 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct XX_FTAP.ldt PROGRAM CONCURRENT_PROGRAM_NAME='XXP%'

[COST] Close Discrete Jobs客製

系統越來越肥了，user反應工單關閉作業（Close Discrete Jobs）已經跑不動了，老是跑到一半不是DB的Undo不然就是Rollback Segment滿了掛了。所以只好寫了這隻小程式，因為r11工單關閉沒有Interface可用，所以借助WIP_DJ_CLOSE_TEMP使用，我把它放入排程，每天執行，範例如下：

-- 模擬使用者環境
fnd_global.apps_initialize(user_id => 1234, resp_id => 1234, resp_appl_id => 1234);

-- 完工
INSERT INTO WIP_DJ_CLOSE_TEMP(
                             WIP_ENTITY_ID,
                             ORGANIZATION_ID,
                             WIP_ENTITY_NAME,
                             STATUS_TYPE,
                             PRIMARY_ITEM_ID,
                             ACTUAL_CLOSE_DATE,
                             GROUP_ID)
                      SELECT
                             WE.WIP_ENTITY_ID,
                             WE.ORGANIZATION_ID,
                             WE.WIP_ENTITY_NAME,
                             WDJ.STATUS_TYPE,
                             WDJ.PRIMARY_ITEM_ID,
                             SYSDATE,123
                        FROM WIP_ENTITIES WE,
                             WIP_DISCRETE_JOBS WDJ
                       WHERE WE.WIP_ENTITY_ID = WDJ.WIP_ENTITY_ID
                         AND WE.ORGANIZATION_ID = 123
                         AND STATUS_TYPE = 4 -- Complete
                         AND DATE_COMPLETED <= SYSDATE
                         AND NOT EXISTS (SELECT 1
                                           FROM WIP_MOVE_TXN_INTERFACE WMTI
                                          WHERE WMTI.WIP_ENTITY_ID = WE.WIP_ENTITY_ID)
                         AND NOT EXISTS (SELECT 1
                                           FROM WIP_COST_TXN_INTERFACE WCTI
                                          WHERE WCTI.WIP_ENTITY_ID = WE.WIP_ENTITY_ID);

                        
-- 執行Close Discrete Jobs
V_REQUEST_ID := FND_REQUEST.SUBMIT_REQUEST
               ('WIP','WICDCL','','',FALSE,
                123,'','','','','','','','','',
                '','','',123,2,'','','',1,'',
                CHR(0),...);

COMMIT ;

[EBS] ORA-20001: FND-ORG_ID PROFILE CANNOT READ

今天測試環境崩潰了，還好是測試環境：

原來已經有一堆受害者了，而且造成錯誤的原因竟然這麼簡單，只要把Profile「MO: Operating Unit」site level的值給清掉。

解決方法：

Oracle error -20001: ORA-20001: FND-ORG_ID PROFILE CANNOT READ: N, OPTION, MO: Operating Unit has been detected in FND_GLOBAL.INITIALIZE.

Solution

Following are steps to change the profile option from backend:

1. Get the Profile option ID using the following query:

SQL> Select PROFILE_OPTION_ID,PROFILE_OPTION_NAME from fnd_profile_options

where PROFILE_OPTION_NAME ='ORG_ID';

2. Run the following procedure which changes the profile option:

DECLARE

stat boolean;

BEGIN

dbms_output.disable;

dbms_output.enable(100000);

stat := FND_PROFILE.SAVE('ORG_ID',<enter the Profile ID from above query>, 'SITE');

IF stat THEN

dbms_output.put_line( 'Stat = TRUE - profile updated' );

ELSE

dbms_output.put_line( 'Stat = FALSE - profile NOT updated' );

END IF;

commit;

END;

3) Restart the apache server

GL - AP Table關連

AP Invoice:

AP Payment:

[GL]GL Interface 簡單範例

Interface table:
GL_INTERFACE

Base table:
GL_JE_HEADERS
GL_JE_LINES
GL_JE_BACTHES

Concurrent Program:
Journal Import

簡單範例：
1.
SELECT GL_JOURNAL_IMPORT_S.NEXTVAL
  INTO V_GROUP_ID                 
  FROM DUAL;                      

SELECT CODE_COMBINATION_ID
  INTO V_CODE_COMBINATION_ID
  FROM GL_CODE_COMBINATIONS_V
WHERE CHART_OF_ACCOUNTS_ID = XXXX;
 
INSERT INTO GL_INTERFACE (
         STATUS,
         SET_OF_BOOKS_ID,
         ACCOUNTING_DATE,
         CURRENCY_CODE,
         DATE_CREATED,
         CREATED_BY,
         ACTUAL_FLAG,
         USER_JE_CATEGORY_NAME,
         USER_JE_SOURCE_NAME,
         BUDGET_VERSION_ID,
         CODE_COMBINATION_ID,
         PERIOD_NAME,
         ENTERED_DR,
         GROUP_ID)
VALUES ('NEW',                     
         :PARAMETER.SET_OF_BOOKS_ID,
         SYSDATE,          
         'TWD',                     
         SYSDATE,
         Fnd_Global.USER_ID,
         'B',                       
         'Accrual',
         'Spreadsheet',
         'test',
         V_CODE_COMBINATION_ID,
         V_PERIOD_NAME,
         100,
         V_GROUP_ID);

2.執行Journal Import

[INV]INV模組Base Table

Invenvtory (INV):
------------------------
1.MTL_SYSTEM_ITEMS_B           -- Master item info
2.MTL_ONHAND_QUANITTIES        -- Item onhand qty info
3.MTL_RESERVATIONS             -- Item Reservation info
4.MTL_MATERIAL_TRANSACTIONS    -- Item Transaction info
5.MTL_ITEM_LOCATIONS           -- Item location info
6,MTL_CATEGERIES               -- Item Category info
7.MTL_ITEM_CATEGORIES          -- Invemtry Categry
8.MTL_SECONDARY_INVENTORIES    -- Subinventories info
9.ORG_ORGANIZATION_DEFINITIONS -- Organizaition info
10.MTL_TRANSACTION_ACCOUNTS    -- Item Transaction info
11.MTL_TXN_SOURCE_TYPES        -- Item Transaction sources
12.MTL_PARAMETERS              -- Inventory Parameters

[PO]PO、AP模組Base Table

Purchase Order : (PO)
1.PO_REQUISITION_HEADERS_ALL -- Requisition Header info
2.PO_REQUISITION_LINES_ALL   -- Requisition Lines info
3.PO_REQ_DISTRIBUTIONS_ALL   -- Requisition Distribution info
4.PO_HEADERS_ALL             -- PO Header Info
5.PO_LINES_ALL               -- PO Line Info
6.PO_LINE_LOCATIONS_ALL      -- PO Line Shipment info
7.PO_DISTRIBUTIONS_ALL       -- PO Distribution info
8.RCV_SHIPMENT_HEADERS       -- Receiving header info
9.RCV_SHIPMENT_LINES         -- Receiving Lines info
10.RCV_TRANSACTIONS          -- Receiving transationd info
11.PO_VENDORS                -- Supplier Header info
12.PO_VENDOR_SITES_ALL       -- Supplier Site info
13.PO_VENDOR_SITE_CONTACTS   -- Supplier Site Contact info
14.HR_LOCATIONS              -- Supplier Site Address

Accounts Payable: (AP)
---------------------
1.AP_INVOICES_ALL               -- Invoice Header info
2.AP_INVOICE_DISTRIBUTIONS_ALL  -- Invoice Line info
3.AP_CHECKS_ALL                 -- Check info
4.AP_INVOICE_PAYMENTS_ALL       -- Invoice Payment info
5.AP_PAYMENT_SCHEDULES_ALL      -- Payment Schedule info
6.AP_HOLDS_ALL                  -- Invoice Holds info
7.AP_LOOKUP_CODES               -- Payable lookup info
8.PO_VENDORS                    -- Supplier Header info
9.PO_VENDOR_SITES_ALL           -- Supplier Site info
10.PO_VENDOR_SITE_CONTACTS      -- Supplier Site Contact info
11.HR_LOCATIONS                 -- Supplier Site Address
12.AP_BANKS                     -- Bank Info
13.AP_BANK_BRANCHES             -- Bank Branch info
14.AP_AE_HEADERS_ALL            -- Accounitng header info
15.AP_AE_LINES_ALL              -- Accounting Lines info
16.AP_AE_ACCOUNTING_EVETS       -- Accounting events info
17.AP_TERMS                     -- Payment Terms

[OM]OM、AR模組Base Table

Order Management (OM)：

1.OE_ORDER_HEADERS_ALL    -- Order Header info
2.OE_ORDER_LINES_ALL      -- Order line info
3.OE_TRANSACTION_TYPES_TL -- Order type info
4.OE_ORDER_HOLDS          -- Order Hold info
4.OE_HOLDS_ALL              -- Order Hold info
5.OE_HOLD_SOURCES           -- Order Hold source info
6.OE_HOLD_RELEASES        -- Hold Release info
7.WSH_DELIVERY_DETAILS    -- Delivery Detial Info
8.WSH_NEW_DELIVERIES      -- Delivery Header info
9.WSH_DELIVERY_ASSIGNMENTS -- Delivery Assignments info
10.WSH_TRIP_STOPS          -- Delivery trips info
11.HZ_CUST_ACCOUNTS        -- Customer info
12.HZ_PARTIES              -- Party info
13.HZ_CUST_SITE_USES_ALL   -- Customer site use info
14.HZ_CUST_ACCT_SITES_ALL  -- Customer Site Acct info
15.HZ_PARTY_SITES          -- Party site info
16.HZ_LOCATIONS            -- Customer Site Adderess
17.WSH_LOOKUPS             -- Shipping lookup info

Accounts Receivables (AR)：

1.RA_CUSTOMER_TRX_ALL            -- Receivable transaction info
2.RA_CUSTOMER_TRX_LINES_ALL      -- Transaction lines info
3.RA_CUST_TRX_LINE_GL_DIST_ALL   -- Transaction distribution info
4.AR_RECEIVABLE_APPLICATIONS_ALL -- Receiving application info
5.AR_CASH_RECEIPTS_ALL           -- Cash Receipt info
6.AR_TERMS                       -- Receivable Terms
7.HZ_CUST_ACCOUNTS               -- Customer info
8.HZ_PARTIES                     -- Party info
9.HZ_CUST_SITE_USES_ALL          -- Customer site use info
10.HZ_CUST_ACCT_SITES_ALL        -- Customer Site Acct info
11.HZ_PARTY_SITES                -- Party site info
12.HZ_LOCATIONS                  -- Customer Site Adderess

[INV] 透過MTL_SYSTEM_ITEMS_INTERFACE將Item欄位清成NULL

Updating Item Attributes to NULL
The method to update these columns to NULL is to use the following values：
for Numeric fields: insert -999999
for Character fields: insert '!'
for Date fields: the above list does not include any updateable date fields

[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;