Important SQL Query to find out the Onhand Quantity in Oracle Fusion

Hi friends, , We are going to discuss about the Query to find onhand quantity in oracle Fusion. We will share the detail sql query which helps to find out the item wise onhand quantity in oracle fusion. Using this sql query, we will be able to find out the system onhand quantities against which lot number and the SubInventory. This sql query will helps to find out the SubInventory for the onhand quantity. We will also share some of the important inventory onhand tables too in oracle fusion which helps to store the item onhand details in fusion. This below onhand sql query is very useful query to extract the system item level onhand details in oracle fusion. Please find below the complete detail about Query to find onhand quantity in oracle Fusion.

Important SQL Query to find out the Onhand Quantity in Oracle Fusion

Top 4 Inventory onhand tables in oracle fusion

1.INV_ONHAND_QUANTITIES_DETAIL

2.INV_UNITS_OF_MEASURE_TL

3.EGP_SYSTEM_ITEMS

4.INV_ORGANIZATION_DEFINITIONS_V

Important SQL Query to find out the onhand quantity in oracle fusion

select   IODV.ORGANIZATION_NAME " Inventory Organization",

    IOP.ORGANIZATION_CODE " Inventory Org Code" ,

ESI.ITEM_NUMBER "Item Code" ,

  ESI.DESCRIPTION  "Item Description",

  UOMT.UNIT_OF_MEASURE  "Item UOM",

  SUM(IOQD.TRANSACTION_QUANTITY) "Onhand Qty",

  IOQD.SUBINVENTORY_CODE "Item Onhand Subinventory" ,

  IOQD.LOT_NUMBER "Item Onhand Lot" 

from INV_ONHAND_QUANTITIES_DETAIL IOQD ,

  INV_UNITS_OF_MEASURE_TL UOMT ,

  INV_UNITS_OF_MEASURE_B UOMB,

  INV_ORG_PARAMETERS IOP ,

  EGP_SYSTEM_ITEMS ESI ,

  INV_ORGANIZATION_DEFINITIONS_V IODV

where 1 = 1

and IODV.ORGANIZATION_ID    =IOP.ORGANIZATION_ID

and IOQD.INVENTORY_ITEM_ID  = ESI.INVENTORY_ITEM_ID

and IOQD.ORGANIZATION_ID    = ESI.ORGANIZATION_ID

and ESI.ORGANIZATION_ID     = IOP.ORGANIZATION_ID

and UOMT.UNIT_OF_MEASURE_ID = UOMB.UNIT_OF_MEASURE_ID

and UOMB.UOM_CODE           = IOQD.TRANSACTION_UOM_CODE

and ESI.ITEM_NUMBER=:P_ITEM_CODE

and IOP.ORGANIZATION_ID=89

group by ESI.ITEM_NUMBER ,

  IODV.ORGANIZATION_NAME,

  ESI.DESCRIPTION ,

  IOP.ORGANIZATION_CODE ,

  UOMT.UNIT_OF_MEASURE ,

  IOQD.SUBINVENTORY_CODE ,

  IOQD.LOT_NUMBER

order by ESI.ITEM_NUMBER

Important SQL Query to find out the Onhand Quantity in Oracle Fusion

Important SQL Query to find out the Onhand Quantity in Oracle Fusion