WIP与SO关联的一种方式

   ORACLE中，除了ATO外，其他SO订单均与任务单无法关联，即使是ATO的单，在完工入库的时候仍然会切断他们之间的关联，但是实际业务过程中仍有需求算出已下SO订单的是否都有全部完工，故可以使用如下方式来客制化实现:

    

/* Formatted on 2014-9-22 17:41:45 (QP5 v5.115.810.9015) */
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;

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