Results for “msd_hierarchy_levels_v”

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

Overview

MSD_HIERARCHY_LEVELS_V is an Oracle E-Business Suite 12.1.1 / 12.2.2 view owned by the APPS schema in the MSD – Demand Planning product. It exposes the level associations that exist within a hierarchy in the Real Dimension realm. In dimensional modeling terms, a hierarchy is a roll-up structure (for example, Item to Category to Family), and each level within that structure is linked to a parent level. This view surfaces those parent-child level relationships together with descriptive attributes drawn from the level and hierarchy views.

The view is read-only and is intended for reporting, integration, and metadata inspection rather than transactional maintenance. Because it resolves level and hierarchy identifiers into names, type codes, and owning dimension information, it is a convenient access point for BI Publisher reports, custom concurrent programs, and interfaces that must understand the shape of a demand planning hierarchy without navigating the underlying normalized tables directly.

Underlying Base Objects

The view is defined over four documented base objects:

• MSD_HIERARCHY_LEVELS (SYNONYM) — the driving table holding one row per level association within a hierarchy.
• MSD_HIERARCHIES_V (VIEW) — supplies the hierarchy name and the owning dimension code and description.
• MSD_LEVELS_V (VIEW) — joined twice, once as MLV1 for the child level and once as MLV2 for the parent level, supplying level names and level type descriptions.

The join predicates are MLV1.LEVEL_ID = MHL.LEVEL_ID, MLV2.LEVEL_ID = MHL.PARENT_LEVEL_ID, and MHV.HIERARCHY_ID = MHL.HIERARCHY_ID. This double join to MSD_LEVELS_V is what produces the paired level and parent-level attribute columns in each output row.

Key Columns

• HIERARCHY_ID / HIERARCHY_NAME — identifier and label of the hierarchy that owns the level association.
• LEVEL_ID / LEVEL_NAME — the child level in the association.
• PARENT_LEVEL_ID / PARENT_LEVEL_NAME — the parent level above the child.
• LEVEL_TYPE_CODE / LEVEL_TYPE and the corresponding parent columns — classify each level (for example, the level type used in the Real Dimension realm).
• OWNING_DIMENSION_CODE / OWNING_DIMENSION — the real dimension to which the hierarchy belongs.
• RELATIONSHIP_VIEW — the name of the database view that materializes the level-value relationships, used to resolve members at that level.
• LEVEL_VALUE_COLUMN, LEVEL_VALUE_PK_COLUMN, PARENT_VALUE_COLUMN, PARENT_VALUE_PK_COLUMN — the physical column names in the relationship view that carry the level and parent values and their primary keys.
• Audit columns — LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN, REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE, and ROWID.

Common Use Cases and Queries

Typical uses include discovering the relationship view behind a hierarchy level, validating parent-child level mappings before loading plan data, and driving dynamic SQL that reads member values from the identified relationship view. To list all level associations for a named hierarchy:

SELECT hierarchy_name, level_name, parent_level_name, relationship_view
FROM apps.msd_hierarchy_levels_v
WHERE hierarchy_name = :p_hierarchy_name
ORDER BY level_id;

To locate the value and key columns needed to build a member query:

SELECT level_name, level_value_column, level_value_pk_column,
parent_value_column, parent_value_pk_column
FROM apps.msd_hierarchy_levels_v
WHERE owning_dimension_code = :p_dimension_code;

To enumerate hierarchies and their owning dimensions:

SELECT DISTINCT hierarchy_id, hierarchy_name, owning_dimension
FROM apps.msd_hierarchy_levels_v;

Because the relationship view name and column names are exposed as data, applications can construct and execute dynamic SQL against the appropriate Real Dimension relationship view, making this object a practical metadata bridge between hierarchy definition and hierarchy member data.