Oracle ERP Material Transaction Interface 成品入庫範例


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

Related Posts Plugin for WordPress, Blogger...