Search Results mtl_onhand_quantities




Overview
Explore in AI Chat

Overview

The MTL_ONHAND_QUANTITIES table is a core transactional table within the Oracle E-Business Suite Inventory (INV) module for versions 12.1.1 and 12.2.2. It serves as the primary repository for storing detailed First-In, First-Out (FIFO) cost layer information for on-hand inventory. Its fundamental role is to track the quantity and cost of inventory items based on their specific receipt date, maintaining granular records by control levels such as subinventory, locator, lot, and revision. This table is essential for accurate inventory valuation, cost management, and supporting FIFO-based cost flow assumptions during material transactions.

Key Information Stored

The table's structure is designed to uniquely identify a specific FIFO cost layer. Its primary key columns are INVENTORY_ITEM_ID, ORGANIZATION_ID, and DATE_RECEIVED, which together define a distinct receipt batch. Other critical columns include SUBINVENTORY_CODE, LOCATOR_ID, LOT_NUMBER, and REVISION, which store the physical and logical attributes of the stock. The PRIMARY_QUANTITY column holds the on-hand amount for that specific layer. Crucially, the table links to material transactions via CREATE_TRANSACTION_ID and UPDATE_TRANSACTION_ID, providing a complete audit trail from the initial receipt through subsequent consumption or adjustments.

Common Use Cases and Queries

This table is central to inventory reporting and cost calculations. A common use case is generating a detailed on-hand valuation report by FIFO layer. Developers and analysts query this table to understand the composition of on-hand balances, often joining it to item and subinventory master tables. A typical query pattern involves filtering by organization and item, and summing quantities by receipt date to analyze aging stock.

  • Sample Query for On-Hand Layers: SELECT moq.inventory_item_id, moq.subinventory_code, moq.date_received, moq.primary_quantity FROM mtl_onhand_quantities moq WHERE moq.organization_id = 101 AND moq.inventory_item_id = 5000 ORDER BY moq.date_received;
  • Reporting Use Case: Calculating the potential cost of goods sold by identifying the oldest (first-in) cost layers that would be consumed in a transaction.
  • Practical Scenario: During month-end closing, financial modules reference this table via costing views to determine the ending inventory valuation under the FIFO costing method.

Related Objects

As indicated by its foreign key constraints, MTL_ONHAND_QUANTITIES has integral relationships with numerous core Inventory tables. It references MTL_SYSTEM_ITEMS_B for item definitions, MTL_SECONDARY_INVENTORIES for subinventory data, and MTL_ITEM_LOCATIONS for locator details. For lot- and revision-controlled items, it links to MTL_LOT_NUMBERS and MTL_ITEM_REVISIONS_B, respectively. Its transactional lineage is maintained through foreign keys to MTL_MATERIAL_TRANSACTIONS. For most reporting and application logic, public interfaces like the Material Transaction and On-hand Quantity APIs should be used instead of direct DML on this table to preserve data integrity.