Search Results msd_cs_defn_dim_dtls_u2

Overview

The table MSD.MSD_CS_DEFN_DIM_DTLS is a seed data object within the Oracle E-Business Suite MSD schema, associated with the Data Stream / Demand Planning component of the ETRM (Enterprise Territory and Resource Management) and Advanced Planning family. Its documented purpose is to hold Data Stream's Dimension details — that is, the definition of individual dimensions attached to a Data Stream "CS Definition." Each row describes one dimension (for example, a product, customer, or time dimension) that participates in a given collection or aggregation definition.

Physically, the table resides in the APPS_TS_SEED tablespace with PCTFREE 10, confirming its role as reference/seed configuration rather than high-volume transactional data. Status is VALID in both Oracle EBS 12.1.1 and 12.2.2, and it is registered under FND Design Data as MSD.MSD_CS_DEFN_DIM_DTLS.

The heuristic Data Vault classification mined from the foreign-key structure is standalone. In Data Vault modeling terms this suggests the table functions less as a hub or link and more as a dependent detail/satellite-style entity: it carries descriptive attributes (collect flags, aggregation types, allocation types) about dimensions that belong to a parent CS Definition, with a single outbound foreign key. It does not itself act as a shared hub for other entities beyond the one child table that references it.

Key Information Stored

The following columns are the most significant for understanding and querying this table.

• CS_DEFN_DIM_DTLS_ID (NUMBER, 15) — the surrogate primary key, documented as Primary Key and enforced by unique index MSD_CS_DEFN_DIM_DTLS_U2.
• CS_DEFINITION_ID (NUMBER, 15) — the Data Definition (CS Definition) to which the dimension belongs; the primary business-key component. Combined with DIMENSION_CODE it forms the second unique business key (MSD_CS_DEFN_DIM_DTLS_U1).
• DIMENSION_CODE (VARCHAR2, 30) — the code identifying the dimension (e.g., product, customer, time).
• COLLECT_FLAG (VARCHAR2, 30) — indicates whether the dimension is collected.
• COLLECT_LEVEL_ID (NUMBER, 15) — the level at which dimension data is collected.
• AGGREGATION_TYPE (VARCHAR2, 30) — aggregation behavior, validated against the MSD_CS_AGGREGATION_TYPE lookup.
• ALLOCATION_TYPE (VARCHAR2, 30) — allocation behavior, validated against the MSD_CS_ALLOCATION_TYPE lookup.
• CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN — standard Who columns auditing record creation and change.
• REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE — extended Who columns identifying the concurrent request and program that created or last modified the row.

The ETRM 12.2.2 physical schema lists a seventeenth column, ZD_EDITION_NAME, which participates in both unique indexes. This is an editioning column introduced under the Edition-Based Redefinition (EBR) model used in 12.2.x, and it does not appear in the 12.1.1 column set.

Common Use Cases and Queries

Primary use cases fall into three categories: (1) inspecting or troubleshooting which dimensions are attached to a given Data Stream definition; (2) verifying collection and aggregation configuration when planning data fails to aggregate as expected; and (3) auditing seeded configuration across environments.

To list all dimensions for a specific CS Definition:

SELECT CS_DEFN_DIM_DTLS_ID, DIMENSION_CODE, COLLECT_FLAG,
       COLLECT_LEVEL_ID, AGGREGATION_TYPE, ALLOCATION_TYPE
FROM   MSD.MSD_CS_DEFN_DIM_DTLS
WHERE  CS_DEFINITION_ID = :p_definition_id
ORDER BY DIMENSION_CODE;

To find which definitions collect a particular dimension (using the business key):

SELECT d.CS_DEFINITION_ID, d.DIMENSION_CODE, d.COLLECT_FLAG
FROM   MSD.MSD_CS_DEFN_DIM_DTLS d
WHERE  d.DIMENSION_CODE = :p_dimension_code
AND    d.COLLECT_FLAG   = 'Y';

Because the table is seed-oriented, reporting joins typically reach upward to the CS Definition header to obtain the definition name and back down to the column-level detail table. Environment comparison queries matching on CS_DEFINITION_ID and DIMENSION_CODE are useful for reconciling configuration between test and production.

Related Objects

• MSD.MSD_CS_DEFINITIONS — parent of this table via the foreign key CS_DEFINITION_ID → MSD_CS_DEFINITIONS. This is the header that each row's dimension belongs to.
• MSD.MSD_CS_CLMN_DIM_DTLS — child of this table, referencing CS_DEFN_DIM_DTLS_ID. It holds column-level detail for each dimension, so a definition → dimension → column drill path joins through this table.
• APPS.MSD_CS_DEFN_DIM_DTLS — the APPS-synonymed view/alias exposed to application code and concurrent programs.
• MSD_CS_AGGREGATION_TYPE lookup — validates the AGGREGATION_TYPE column.
• MSD_CS_ALLOCATION_TYPE lookup — validates the ALLOCATION_TYPE column.