On Hand Quantity Query in Oracle SCM Cloud

select ESI.ITEM_NUMBER ,
  IODV.ORGANIZATION_NAME,
  ESI.DESCRIPTION ,
  IOP.ORGANIZATION_CODE ,
  SUM(IOQD.TRANSACTION_QUANTITY) TRX_QTY,
  UOMT.UNIT_OF_MEASURE ,
  IOQD.SUBINVENTORY_CODE ,
  IOQD.LOT_NUMBER,
  IIL.SEGMENT1,
  IIL.SEGMENT2,
  IIL.SEGMENT3,
  IIL.SEGMENT4
from INV_ONHAND_QUANTITIES_DETAIL IOQD ,
  EGP_SYSTEM_ITEMS ESI ,
  INV_ORG_PARAMETERS IOP ,
  INV_ORGANIZATION_DEFINITIONS_V IODV,
  INV_UNITS_OF_MEASURE_TL UOMT ,
  INV_UNITS_OF_MEASURE_B UOMB,
  INV_ITEM_LOCATIONS IIL
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 IOQD.ORGANIZATION_ID    = IIL.ORGANIZATION_ID(+)
and IOQD.SUBINVENTORY_CODE  = IIL.SUBINVENTORY_CODE(+)
and IOQD.LOCATOR_ID         =IIL.INVENTORY_LOCATION_ID(+)
and ESI.ITEM_NUMBER        in '<Item Numbers>'
group by ESI.ITEM_NUMBER ,
  IODV.ORGANIZATION_NAME,
  ESI.DESCRIPTION ,
  IOP.ORGANIZATION_CODE ,
  UOMT.UNIT_OF_MEASURE ,
  IOQD.SUBINVENTORY_CODE ,
  IOQD.LOT_NUMBER,
  IIL.SEGMENT1,
  IIL.SEGMENT2,
  IIL.SEGMENT3,
  IIL.SEGMENT4
order by 1,2

Using this query you can find out On hand quantity for any item in Oracle SCM cloud. It used tables in SCM Cloud such as EGP_system_Items, INVentory tables like INV_units_of_measure, INV_org_parameters, INV_onhand_quantities_detail and INV_item_locations tables.

Add any additional columns that you want and use Oracle Cloud Tools SQLConect database browser to learn more about each of these tables/views .

Leave a Reply

Your email address will not be published. Required fields are marked *