Search Results msd_hierarchies_pk

Overview

MSD_HIERARCHIES is a Demand Planning table in the MSD product schema of Oracle E-Business Suite, documented as valid in both 12.1.1 and 12.2.2. It stores the definition of hierarchies used by the Demand Planning engine, together with the operational state of each hierarchy (valid or not valid) and the dimension to which the hierarchy belongs. Hierarchies in this context drive how planning data is aggregated and disaggregated across the dimension members that participate in forecasting, demand shaping, and plan review.

From a modeling perspective, the provided relationship metadata classifies this table as hub-leaning. The primary key MSD_HIERARCHIES_PK is defined on HIERARCHY_ID, a surrogate identifier for each hierarchy record. The table is referenced by MSD_HIERARCHY_LEVELS through HIERARCHY_ID, indicating that hierarchy levels are dependent detail records that describe the structure beneath each hierarchy header. A hub-leaning classification suggests the table behaves as a stable reference entity for hierarchy identity, with the level structure and other descriptive attributes acting as associated detail.

Key Information Stored

The documented physical schema for 12.2.2 lists 32 columns. The most significant for functional and integration purposes include:

• HIERARCHY_ID — surrogate primary key and the value propagated to MSD_HIERARCHY_LEVELS as a foreign key.
• HIERARCHY_NAME — the user-facing name of the hierarchy; part of unique index MSD_HIERARCHIES_U2 along with PLAN_TYPE and ZD_EDITION_NAME.
• DESCRIPTION — free-text description of the hierarchy's purpose.
• DIMENSION_CODE — identifies the dimension to which the hierarchy belongs, linking the hierarchy to its analytical context.
• VALID_FLAG — indicates whether the hierarchy is currently valid for use.
• PLAN_TYPE — distinguishes the planning context in which the hierarchy is defined; part of both unique indexes.
• ZD_EDITION_NAME — editioning column used for online patching in 12.2.x; participates in both unique indexes.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN — standard audit columns.
• REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE — concurrent program context for the process that created or last modified the row.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1 through ATTRIBUTE15 — the descriptive flexfield segment columns available for customer extensions.

Two unique indexes act as business-key candidates: MSD_HIERARCHIES_U1 on (PLAN_TYPE, HIERARCHY_ID, ZD_EDITION_NAME) and MSD_HIERARCHIES_U2 on (HIERARCHY_NAME, PLAN_TYPE, ZD_EDITION_NAME). Together they enforce that a hierarchy name is unique within a plan type and edition.

Common Use Cases and Queries

Typical reporting and validation scenarios involve identifying which hierarchies are active for a given dimension or plan type, and reconciling hierarchies against their level definitions.

• Listing valid hierarchies for a dimension:
  SELECT h.hierarchy_id, h.hierarchy_name, h.dimension_code
FROM msd.msd_hierarchies h
WHERE h.dimension_code = :dimension_code
AND h.valid_flag = 'Y';
• Joining hierarchies to their levels to inspect the structure:
  SELECT h.hierarchy_name, l.level_id, l.level_name
FROM msd.msd_hierarchies h, msd.msd_hierarchy_levels l
WHERE h.hierarchy_id = l.hierarchy_id
AND h.plan_type = :plan_type;
• Checking the audit and concurrent program context for recently created hierarchies using CREATED_BY, CREATION_DATE, PROGRAM_ID, and REQUEST_ID.
• Reading descriptive flexfield values through ATTRIBUTE_CATEGORY and ATTRIBUTE1–15 in custom extracts.

Because the table is editioned in 12.2.2, queries that run outside the standard editioning context should be aware of ZD_EDITION_NAME to avoid returning rows from multiple editions.

Related Objects

The most significant documented relationship is to MSD_HIERARCHY_LEVELS, which references MSD_HIERARCHIES via HIERARCHY_ID. This child table carries the level-by-level structure that gives each hierarchy its meaning; joins between the two are essential for structural reporting and for validating that a hierarchy is complete.

Other significant related objects in the MSD Demand Planning schema include MSD_DIMENSIONS, which defines the dimensions referenced by DIMENSION_CODE, and the planning and forecast tables that consume hierarchy definitions when aggregating and disaggregating demand. Integration points typically include the Demand Planning concurrent programs recorded through PROGRAM_ID and REQUEST_ID, and any published interfaces or views that expose hierarchy metadata to downstream reporting.