Search Results mtl_secondary_locators

Overview

The MTL_SECONDARY_LOCATORS table is a core data object within the Oracle E-Business Suite Inventory (INV) module, specifically for versions 12.1.1 and 12.2.2. It serves as the system of record for defining and managing secondary locator assignments for items. Its primary role is to enforce and maintain the relationship between an inventory item, a specific subinventory, and an alternate (secondary) locator within that subinventory. This table is essential for supporting flexible material storage and retrieval rules, allowing items to be directed to or picked from predefined alternate locations beyond their primary assignment, which is crucial for complex warehouse management and manufacturing processes.

Key Information Stored

The table's structure centers on a composite primary key that uniquely identifies each secondary locator assignment. The key columns are INVENTORY_ITEM_ID, ORGANIZATION_ID, and SECONDARY_LOCATOR. The INVENTORY_ITEM_ID links to the specific item in MTL_SYSTEM_ITEMS_B. The ORGANIZATION_ID specifies the operating unit. The SECONDARY_LOCATOR is a foreign key to MTL_ITEM_LOCATIONS, storing the identifier for the alternate storage location. While not part of the primary key, the SUBINVENTORY_CODE is a critical foreign key column linking to MTL_ITEM_SUB_INVENTORIES, defining the specific subinventory context for the secondary locator. This design ensures that for a given item and organization, multiple secondary locators can be defined, each scoped to a particular subinventory.

Common Use Cases and Queries

A primary use case is validating and determining valid locators for material transactions, such as a move order or a WIP job completion. The table is queried to present a list of allowable locators for an item in a given subinventory. For reporting, analysts often query this table to audit item storage rules or identify items with multiple locator options. A common SQL pattern retrieves all secondary locators for a specific item and organization, joining to descriptive tables:

• SELECT msn.SECONDARY_LOCATOR, mil.CONCATENATED_SEGMENTS, msn.SUBINVENTORY_CODE FROM INV.MTL_SECONDARY_LOCATORS msn, INV.MTL_ITEM_LOCATIONS mil WHERE msn.INVENTORY_ITEM_ID = :p_item_id AND msn.ORGANIZATION_ID = :p_org_id AND msn.SECONDARY_LOCATOR = mil.INVENTORY_LOCATION_ID(+);

Another critical use is integration with Work in Process (WIP), where the table validates the completion locator specified for a discrete job or repetitive schedule.

Related Objects

MTL_SECONDARY_LOCATORS has integral relationships with several key Inventory and WIP tables, as documented by its foreign key constraints:

• MTL_ITEM_LOCATIONS: Joined via SECONDARY_LOCATOR and ORGANIZATION_ID to validate the locator master data.
• MTL_ITEM_SUB_INVENTORIES: Joined via INVENTORY_ITEM_ID, ORGANIZATION_ID, and SUBINVENTORY_CODE to validate the item-subinventory relationship.
• WIP_DISCRETE_JOBS: Referenced by WIP_DISCRETE_JOBS using PRIMARY_ITEM_ID, ORGANIZATION_ID, and COMPLETION_LOCATOR_ID to validate the job completion locator.
• WIP_REPETITIVE_ITEMS: Referenced similarly using PRIMARY_ITEM_ID, ORGANIZATION_ID, and COMPLETION_LOCATOR_ID for repetitive manufacturing.
• WIP_REQUIREMENT_OPERATIONS: Referenced using INVENTORY_ITEM_ID, ORGANIZATION_ID, and SUPPLY_LOCATOR_ID to validate component supply locators for WIP operations.

These relationships underscore the table's central role in ensuring data integrity for locator assignments across transaction processing and manufacturing execution.