Onhand Quantity Query in Oracle Fusion

Onhand Quantity Query in Oracle Fusion

Hi friends, we are going to discuss about the Onhand Quantity Query in Oracle Fusion. We will share the detail sql query to find out the onhand quantity in oracle fusion. This query help you to find the item wise onhand details in fusion system. We will also share some of the important tables which helps to share the Onhand item informations in oracle fusion. Using this query , we can develop the inventory item onhand reports in oracle fusion. This is one of the most important sql query about inventory in oracle fusion. Please find below the complete detail about Onhand Quantity Query in Oracle Fusion.

Onhand Quantity Query in Oracle Fusion

Top 6 Onhand Quantity Tables in Oracle Fusion

1.INV_ORG_PARAMETERS

2.EGP_SYSTEM_ITEMS

3.INV_ONHAND_QUANTITIES_DETAIL

4.INV_UNITS_OF_MEASURE_TL

5.INV_UNITS_OF_MEASURE_B

6.INV_ORGANIZATION_DEFINITIONS_V

Detail SQL Query to extract the Onhand Quantity Query in Oracle Fusion

Here below is the sql query which helps to extract the onhand details in oracle fusion. We can use this sql query to develop the custom onhand report in oracle fusion.

select   IODV.ORGANIZATION_NAME " Inventory Org Name",

    IOP.ORGANIZATION_CODE " Inventory Org Code" ,

ESI.ITEM_NUMBER "Inventory Item" ,

  ESI.DESCRIPTION  "Item Description",

  UOMT.UNIT_OF_MEASURE  "UOM",

  SUM(IOQD.TRANSACTION_QUANTITY) "Total Onhand Qty",

  IOQD.LOT_NUMBER "Onhand Lot" ,

  IOQD.SUBINVENTORY_CODE "Onhand Subinventory" 

from 

  INV_ORG_PARAMETERS IOP ,

  EGP_SYSTEM_ITEMS ESI ,

INV_ONHAND_QUANTITIES_DETAIL IOQD ,

  INV_UNITS_OF_MEASURE_TL UOMT ,

  INV_UNITS_OF_MEASURE_B UOMB,

  INV_ORGANIZATION_DEFINITIONS_V IODV

where 1 = 1

and IODV.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.ORGANIZATION_ID     = IOP.ORGANIZATION_ID

and ESI.ITEM_NUMBER=:P_ITEM_CODE

and IOP.ORGANIZATION_ID=:P_ORG_ID

and IOQD.INVENTORY_ITEM_ID  = ESI.INVENTORY_ITEM_ID

and IOQD.ORGANIZATION_ID    = ESI.ORGANIZATION_ID

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 IODV.ORGANIZATION_NAME,ESI.ITEM_NUMBER

Onhand Quantity Query in Oracle Fusion