Search Results itm_lvl

Overview

APPS.MSD_OPPORTUNITY_DATA_V is a reporting view in the Oracle EBS Advanced Planning / Demand Planning schema (MSD) that exposes sales opportunity data in a fully denormalized, human-readable form. The MSD schema stores dimensional information in a generic, key-based structure: a single fact table (MSD_CS_DATA) holds numeric and descriptive attributes, while MSD_LEVEL_VALUES holds the member values and primary keys for each dimension level. This view joins the two so that reporting tools, Discoverer workbooks, and custom interfaces can retrieve opportunity records without resolving surrogate keys manually.

The view is scoped to a single planning definition. A scalar subquery restricts output to rows whose CS_DEFINITION_ID corresponds to the definition named MSD_SALES_OPPORTUNITY in MSD_CS_DEFINITIONS, ensuring only opportunity-type records are returned. The view therefore behaves as a purpose-built reporting interface rather than a general-purpose fact access path.

Underlying Base Objects

The documented base objects are MSD_CS_DATA, MSD_CS_DEFINITIONS, and MSD_LEVEL_VALUES, all referenced through APPS synonyms. MSD_CS_DATA supplies the fact rows and the attribute_N columns that carry dimension identifiers, measures, and descriptive text. MSD_LEVEL_VALUES is joined six times — once per dimension level — to translate the generic instance/level_id/sr_level_pk triples stored in the fact table into readable values. MSD_CS_DEFINITIONS supplies the definition name used to filter the result set.

Each dimension join follows an identical three-part predicate: the instance (attribute_1) must match, the level identifier must match, and the source level primary key must match. Failure to align any one of these produces row multiplication or dropped rows, so the joins are effectively mandatory rather than optional lookups.

Key Columns

• INV_ORG / INV_ORG_PK — Inventory organization and its primary key (from org_lvl).
• ITEM / ITEM_PK — Item member and key (from itm_lvl).
• CUSTOMER / CUSTOMER_PK — Customer member and key (from cus_lvl).
• SALES_CHANNEL / SALES_CHANNEL_PK — Sales channel member and key (from chn_lvl).
• SALES_REP / SALES_REP_PK — Sales representative member and key, sourced via rep_lvl and the attribute_18 / attribute_19 / attribute_1 joins.
• SHIP_TO_LOC / SHIP_TO_LOC_PK — Ship-to location and key (from geo_lvl).
• USER_DEFINED1 / USER_DEFINED2 — Descriptive attributes mapped from attribute_28 and attribute_32; the corresponding _PK columns are hard-coded NULL.
• QUANTITY — Numeric opportunity quantity, converted from attribute_41 via TO_NUMBER.
• AMOUNT — Numeric opportunity amount, converted from attribute_42 via TO_NUMBER.
• TIME_LVL_ID — Time level identifier taken from attribute_34.
• END_DATE — Opportunity end date, converted from attribute_43 using the YYYY/MM/DD format mask.

Because QUANTITY, AMOUNT, and END_DATE rely on implicit conversions, any non-numeric or non-conforming source value will raise an ORA-01722 or ORA-01858 error at query time.

Common Use Cases and Queries

The view is typically used for opportunity pipeline reporting by sales representative, customer, item, or organization, and for feeding downstream analytics or extracts.

• Pipeline value by sales representative: aggregate AMOUNT grouped by SALES_REP.
• Opportunity detail by customer or item: filter on CUSTOMER or ITEM and select QUANTITY, AMOUNT, and END_DATE.
• Forecast window extracts: restrict rows by END_DATE to a reporting period.
• Channel analysis: group by SALES_CHANNEL and INV_ORG.

A representative query is:

SELECT sales_rep, customer, item, SUM(amount) total_amount, SUM(quantity) total_qty FROM apps.msd_opportunity_data_v WHERE end_date BETWEEN :p_from AND :p_to GROUP BY sales_rep, customer, item;

Regardless of how the view is queried, the joins to MSD_LEVEL_VALUES and the definition filter on MSD_SALES_OPPORTUNITY are applied internally, so callers receive only resolved opportunity rows. Queries should constrain END_DATE or the dimension columns to avoid full scans of the underlying fact table.