Search Results msd_dp_hierarchies_v




Overview
Explore in AI Chat

Overview

MSD_DP_HIERARCHIES_V is a reporting and integration view owned by the APPS schema in Oracle E-Business Suite, belonging to the MSD (Demand Planning) product family within ETRM / Advanced Supply Chain Planning. Its documented purpose is to store and expose the association between Demand Plans and the hierarchies assigned to them. In practice, the view denormalizes a set of related planning entities — demand plans, plan dimensions, and hierarchy definitions — into a single flat record set, so that downstream concurrent programs, OBIEE extracts, Discoverer workbooks, and custom reports can resolve the plan-to-hierarchy relationship without writing multi-table joins themselves. Because it is a view rather than a table, it carries no storage of its own and reflects the current state of its underlying base objects at query time. In 12.1.1 and 12.2.2 the object is documented as VALID, with the same structure and column list; the 12.2.2 ETRM documentation identifies the referenced base objects as FND_LOOKUP_VALUES_VL, MFG_LOOKUPS, MSD_DEMAND_PLANS, MSD_DP_HIERARCHIES, and MSD_HIERARCHIES.

Underlying Base Objects

The view is defined as a join across four principal sources. MSD_DP_HIERARCHIES (referenced through a synonym) is the association table and supplies the core Demand Plan ID, dimension code, hierarchy ID, and the standard WHO/audit columns plus the concurrent request identifiers. MSD_DEMAND_PLANS provides the Demand Plan Name. MSD_HIERARCHIES, exposed through its own view MSD_HIERARCHIES_V, supplies the hierarchy name, description, and — most importantly for the column referenced in the search term — the owning dimension attributes. FND_LOOKUP_VALUES_VL joins on LOOKUP_TYPE = 'MSD_USER_DIMENSIONS' and LOOKUP_CODE = DP_DIMENSION_CODE to translate the stored dimension code into a user-facing meaning; MFG_LOOKUPS appears as an additional referenced object in the documented dependency list. The join therefore moves from the plan-hierarchy association out to both the plan and hierarchy masters, then out again to the lookup that gives the dimension code a display value.

Key Columns

Common Use Cases and Queries

Typical usage includes validating that each active demand plan has a hierarchy assigned for every required dimension, reporting the owning dimension for hierarchies in use, and reconciling plan configuration before a planning run.

  • List all hierarchies for a plan: SELECT demand_plan_name, dp_dimension, hierarchy_name FROM msd_dp_hierarchies_v WHERE demand_plan_id = :p_plan_id
  • Find plans by owning dimension: SELECT demand_plan_name, hierarchy_name, owning_dimension FROM msd_dp_hierarchies_v WHERE owning_dimension_code = :p_dim
  • Audit recent changes: SELECT demand_plan_name, hierarchy_name, last_updated_by, last_update_date FROM msd_dp_hierarchies_v ORDER BY last_update_date DESC