backlog PO SO

TRUNCATE TABLE MEW_PEWC_SUPPLY_DEMAND_TMP;
  INSERT INTO MEW_PEWC_SUPPLY_DEMAND_TMP
    (ORGANIZATION_ID
    ,INVENTORY_ITEM_ID
    ,ONHAND
    ,RESERVED_ONHAND
    ,INTRANSIT_ONHAND
    ,BACKLOG_PO_QTY
    ,BACKLOG_SO_QTY
    ,DATA_GENERATION_DATE)
    SELECT ORGANIZATION_ID
          ,INVENTORY_ITEM_ID
          ,ONHAND
          ,RESERVED_ONHAND
          ,INTRANSIT_ONHAND
          ,BACKLOG_PO_QTY
          ,BACKLOG_SO_QTY
          ,SYSDATE
      FROM (SELECT MR.ORGANIZATION_ID ORGANIZATION_ID
                  ,MR.INVENTORY_ITEM_ID INVENTORY_ITEM_ID
                  ,0 ONHAND
                  ,SUM(MR.RESERVATION_QUANTITY) RESERVED_ONHAND
                  ,0 INTRANSIT_ONHAND
                  ,0 BACKLOG_PO_QTY
                  ,0 BACKLOG_SO_QTY
              FROM apps.MTL_RESERVATIONS MR
             WHERE MR.ORGANIZATION_ID BETWEEN 236 AND 242
             GROUP BY MR.ORGANIZATION_ID
                     ,MR.INVENTORY_ITEM_ID
            HAVING SUM(MR.RESERVATION_QUANTITY) <> 0
            UNION ALL
            SELECT MQD.ORGANIZATION_ID
                  ,MQD.INVENTORY_ITEM_ID
                  ,SUM(MQD.TRANSACTION_QUANTITY) ONHAND
                  ,0 RESERVED_ONHAND
                  ,0 INTRANSIT_ONHAND
                  ,0 BACKLOG_PO_QTY
                  ,0 BACKLOG_SO_QTY
              FROM apps.MTL_ONHAND_QUANTITIES_DETAIL MQD
             WHERE MQD.ORGANIZATION_ID BETWEEN 236 AND 242
             GROUP BY MQD.ORGANIZATION_ID
                     ,MQD.INVENTORY_ITEM_ID
            HAVING SUM(MQD.TRANSACTION_QUANTITY) <> 0
            UNION ALL
            SELECT MQD.ORGANIZATION_ID
                  ,MQD.INVENTORY_ITEM_ID
                  ,0 ONHAND
                  ,0 RESERVED_ONHAND
                  ,SUM(MQD.TRANSACTION_QUANTITY) INTRANSIT_ONHAND
                  ,0 BACKLOG_PO_QTY
                  ,0 BACKLOG_SO_QTY
              FROM apps.MTL_ONHAND_QUANTITIES_DETAIL MQD
                  ,apps.MTL_SECONDARY_INVENTORIES    MSI
             WHERE MQD.ORGANIZATION_ID BETWEEN 236 AND 242
               AND MQD.ORGANIZATION_ID = MSI.ORGANIZATION_ID
               AND MQD.SUBINVENTORY_CODE = MSI.SECONDARY_INVENTORY_NAME
               AND MSI.ATTRIBUTE1='2'
             GROUP BY MQD.ORGANIZATION_ID
                     ,MQD.INVENTORY_ITEM_ID
            HAVING SUM(MQD.TRANSACTION_QUANTITY) <> 0
            UNION ALL
            SELECT PLLA.SHIP_TO_ORGANIZATION_ID ORGANIZTAION_ID
                  ,PLA.ITEM_ID
                  ,0 ONHAND
                  ,0 RESERVED_ONHAND
                  ,0 INTRANSIT_ONHAND
                  ,SUM(PLLA.QUANTITY - PLLA.QUANTITY_RECEIVED) BACKLOG_PO_QTY
                  ,0 BACKLOG_SO_QTY
              FROM apps.PO_HEADERS_ALL        PHA
                  ,apps.PO_LINES_ALL          PLA
                  ,apps.PO_LINE_LOCATIONS_ALL PLLA
             WHERE PHA.PO_HEADER_ID = PLA.PO_HEADER_ID
               AND PLA.PO_LINE_ID = PLLA.PO_LINE_ID
               AND PHA.AUTHORIZATION_STATUS = 'APPROVED'
               AND PHA.TYPE_LOOKUP_CODE = 'STANDARD'
               AND NVL(PLLA.CANCEL_FLAG
                      ,'N') <> 'Y'
               AND NVL(PLLA.CLOSED_CODE
                      ,'N')  NOT IN ('CLOSED'
                       ,'FINALLY CLOSED'
                       ,'CLOSED FOR RECEIVING'
                       ,'CLOSED FOR INVOICE')
               AND PHA.ORG_ID BETWEEN 236 AND 242
             GROUP BY PLLA.SHIP_TO_ORGANIZATION_ID
                     ,PLA.ITEM_ID
            HAVING SUM(PLLA.QUANTITY - PLLA.QUANTITY_RECEIVED) <> 0
            UNION ALL
            SELECT OOL.SHIP_FROM_ORG_ID
                  ,OOL.INVENTORY_ITEM_ID
                  ,0 ONHAND
                  ,0 RESERVED_ONHAND
                  ,0 INTRANSIT_ONHAND
                  ,0 BACKLOG_PO_QTY
                  ,SUM(OOL.ORDERED_QUANTITY - NVL(OOL.SHIPPED_QUANTITY
                                                 ,0)) BACKLOG_SO_QTY
              FROM apps.OE_ORDER_HEADERS_ALL OOH
                  ,apps.OE_ORDER_LINES_ALL   OOL
             WHERE OOH.SHIP_FROM_ORG_ID BETWEEN 236 AND 242
               AND OOH.HEADER_ID = OOL.HEADER_ID
               AND OOH.FLOW_STATUS_CODE = 'BOOKED'
               AND OOL.ORDERED_QUANTITY - NVL(OOL.SHIPPED_QUANTITY
                                             ,0) > 0
             GROUP BY OOL.SHIP_FROM_ORG_ID
                     ,OOL.INVENTORY_ITEM_ID);
  TRUNCATE TABLE MEW_PEWC_SUPPLY_DEMAND;
  INSERT INTO MEW_PEWC_SUPPLY_DEMAND
    (ORGANIZATION_ID
    ,INVENTORY_ITEM_ID
    ,ONHAND
    ,RESERVED_ONHAND
    ,INTRANSIT_ONHAND
    ,BACKLOG_PO_QTY
    ,BACKLOG_SO_QTY
    ,DATA_GENERATION_DATE)
    SELECT ORGANIZATION_ID
          ,INVENTORY_ITEM_ID
          ,SUM(ONHAND)
          ,SUM(RESERVED_ONHAND)
          ,SUM(INTRANSIT_ONHAND)
          ,SUM(BACKLOG_PO_QTY)
          ,SUM(BACKLOG_SO_QTY)
          ,SYSDATE
      FROM MEW_PEWC_SUPPLY_DEMAND_TMP
     GROUP BY ORGANIZATION_ID
             ,INVENTORY_ITEM_ID;