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.

Table: MTL_ONHAND_QUANTITIES 12.1.1
product: INV - Inventory , description: FIFO quantities by control level and receipt , implementation_dba_data: Not implemented in this database ,
View: MTL_ONHAND_QUANTITIES 12.2.2
owner:APPS, object_type:VIEW, fnd_design_data:INV.MTL_ONHAND_QUANTITIES, object_name:MTL_ONHAND_QUANTITIES, status:VALID, product: INV - Inventory , description: View of items owned, planned and physically belonging to the same inventory organization , implementation_dba_data: APPS.MTL_ONHAND_QUANTITIES ,
Table: MTL_ONHAND_QUANTITIES 12.2.2
product: INV - Inventory , description: FIFO quantities by control level and receipt , implementation_dba_data: Not implemented in this database ,
View: MTL_ONHAND_QUANTITIES 12.1.1
owner:APPS, object_type:VIEW, fnd_design_data:INV.MTL_ONHAND_QUANTITIES, object_name:MTL_ONHAND_QUANTITIES, status:VALID, product: INV - Inventory , description: View of items owned, planned and physically belonging to the same inventory organization , implementation_dba_data: APPS.MTL_ONHAND_QUANTITIES ,
Table: MTL_MATERIAL_TRANSACTIONS 12.2.2
owner:INV, object_type:TABLE, fnd_design_data:INV.MTL_MATERIAL_TRANSACTIONS, object_name:MTL_MATERIAL_TRANSACTIONS, status:VALID, product: INV - Inventory , description: Material transaction table , implementation_dba_data: INV.MTL_MATERIAL_TRANSACTIONS ,
Table: MTL_MATERIAL_TRANSACTIONS 12.1.1
owner:INV, object_type:TABLE, fnd_design_data:INV.MTL_MATERIAL_TRANSACTIONS, object_name:MTL_MATERIAL_TRANSACTIONS, status:VALID, product: INV - Inventory , description: Material transaction table , implementation_dba_data: INV.MTL_MATERIAL_TRANSACTIONS ,
View: INVBV_ONHAND_BALANCES 12.1.1
owner:APPS, object_type:VIEW, fnd_design_data:INV.INVBV_ONHAND_BALANCES, object_name:INVBV_ONHAND_BALANCES, status:VALID, product: INV - Inventory , description: - Retrofitted , implementation_dba_data: APPS.INVBV_ONHAND_BALANCES ,
View: INVBV_ONHAND_BALANCES 12.2.2
owner:APPS, object_type:VIEW, fnd_design_data:INV.INVBV_ONHAND_BALANCES, object_name:INVBV_ONHAND_BALANCES, status:VALID, product: INV - Inventory , description: - Retrofitted , implementation_dba_data: APPS.INVBV_ONHAND_BALANCES ,
View: INVFV_ONHAND_BALANCES 12.1.1
owner:APPS, object_type:VIEW, fnd_design_data:INV.INVFV_ONHAND_BALANCES, object_name:INVFV_ONHAND_BALANCES, status:VALID, product: INV - Inventory , description: - Retrofitted , implementation_dba_data: APPS.INVFV_ONHAND_BALANCES ,
View: INVFV_ONHAND_BALANCES 12.2.2
owner:APPS, object_type:VIEW, fnd_design_data:INV.INVFV_ONHAND_BALANCES, object_name:INVFV_ONHAND_BALANCES, status:VALID, product: INV - Inventory , description: - Retrofitted , implementation_dba_data: APPS.INVFV_ONHAND_BALANCES ,
View: MTL_ATT_QTY_V 12.1.1
owner:APPS, object_type:VIEW, fnd_design_data:INV.MTL_ATT_QTY_V, object_name:MTL_ATT_QTY_V, status:VALID, product: INV - Inventory , description: No longer used , implementation_dba_data: APPS.MTL_ATT_QTY_V ,
View: MTL_ATT_QTY_V 12.2.2
owner:APPS, object_type:VIEW, fnd_design_data:INV.MTL_ATT_QTY_V, object_name:MTL_ATT_QTY_V, status:VALID, product: INV - Inventory , description: No longer used , implementation_dba_data: APPS.MTL_ATT_QTY_V ,
Table: MTL_LOT_NUMBERS 12.1.1
owner:INV, object_type:TABLE, fnd_design_data:INV.MTL_LOT_NUMBERS, object_name:MTL_LOT_NUMBERS, status:VALID, product: INV - Inventory , description: Lot number definitions , implementation_dba_data: INV.MTL_LOT_NUMBERS ,
Table: MTL_ITEM_REVISIONS_B 12.2.2
owner:INV, object_type:TABLE, fnd_design_data:INV.MTL_ITEM_REVISIONS_B, object_name:MTL_ITEM_REVISIONS_B, status:VALID, product: INV - Inventory , description: Item revisions , implementation_dba_data: INV.MTL_ITEM_REVISIONS_B ,
Table: MTL_LOT_NUMBERS 12.2.2
owner:INV, object_type:TABLE, fnd_design_data:INV.MTL_LOT_NUMBERS, object_name:MTL_LOT_NUMBERS, status:VALID, product: INV - Inventory , description: Lot number definitions , implementation_dba_data: INV.MTL_LOT_NUMBERS ,
Table: MTL_ITEM_REVISIONS_B 12.1.1
owner:INV, object_type:TABLE, fnd_design_data:INV.MTL_ITEM_REVISIONS_B, object_name:MTL_ITEM_REVISIONS_B, status:VALID, product: INV - Inventory , description: Item revisions , implementation_dba_data: INV.MTL_ITEM_REVISIONS_B ,
Table: MTL_ITEM_LOCATIONS 12.1.1
owner:INV, object_type:TABLE, fnd_design_data:INV.MTL_ITEM_LOCATIONS, object_name:MTL_ITEM_LOCATIONS, status:VALID, product: INV - Inventory , description: Definitions for stock locators , implementation_dba_data: INV.MTL_ITEM_LOCATIONS ,
Table: MTL_ITEM_LOCATIONS 12.2.2
owner:INV, object_type:TABLE, fnd_design_data:INV.MTL_ITEM_LOCATIONS, object_name:MTL_ITEM_LOCATIONS, status:VALID, product: INV - Inventory , description: Definitions for stock locators , implementation_dba_data: INV.MTL_ITEM_LOCATIONS ,
Table: MTL_SECONDARY_INVENTORIES 12.2.2
owner:INV, object_type:TABLE, fnd_design_data:INV.MTL_SECONDARY_INVENTORIES, object_name:MTL_SECONDARY_INVENTORIES, status:VALID, product: INV - Inventory , description: Subinventory definitions , implementation_dba_data: INV.MTL_SECONDARY_INVENTORIES ,
Table: MTL_SECONDARY_INVENTORIES 12.1.1
owner:INV, object_type:TABLE, fnd_design_data:INV.MTL_SECONDARY_INVENTORIES, object_name:MTL_SECONDARY_INVENTORIES, status:VALID, product: INV - Inventory , description: Subinventory definitions , implementation_dba_data: INV.MTL_SECONDARY_INVENTORIES ,
Table: MTL_SYSTEM_ITEMS_B 12.1.1
owner:INV, object_type:TABLE, fnd_design_data:INV.MTL_SYSTEM_ITEMS_B, object_name:MTL_SYSTEM_ITEMS_B, status:VALID, product: INV - Inventory , description: Inventory item definitions , implementation_dba_data: INV.MTL_SYSTEM_ITEMS_B ,
Table: MTL_SYSTEM_ITEMS_B 12.2.2
owner:INV, object_type:TABLE, fnd_design_data:INV.MTL_SYSTEM_ITEMS_B, object_name:MTL_SYSTEM_ITEMS_B, status:VALID, product: INV - Inventory , description: Inventory item definitions , implementation_dba_data: INV.MTL_SYSTEM_ITEMS_B ,