Search Results msd_demand_plans

Overview

MSD_DEMAND_PLANS is the master definition table for Demand Plans within the Oracle Demand Planning module (product code MSD), part of the Advanced Planning suite in Oracle E-Business Suite 12.1.1 and 12.2.2. Each row in this table represents a single demand plan, capturing the calendar model, planning parameters, time-level configuration, unit-of-measure defaults, and liability-planning attributes that govern how forecast and history data are consumed, exploded, and published. The table is owned by the MSD schema.

In a heuristic Data Vault classification, MSD_DEMAND_PLANS is best modeled as a hub. The DEMAND_PLAN_ID surrogate key anchors a cluster of dependent satellite-style tables (calendars, dimensions, express setup, parameters, and scenarios) that all reference this hub via foreign keys. This classification is a modeling suggestion rather than a physical constraint, but it reflects the fact that MSD_DEMAND_PLANS supplies the identity of the plan while its attributes and children accrue detail around it.

Key Information Stored

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

• DEMAND_PLAN_ID — the surrogate primary key, enforced by index MSD_DEMAND_PLANS_PK. This is the value propagated to all child tables.
• DEMAND_PLAN_NAME — the user-facing name of the plan, part of the unique business-key index MSD_DEMAND_PLANS_U2.
• PLAN_TYPE — classifies the plan (e.g., standard versus liability), also part of MSD_DEMAND_PLANS_U2.
• ZD_EDITION_NAME — present in both unique indexes U1 and U2, indicating this table participates in Oracle's ZD (zero-downtime) editioning scheme for online patching in 12.2.x.
• ORGANIZATION_ID — the inventory organization context, part of MSD_DEMAND_PLANS_U1.
• CALENDAR_TYPE, CALENDAR_CODE, PERIOD_SET_NAME — define the time calendar the plan uses for bucketing history and forecast.
• BASE_UOM — the base unit of measure for the plan's quantities.
• HISTORY_START_DATE — the point from which historical demand is loaded.
• PLAN_START_DATE and PLAN_END_DATE — the horizon of the plan.
• VALID_FLAG, ENABLE_FCST_EXPLOSION — control plan validity and whether forecast explosion is enabled.
• ROUNDOFF_THREASHOLD, ROUNDOFF_DECIMAL_PLACES, AMT_THRESHOLD, AMT_DECIMAL_PLACES — rounding and threshold behavior for quantities and amounts.
• TEMPLATE_FLAG, TEMPLATE_ID, DEFAULT_TEMPLATE — govern plan-template derivation and defaulting.
• DP_BUILD_ERROR_FLAG, DP_BUILD_REFRESH_NUM — track build status and refresh sequencing of the plan's data structures.
• LIAB_PLAN_ID, LIAB_PLAN_NAME, LIABILITY_REVISION_NUM — support liability planning scenarios.

Standard audit columns (CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, and the WHO columns) are also present, alongside SR_INSTANCE_ID, which ties the row to its source EBS instance.

Common Use Cases and Queries

One of the most frequent lookups is resolving a plan ID from a plan name, which is precisely what the user seeking msd_demand_plans_pk is likely attempting:

• Retrieving plan metadata: SELECT demand_plan_id, demand_plan_name, plan_type, cal... (join to calendars) for a specific organization.
• Listing active plans: SELECT * FROM msd.msd_demand_plans WHERE valid_flag = 'Y'.
• Join-based reporting: linking MSD_DEMAND_PLANS to MSD_DP_PARAMETERS to expose planning parameters per plan, or to MSD_DP_SCENARIOS to enumerate scenarios.
• Data Vault / ETL modeling: treating DEMAND_PLAN_ID as a hub key and joining to child tables for descriptive satellites.
• Audit and refresh tracking: querying DP_BUILD_REFRESH_NUM and DP_BUILD_ERROR_FLAG to monitor plan builds.

Related Objects

The FK relationship data identifies five principal dependent tables, all keyed on DEMAND_PLAN_ID:

• MSD_DP_CALENDARS — calendar configuration per plan (MSD_DP_CALENDARS.DEMAND_PLAN_ID → MSD_DEMAND_PLANS).
• MSD_DP_DIMENSIONS — dimensional setup for each plan.
• MSD_DP_EXPRESS_SETUP — Express (OLAP) setup metadata.
• MSD_DP_PARAMETERS — plan-level planning parameters.
• MSD_DP_SCENARIOS — scenarios defined within the plan.

Because these tables reference MSD_DEMAND_PLANS rather than the reverse, MSD_DEMAND_PLANS behaves as a parent hub; care should be taken when deleting or renumbering rows, as orphaned children would otherwise result.