Results for “asf_opp_top_mv”

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

Overview

ASF_OPP_TOP_MV is an APPS-owned materialized view registered as a table object in the Oracle E-Business Suite database, delivered under the ASF – Sales Online product family. It exposes opportunity and lead tracking information used by the Sales Online (Sales Foundation) reporting and dashboard layer, aggregating opportunity-level metrics such as revenue amounts, win/loss outcomes, pipeline status, and conversion details. The object is documented as VALID in the ETRM repository and contains 18 columns in the Oracle EBS 12.1.1 physical schema, a column count that carries forward to the 12.2.2 environment. Despite the _MV suffix, which normally denotes a materialized view, the object is catalogued as a TABLE type, reflecting how Oracle EBS registers snapshot segments and materialized view containers in the data dictionary.

From a Data Vault modeling perspective, the mined relationship metadata classifies ASF_OPP_TOP_MV as a standalone construct rather than a hub, link, or satellite. This heuristic reflects that the object behaves largely as a self-contained denormalized reporting structure carrying descriptive opportunity attributes and foreign-key references outward, without itself functioning as either a business-key hub or an associative link in a normalized warehouse model.

Key Information Stored

The most significant columns support opportunity analysis across several dimensions:

• LEAD_ID – The foreign-key reference to AS_LEADS_ALL, linking each materialized row to its originating sales lead and serving as the primary business-key candidate for joining.
• CUSTOMER_ID and CUSTOMER_NAME – Party identifiers and denormalized descriptive text for the account associated with the opportunity.
• PARTY_TYPE – Classifies the customer entity (for example, person versus organization) for segmentation.
• TOTAL_AMOUNT and CURRENCY_AMOUNT – The opportunity value metrics used in pipeline and forecast reporting.
• OPP_DESCRIPTION and SALES_STAGE – Textual opportunity detail and the current sales stage for funnel analysis.
• WIN_LOSS_INDICATOR, OPP_OPEN_STATUS_FLAG, and WIN_PROBABILITY – Outcome and probability fields underpinning win-rate, close-rate, and weighted-forecast calculations.
• DECISION_DATE – The anticipated or actual decision milestone for the opportunity.
• MANAGER_PERSON_ID and PARENT_RESOURCE_ID – Resource and hierarchy references supporting territory and roll-up reporting.
• CONVERSION_PERIOD_TYPE, CONVERSION_PERIOD_NAME, and TO_CURRENCY_CODE – Currency conversion context for normalized reporting amounts.
• STATUS – The record processing or lifecycle state.

No separate surrogate primary key column is documented in the ETRM extract; the identified foreign-key column LEAD_ID is the principal candidate for a business-key join back to AS_LEADS_ALL.

Common Use Cases and Queries

Typical usage centers on opportunity pipeline reporting, win/loss trend analysis, and currency-normalized revenue forecasting. A representative query pattern joining the object to its documented parent is:

• SELECT m.LEAD_ID, m.CUSTOMER_NAME, m.TOTAL_AMOUNT, m.SALES_STAGE, m.WIN_PROBABILITY FROM APPS.ASF_OPP_TOP_MV m WHERE m.OPP_OPEN_STATUS_FLAG = 'Y';
• Aggregating weighted forecast by stage: SELECT SALES_STAGE, SUM(TOTAL_AMOUNT * WIN_PROBABILITY) FROM APPS.ASF_OPP_TOP_MV GROUP BY SALES_STAGE;
• Joining to the lead source: ... FROM APPS.ASF_OPP_TOP_MV m, APPS.AS_LEADS_ALL l WHERE m.LEAD_ID = l.LEAD_ID;

Because it is a materialized container, refresh timing affects data latency, and reports should account for the underlying refresh schedule.

Related Objects

• AS_LEADS_ALL – The documented foreign-key parent referenced by ASF_OPP_TOP_MV.LEAD_ID, holding the consolidated lead records.
• AS_LEADS and associated lead extension tables – Underpin lead attribute derivation.
• AS_OPPORTUNITIES – Opportunity header data feeding pipeline metrics.
• AS_SALES_LEADS – Sales lead staging and qualification data.
• AS_RESOURCE_GROUPS / resource assignment objects – Support the PARENT_RESOURCE_ID and MANAGER_PERSON_ID roll-ups.
• FND_CURRENCIES – Provides validation for TO_CURRENCY_CODE conversion context.

The object's standalone classification and its single documented outbound reference make lead-to-opportunity lineage the principal dependency to track during upgrades from 12.1.1 to 12.2.2.