最近要上線囉,再轉30000 多筆item 時,
問題一.發生一次全丟進去轉入會死給你看的情形
oracle 啊oracle ...嘆氣ing ,
解法: 因此必須要拆筆數分批轉入,所以改寫500筆submit 一個request ,
問題二.
同一個session 不能FND_GLOBAL.APPS_INITIALIZE 兩次,送出第一個request後,再送就錯誤...oracle 啊oracle ...嘆氣ing ,
解法:建立function 做自治性事務騙過oracle .紅字是重點,請自己找google了解他是做蛇用的.
CREATE OR REPLACE FUNCTION APPS.import_INCOIN(vmas_org in number,vprocess_id in number) RETURN NUMBER
as
p_request_id NUMBER;
l_phase VARCHAR2 (255);
l_status VARCHAR2 (255);
l_dev_phase VARCHAR2 (255);
l_dev_status VARCHAR2 (255);
l_message VARCHAR2 (255);
l_request_status BOOLEAN := FALSE;
l_return NUMBER;
pragma autonomous_transaction;
BEGIN
FND_GLOBAL.APPS_INITIALIZE (user_id => 1157,
resp_id => 50659,
resp_appl_id => 401);
p_request_id :=
fnd_request.submit_request ('INV',
'INCOIN',
'Import item',
NULL,
FALSE,
vmas_org,
'1',
'1',
'1',
'1',
vprocess_id,
'1');
sys.dbms_output.put_line(to_char(p_request_id));
commit;
/*
l_request_status :=
Fnd_Concurrent.Wait_For_Request (p_request_id,
5,
0,
l_phase,
l_status,
l_dev_phase,
l_dev_status,
l_message);
IF l_request_status
THEN
IF l_dev_status = 'NORMAL'
THEN
-- DBMS_OUTPUT.put_line ('運行成功：' || l_dev_status || ',開始指定分類/海關料號/客料號....' );
--開始塞 customer item interface
--開始塞 item category interface
--呼叫cross reference api 新增海關分類,編碼
--.submit_request 呼叫以上幾個需要執行import 的program .
l_return := 1;
ELSE
-- DBMS_OUTPUT.put_line ('運行請求不成功：' || l_dev_status);
l_return := 0;
-- RETURN;
END IF;
ELSE
-- DBMS_OUTPUT.put_line ('請求未完成,無法察看!');
l_return := 2;
-- RETURN;
END IF;
*/
return l_return;
END;
/
問題三.
搞定一次送出多個conc request 後,發現job一直running 跑不完,trace 後發現這程式,不能平行執行....程式也沒限制也沒說明
oracle 啊oracle ...嘆氣ing
解法:將該程式設定成跟自己互斥,請參考如下圖
最後, 終於可以了....oracle 啊oracle ...oracle 啊oracle ...oracle 啊oracle ...嘆氣ing
主執行程式如下
DECLARE
/** create master org item */
CURSOR cur
IS
SELECT *
FROM R12_IMPORT_ITEM_201106; /*這是我整理好要轉進去的item */
-- WHERE ROWNUM <= 5000;
rec_imp MTL_SYSTEM_ITEMS_INTERFACE%ROWTYPE;
rec_imp_init MTL_SYSTEM_ITEMS_INTERFACE%ROWTYPE;
vPROCESS_FLAG NUMBER := 1;
vTRANSACTION_TYPE VARCHAR2 (10) := NULL;
vSET_PROCESS_ID NUMBER := 1694;
vmas_org NUMBER := 103;
vCREATED_BY NUMBER := 1157;
i PLS_INTEGER := 0;
cnt number:=30000; /*這是我要產生分批轉入的批次pprocess no 累加*/
BEGIN
FOR rec IN cur
LOOP
rec_imp := rec_imp_init;
rec_imp.ORGANIZATION_ID := rec.ORGANIZATION_ID;
rec_imp.CREATED_BY := vCREATED_BY;
rec_imp.SET_PROCESS_ID := cnt ;
rec_imp.PROCESS_FLAG := vPROCESS_FLAG;
rec_imp.TRANSACTION_TYPE := NULL;
BEGIN
SELECT 'UPDATE'
INTO rec_imp.TRANSACTION_TYPE
FROM MTL_SYSTEM_ITEMS
WHERE ORGANIZATION_ID = vmas_org AND segment1 = rec.segment1;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
rec_imp.TRANSACTION_TYPE := 'CREATE';
END;
BEGIN
rec_imp.ORGANIZATION_ID := vmas_org; --rec.ORGANIZATION_ID;
rec_imp.SEGMENT1 := rec.SEGMENT1;
IF LENGTHB (rec.DESCRIPTION) > 200
THEN
rec_imp.DESCRIPTION := ' 料號說明太長>240,需調整!';
sys.DBMS_OUTPUT.put_line (rec.SEGMENT1);
ELSE
rec_imp.DESCRIPTION := rec.DESCRIPTION;
END IF;
rec_imp.ITEM_TYPE := rec.R12_ITEM_TYPE;
rec_imp.TEMPLATE_ID := rec.TEMPLATE_ID;
rec_imp.INVENTORY_ITEM_STATUS_CODE := rec.INVENTORY_ITEM_STATUS_CODE;
rec_imp.RETURNABLE_FLAG := rec.RETURNABLE_FLAG;
rec_imp.RETURN_INSPECTION_REQUIREMENT :=
rec.RETURN_INSPECTION_REQUIREMENT;
/* 要assign category轉入用的,本例用不到
rec_imp.INV_CATEGORY_ID := rec.INV_CATEGORY_ID;
rec_imp.INV_CATEGORY := rec.INV_CATEGORY;
rec_imp.SALES_CATEGORY_ID := rec.SALES_CATEGORY_ID;
rec_imp.SALES_CATEGORY := rec.SALES_CATEGORY;
rec_imp.COST_CATEGORY_ID := rec.COST_CATEGORY_ID;
rec_imp.COST_CATEGORY := rec.COST_CATEGORY;
rec_imp.PO_CATEGORY_ID := rec.PO_CATEGORY_ID;
rec_imp.PO_CATEGORY := rec.PO_CATEGORY;
rec_imp.MFG_CATEGORY_ID := rec.MFG_CATEGORY_ID;
rec_imp.MFG_CATEGORY := rec.MFG_CATEGORY;
*/
rec_imp.PLANNING_MAKE_BUY_CODE := rec.PLANNING_MAKE_BUY_CODE;
rec_imp.PREPROCESSING_LEAD_TIME := rec.PREPROCESSING_LEAD_TIME;
rec_imp.FULL_LEAD_TIME := rec.FULL_LEAD_TIME;
/*make 件不能有 post leadtime,要轉掉*/
IF rec_imp.PLANNING_MAKE_BUY_CODE = 1
AND NVL (rec.POSTPROCESSING_LEAD_TIME, 0) <> 0
THEN
rec_imp.FIXED_LEAD_TIME :=
NVL (rec.FIXED_LEAD_TIME, 0)
+ NVL (rec.POSTPROCESSING_LEAD_TIME, 0);
rec_imp.POSTPROCESSING_LEAD_TIME := 0;
ELSE
rec_imp.FIXED_LEAD_TIME := rec.FIXED_LEAD_TIME;
rec_imp.POSTPROCESSING_LEAD_TIME := rec.POSTPROCESSING_LEAD_TIME;
END IF;
rec_imp.VARIABLE_LEAD_TIME := rec.VARIABLE_LEAD_TIME;
rec_imp.CUM_MANUFACTURING_LEAD_TIME :=
rec.CUM_MANUFACTURING_LEAD_TIME;
rec_imp.CUMULATIVE_TOTAL_LEAD_TIME := rec.CUMULATIVE_TOTAL_LEAD_TIME;
rec_imp.WIP_SUPPLY_TYPE := rec.WIP_SUPPLY_TYPE;
--rec_imp.WIP_SUPPLY_SUBINVENTORY := rec.WIP_SUPPLY_SUBINVENTORY;
--rec_imp.WIP_SUPPLY_LOCATOR_ID := rec.WIP_SUPPLY_LOCATOR_ID;
--rec_imp.DEFAULT_RECEIVING_SUBINV := rec.DEFAULT_RECEIVING_SUBINV;
rec_imp.PRIMARY_UNIT_OF_MEASURE := rec.PRIMARY_UNIT_OF_MEASURE;
--rec_imp.COST_OF_SALES_ACCOUNT := rec.COST_OF_SALES_ACCOUNT;
--rec_imp.EXPENSE_ACCOUNT := rec.EXPENSE_ACCOUNT;
--rec_imp.SALES_ACCOUNT := rec.SALES_ACCOUNT;
rec_imp.PLANNER_CODE := rec.PLANNER_CODE;
rec_imp.SAFETY_STOCK_BUCKET_DAYS := rec.SAFETY_STOCK_BUCKET_DAYS;
rec_imp.MRP_SAFETY_STOCK_PERCENT := rec.MRP_SAFETY_STOCK_PERCENT;
rec_imp.MRP_SAFETY_STOCK_CODE := rec.MRP_SAFETY_STOCK_CODE;
rec_imp.FIXED_DAYS_SUPPLY := rec.FIXED_DAYS_SUPPLY;
rec_imp.MINIMUM_ORDER_QUANTITY := rec.MINIMUM_ORDER_QUANTITY;
rec_imp.MAXIMUM_ORDER_QUANTITY := rec.MAXIMUM_ORDER_QUANTITY;
rec_imp.FIXED_LOT_MULTIPLIER := rec.FIXED_LOT_MULTIPLIER;
rec_imp.MIN_MINMAX_QUANTITY := rec.MIN_MINMAX_QUANTITY;
rec_imp.MAX_MINMAX_QUANTITY := rec.MAX_MINMAX_QUANTITY;
rec_imp.DAYS_EARLY_RECEIPT_ALLOWED := rec.DAYS_EARLY_RECEIPT_ALLOWED;
rec_imp.DAYS_LATE_RECEIPT_ALLOWED := rec.DAYS_LATE_RECEIPT_ALLOWED;
rec_imp.RECEIPT_DAYS_EXCEPTION_CODE :=
rec.RECEIPT_DAYS_EXCEPTION_CODE;
rec_imp.RESTRICT_SUBINVENTORIES_CODE :=
rec.RESTRICT_SUBINVENTORIES_CODE;
END;
/*本例只塞new item , update 不再此例介紹 */
IF rec_imp.TRANSACTION_TYPE = 'CREATE'
THEN
i := i + 1;
INSERT INTO MTL_SYSTEM_ITEMS_INTERFACE
VALUES rec_imp;
COMMIT;
IF i >= 500
THEN
declare
sts number;
/*每500 record 送出request一次*/
begin
sts:=APPS.import_INCOIN(vmas_org, cnt);
cnt := cnt+1;
end;
i := 0;
END IF;
END IF;
END LOOP;
COMMIT;
END;
留言列表
Oracle (6)
