Results for “mtl_item_locations”

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

Overview

MTL_ITEM_LOCATIONS is the Inventory (INV) master table that stores the definitions for stock locators within Oracle E-Business Suite Release 12.1.1 and 12.2.2. A stock locator represents a physical storage position inside a subinventory — a bin, aisle, rack, shelf, or staging position — and is the finest level of inventory granularity tracked by the application. The table is owned by the INV schema and holds 94 documented columns. Each row defines one locator for one organization, and is uniquely identified by the combination of INVENTORY_LOCATION_ID and ORGANIZATION_ID.

From a dimensional modeling standpoint, the ETRM heuristic classifies MTL_ITEM_LOCATIONS as hub-leaning. In Data Vault terms, it behaves as a hub: it carries a stable surrogate key (INVENTORY_LOCATION_ID) that is referenced by a very large number of dependent tables throughout Inventory, WIP, Receiving, Warehouse Management, and Process Manufacturing, while its own foreign keys point outward to a small set of reference masters. This makes it a candidate for a hub design in a Data Vault model, with attributes such as capacity, status, and flags distributed to satellites.

Key Information Stored

The primary key MTL_ITEM_LOCATIONS_PK is composed of INVENTORY_LOCATION_ID and ORGANIZATION_ID, where INVENTORY_LOCATION_ID is the surrogate locator identifier. A second unique index, MTL_ITEM_LOCATIONS_U1, also covers (INVENTORY_LOCATION_ID, ORGANIZATION_ID), reinforcing that this pair is the business-key candidate for the locator record.

• INVENTORY_LOCATION_ID — the system-generated locator identifier; the principal join key across all transaction tables.
• ORGANIZATION_ID — the inventory organization to which the locator belongs; foreign key to MTL_PARAMETERS.
• SUBINVENTORY_CODE — the subinventory in which the locator resides; foreign key to MTL_SECONDARY_INVENTORIES.
• INVENTORY_LOCATION_TYPE — classifies the locator (for example storage, receiving, or shipping usage type).
• STATUS_ID — the material status assigned to the locator; foreign key to MTL_MATERIAL_STATUSES_B.
• INVENTORY_ACCOUNT_ID — the general ledger account used for inventory valuation at the locator; foreign key to GL_CODE_COMBINATIONS.
• ENABLED_FLAG, START_DATE_ACTIVE, END_DATE_ACTIVE, DISABLE_DATE — control whether and when the locator is active.
• PHYSICAL_LOCATION_CODE and PHYSICAL_LOCATION_ID — the human-readable label and surrogate reference for the physical position.
• SEGMENT1 through SEGMENT20 and SUMMARY_FLAG — key flexfield segments supporting locator naming or descriptive flexibility.
• Capacity and geometry attributes: MAX_WEIGHT, MAX_CUBIC_AREA, LENGTH, WIDTH, HEIGHT, LOCATION_MAXIMUM_UNITS, and the AVAILABLE_*/CURRENT_* /SUGGESTED_* capacity columns.
• LOCATION_CURRENT_UNITS, LOCATION_AVAILABLE_UNITS, CURRENT_WEIGHT, AVAILABLE_WEIGHT, CURRENT_CUBIC_AREA, AVAILABLE_CUBIC_AREA — volumetric and weight utilization tracking.
• MIXED_ITEMS_FLAG, EMPTY_FLAG, AVAILABILITY_TYPE, INVENTORY_ATP_CODE, RESERVABLE_TYPE — behavior flags governing stock mixing, ATP, and reservations.
• PICKING_ORDER and DROPPING_ORDER — sequencing used by pick and put-away operations.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1 through ATTRIBUTE15 — the standard Oracle EBS descriptive flexfield columns.
• WHO audit columns (CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN) plus REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, and PROGRAM_UPDATE_DATE.

Common Use Cases and Queries

The most common use case is resolving a locator ID from a transaction record back to its descriptive name and subinventory. Transaction tables such as MTL_MATERIAL_TRANSACTIONS, MTL_ONHAND_QUANTITIES, MTL_RESERVATIONS, and RCV_TRANSACTIONS all carry LOCATOR_ID, and virtually every locator-level inventory report joins back to MTL_ITEM_LOCATIONS.

• Locator lookup for on-hand reporting — join MTL_ONHAND_QUANTITIES.LOCATOR_ID to INVENTORY_LOCATION_ID and ORGANIZATION_ID to list quantities by bin.
• Listing active stock locators in a subinventory — filter on ORGANIZATION_ID, SUBINVENTORY_CODE, and ENABLED_FLAG = 'Y'.
• Capacity and utilization reporting — query LOCATION_CURRENT_UNITS versus LOCATION_MAXIMUM_UNITS, or CURRENT_WEIGHT versus MAX_WEIGHT, to identify overfull or empty bins using EMPTY_FLAG.
• Material status enforcement — join STATUS_ID to MTL_MATERIAL_STATUSES_B to identify locators restricted to quarantined or rejected stock.
• Audit and receiving analysis — join MTL_MATERIAL_TRANSACTIONS or RCV_TRANSACTIONS on LOCATOR_ID to trace put-away and movement activity per locator.
• Attribute and flexfield reporting — use ATTRIBUTE_CATEGORY/ATTRIBUTE1–15 and SEGMENT1–20 for site-specific locator classifications.

Related Objects

MTL_ITEM_LOCATIONS is referenced by an extensive set of dependent tables, confirming its hub role. The most significant relationships include:

• MTL_MATERIAL_TRANSACTIONS (LOCATOR_ID) and MTL_MATERIAL_TRANSACTIONS_TEMP (LOCATOR_ID, TRANSFER_TO_LOCATION) — all inventory transaction history and interface staging.
• MTL_ONHAND_QUANTITIES (LOCATOR_ID) and MTL_ONHAND_QUANTITY_TEMP (LOCATOR_ID) — current on-hand balances held at each locator.
• MTL_RESERVATIONS (LOCATOR_ID) plus MTL_RESERVATIONS_INTERFACE (LOCATOR_ID, TO_LOCATOR_ID) — reservation and demand reservation activity.
• MTL_SECONDARY_INVENTORIES (SUBINVENTORY_CODE, ORGANIZATION_ID) — the parent subinventory definition referenced by each locator.
• MTL_PARAMETERS (ORGANIZATION_ID) — the inventory organization master.
• MTL_MATERIAL_STATUSES_B (STATUS_ID) — material status definitions available for locators.
• GL_CODE_COMBINATIONS (INVENTORY_ACCOUNT_ID) — the inventory valuation account.
• WIP_DISCRETE_JOBS and WIP_REPETITIVE_ITEMS (COMPLETION_LOCATOR_ID) — work order completion locators.
• RCV_TRANSACTIONS (LOCATOR_ID) and RCV_TRANSACTIONS_INTERFACE (LOCATOR_ID) — receiving delivery locators.
• MTL_CYCLE_COUNT_ENTRIES and MTL_PHYSICAL_INVENTORY_TAGS (LOCATOR_ID) — cycle count and physical inventory counting by locator.