Search Results mtl_onhand_quantities_detail




The MTL_ONHAND_QUANTITIES_DETAIL table in Oracle E-Business Suite (EBS) 12.1.1 and 12.2.2 is a critical inventory management table that stores detailed on-hand quantity information for items across various organizational structures. This table is part of the Oracle Inventory module and serves as a foundational data source for tracking item quantities, lot/serial attributes, and subinventory details. Below is a detailed breakdown of its structure, purpose, and key functionalities.

Purpose and Overview

The MTL_ONHAND_QUANTITIES_DETAIL table captures granular on-hand inventory data, including item quantities, lot numbers, serial numbers, revisions, and locator details. It supports real-time inventory visibility, enabling organizations to manage stock levels, perform cycle counts, and execute transactions like issues, transfers, and receipts. This table is tightly integrated with other inventory tables such as MTL_SYSTEM_ITEMS_B, MTL_ITEM_LOCATIONS, and MTL_SECONDARY_INVENTORIES.

Key Columns and Their Significance

  • INVENTORY_ITEM_ID: Links to MTL_SYSTEM_ITEMS_B to identify the item.
  • ORGANIZATION_ID: Specifies the inventory organization where the item is stored.
  • SUBINVENTORY_CODE: Identifies the subinventory (e.g., "STOREROOM" or "WIP").
  • LOCATOR_ID: References MTL_ITEM_LOCATIONS for bin-level tracking.
  • LOT_NUMBER and SERIAL_NUMBER: Track lot/serial-controlled items.
  • PRIMARY_QUANTITY: Stores the on-hand quantity in the primary unit of measure.
  • TRANSACTION_QUANTITY: Tracks quantities involved in pending transactions.
  • REVISION: Captures item revision details for engineering-controlled items.

Integration with Other Modules

The table integrates with Oracle's core modules:

  • Order Management: Ensures accurate ATP (Available-to-Promise) checks.
  • Purchasing: Validates receipt quantities against purchase orders.
  • Manufacturing: Tracks WIP (Work in Process) component availability.
  • Cost Management: Provides data for inventory valuation and cost roll-ups.

Performance Considerations

Due to its transactional nature, the MTL_ONHAND_QUANTITIES_DETAIL table can grow significantly in high-volume environments. Oracle recommends:

  • Regularly purging obsolete data using INV_PURGE_UTILITY.
  • Partitioning the table by ORGANIZATION_ID for scalability.
  • Creating indexes on frequently queried columns (e.g., INVENTORY_ITEM_ID, LOT_NUMBER).

Common Use Cases

  • Inventory Reconciliation: Comparing physical counts with system records.
  • Lot/Serial Traceability: Tracking item movements for compliance (e.g., FDA, ISO).
  • Reporting: Generating stock status, aging, or turnover reports.

Differences Between EBS 12.1.1 and 12.2.2

While the core structure remains consistent, EBS 12.2.2 introduces optimizations for cloud deployments, including:

  • Enhanced partitioning strategies for multi-tenant architectures.
  • Improved APIs for REST-based integrations.
  • Additional columns for advanced lot/serial attributes.

Conclusion

The MTL_ONHAND_QUANTITIES_DETAIL table is indispensable for inventory accuracy in Oracle EBS. Its design supports complex tracking requirements while integrating seamlessly with other modules. Proper maintenance and indexing are essential to ensure optimal performance, especially in large-scale implementations.