Search Results msd_levels_pk

Overview

MSD_LEVELS is a core reference table in the Oracle E-Business Suite Demand Planning module (MSD). It stores the definition of planning levels, the owning dimension to which each level belongs, and the classification of the level through a level type code. Within the Demand Planning architecture, levels represent the granularity at which planning data is organized, aggregated, and analyzed. Each row defines a distinct level that other planning engines, hierarchies, and security constructs reference when selecting, storing, or displaying data.

The table resides in the MSD schema and is documented as VALID in ETRM for both Release 12.1.1 and 12.2.2. The documented physical schema contains 40 columns. Under a heuristic Data Vault classification mined from the foreign key structure, MSD_LEVELS is identified as a standalone entity, which suggests it functions as a foundational reference or hub-style table rather than a transactional link or descriptive satellite. This classification is a modeling suggestion only; the object is a standard Oracle EBS table.

Key Information Stored

The primary key of the table is defined by the MSD_LEVELS_PK constraint on LEVEL_ID, the surrogate identifier for each level. Two unique indexes act as business-key candidates: MSD_LEVELS_U1 on (PLAN_TYPE, LEVEL_ID, ZD_EDITION_NAME) and MSD_LEVELS_U2 on (PLAN_TYPE, LEVEL_NAME, ZD_EDITION_NAME). These indicate that a level name is unique within a given plan type and edition context.

• LEVEL_ID — Surrogate primary key and the value referenced by all dependent tables.
• LEVEL_NAME — User-facing name of the planning level.
• DESCRIPTION — Free-text description of the level's purpose.
• DIMENSION_CODE — Identifies the owning dimension for the level.
• LEVEL_TYPE_CODE — Specifies the level type; documented possible values distinguish the classification of the level.
• PLAN_TYPE — Plan type context that scopes the level definition and participates in both unique indexes.
• ORG_RELATIONSHIP_VIEW — Relates the level to the organizational relationship view.
• ZD_EDITION_NAME — Editioning column supporting Oracle EBS online patching (12.2.2).
• ATTRIBUTE_CATEGORY and ATTRIBUTE1–ATTRIBUTE15 — Descriptive flexfield storage for client-specific extensions.
• ATTRIBUTE1_CONTEXT through ATTRIBUTE5_CONTEXT, SYSTEM_ATTRIBUTE1_CONTEXT, SYSTEM_ATTRIBUTE2_CONTEXT — Context columns governing flexfield segment display and validation.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN — Standard EBS audit columns.
• REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE — Concurrent program audit trail identifying the process that created or updated the row.

Common Use Cases and Queries

MSD_LEVELS is typically queried as a lookup to resolve level identifiers into names, dimensions, and types for reporting, hierarchy maintenance, and security configuration. A common pattern joins dependent tables such as BIS_LEVELS, MSD_LEVEL_ASSOCIATIONS, or MSD_ST_LEVEL_VALUES back to MSD_LEVELS on LEVEL_ID to translate stored identifiers into meaningful labels.

• Listing all defined levels for a plan type: SELECT level_id, level_name, dimension_code, level_type_code FROM msd.msd_levels WHERE plan_type = :plan_type;
• Resolving a level name to its surrogate key: SELECT level_id FROM msd.msd_levels WHERE level_name = :name AND plan_type = :plan_type;
• Auditing recently modified level definitions using LAST_UPDATE_DATE, LAST_UPDATED_BY, and the concurrent program columns.
• Driving dimension and hierarchy reports by grouping records on DIMENSION_CODE and LEVEL_TYPE_CODE.

Because LEVEL_NAME is not globally unique across plan types and editions, queries should always include PLAN_TYPE and, where relevant, ZD_EDITION_NAME to avoid ambiguity.

Related Objects

MSD_LEVELS is referenced by a broad set of tables through the LEVEL_ID column, confirming its role as a foundational reference object. The most significant dependents include:

• BIS_LEVELS and BIS_LEVELS_TL — Core level definitions and translations, joined on LEVEL_ID.
• BIS_LEVELS_CUSTOMIZATIONS — Client-specific level customizations.
• MSD_LEVEL_ASSOCIATIONS and MSD_ST_LEVEL_ASSOCIATIONS — Associate levels with dimensions and hierarchy structures.
• MSD_ST_LEVEL_VALUES and MSD_DELETED_LEVEL_VALUES — Store and track level member values, including deletions.
• FII_FIN_ITEM_HIERARCHIES and FII_LOB_HIERARCHIES — Financial item and line-of-business hierarchy definitions built on levels.
• CSP_PLANNING_PARAMETERS — Planning parameters keyed by LEVEL_ID.
• MSC_ALLOCATIONS — Allocation definitions referencing planning levels.
• EDW_LEVELS_MD, EDW_LEVEL_TABLE_ATTS_MD, EDW_SEC_LVL_INFO_T, and EDW_SEC_DIM_ACCESS — Warehouse and security metadata layers that consume level definitions.
• WIP_PREFERENCE_LEVELS, WIP_PREFERENCE_VALUES, and BOM_OPERATION_SKILLS — Manufacturing-side references to level identifiers.

These relationships confirm that MSD_LEVELS should be treated as a stable, centrally maintained reference table; changes to level definitions propagate widely across planning, financial, warehouse, and manufacturing reporting throughout the E-Business Suite.