Results for “msd_cs_defn_column_dtls_pk”

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

Overview

MSD_CS_DEFN_COLUMN_DTLS is a Demand Planning (MSD) configuration table in Oracle E-Business Suite 12.1.1 and 12.2.2 that stores the column-level mappings for each Data Stream definition. A Data Stream definition describes how source data is extracted, transformed, and loaded into a planning target; this child table records, for each such definition, how individual source columns correspond to their planning-side counterparts and how those columns are treated during aggregation, allocation, and unit-of-measure conversion. It is therefore a metadata or setup repository rather than a transactional fact table, and it is populated and maintained by the Demand Planning engine whenever a Data Stream is defined or refreshed.

The mined Data Vault classification for this object is standalone, meaning the heuristic FK analysis did not identify it as a classic hub, link, or satellite. In modeling terms, it is best treated as a descriptive satellite-like detail table hanging off the MSD_CS_DEFINITIONS entity, carrying the versioned attributes of a Data Stream definition's column mapping. No subordinate table was detected as a pure dependent child beyond the configuration detail it feeds.

Key Information Stored

The table contains 19 columns. The most significant are:

• CS_COLUMN_DTLS_ID — the surrogate primary key, enforced by MSD_CS_DEFN_COLUMN_DTLS_PK. It uniquely identifies each column-mapping record.
• CS_DEFINITION_ID — foreign key to MSD_CS_DEFINITIONS, tying the column mapping to its parent Data Stream definition.
• SOURCE_VIEW_COLUMN_NAME — the source-side column name used in the extraction view.
• PLANNING_VIEW_COLUMN_NAME — the corresponding column name on the planning target view.
• COLUMN_IDENTIFIER — a logical key for the column, participating in the U1 unique index.
• TABLE_COLUMN — the physical table column associated with the mapping.
• AGGREGATION_TYPE — how values are aggregated (for example sum, average, or none) when data is rolled up.
• ALLOCATION_TYPE — how values are spread or allocated across lower levels of the planning hierarchy.
• UOM_CONVERSION_FLAG — indicates whether unit-of-measure conversion applies to the column.
• ZD_EDITION_NAME — the editioning column required in Oracle 12.2.2, present in both unique indexes (U1 and U2) and essential for edition-aware queries.
• Standard audit columns CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE, and REQUEST_ID.

The two unique indexes are business-key candidates rather than primary keys: U1 combines CS_DEFINITION_ID, COLUMN_IDENTIFIER, and ZD_EDITION_NAME, while U2 combines CS_COLUMN_DTLS_ID and ZD_EDITION_NAME. U1 expresses the natural grain — one mapping per definition per column identifier per edition.

Common Use Cases and Queries

Typical uses include auditing how a Data Stream maps source to planning columns, diagnosing aggregation or allocation behavior, and driving reports on Data Stream configuration. A representative query joins the mapping to its parent definition:

• SELECT d.cs_column_dtls_id, d.cs_definition_id, d.source_view_column_name, d.planning_view_column_name, d.aggregation_type, d.allocation_type FROM msd.msd_cs_defn_column_dtls d WHERE d.cs_definition_id = :p_definition_id AND d.zd_edition_name = 'SET1';
• To trace where a given mapping is consumed, join to MSD_CS_CLMN_DIM_DTLS on CS_COLUMN_DTLS_ID.
• To find mappings requiring UOM handling, filter WHERE uom_conversion_flag = 'Y'.

Because editioning is active in 12.2.2, all queries should constrain or be aware of ZD_EDITION_NAME to avoid returning rows across editions.

Related Objects

• MSD_CS_DEFINITIONS — parent definition table; joined via MSD_CS_DEFN_COLUMN_DTLS.CS_DEFINITION_ID → MSD_CS_DEFINITIONS.
• MSD_CS_CLMN_DIM_DTLS — dependent detail table; references this table via CS_COLUMN_DTLS_ID.
• MSD_CS_DEFN_COLUMN_DTLS_PK — primary key constraint on CS_COLUMN_DTLS_ID.
• MSD_CS_DEFN_COLUMN_DTLS_U1 — unique index on CS_DEFINITION_ID, COLUMN_IDENTIFIER, ZD_EDITION_NAME.
• MSD_CS_DEFN_COLUMN_DTLS_U2 — unique index on CS_COLUMN_DTLS_ID, ZD_EDITION_NAME.

These relationships make the table the configurational bridge between a Data Stream definition and the column-level dimension detail that the Demand Planning load process ultimately consumes.