Search Results sub_inventory

Overview

EDW_MTL_ILDM_SUB_INVENTORY_LCV is an Oracle E-Business Suite (EBS) source view owned by the APPS schema and published under the Operations Intelligence (OPI) product family. Its documented purpose is to serve as the source view for the Sub-Inventory Organization level of the Inventory Locator Dimension. In practical terms, the view exposes a flattened, reporting-ready list of sub-inventories (secondary inventories) together with the inventory organization, operating unit, and local instance context that they belong to. It is part of the wider EDW (Enterprise Data Warehouse) family of views used by Oracle's operational reporting and analytics stack, including the Inventory Locator Dimension that drives inventory analytics. The view is defined as the union of several SELECT statements, each contributing a different level or type of sub-inventory entity, which makes it a single point of access for consumers that need a unified, hierarchical view of sub-inventory data.

Underlying Base Objects

The view text references several underlying EBS tables and related views that supply its data. The primary base table is MTL_SECONDARY_INVENTORIES (aliased MSI), which stores individual sub-inventory definitions. MTL_PARAMETERS (MP) supplies the organization code for the owning inventory organization, while HR_ALL_ORGANIZATION_UNITS (joined as BG and ORG) provides the organization/operating unit hierarchy, with ORG.NAME used for the descriptive label. HR_ORGANIZATION_INFORMATION and FND_PRODUCT_GROUPS appear in the second UNION branch, where they support the "ALL SUB INVENTORIES" plant-level record. EDW_LOCAL_INSTANCE (INST) supplies the local instance code, and EDW_MTL_ILDM_OU_LCV (OU) is unioned in to contribute operating-unit-level records. A fourth branch references operating-unit and OPM process-manufacturing plant data. Although the documented metadata lists no referenced base objects for the 12.2.2 ETRM entry, the view text itself makes clear that these tables and companion EDW views are the physical sources. The view is valid and active in the APPS schema in both EBS 12.1.1 and 12.2.2, with identical or near-identical definitions across the two releases.

Key Columns

The view exposes a positional set of columns (the SELECT lists do not assign explicit aliases). The first column is the primary key and label, formed by concatenating the secondary inventory name, the organization code, and the instance code, or by using the operating-unit/plant codes in the alternate UNION branches. The second column provides the parent organization key, built as organization code joined to instance code and defaulted to 'NA_EDW' when null. A third column carries the instance code. The fourth column provides a display name in the form "SUB_INVENTORY(ORGANIZATION_CODE)" or the plain organization code for plant-level rows. The fifth column holds the sub-inventory description. The sixth column is a type discriminator whose literal values include 'SUB_INVENTORY' and 'ALL SUB INVENTORIES'. Subsequent columns carry creation and last-update dates for change detection in ETL processes, along with five trailing columns (frequently NULL for sub-inventory rows) that correspond to USER_ATTRIBUTE1 through USER_ATTRIBUTE5 populated by the operating-unit branch. This structure allows consumers to treat sub-inventory, plant-level, and operating-unit-level records uniformly.

Common Use Cases and Queries

The primary use case is feeding the Inventory Locator Dimension in the OPI/EDW analytics model, where the view supplies the sub-inventory level of the inventory hierarchy. Typical work includes:

• Populating dimension tables for inventory reporting and dashboards.
• Validating that all sub-inventories are captured per organization.
• Reconciling sub-inventory metadata against MTL_SECONDARY_INVENTORIES during ETL loads.
• Filtering by type discriminator to isolate material sub-inventories from aggregate "ALL SUB INVENTORIES" placeholders.

A representative query is:
SELECT * FROM APPS.EDW_MTL_ILDM_SUB_INV_LCV WHERE column6 = 'SUB_INVENTORY';
Because columns are unnamed in the definition, callers typically reference them by position or wrap the view in an inline view with explicit aliases. For example:
SELECT sub_inventory_key, org_key, instance_code, display_name FROM (SELECT column1 AS sub_inventory_key, column2 AS org_key, column3 AS instance_code, column4 AS display_name, column6 AS record_type FROM APPS.EDW_MTL_ILDM_SUB_INV_LCV) WHERE record_type = 'SUB_INVENTORY';
Change-detection extracts commonly use the creation and last-update columns to identify newly created or modified sub-inventories between incremental runs.