Search Results msd_cs_defn_column_dtls_u2

Overview

MSD.MSD_CS_DEFN_COLUMN_DTLS is a seed data table in the Oracle EBS Advanced Planning / Demand Planning (MSD) schema that stores the column-level mappings used by Data Stream definitions. Each row describes how a column in a source view is projected, transformed, and consumed within a planning view, including aggregation, allocation, and unit-of-measure handling rules. The table resides in the APPS_TS_SEED tablespace with PCTFREE 10, consistent with its role as a reference/configuration object rather than a high-volume transactional table.

Heuristically, the metadata suggests this object functions as a standalone table in a Data Vault sense, with no inherited parent lineage other than the direct foreign key to MSD_CS_DEFINITIONS. Its primary key, MSD_CS_DEFN_COLUMN_DTLS_PK, is defined on CS_COLUMN_DTLS_ID. A candidate business key is exposed through the unique index MSD_CS_DEFN_COLUMN_DTLS_U1 on (CS_DEFINITION_ID, COLUMN_IDENTIFIER, ZD_EDITION_NAME), allowing each definition to declare column identifiers uniquely within an edition. A secondary unique constraint, MSD_CS_DEFN_COLUMN_DTLS_U2 on (CS_COLUMN_DTLS_ID, ZD_EDITION_NAME) — the identifier referenced in the user's search — functions as an edition-aware alternate key supporting edition-based redefinition.

Key Information Stored

The most relevant columns of this table are:

• CS_COLUMN_DTLS_ID (NUMBER 15) — surrogate primary key for the row.
• CS_DEFINITION_ID (NUMBER 15) — foreign key to MSD_CS_DEFINITIONS; identifies the parent Data Stream definition.
• COLUMN_IDENTIFIER (VARCHAR2 30) — the business identifier of the mapped column, unique per definition and edition.
• SOURCE_VIEW_COLUMN_NAME (VARCHAR2 30) — column name on the source view feeding the stream.
• PLANNING_VIEW_COLUMN_NAME (VARCHAR2 30) — target column on the planning view.
• TABLE_COLUMN (VARCHAR2 30) — underlying table column that the mapping resolves to.
• AGGREGATION_TYPE (VARCHAR2 30) — lookup code against MSD_CS_AGGREGATION_TYPE describing how values roll up.
• ALLOCATION_TYPE (VARCHAR2 30) — lookup code against MSD_CS_ALLOCATION_TYPE describing value distribution.
• UOM_CONVERSION_FLAG (VARCHAR2 30) — indicates whether unit-of-measure conversion is applied.
• ZD_EDITION_NAME (VARCHAR2 30) — edition discriminator enabling edition-based redefinition of the mapping set.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE — standard "Who" audit columns.
• PROGRAM_APPLICATION_ID, PROGRAM_ID, REQUEST_ID, PROGRAM_UPDATE_DATE — extended Who columns identifying the concurrent program that last changed the row.

Common Use Cases and Queries

Typical uses include auditing Data Stream column mappings, verifying aggregation or allocation coverage across definitions, and driving downstream planning engine configuration. A common pattern lists all column mappings for a given definition:

• SELECT d.definition_name, c.column_identifier, c.source_view_column_name, c.planning_view_column_name, c.aggregation_type FROM msd.msd_cs_defn_column_dtls c JOIN msd.msd_cs_definitions d ON d.cs_definition_id = c.cs_definition_id WHERE c.cs_definition_id = :p_def_id;
• Filtering by edition — WHERE zd_edition_name = SYS_CONTEXT('USERENV','DB_EDITION') — to read only the active mapping set.
• Detecting rows requiring UOM conversion — WHERE uom_conversion_flag = 'Y' — for reconciliation reporting.
• Joining to MSD_CS_CLMN_DIM_DTLS on CS_COLUMN_DTLS_ID to inspect dimensional expansion of each column mapping.

Related Objects

• MSD_CS_DEFINITIONS — parent table; joined via CS_DEFINITION_ID.
• MSD_CS_CLMN_DIM_DTLS — child table referencing CS_COLUMN_DTLS_ID; stores dimension details per mapped column.
• Lookups MSD_CS_AGGREGATION_TYPE and MSD_CS_ALLOCATION_TYPE — validate AGGREGATION_TYPE and ALLOCATION_TYPE values.
• Indexes MSD_CS_DEFN_COLUMN_DTLS_U1, _U2, and PK — support the two unique business-key candidates plus the surrogate key.
• Concurrent programs registered against MSD_CS_DEFN_COLUMN_DTLS — identified via PROGRAM_ID / REQUEST_ID for change-tracking reports.