Search Results msd_demand_plans_u1

Overview

MSD.MSD_DEMAND_PLANS is a core definition table in the Demand Planning module of Oracle E-Business Suite Advanced Supply Chain Planning (ASCP) / Demand Signal Repository stack, owned by the MSD schema. It stores one row per Demand Plan Definition, capturing the calendar, UOM, category aggregation, time-level, and planning-horizon configuration that governs how forecast and demand data are modeled, exploded, and aggregated. Applications such as Demantra Demand Management and ASCP consult this table when building, refreshing, and publishing a demand plan, and it acts as the central anchor to which dependent plan configuration records (calendars, dimensions, parameters, scenarios, express setup) attach.

Heuristically, based on the documented foreign-key structure, this table is best modeled as a hub in a Data Vault sense. It carries the natural/business identity of a demand plan (DEMAND_PLAN_ID, plus the composite MSD_DEMAND_PLANS_U1 key) and is the parent referenced by five dependent child tables. From the documented schema, 75 columns are present in ETRM 12.2.2.

Key Information Stored

The most significant columns and their documented meaning are:

• DEMAND_PLAN_ID — the surrogate primary key (MSD_DEMAND_PLANS_PK) and Demand Plan unique ID.
• ORGANIZATION_ID — organization identifier, equal to the trading partner ID from the source instance; it is part of the unique business key.
• DEMAND_PLAN_NAME — the descriptive name of the demand plan (VARCHAR2(30)); combined with PLAN_TYPE and ZD_EDITION_NAME it forms the MSD_DEMAND_PLANS_U2 business key.
• PLAN_TYPE — the plan type, forming the second element of the U2 unique index.
• ZD_EDITION_NAME — the edition discriminator included in both unique indexes, supporting editioned/versioned plan definitions.
• DESCRIPTION — free-text plan description (up to 240 characters).
• CALENDAR_TYPE — Gregorian, Manufacturing, or Fiscal, driven by the MSD_CALENDAR_TYPE lookup; with CALENDAR_CODE (manufacturing) and PERIOD_SET_NAME (fiscal) it determines the plan calendar.
• BASE_UOM — the base unit of measure for the plan.
• CATEGORY_SET_ID — the category set used to aggregate product along the category hierarchy.
• LOWEST_PERIOD_TYPE — the lowest time level used (e.g., manufacturing week/period, fiscal month/quarter/year, Gregorian month/quarter/year, day), per the MSD_PERIOD_TYPE lookup.
• HISTORY_START_DATE, PLAN_START_DATE, PLAN_END_DATE — the history start and the planning horizon boundaries.
• LIAB_PLAN_ID / LIAB_PLAN_NAME — linkage to a liability plan (related index MSD_DEMAND_PLANS_N1 on LIAB_PLAN_ID).
• VALID_FLAG / DELETE_PLAN_FLAG / TEMPLATE_FLAG — status and plan-role indicators.
• SR_INSTANCE_ID and the standard WHO columns (CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, REQUEST_ID, PROGRAM_ID).

The remaining columns (ATTRIBUTE1–15, rounding/threshold fields, stripe and time-level IDs, BOM and liability revision fields) extend this core definition.

Common Use Cases and Queries

Typical scenarios include identifying and listing all active demand plans for a given organization, reporting the calendar and time-level configuration, and resolving a plan name to its surrogate ID for downstream joins.

• Look up a plan by its business key:

  SELECT DEMAND_PLAN_ID, DEMAND_PLAN_NAME, PLAN_TYPE
FROM MSD.MSD_DEMAND_PLANS
WHERE ORGANIZATION_ID = :org_id
AND DEMAND_PLAN_NAME = :name;

• List valid, non-deleted plans:

  SELECT DEMAND_PLAN_ID, DEMAND_PLAN_NAME, LOWEST_PERIOD_TYPE, HISTORY_START_DATE, PLAN_START_DATE, PLAN_END_DATE
FROM MSD.MSD_DEMAND_PLANS
WHERE VALID_FLAG = 'Y' AND NVL(DELETE_PLAN_FLAG,'N') = 'N';

• Join the plan definition to its parameters and scenarios:

  SELECT p.DEMAND_PLAN_NAME, par.PARAMETER_ID, s.SCENARIO_ID
FROM MSD.MSD_DEMAND_PLANS p, MSD.MSD_DP_PARAMETERS par, MSD.MSD_DP_SCENARIOS s
WHERE p.DEMAND_PLAN_ID = par.DEMAND_PLAN_ID
AND p.DEMAND_PLAN_ID = s.DEMAND_PLAN_ID;

• Report by calendar type / period granularity for planning-conformance dashboards.
• Resolve plans that reference a liability plan (LIAB_PLAN_ID) to analyze liability-revision lineage.

Related Objects

The parent hub is referenced by the following documented child tables via DEMAND_PLAN_ID:

• MSD_DP_CALENDARS.DEMAND_PLAN_ID → MSD_DEMAND_PLANS — calendar definitions per plan.
• MSD_DP_DIMENSIONS.DEMAND_PLAN_ID → MSD_DEMAND_PLANS — dimension configuration for the plan.
• MSD_DP_EXPRESS_SETUP.DEMAND_PLAN_ID → MSD_DEMAND_PLANS — express setup parameters.
• MSD_DP_PARAMETERS.DEMAND_PLAN_ID → MSD_DEMAND_PLANS — plan-level parameters and thresholds.
• MSD_DP_SCENARIOS.DEMAND_PLAN_ID → MSD_DEMAND_PLANS — scenarios associated with the plan.

Together these dependent objects, keyed by DEMAND_PLAN_ID, define the complete behavioral footprint of a demand plan and should be joined whenever plan behavior, parameters, or scenario analysis is required.