SELECT   miq.SubInventory_Code,
         msi1.Segment1 fg,
         cic.Item_Cost,
         Substr(msi1.Description,1,50) fg_desc,
         miq.fg_qty,
         DECODE(miq.Organization_Id,173,'IPH',
                                    'SNY') Organization,
         Round((SYSDATE - miq.LastReceived),0) aGing,
         miq2.Totalfg--,blog.sonum
FROM     mtl_System_Items_b msi1,
         cst_Item_Costs cic,
         (SELECT   moq.Inventory_Item_Id,
                   moq.SubInventory_Code,
                   moq.Organization_Id,
                   SUM(Transaction_Quantity) fg_qty,
                   MAX(Date_Received) LastReceived-- ,msi1.segment1 fg,substr(msi1.description,1,50)fg_desc
          FROM     mtl_OnHand_quAntiTies_Detail moq
          WHERE    moq.Organization_Id IN (173,
                                           92)
                   AND SubInventory_Code IN ('IPHFG',
                                             'IPHFGTBS',
                                             'IPHFGSORT',
                                             'IPHNPIFG',
                                             'IPHINFFG',
                                             'IPHPONFG')
          GROUP BY moq.Inventory_Item_Id,
                   moq.Organization_Id,
                   moq.SubInventory_Code--msi1.segment1,substr(msi1.description,1,50)
                   ) miq,
         (SELECT   moq.Inventory_Item_Id,
                   SUM(Transaction_Quantity) Totalfg
          FROM     mtl_OnHand_quAntiTies_Detail moq
          WHERE    moq.Organization_Id IN (173,
                                           92)
                   AND SubInventory_Code IN ('IPHFG',
                                             'IPHFGTBS',
                                             'IPHFGSORT',
                                             'IPHNPIFG',
                                             'IPHINFFG',
                                             'IPHPONFG')
          GROUP BY moq.Inventory_Item_Id) miq2,
         (SELECT msi.Inventory_Item_Id Inventory_Item_Id,
                 msi.Segment1 ItemNumber,
                 oeh.Order_Number Sonum,
                 oel.Ship_From_Org_Id Organization_Id
          FROM   wsh_Delivery_Details wsh,
                 oe_Order_Headers_All oeh,
                 oe_Order_Lines_All oel,
                 mtl_System_Items_b msi
          WHERE  wsh.Source_Header_Id = oeh.Header_Id
                 AND wsh.Source_Line_Id = oel.Line_Id
                 AND oeh.Header_Id = oel.Header_Id
                 AND wsh.Inventory_Item_Id = msi.Inventory_Item_Id
                 AND wsh.Organization_Id = msi.Organization_Id
                 AND wsh.Organization_Id IN (92,
                                             173,
                                             393)
                 AND wsh.Released_Status IN ('B',
                                             'S',
                                             'R',
                                             'Y',
                                             'N') --b backorder, c interfaced, d cancelled, n not ready to release, r ready to release,y staged/pick confirmed,
                 AND (msi.Item_Type = 'FG'
                       OR msi.Description LIKE 'UFA%'
                       OR msi.Description LIKE 'BIA%'
                       OR msi.Description LIKE 'ASY%SEMI FIN%')
                     --and oeh.ORDER_NUMBER='109804'
                 AND oel.Flow_Status_Code NOT IN ('CLOSED',
                                                  'CANCELLED')) bLog
WHERE    miq.Inventory_Item_Id = bLog.Inventory_Item_Id (+)
         AND msi1.Inventory_Item_Id = cic.Inventory_Item_Id
         AND msi1.Organization_Id = cic.Organization_Id
         AND cic.Cost_Type_Id = 1  --1 frozen / 2 pending
                                --and msi.ORGANIZATION_ID = 173
         AND msi1.Costing_Enabled_Flag = 'Y'
         AND miq.Organization_Id = bLog.Organization_Id (+)
         AND bLog.Sonum IS NULL
         AND miq.Inventory_Item_Id = msi1.Inventory_Item_Id
         AND miq.Organization_Id = msi1.Organization_Id
         AND (msi1.Item_Type = 'FG'
               OR msi1.Description LIKE 'UFA%'
               OR msi1.Description LIKE 'BIA%'
               OR msi1.Description LIKE 'ASY%SEMI FIN%')
             --and miq.inventory_item_id = '115436'
ORDER BY 1,
         7 DESC