Results for “asf_forecast_prdws_histy_v”

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

Overview

The ASF_FORECAST_PRDWS_HISTY_V view belongs to the ASF - Sales Online module of Oracle E-Business Suite (12.1.1 and 12.2.2). It presents an extract of product forecast worksheet lines that have reached the SUBMITTED status, enriched with descriptive attributes drawn from resources, forecast categories, and sales credit types. In practice it functions as a historical / submitted forecast reporting layer over the underlying AS_PROD_WORKSHEET_LINES fact, resolving surrogate identifiers into human-readable names so that downstream reports and integrations do not have to join the reference tables themselves.

The view is implemented as a UNION ALL of two branches. The first branch resolves the owner of a worksheet line to an individual resource; the second resolves lines without an individual salesforce identifier to a sales group. Both branches apply the STATUS_CODE = 'SUBMITTED' predicate, so only committed forecast submissions are surfaced. Note that the ETRM documentation marks this object as "Not implemented in this database," indicating it may be absent or inactive in some environments despite being defined in the product data model.

Underlying Base Objects

Although the documented base object list is empty, the view text identifies four referenced objects:

• AS_PROD_WORKSHEET_LINES (aliased PW) — the primary source of worksheet line data and the driver of both union branches.
• JTF_RS_RESOURCE_EXTNS (RES) — supplies the individual resource name via SOURCE_NAME, joined on RESOURCE_ID = SALESFORCE_ID in the first branch.
• JTF_RS_GROUPS_TL (GTL) — supplies the group name via GROUP_NAME, joined on GROUP_ID = SALES_GROUP_ID and filtered by USERENV('LANG') in the second branch.
• AS_FORECAST_CATEGORIES (FC) — provides FORECAST_CATEGORY_NAME.
• ASO_I_SALES_CREDIT_TYPES_V (CT) — provides the credit type NAME, joined on SALES_CREDIT_TYPE_ID = CREDIT_TYPE_ID.

The join to ASO_I_SALES_CREDIT_TYPES_V is significant given the originating search; that view is the sales credit type reference used to translate the numeric CREDIT_TYPE_ID stored on the worksheet line into a display name.

Key Columns

• PROD_WORKSHEET_LINE_ID, FORECAST_WORKSHEET_ID — worksheet line and header identifiers.
• SALESFORCE_ID / FULLNAME — the resource or group that owns the line, resolved through the two union branches.
• PERIOD_NAME, CURRENCY_CODE — forecast period and currency context.
• FORECAST_CATEGORY_ID, FORECAST_CATEGORY_NAME — the forecast category classification.
• CREDIT_TYPE_ID, NAME — the sales credit type and its descriptive name from ASO_I_SALES_CREDIT_TYPES_V.
• Forecast amount metrics: PIPELINE_AMOUNT, WEIGHTED_PIPELINE_AMOUNT, WON_PIPELINE_AMOUNT, WORST_FORECAST_AMOUNT, FORECAST_AMOUNT, BEST_FORECAST_AMOUNT, each paired with a corresponding _FLAG column indicating whether the amount is populated or included in reporting.
• FST_SALES_CATEGORY_ID, INTEREST_TYPE_ID, LAST_UPDATE_DATE, LAST_UPDATED_BY — supporting classification and audit columns.

Common Use Cases and Queries

Typical usage is submitted-forecast reporting: extracting forecast amounts by resource, group, forecast category, and credit type, and feeding BI Publisher reports or ETL loads into a data warehouse.

• Forecast pipeline by owner and category for a period.
• Sales credit type breakdown of submitted forecasts.
• Reconciliation of individual versus group-owned worksheet lines.

SELECT fullname,
       forecast_category_name,
       name                AS credit_type,
       period_name,
       currency_code,
       forecast_amount,
       best_forecast_amount,
       worst_forecast_amount
FROM   asf_forecast_prdws_histy_v
WHERE  period_name = :p_period
ORDER  BY fullname, forecast_category_name;

Because the view already restricts to SUBMITTED lines and resolves names, queries against it avoid the multi-table join logic embedded in its definition, though callers should verify the object is actually deployed in their instance given the "not implemented" note in the documentation.