Search Results as_sales_forecast_uwq_v




Overview
Explore in AI Chat

Overview

AS_SALES_FORECAST_UWQ_V is a reporting view owned by the APPS schema in Oracle E-Business Suite, classified under the AS - Sales Foundation product family. Its documented purpose is to expose sales forecast data to the Universal Work Queue (UWQ) framework, the component of Oracle's CRM and telephony infrastructure that pushes prioritized work items to agent desktops. In this role, the view functions as a lightweight, query-optimized projection of forecast records rather than as a transactional interface. It presents one row per internal forecast record, decorated with a resource identifier, a resource classification, and a constant object-code literal of 'FORECAST'. This last detail is significant: UWQ consumes heterogeneous work-item sources, and the constant allows UWQ to distinguish forecast-driven rows from other work-item types without additional joins.

The view is read-only and carries no data of its own. It is available and valid in both the 12.1.1 and 12.2.2 release streams, where its definition is materially identical. Because it is a view rather than a table, no DML is permitted against it, and any attempt to insert, update, or delete produces an Oracle error. This is consistent with its intended consumption pattern: reporting, worklist rendering, and integration extracts.

Underlying Base Objects

The view is defined over two underlying objects, both referenced through APPS synonyms:

  • AS_INTERNAL_FORECASTS (INF) — the primary driver table, holding the forecast header records including forecast identifier, period, status, sales force assignment, and the three forecast amount variants.
  • JTF_RS_RESOURCE_EXTNS (RES) — the resource extension table from the Foundation resource model, providing the category attribute used to classify the owning sales resource.

The join is expressed as INF.SALESFORCE_ID = RES.RESOURCE_ID(+), an outer join anchored on the forecast table. Consequently, forecasts whose sales force identifier does not resolve to a resource extension row are still returned, with NULL resource classification. The DECODE expression maps the raw category value to two UWQ-facing tokens, 'RS_EMPLOYEE' and 'RS_PARTNER'; any other or missing category yields NULL.

Key Columns

  • FORECAST_ID — Primary identifier for the forecast record, carried directly from AS_INTERNAL_FORECASTS. Used as the UWQ work-item key.
  • PERIOD_NAME — The forecast period label, typically a calendar or fiscal period name used for time-bucketing and period-over-period reporting.
  • STATUS_CODE — Lifecycle status of the forecast, supporting filtering of open versus closed or submitted forecasts.
  • RESOURCE_ID — The sales resource or sales force identifier, sourced from INF.SALESFORCE_ID. Despite the column name, it represents the forecast owner rather than a UWQ agent in the strict sense.
  • RESOURCE_TYPE — The decoded classification, resolving to 'RS_EMPLOYEE' or 'RS_PARTNER'.
  • OBJECT_CODE — A constant literal, 'FORECAST', used by UWQ for source discrimination.
  • RESOURCE_GROUP_ID — The sales group identifier, carried from INF.SALES_GROUP_ID, useful for territory and hierarchy reporting.
  • FORECAST_AMOUNT — The committed or baseline forecast value for the period.
  • BEST_FORECAST_AMOUNT — The optimistic case forecast, representing the upper confidence estimate.
  • WORST_FORECAST_AMOUNT — The pessimistic or conservative case forecast. This is the column most frequently targeted by ad hoc analysis, since it drives downside-risk and pipeline-adequacy assessments.

Common Use Cases and Queries

The principal use cases are UWQ worklist rendering, forecast-versus-actual reporting, and risk analysis centered on the worst-case estimate. A typical query retrieving all forecast amounts for a period follows:

  • SELECT forecast_id, period_name, status_code, resource_id, resource_type, forecast_amount, best_forecast_amount, worst_forecast_amount FROM apps.as_sales_forecast_uwq_v WHERE period_name = :period;

To isolate downside exposure by owner, filter and aggregate on the worst-case column:

  • SELECT resource_id, resource_type, SUM(worst_forecast_amount) worst_total FROM apps.as_sales_forecast_uwq_v WHERE period_name = :period GROUP BY resource_id, resource_type ORDER BY worst_total;

To compare the spread between best and worst case, the three amount columns can be combined arithmetically in a single pass, which is efficient because no additional joins are required beyond those already materialized in the view. Because the view performs an outer join to JTF_RS_RESOURCE_EXTNS, reports should treat RESOURCE_TYPE as nullable and avoid inner-join assumptions when classifying rows.

Queries should always constrain on PERIOD_NAME or STATUS_CODE where possible, since the view itself applies no filtering predicates and returns the full forecast population for the accessible operating unit.