Search Results mtl_item_sub_inventories




The MTL_ITEM_SUB_INVENTORIES table in Oracle E-Business Suite (EBS) 12.1.1 and 12.2.2 is a critical inventory management entity that defines the relationship between items and subinventories within the Oracle Inventory module. This table plays a pivotal role in controlling item availability, stock movement, and material transactions by enforcing subinventory-level restrictions for specific items. Below is a detailed technical breakdown of its structure, functionality, and integration within Oracle EBS.

Table Structure and Key Columns

The MTL_ITEM_SUB_INVENTORIES table stores records that associate items (INVENTORY_ITEM_ID) with subinventories (SUBINVENTORY_CODE), along with control attributes. Key columns include:
  • INVENTORY_ITEM_ID: Foreign key to MTL_SYSTEM_ITEMS_B, linking to the item master.
  • ORGANIZATION_ID: Identifies the inventory organization (foreign key to HR_ALL_ORGANIZATION_UNITS).
  • SUBINVENTORY_CODE: References MTL_SECONDARY_INVENTORIES, defining valid storage locations.
  • STATUS_ID: Indicates whether the item-subinventory combination is active or inactive.
  • LOCATOR_TYPE: Controls locator requirements (e.g., predefined, dynamic, or none).
  • INVENTORY_PLANNING_CODE: Determines if MRP/DRP planning is enabled for the item-subinventory pair.
  • PRIMARY_SUPPLY_SUBINVENTORY: Flags whether the subinventory is a primary replenishment source.

Functional Significance

This table serves as a gatekeeper for inventory operations:
  1. Item Restriction: Ensures items can only be transacted in designated subinventories, preventing erroneous stock movements.
  2. Locator Control: Enforces locator-level tracking based on LOCATOR_TYPE settings (e.g., mandatory for lot-controlled items).
  3. Supply Chain Planning: The INVENTORY_PLANNING_CODE column influences MRP calculations by including/excluding subinventory stock.
  4. Replenishment Logic: Designates primary supply sources for automated replenishment workflows.

Integration Points

The table interacts with multiple Oracle EBS modules:
  • Inventory Transactions: Validates subinventory eligibility during material receipts, issues, and transfers.
  • Order Management: Restricts pick release operations to authorized subinventories.
  • Cost Management: Provides subinventory-level cost group associations for valuation.
  • Warehouse Management: Integrates with WMS for directed put-away and picking rules.

Technical Considerations

  • Indexes: Key indexes include MTL_ITEM_SUB_INVENTORIES_U1 (unique on INVENTORY_ITEM_ID, ORGANIZATION_ID, SUBINVENTORY_CODE) and MTL_ITEM_SUB_INVENTORIES_N1 (on SUBINVENTORY_CODE).
  • APIs: Managed via INV_ITEM_SUB_INVENTORY_API for CRUD operations.
  • Data Population: Records are created through the "Item Subinventories" form (INVSUBIN) or via batch programs.

Version-Specific Notes

In EBS 12.2.2, enhancements include:
  • Improved validation for mixed subinventory/item status combinations.
  • Tighter integration with Oracle Warehouse Management Cloud (where applicable).
  • Extended attributes for cross-organization subinventory relationships.
This table is fundamental to inventory control, ensuring compliance with organizational storage policies and enabling precise material flow management across the supply chain.