Results for “sr_parent_level_pk”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

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

• LEVEL_ID and PARENT_LEVEL_ID — the identifiers of the child and parent planning levels respectively; these are the columns most commonly used for lookups and joins.
• LEVEL_NAME, DIMENSION_CODE, and PARENT_LEVEL_NAME — descriptive attributes of the levels involved.
• LEVEL_VALUE and PARENT_LEVEL_VALUE, with LEVEL_PK — the member values and their keys.
• SR_LEVEL_PK and SR_PARENT_LEVEL_PK — system-generated surrogate keys for the child and parent level values.
• INSTANCE and INSTANCE_CODE — the planning instance context for the association.
• LAST_UPDATE_DATE, CREATED_BY, LAST_REFRESH_NUM, REQUEST_ID, PROGRAM_ID — audit and process traceability 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.