Results for “mtl_secondary_locators”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

MTL_SECONDARY_LOCATORS is an Oracle Inventory (INV) base table that stores item-subinventory-locator assignments. In Oracle EBS 12.1.1 and 12.2.2, the table defines which item numbers are permitted within a given subinventory and, more specifically, within a secondary locator (a storage position such as a row, rack, or bin) inside that subinventory. It is one of the control tables that govern material placement, picking, and consumption behavior across Inventory, Work in Process, and Order Management flows.

Each row represents a validated combination of inventory item, organization, subinventory, and locator. The table also carries attributes used to designate a preferred (primary) locator and to sequence picking activity, making it a functional reference for warehouse execution. Under a Data Vault modeling heuristic derived from its foreign key structure, MTL_SECONDARY_LOCATORS is classified as a hub-leaning entity, since it holds a composite natural business key rather than representing a transactional or purely descriptive satellite record.

Key Information Stored

The composite primary key MTL_SECONDARY_LOCATORS_PK is defined on INVENTORY_ITEM_ID, ORGANIZATION_ID, and SECONDARY_LOCATOR. A unique index, MTL_SECONDARY_LOCATORS_U1, covers the same three columns in the order INVENTORY_ITEM_ID, SECONDARY_LOCATOR, ORGANIZATION_ID, and serves as the documented business-key candidate.

• INVENTORY_ITEM_ID — the item being assigned; part of the primary key and a foreign key to MTL_ITEM_SUB_INVENTORIES (with ORGANIZATION_ID and SUBINVENTORY_CODE).
• ORGANIZATION_ID — the inventory organization in which the assignment is valid.
• SECONDARY_LOCATOR — the locator identifier; a foreign key to MTL_ITEM_LOCATIONS (with ORGANIZATION_ID).
• SUBINVENTORY_CODE — the subinventory portion of the assignment, participating in the MTL_ITEM_SUB_INVENTORIES relationship.
• PRIMARY_LOCATOR_FLAG — indicates whether the locator is the preferred storage location for the item in that subinventory.
• PICKING_ORDER — numeric sequencing used to control the order in which locators are suggested during picking.
• MAXIMUM_QUANTITY — optional capacity or quantity ceiling associated with the assignment.
• STATUS_ID — status reference for the assignment.
• Audit columns — LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, and LAST_UPDATE_LOGIN, standard EBS who-columns.
• Concurrent program columns — REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, and PROGRAM_UPDATE_DATE, recording the last program that touched the row.

Common Use Cases and Queries

The table is queried whenever users need to know where an item may be stored or picked, validate that a locator is enabled for an item-subinventory combination, or retrieve the primary locator associated with an item. Typical reporting includes item-to-locator mapping extracts, pick path analysis by PICKING_ORDER, and capacity checks against MAXIMUM_QUANTITY.

A representative query joins the assignment to the locator and item masters:

• SELECT msl.inventory_item_id, msl.organization_id, msl.subinventory_code, msl.secondary_locator, msl.primary_locator_flag, msl.picking_order FROM mtl_secondary_locators msl WHERE msl.organization_id = :org_id AND msl.inventory_item_id = :item_id;
• Joining MTL_ITEM_LOCATIONS on SECONDARY_LOCATOR and ORGANIZATION_ID yields the physical locator description; joining MTL_ITEM_SUB_INVENTORIES on INVENTORY_ITEM_ID, ORGANIZATION_ID, and SUBINVENTORY_CODE confirms the item-subinventory setup.

Work in Process uses the table to resolve completion and supply locators. WIP_DISCRETE_JOBS and WIP_REPETITIVE_ITEMS reference it through PRIMARY_ITEM_ID, ORGANIZATION_ID, and COMPLETION_LOCATOR_ID, while WIP_REQUIREMENT_OPERATIONS references it through INVENTORY_ITEM_ID, ORGANIZATION_ID, and SUPPLY_LOCATOR_ID. These relationships make the table relevant to production reporting and backflush validation.

Related Objects

• MTL_ITEM_SUB_INVENTORIES — parent setup table; joined on INVENTORY_ITEM_ID, ORGANIZATION_ID, and SUBINVENTORY_CODE.
• MTL_ITEM_LOCATIONS — locator master; joined on SECONDARY_LOCATOR and ORGANIZATION_ID.
• WIP_DISCRETE_JOBS — references the table via PRIMARY_ITEM_ID, ORGANIZATION_ID, and COMPLETION_LOCATOR_ID.
• WIP_REPETITIVE_ITEMS — references the table via PRIMARY_ITEM_ID, ORGANIZATION_ID, and COMPLETION_LOCATOR_ID.
• WIP_REQUIREMENT_OPERATIONS — references the table via INVENTORY_ITEM_ID, ORGANIZATION_ID, and SUPPLY_LOCATOR_ID.
• MTL_ITEM_LOCATIONS_KFV / MTL_SECONDARY_LOCATORS_V — descriptive views and APIs that expose locator assignment data to forms and reports.