Search Results as_forecast_calendar

Overview

APPS.MSD_SR_SALES_FCST_V is a source view in the MSD (Demand Planning) product family within Oracle E-Business Suite, validated in both 12.1.1 and 12.2.2. Its documented purpose is to serve as the Source View for Sales Forecast, exposed for retrieval of sales forecast information from an Oracle Applications instance into the Demand Planning or Advanced Supply Chain Planning (ASCP) collections. The SR prefix denotes a "source" or staging view intended to be read by the collections engine rather than queried transactionally by end users. Because the view is created in the APPS schema, it inherits the standard APPS synonym resolution and grants applied to concurrent collection programs such as MSD_SR_ML_* and the MSC/MSD collections manager.

The view normalizes heterogeneous forecast sources — opportunity worksheets and product (internal) forecasts — into a single canonical shape, applying the standardized demand-planning column layout used by all MSD source views (numeric dimension identifiers, measure columns, and date boundaries). Monetary measures are currency-normalized at the point of extraction.

Underlying Base Objects

The documented base objects referenced by the view are synonymous within APPS and include:

• AS_OPP_WORKSHEET_LINES — opportunity/sales-worksheet forecast lines, the primary source for the sales forecast measures.
• AS_PROD_WORKSHEET_LINES and AS_INTERNAL_FORECASTS — product and internal forecast lines combined via UNION ALL with the opportunity branch.
• AS_FORECAST_CATEGORIES — provides FORECAST_CATEGORY_NAME, the forecast category (pipeline, best case, commit, and so on).
• GL_PERIODS — supplies START_DATE, END_DATE, and PERIOD_TYPE, and drives the currency conversion date.
• MSD_SETUP_PARAMETERS — the row naming parameter AS_FORECAST_CALENDAR determines the forecast calendar (period set name) that filters GL_PERIODS.
• JTF_RS_GROUPS_B and JTF_RS_GRP_RELATIONS — an inline resource-group subquery excludes parent groups (relation type PARENT_GROUP) that are currently active and not deleted.
• MSD_SR_UTIL — a package supplying surrogate key generators (GET_ALL_PRD_PK, GET_ALL_ORG_PK, GET_ALL_SCS_PK, GET_ALL_REP_PK, GET_ALL_GEO_PK, GET_NULL_PK) and CONVERT_GLOBAL_AMT, which converts amounts from the source currency to the global planning currency.
• MTL_DEFAULT_CATEGORY_SETS and ENI_DENORM_HIERARCHIES — referenced for item/category and hierarchy resolution used by the unions.

The dominant filter set restricts rows to forecast lines with STATUS_CODE = 'SUBMITTED', END_DATE_ACTIVE IS NULL, and SALESFORCE_ID IS NULL, joined to the configured forecast calendar.

Key Columns

• BEST_FORECAST_AMOUNT — the best-case forecast value; this is the column most often targeted by searches for "best_forecast_amount". It is computed as OWL.BEST_FORECAST_AMOUNT * MSD_SR_UTIL.CONVERT_GLOBAL_AMT(CURRENCY_CODE, GL_PERIODS.END_DATE), so the stored figure is currency-normalized to the global (planning) currency.
• FORECAST_AMOUNT, WORST_FORECAST_AMOUNT — the forecast and worst-case amounts, converted on the same basis.
• PIPELINE_AMOUNT, WEIGHTED_PIPELINE_AMOUNT — raw and weighted pipeline values, likewise converted.
• START_DATE, END_DATE — calendar period boundaries from GL_PERIODS; the period year/quarter/month type is encoded as 3/4/5 respectively via DECODE.
• FORECAST_CATEGORY_NAME — descriptive forecast category from AS_FORECAST_CATEGORIES.
• Leading numeric literals identify the source system and dimension semantics (28/15/29/33/32 for the opportunity branch, 4/30/29/33 for the product branch), enabling the collections engine to map columns positionally.

Common Use Cases and Queries

Typical consumption is diagnostic — reconciling what the MSD collections process will extract for exchange with Demantra/Demand Planning. A representative query isolating the best-case forecast by category and period is:

• SELECT forecast_category_name, start_date, end_date, SUM(best_forecast_amount) FROM apps.msd_sr_sales_fcst_v WHERE end_date BETWEEN :p_from AND :p_to GROUP BY forecast_category_name, start_date, end_date ORDER BY start_date;
• SELECT * FROM apps.msd_sr_sales_fcst_v WHERE best_forecast_amount > 0 AND forecast_category_name = :category;
• SELECT start_date, SUM(forecast_amount), SUM(best_forecast_amount), SUM(worst_forecast_amount) FROM apps.msd_sr_sales_fcst_v GROUP BY start_date;

Because the view performs the currency conversion and surrogate-key generation at runtime, performance depends on the filtered period range; queries should always constrain START_DATE/END_DATE against GL_PERIODS to avoid full scans across the forecast calendar.