CREATE OR REPLACE TRIGGER CUX_WIP_DISCRETE_JOBS
BEFORE INSERT ON INV.MTL_RESERVATIONS
FOR EACH ROW
when (NEW.SUPPLY_SOURCE_TYPE_ID = 5 AND --Job or Schedule
NEW.DEMAND_SOURCE_TYPE_ID IN (2, 8))
DECLARE
L_ORDER VARCHAR2(40);--訂單號碼
L_LINE VARCHAR2(40);--明細行號
L_ORG_ID NUMBER; --組織ID
L_LIN_QUAN NUMBER; --明細行數量
L_SHIP_DATE VARCHAR2(40);--出貨日期
L_BOOK_DATE VARCHAR2(40);--接單日期
L_BF_ALLOW VARCHAR2(40);--單廢允收
BEGIN
L_ORG_ID:=:NEW.ORGANIZATION_ID;
------------------------------------------------------------------------------------
BEGIN
SELECT MSO.SEGMENT1 --get the so number
INTO L_ORDER
FROM MTL_SALES_ORDERS MSO
WHERE MSO.SALES_ORDER_ID = :NEW.DEMAND_SOURCE_HEADER_ID;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN;
END;
BEGIN
SELECT TO_CHAR(OOH.ORDERED_DATE,'MM/DD'),OOH.ATTRIBUTE7,TO_CHAR(OOL.LINE_NUMBER),OOL.ORDERED_QUANTITY,TO_CHAR(OOL.SCHEDULE_SHIP_DATE,'MM/DD')-- get so line_number
INTO L_BOOK_DATE,L_BF_ALLOW,L_LINE,L_LIN_QUAN,L_SHIP_DATE
FROM OE_ORDER_LINES OOL,ont.oe_order_headers_all OOH
WHERE OOL.LINE_ID = :NEW.DEMAND_SOURCE_LINE_ID
AND OOL.HEADER_ID=OOH.HEADER_ID;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN;
END;
---------------抓訂單號碼,接單日期,是否允收單廢,抓訂單明細行,明細行數量,明細行交期
BEGIN
UPDATE WIP_DISCRETE_JOBS WJ
SET WJ.ATTRIBUTE2 =nvl(WJ.ATTRIBUTE2,0)+nvl(L_LIN_QUAN,0),--明細行訂單數量加總,得到工單對應的銷售訂單總量
WJ.ATTRIBUTE3 =WJ.ATTRIBUTE3||chr(10)||L_LIN_QUAN, --明細行數量並連 ,得到每個明細行的訂單數量
WJ.ATTRIBUTE4 =WJ.ATTRIBUTE4||chr(10)||L_BOOK_DATE, --明細行接單日期并連
WJ.ATTRIBUTE5 =WJ.ATTRIBUTE5||chr(10)||L_SHIP_DATE, --明細行交期并連
WJ.ATTRIBUTE6 =WJ.ATTRIBUTE6||chr(10)||L_BF_ALLOW, --是否允收單廢并連
WJ.ATTRIBUTE7 = WJ.ATTRIBUTE7||'_'||L_ORDER || '.' || L_LINE--銷售訂單及明細行
WHERE WJ.ORGANIZATION_ID =L_ORG_ID
AND WJ.WIP_ENTITY_ID = :NEW.SUPPLY_SOURCE_HEADER_ID;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN;
END;
END;
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/16860121/viewspace-615541/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/16860121/viewspace-615541/