Results for “parent_level_value”

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

Overview

The MSD_ST_LEVEL_VALUES table is a staging object within the Oracle EBS MSD – Demand Planning module. It stores level information used during the demand planning data loading and processing cycle, capturing both the attribute definitions and their corresponding values. Unlike many staging tables in the Demand Planning schema, this table is not stripped by Demand Plan Id, meaning its records persist across plan identifiers and are scoped instead through the SR/INSTANCE mechanisms and the LEVEL_ID relationship.

From a heuristic Data Vault modeling perspective, the metadata classifies MSD_ST_LEVEL_VALUES as standalone. This suggests the table behaves more like a reference or staging structure than a fully normalized hub-and-satellite construct. A modeling suggestion would be to treat LEVEL_ID plus INSTANCE as a candidate composite business key, with the descriptive and audit columns forming satellite-style attributes. Because the table is a transient staging surface, its primary dependencies are on MSD_LEVELS rather than on transactional planning fact tables.

Key Information Stored

The table contains 32 documented columns, of which the following are the most operationally significant:

• LEVEL_ID – Foreign key to MSD_LEVELS; the core structural identifier linking stored values to a defined planning level.
• LEVEL_VALUE – The actual value assigned to the level; the primary payload of the staging row.
• LEVEL_NAME and LEVEL_VALUE_DESC – Human-readable labels for the level and its value.
• PARENT_LEVEL_ID and PARENT_LEVEL_VALUE – Enable hierarchical level reporting and parent-child verification.
• INSTANCE and SR_INSTANCE_CODE – Identify the source instance and staging run context.
• SR_LEVEL_PK – Surrogate key used by the staging repository for this row.
• ATTRIBUTE1 … ATTRIBUTE5 – Descriptive flexfield-style columns for extended level attributes.
• SYSTEM_ATTRIBUTE1 and SYSTEM_ATTRIBUTE2 – System-populated supporting attributes.
• PROCESS_FLAG, BATCH_ID, and ST_TRANSACTION_ID – Control the state of the staging row and identify its processing batch.
• ERROR_TEXT and MESSAGE_ID – Capture validation failures during load.
• DP_ENABLED_FLAG – Indicates whether the level value is eligible for demand planning.
• LAST_UPDATE_DATE, CREATED_BY, and LAST_UPDATED_BY – Standard audit columns.

The documented surrogate/foreign key is LEVEL_ID referencing MSD_LEVELS. The composite of LEVEL_ID, INSTANCE, and LEVEL_VALUE serves as the most practical business-key candidate.

Common Use Cases and Queries

Typical usage centers on validating staged level data before it is consumed by the demand planning engine, and on diagnostics after a load failure.

• Error review: select rows where ERROR_TEXT is not null to enumerate rejected level values for a batch.
• Batch monitoring: group by BATCH_ID and PROCESS_FLAG to assess pipeline progress.
• Hierarchy validation: join PARENT_LEVEL_ID to MSD_LEVELS to detect orphaned or mismatched parent values.
• DP eligibility: filter where DP_ENABLED_FLAG = 'Y' before downstream consumption.

A representative query:

SELECT l.LEVEL_NAME, v.LEVEL_VALUE, v.ERROR_TEXT, v.PROCESS_FLAG
FROM   MSD.MSD_ST_LEVEL_VALUES v
JOIN   MSD.MSD_LEVELS l ON l.LEVEL_ID = v.LEVEL_ID
WHERE  v.BATCH_ID = :batch_id
ORDER  BY l.LEVEL_NAME, v.LEVEL_VALUE;

Related Objects

• MSD_LEVELS – Parent table; joined on LEVEL_ID.
• MSD_ST_LEVEL_VALUES self-reference via PARENT_LEVEL_ID for hierarchy checks.
• Other MSD_ST_* staging tables sharing INSTANCE, SR_INSTANCE_CODE, and BATCH_ID for end-to-end load tracing.
• Demand Planning load and validation programs that read PROCESS_FLAG and ERROR_TEXT.