EBS Reference

MTL_ITEM_LOCATIONS

Schema: INVmasterPer inventory org

The stock locator master — the physical bin/row/rack positions inside a subinventory, with the locator name held as SEGMENTn flexfield columns

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

master: Data class: what the table holds — master data, transaction documents, control/configuration, interface/staging, or an APPS-schema view.

Fields

10 fields · 2 key

Table fields: position, field name, description, data type, and flags. 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	Key
2	ORGANIZATION_ID	Inventory organization — part of the key	NUMBER	Key
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	Flex segment
5	SEGMENT2	Stock Locators flexfield segment 2	VARCHAR2	Flex segment
6	SEGMENT3	Stock Locators flexfield segment 3	VARCHAR2	Flex segment
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_LOCATIONSon Databricks — real DATE columns need no conversion, and the org anchor and the LAST_UPDATE_DATE watermark are already in place. Set your Unity Catalog location, schema, and org values below; they’re substituted into the SQL and the copy button.

Query parameters

CatalogSchemaInventory org (ORGANIZATION_ID)Locator IDwatermark

Include optional filters (2)

-- ============================================================
-- 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;

5 parameters not filled: <catalog>, <schema>, <inventory_org_id>, <INVENTORY_LOCATION_ID>, <watermark>

Relationships

1-hop neighbors — click a table to navigate there. FND lookup decode and translation edges are highlighted; they’re the joins newcomers most often get wrong.

Join details

MTL_ITEM_LOCATIONSMTL_SECONDARY_INVENTORIESforeign key · N:1
ON MTL_ITEM_LOCATIONS.SUBINVENTORY_CODE = MTL_SECONDARY_INVENTORIES.SECONDARY_INVENTORY_NAME AND MTL_ITEM_LOCATIONS.ORGANIZATION_ID = MTL_SECONDARY_INVENTORIES.ORGANIZATION_ID
MTL_ITEM_LOCATIONSMTL_PARAMETERSforeign key · N:1
ON MTL_ITEM_LOCATIONS.ORGANIZATION_ID = MTL_PARAMETERS.ORGANIZATION_ID
MTL_ONHAND_QUANTITIES_DETAILMTL_ITEM_LOCATIONSforeign key · N:1
ON MTL_ONHAND_QUANTITIES_DETAIL.LOCATOR_ID = MTL_ITEM_LOCATIONS.INVENTORY_LOCATION_ID AND MTL_ONHAND_QUANTITIES_DETAIL.ORGANIZATION_ID = MTL_ITEM_LOCATIONS.ORGANIZATION_ID
MTL_MATERIAL_TRANSACTIONSMTL_ITEM_LOCATIONSforeign key · N:1
ON MTL_MATERIAL_TRANSACTIONS.LOCATOR_ID = MTL_ITEM_LOCATIONS.INVENTORY_LOCATION_ID AND MTL_MATERIAL_TRANSACTIONS.ORGANIZATION_ID = MTL_ITEM_LOCATIONS.ORGANIZATION_ID

Browse more Inventorytables →