Results for “asf_frcst_sum_reprt_v”

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

Overview

ASF_FRCST_SUM_REPRT_V is a reporting view owned by the APPS schema within the ASF – Sales Online product family in Oracle E-Business Suite 12.1.1 and 12.2.2. It presents a denormalized, aggregated forecast summary across sales opportunities (leads), sales credits, and opportunity worksheet lines, enabling users to report on forecasted revenue at the level of sales representative, sales group, customer, and opportunity. The view is structured specifically for forecast reporting and pipeline analysis, joining opportunity records to their associated sales credit and multi-currency conversion data. Because the object is a view rather than a table, it holds no data of its own; it materializes results dynamically from its base objects at query time.

Underlying Base Objects

The view is defined over three documented base objects, all referenced through APPS synonyms:

• AS_SALES_CREDITS_DENORM (SYNONYM) — aliased as V in the view text. This denormalized sales credit object supplies the opportunity-level attributes, including lead identifiers, opportunity description, sales group, sales representative name, customer, lead number, sales stage, status, channel, win probability, and interest/credit type information.
• AS_MC_SALES_CREDITS_DEN (SYNONYM) — aliased as MC. This object provides multi-currency sales credit amounts and conversion details, including conversion type, from/to currency codes, conversion period and date, total amount, sales credit amount, won amount, and weighted amount.
• AS_OPP_WORKSHEET_LINES (SYNONYM) — aliased as AOWL. This object contributes opportunity worksheet line identifiers and forecast figures (forecast amount, worst forecast amount, and best forecast amount).

The join conditions link V.SALES_CREDIT_ID to MC.SALES_CREDIT_ID, with MC.TYPE restricted to the value 'PERIOD'. The opportunity worksheet lines are outer-joined to the sales credit data on LEAD_ID and EMPLOYEE_PERSON_ID, with an additional predicate limiting rows to those where AOWL.END_DATE_ACTIVE is null or earlier than SYSDATE. Aggregation is applied using SUM over the multi-currency amount columns and MAX over the conversion status flag, grouped by the descriptive sales credit attributes.

Key Columns

The view exposes a broad set of columns. Notable examples include:

• SALES_REP_NAME — the name of the sales representative associated with the opportunity, the column most directly sought by users searching for sales_rep_name.
• LEAD_ID, LEAD_NUMBER, OPP_DESCRIPTION — identifiers and description for the underlying opportunity or lead record.
• SALES_GROUP_NAME, SALES_GROUP_ID, SALESFORCE_ID — organizational and territory attributes for attribution.
• CUSTOMER_NAME, CUSTOMER_ID, CUSTOMER_CATEGORY, PARTY_TYPE — customer and party classification data.
• SALES_STAGE, SALES_STAGE_ID, STATUS, STATUS_CODE, WIN_PROBABILITY, DECISION_DATE — pipeline and forecast-stage metrics.
• TOTAL_AMOUNT and SUM(NVL(SALES_CREDIT_AMOUNT, 0)), SUM(NVL(WON_AMOUNT, 0)), SUM(NVL(WEIGHTED_AMOUNT, 0)) — aggregated monetary forecast values in the reporting currency context.
• FORECAST_AMOUNT, WORST_FORECAST_AMOUNT, BEST_FORECAST_AMOUNT — worksheet-derived best/worst/expected forecast figures.
• CONVERSION_TYPE, TO/FROM_CURRENCY_CODE, CONVERSION_PERIOD_NAME — multi-currency conversion context.

Common Use Cases and Queries

Typical uses include forecast reporting by sales representative, pipeline analysis by sales group or stage, and reconciliation of forecasted versus weighted amounts. A representative query to retrieve forecast totals per representative:

• SELECT sales_rep_name, SUM(total_amount) FROM apps.asf_frcst_sum_reprt_v GROUP BY sales_rep_name;
• SELECT sales_rep_name, sales_group_name, SUM(weighted_amount) FROM apps.asf_frcst_sum_reprt_v WHERE sales_stage = :1 GROUP BY sales_rep_name, sales_group_name;
• SELECT lead_number, opp_description, forecast_amount, best_forecast_amount FROM apps.asf_frcst_sum_reprt_v WHERE sales_rep_name = :rep;

Because the view aggregates multi-currency rows, consumers should apply currency and period filters where cross-currency comparisons are required. Access should be granted via the APPS schema or an appropriate reporting responsibility.