超越 Oracle EBS MRP 重寫中

         SELECT /*+PARALLEL */
                TRUNC(sn.promise_date+1,'WW') promise_date,
                FLOOR(
                (TRUNC(sn.promise_date+1,'WW')- TRUNC(SYSDATE+1,'WW'))
                /7) weeks,
                sn.inventory_item_id, sn.organization_id,
            --  a.segment1 AS inventory_item,A
            --  mp.organization_code,   -- mtl_parameters.organization_code=mtl_secondary_inventories.attribute10
            --  oh.order_number||'-'||ol.line_number||'.'|| shipment_number AS order_number,
           SUM(NVL (sn.ordered_quantity, 0) - NVL (sn.shipped_quantity, 0)) AS quantity
           FROM oe_odr_lines_sn sn
     INNER JOIN oe_order_lines_all ol ON ol.line_id = sn.line_id
                                     AND ol.flow_status_code = 'AWAITING_SHIPPING'
                                     AND TRUNC (ol.schedule_ship_date) < TRUNC (SYSDATE, 'WW') --  + 6
                                    
     INNER JOIN oe_order_headers_all       oh ON ol.header_id = oh.header_id
     INNER JOIN mtl_secondary_inventories msi ON msi.organization_id          = sn.organization_id
                                             AND msi.secondary_inventory_name = ol.subinventory
                                             AND msi.availability_type        = 1
     INNER JOIN mtl_parameters             mp ON mp.organization_code  = msi.attribute10  --EMS mtl_secondary_inventories
     INNER JOIN mtl_item_categories       mic ON mic.inventory_item_id = sn.inventory_item_id
                                             AND mic.organization_id   = sn.organization_id
     INNER JOIN mtl_categories_b           mc ON mc.category_id = mic.category_id
                                             AND mc.segment1 IN ('FG', 'SM')
------------------------------------------------
     INNER JOIN mtl_parameters          e  ON e.organization_id     = mp.organization_id  --sn.organization_id
                                        --AND e.attribute12         = 'Open'  -- this not a index key
                                        --AND e.attribute10         IN (2,5)  -- (same with above)
     INNER JOIN mtl_item_categories     b  ON b.organization_id     = mp.organization_id  --sn.organization_id
                                          AND b.inventory_item_id   = sn.inventory_item_id
                                          AND b.category_set_id     = 1
     INNER JOIN mtl_categories_b        c  ON c.category_id         = b.category_id
     INNER JOIN mtl_system_items_b      a  ON a.organization_id     =b.organization_id   --a.segment1 AS inventory_item
                                          AND a.inventory_item_id   =b.inventory_item_id
                                          AND a.wip_supply_type    <> 6
          WHERE NVL (sn.ordered_quantity, 0) - NVL (sn.shipped_quantity, 0) > 0
          AND   FLOOR((TRUNC(sn.promise_date+1,'WW')- TRUNC(SYSDATE+1,'WW')) /7) < 26
          GROUP BY
                TRUNC(sn.promise_date+1,'WW'),
                FLOOR(
                (TRUNC(sn.promise_date+1,'WW')- TRUNC(SYSDATE+1,'WW'))
                /7),
                sn.inventory_item_id, sn.organization_id;