Search Results currency_amount




Overview
Explore in AI Chat

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:

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.