Search Results msd_dp_scenario_revisions




Overview
Explore in AI Chat

Overview

The MSD_DP_SCENARIO_REVISIONS table is a core data object within the Demand Planning module (product code MSD) of Oracle E-Business Suite, owned by the MSD schema. It functions as the header table for scenario entries, recording each discrete revision of a demand plan scenario. In the context of Advanced Supply Chain Planning and Demand Planning, scenarios allow planners to model alternative demand assumptions before committing a plan to execution. This table anchors that revision history, associating every scenario revision with the demand plan it belongs to.

The heuristic Data Vault classification mined from the foreign key structure is standalone. This suggests that MSD_DP_SCENARIO_REVISIONS behaves as an independent hub-like entity rather than a dependent satellite or a pure link table. From a modeling standpoint, it is best treated as a master record that other tables reference, though its unique business key of SCENARIO_ID, DEMAND_PLAN_ID, and REVISION provides the natural grain for historical tracking.

Key Information Stored

The table contains eleven documented columns. The most significant include:

  • SCENARIO_ID — Surrogate identifier for the scenario; part of both the unique business key and the primary key candidate.
  • DEMAND_PLAN_ID — Identifies the parent demand plan that the scenario revision is associated with; also part of the business key.
  • REVISION — The revision number for the scenario, completing the unique business key.
  • REVISION_NAME — Descriptive label for the revision, used in user-facing lists and reports.
  • PLAN_START_DATE — Date from which the revision's plan horizon begins, relevant for time-phased reporting.
  • ERROR_TYPE — Classification of any error encountered during scenario processing, supporting exception reporting.
  • LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN — Standard EBS audit columns capturing who created and last modified the record.

The documented primary key is MSD_DP_SCENARIO_REVISIONS_UK1 (SCENARIO_ID, DEMAND_PLAN_ID, REVISION), and the unique index MSD_DP_SCENARIO_REVISIONS_U1 carries the same three columns, confirming the business-key candidate. Future schema reference should treat these three columns as the definitive grain of the table.

Common Use Cases and Queries

Typical usage centers on enumerating revisions for a plan, identifying the latest revision, and validating scenario data. A simple retrieval pattern:

  • SELECT SCENARIO_ID, DEMAND_PLAN_ID, REVISION, REVISION_NAME, PLAN_START_DATE FROM MSD.MSD_DP_SCENARIO_REVISIONS WHERE DEMAND_PLAN_ID = :plan_id ORDER BY REVISION DESC;
  • Finding the most recent revision: use an analytic ROW_NUMBER() OVER (PARTITION BY DEMAND_PLAN_ID ORDER BY REVISION DESC) and filter to row 1.
  • Audit reporting: join on CREATED_BY and LAST_UPDATED_BY to FND_USER for planner attribution.

These queries support scenario comparison dashboards, revision lifecycle audits, and exception reports driven by ERROR_TYPE.

Related Objects

Because the classification is standalone, direct foreign-key dependencies are limited, but the following objects are the most significant for operational joins:

  • MSD_DP_SCENARIO_ENTRIES — Detail lines for each scenario; join on SCENARIO_ID, DEMAND_PLAN_ID, and REVISION.
  • MSD_DEMAND_PLANS — Parent plan header; join on DEMAND_PLAN_ID.
  • MSD_SCENARIOS — Scenario definitions; join on SCENARIO_ID.
  • FND_USER — Audit attribution via CREATED_BY and LAST_UPDATED_BY.
  • MSD_DP_PLAN_HISTORIES — Historical planning context by plan and revision.

These relationships should be confirmed against the actual deployment before being relied upon in custom SQL or integration logic.