Results for “msd_level_values_v”

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

Overview

MSD_LEVEL_VALUES_V is an APPS-owned database view in the Oracle E-Business Suite Demand Planning (MSD) module. It exposes the level value associations that link each level value in a planning hierarchy to its corresponding parent level value, together with the system-generated primary keys (LEVEL_PK) assigned to those members. Per the ETRM metadata, the object is defined with status VALID in releases 12.1.1 and 12.2.2.

The defining characteristic of this view is that it is not stripped by demand plan Id. Most Demand Planning queries are filtered by a specific plan identifier so that hierarchies remain plan-scoped; MSD_LEVEL_VALUES_V intentionally omits that predicate so that the full set of parent/child level-value relationships across all plans and instances can be interrogated. This makes the view useful for cross-plan analysis, data-conversion validation, and integration extracts that need the superset of level-value associations rather than a single plan's slice. Because it surfaces both the member key (LEVEL_PK) and the parent member key (PARENT_LEVEL_PK), it is the primary reference for reconstructing the tree structure of a hierarchy, which the flattened MSD_LEVEL_VALUES table alone does not directly express.

Underlying Base Objects

The view is constructed over three synonyms in the APPS schema, with additional dependencies on two packages. The documented referenced base objects are: MSD_LEVELS (SYNONYM), MSD_LEVEL_ASSOCIATIONS (SYNONYM), MSD_LEVEL_VALUES (SYNONYM), MSD_COMMON_UTILITIES (PACKAGE), and MSD_SR_UTIL (PACKAGE).

The view text self-joins MSD_LEVEL_VALUES twice — once aliased MLV1 for the child level value and once aliased MLV2 for the parent level value — and joins both to MSD_LEVEL_ASSOCIATIONS (aliased MLA). The join predicates are:

• MLV1.LEVEL_ID = MLA.LEVEL_ID AND MLV1.INSTANCE = MLA.INSTANCE AND MLV1.SR_LEVEL_PK = MLA.SR_LEVEL_PK — resolving the child member.
• MLV2.LEVEL_ID = MLA.PARENT_LEVEL_ID AND MLV2.INSTANCE = MLA.INSTANCE AND MLV2.SR_LEVEL_PK = MLA.SR_PARENT_LEVEL_PK — resolving the parent member.

MSD_LEVEL_ASSOCIATIONS is therefore the driving relationship table that maps a source level PK to its surrogate parent level PK within an instance. MSD_LEVELS supplies the level definitions referenced by LEVEL_ID, while MSD_COMMON_UTILITIES and MSD_SR_UTIL provide the standard MSD utilities relied upon in the Demand Planning data model. The surrogate-key pairing (SR_LEVEL_PK / SR_PARENT_LEVEL_PK) is significant: matching occurs on the internal system keys rather than on the business level values, which is why the view can expose reliable parent-child chains even when level values are shared across levels.

Key Columns

The view projects eighteen columns. The principal identity and hierarchy columns are:

• LEVEL_ID — identifier of the level to which the member belongs (appears twice, once for the child member and once for the parent member).
• LEVEL_VALUE — the business value of the level member; duplicated for child and parent.
• LEVEL_PK — the system-generated primary key for the level value. The child instance is the searched key; the second occurrence is the parent member's key.
• PARENT_LEVEL_ID, PARENT_LEVEL_VALUE, PARENT_LEVEL_PK — the corresponding identifier, value, and primary key of the parent member in the hierarchy.

Descriptive and auditing columns include ATTRIBUTE1 through ATTRIBUTE5 (flexible descriptive segments sourced from the child level value row), LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, and LAST_UPDATE_LOGIN. Two refresh-integrity columns are also exposed: LAST_REFRESH_NUM and CREATED_BY_REFRESH_NUM, each computed using the GREATEST function across the level value and association rows (GREATEST(MLV1.LAST_REFRESH_NUM, MLA.LAST_REFRESH_NUM) and GREATEST(MLV1.CREATED_BY_REFRESH_NUM, MLA.CREATED_BY_REFRESH_NUM)). These columns allow downstream consumers performing incremental extracts to detect the most recent refresh watermark affecting either side of the association, avoiding stale reads during borderline refresh windows.

Common Use Cases and Queries

Typical scenarios include validating hierarchy integrity after data conversion or a collection run, extracting the complete level-value association set for a planning data mart, and tracing a member's ancestry for reporting. A basic query listing all associations is:

• SELECT LEVEL_ID, LEVEL_VALUE, LEVEL_PK, PARENT_LEVEL_ID, PARENT_LEVEL_VALUE, PARENT_LEVEL_PK FROM APPS.MSD_LEVEL_VALUES_V;
• To locate a specific member and its parent by primary key: SELECT LEVEL_VALUE, PARENT_LEVEL_VALUE FROM APPS.MSD_LEVEL_VALUES_V WHERE LEVEL_PK = :p_level_pk;
• To find all children of a given parent: SELECT LEVEL_VALUE, LEVEL_PK FROM APPS.MSD_LEVEL_VALUES_V WHERE PARENT_LEVEL_PK = :p_parent_pk;
• For incremental refresh checks: SELECT LEVEL_PK, LAST_REFRESH_NUM FROM APPS.MSD_LEVEL_VALUES_V WHERE LAST_REFRESH_NUM > :last_run;

Because the view is not plan-filtered, callers that require plan-specific results must impose their own filtering logic against the underlying level and plan structures, or join to a plan-scoped table, to avoid returning associations belonging to other plans or instances.