Search Results msd_st_level_associations




Overview
Explore in AI Chat

Overview

MSD.MSD_ST_LEVEL_ASSOCIATIONS is a staging table in the Oracle E-Business Suite Demand Planning module (product code MSD). It stores the value-level associations between a hierarchy level and its parent level, capturing the structural relationships that define how members of one level roll up into members of the level above it. The table is populated during data load and batch processing routines that import hierarchy definitions from source systems, and it functions as a transient staging area before validated associations are promoted into the planning engine.

A distinguishing characteristic documented in the ETRM metadata is that this table is not stripped by Demand Plan Id. Unlike many MSD staging objects that are partitioned or purged per planning scenario, MSD_ST_LEVEL_ASSOCIATIONS retains data across demand plan contexts, which means its rows persist until explicitly cleaned by concurrent program logic or batch identifiers. This has implications for both query design and housekeeping.

The heuristic Data Vault classification mined from the foreign key structure is standalone. In modeling terms, this suggests the table behaves as an independent staging construct rather than a conformed hub, link, or satellite. Its single documented foreign key, LEVEL_ID to MSD_LEVELS, establishes the anchoring relationship to the level definition, but no downstream FK dependencies were identified, reinforcing its role as a leaf-level staging artifact.

Key Information Stored

The table carries 32 documented columns in the 12.2.2 schema. The most operationally significant are summarized below.

  • LEVEL_ID / PARENT_LEVEL_ID — The core business keys, identifying the child level and its parent level respectively. LEVEL_ID is the documented foreign key referencing MSD_LEVELS.
  • SR_LEVEL_PK / SR_PARENT_LEVEL_PK — Source-system surrogate keys for the level and parent level, used to reconcile incoming records against their originating hierarchy definitions.
  • SR_LEVEL_VALUE / SR_PARENT_LEVEL_VALUE / SR_LEVEL_VALUE1 — Source value identifiers that bind specific level members to their parent members, forming the actual association payload.
  • LEVEL_NAME / PARENT_LEVEL_NAME — Descriptive names retained in the staging row to support validation, error reporting, and auditability without requiring joins.
  • SR_INSTANCE_CODE — Identifies the source instance from which the association was extracted.
  • INSTANCE — The EBS instance discriminator used across MSD staging tables.
  • PROCESS_FLAG — Controls whether the row has been processed, is pending, or has failed during the load cycle.
  • DATA_SOURCE_TYPE — Indicates the origin classification of the staged record.
  • ST_TRANSACTION_ID / BATCH_ID — Batch and transaction lineage identifiers enabling targeted reprocessing or purge.
  • ERROR_TEXT / MESSAGE_ID — Diagnostics captured when association validation fails.
  • DP_ENABLED_FLAG — Indicates whether the association is active for demand planning consumption.

Standard EBS audit columns (CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE) plus ATTRIBUTE1 through ATTRIBUTE5 complete the structure.

Common Use Cases and Queries

The primary use case is troubleshooting hierarchy load failures. Rows with a populated ERROR_TEXT or a non-successful PROCESS_FLAG identify associations that could not be validated against MSD_LEVELS.

SELECT level_id, parent_level_id, level_name, parent_level_name,
       process_flag, error_text, message_id, batch_id
  FROM msd.msd_st_level_associations
 WHERE process_flag IN ('E','F')
   AND batch_id = :batch_id;

A second use case is auditing the parent-child structure of a hierarchy prior to promotion. Joining to MSD_LEVELS on LEVEL_ID resolves level metadata:

SELECT a.level_id, l.level_name, a.parent_level_id,
       a.sr_level_value, a.sr_parent_level_value
  FROM msd.msd_st_level_associations a,
       msd.msd_levels l
 WHERE a.level_id = l.level_id
   AND a.dp_enabled_flag = 'Y';

Reporting teams also use the table to reconcile source instance counts against target planning hierarchies using SR_INSTANCE_CODE and BATCH_ID groupings. Because the table is not stripped by Demand Plan Id, purge scripts should always filter on BATCH_ID or ST_TRANSACTION_ID rather than assuming plan-scoped cleanup.

Related Objects

  • MSD_LEVELS — Referenced via MSD_ST_LEVEL_ASSOCIATIONS.LEVEL_ID → MSD_LEVELS.LEVEL_ID; the authoritative level definition table.
  • MSD_ST_LEVELS — Companion staging table for level definitions, typically loaded alongside associations.
  • MSD_ST_HIERARCHIES / MSD_HIERARCHIES — Hierarchy headers to which level associations ultimately belong.
  • MSD_ST_LEVEL_VALUES — Staging table for level member values referenced by SR_LEVEL_VALUE.
  • MSD_ST_LEVEL_MEMBERS — Member-level staging that consumes validated associations.
  • MSD_ST_TRANSACTIONS / MSD_ST_BATCHES — Batch lineage referenced by BATCH_ID and ST_TRANSACTION_ID.
  • MSD_DP_HIERARCHY_LOAD — Concurrent program logic that processes staging rows into the planning engine.

Together these objects form the staged hierarchy ingestion pipeline, with MSD_ST_LEVEL_ASSOCIATIONS occupying the relational bridge between level definitions and their parent-child value mappings.