Search Results msd_cs_clmn_dim_dtls_v




Overview
Explore in AI Chat

Overview

MSD_CS_CLMN_DIM_DTLS_V is a read-only database view owned by the APPS schema in Oracle E-Business Suite, delivered as part of the MSD (Demand Planning) product family. The view exposes Data Stream dimension-column specific details, functioning as the intersection layer that maps columns defined within a Data Stream definition to the dimensions those columns participate in. In the ETRM 12.1.1 and 12.2.2 documentation sets, the object carries a VALID status and is classified as a VIEW, meaning it is not directly maintained by users but derived at runtime from its underlying definition and synonym structure.

Its practical role in Oracle EBS reporting and integration is to present descriptive, human-readable attributes for each dimension-column pairing. Rather than forcing report authors to resolve lookup codes manually, the view joins the raw AGGREGATION_TYPE and ALLOCATION_TYPE codes against FND_LOOKUP_VALUES_VL to surface the corresponding AGGREGATION_TYPE_DESC and ALLOCATION_TYPE_DESC meanings. This makes the view a frequent target for ad hoc queries, Discoverer workbooks, and BI Publisher data templates that examine how a given Data Stream aggregates or allocates dimensional data.

Underlying Base Objects

The view is defined over a join of five documented objects. The primary driving table is the synonym MSD_CS_CLMN_DIM_DTLS, aliased CLDIM, which stores the actual column-to-dimension detail rows. Three views supply contextual attributes: MSD_CS_DEFN_COLUMN_DTLS_V (DFCLM) supplies definition-level column identifiers and table column references; MSD_CS_DEFN_DIM_DTLS_V (DFDIM) supplies dimension codes and descriptions; and FND_LOOKUP_VALUES_VL is joined twice (aliases AGG and ALLO) to translate the aggregation and allocation codes using the lookup types MSD_CS_AGGREGATION_TYPE and MSD_CS_ALLOCATION_TYPE respectively. Both lookup joins are outer joins, so rows with an unresolved or null code are still returned with a null description. The MSD_CS_DFN_UTL package is also referenced in the dependency chain, providing utility logic for the Data Stream definition metadata used to populate the base objects.

Key Columns

Common Use Cases and Queries

The most frequent use case is resolving allocation and aggregation behavior for a Data Stream. A query filtered on ALLOCATION_TYPE_DESC allows planners and analysts to list every column-dimension mapping that uses a particular allocation rule:

SELECT DIMENSION_CODE, COLUMN_IDENTIFIER,
       AGGREGATION_TYPE_DESC, ALLOCATION_TYPE_DESC
FROM   APPS.MSD_CS_CLMN_DIM_DTLS_V
WHERE  ALLOCATION_TYPE_DESC = :p_allocation_type
AND    CS_DEFINITION_ID = :p_definition_id;

Administrators troubleshooting Data Stream setup use the view to audit unmapped or null lookups by checking rows where ALLOCATION_TYPE_DESC IS NULL despite a populated ALLOCATION_TYPE. Integration developers extract the full dimension-column inventory for a definition to feed external planning engines, selecting DIMENSION_CODE, TABLE_COLUMN, and the descriptive attributes together. Because all descriptive columns are pre-joined, the view eliminates the need for custom lookup logic in downstream reports.