Results for “msd_cs_defn_dim_dtls_v”

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

Overview

MSD_CS_DEFN_DIM_DTLS_V is a reporting view owned by the APPS schema in Oracle E-Business Suite, delivered as part of the MSD (Demand Planning) product family within the Advanced Supply Chain Planning / Demand Planning application stack. The view exposes Data Stream dimension detail definitions, providing a denormalized and human-readable presentation of the configuration records stored in the underlying MSD_CS_DEFN_DIM_DTLS table. Its principal role is to translate stored code values—dimension codes, allocation types, and aggregation types—into their descriptive meanings by joining to the FND_LOOKUP_VALUES_VL lookup view.

This view is relevant to both functional configuration review and technical integration scenarios, particularly where downstream processes must understand how a Data Stream definition aggregates or allocates demand data across dimensions. The aggregation_type column, which the user searched for, is one of the primary configurable attributes surfaced by this view and is decoded through the MSD_CS_AGGREGATION_TYPE lookup type.

Underlying Base Objects

The view is defined over the following documented base objects:

• MSD_CS_DEFN_DIM_DTLS (SYNONYM) — the primary table holding one row per dimension detail associated with a Data Stream (CS) definition. This is the driving table in the join.
• FND_LOOKUP_VALUES_VL (VIEW) — the standard Oracle Application Object Library lookup view, referenced three times (aliased DIM, AGG, and ALLO) to resolve dimension, aggregation, and allocation code values into meanings.
• MSD_CS_DFN_UTL (PACKAGE) — a PL/SQL utility package whose GET_LEVEL_DESC function is invoked inline to derive the LEVEL_DESC (collect level description) from the dimension code and collect level ID.

The joins to FND_LOOKUP_VALUES_VL are the key structural characteristic of the view. The join to DIM (lookup type MSD_DIMENSIONS) is an inner join, meaning a dimension detail row is only returned when its dimension code resolves to a valid lookup entry. The joins to AGG (lookup type MSD_CS_AGGREGATION_TYPE) and ALLO (lookup type MSD_CS_ALLOCATION_TYPE) are outer joins, so rows with null aggregation or allocation types are still returned, with their corresponding description columns null.

Key Columns

• CS_DEFN_DIM_DTLS_ID — primary identifier for the dimension detail record.
• CS_DEFINITION_ID — foreign key linking the detail to its parent Data Stream (CS) definition.
• DIMENSION_CODE / DIMENSION_DESC — the dimension identifier and its decoded meaning from the MSD_DIMENSIONS lookup.
• COLLECT_LEVEL_ID / COLLECT_LEVEL_DESC — the collection level and its description, the latter derived via MSD_CS_DFN_UTL.GET_LEVEL_DESC.
• COLLECT_FLAG — indicates whether the dimension is collected.
• ALLOCATION_TYPE / ALLOCATION_TYPE_DESC — allocation code and decoded meaning (MSD_CS_ALLOCATION_TYPE).
• AGGREGATION_TYPE / AGGREGATION_TYPE_DESC — aggregation code and decoded meaning (MSD_CS_AGGREGATION_TYPE); the column of primary interest when analyzing how a dimension rolls up.
• Audit columns — CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE.

Common Use Cases and Queries

Typical uses include reviewing dimension configuration for a Data Stream definition, auditing aggregation and allocation settings, and driving integration extracts. A representative query filtering on aggregation type is:

SELECT cs_definition_id,
       dimension_code,
       dimension_desc,
       aggregation_type,
       aggregation_type_desc,
       allocation_type_desc,
       collect_level_desc
  FROM apps.msd_cs_defn_dim_dtls_v
 WHERE cs_definition_id = :p_cs_definition_id
   AND aggregation_type = :p_aggregation_type;

A configuration audit listing all dimensions and their aggregation behavior for a given Data Stream:

SELECT dimension_desc, collect_flag, aggregation_type_desc
  FROM apps.msd_cs_defn_dim_dtls_v
 WHERE cs_definition_id = :p_cs_definition_id
 ORDER BY dimension_code;

Because AGG and ALLO are outer-joined, filters on aggregation_type should account for possibly null values; use NVL(aggregation_type, 'NONE') if unset aggregations must be included. The view is read-only and intended for query and reporting access rather than direct DML.