Results for “msd_level_values”

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

Overview

MSD_LEVEL_VALUES is a Demand Planning table in the MSD (Demand Planning) product schema of Oracle E-Business Suite, documented as VALID in both 12.1.1 and 12.2.2. It stores the master set of level values that describe the dimensions along which demand planning data is aggregated — product, geography, organization, sales channel, sales representative, and user-defined dimensions. As stated in the ETRM metadata, the table holds "all the level values Information including the system generated primary key and the level value attributes," and, importantly, it "is not stripped by the Demand Plan Id," meaning its rows are shared across plans rather than partitioned per planning instance.

The table is classified via heuristic Data Vault mining as a hub, reflecting its role as a stable repository of business keys (level values) referenced by other demand planning structures. This classification is a modeling suggestion only; the physical object predates Data Vault conventions.

Key Information Stored

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

• LEVEL_PK — system-generated surrogate primary key (MSD_LEVEL_VALUES_PK); uniquely identifies each level value row.
• LEVEL_VALUE_PK — the business identifier for the level value itself.
• LEVEL_ID and LEVEL_VALUE — the level definition and its value (e.g., a product category, region, or channel name).
• LEVEL_VALUE_DESC — descriptive text for reporting and display.
• PARENT_LEVEL_ID / PARENT_LEVEL_VALUE / PARENT_LEVEL_VALUE_PK — hierarchical pointers forming the parent-child dimension structure.
• INSTANCE — the application instance context for the row.
• SR_LEVEL_PK — source level reference used in the unique business-key index.
• DP_ENABLED_FLAG — indicates whether the value is active for demand planning.
• ATTRIBUTE1–ATTRIBUTE5 and SYSTEM_ATTRIBUTE1 / SYSTEM_ATTRIBUTE2 — extensibility/descriptive flex columns.
• LAST_UPDATE_DATE, CREATED_BY, LAST_UPDATED_BY, CREATION_DATE — standard audit columns.
• REQUEST_ID, PROGRAM_ID, PROGRAM_APPLICATION_ID, PROGRAM_UPDATE_DATE — concurrent program tracking.
• LAST_REFRESH_NUM, CREATED_BY_REFRESH_NUM, ACTION_CODE — refresh and change-tracking controls.

Two unique indexes define documented business-key candidates: MSD_LEVEL_VALUES_U1 on LEVEL_PK, and MSD_LEVEL_VALUES_U3 on (INSTANCE, LEVEL_ID, SR_LEVEL_PK).

Common Use Cases and Queries

Typical uses include validating dimension members before loading plans, resolving parent-child hierarchies for reporting, and identifying which values are DP-enabled. A common join resolves scenario entries to their product, geography, organization, channel, or sales-rep level values:

• Selecting enabled level values: SELECT LEVEL_VALUE_PK, LEVEL_VALUE, LEVEL_VALUE_DESC FROM MSD.MSD_LEVEL_VALUES WHERE DP_ENABLED_FLAG = 'Y';
• Walking the hierarchy: filter on PARENT_LEVEL_VALUE_PK to retrieve children of a node.
• Joining scenario entries: SELECT s.*, lv.LEVEL_VALUE FROM MSD.MSD_DP_SCENARIO_ENTRIES s JOIN MSD.MSD_LEVEL_VALUES lv ON s.PRODUCT_LVL_PK = lv.LEVEL_PK;
• Auditing refresh activity via LAST_REFRESH_NUM and ACTION_CODE.

Related Objects

The following objects reference MSD_LEVEL_VALUES through foreign keys:

• MSD_DP_SCENARIO_ENTRIES — references via 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.
• MSD_SHIPMENT_DATA — references via SR_ITEM_PK.
• MSD_LEVEL_VALUES itself carries a self-referencing relationship on LEVEL_ID, supporting hierarchical structures.

These linkages make MSD_LEVEL_VALUES a central dimension table for demand planning scenario and shipment reporting.