Item availability Report(Oracle Fusion)

  --Item availability Report --> 

Select  

       a.organization_code Organization_name,

   a.secondary_inventory_name  Subinventory,

   a.item_number,

   a.onhand_qty,

   a.Primary_Reserv_qty,

   a.onhand_qty - a.Primary_Reserv_qty  Available_to_Reserve,

       a.onhand_qty-( a.onhand_qty - a.Primary_Reserv_qty) Reserve_Quantity    

from 

(

SELECT

  iop.organization_code ,

    isi.secondary_inventory_name ,

    esib.item_number  ,

  (NVL(sum(ioqd.primary_transaction_quantity), 0)) onhand_qty,

(select  (nvl(sum(ir.PRIMARY_RESERVATION_QUANTITY),0)) 

from inv_reservations ir  

where 1=1

AND esib.inventory_item_id = ir.inventory_item_id 

AND esib.organization_id = ir.organization_id 

and ioqd.subinventory_code = ir.subinventory_code  )

Primary_Reserv_qty,

(Select sum(TRANSACTION_QUANTITY) 

from inv_material_txns imt

where  esib.inventory_item_id = imt.inventory_item_id 

AND esib.organization_id = imt.organization_id 

and ioqd.subinventory_code = imt.subinventory_code ) mtr_qty

 

 

FROM

  egp_system_items_v esib,

  inv_org_parameters iop,

  inv_secondary_inventories isi,

  inv_item_locations iil,

  inv_onhand_quantities_detail ioqd

  -- inv_reservations ir

WHERE

  iop.organization_id = esib.organization_id

  AND iop.organization_id = isi.organization_id

  AND isi.secondary_inventory_name = ioqd.subinventory_code(+)

  AND isi.organization_id = ioqd.organization_id (+)

  AND ioqd.organization_id = esib.organization_id(+)

  AND ioqd.inventory_item_id = esib.inventory_item_id (+)

  AND ioqd.locator_id = iil.inventory_location_id(+)

  AND ioqd.organization_id = iil.organization_id(+)

  -- AND esib.inventory_item_id = ir.inventory_item_id 

-- AND iop.organization_id = ir.organization_id 

-- AND isi.subinventory_id = ir.subinventory_id 

  -- and esib.ITEM_NUMBvER in ('ABC123','ABC234','ABC456')

GROUP BY

  iop.organization_code,

  esib.item_number,

  esib.primary_uom_code,

  esib.description,

  isi.secondary_inventory_name,

  isi.description,

  iil.locator_name,

  ioqd.revision,

  ioqd.locator_id,

  esib.inventory_item_id,

  isi.subinventory_id,

  esib.organization_id,

  ioqd.subinventory_code

ORDER BY

  isi.secondary_inventory_name,

  esib.item_number

  ) A

  order by  a.item_number,a.secondary_inventory_name