Search Results containerized_flag




Overview
Explore in AI Chat

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:

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