F81[@more@]一,定義相關TABLE 1,來料時記錄 CREATE TABLE GOBO_F81_IN_TRANSACTIONS ( ORGANIZATION_ID NUMBER, TRANSACTION_ID NUMBER, INVENTORY_ITEM_ID NUMBER, REVISION VARCHAR2(3 BYTE), SUBINVENTORY_CODE VARCHAR2(10 BYTE), TRANSACTION_QUANTITY NUMBER, ZC_QTY NUMBER, FZC_QTY NUMBER, CLOSED_FLAG VARCHAR2(1 BYTE), TRANSACTION_DATE DATE, ACCT_PERIOD_ID NUMBER, TRANSACTION_REFERENCE VARCHAR2(80 BYTE), TRANSACTION_TYPE_ID NUMBER, TRANSACTION_ACTION_ID NUMBER, TRANSACTION_SOURCE_ID NUMBER, LAST_UPDATE_DATE DATE, LAST_UPDATED_BY NUMBER, CREATION_DATE DATE, CREATED_BY NUMBER, REF_TRANSACTION VARCHAR2(240 BYTE), REF_TRANSACTION2 VARCHAR2(240 BYTE), ZC_LAST_DATE DATE, FZC_LAST_DATE DATE ) 2,轉良品倉TM時用 CREATE TABLE GOBO_F81_OUT_TRANSACTIONS ( ORGANIZATION_ID NUMBER, TRANSACTION_ID NUMBER, INVENTORY_ITEM_ID NUMBER, REVISION VARCHAR2(3 BYTE), SUBINVENTORY_CODE VARCHAR2(10 BYTE), TRANSACTION_QUANTITY NUMBER, TRANSACTION_DATE DATE, ACCT_PERIOD_ID NUMBER, TRANSACTION_REFERENCE VARCHAR2(80 BYTE), TRANSACTION_TYPE_ID NUMBER, TRANSACTION_ACTION_ID NUMBER, TRANSACTION_SOURCE_ID NUMBER, LAST_UPDATE_DATE DATE, LAST_UPDATED_BY NUMBER, CREATION_DATE DATE, CREATED_BY NUMBER ) 3,轉不良品倉TN時用 CREATE TABLE GOBO_F81_OUT_TN_TRANSACTIONS ( ORGANIZATION_ID NUMBER, TRANSACTION_ID NUMBER, INVENTORY_ITEM_ID NUMBER, REVISION VARCHAR2(3 BYTE), SUBINVENTORY_CODE VARCHAR2(10 BYTE), TRANSACTION_QUANTITY NUMBER, TRANSACTION_DATE DATE, ACCT_PERIOD_ID NUMBER, TRANSACTION_REFERENCE VARCHAR2(80 BYTE), TRANSACTION_TYPE_ID NUMBER, TRANSACTION_ACTION_ID NUMBER, TRANSACTION_SOURCE_ID NUMBER, LAST_UPDATE_DATE DATE, LAST_UPDATED_BY NUMBER, CREATION_DATE DATE, CREATED_BY NUMBER ) 二,定義觸發器將相關記錄寫入 CREATE OR REPLACE TRIGGER APPS.GOBO_MTL_MATERIAL_F81TRA_T1 AFTER INSERT ON INV.MTL_MATERIAL_TRANSACTIONS REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW WHEN ( new.TRANSACTION_TYPE_ID IN (31,41,2) AND NEW.ORGANIZATION_ID IN (761) ) DECLARE t_type_id number; t_subinv varchar2(10); zc_q number; zc_qq number; new_orgid number; new_itemid number; new_trandate date; cursor cur(norgid in number,nitemid in number,ntrandate in date) is select * from gobo_f81_in_transactions where organization_id=norgid and inventory_item_id=nitemid and transaction_datezc_q then update gobo_f81_in_transactions set zc_qty=zc_q, zc_last_date=sysdate, last_update_date=sysdate, ref_transaction=ref_transaction||:new.transaction_id ||',' where transaction_id=rec.transaction_id and nvl(closed_flag,'N')='N'; zc_q:=0; elsif zc_qqzc_q then update gobo_f81_in_transactions set fzc_qty=zc_q, fzc_last_date=sysdate, last_update_date=sysdate, ref_transaction2=ref_transaction2||:new.transaction_id ||',' where transaction_id=rec.transaction_id and nvl(closed_flag,'N')='N'; zc_q:=0; elsif zc_qq=fdate and exception_date=fdate and exception_date