Query to Check On-Hand Quantity from a Receipt in Inventory

In Oracle Inventory, the table INV_ONHAND_QUANTITIES_DETAIL stores the current on-hand quantity of items available in inventory. This table is commonly used when we want to verify how much quantity is available for a specific item after receiving goods.

To trace inventory quantities related to a particular receipt, we can join the Receiving tables with the inventory on-hand table.

In the example below, we retrieve the on-hand quantity information for items that were received under receipt number 8766

SELECT

onhand.*

FROM

rcv_shipment_headers hdr,

rcv_shipment_lines line,

inv_onhand_quantities_detail onhand

WHERE

hdr.shipment_header_id = line.shipment_header_id

AND onhand.inventory_item_id = line.item_id

AND hdr.receipt_num = 1278;

Explanation

• RCV_SHIPMENT_HEADERS – Stores header level information about receiving transactions.

• RCV_SHIPMENT_LINES – Contains the individual item lines associated with the receipt.

• INV_ONHAND_QUANTITIES_DETAIL – Maintains the detailed on-hand inventory quantity for items.

By joining these tables, we can identify the inventory quantities associated with items received in a particular receipt transaction.

This approach is helpful for:

• Tracking inventory after goods receipt

• Validating quantity updates in inventory

• Troubleshooting receiving or inventory discrepancies

Happy Learning