Search Results invl_locator_pk




Overview
Explore in AI Chat

Overview

The EDW_MTL_INVENTORY_LOC_M table is an Enterprise Data Warehouse (EDW) materialized staging object owned by the OPI schema within the Oracle E-Business Suite 12.1.1 and 12.2.2 environments. It belongs to the BIS (Business Intelligence System) product family, which supplies the extract, transform, and load infrastructure used by Oracle's embedded analytics and custom data warehouse solutions. This table consolidates inventory locator information — the physical positions within a subinventory where material is stored — and enriches it with the surrounding organizational hierarchy: operating unit, legal entity/company, plant, and stock room context. It is a denormalized reference table used primarily for dimensional lookups in inventory, costing, and supply chain reporting.

The ETRM metadata classifies this object heuristically as standalone under its Data Vault modeling suggestion. This implies the table functions as a self-contained reference or dimension, not as a raw transaction or association entity. Note that the documented primary key, EDW_MTL_INVENTORY_LOC_M_P, is defined on INVL_LOCATOR_PK_KEY, confirming this is a keyed dimensional structure rather than a pure Data Vault hub, link, or satellite.

Key Information Stored

The table exposes 86 columns, organized around several business contexts. The most significant columns are:

Each context also carries a repeating set of five USER_ATTRIBUTE columns (INVL, OU, PLNT, SUBI, PORG, PCMP), providing extensibility. The all_name and all_pk / all_pk_key columns supply a consolidated "all" level for hierarchy roll-ups.

Common Use Cases and Queries

This table is used predominantly as a lookup dimension in inventory and warehouse reporting. Typical scenarios include stock-on-hand reports by locator, locator utilization analysis, and inventory cycle count planning. Because it carries the full organizational hierarchy (operating unit to plant to subinventory to locator), it is frequently joined to transaction and balance facts without requiring additional lookups.

A representative query retrieves active locators for a given subinventory:

  • SELECT INVL_LOCATOR_NAME, INVL_DESCRIPTION FROM OPI.EDW_MTL_INVENTORY_LOC_M WHERE SUBI_STOCK_ROOM_PK = :stockroom_pk AND INVL_ENABLED_FLAG = 'Y';

Another common pattern joins the table to material balance facts using the surrogate key and filters by organization:

  • SELECT l.INVL_LOCATOR_NAME, SUM(b.QTY) FROM OPI.EDW_MTL_INVENTORY_LOC_M l JOIN EDW_INV_ONHAND b ON b.INVL_LOCATOR_PK_KEY = l.INVL_LOCATOR_PK_KEY WHERE l.PLNT_ORGANIZATION_CODE = :org GROUP BY l.INVL_LOCATOR_NAME;

Reporting use cases include locator master data extracts, cross-instance locator reconciliation using INVL_INSTANCE_CODE, and cost-method-aware inventory valuation filtered by OU_PRIMARY_CST_MTHD.

Related Objects

Although the metadata classifies this table as standalone with no documented foreign key relationships, it operates within the EDW inventory star schema and is joined on its surrogate key by fact and dimension tables. The following are the most significant related objects: