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