Search Results msd_st_level_values




Overview
Explore in AI Chat

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:

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.