Search Results mtl_secondary_inventories

Overview

The MTL_SECONDARY_INVENTORIES table is a core master data table within Oracle E-Business Suite Inventory (INV) module. It stores the definition and configuration of all subinventories across every inventory organization. A subinventory represents a physical or logical subdivision of an organization's total inventory, such as a stockroom, warehouse aisle, or staging area. This table is fundamental to inventory transactions, costing, and material storage logic, enabling granular tracking and control of items. Its integrity is enforced by a primary key on the combination of ORGANIZATION_ID and SECONDARY_INVENTORY_NAME, ensuring uniqueness within each organization.

Key Information Stored

The table's columns define the operational and financial characteristics of each subinventory. The primary identifiers are ORGANIZATION_ID and SECONDARY_INVENTORY_NAME. Key configuration columns include DESCRIPTIVE text fields, asset tracking flags (ASSET_INVENTORY), and locator control settings (LOCATOR_TYPE). Crucially, the table holds numerous foreign keys to the GL_CODE_COMBINATIONS table, linking the subinventory to specific general ledger accounts for material, resource, overhead, and expense posting. Other significant references include DEFAULT_COST_GROUP_ID to CST_COST_GROUPS, STATUS_ID to MTL_MATERIAL_STATUSES_B for default status control, and SOURCE_SUBINVENTORY/SOURCE_ORGANIZATION_ID for inter-org transfers. The metadata also indicates links for approval notifications (NOTIFY_LIST_ID) and encumbrance accounting.

Common Use Cases and Queries

This table is central to reporting, data validation, and integration tasks. Common use cases include generating a list of all subinventories for a given organization, validating account combinations before transaction posting, and identifying subinventories configured for asset tracking. A typical query to list subinventories with their material account might be:

• SELECT msi.secondary_inventory_name, msi.description, gcc.segment1, gcc.segment2
• FROM inv.mtl_secondary_inventories msi,
• gl.gl_code_combinations gcc
• WHERE msi.organization_id = :p_org_id
• AND msi.material_account = gcc.code_combination_id;

Another critical scenario involves troubleshooting transaction errors by verifying that all required subinventory parameters, such as the mandatory material account, are correctly populated. Data fixes or migrations often target this table when aligning subinventory structures across environments.

Related Objects

As indicated by the extensive foreign key relationships, MTL_SECONDARY_INVENTORIES is heavily referenced. Key parent tables include MTL_PARAMETERS (for the organization definition), GL_CODE_COMBINATIONS (for various accounts), and CST_COST_GROUPS. It is a parent table itself, referenced by numerous transactional and setup entities. The provided metadata lists AHL_WORKORDER_MTL_TXNS and BOM_OPERATIONAL_ROUTINGS as examples of child tables, where the subinventory code is stored to define completion locations for work orders and routings. In practice, major transactional tables like MTL_MATERIAL_TRANSACTIONS and MTL_ONHAND_QUANTITIES also rely on the subinventory definitions stored here. Key APIs, such as INV_LOCATOR_PUB and INV_ITEM_PUB, internally validate and utilize data from this table.