Search Results msd_level_associations_v1




Overview
Explore in AI Chat

Overview

MSD_LEVEL_ASSOCIATIONS_V1 is an APPS-owned database view within the Demand Planning (MSD) product family of Oracle E-Business Suite. It exposes the level value associations that link a planning level to its parent level value within a demand planning hierarchy, together with the surrogate primary keys that Oracle Demand Planning generates for those level values. The view is documented as a single-purpose object: it is used exclusively by the Level Associations form, which administrators and planners use to maintain parent-child relationships between level values.

Because the view resolves both the child level value and its parent level value into readable names in a single row, it is a convenient integration and reporting surface. Users searching on level_id will find this column exposed directly, alongside the parent counterpart parent_level_id, making the view well suited to queries that traverse or flatten a planning hierarchy. The presence of WHO columns and concurrent program columns further indicates that the view is also a diagnostic surface for concurrent processes that build or refresh level associations.

Underlying Base Objects

The view is defined over five underlying objects, each referenced in the APPS schema through synonyms: MSC_APPS_INSTANCES, MSD_LEVELS, MSD_LEVEL_ASSOCIATIONS, MSD_LEVEL_VALUES, and the MSD_SR_UTIL package.

  • MSD_LEVEL_ASSOCIATIONS is the driving table. It supplies the association row itself, the child and parent surrogate key pairs (SR_LEVEL_PK and SR_PARENT_LEVEL_PK), the instance identifier, and the standard audit and concurrent program columns.
  • MSD_LEVELS is joined twice (aliases ML1 and ML2) to resolve the child LEVEL_ID and the PARENT_LEVEL_ID into their level names and dimension codes.
  • MSD_LEVEL_VALUES is likewise joined twice (MLV1 and MLV2) to resolve the child and parent level values, matching on instance and surrogate key in addition to level identifier.
  • MSC_APPS_INSTANCES supplies the INSTANCE_CODE, translating the numeric instance identifier into its human-readable code.
  • MSD_SR_UTIL is the Demand Planning utility package that supports surrogate key handling for these hierarchy records.

Key Columns

Common Use Cases and Queries

A typical use is to list all child values for a given parent within an instance, or to confirm that associations were refreshed by a specific concurrent request.

SELECT level_id, level_name, level_value,
       parent_level_id, parent_level_name, parent_level_value
FROM   apps.msd_level_associations_v1
WHERE  instance_code = :instance_code
AND    level_id = :level_id
ORDER BY parent_level_value, level_value;

A second pattern checks recent refresh activity by request or program, which helps validate that the Level Associations form is displaying current data.

SELECT request_id, program_id, last_refresh_num,
       COUNT(*) association_count
FROM   apps.msd_level_associations_v1
WHERE  last_update_date >= SYSDATE - 7
GROUP BY request_id, program_id, last_refresh_num;

Because the view is documented as being used only by the Level Associations form, custom integrations should treat it as a read-only reporting surface and avoid relying on it as an interface table.