Results for “level_value_pk_column”

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

Overview

MSD_HIERARCHY_LEVELS is a Demand Planning (MSD) configuration table in the Oracle E-Business Suite database. It stores the level associations that define how the levels of a real dimension hierarchy relate to one another. Each row describes a parent-child relationship between two levels of a hierarchy, thereby establishing the navigational and aggregation structure that Demand Planning uses when rolling up or drilling down dimensional data.

In Oracle EBS 12.1.1 and 12.2.2, the table is owned by the MSD schema and is documented as VALID. It is a foundational metadata object rather than a transactional table: it does not hold planning facts or demand figures, but rather the structural blueprint that tells the application how to traverse a dimension. The hierarchy definition itself is stored in MSD_HIERARCHIES, while MSD_HIERARCHY_LEVELS provides the connective fabric between the levels referenced by that definition.

From a Data Vault modeling perspective, the metadata's foreign-key pattern suggests this object behaves as a link. It resolves relationships among hierarchies and levels—associating a hierarchy with a level and with that level's parent level—rather than storing descriptive attributes about a single business entity. This classification is a heuristic derived from the foreign-key structure and is offered as a modeling suggestion, not a declared property of the EBS object.

Key Information Stored

The table contains 21 documented columns. The most significant are the identifiers and the column-name mapping attributes that describe how level values are retrieved at runtime.

• HIERARCHY_ID — Identifies the hierarchy to which the level association belongs; foreign key to MSD_HIERARCHIES.
• LEVEL_ID — Identifies the level within the hierarchy.
• PARENT_LEVEL_ID — Identifies the parent level, defining the parent-child edge in the hierarchy tree.
• RELATIONSHIP_VIEW — Names the view that exposes the relationship, used by the planning engine to query level relationships.
• LEVEL_VALUE_COLUMN / LEVEL_VALUE_PK_COLUMN / LEVEL_VALUE_DESC_COLUMN — Define the source columns for the level's value, primary key, and description.
• PARENT_VALUE_COLUMN / PARENT_VALUE_PK_COLUMN / PARENT_VALUE_DESC_COLUMN — Define the corresponding source columns for the parent level's value, primary key, and description.
• PLAN_TYPE — Associates the level configuration with a plan type, allowing different hierarchies to support different planning scenarios.
• ZD_EDITION_NAME — Editioning column supporting Online Patching in EBS 12.2.2.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN — Standard EBS audit who-columns.
• REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE — Concurrency request and program audit columns.

The primary key is MSD_HIERARCHY_LEVELS_PK, composed of HIERARCHY_ID, LEVEL_ID, and PARENT_LEVEL_ID. A unique index, MSD_HIERARCHY_LEVELS_U1, spans HIERARCHY_ID, LEVEL_ID, PARENT_LEVEL_ID, and ZD_EDITION_NAME, representing the business-key candidate under editioning. The surrogate key concept in this table is the composite of the three identifier columns, while ZD_EDITION_NAME distinguishes editioned copies of the same logical row.

Common Use Cases and Queries

Typical use cases center on hierarchy configuration validation, migration verification, and structural reporting. Administrators and developers often query this table to confirm that a dimension hierarchy has been set up with the correct parent-child relationships before running a demand plan.

• Retrieving all levels for a hierarchy: SELECT LEVEL_ID, PARENT_LEVEL_ID FROM MSD.MSD_HIERARCHY_LEVELS WHERE HIERARCHY_ID = :hierarchy_id;
• Identifying root levels where PARENT_LEVEL_ID is null or self-referencing.
• Reconstructing the full hierarchy path by joining the table to itself on HIERARCHY_ID and PARENT_LEVEL_ID.
• Auditing changes using LAST_UPDATE_DATE, LAST_UPDATED_BY, and REQUEST_ID to trace which concurrent program last modified a level association.
• Verifying the runtime column mappings (LEVEL_VALUE_COLUMN, PARENT_VALUE_COLUMN, RELATIONSHIP_VIEW) when debugging drill-down failures in Demand Planning.
• Reviewing PLAN_TYPE to confirm which planning scenario each hierarchy level configuration supports.

Because parent-child self-references are recursive, hierarchical queries using CONNECT BY or recursive subquery factoring are appropriate for path reconstruction. Edition-aware queries in 12.2.2 should filter or acknowledge ZD_EDITION_NAME.

Related Objects

• MSD_HIERARCHIES — The parent table defining the hierarchy; MSD_HIERARCHY_LEVELS.HIERARCHY_ID references it. This is the primary dependency.
• MSD_HIERARCHY_LEVELS (self-reference) — Hierarchies nest through LEVEL_ID to PARENT_LEVEL_ID, enabling recursive traversal within the same table.
• MSD_HIERARCHY_LEVELS.LEVEL_ID — Referenced linkage to level definitions, providing the identity of each level in the association.
• MSD_HIERARCHY_LEVELS.PARENT_LEVEL_ID — Self-referencing linkage identifying the parent level in the association.
• MSD_DIMENSIONS and related dimension metadata tables — Real dimensions rely on hierarchy level definitions to resolve values at planning time.
• MSD planning engine views — Named through RELATIONSHIP_VIEW and referenced dynamically during plan execution.

The table's role is structural and referential; its integrity depends on consistent HIERARCHY_ID values in MSD_HIERARCHIES and coherent parent-child chains within its own rows.