One of the most common business requirements in Inventory Management is the ability to view on-hand inventory at a detailed level- specifically by Subinventory, Locator, and Lot. This level of visibility helps business users clearly understand current stock positions and make informed operational decisions.
Inventory is not always immediately available for use. While some stock is Active and available, other quantities may be in statuses such as:
- Quarantine
- Quality Inspection
- Expired
- Restricted or Hold
Inventory in Quarantine or Quality cannot be used to fulfill customer demand or support internal manufacturing until it is released. Similarly, expired lot-controlled items cannot be issued to sales orders or work orders.
Without visibility into material status and lot expiration, planning and fulfillment teams may assume inventory is available when it is not — leading to delays and operational disruptions.
Below is a sample SQL query to get on-hand quantities by Subinventory, Locator, and Lot, including Subinventory Material Status, Lot Material Status, and Lot Expiration Date. Material Status determines whether the inventory can be used.
SELECT iop.organization_code,”||item.item_number item_number,
oh.subinventory_code, loc.segment1 locator, oh.lot_number,
SUM(NVL(oh.transaction_quantity,0)) onhand_qty, oh.TRANSACTION_UOM_CODE UOM,
Sub_Status.status_code Subinv_Material_Status, Lot_Status.status_code Lot_Material_status, to_char(LOT.EXPIRATION_DATE,’MM/DD/YYYY hh24:mi:ss’) LOT_EXPIRATION_DATE
FROM egp_system_items item,
inv_onhand_quantities_detail oh,
inv_secondary_inventories sub,
INV_MATERIAL_STATUSES_VL Sub_Status,
inv_item_locations loc,
inv_org_parameters iop,
INV_LOT_NUMBERS LOT,
INV_MATERIAL_STATUSES_VL Lot_Status
WHERE oh.inventory_item_id = item.inventory_item_id
AND oh.organization_id = item.organization_id
AND iop.organization_id = item.organization_id
ANd oh.subinventory_code = sub.secondary_inventory_name
AND oh.organization_id = sub.organization_id
AND Sub_Status.status_id = sub.status_id
–AND sub.reservable_type = 1 — Reservable quantities
–AND oh.inventory_item_id = loc.INVENTORY_ITEM_ID (+)
AND oh.organization_id = loc.organization_id(+)
and oh.subinventory_code = loc.subinventory_code(+)
and oh.locator_id = loc.inventory_location_id(+)
AND oh.LOT_NUMBER = lot.lOT_NUMBER
AND oh.ORGANIZATION_ID = lot.ORGANIZATION_ID
and oh.INVENTORY_ITEM_ID = lot.INVENTORY_ITEM_ID
AND lot.status_id = Lot_Status.status_id
–AND ( Sub_Status.status_code=’Quarantine’ OR Lot_Status.status_code=’Quarantine’ )
GROUP BY iop.organization_code, item.item_number, oh.subinventory_code, Sub_Status.status_code,loc.segment1, oh.lot_number,Lot_Status.status_code, lot.EXPIRATION_DATE,oh.TRANSACTION_UOM_CODE
ORDER BY iop.organization_code, item.item_number, oh.subinventory_code, oh.lot_number
Sample Report Output:
