Search Results msd_cs_definitions_u1

Overview

The MSD.MSD_CS_DEFINITIONS table is a seed data table in the Oracle E-Business Suite Demand Planning (MSD) schema that stores Custom Stream Definitions. Custom Streams are the mechanism by which Oracle Demand Planning defines, collects, and organizes demand and supply data streams used in forecasting and planning. Each row in this table describes a single stream definition, including its classification, source type, measurement characteristics, and the database views and concurrent programs used to collect its data. The table resides in the APPS_TS_SEED tablespace, consistent with its role as a configuration and seed data repository whose contents are typically delivered by Oracle or created during implementation setup rather than entered during routine transactional processing.

From a data modeling perspective, the heuristic Data Vault classification of this table is a hub. The business key is the CS_DEFINITION_ID, and the table sits at the center of a set of dependent child tables that reference it. This classification is offered as a modeling suggestion: in a Data Vault representation, MSD_CS_DEFINITIONS would function as a hub capturing the unique stream definition identities, while descriptive attributes such as CS_TYPE, MEASUREMENT_TYPE, and the various flags would more naturally reside in a satellite.

Key Information Stored

The table contains 47 documented columns. The most significant are:

• CS_DEFINITION_ID — NUMBER(15), the surrogate primary key (MSD_CS_DEFINITIONS_PK), generated from the MSD_CS_DEFINITIONS_S sequence. It is also the leading column of the unique index MSD_CS_DEFINITIONS_U1, paired with ZD_EDITION_NAME.
• NAME — VARCHAR2(30), the unique short name of the custom stream definition. It is a business-key candidate, forming MSD_CS_DEFINITIONS_U2 together with ZD_EDITION_NAME.
• DESCRIPTION — VARCHAR2(240), the descriptive text for the definition.
• CS_CLASSIFICATION — VARCHAR2(30), classifies the stream as DATA or OTHER.
• CS_TYPE — VARCHAR2(30), the stream source, validated against the FND_LOOKUP MSD_CS_STREAM_SOURCE_TYPE.
• MEASUREMENT_TYPE — VARCHAR2(30), the measurement supported by the definition, validated against the FND_CS_MEASUREMENT_TYPE lookup.
• SYSTEM_FLAG — VARCHAR2(30), indicates whether the stream is delivered by Oracle Demand Planning and is therefore not customer-modifiable.
• AGGREGATION_ALLOWED_FLAG / ALLOCATION_ALLOWED_FLAG — VARCHAR2(30), indicate whether the definition permits aggregation or allocation respectively.
• MULTIPLE_STREAM_FLAG — VARCHAR2(30), indicates whether multiple streams of this type may be collected for the same time period.
• STREAM_EDITABLE_FLAG — VARCHAR2(30), indicates whether the stream is editable.
• VALID_FLAG / ENABLE_FLAG — VARCHAR2(30), control validity and enablement of the definition.
• PLANNING_SERVER_VIEW_NAME, SOURCE_VIEW_NAME, CS_LOV_VIEW_NAME, COLLECTION_PROGRAM_NAME — VARCHAR2 columns that bind the definition to its underlying database views and the concurrent program used for data collection.
• COLLECT_ADDTL_WHERE_CLAUSE / PULL_ADDTL_WHERE_CLAUSE — additional filter predicates applied during collection and pull operations.
• Standard WHO/audit columns (CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN) plus concurrent program context columns (PROGRAM_ID, PROGRAM_APPLICATION_ID, REQUEST_ID, PROGRAM_UPDATE_DATE).
• ZD_EDITION_NAME — the editioning column used for Online Patching / Edition-Based Redefinition, which is why both unique indexes include it.

Common Use Cases and Queries

Implementation teams frequently query MSD_CS_DEFINITIONS to inventory the custom streams available for collection and to determine which are Oracle-delivered versus customer-defined. A typical query lists active, non-system definitions:

• Enumerate custom streams: SELECT cs_definition_id, name, description, cs_classification, cs_type FROM msd.msd_cs_definitions WHERE system_flag = 'N' AND valid_flag = 'Y';
• Distinguish Oracle-delivered streams: SELECT name, cs_type, measurement_type FROM msd.msd_cs_definitions WHERE system_flag = 'Y';
• Find collectable streams by type: filter on CS_TYPE against the MSD_CS_STREAM_SOURCE_TYPE lookup and on AGGREGATION_ALLOWED_FLAG or ALLOCATION_ALLOWED_FLAG depending on planning requirements.
• Trace collection configuration: select COLLECTION_PROGRAM_NAME, SOURCE_VIEW_NAME, and COLLECT_ADDTL_WHERE_CLAUSE to audit how each stream is populated during the planning data collection cycle.

Because the table is seed data, it is more often read than written. Reporting typically joins it to child tables by CS_DEFINITION_ID to resolve a stream's name and classification in collection logs, data headers, and dimension detail reports.

Related Objects

MSD_CS_DEFINITIONS is referenced by several dependent tables through CS_DEFINITION_ID foreign keys, and these relationships define its role as a hub:

• MSD_CS_DEFN_DIM_DTLS — dimension detail rows linked via CS_DEFINITION_ID, defining the dimensions attached to each stream definition.
• MSD_CS_DEFN_COLUMN_DTLS — column-level detail for each definition, joined on CS_DEFINITION_ID.
• MSD_CS_DATA_HEADERS — header records for collected stream data, keyed to the definition via CS_DEFINITION_ID.
• MSD_CS_DATA — the collected stream data itself, referencing CS_DEFINITION_ID.
• MSD_ST_CS_DATA — stage-level custom stream data, also joined on CS_DEFINITION_ID.

The unique index MSD_CS_DEFINITIONS_U1 (CS_DEFINITION_ID, ZD_EDITION_NAME) enforces the primary key, while MSD_CS_DEFINITIONS_U2 (NAME, ZD_EDITION_NAME) enforces uniqueness of the stream name. Any interface that resolves a stream name to its ID, or vice versa, should account for the editioning column ZD_EDITION_NAME to remain correct under Online Patching in EBS 12.2.2.