Results for “asfv_sales_opportunities”

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

Overview

ASFV_SALES_OPPORTUNITIES is a reporting view belonging to the AS (Sales Foundation) product within Oracle E-Business Suite. In the 12.1.1 and 12.2.2 releases, Sales Foundation underpins opportunity and lead management functionality that feeds the Oracle Sales and Oracle Telesales applications. This view consolidates lead and opportunity data held in the AS_LEADS_ALL table with descriptive attributes drawn from related lookup, customer, address, currency, price list, promotion, sales stage, and forecast probability sources.

The view presents a denormalized, presentation-ready projection of an opportunity: the lead number, its source, status, channel, decision date, win probability, total amount, currency, close reason, competitor, and descriptive text are exposed alongside the associated customer name and number, city, customer and address status, sales stage name, and organization code. Rather than requiring report authors or integration developers to assemble a dozen joins manually, ASFV_SALES_OPPORTUNITIES supplies the joined result set directly. It is typically consumed for pipeline reporting, territory and forecast analysis, and outbound extracts. Note that the ETRM implementation metadata records the object as "Not implemented in this database," meaning it exists in the application definition but is not deployed in every environment; availability must be verified in each target instance.

Underlying Base Objects

The view text identifies its sources explicitly. The driving table is AS_LEADS_ALL (aliased LEAD), which stores the lead and opportunity records. It is outer-joined to MTL_PARAMETERS (PARA) and HR_ALL_ORGANIZATION_UNITS (ORG) on ORG_ID to resolve the operating unit and organization name; to FND_CURRENCIES_VL (CUR) on CURRENCY_CODE; to SO_PRICE_LISTS (SOPL) on PRICE_LIST_ID; to AS_PROMOTIONS_ALL (PROM) on SOURCE_PROMOTION_ID; to AS_SALES_STAGES_ALL (STAGE) on SALES_STAGE_ID; to AS_FORECAST_PROB_ALL (PROB) for the probability meaning; and to three instances of RA_CUSTOMERS (CUST, CUST2, CUST3) and two instances of RA_ADDRESSES_ALL (ADDR, ADDR2) to return customer and end-user customer/address details.

The ETRM record lists no separately documented referenced base objects, so the view text itself is the authoritative definition. Because almost every join is an outer join (+), the view preserves lead rows even when related lookup or master data is missing.

Key Columns

• LEAD_NUMBER — the human-readable opportunity identifier.
• LEAD_SOURCE_CODE — decoded against AS_LOOKUPS (LEAD_SOURCE) to return the source meaning.
• STATUS — decoded against AS_LOOKUPS (LEAD_STATUS).
• CHANNEL_CODE — decoded against SO_LOOKUPS (SALES_CHANNEL).
• WIN_PROBABILITY, TOTAL_AMOUNT, CURRENCY_CODE — core pipeline metrics.
• DECISION_DATE — expected close/decision date.
• CLOSE_REASON, CLOSE_COMPETITOR, CLOSE_COMMENT — outcome detail for closed opportunities.
• STAGE.NAME, MIN/MAX_WIN_PROBABILITY, PROB.MEANING — sales stage and forecast probability descriptors.
• CUSTOMER_NAME, CUSTOMER_NUMBER, CITY, CUSTOMER STATUS, ADDRESS STATUS — customer and location context.
• ORGANIZATION_CODE, ORG.NAME — operating unit identification.
• ID columns — LEAD_ID, CUSTOMER_ID, ADDRESS_ID, SALES_STAGE_ID, PRICE_LIST_ID, and related foreign keys retained for drill-down.
• WHO columns — CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY for audit and incremental extraction.

Common Use Cases and Queries

Typical usage includes pipeline reporting by stage and probability, territory and organization rollups, and incremental extracts keyed on LAST_UPDATE_DATE.

• Opportunity pipeline by organization and stage.
• Win/loss analysis using CLOSE_REASON and CLOSE_COMPETITOR.
• Forecast weighting via WIN_PROBABILITY and TOTAL_AMOUNT.
• Incremental integration extracts filtered on LAST_UPDATE_DATE.

SELECT LEAD_NUMBER, CUSTOMER_NAME, CITY,
       NAME AS STAGE, WIN_PROBABILITY,
       TOTAL_AMOUNT, CURRENCY_CODE, DECISION_DATE
FROM   ASFV_SALES_OPPORTUNITIES
WHERE  ORGANIZATION_CODE = :p_org
AND    STATUS = 'OPEN'
ORDER BY DECISION_DATE;

Because the view depends on AS_LEADS_ALL and multiple outer joins, query performance benefits from filtering on indexed columns such as ORG_ID-derived ORGANIZATION_CODE, LEAD_NUMBER, and LAST_UPDATE_DATE rather than on decoded lookup meanings.