[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