Search Results lvl_prefix

Overview

APPS.EDW_HIERARCHY_LEVEL_MD_V is a reporting view in the Oracle E-Business Suite (EBS) 12.1.1 and 12.2.2 data model. It exposes metadata that describes the levels defined within the analytical hierarchies used by the Enterprise Data Warehouse (EDW) / Business Intelligence (BI) components of EBS. The view presents one row per hierarchy level, pairing each level with its owning dimension and hierarchy so that downstream reporting, ETL, and integration processes can resolve the structural layout of a hierarchy without directly querying the base metadata object.

The object is defined as a straightforward projection over a single base table and does not itself perform joins, aggregations, or filtering. Its purpose is therefore to provide a stable, named interface through which hierarchical level metadata is consumed by EDW and BI tooling, and to abstract the physical base table from external consumers. In practice, the view is most relevant when tracing how a dimension such as a product, customer, or account hierarchy is decomposed into ordered levels.

Underlying Base Objects

The ETRM documentation records no referenced base objects for this view; however, the documented view text identifies the sole source object explicitly. The view is defined as:

• SELECT "DIM_ID","DIM_NAME","HIER_ID","HIER_NAME","LVL_ID","LVL_NAME","LVL_PREFIX","PARENT_LVL_ID" FROM EDW_HIERARCHY_LEVEL_MD

Consequently, all columns exposed by EDW_HIERARCHY_LEVEL_MD_V are derived directly, one-to-one, from EDW_HIERARCHY_LEVEL_MD. There is no transformation, calculated column, or additional join involved. Any insert, update, or delete performed against the base table is immediately reflected through the view, and the view itself is not independently updatable in the sense of owning data. Referential integrity and row uniqueness are governed entirely by constraints and data governance applied to EDW_HIERARCHY_LEVEL_MD.

Key Columns

The view exposes eight metadata columns describing dimension, hierarchy, and level structure:

• DIM_ID — Numeric identifier of the dimension to which the hierarchy level belongs.
• DIM_NAME — Descriptive name of the dimension.
• HIER_ID — Numeric identifier of the hierarchy within the dimension.
• HIER_NAME — Descriptive name of the hierarchy.
• LVL_ID — Numeric identifier of the individual level within the hierarchy.
• LVL_NAME — Descriptive name assigned to the level.
• LVL_PREFIX — Prefix token applied to the level, typically used when constructing qualified column or member references in BI metadata.
• PARENT_LVL_ID — Identifier of the parent level of the current level, establishing the parent-child relationship between levels in the hierarchy. This is the column most commonly searched for reporting on level ancestry and hierarchy depth.

Common Use Cases and Queries

Typical uses include generating hierarchy documentation for an EDW load, validating level configuration before a data refresh, and resolving parent-child level relationships for report definition. A sample query listing all levels for a given hierarchy follows:

• SELECT dim_name, hier_name, lvl_id, lvl_name, parent_lvl_id FROM apps.edw_hierarchy_level_md_v WHERE hier_id = :p_hier_id ORDER BY lvl_id;
• SELECT lvl_name, parent_lvl_id FROM apps.edw_hierarchy_level_md_v WHERE dim_name = :p_dim_name AND parent_lvl_id IS NULL;

Because the view is a direct projection of EDW_HIERARCHY_LEVEL_MD, query performance and access controls are inherited from the base object. Readers should apply the standard APPS schema privileges when querying the view.