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