Results for “get_locator_fk”

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

Overview

The EDW_MTL_LOCATOR_FKV view is a foreign key view (FKV) delivered in the APPS schema as part of the Oracle Operations Intelligence (OPI) product family within Oracle E-Business Suite 12.1.1 and 12.2.2. Its purpose is to bridge the transactional (OLTP) inventory locator model with the Oracle Business Intelligence / Data Warehouse (EDW) star schema. Specifically, it returns the Data Warehouse foreign key that corresponds to a given OLTP inventory locator, allowing ETL processes, reports, and analytic queries to translate an operational locator identifier into the surrogate key used by the Inventory Location Dimension.

The view is functionally equivalent to the get_locator_fk API, which performs the same OLTP-to-DW key resolution in PL/SQL. Developers may therefore choose between calling the API or querying this view, depending on whether the resolution is required inside procedural code or within a set-based SQL statement. The view is documented as VALID and owned by APPS.

Underlying Base Objects

The view is defined over three OLTP/EDW objects: MTL_ITEM_LOCATIONS_KFV, MTL_PARAMETERS, and EDW_LOCAL_INSTANCE. The documented metadata notes no explicitly listed referenced base objects in the ETRM extract, but the view text confirms these three sources.

MTL_ITEM_LOCATIONS_KFV is the key-flexfield view over the item locations table and supplies the locator identity (inventory location, organization, subinventory, physical location). MTL_PARAMETERS provides the organization code used to build the composite dimension key, and EDW_LOCAL_INSTANCE supplies the instance code for the source installation. The view is a UNION ALL of two branches: the first resolves real locators, and the second produces a synthetic "NULL_LOCATOR" row (with INVENTORY_LOCATION_ID = -1) for every subinventory, ensuring that subinventory-level facts without an assigned locator still map to a dimension member.

Key Columns

• INVENTORY_LOCATION_ID — The OLTP inventory location identifier. The value -1 is used in the UNION ALL branch to represent the NULL locator condition.
• ORGANIZATION_ID — The inventory organization to which the locator or subinventory belongs.
• SUBINVENTORY_CODE — The subinventory name. In the NULL locator branch this is the secondary inventory name.
• LOCATOR_FK — The composite Data Warehouse foreign key. It is constructed by concatenating the subinventory code, organization code, and instance code, separated by hyphens.

Common Use Cases and Queries

The view is typically used in ETL and reporting when translating an operational locator into its warehouse surrogate key, or when joining OLTP inventory facts to the Inventory Location Dimension. A typical query resolving a locator key appears as:

SELECT locator_fk FROM edw_mtl_locator_fkv WHERE inventory_location_id = :p_locator_id AND organization_id = :p_org_id;

To enumerate all locators for an organization:

SELECT subinventory_code, locator_fk FROM edw_mtl_locator_fkv WHERE organization_id = :p_org_id ORDER BY subinventory_code;

Because the view already embeds the instance and organization codes, it can be joined directly to EDW dimension tables without additional lookups. When the requirement is procedural rather than set-based, the equivalent get_locator_fk API should be used to obtain the same foreign key value.