Results for “asf_opp_prd_mv”

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

Overview

ASF_OPP_PRD_MV is a materialized view object owned by the APPS schema within the Oracle E-Business Suite environment, associated with the ASF – Sales Online product family (Oracle Sales Foundation). In EBS 12.1.1 and 12.2.2, this object serves the sales opportunity and forecasting domain, aggregating opportunity product and product-category level data used by sales managers, forecast rollup engines, and pipeline reporting. The "_MV" suffix indicates it is materialized, meaning the row set is physically stored and refreshed on a defined schedule rather than resolved at query time, which improves performance for aggregation-heavy sales dashboards.

Under a heuristic Data Vault classification derived from the foreign-key structure, this object is best modeled as a standalone construct — effectively a satellite-style aggregate without a hub dependency clearly exposed in the documented key set. The modeling suggestion is to treat it as a derived, denormalized reporting structure rather than a normalized transactional entity; consumers should not treat it as a source-of-truth hub for opportunity identity.

Key Information Stored

The documented physical schema for 12.1.1 exposes 15 columns. The most significant are:

• PRODUCT_CATEGORY_ID and PRODUCT_CAT_SET_ID — identify the product category and its category set, forming the core business-key candidate for grouping opportunities by product classification.
• LEADS and BUDGET — quantitative measures capturing lead counts and budgetary amounts used in pipeline and forecast analysis.
• WIN_LOSS_INDICATOR — flags whether the associated opportunity resulted in a win or loss, central to win/loss ratio reporting.
• FORECAST_ROLLUP_FLAG and OPP_OPEN_STATUS_FLAG — drive whether a row participates in forecast rollups and whether the opportunity remains open.
• MANAGER_PERSON_ID and PARENT_RESOURCE_ID — resource/person references supporting sales hierarchy and manager-level rollup views.
• CONVERSION_PERIOD_TYPE and CONVERSION_PERIOD_NAME — define the time bucket over which currency and forecast conversion is applied.
• TO_CURRENCY_CODE — the target currency for converted amounts.
• SALESFORCE_ID — foreign key to AS_SALESFORCE, linking the aggregate to a sales force definition.
• CREDIT_TYPE_ID — foreign key to IGS_FI_CR_TYPES_ALL, tying rows to a credit type classification.

No explicit surrogate primary key is documented in the metadata excerpt; business-key uniqueness is implied by the combination of product category, period, and sales resource attributes.

Common Use Cases and Queries

Typical usage centers on sales pipeline and forecast reporting. A representative pattern filters by period and rollup flag:

• Aggregating budget and lead counts by period: SELECT CONVERSION_PERIOD_NAME, SUM(BUDGET), SUM(LEADS) FROM ASF_OPP_PRD_MV WHERE FORECAST_ROLLUP_FLAG = 'Y' GROUP BY CONVERSION_PERIOD_NAME;
• Win/loss analysis by product category: filtering on WIN_LOSS_INDICATOR and grouping by PRODUCT_CATEGORY_ID.
• Manager rollups joining on MANAGER_PERSON_ID to the resource hierarchy for territory-based dashboards.
• Salesforce linkage reporting via the SALESFORCE_ID join to AS_SALESFORCE.

Because the object is materialized, consumers should account for refresh latency and query the base opportunity tables when real-time accuracy is required.

Related Objects

• AS_SALESFORCE — joined on ASF_OPP_PRD_MV.SALESFORCE_ID = AS_SALESFORCE.SALESFORCE_ID.
• IGS_FI_CR_TYPES_ALL — joined on ASF_OPP_PRD_MV.CREDIT_TYPE_ID = IGS_FI_CR_TYPES_ALL.CREDIT_TYPE_ID.
• ASF_OPP_PRD_MV base opportunity and opportunity-product tables (ASF_OPP_PRD / opportunity lines) that feed the materialized view refresh.
• PER_ALL_PEOPLE_F — for resolving MANAGER_PERSON_ID to a person name.
• FND_CURRENCIES — to validate and describe TO_CURRENCY_CODE.

These relationships position ASF_OPP_PRD_MV as a reporting-oriented aggregate linking sales opportunity product data to sales force and credit-type reference dimensions.