Search Results msd_level_associations




Overview
Explore in AI Chat

Overview

MSD_LEVEL_ASSOCIATIONS is a Demand Planning configuration table owned by the MSD schema in Oracle E-Business Suite 12.1.1 and 12.2.2. It stores the hierarchical association information that links levels to their parent levels within the Demand Planning (MSD) module. Levels in Demand Planning represent the organizational and planning dimensions — such as product, customer, and channel hierarchies — over which forecasts, historical demand, and aggregate plans are computed. By persisting the parent-child relationship between levels, this table defines how planning data rolls up and how dimensional hierarchies are navigated during a planning run.

In terms of Data Vault modeling heuristics, the FK structure classifies this table as a standalone object. Its single foreign key reference to MSD_LEVELS suggests it acts as a link-style construct connecting two level records (a level and its parent). From an EBS architecture perspective, it is an operational configuration table rather than a transactional fact table: it defines metadata that downstream planning engines interpret when aggregating and disaggregating forecast data.

Key Information Stored

The table contains 23 documented columns. The most significant are the level identifiers and their surrogate primary keys:

The unique index MSD_LEVEL_ASSOCIATIONS_U1 spans INSTANCE, LEVEL_ID, SR_LEVEL_PK, PARENT_LEVEL_ID, and SR_PARENT_LEVEL_PK. This composite is the business-key candidate, ensuring a level-to-parent association is uniquely defined within an instance. There is no separate single-column surrogate primary key documented; the composite uniqueness constraint governs record identity.

Common Use Cases and Queries

Typical scenarios include validating hierarchy configuration, tracing rollup paths, and auditing level changes before a planning run. A common query resolves the parent chain for a given level:

  • Listing all associations for an instance: SELECT LEVEL_ID, PARENT_LEVEL_ID, DP_ENABLED_FLAG FROM MSD.MSD_LEVEL_ASSOCIATIONS WHERE INSTANCE = :instance;
  • Identifying orphaned or disabled links: SELECT * FROM MSD.MSD_LEVEL_ASSOCIATIONS WHERE DP_ENABLED_FLAG = 'N';
  • Auditing recent configuration changes: SELECT LEVEL_ID, PARENT_LEVEL_ID, LAST_UPDATED_BY, LAST_UPDATE_DATE FROM MSD.MSD_LEVEL_ASSOCIATIONS ORDER BY LAST_UPDATE_DATE DESC;
  • Joining to MSD_LEVELS to resolve level names: SELECT a.LEVEL_ID, l.LEVEL_NAME, a.PARENT_LEVEL_ID FROM MSD.MSD_LEVEL_ASSOCIATIONS a, MSD.MSD_LEVELS l WHERE a.LEVEL_ID = l.LEVEL_ID;

Reporting use cases include verifying that all levels required by a forecast have a valid parent, and diagnosing rollup discrepancies where aggregated demand does not reconcile to detail level data.

Related Objects

  • MSD_LEVELS — Referenced via MSD_LEVEL_ASSOCIATIONS.LEVEL_ID → MSD_LEVELS; the master level definition table.
  • MSD_LEVEL_ASSOCIATIONS_U1 — The unique index enforcing business-key uniqueness on the level and parent columns.
  • Demand Planning hierarchy/structure tables in the MSD schema that consume level association metadata during plan generation.
  • MSD concurrent programs referenced through PROGRAM_ID / REQUEST_ID that build and refresh hierarchy associations.
  • Planning engine collection routines that read DP_ENABLED_FLAG and refresh counters to determine which associations participate in a run.