WIP: 工单无法与销售订单关联

         ORACLE除ATO外，其它销售订单不直接与工单关联；即使ATO有关联，但在工单完工入库时候，订单与工单关系自动切断；但实际应用中又经常需要订单对订单执行状况进行跟踪。

        以下为订单进展时，把订单号与行号写入 WIP_DISCRETE_JOBS
弹性栏位中。

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
NEW.DEMAND_SOURCE_TYPE_ID IN (2, 8))
DECLARE
L_REQ NUMBER;
L_ORDER VARCHAR2(40);
L_LINE VARCHAR2(40);
H_PACKING VARCHAR2(150);
L_PACKING VARCHAR2(150);
BEGIN
BEGIN
SELECT MSO.SEGMENT1
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 SUBSTR(OOH.PACKING_INSTRUCTIONS, 1, 150)
INTO H_PACKING
FROM OE_ORDER_HEADERS_ALL OOH
WHERE OOH.HEADER_ID = :NEW.DEMAND_SOURCE_HEADER_ID;
EXCEPTION
WHEN OTHERS THEN
H_PACKING := '';
END;

BEGIN
SELECT SUBSTR(OOL.PACKING_INSTRUCTIONS, 1, 150)
INTO L_PACKING
FROM OE_ORDER_LINES OOL
WHERE OOL.LINE_ID = :NEW.DEMAND_SOURCE_LINE_ID;
EXCEPTION
WHEN OTHERS THEN
L_PACKING := '';
END;*/

BEGIN
SELECT TO_CHAR(OOL.LINE_NUMBER)
INTO L_LINE
FROM OE_ORDER_LINES OOL
WHERE OOL.LINE_ID = :NEW.DEMAND_SOURCE_LINE_ID;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN;
END;

BEGIN
SELECT WRV.REQUEST_ID
INTO L_REQ
FROM WIP_DISCRETE_JOBS WRV
WHERE WRV.ORGANIZATION_ID = 104
AND WRV.WIP_ENTITY_ID = :NEW.SUPPLY_SOURCE_HEADER_ID
AND WRV.SOURCE_CODE = 'WICDOL';
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN;
END;

UPDATE WIP_DISCRETE_JOBS WJ
SET WJ.ATTRIBUTE3 = L_ORDER || '.' || L_LINE,
WJ.SOURCE_CODE = ''
--WJ.ATTRIBUTE12 = L_PACKING,
-- WJ.ATTRIBUTE11 = H_PACKING
/*,WJ.SCHEDULE_GROUP_ID = (SELECT WSG.SCHEDULE_GROUP_ID
FROM WIP_SCHEDULE_GROUPS WSG,
FND_LOOKUP_VALUES flv
WHERE WSG.ORGANIZATION_ID = 104
AND WSG.SCHEDULE_GROUP_NAME = FLV.TAG
AND FLV.LOOKUP_TYPE = 'WIP_WORKFLOOR_XXX'
AND FLV.LANGUAGE = 'ZHS'
AND WJ.CLASS_CODE = FLV.DESCRIPTION)*/
WHERE WJ.REQUEST_ID = L_REQ;
END;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/41594/viewspace-528393/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/41594/viewspace-528393/