Results for “msd_sr_opportunity_v”

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

Overview

MSD_SR_OPPORTUNITY_V is an APPS-owned source view registered in the ETRM repository under the MSD (Demand Planning) product family. Its documented purpose is to serve as the Source View for Sales Opportunity data extracted from an Oracle Applications instance and loaded into Oracle Demand Planning. The view functions as the staging interface layer between the transactional CRM/Order Capture schema and the demand planning collection programs, exposing lead line, interest code, item, organization, and customer attributes in a single denormalized projection suitable for bulk extraction.

In EBS 12.1.1 and 12.2.2, the delivered definition of this view is effectively disabled. The view text consists of a fully commented-out SELECT statement followed by a literal projection of NULL values over DUAL, constrained by a WHERE 1 <> 1 predicate. Consequently, the view is VALID from a compilation standpoint and returns no rows when queried in a standard instance. This is a deliberate design pattern: the real query is supplied as a template that implementers uncomment and adapt when the underlying CRM tables are populated and the customer elects to source opportunity data into Demand Planning.

Underlying Base Objects

Per the documented ETRM metadata for 12.2.2, the only referenced base object is the DUAL synonym, which is consistent with the shipped NULL-projection form of the view. The commented query block documents the intended join set that becomes active once the view is enabled. Those referenced objects are:

• AS_LEAD_LINES_ALL — lead line detail, providing quantity, UOM, amount, decision date, win probability, status, channel, and interest code foreign keys.
• AS_LEADS_ALL — lead header, providing customer, currency, initiating contact, and organization context.
• AS_INTEREST_TYPES and AS_INTEREST_CODES (aliased IC1 and IC2) — interest type and the primary/secondary interest code lookups. These are joined with the outer-join operator (+), indicating that interest data is optional on a lead line.
• MTL_SYSTEM_ITEMS_KFV — the concatenated-segment item key flexfield view, supplying the inventory item identifier and formatted item number.
• ORG_ORGANIZATION_DEFINITIONS — inventory organization identifier and name.
• RA_CUSTOMERS — customer identifier and name.

Helper functions from MSD_SR_UTIL are also invoked: UOM_CONV, CONVERT_GLOBAL_AMT, LOCATION, and SREP. These normalize units of measure, convert amounts to the global currency using the decision date, derive a location, and resolve the sales representative respectively.

Key Columns

The documented column list of the view is:

• LEAD_NUMBER — the lead identifier used as the opportunity key.
• INTEREST_TYPE_PK / INTEREST_TYPE — the interest type identifier and its descriptive name. This is the column family most directly associated with the user's search term "as_interest_codes", since INTEREST_TYPE is sourced from AS_INTEREST_TYPES while the code columns are sourced from AS_INTEREST_CODES.
• PRIMARY_INTEREST_CODE_PK / PRIMARY_INTEREST_CODE — the identifier and decoded CODE value of the lead line's primary interest code (IC1.CODE).
• SECONDARY_INTEREST_CODE_PK / SECONDARY_INTEREST_CODE — the identifier and decoded CODE value of the secondary interest code (IC2.CODE).
• SR_ITEM_PK / ITEM — inventory item identifier and concatenated segment string.
• SR_INV_ORG_PK / INV_ORG — inventory organization identifier and name.
• QUANTITY — UOM-converted lead line quantity.
• AMOUNT — total amount converted to the global currency.
• SR_CUSTOM — customer attribute carried for the planning load.

Common Use Cases and Queries

The primary use case is the Demand Planning collection of sales opportunity pipeline data, where lead lines are flattened into the planning interface for forecast enrichment. A secondary use case is diagnostic validation — confirming whether the view has been enabled in a given instance, since a disabled view returns zero rows.

A basic validation query:

• SELECT COUNT(*) FROM APPS.MSD_SR_OPPORTUNITY_V;

After the commented query is activated by the implementation team, a typical extraction filtering by interest code and item would resemble:

• SELECT LEAD_NUMBER, INTEREST_TYPE, PRIMARY_INTEREST_CODE, SECONDARY_INTEREST_CODE, ITEM, INV_ORG, QUANTITY, AMOUNT FROM APPS.MSD_SR_OPPORTUNITY_V WHERE PRIMARY_INTEREST_CODE = :p_code;

Because the shipped view is a stub, any query against an unmodified instance returns no data, and the view definition must be verified before relying on it for reporting or integration.