Results for “asf_forecast_oppws_submit_v”

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

Overview

ASF_FORECAST_OPPWS_SUBMIT_V is a reporting view within the ASF – Sales Online product family of Oracle E-Business Suite (applicable to both 12.1.1 and 12.2.2). It consolidates submitted forecast worksheet opportunity lines together with the associated lead, trading partner, sales stage, status, and resource information. Its defining characteristic is the hardcoded predicate OW.STATUS_CODE = 'SUBMITTED', which restricts the result set to worksheet lines that have been formally submitted for forecast processing. The view therefore functions as a purpose-built data source for downstream processes that must act only on committed forecast submissions, such as forecast roll-up, pipeline analytics, and integration exports to external CRM or forecasting engines.

Because the view is a join of multiple transactional and reference tables rather than a base table, it is read-only and carries no independent storage. It presents a denormalized, human-readable projection of forecast data, resolving surrogate identifiers (lead, party, resource, stage, status) into descriptive attributes.

Underlying Base Objects

The view is defined over six objects joined through their primary and foreign key relationships:

• AS_OPP_WORKSHEET_LINES OW — the driving table, holding individual opportunity worksheet lines and their forecast amounts, stage, status, and resource assignments.
• AS_LEADS_ALL LEAD — joined on OW.LEAD_ID = LEAD.LEAD_ID, supplying the originating lead and its customer reference.
• HZ_PARTIES HZP — joined on LEAD.CUSTOMER_ID = HZP.PARTY_ID, providing trading partner name and type.
• AS_STATUSES_VL STATUS — joined on OW.STATUS = STATUS.STATUS_CODE, translating the status code into a user-facing meaning.
• AS_SALES_STAGES_ALL_VL STAGE — joined on OW.SALES_STAGE_ID = STAGE.SALES_STAGE_ID, resolving the sales stage name. This is the object most directly relevant to a search for sales_stage.
• JTF_RS_RESOURCE_EXTNS PA — joined on OW.RESOURCE_ID = PA.RESOURCE_ID, supplying the salesperson's name and resource identifier.

Note that the documented metadata records no separately enumerated base objects; the relationships above are derived from the view text.

Key Columns

The view exposes worksheet identifiers (OPP_WORKSHEET_LINE_ID, FORECAST_WORKSHEET_ID), audit columns (CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN), and forecast context columns (FORECAST_ID, FORECAST_CATEGORY_ID, SALES_GROUP_ID, PERIOD_NAME, CURRENCY_CODE, STATUS_CODE).

Sales-stage information appears as SALES_STAGE_ID and the resolved SALES_STAGE, alongside WIN_PROBABILITY, DECISION_DATE, and FORECAST_DATE. Party data includes PARTY_ID, PARTY_NAME, and PARTY_TYPE. Resource data is exposed as EMPLOYEE_NUMBER, FIRST_NAME, LAST_NAME, and SOURCE_ID. Amount columns follow a consistent flag/amount pairing: WEIGHTED_AMOUNT_FLAG/WEIGHTED_AMOUNT, TOTAL_AMOUNT_FLAG/TOTAL_AMOUNT, WORST_FORECAST_AMOUNT, FORECAST_AMOUNT, and BEST_FORECAST_AMOUNT.

Common Use Cases and Queries

Typical uses include submitted-forecast pipeline reporting, stage-weighted revenue projections, and reconciliation of forecast worksheets by salesperson or trading partner.

SELECT SALES_STAGE,
       PERIOD_NAME,
       CURRENCY_CODE,
       SUM(TOTAL_AMOUNT)    TOTAL_AMOUNT,
       SUM(WEIGHTED_AMOUNT) WEIGHTED_AMOUNT
FROM   ASF_FORECAST_OPPWS_SUBMIT_V
GROUP  BY SALES_STAGE, PERIOD_NAME, CURRENCY_CODE;

SELECT PARTY_NAME,
       SALES_STAGE,
       WIN_PROBABILITY,
       FORECAST_AMOUNT,
       BEST_FORECAST_AMOUNT,
       WORST_FORECAST_AMOUNT
FROM   ASF_FORECAST_OPPWS_SUBMIT_V
WHERE  PERIOD_NAME = :period
ORDER  BY FORECAST_AMOUNT DESC;

Because the SUBMITTED predicate is embedded, no additional status filter is required by consumers; the view already guarantees only submitted worksheet lines are returned.