Search Results msd_opportunity_data_cs_v




Overview
Explore in AI Chat

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

Overview

MSD_OPPORTUNITY_DATA_CS_V is an APPS-owned reporting view within the Oracle E-Business Suite Demand Planning (MSD) module. It presents opportunity history data held in the flexible data stream entity MSD_CS_DATA, exposing a curated subset of generic attribute columns as business-meaningful fields. The view is a presentation layer over the consolidated data stream used by Demand Planning and related supply chain planning products, and it exists so that reporting tools, concurrent programs, and integration interfaces can query opportunity records without needing to understand the positional meaning of the underlying generic ATTRIBUTE_n columns.

The view is a filtered projection: it reads rows from MSD_CS_DATA whose CS_DEFINITION_ID corresponds to the definition named MSD_SALES_OPPORTUNITY. In Oracle EBS 12.1.1 and 12.2.2 the definition row is resolved through MSD_CS_DEFINITIONS, making the view independently addressable across environments where the numeric definition identifiers may differ. Its columns include the refresh and audit lineage fields — LAST_REFRESH_NUM and CREATED_BY_REFRESH_NUM — which are central to incremental processing and to answering the common user question of which collection run created a given opportunity record.

Underlying Base Objects

The view is defined over two referenced base objects, both exposed in APPS as synonyms:

  • MSD_CS_DATA — the flexible data stream entity that stores all consolidated planning records in a generic structure. Each row carries a CS_DEFINITION_ID, a CS_NAME, a set of positional ATTRIBUTE_n values, standard audit columns, refresh lineage columns, and an ACTION_CODE.
  • MSD_CS_DEFINITIONS — the metadata table that maps a human-readable stream name to its numeric CS_DEFINITION_ID.

The view joins by subquery rather than by outer join: it returns those MSD_CS_DATA rows where CS_DEFINITION_ID equals the identifier defined for the stream named 'MSD_SALES_OPPORTUNITY'. The underlying ATTRIBUTE columns are heterogeneous — most are stored as character strings, while ATTRIBUTE_43 is converted to a DATE using the format mask 'YYYY/MM/DD'. Because MSD_CS_DATA holds many streams, the filter on CS_DEFINITION_ID is what isolates opportunity history from all other flexible data stream content.

Key Columns

  • CS_DEFINITION_ID, CS_NAME — the stream definition identity and name for the row; together they identify the opportunity entity the record belongs to.
  • ATTRIBUTE_2, ATTRIBUTE_5, ATTRIBUTE_6, ATTRIBUTE_9, ATTRIBUTE_10, ATTRIBUTE_13, ATTRIBUTE_18, ATTRIBUTE_21, ATTRIBUTE_22 — mapped dimension and descriptive values for the opportunity record, interpreted according to the MSD_SALES_OPPORTUNITY stream definition.
  • ATTRIBUTE_25, ATTRIBUTE_26, ATTRIBUTE_29, ATTRIBUTE_30, ATTRIBUTE_33, ATTRIBUTE_34, ATTRIBUTE_41, ATTRIBUTE_42 — additional opportunity attributes such as quantity, amount, and level key values.
  • ATTRIBUTE_43 (TO_DATE) — the date attribute of the opportunity record, converted with the explicit format mask 'YYYY/MM/DD'.
  • CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN — standard EBS audit columns recording row insertion and last modification.
  • LAST_REFRESH_NUM — the refresh (collection) run number that last populated or updated the record; used to scope queries to a specific planning collection cycle.
  • CREATED_BY_REFRESH_NUM — the refresh number in which the record was first created. This is the column users typically search when they want to trace an opportunity record back to the original data refresh that introduced it.
  • ACTION_CODE — the operation associated with the row (for example insert, update, or delete), supporting change-consumption and interface processing.

Common Use Cases and Queries

The view is commonly queried to report opportunity history for a given refresh cycle, to reconcile which collection run created a record, and as a source for downstream extract or interface programs that consume the MSD data stream. A representative query returning records created by a specific refresh follows:

  • SELECT CS_NAME, ATTRIBUTE_2, ATTRIBUTE_43, CREATED_BY, CREATED_BY_REFRESH_NUM, LAST_REFRESH_NUM, ACTION_CODE FROM APPS.MSD_OPPORTUNITY_DATA_CS_V WHERE CREATED_BY_REFRESH_NUM = :refresh_num;
  • SELECT CS_DEFINITION_ID, ATTRIBUTE_5, ATTRIBUTE_6, TO_DATE(ATTRIBUTE_43,'YYYY/MM/DD') opp_date FROM APPS.MSD_OPPORTUNITY_DATA_CS_V WHERE ACTION_CODE = 'INSERT' AND LAST_REFRESH_NUM >= :from_refresh;
  • SELECT ACTION_CODE, COUNT(*) FROM APPS.MSD_OPPORTUNITY_DATA_CS_V GROUP BY ACTION_CODE;

When querying, note that the view is read-only, that ATTRIBUTE_n columns are string-typed unless explicitly converted, and that large volumes in MSD_CS_DATA make filtering on CS_DEFINITION_ID, refresh numbers, or action codes essential for acceptable performance.