Search Results mtl_item_sub_inventories

Overview

The MTL_ITEM_SUB_INVENTORIES table is a core transactional table within the Oracle E-Business Suite Inventory (INV) module, central to material control and storage management. Its primary function is to define and enforce the valid assignment of items to specific subinventories within an organization. This table acts as a master control point, governing where an item can be stored, received, or completed, thereby enabling granular inventory policies. It is a mandatory setup for any item that will have on-hand quantities, as it defines the permissible storage locations. The table's integrity is maintained through foreign key relationships to master data tables, ensuring that assignments are made only to valid items and subinventories.

Key Information Stored

The table's structure is defined by a composite primary key consisting of INVENTORY_ITEM_ID, ORGANIZATION_ID, and SECONDARY_INVENTORY. This enforces uniqueness for each item-subinventory combination per organization. Key columns include INVENTORY_ITEM_ID and ORGANIZATION_ID, linking to MTL_SYSTEM_ITEMS_B to identify the item. The SECONDARY_INVENTORY column, linked to MTL_SECONDARY_INVENTORIES, identifies the assigned subinventory. Additional important columns support advanced functionality: SOURCE_SUBINVENTORY and SOURCE_ORGANIZATION_ID define default replenishment sources, while LOCATOR_TYPE indicates whether locator control is predefined or dynamic. Columns like PICKING_ORDER, DROPPING_ORDER, and INVENTORY_PLANNING_CODE control material handling and planning behaviors for the item within that specific subinventory.

Common Use Cases and Queries

A primary use case is validating item storage locations during transactions like material receipts, subinventory transfers, and work order completions. The table is frequently queried for operational reporting and setup validation. Common SQL patterns include listing all subinventories assigned to a specific item, or identifying all items allowed in a particular subinventory. For example, to find all enabled items in a 'FINISHED GOODS' subinventory for organization 101, one might query: SELECT msi.segment1 Item, msi.description FROM mtl_item_sub_inventories misi, mtl_system_items_b msi WHERE misi.inventory_item_id = msi.inventory_item_id AND misi.organization_id = msi.organization_id AND misi.organization_id = 101 AND misi.secondary_inventory = 'FINISHED GOODS' AND msi.enabled_flag = 'Y'. This table is also critical for defining completion subinventories in Work in Process (WIP) modules.

Related Objects

As indicated by the foreign key metadata, MTL_ITEM_SUB_INVENTORIES has extensive relationships with core Inventory and Manufacturing tables. It is directly referenced by MTL_SECONDARY_LOCATORS for defining item-specific locators. Crucially, it is referenced by key WIP tables—WIP_DISCRETE_JOBS, WIP_FLOW_SCHEDULES, WIP_REPETITIVE_ITEMS, and WIP_REQUIREMENT_OPERATIONS—to validate the completion and supply subinventories used in manufacturing. Its primary foreign key dependencies are on the master data tables MTL_SYSTEM_ITEMS_B (for items) and MTL_SECONDARY_INVENTORIES (for subinventories). The relationships with MTL_PARAMETERS and the source columns further tie it to inter-organization logistics and replenishment rules.