Search Results edw_local_instance




Overview
Explore in AI Chat

Overview

The view EDW_MTL_ILDM_LOCATOR_LCV is an Oracle E-Business Suite (EBS) database object owned by the APPS schema and classified under the OPI (Operations Intelligence) product family. In ETRM metadata, its status is recorded as VALID, and it is explicitly described as the "Source View for Locator Level of Inventory Locator Dimension." This designation places the object within Oracle's Enterprise Data Warehouse (EDW) / Operations Intelligence reporting layer, rather than the transactional inventory model itself.

Functionally, the view flattens and re-keys the locator hierarchy used by inventory reporting. It consolidates three granularities of inventory storage locations — individual stock locators, subinventories, and operating-plant (inventory organization) level pseudo-locators — into a single denormalized result set through the use of UNION ALL. This uniformity is intended to feed a dimension table in the Operations Intelligence dimensional model, allowing downstream ETL and analytics to join at a consistent grain. In EBS 12.1.1 and 12.2.2, the object remains defined in the APPS schema in support of the OPI/EDW data extraction layer. The user's search term, inventory_location_id, corresponds directly to a key column exposed by this view, which is derived from MTL_ITEM_LOCATIONS_KFV.INVENTORY_LOCATION_ID.

Underlying Base Objects

The view text references several transactional and HR tables. Although the ETRM metadata lists no documented base objects, the implementation text identifies the following as the underlying sources:

  • MTL_ITEM_LOCATIONS_KFV — the key flexfield view of item locations, aliased LOCF, supplying the actual stock locators.
  • MTL_PARAMETERS — aliased MP, supplying the inventory organization code and organization ID linkage.
  • MTL_SECONDARY_INVENTORIES — aliased MSI, supplying subinventory-level records.
  • HR_ORGANIZATION_UNITS and HR_ALL_ORGANIZATION_UNITS — supplying operating unit and inventory organization names.
  • EDW_LOCAL_INSTANCE — aliased INST, supplying the instance code used by the EDW layer for multi-instance identification.

The view is a pure reporting construct: it does not store data, and each of its three UNION ALL branches draws from these base tables to produce a merged set of locator records at differing levels of granularity.

Key Columns

  • Inventory Location Identifier — the concatenated key string, INVENTORY_LOCATION_ID||'-'||ORGANIZATION_CODE||'-'||INSTANCE_CODE, exposed as the primary descriptive identifier, plus the raw INVENTORY_LOCATION_ID value used by the transaction tables.
  • INSTANCE_CODE — identifies the EBS instance, enabling cross-instance consolidation within the EDW.
  • Subinventory / Concatenated Segment / Description — SUBDINVENTORY_CODE derived values, CONCATENATED_SEGMENTS, and the locator DESCRIPTION provide human-readable identification.
  • ENABLED_FLAG — indicates whether the locator is active.
  • Level Discriminator — the literal 'LOCATOR' or 'ALL LOCATORS' distinguishes a specific locator record from a subinventory- or plant-level pseudo-record.
  • ORGANIZATION_ID / ORGANIZATION_CODE — the inventory organization to which the locator belongs.
  • CREATION_DATE / LAST_UPDATE_DATE — audit columns inherited from the base rows, used for incremental ETL extraction.

Common Use Cases and Queries

The view is typically consumed by Operations Intelligence ETL programs loading the Inventory Locator dimension, or by ad hoc reporting that requires a single, consistent key across locator, subinventory, and plant granularities. A representative query filtering on the searched identifier is:

  • SELECT inventory_location_id, concatenated_segments, organization_id, enabled_flag FROM apps.edw_mtl_ildm_locator_lcv WHERE inventory_location_id = :p_location_id;
  • SELECT organization_code, sub_inventory_code, enabled_flag FROM apps.edw_mtl_ildm_locator_lcv WHERE enabled_flag = 'Y' ORDER BY organization_code;
  • SELECT DISTINCT instance_code, organization_id FROM apps.edw_mtl_ildm_locator_lcv;

Because the view is defined over key flexfield and HR views, query performance is best supported by filtering on ORGANIZATION_ID or INVENTORY_LOCATION_ID, which map directly to indexed columns on the base tables.