Search Results cs_definition_id

Overview

APPS.MSD_CS_DEFINITIONS_V1 is a denormalized reporting view in Oracle EBS that exposes stream and collection plan definitions maintained by the Enterprise Trade and Transportation Management / Supply Chain Management (MSD) schema. It is defined over the MSD_CS_DEFINITIONS synonym (the stream/definition header), the MSD_CS_DEFN_DIM_DTLS synonym (dimension detail rows), and FND_LOOKUP_VALUES_VL (for descriptive meaning values). The view serves as the canonical read interface for ETRM stream definitions, joining header attributes with one detail row per dimension code.

The user search term dimension_code is significant: the underlying detail table MSD_CS_DEFN_DIM_DTLS is keyed in part by dimension_code, and the view pivots up to eight such codes ('DCS', 'PRD', 'GEO', and others) into distinct column sets. Each dimension appears as a separate outer-joined block providing collect_level_id and collect_flag.

Underlying Base Objects

• FND_LOOKUP_VALUES_VL (VIEW) — the standard EBS lookup view, referenced twice with the aliases lok1 and lok2. It resolves CS_TYPE against lookup type MSD_CS_STREAM_SOURCE_TYPE and MEASUREMENT_TYPE against MSD_CS_MEASUREMENT_TYPE.
• MSD_CS_DEFINITIONS (SYNONYM) — the definition header, providing identity, naming, classification and control flags.
• MSD_CS_DEFN_DIM_DTLS (SYNONYM) — the dimension detail table, joined once (as dcs) with dimension_code = 'DCS' and up to eight times (as dfdim1 through dfdim8) for PRD, GEO and the remaining dimension codes.

All dimension joins are outer joins (denoted (+) in the view text), so a definition appears even when a given dimension has no stored detail row. Lookup joins are likewise outer joins, ensuring definitions persist regardless of lookup completeness.

Key Columns

• CS_DEFINITION_ID — primary key of the definition header; the pivot key for all dimension joins.
• NAME / DESCRIPTION — user-facing identity of the stream or collection definition.
• CS_CLASSIFICATION, CS_TYPE, CS_TYPE_DESC — classification and stream source type, the latter resolved from MSD_CS_STREAM_SOURCE_TYPE.
• MEASUREMENT_TYPE — measurement type code, with corresponding meaning from MSD_CS_MEASUREMENT_TYPE.
• LOWEST_LEVEL_FLAG, STRICT_FLAG, SYSTEM_FLAG, VALID_FLAG — core control flags governing definition behavior.
• AGGREGATION_ALLOWED_FLAG, ALLOCATION_ALLOWED_FLAG, STREAM_EDITABLE_FLAG, MULTIPLE_STREAM_FLAG — operational capability flags.
• PLANNING_SERVER_VIEW_NAME, SOURCE_VIEW_NAME, CS_LOV_VIEW_NAME — the underlying data sources used by the collection engine and LOV rendering.
• COLLECTION_PROGRAM_NAME, COLLECT_ADDTL_WHERE_CLAUSE, PULL_ADDTL_WHERE_CLAUSE — collection program and SQL predicate fragments.
• COLLECT_LEVEL_ID / COLLECT_FLAG (×8) — per-dimension collection settings for dimension codes DCS, PRD, GEO, and others.
• CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE — standard EBS who/audit columns.

Common Use Cases and Queries

Typical uses include validation reports for stream definitions, data-warehouse extraction into planning engines, and configuration audits before running collection programs.

SELECT cs_definition_id, name, cs_type_desc, measurement_type,
       dcs_collect_level_id, dcs_collect_flag
  FROM apps.msd_cs_definitions_v1
 WHERE valid_flag = 'Y';

A dimension-focused query, matching the dimension_code search, inspects the detail synonym directly:

SELECT cs_definition_id, dimension_code, collect_level_id, collect_flag
  FROM apps.msd_cs_defn_dim_dtls
 WHERE dimension_code IN ('DCS','PRD','GEO');

Integration queries join the definition view to collection runs via CS_DEFINITION_ID for auditing, while lookup-driven queries filter on CS_TYPE_DESC to isolate a given stream source. Because the pivot uses outer joins, IS NULL predicates on dimension columns reliably identify definitions lacking a configured dimension. Note that the view text is truncated after cs_defini in the documented metadata; consulting the deployed schema for the complete join list is advisable before relying on later dimension aliases.