EBS Reference
MTL_ITEM_LOCATIONS
Schema: INVmasterPer inventory orgThe stock locator master — the physical bin/row/rack positions inside a subinventory, with the locator name held as SEGMENTn flexfield columns
Identity
Module: InventoryInventory-org partitioned (ORGANIZATION_ID)
Notes
Locator ids are unique only within an organization (the key is INVENTORY_LOCATION_ID + ORGANIZATION_ID) — joining on the id alone crosses orgs silently.
What the badges mean
- Schema: INV
- Schema: the Oracle product schema that owns the table (INV, ONT, WSH, PO, BOM, WIP, MRP, MSC, AR, AP, GL, HR, APPLSYS) — tells you which product family the object belongs to, not who can query it.
- master
- Data class: what the table holds — master data, transaction documents, control/configuration, interface/staging, or an APPS-schema view.
- OU-striped (ORG_ID)
- Rows are scoped to an operating unit. A landed extract carries every operating unit’s rows — filter or join on
ORG_ID, and don’t confuse it withORGANIZATION_ID(see the quirks guide). - Per inventory org
- Rows are scoped to an inventory organization (plant or warehouse) via
ORGANIZATION_ID— a different partition from OU-striped tables (see the quirks guide). - Language-striped
- The table carries a
LANGUAGEcolumn (a _TL translation table or FND_LOOKUP_VALUES) — one row per language. Filter to oneLANGUAGEor a join multiplies rows (see the quirks guide). - View
- This is an APPS-schema convenience view, not a physical table. Extract the base tables it joins instead — views can be slow at scale and aren't guaranteed stable across patches.
Structural facts — how the table is partitioned, not a trap by itself
Join & extract hazards — verify before you rely on this
In field listings, the Key chip marks a primary-key field.
Fields
10 fields · 2 key
10 fields.
| # | Field | Description | Type | Flags |
|---|---|---|---|---|
| 1 | INVENTORY_LOCATION_ID | Locator id — unique only within an org; balances and transactions carry it as LOCATOR_ID | NUMBER | Primary-key field |
| 2 | ORGANIZATION_ID | Inventory organization — part of the key | NUMBER | Primary-key field |
| 3 | SUBINVENTORY_CODE | The subinventory the locator sits in | VARCHAR2 | |
| 4 | SEGMENT1 | Stock Locators flexfield segment 1 — the locator name is the configured concatenation (row/rack/bin is a convention, not a rule) | VARCHAR2 | |
| 5 | SEGMENT2 | Stock Locators flexfield segment 2 | VARCHAR2 | |
| 6 | SEGMENT3 | Stock Locators flexfield segment 3 | VARCHAR2 | |
| 7 | ENABLED_FLAG | Whether the locator is active | VARCHAR2 | |
| 8 | DISABLE_DATE | When the locator was disabled — NULL while active | DATE | |
| 9 | PICKING_ORDER | Pick-path sequence for the locator | NUMBER | |
| 10 | STATUS_ID | Material status of the locator (WMS) | NUMBER |
Field provenance: hand-curated. 2 key fields.
Boilerplate SQL
Starting point for reading MTL_ITEM_LOCATIONS on Databricks — real DATE columns need no conversion, and the org anchor is already in place. The optional LAST_UPDATE_DATE watermark is included. Set your Unity Catalog location, schema, and org values below; they’re substituted into the SQL and the copy button.
Query parameters
5 parameters not filled: <catalog>, <schema>, <inventory_org_id>, <INVENTORY_LOCATION_ID>, <watermark>
-- ============================================================
-- Table : MTL_ITEM_LOCATIONS — The stock locator master — the physical bin/row/rack positions inside a subinventory, with the locator name held as SEGMENTn flexfield columns
-- Purpose: Column-selected read of MTL_ITEM_LOCATIONS — auto-generated from field metadata
-- Grain : One row per inventory org (ORGANIZATION_ID) + INVENTORY_LOCATION_ID
-- Notes : Auto-generated skeleton for Oracle EBS R12 data landed in your lakehouse. Dates are real DATE/TIMESTAMP columns — no conversion needed. WHO audit columns omitted (see the quirks guide); the optional LAST_UPDATE_DATE watermark filter supports incremental extracts.
-- ============================================================
SELECT
t.INVENTORY_LOCATION_ID AS "Locator id — unique only within an org; balances and transactions carry it as LOCATOR_ID",
t.ORGANIZATION_ID AS "Inventory organization — part of the key",
t.SUBINVENTORY_CODE AS "The subinventory the locator sits in",
t.SEGMENT1 AS "Stock Locators flexfield segment 1 — the locator name is the configured concatenation (row/rack/bin is a convention, not a rule)", -- flexfield segment: concatenation is configuration-defined — see quirks guide #flexfields
t.SEGMENT2 AS "Stock Locators flexfield segment 2", -- flexfield segment: concatenation is configuration-defined — see quirks guide #flexfields
t.SEGMENT3 AS "Stock Locators flexfield segment 3", -- flexfield segment: concatenation is configuration-defined — see quirks guide #flexfields
t.ENABLED_FLAG AS "Whether the locator is active",
t.DISABLE_DATE AS "When the locator was disabled — NULL while active",
t.PICKING_ORDER AS "Pick-path sequence for the locator",
t.STATUS_ID AS "Material status of the locator (WMS)"
FROM <catalog>.<schema>.MTL_ITEM_LOCATIONS t
WHERE
t.ORGANIZATION_ID = <inventory_org_id> -- inventory org, NOT the operating unit — see quirks guide #two-orgs
-- AND t.INVENTORY_LOCATION_ID = <INVENTORY_LOCATION_ID>
-- AND t.LAST_UPDATE_DATE >= TIMESTAMP '<watermark>' -- WHO watermark, bulk-stamped by batch jobs; see quirks guide #who-columns
ORDER BY t.INVENTORY_LOCATION_ID;Verified September 2026
Relationships
Diagram of 1-hop neighbors — join details below. FND lookup decode and translation edges are highlighted; they’re the joins newcomers most often get wrong.
Join details
ON MTL_ITEM_LOCATIONS.SUBINVENTORY_CODE = MTL_SECONDARY_INVENTORIES.SECONDARY_INVENTORY_NAME AND MTL_ITEM_LOCATIONS.ORGANIZATION_ID = MTL_SECONDARY_INVENTORIES.ORGANIZATION_IDON MTL_ITEM_LOCATIONS.ORGANIZATION_ID = MTL_PARAMETERS.ORGANIZATION_IDON MTL_ONHAND_QUANTITIES_DETAIL.LOCATOR_ID = MTL_ITEM_LOCATIONS.INVENTORY_LOCATION_ID AND MTL_ONHAND_QUANTITIES_DETAIL.ORGANIZATION_ID = MTL_ITEM_LOCATIONS.ORGANIZATION_IDON MTL_MATERIAL_TRANSACTIONS.LOCATOR_ID = MTL_ITEM_LOCATIONS.INVENTORY_LOCATION_ID AND MTL_MATERIAL_TRANSACTIONS.ORGANIZATION_ID = MTL_ITEM_LOCATIONS.ORGANIZATION_ID
More Inventory tables
- MTL_LOT_NUMBERSThe lot master — definition, genealogy (parent lot), status, grade, and the origination/expiration/retest dates for every lot, keyed by item + organization + lot number
- MTL_MATERIAL_TRANSACTIONSThe material transaction ledger — one row per inventory movement or cost update, classified by transaction type, action, and source type, with quantity, date, and the document reference that caused it; the reconciliation backbone for every stock question
- MTL_ONHAND_QUANTITIES_DETAILThe on-hand balance detail — receipt-level slices of stock per item, org, subinventory, locator, lot, and revision, consumed in FIFO order as material issues
- MTL_PARAMETERSInventory organization parameters — one row per inventory org, holding the short org code, the master org it points to, and the org's costing method and locator/lot/serial control defaults; the org list every ORGANIZATION_ID resolves against
- MTL_RESERVATIONSFirm reservations tying a demand source (a sales order line, most commonly) to a supply source (on-hand or expected supply) for an item in an org, optionally pinned down to subinventory, locator, and lot
- MTL_SECONDARY_INVENTORIESThe subinventory master — each row a named section of stock within an organization (stores, staging, WIP, rejects) with its asset/expense nature, tracking and reservability controls, and default accounts