Results for “mtl_onhand_quantities_detail”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

MTL_ONHAND_QUANTITIES_DETAIL is the Inventory (INV) module table that holds on-hand quantity balances at the finest level of granularity supported by Oracle E-Business Suite: FIFO quantities broken out by control level — organization, subinventory, locator, revision, lot, and status — and by individual receipt. It operates in both 12.1.1 and 12.2.2, and the documented physical schema lists 33 columns. This is the source of truth for on-hand availability consumed by shipping, receiving, order management, manufacturing, and cost management processes, and it underpins the FIFO cost layering used to value inventory issues.

From a dimensional modeling perspective, the ETRM metadata classifies this object heuristically as standalone rather than as a hub, link, or satellite. In Data Vault terms it behaves much like a transaction-level fact or a satellite of the item/organization/locator business key, since each row is a discrete on-hand record tied to a receipt event rather than a pure reference entity. This classification should be treated as a modeling suggestion, not an architectural constraint.

Key Information Stored

The most significant columns in this table include:

• ONHAND_QUANTITIES_ID — surrogate primary key, defined by the MTL_ONHAND_QUANTITIES_PK index; uniquely identifies each detail row.
• INVENTORY_ITEM_ID, ORGANIZATION_ID, OWNING_ORGANIZATION_ID, PLANNING_ORGANIZATION_ID, DATE_RECEIVED — together these form the composite business key MTL_FIFO_COST_U1, which enforces FIFO uniqueness by organization and receipt date.
• SUBINVENTORY_CODE and LOCATOR_ID — identify the physical storage location of the on-hand quantity.
• REVISION and LOT_NUMBER — capture revision and lot control attributes where enabled.
• PRIMARY_TRANSACTION_QUANTITY — the on-hand quantity in the primary UOM.
• TRANSACTION_UOM_CODE and TRANSACTION_QUANTITY — secondary quantity representation; SECONDARY_UOM_CODE and SECONDARY_TRANSACTION_QUANTITY support dual-unit tracking.
• COST_GROUP_ID — foreign key to CST_COST_GROUPS, used in costing and FIFO valuation.
• STATUS_ID — material status controlling usability of the on-hand quantity.
• LPN_ID — license plate number association, relevant in 12.2.2 warehouse management, alongside CONTAINERIZED_FLAG and IS_CONSIGNED.

Common Use Cases and Queries

Typical scenarios include on-hand inventory reporting, FIFO cost layer reconciliation, available-to-promise checks, and subinventory transfer validation. A common query pattern aggregates quantity by item and subinventory:

SELECT inventory_item_id, organization_id, subinventory_code,
       SUM(primary_transaction_quantity) available_qty
FROM   mtl_onhand_quantities_detail
WHERE  organization_id = :org_id
GROUP  BY inventory_item_id, organization_id, subinventory_code;

Because the composite key MTL_FIFO_COST_U1 spans organization and receipt date, receipt-level FIFO analysis and aging reports are straightforward. Reporting teams frequently join this table to MTL_SYSTEM_ITEMS_B, MTL_ITEM_LOCATIONS, and MTL_LOT_NUMBERS to enrich descriptive attributes.

Related Objects

• CST_COST_GROUPS — referenced via COST_GROUP_ID for cost grouping.
• MTL_ONHAND_QUANTITIES — the aggregated on-hand summary maintained in parallel.
• MTL_SYSTEM_ITEMS_B — item master descriptive and control information.
• MTL_ITEM_LOCATIONS — locator definitions joined via LOCATOR_ID.
• MTL_LOT_NUMBERS and MTL_ITEM_REVISIONS_B — lot and revision validation.
• MTL_MATERIAL_TRANSACTIONS — transaction history tied through CREATE_TRANSACTION_ID and UPDATE_TRANSACTION_ID.
• MTL_SECONDARY_INVENTORIES — subinventory attributes joined via SUBINVENTORY_CODE.