Search Results msd_level_values_u2

Overview

MSD.MSD_LEVEL_VALUES is a foundational table within the Demand Planning (MSD) schema of Oracle E-Business Suite, present in both 12.1.1 and 12.2.2. It stores level value information for demand planning hierarchies, including a system-generated surrogate primary key and the associated level value attributes. Critically, the object documentation notes that the table is "not stripped by the Demand Plan Id," meaning its contents are shared across demand plans rather than partitioned per plan. This makes it a central reference for the multi-dimensional structure that demand planning consumes.

The table resides in the APPS_TS_TX_DATA tablespace with a PCT Free of 10, and its indexes are held in APPS_TS_TX_IDX. Under the heuristic Data Vault classification mined from its foreign-key structure, this object is modeled as a hub. In Data Vault terms, a hub represents a core business key entity whose identity is referenced by surrounding satellites and links; the presence of a stable, unique surrogate primary key (LEVEL_PK) as well as descriptive attributes supports this interpretation.

Key Information Stored

The table comprises 30 documented columns. The most significant are:

• LEVEL_PK — the system-generated primary key for the level, backed by the unique index MSD_LEVEL_VALUES_U1 (MSD_LEVEL_VALUES_PK). This is the surrogate key referenced by dependent tables.
• LEVEL_ID — the level identifier, the primary business-key component. Combined with INSTANCE and SR_LEVEL_PK, it forms the candidate unique key MSD_LEVEL_VALUES_U3.
• INSTANCE — the source instance ID (VARCHAR2 40), identifying the originating source system.
• SR_LEVEL_PK — the primary key for the level in the source instance, the third component of the U3 business key.
• LEVEL_VALUE — the value of the level (VARCHAR2 240). This, together with LEVEL_ID, is indexed non-uniquely by MSD_LEVEL_VALUES_U2.
• LEVEL_VALUE_PK — source-level value primary key.
• PARENT_LEVEL_ID / PARENT_LEVEL_VALUE / PARENT_LEVEL_VALUE_PK — capture the parent level relationship, enabling hierarchy traversal.
• LEVEL_VALUE_DESC — descriptive text for the level value.
• ATTRIBUTE1 through ATTRIBUTE5 — descriptive flexfield segments.
• LAST_REFRESH_NUM / CREATED_BY_REFRESH_NUM / ACTION_CODE — used in incremental refresh and DML action tracking.
• DP_ENABLED_FLAG — indicates whether the level value is enabled for demand planning.
• The standard who columns (CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN) and extended who columns (REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE) provide audit lineage.

Common Use Cases and Queries

MSD_LEVEL_VALUES is queried to resolve level values into their surrogate keys when building or reading demand plan scenario entries, and to traverse hierarchy relationships via the parent columns. Typical reporting includes listing all values for a given level, retrieving enabled levels, and joining to scenario entries. Example patterns:

• Resolve a level value to its surrogate key: SELECT LEVEL_PK FROM MSD_LEVEL_VALUES WHERE LEVEL_ID = :level_id AND LEVEL_VALUE = :level_value AND INSTANCE = :instance;
• List enabled values for a level: SELECT LEVEL_VALUE, LEVEL_VALUE_DESC FROM MSD_LEVEL_VALUES WHERE DP_ENABLED_FLAG = 'Y' AND LEVEL_ID = :level_id;
• Traverse parent-child: join a child row to its parent via PARENT_LEVEL_ID and PARENT_LEVEL_VALUE.
• Refresh monitoring: SELECT LEVEL_VALUE, LAST_REFRESH_NUM, ACTION_CODE FROM MSD_LEVEL_VALUES WHERE LAST_REFRESH_NUM > :last_num;

Join to MSD_DP_SCENARIO_ENTRIES on the relevant Lvl_Pk columns to associate scenario data with level values.

Related Objects

The table is referenced by several demand planning objects through foreign keys, confirming its hub role:

• MSD_DP_SCENARIO_ENTRIES — references this table through multiple columns: PRODUCT_LVL_PK, GEOGRAPHY_LVL_PK, ORGANIZATION_LVL_PK, SALESCHANNEL_LVL_PK, SALES_REP_LVL_PK, USER_DEFINED1_LVL_PK, and USER_DEFINED2_LVL_PK, all pointing to LEVEL_PK.
• MSD_SHIPMENT_DATA — references this table via SR_ITEM_PK.
• The table's own LEVEL_ID foreign key relates to the level definition in the MSD schema.

These relationships position MSD_LEVEL_VALUES as the shared dimension reference that scenario and shipment data rely upon across demand planning instances.