Search Results msd_dp_hierarchies




Overview
Explore in AI Chat

Overview

MSD_DP_HIERARCHIES is a Demand Planning table owned by the MSD schema in Oracle E-Business Suite 12.1.1 and 12.2.2. It stores the associations between a Demand Plan and the hierarchies defined for that plan, allowing a single Demand Plan to be analyzed through multiple dimensional rollups (for example, product, customer, and geography hierarchies). Each row represents one Demand Plan to hierarchy pairing and carries audit and program-context columns inherited from the standard EBS concurrent-manager conventions.

From a Data Vault modeling perspective, the mined foreign-key structure classifies this table as satellite-leaning. In practice it functions as an association or link-style table positioned between a Demand Plan hub and a hierarchy hub, with descriptive attributes such as dimension code and enablement flags. The classification is a heuristic suggestion, not a normative EBS designation.

Key Information Stored

The documented physical schema lists 15 columns. The most significant are:

The surrogate primary key is MSD_DP_HIERARCHIES_PK, defined on (DEMAND_PLAN_ID, HIERARCHY_ID). A unique index, MSD_DP_HIERARCHIES_U1, adds ZD_EDITION_NAME to that pair, making it the business-key candidate under editioning.

Common Use Cases and Queries

Typical scenarios include identifying which hierarchies are enabled for a given Demand Plan, listing plans that share a particular hierarchy, and validating dimension coverage before running a demand plan. A representative query to list hierarchies for a plan follows:

  • SELECT dph.DEMAND_PLAN_ID, dph.HIERARCHY_ID, dph.DP_DIMENSION_CODE, dph.ENABLE_NONSEED_FLAG FROM MSD.MSD_DP_HIERARCHIES dph WHERE dph.DEMAND_PLAN_ID = :plan_id AND dph.ZD_EDITION_NAME = 'ORA$BASE';
  • To find all plans using a hierarchy: SELECT dph.DEMAND_PLAN_ID FROM MSD.MSD_DP_HIERARCHIES dph WHERE dph.HIERARCHY_ID = :hierarchy_id;
  • To audit recent changes: filter on LAST_UPDATE_DATE and join REQUEST_ID to FND_CONCURRENT_REQUESTS for program context.

These queries are common in custom demand planning reports, migration validation scripts, and troubleshooting of empty or misconfigured plans, where a missing row for an expected dimension explains failed forecast generation.

Related Objects

The documented foreign key shows MSD_DP_HIERARCHIES.HIERARCHY_ID referencing the hierarchy definition table (the source of HIERARCHY_ID). The Demand Plan identifier, DEMAND_PLAN_ID, is the companion key from the demand plan header. Related objects typically include:

  • MSD_DP_HIERARCHIES referenced via HIERARCHY_ID from the hierarchy master table (e.g., the MSD hierarchy definition object).
  • Demand Plan header table joined on DEMAND_PLAN_ID.
  • FND_CONCURRENT_REQUESTS joined on REQUEST_ID for program execution context.
  • FND_APPLICATION / FND_PROGRAM joined on PROGRAM_APPLICATION_ID and PROGRAM_ID.
  • MSD_DP_* companion demand planning objects (dimensions, plan members) that consume the hierarchy assignment.

Because only one explicit foreign key is documented, additional dependencies should be confirmed against the live data dictionary in the target environment.