Search Results child_lvltbl_name

Overview

The view EDW_LEVEL_RELATIONS_MD_V is an Oracle E-Business Suite dictionary object owned by the APPS schema and classified under the BIS – Applications BIS product family. It forms part of the Oracle Enterprise Data Warehouse (EDW) metadata layer that supports the Business Intelligence System (BIS) and Daily Business Intelligence (DBI) reporting stacks. Its purpose is to expose the relationships that exist between levels within an analytic dimension hierarchy — the parent/child level pairings that define how data aggregates and drills across a star-schema dimensional model.

The object is a metadata view rather than a transactional one. It does not hold data itself; instead it projects and joins records drawn from other dictionary views. Because the name is suffixed _MD_V (metadata view), it is intended for use by the EDW metadata engine, ETL configuration utilities, and reporting developers who need to resolve the structural relationships between hierarchy levels at build time. The user search term "cmplevel_v" is directly relevant: CMPLEVEL_V is one of the principal source views that this object queries.

Underlying Base Objects

According to the documented view text, EDW_LEVEL_RELATIONS_MD_V is defined as a UNION ALL query over several underlying dictionary views. The primary objects referenced in the first branch are:

• CMPWBDIMENSION_V – source of dimension identity and name.
• CMPITEMHIERARCHY_V – source of hierarchy identity, linked to its owning dimension.
• CMPLEVELRELATIONSHIP_V – the parent/child level relationship records.
• CMPLEVEL_V – referenced twice (aliased PLVL for the parent level and CLVL for the child level) to resolve level names and prefixes.
• EDW_TABLES_MD_V – referenced twice (aliased PTBL and CTBL) to resolve the physical level tables (<LEVEL>_LTC) for parent and child.
• CMPUNIQUEKEY_V and CMPFOREIGNKEY_V – to resolve the unique-key and foreign-key definitions that link the two level tables.

The joins rely on outer-join syntax (the legacy (+) operator), meaning that level rows without a corresponding table, unique key, or foreign key are still surfaced. The second UNION ALL branch returns the same relationship rows but substitutes NULL values for the table, key, and foreign-key columns, effectively representing relationships that have no physical key-level mapping. The ETRM metadata notes that no base tables are separately documented for this object, reinforcing that it is a metadata projection built entirely on other views.

Key Columns

The view exposes an extensive column set describing each level relationship:

• REL_ID – the underlying level-relationship element identifier (LVLREL.ELEMENTID).
• DIM_ID, DIM_NAME – the owning dimension identifier and name.
• HIER_ID, HIER_NAME – the hierarchy identifier and name.
• PARENT_LVL_ID, PARENT_LVL_NAME, PARENT_LVL_PREFIX – the parent level's identity and prefix.
• PARENT_LVLTBL_ID, PARENT_LVLTBL_NAME – the parent physical table (<level name>_LTC).
• CHILD_LVL_ID, CHILD_LVL_NAME, CHILD_LVL_PREFIX – the child level's identity and prefix.
• CHILD_LVLTBL_ID, CHILD_LVLTBL_NAME – the child physical table.
• UK_ID, UK_NAME – the unique key definition on the parent level table.
• FK_ID, FK_NAME – the foreign key definition on the child level table.

Together these columns allow a consumer to reconstruct not only the logical level hierarchy but also the physical table and key linkage used for aggregation and drill-down.

Common Use Cases and Queries

This view is typically queried during warehouse build and validation to confirm that level relationships are correctly defined and that the corresponding unique/foreign key mappings exist. A representative query:

• SELECT DIM_NAME, HIER_NAME, PARENT_LVL_NAME, CHILD_LVL_NAME FROM APPS.EDW_LEVEL_RELATIONS_MD_V WHERE UK_ID IS NULL OR FK_ID IS NULL; — to find level relationships missing physical key mappings.
• SELECT REL_ID, DIM_ID, HIER_ID, PARENT_LVL_NAME, CHILD_LVL_NAME FROM APPS.EDW_LEVEL_RELATIONS_MD_V ORDER BY HIER_ID; — to list all parent/child level pairs per hierarchy.
• Join back to CMPLEVEL_V on PARENT_LVL_ID or CHILD_LVL_ID to enrich with additional level attributes.

Because the object is metadata, it should be used for configuration and diagnostics, not for operational reporting on business data.