Search Results mtl_onhand_quantities




The MTL_ONHAND_QUANTITIES table is a critical inventory management entity within Oracle E-Business Suite (EBS) 12.1.1 and 12.2.2. It serves as the primary repository for tracking real-time on-hand inventory quantities across organizations, subinventories, and locators. This table is fundamental to Oracle Inventory and related modules, providing visibility into item availability, stock levels, and material movement. Below is a detailed technical and functional analysis of its structure, purpose, and integration within Oracle EBS.

1. Table Purpose and Functional Role

The MTL_ONHAND_QUANTITIES table stores granular details about item quantities physically available in inventory. It supports core functionalities such as:

  • Inventory Visibility: Tracks quantities by organization, subinventory, locator, lot, and serial number.
  • Material Transactions: Updated in real-time during receipts, issues, transfers, and adjustments.
  • Supply Chain Planning: Provides data for ATP (Available-to-Promise), replenishment, and MRP.
  • Costing: Links to CST_QUANTITY_LAYERS for inventory valuation.

2. Key Columns and Relationships

The table's structure includes columns that map to organizational hierarchies and item attributes:

Column Description Related Tables
INVENTORY_ITEM_ID Foreign key to MTL_SYSTEM_ITEMS_B for item master data. MTL_SYSTEM_ITEMS_B
ORGANIZATION_ID Links to HR_ORGANIZATION_UNITS for inventory org. HR_ORGANIZATION_UNITS
SUBINVENTORY_CODE Identifies storage subinventory (e.g., STORES, WIP). MTL_SECONDARY_INVENTORIES
LOCATOR_ID Optional locator detail; references MTL_ITEM_LOCATIONS. MTL_ITEM_LOCATIONS
LOT_NUMBER, SERIAL_NUMBER Tracks lot/serial-controlled items. MTL_LOT_NUMBERS, MTL_SERIAL_NUMBERS
PRIMARY_QUANTITY Quantity in primary UOM (Unit of Measure). Derived from MTL_UOM_CONVERSIONS.

3. Integration with Oracle EBS Modules

The table interacts with multiple EBS modules:

  • Order Management: Validates ATP checks using on-hand data.
  • Purchasing: Updates quantities upon receipt against PO lines.
  • Manufacturing: Tracks component issues and finished goods receipts.
  • Cost Management: Syncs with CST_QUANTITY_LAYERS for FIFO/LIFO costing.

4. Technical Considerations

Key technical aspects include:

  • Indexing: Heavily indexed on INVENTORY_ITEM_ID, ORGANIZATION_ID, and SUBINVENTORY_CODE for performance.
  • Partitioning: In 12.2.2, partitioning by ORGANIZATION_ID improves query performance.
  • Concurrency: Uses row-level locking during transactions to prevent data corruption.

5. Customization and Extensions

Common extensions include:

  • Triggers: Custom logic to validate on-hand updates.
  • Views: Joined with MTL_SYSTEM_ITEMS_B for reporting.
  • APIs: Oracle's INV_QUANTITY_TREE_PUB for safe data access.

6. Data Maintenance

Routine activities involve:

  • Purge: Archived via INV_PURGE_UTIL to manage growth.
  • Reconciliation: Compared with MTL_MATERIAL_TRANSACTIONS for accuracy.

In summary, MTL_ONHAND_QUANTITIES is a backbone table for inventory operations in Oracle EBS, enabling real-time tracking, multi-org visibility, and integration with supply chain processes. Its design ensures scalability and compliance with Oracle's inventory architecture.