在 Oracle ERP 中,
如何透過 "Material Transaction Interface" 進行成品入庫的資料拋轉,
範例如下 ;
程式碼
DECLARE
-- 以下必須輸入
V_TRANSACTION_INTERFACE_ID NUMBER;
V_SOURCE_CODE VARCHAR2(30) := 'TOMKUO'; -- 自訂一個Source_Code, 目的跟其他作業作區隔
V_SOURCE_HEADER_ID NUMBER := -1; -- 紀錄來源單據 ID
V_SOURCE_LINE_ID NUMBER := -1; -- 紀錄來源單據明細 ID
V_PROCESS_FLAG NUMBER := 1; -- 很重要, 一定要直接帶入 1 (待處理中)
V_VALIDATION_REQUIRED NUMBER := 1; -- 很重要, 一定要直接帶入 1
V_TRANSACTION_MODE NUMBER := 3; -- 很重要, 一定要直接帶入 3
V_LOCK_FLAG NUMBER := 2; -- 很重要, 一定要直接帶入 2
V_LAST_UPDATE_DATE DATE := SYSDATE;
V_LAST_UPDATED_BY NUMBER := 1285;
V_CREATION_DATE DATE := SYSDATE;
V_CREATED_BY NUMBER := 1285;
V_ORGANIZATION_ID NUMBER := 254; -- 廠別
V_INVENTORY_ITEM_ID NUMBER := 376315; -- 料號 ID
V_TRANSACTION_TYPE_ID NUMBER := 44; -- 交易型態 ID
V_TRANSACTION_QUANTITY NUMBER := 10; -- 交易數量 (庫存增加 > 0, 庫存減少 < 0)
V_TRANSACTION_UOM VARCHAR2(3) := 'PCS'; -- 交易單位
V_TRANSACTION_DATE DATE := SYSDATE; -- 交易日期
V_SUBINVENTORY_CODE VARCHAR2(10) := '800'; -- 交易倉庫
V_LOCATOR_ID NUMBER := 513; -- 交易儲位 (視設定, 是否需要儲位)
-- 以下可有可無
V_TRANSACTION_REFERENCE VARCHAR2(240) := '測試'; -- 備註
V_TRANSACTION_SOURCE_ID NUMBER := 287653; -- 紀錄 SOURCE_HEADER_ID 用來處理哪一張單據
V_WIP_ENTITY_TYPE NUMBER := 1; -- 工單型態 (工單必須輸入)
V_OPERATION_SEQ_NUM NUMBER := NULL; -- 製程工序 (工單領退料必須輸入, 其餘不用輸入)
V_FINAL_COMPLETION_FLAG VARCHAR2(1) := 'N'; -- 工單繳庫後, 是否工單狀態為 Completion (工單繳庫必須輸入)
/* ======================================================
用於 批次
====================================================== */
V_LOT_NUMBER NUMBER := NULL; -- 批號
/* ======================================================
用於 序號
====================================================== */
V_FM_SERIAL_NUMBER NUMBER := NULL; -- 開始序號
V_TO_SERIAL_NUMBER NUMBER := NULL; -- 結束序號
/* ======================================================
用於 工單成本
====================================================== */
CURSOR CUR_OPERATION( P_WIP_ENTITY_ID NUMBER ) IS
SELECT *
FROM WIP_OPERATIONS
WHERE WIP_ENTITY_ID = P_WIP_ENTITY_ID;
BEGIN
SELECT MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL
INTO V_TRANSACTION_INTERFACE_ID
FROM DUAL;
-- MMT Interface
INSERT INTO MTL_TRANSACTIONS_INTERFACE (
TRANSACTION_INTERFACE_ID
, SOURCE_CODE
, SOURCE_HEADER_ID
, SOURCE_LINE_ID
, PROCESS_FLAG
, VALIDATION_REQUIRED
, TRANSACTION_MODE
, LOCK_FLAG
, TRANSACTION_TYPE_ID
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, CREATION_DATE
, CREATED_BY
, ORGANIZATION_ID
, INVENTORY_ITEM_ID
, TRANSACTION_QUANTITY
, TRANSACTION_UOM
, TRANSACTION_DATE
, SUBINVENTORY_CODE
, LOCATOR_ID
, TRANSACTION_REFERENCE
, TRANSACTION_SOURCE_ID
, WIP_ENTITY_TYPE
, OPERATION_SEQ_NUM
, FINAL_COMPLETION_FLAG
)
VALUES (
V_TRANSACTION_INTERFACE_ID
, V_SOURCE_CODE
, V_SOURCE_HEADER_ID
, V_SOURCE_LINE_ID
, V_PROCESS_FLAG
, V_VALIDATION_REQUIRED
, V_TRANSACTION_MODE
, V_LOCK_FLAG
, V_TRANSACTION_TYPE_ID
, V_LAST_UPDATE_DATE
, V_LAST_UPDATED_BY
, V_CREATION_DATE
, V_CREATED_BY
, V_ORGANIZATION_ID
, V_INVENTORY_ITEM_ID
, V_TRANSACTION_QUANTITY
, V_TRANSACTION_UOM
, V_TRANSACTION_DATE
, V_SUBINVENTORY_CODE
, V_LOCATOR_ID
, V_TRANSACTION_REFERENCE
, V_TRANSACTION_SOURCE_ID
, V_WIP_ENTITY_TYPE
, V_OPERATION_SEQ_NUM
, V_FINAL_COMPLETION_FLAG
);
-- 成本 Interface (用於工單)
FOR REC_OPERATION IN CUR_OPERATION( V_TRANSACTION_SOURCE_ID ) LOOP
INSERT INTO CST_COMP_SNAP_INTERFACE (
TRANSACTION_INTERFACE_ID
, WIP_ENTITY_ID
, OPERATION_SEQ_NUM
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, CREATION_DATE
, CREATED_BY
, PRIMARY_QUANTITY
, QUANTITY_COMPLETED
) VALUES (
V_TRANSACTION_INTERFACE_ID
, REC_OPERATION.WIP_ENTITY_ID
, REC_OPERATION.OPERATION_SEQ_NUM
, V_LAST_UPDATE_DATE
, V_LAST_UPDATED_BY
, V_CREATION_DATE
, V_CREATED_BY
, REC_OPERATION.QUANTITY_COMPLETED
, REC_OPERATION.QUANTITY_COMPLETED
);
END LOOP;
COMMIT;
/*
===== Submit Request =====
*/
declare
v_request_id number;
v_phase varchar2(200);
v_status varchar2(200);
v_dev_phase varchar2(200);
v_dev_status varchar2(200);
v_message varchar2(200);
begin
-- FND_GLOBAL.APPS_INITIALIZE(FND_GLOBAL.USER_ID, FND_GLOBAL.RESP_ID, FND_GLOBAL.RESP_APPL_ID);
FND_GLOBAL.APPS_INITIALIZE(FND_GLOBAL.USER_ID, 51538, 20083);
v_request_id := fnd_request.submit_request( 'INV', 'INCTCM', '', '', false,
CHR(0), '', '', '', '', '', '', '', '', '',
'', '', '', '', '', '', '', '', '', '',
'', '', '', '', '', '', '', '', '', '',
'', '', '', '', '', '', '', '', '', '',
'', '', '', '', '', '', '', '', '', '',
'', '', '', '', '', '', '', '', '', '',
'', '', '', '', '', '', '', '', '', '',
'', '', '', '', '', '', '', '', '', '',
'', '', '', '', '', '', '', '', '', '',
'', '', '', '', '', '', '', '', '', '' );
COMMIT;
if v_request_id > 0 then
dbms_output.put_line('Submit Request ' || to_char(v_request_id) || ' Success !!' );
else
dbms_output.put_line('Submit Request Failure !!');
end if;
/*
偵測 Request 執行情形 : 每 5 秒偵測一次, 程序最長執行時間設定為 1800 秒
*/
if fnd_concurrent.wait_for_request ( v_request_id, 5, 1800, v_phase, v_status, v_dev_phase, v_dev_status, v_message ) then
if v_dev_phase != 'COMPLETE' or v_dev_status != 'NORMAL' then
dbms_output.put_line( 'Request failed with message ' || v_message );
else
dbms_output.put_line( 'Request finished with status ' || v_status );
end if;
end if;
end;
END;
