Results for “msd_dp_scn_output_lvls_pk”

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

Overview

MSD.MSD_DP_SCENARIO_OUTPUT_LEVELS is a Demand Planning (MSD) configuration table in Oracle E-Business Suite 12.1.1 and 12.2.2. It defines the dimensional granularity — excluding the time dimension — at which a given Demand Planning scenario is generated by the Oracle Express engine. Each row associates a demand plan and a scenario with a specific dimension level, effectively instructing the planning engine how aggregated or how detailed the resulting scenario output should be. Without these rows, scenario generation would lack explicit level instructions for dimensions such as product, customer, and organization.

The object resides in the MSD schema and is classified as VALID. Its documented heuristic Data Vault classification is standalone, meaning it is not modeled as a hub, link, or satellite in a Data Vault sense. As a modeling suggestion, it is best treated as a configuration or reference table governed by its composite business key rather than as a transactional fact or a descriptive satellite.

Key Information Stored

The physical schema exposes 15 columns. The most significant are listed below.

• DEMAND_PLAN_ID — Identifies the demand plan to which the scenario and its output levels belong.
• SCENARIO_ID — Identifies the specific Demand Planning scenario being generated.
• LEVEL_ID — Identifies the dimension level at which the scenario output is generated.
• ZD_EDITION_NAME — Editioning column associated with the unique index; present in the documented unique key.
• DELETEABLE_FLAG — Indicates whether the level definition can be removed.
• ENABLE_NONSEED_FLAG — Controls whether non-seed (user-defined) members are enabled for the level.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN — Standard audit columns capturing the most recent change.
• CREATION_DATE, CREATED_BY — Standard audit columns capturing initial row creation.
• REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE — Concurrent program context identifying the process that last touched the row.

The surrogate primary key is defined by the constraint MSD_DP_SCN_OUTPUT_LVLS_PK over (DEMAND_PLAN_ID, SCENARIO_ID, LEVEL_ID). The unique index MSD_DP_SCN_OUTPUT_LVLS_U1 covers (DEMAND_PLAN_ID, SCENARIO_ID, LEVEL_ID, ZD_EDITION_NAME) and serves as the documented business-key candidate, extending the primary key with the editioning column.

Common Use Cases and Queries

Typical usage involves validating scenario configuration before plan generation, auditing which levels are enabled, and joining level metadata to dimension tables for reporting.

• Listing all output levels for a scenario: SELECT LEVEL_ID, ENABLE_NONSEED_FLAG, DELETEABLE_FLAG FROM MSD.MSD_DP_SCENARIO_OUTPUT_LEVELS WHERE DEMAND_PLAN_ID = :p_plan AND SCENARIO_ID = :p_scenario ORDER BY LEVEL_ID;
• Checking which levels permit deletion prior to maintenance: filter on DELETEABLE_FLAG = 'Y'.
• Auditing recent configuration changes via LAST_UPDATE_DATE and LAST_UPDATED_BY, or tracing the concurrent program that performed the update through PROGRAM_ID and REQUEST_ID.
• Reporting scenario-level setup by joining LEVEL_ID to the MSD level and dimension definition tables.

Related Objects

The metadata classifies this table as standalone, so no foreign keys are documented. The following related objects are the most significant based on the columns present and Demand Planning conventions.

• MSD_DP_SCENARIOS — Joined on SCENARIO_ID; holds scenario header details.
• MSD_DEMAND_PLANS — Joined on DEMAND_PLAN_ID; holds demand plan definitions.
• MSD_LEVELS / level definition tables — Joined on LEVEL_ID; supply dimension and level metadata.
• MSD_DP_SCENARIO_DIMENSIONS — Companion configuration for non-time dimension settings.
• FND_CONCURRENT_REQUESTS — Joined on REQUEST_ID for concurrent program context.
• FND_USER — Joined on CREATED_BY and LAST_UPDATED_BY for user identification.