SELECT PAR.ORGANIZATION_CODE ORG,
       MSI.SEGMENT1 ITEM,
       MSI.DESCRIPTION,
       msi.INVENTORY_ITEM_ID ITEM_ID,
       DECODE(MSI.END_ASSEMBLY_PEGGING_FLAG,
              'A',
              'Soft PEGGING',
              'B',
              'END ASSEMBLY/Soft PEGGING',
              'I',
              'Hard Pegging',
              'N',
              'NONE',
              'X',
              'End Assembly/Hard Pegging',
              'Y',
              'END ASSEMBLY PEGGING') PEGGING_INFORMATION,
       LOC.SUBINVENTORY_CODE SUBINVENTORY, -- MSN.SERIAL_NUMBER,
       LOC.SEGMENT1 || '.' || LOC.SEGMENT2 || '.' || LOC.SEGMENT3 || '.' ||
       LOC.SEGMENT4 || '.' || PRJ.SEGMENT1 || '.' || TSK.TASK_NUMBER LOCATOR,
       (SELECT SUM(OH.TRANSACTION_QUANTITY)
          FROM INV.MTL_ONHAND_QUANTITIES_DETAIL OH
         WHERE OH.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID    
           AND OH.ORGANIZATION_ID = OHM.ORGANIZATION_ID   
           AND OH.LOCATOR_ID = OHM.LOCATOR_ID) TOTAL_QTY,
      
       (NVL(     
            (SELECT SUM(OH.TRANSACTION_QUANTITY)
               FROM INV.MTL_ONHAND_QUANTITIES_DETAIL OH
              WHERE OH.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID    
                AND OH.ORGANIZATION_ID = OHM.ORGANIZATION_ID    
                AND OH.LOCATOR_ID = OHM.LOCATOR_ID), 0)
          -
       NVL((NVL((SELECT SUM(RES.PRIMARY_RESERVATION_QUANTITY)      
                   FROM INV.MTL_RESERVATIONS RES     
                  WHERE RES.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID          
                    AND RES.ORGANIZATION_ID = OHM.ORGANIZATION_ID       
                    AND RES.SUBINVENTORY_CODE = OHM.SUBINVENTORY_CODE      
                    AND OHM.LOCATOR_ID = RES.LOCATOR_ID    
                 ),  0)  
            + 
            NVL((SELECT SUM(TRANSACTION_QUANTITY)  
                   FROM INV.MTL_MATERIAL_TRANSACTIONS_TEMP TXN    
                  WHERE TXN.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID       
                    AND TXN.ORGANIZATION_ID = OHM.ORGANIZATION_ID      
                    AND TXN.LOCATOR_ID = OHM.LOCATOR_ID       
                    AND TXN.TRANSACTION_TYPE_ID NOT IN (18, 61, 15, /*35,*/
                         87, 53, 52)),  0)),  0)
       -
       NVL((SELECT SUM(OH.TRANSACTION_QUANTITY)  
              FROM INV.MTL_ONHAND_QUANTITIES_DETAIL OH, 
                   INV.MTL_SECONDARY_INVENTORIES SUB
             WHERE OH.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID   
               AND OH.ORGANIZATION_ID = OHM.ORGANIZATION_ID  
               AND OH.SUBINVENTORY_CODE = SUB.SECONDARY_INVENTORY_NAME   
               AND OH.ORGANIZATION_ID = SUB.ORGANIZATION_ID  
               AND OH.LOCATOR_ID = OHM.LOCATOR_ID   
               AND SUB.AVAILABILITY_TYPE = 2),  0)) AVAILABLE_QTY
--MSI.SERIAL_NUMBER_CONTROL_CODE,
  FROM INV.MTL_SYSTEM_ITEMS_B MSI,  
       INV.MTL_ONHAND_QUANTITIES_DETAIL OHM,  
       INV.MTL_ITEM_LOCATIONS LOC,
       APPS.PA_PROJECTS_ALL PRJ,
       PA.PA_TASKS TSK,
       APPS.MTL_SECONDARY_INVENTORIES SUB,
       INV.MTL_PARAMETERS PAR,
       apps.mtl_material_transactions mmt
--APPS.MTL_SERIAL_NUMBERS MSN
 WHERE 1 = 1
   AND SUB.ORGANIZATION_ID = PAR.ORGANIZATION_ID   
   AND SUB.SECONDARY_INVENTORY_NAME = LOC.SUBINVENTORY_CODE   
   AND MSI.ORGANIZATION_ID = mmt.organization_id   
   AND PAR.ORGANIZATION_ID = OHM.ORGANIZATION_ID  
   AND MSI.INVENTORY_ITEM_ID = OHM.INVENTORY_ITEM_ID  
   AND OHM.LOCATOR_ID = LOC.INVENTORY_LOCATION_ID  
   AND LOC.PROJECT_ID = PRJ.PROJECT_ID(+)  
   AND LOC.TASK_ID = TSK.TASK_ID(+) 
   AND PAR.ORGANIZATION_ID = mmt.organization_id  
   AND mmt.inventory_item_id = msi.inventory_item_id  
   and mmt.organization_id in (1, 2, 3)  
   and mmt.transaction_type_id = 15  
   and transaction_date =
       (select max(transaction_date)
          from apps.mtl_material_transactions
         where inventory_item_id = mmt.inventory_item_id
           and organization_id = mmt.organization_id) 
   AND MSI.SEGMENT1 in ('xxxxxxx')
 GROUP BY LOC.INVENTORY_LOCATION_ID,
          PAR.ORGANIZATION_CODE,    
          MSI.SEGMENT1,
          MSI.DESCRIPTION,   
          DECODE(MSI.END_ASSEMBLY_PEGGING_FLAG,
                 'A',
                 'Soft PEGGING',
                 'B',
                 'END ASSEMBLY/Soft PEGGING',
                 'I',
                 'Hard Pegging',
                 'N',
                 'NONE',
                 'X',
                 'End Assembly/Hard Pegging',
                 'Y',
                 'END ASSEMBLY PEGGING'),   
          LOC.SUBINVENTORY_CODE, 
          SUB.AVAILABILITY_TYPE,       
          OHM.SUBINVENTORY_CODE,
          --MSN.SERIAL_NUMBER,
          LOC.INVENTORY_LOCATION_ID,
          OHM.LOCATOR_ID,
          LOC.SEGMENT1 || '.' || LOC.SEGMENT2 || '.' || LOC.SEGMENT3 || '.' ||
          LOC.SEGMENT4 || '.' || PRJ.SEGMENT1 || '.' || TSK.TASK_NUMBER,
          MSI.INVENTORY_ITEM_ID,
          OHM.ORGANIZATION_ID