Search Results mtl_onhand_quantities_detail

Overview

The MTL_ONHAND_QUANTITIES_DETAIL table is a core transactional table within the Oracle E-Business Suite Inventory (INV) module. It serves as the detailed repository for tracking on-hand inventory quantities using the First-In, First-Out (FIFO) costing method. Its primary role is to store granular receipt-level quantity data, enabling the system to accurately determine the cost of goods sold and the valuation of remaining inventory based on the chronological order of receipts. This table is fundamental to the financial integrity of inventory accounting for organizations utilizing the FIFO cost model in versions 12.1.1 and 12.2.2.

Key Information Stored

The table's structure is designed to record inventory balances at multiple organizational and receipt levels. As indicated by the primary key constraints, critical columns include a unique surrogate key (ONHAND_QUANTITIES_ID) and a composite key for FIFO tracking. Essential data points stored are:

• INVENTORY_ITEM_ID and ORGANIZATION_ID: Identify the specific item and its stocking organization.
• OWNING_ORGANIZATION_ID and PLANNING_ORGANIZATION_ID: Support inter-org and multi-org inventory structures.
• DATE_RECEIVED: The pivotal timestamp for FIFO calculation, determining the cost layer sequence.
• Quantity and secondary quantity fields to record the on-hand balance from each specific receipt.

The dual primary keys highlight its dual purpose: ensuring row uniqueness (MTL_ONHAND_QUANTITIES_PK) and supporting the FIFO costing engine's need to query and sequence receipts efficiently (MTL_FIFO_COST_U1).

Common Use Cases and Queries

This table is central to inventory valuation and cost management processes. A primary use case is generating the FIFO cost layers report, which details the quantity and cost of inventory available for consumption, ordered by receipt date. It is also heavily queried during transaction processing (like issues and shipments) to identify the specific cost layer to relieve. Common SQL patterns involve joining with MTL_SYSTEM_ITEMS_B and HR_ORGANIZATION_UNITS for reporting.

SELECT mohqd.inventory_item_id,
       msi.segment1 item_code,
       mohqd.organization_id,
       mohqd.date_received,
       mohqd.primary_transaction_quantity
FROM inv.mtl_onhand_quantities_detail mohqd,
     inv.mtl_system_items_b msi
WHERE mohqd.inventory_item_id = msi.inventory_item_id
  AND mohqd.organization_id = msi.organization_id
  AND mohqd.organization_id = :p_org_id
ORDER BY mohqd.date_received;

This query retrieves the FIFO layers for a given organization, crucial for month-end closing and financial analysis.

Related Objects

MTL_ONHAND_QUANTITIES_DETAIL is intrinsically linked to other key Inventory entities. It is the detailed counterpart to summary on-hand tables like MTL_ONHAND_QUANTITIES. For transactional integrity, it relates to MTL_MATERIAL_TRANSACTIONS, which posts transactions that create or consume the detailed quantities. The table is maintained by internal Inventory APIs and is the direct source for cost calculation views used by the Cost Management module. Key related objects include the MTL_FIFO_COST_U1 index for performance and the primary key relationship with other inventory detail tables.