Search Results msd_level_associations_u1

Overview

MSD.MSD_LEVEL_ASSOCIATIONS is a transactional table in the Oracle E-Business Suite 12.1.1 and 12.2.2 ETRM (Enterprise Territory and Resource Management) schema that maintains association information between levels in a hierarchical planning model. Specifically, it persists parent-child relationships between level definitions, allowing the applications to reconstruct the topology of a demand planning, supply chain, or territory hierarchy without re-deriving structure from the source system on every read.

The table resides in the APPS_TS_TX_DATA tablespace with a PCT Free of 10, and is registered in FND Design Data as MSD.MSD_LEVEL_ASSOCIATIONS. Its status is VALID and it is owned by the MSD schema. The unique index MSD_LEVEL_ASSOCIATIONS_U1, which the user searched for, is a NORMAL UNIQUE index created on APPS_TS_TX_IDX and is the primary business-key enforcement mechanism for the table.

From a Data Vault modeling perspective, the mined structure suggests a link classification. The table does not carry descriptive attributes about a single business entity; instead it connects two level instances (child and parent) and therefore functions as a relationship/link record between hubs representing levels. This is a heuristic suggestion rather than a native Oracle designation.

Key Information Stored

The most significant columns are:

• INSTANCE (VARCHAR2(40)) — the instance identifier that scopes which planning or execution instance the level association belongs to.
• LEVEL_ID (NUMBER) — the level ID of the child level; foreign-keyed to MSD_LEVELS.
• SR_LEVEL_PK (VARCHAR2(240)) — the source-system primary key of the child level.
• PARENT_LEVEL_ID (NUMBER) — the level ID of the parent level.
• SR_PARENT_LEVEL_PK (VARCHAR2(240)) — the source-system primary key of the parent level.
• ATTRIBUTE1–ATTRIBUTE5 (VARCHAR2(240)) — reserved for future use; typically available for extensibility.
• LAST_REFRESH_NUM and CREATED_BY_REFRESH_NUM (NUMBER) — refresh counters used by the collection/refresh programs to identify the generation of data.
• ACTION_CODE (VARCHAR2(30)) — the DML action code recorded during refresh (for example insert/update/delete semantics).
• DP_ENABLED_FLAG (NUMBER) — documented as not in use; historically distinguished DP, SOP, and EOL level values.
• Standard Who columns — LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN, plus the concurrent Who columns REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE.

There is no single surrogate primary key column. The business-key candidate is the unique index MSD_LEVEL_ASSOCIATIONS_U1 over (INSTANCE, LEVEL_ID, SR_LEVEL_PK, PARENT_LEVEL_ID, SR_PARENT_LEVEL_PK). The same column set is also covered by the SR_LEVEL_PK and SR_PARENT_LEVEL_PK indexed variants described in the storage metadata.

Common Use Cases and Queries

Typical uses include hierarchy reconstruction, level roll-up reporting, and validating parent-child integrity after a data refresh. A query to retrieve the full parent-child mapping for an instance is:

• SELECT INSTANCE, LEVEL_ID, SR_LEVEL_PK, PARENT_LEVEL_ID, SR_PARENT_LEVEL_PK FROM MSD.MSD_LEVEL_ASSOCIATIONS WHERE INSTANCE = :instance_id;
• To find all parents of a given child: filter on LEVEL_ID and SR_LEVEL_PK from the unique key.
• To identify stale rows after a refresh run, compare LAST_REFRESH_NUM against the current refresh generation, and inspect rows where ACTION_CODE indicates a delete.
• To reconcile the EBS association against the source system, join SR_LEVEL_PK and SR_PARENT_LEVEL_PK back to the source extraction tables.

Because DP_ENABLED_FLAG is documented as not in use, queries should not filter on it. Restricted use of the ATTRIBUTE columns is also advisable absent a documented business meaning.

Related Objects

The documented foreign key from LEVEL_ID to MSD_LEVELS makes MSD_LEVELS the principal related table. The 5–10 most significant related objects are:

• MSD.MSD_LEVELS — join on LEVEL_ID = MSD_LEVELS.LEVEL_ID (and PARENT_LEVEL_ID where a self-join is required) to resolve level names and attributes.
• MSD.MSD_LEVEL_ASSOCIATIONS_U1 — the unique index enforcing the business key (INSTANCE, LEVEL_ID, SR_LEVEL_PK, PARENT_LEVEL_ID, SR_PARENT_LEVEL_PK).
• MSD.MSD_LEVEL_ASSOCIATIONS_N1/N2 — the SR_LEVEL_PK and SR_PARENT_LEVEL_PK indexed lookups identified in the storage metadata.
• MSD.MSD_LEVEL_ASSOCIATIONS self-join — to traverse multi-tier hierarchies (grandparent, grandchild) via PARENT_LEVEL_ID and LEVEL_ID.
• MSD_LEVELS-dependent planning views in the MSD schema that consume level association data for roll-up and allocation logic.
• ETRM refresh and collection concurrent programs that populate LAST_REFRESH_NUM, CREATED_BY_REFRESH_NUM, and ACTION_CODE during each load cycle.

These relationships should be used as the basis for any join, reconciliation, or reporting query against MSD_LEVEL_ASSOCIATIONS.