Search Results collect_level_id




Overview
Explore in AI Chat

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

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.