Oracle ASCP Demand Supply 的轉移到 iDempiere 實作

Oracle ASCP Demand Supply 的轉移到 iDempiere 實作
第一輪   主料  取用
第二輪   有組件限制替代料  取用
第三輪   無組件限制替代料  取用


是 每一輪是一個 Cursor  !!!
是 總共只有一輪只有一個 Cursor  !!!
 ==>依據可供料日期順序取用 !!!!

這才對 !!!!!!!!!!!!!!!!!!


CURSOR C_Supply_2 (x_Organization_ID NUMBER, x_Assembly_Item_ID NUMBER, x_Inventory_Item_ID   NUMBER)IS
                  
   SELECT a.LOS_MRP_SUPPLY_SEQ,a.COMPILE_DESIGNATOR,a.SOURCE_TYPE,a.ORGANIZATION_ID,a.INVENTORY_ITEM_ID,a.ITEM_NAME,a.ITEM_TYPE,
       -- ITEM_PACKAGE,ITEM_PACKAGE_DESC,ITEM_CLASS_DESC,
          SUPPLY_DATE,QTY,
          NVL((SELECT SUM(NVL(b.Allocated_Qty,0)) FROM LOS_MRP_ALLOCATED_1 b WHERE a.LOS_MRP_SUPPLY_SEQ=b.LOS_MRP_SUPPLY_SEQ),0) AS AllocatedQty1,
          NVL((SELECT SUM(NVL(b.Allocated_Qty,0)) FROM LOS_MRP_ALLOCATED_3 b WHERE a.LOS_MRP_SUPPLY_SEQ=b.LOS_MRP_SUPPLY_SEQ),0) AS AllocatedQty3
      FROM LOS_MRP_SUPPLY a
INNER JOIN MRP_Designators p  ON p.Compile_Designator = a.Compile_Designator AND p.Attribute1 = 'Y'
    WHERE       (a.Organization_ID =x_Organization_ID AND a.Inventory_Item_ID  =x_Inventory_Item_ID)
      OR EXISTS (SELECT 1  --Organization_ID,Assembly_Item_ID,Component_Item_ID,S_Component_Item_ID
                            --bh.Organization_ID,,bh.Assembly_Item_ID
                            --bc.Component_Item_ID
                            --bc1.Component_Item_ID   AS s_Component_Item_ID
                   FROM BOM_Components_B bc   --主料
             INNER JOIN BOM_Components_B bc1  ON bc.Bill_Sequence_ID = bc1.Bill_Sequence_ID  --替代料
                                             AND bc.Item_Num = bc1.Item_Num - MOD(bc1.Item_Num,10) AND MOD(bc1.Item_Num,10) > 0
             INNER JOIN BOM_Structures_B bh   ON bc.Bill_Sequence_ID = bh.Common_Bill_Sequence_ID
                                             AND bh.Alternate_BOM_Designator IS NULL 
                  WHERE 1=1
                    AND bc.DISABLE_DATE  IS NULL
                    AND bc1.DISABLE_DATE IS NULL
                    AND bh.Organization_ID    = x_Organization_ID
                    AND bh.Assembly_Item_ID   = x_Assembly_Item_ID     --組件
                    AND bc.Component_Item_ID  = x_Inventory_Item_ID
                    AND bc1.Component_Item_ID = a.Inventory_Item_ID)   --用料
      OR EXISTS (SELECT 1 
                   FROM Mtl_Related_Items r
                  WHERE r.Organization_ID   = x_Organization_ID
                    AND r.Related_Item_ID   = x_Inventory_Item_ID
                    AND r.Inventory_Item_ID = a.Inventory_Item_ID)
               ORDER BY a.Supply_Date;