SQL Query to Extract Item Onhand , Reserved & Transact Quantity in Oracle Fusion

Hi Friends, we are going to discuss about the SQL Query to Extract Item Onhand , Reserved & Transact Quantity in Oracle Fusion. We will share the details sql query which helps to extract the complete details about the Inventory item stock status. Using this Query , we can able to find out the Actual Onhand for the Inventory item in oracle fusion and how much qty/stock is reserved against the sales order or in production process. If you want to develop the Item Stock report in oracle fusion then this is the best query  which you can use to develop the custom BIP reports in oracle fusion. In oracle fusion , some inventory item got changed as compared to oracle apps r12 tables. In Fusion some of the Inventory tables starts EGP% prefix. 

Onhand Qty :- Onhand is the total qty available for an item in system which we have not consumed yet.

Reserve Qty:- Reserve qty is also a onhand qty but it is already reserved against some sales order or move order.

Transaction Qty:- Transact qty is the difference of Onhand Qty - Reserve Qty. This is the actual onhand which we can use in the system.

3 Important Tables about Item Onhand , Reserved & Transact Quantity in Oracle Fusion

1.INV_RESERVATIONS

2.INV_ONHAND_QUANTITIES_DETAIL

3.EGP_SYSTEM_ITEMS_B

SQL Query to extract the Item Details about Onhand , Reserved & Transact Quantity in Oracle Fusion

Here below is the SQL query which helps to extract the complete information's about item onhand stock in oracle fusion inventory.

Item Number, Item Description, UOM, Total Quantity, Available to Transact Quantity, Sales Order Reserved Quantity.

Following are the tables where the data for a few fields can be fetched:

INV_RESERVATIONS

INV_ONHAND_QUANTITIES_DETAIL

EGP_SYSTEM_ITEMS_B

Select ESIB.ITEM_NUMBER, ESIT.DESCRIPTION, ESIB.PRIMARY_UOM_CODE, IR.RESERVATION_QUANTITY

from

EGP_SYSTEM_ITEMS_B ESIB,

EGP_SYSTEM_ITEMS_TL ESIT,

INV_RESERVATIONS IR

where ESIB.INVENTORY_ITEM_ID=ESIT.INVENTORY_ITEM_ID

and ESIB.ORGANIZATION_ID=ESIT.ORGANIZATION_ID

and ESIT.LANGUAGE=USERENV.('LANG')

and IR.INVENTORY_ITEM_ID=ESIB.INVENTORY_ITEM_ID

and IR.ORGANIZATION_ID=ESIB.ORGANIZATION_ID;

This is all about the Item Onhand Tables and SLQ Query in Oracle Fusion 

Thanks !!