Fusion Reference

INV_ONHAND_QUANTITIES_SUMMARY

Product: INVtransactionPer inventory org

Pre-aggregated on-hand at the item + location grain, maintained automatically in step with the detail table — the ready-made balance EBS never had

Module: InventoryInventory-org partitioned (ORGANIZATION_ID)EBS equivalent: MTL_ONHAND_QUANTITIES_DETAIL

Notes

Every write to the detail table updates the matching summary row, so this is current state, not history. The balance column is plainly named QUANTITY (primary UOM), with stocking- and secondary-UOM twins; LAST_QUANTITY holds the prior balance before the last change.

What the badges mean

master: Data class: what the table holds — master data, transaction documents, control/configuration, interface/staging, or a documented view.

Fields

16 fields · 1 key

Table fields: position, field name, description, data type, and flags. 16 fields.
#	Field	Description	Type	Flags
1	ONHAND_SUMMARY_ID	Surrogate key of the summary row	NUMBER	Key
2	INVENTORY_ITEM_ID	Item on hand	NUMBER
3	ORGANIZATION_ID	Inventory org holding the stock	NUMBER
4	SUBINVENTORY_CODE	Subinventory holding the stock	VARCHAR2
5	LOCATOR_ID	Locator holding the stock	NUMBER
6	LOT_NUMBER	Lot, when lot-controlled	VARCHAR2
7	QUANTITY	Consolidated on-hand in the primary UOM — the ready-made balance	NUMBER
8	PRIMARY_UOM_CODE	Primary UOM of the balance	VARCHAR2
9	SECONDARY_UOM_QUANTITY	Balance converted to the secondary UOM	NUMBER
10	SECONDARY_UOM_CODE	Secondary UOM	VARCHAR2
11	STOCKING_UOM_QUANTITY	Balance converted to the stocking UOM	NUMBER
12	STOCKING_UOM_CODE	Stocking UOM	VARCHAR2
13	LAST_QUANTITY	Prior balance (primary UOM) before the last change	NUMBER
14	LOT_CONTROL_CODE	Item lot-control setting denormalized onto the row	NUMBER
15	SERIAL_NUMBER_CONTROL_CODE	Item serial-control setting denormalized onto the row	NUMBER
16	REVISION_QTY_CONTROL_CODE	Item revision-control setting denormalized onto the row	NUMBER

Field provenance: hand-curated. 1 key field.

Boilerplate SQL

Starting point for reading the BICC-landed copy of INV_ONHAND_QUANTITIES_SUMMARYon Databricks — real DATE columns need no conversion, and the org anchor and the LAST_UPDATE_DATE watermark (the column incremental BICC extracts key on) are already in place. Set your Unity Catalog location, schema, and org values below; they’re substituted into the SQL and the copy button.

Query parameters

CatalogSchemaInventory org (ORGANIZATION_ID)On-hand summary IDwatermark

Include optional filters (2)

-- ============================================================
-- Table  : INV_ONHAND_QUANTITIES_SUMMARY — Pre-aggregated on-hand at the item + location grain, maintained automatically in step with the detail table — the ready-made balance EBS never had
-- Purpose: Column-selected read of INV_ONHAND_QUANTITIES_SUMMARY — auto-generated from field metadata
-- Grain  : One row per inventory org (ORGANIZATION_ID) + ONHAND_SUMMARY_ID
-- Notes  : Auto-generated skeleton for Oracle Fusion Cloud data landed in your lakehouse by a BICC extract — there is no SQL path to the SaaS database. Column names follow Oracle's table documentation — if your landed data still carries PVO attribute headers, map names first; see quirks #pvo-drift. Dates are real DATE/TIMESTAMP columns — no conversion needed. WHO audit columns omitted (see the quirks guide); the optional LAST_UPDATE_DATE watermark filter supports incremental extracts.
-- ============================================================
SELECT
  t.ONHAND_SUMMARY_ID AS "Surrogate key of the summary row",
  t.INVENTORY_ITEM_ID AS "Item on hand",
  t.ORGANIZATION_ID AS "Inventory org holding the stock",
  t.SUBINVENTORY_CODE AS "Subinventory holding the stock",
  t.LOCATOR_ID AS "Locator holding the stock",
  t.LOT_NUMBER AS "Lot, when lot-controlled",
  t.QUANTITY AS "Consolidated on-hand in the primary UOM — the ready-made balance",
  t.PRIMARY_UOM_CODE AS "Primary UOM of the balance",
  t.SECONDARY_UOM_QUANTITY AS "Balance converted to the secondary UOM",
  t.SECONDARY_UOM_CODE AS "Secondary UOM",
  t.STOCKING_UOM_QUANTITY AS "Balance converted to the stocking UOM",
  t.STOCKING_UOM_CODE AS "Stocking UOM",
  t.LAST_QUANTITY AS "Prior balance (primary UOM) before the last change",
  t.LOT_CONTROL_CODE AS "Item lot-control setting denormalized onto the row",
  t.SERIAL_NUMBER_CONTROL_CODE AS "Item serial-control setting denormalized onto the row",
  t.REVISION_QTY_CONTROL_CODE AS "Item revision-control setting denormalized onto the row"
FROM <catalog>.<schema>.INV_ONHAND_QUANTITIES_SUMMARY t
WHERE
  t.ORGANIZATION_ID = <inventory_org_id>  -- inventory org, NOT the business unit — see quirks guide #item-org-striping
  -- AND t.ONHAND_SUMMARY_ID = <ONHAND_SUMMARY_ID>
  -- AND t.LAST_UPDATE_DATE >= TIMESTAMP '<watermark>'  -- the column incremental BICC extracts key on
ORDER BY t.ONHAND_SUMMARY_ID;

5 parameters not filled: <catalog>, <schema>, <inventory_org_id>, <ONHAND_SUMMARY_ID>, <watermark>

Relationships

1-hop neighbors — click a table to navigate there. FND lookup decode and translation edges are highlighted; they’re the joins newcomers most often get wrong.

Join details

INV_ONHAND_QUANTITIES_SUMMARYEGP_SYSTEM_ITEMS_Bforeign key · N:1
ON INV_ONHAND_QUANTITIES_SUMMARY.INVENTORY_ITEM_ID = EGP_SYSTEM_ITEMS_B.INVENTORY_ITEM_ID AND INV_ONHAND_QUANTITIES_SUMMARY.ORGANIZATION_ID = EGP_SYSTEM_ITEMS_B.ORGANIZATION_ID
INV_ONHAND_QUANTITIES_SUMMARYINV_SECONDARY_INVENTORIESforeign key · N:1
ON INV_ONHAND_QUANTITIES_SUMMARY.SUBINVENTORY_CODE = INV_SECONDARY_INVENTORIES.SECONDARY_INVENTORY_NAME AND INV_ONHAND_QUANTITIES_SUMMARY.ORGANIZATION_ID = INV_SECONDARY_INVENTORIES.ORGANIZATION_ID

Browse more Inventorytables →