2011年6月18日 星期六

[INV] ITEM SUPPLY/DEMAND 客製運用

DECLARE

   -- 測試料號
   CURSOR C1 IS
   SELECT MSI.INVENTORY_ITEM_ID,MSI.ORGANIZATION_ID SHIP_FROM_ORG_ID
     FROM MTL_SYSTEM_ITEMS MSI
    WHERE MSI.ORGANIZATION_ID = 104
      AND MSI.SEGMENT1 = 'MK001';
  
   V_SEQ_NUM        NUMBER;
   V_CUTOFF_DATE_J  NUMBER;
   V_TOTAL_TEMP     VARCHAR2(30);
   V_AVAIL_QTY_TEMP VARCHAR2(30);

BEGIN
   -- 取得 CLIENT 端 PROFILE 值
   FND_GLOBAL.APPS_INITIALIZE(1168, 50270, 401);
  
   -- 設定存貨組織
   INV_GLOBALS.SET_ORG_ID(84);

   SELECT TO_CHAR (SYSDATE, 'J')
     INTO V_CUTOFF_DATE_J
     FROM DUAL;

   SELECT MTL_DEMAND_INTERFACE_S.NEXTVAL
     INTO V_SEQ_NUM
     FROM DUAL;

   V_TOTAL_TEMP := 0;
   V_AVAIL_QTY_TEMP := 0;

   FOR I IN C1 LOOP
       IF (INV_TM.LAUNCH('INXDSD',
          'GROUP_ID=' ||
          TO_CHAR(V_SEQ_NUM) ||
          ' ' ||
          'ORGANIZATION_ID=' ||
          TO_CHAR(I.SHIP_FROM_ORG_ID) ||
          ' ' ||
          'INVENTORY_ITEM_ID=' ||
          TO_CHAR(I.INVENTORY_ITEM_ID) ||
          ' ' ||
          'ONHAND_SOURCE=' ||
          '1'||
          ' ' ||
          'CUTOFF_DATE="' ||
          TO_CHAR(V_CUTOFF_DATE_J) ||
          '" ' ||
          'MRP_STATUS=' ||
          '1' ||
          ' ' ||
          'ONHAND_FIELD=V_TOTAL_TEMP ' ||
          'AVAIL_FIELD=V_AVAIL_QTY_TEMP',
          V_TOTAL_TEMP,
          V_AVAIL_QTY_TEMP) = FALSE) THEN
      
          DBMS_OUTPUT.PUT_LINE('FAILED');
       ELSE
          DBMS_OUTPUT.PUT_LINE('SUCCESS');
          DBMS_OUTPUT.PUT_LINE(V_TOTAL_TEMP);
          DBMS_OUTPUT.PUT_LINE(V_AVAIL_QTY_TEMP);
       END IF;
   END LOOP;
END;

取得結果:
SELECT * FROM  MTL_SUPPLY_DEMAND_TEMP
WHERE SEQ_NUM = 0;