Search Results mtl_onhand_quantities_detail




Overview
Explore in AI Chat

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:

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.