Search Results owning_dimension_code




Overview
Explore in AI Chat

Overview

APPS.MSD_DP_HIERARCHIES_V is a reporting and integration view within the Oracle E-Business Suite Advanced Supply Chain Planning (ASCP) / Demand Planning (DP) schema, owned by the APPS database user. It presents the association between demand plans and the hierarchies that organize their planning dimensions. In the ETRM 12.1.1 and 12.2.2 data model, the underlying transactional table MSD_DP_HIERARCHIES stores which hierarchy is assigned to a demand plan for a given dimension, and this view enriches that assignment with descriptive attributes drawn from the demand plan definition, the hierarchy definition, and the FND lookup that labels the dimension. The view therefore acts as a denormalized, human-readable access point for demand plan hierarchy configuration, removing the need for callers to join four separate objects themselves. Because the view exposes ROWID and WHO/audit columns from the base entity, it can also be used as the driving query for Oracle Forms-based maintenance and for concurrent program data extraction.

Underlying Base Objects

The view is defined over five documented base objects:

  • MSD_DP_HIERARCHIES (synonym over the base table) — the driving entity, providing demand_plan_id, dp_dimension_code, hierarchy_id, and the audit and program columns.
  • MSD_DEMAND_PLANS (synonym) — supplies demand_plan_name and plan_type; joined on demand_plan_id.
  • MSD_HIERARCHIES (synonym) — supplies hierarchy_name, description, dimension_code, valid_flag, and plan_type; joined on hierarchy_id.
  • FND_LOOKUP_VALUES_VL (view) — resolves dp_dimension_code to a translatable meaning. The lookup_type is chosen dynamically: MSD_DIMENSIONS_LB when the plan type is LIABILITY, otherwise MSD_USER_DIMENSIONS.
  • MFG_LOOKUPS (view) — validates hierarchy.valid_flag against lookup_type SYS_YES_NO.

A further filter reconciles hierarchy plan_type with demand plan plan_type: SOP and EOL plans map to 'DP', a null hierarchy plan_type defaults to 'DP', and all other cases must match exactly. This ensures that only compatible hierarchies are returned for a given plan.

Key Columns

Common Use Cases and Queries

The view is typically used by Demand Planning setup and diagnostics reports to list hierarchy assignments per plan and dimension. The example below retrieves all hierarchies for plans whose dimension maps to the intermediate ("INTMED") dimension code.

SELECT demand_plan_name, dp_dimension_code, meaning, hierarchy_name, dimension_code, enable_nonseed_flag FROM apps.msd_dp_hierarchies_v WHERE meaning LIKE '%INTMED%' OR dp_dimension_code LIKE '%INTMED%' ORDER BY demand_plan_name, meaning;

Other common patterns include auditing assignments created within a date range using creation_date, checking deleteable_flag before cleanup, and pivoting results by demand_plan_name to compare configured hierarchies across plans. Because the view performs no aggregation, it is also suitable as a source in OBIEE/BI Publisher extracts and in custom PL/SQL validation routines that verify a plan has exactly one hierarchy per required dimension.