Results for “asf_win_won_top5_mv”

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

Overview

ASF_WIN_WON_TOP5_MV is a materialized view owned by the APPS schema within the Oracle E-Business Suite environment. It is a component of the ASF (Sales Online) product family, which underpins Oracle Sales and the HTML-based sales prospecting interface commonly deployed alongside Oracle Telesales and Oracle Sales for the 12.1.1 and 12.2.2 releases. The object aggregates forecast and opportunity information to support the "Top 5 Win/Won" style dashboards used by sales management, presenting a compact snapshot of the most significant opportunities expected to close within a defined period.

From a Data Vault modeling perspective, the standalone classification mined from the FK structure suggests treating this object as a satellite or an aggregate/reporting snapshot rather than a true hub or link. It does not act as an integration point across multiple business keys; instead it consolidates descriptive and quantitative attributes sourced from lead, salesforce, and credit-type dimensions.

Key Information Stored

The table exposes twenty documented columns that can be grouped into identity, dimensional, and financial categories:

• LEAD_ID — Foreign key to AS_LEADS_ALL; the primary business linkage to the opportunity/lead record being measured.
• SALESFORCE_ID — Foreign key to AS_SALESFORCE; identifies the owning sales organization rollup.
• CREDIT_TYPE_ID — Foreign key to IGS_FI_CR_TYPES_ALL; classifies the credit or revenue type associated with the amount.
• CUSTOMER_ID / PARTY_ID / PARTY_TYPE — Customer and trading partner identifiers supporting the customer/party dimension.
• CUSTOMER_NAME / OPP_DESCRIPTION — Descriptive attributes enabling display without additional joins.
• TOTAL_AMOUNT / CURRENCY_TOTAL_AMOUNT / CURRENCY_CODE — Value and currency of the opportunity, with normalized and original currency forms.
• PERIOD_TYPE / PERIOD_NAME — Calendar or forecast period classification, essential for time-bucketed reporting.
• PARENT_RESOURCE_ID / PARENT_GROUP_ID / SALES_GROUP_ID — Organizational hierarchy references for resource and group-level rollups.
• PRODUCT_CATEGORY_ID / PRODUCT_CAT_SET_ID — Product dimension references for category-level analysis.
• CLOSE_COMPETITOR — Competitor captured at close, supporting win/loss analysis.
• FORECAST_ROLLUP_FLAG — Indicator governing whether the row participates in forecast aggregation.

No surrogate primary key is documented; the row identity is effectively a composite of LEAD_ID plus the associated dimensional keys.

Common Use Cases and Queries

The materialized view primarily supports sales dashboards and pipeline reports. Typical reporting queries aggregate amounts by period and sales organization, for example:

• Top 5 opportunities by currency amount within the current forecast period.
• Pipeline rollups by PRODUCT_CATEGORY_ID or SALESFORCE_ID for management review.
• Win/loss analyses correlating CLOSE_COMPETITOR with realized totals.
• Cross-period trend reporting using PERIOD_NAME and PERIOD_TYPE.

A representative query joining back to the lead source would be:

SELECT m.customer_name, m.opp_description, m.currency_total_amount FROM apps.asf_win_won_top5_mv m WHERE m.period_name = :period AND m.salesforce_id = :salesforce ORDER BY m.currency_total_amount DESC;

Because it is a materialized view, refresh timing should be considered; scheduled refreshes keep the dashboard reasonably current while avoiding repeated scans of the underlying opportunity tables.

Related Objects

The most significant related objects are those referenced through the documented foreign keys:

• AS_LEADS_ALL — joined via LEAD_ID; the source of opportunity and lead detail.
• AS_SALESFORCE — joined via SALESFORCE_ID; defines the selling organization.
• IGS_FI_CR_TYPES_ALL — joined via CREDIT_TYPE_ID; provides revenue/credit type classification.
• AS_LEADS and related AS_% opportunity tables — the broader Sales Online data model that feeds this aggregate.
• HZ_PARTIES / HZ_CUST_ACCOUNTS — commonly joined on PARTY_ID and CUSTOMER_ID to resolve party names where the stored CUSTOMER_NAME is insufficient.

These relationships make ASF_WIN_WON_TOP5_MV a lightweight, pre-joined projection of the sales opportunity model, suited to high-frequency read access in the Oracle Sales Online interface.