Search Results msd_opportunity_data_v




Overview
Explore in AI Chat

Overview

MSD_OPPORTUNITY_DATA_V is a PL/SQL view owned by the APPS schema and shipped as part of the MSD – Demand Planning module in Oracle E-Business Suite 12.1.1 and 12.2.2. It is not a conventional functional view; it is a user-extensible interface view that exposes Opportunity History data stored in the Advanced Supply Chain Planning / Demand Planning staging tables. The view is defined over MSD_CS_DATA and translates the generic, positional ATTRIBUTE_n columns of the staging table into meaningful planning dimensions such as inventory organization, item, customer, sales channel, sales representative, ship-to location, quantity, amount, and end date.

The defining characteristic of this view is that it is not stripped by demand plan ID. Unlike many MSD views that are automatically filtered for a specific plan, MSD_OPPORTUNITY_DATA_V returns the complete set of opportunity data. This design allows customers to create a custom view that further restricts the rows, then pass that custom view name as a parameter to a demand plan. The view therefore acts as a template that controls which opportunity records feed the planning engine.

Underlying Base Objects

The view joins the synonym MSD_CS_DATA (aliased MBD) to five instances of the synonym MSD_LEVEL_VALUES (aliased ORG_LVL, ITM_LVL, CUS_LVL, CHN_LVL, REP_LVL, and GEO_LVL). A subquery against MSD_CS_DEFINITIONS restricts rows to the correct data collection definition. MSD_CS_DATA stores opportunity and consumption history in a flattened form; MSD_LEVEL_VALUES holds the dimension member values and surrogate keys that decode the internal level identifiers; MSD_CS_DEFINITIONS defines each collection program and its identifiers.

Each dimension join is driven by the instance identifier MBD.ATTRIBUTE_1 matched to INSTANCE, followed by the appropriate ATTRIBUTE_n pair matched to LEVEL_ID and SR_LEVEL_PK. This pattern decodes the raw stored keys into human-readable level values while preserving the surrogate primary keys required by the planning engine.

Key Columns

Common Use Cases and Queries

The principal use case is to build a restriction view for a demand plan. A planner creates a custom view that selects from MSD_OPPORTUNITY_DATA_V with a WHERE clause limiting data by inventory organization, customer, or date range, then supplies that view name as a parameter when defining the demand plan. The following examples illustrate typical access patterns.

To review all opportunities for a specific inventory organization:

SELECT INV_ORG, ITEM, CUSTOMER, QUANTITY, AMOUNT, END_DATE
FROM APPS.MSD_OPPORTUNITY_DATA_V
WHERE INV_ORG = :p_org;

To aggregate opportunity demand by organization and item within a date window:

SELECT INV_ORG, ITEM, SUM(QUANTITY) TOTAL_QTY, SUM(AMOUNT) TOTAL_AMT
FROM APPS.MSD_OPPORTUNITY_DATA_V
WHERE END_DATE BETWEEN :start_date AND :end_date
GROUP BY INV_ORG, ITEM;

Because the view is unstripped by plan ID, queries against it may return rows belonging to multiple data collections. Analysts should join or filter on criteria consistent with MSD_CS_DEFINITIONS when plan-specific isolation is required. The view is read-only and intended for query and parameterization rather than direct DML.