The Forecast fact table is based on PA_BUDGET_LINES. A filter is applied to the Budget Version table to extract only baselined Forecasts for the Forecast fact. The grain of this table is a Forecast line. The ETL extracts only baselined forecasts, so the records in this table are not updated after they are loaded to Oracle Business Analytics Warehouse; only new records are inserted during an incremental run.
Forecasts are stored in the Budget dimension (W_PROJ_BUDGET_D) as well.
Note:
For E-Business Suite, Transaction Currency is the Document Currency for this fact.Defining Filters on Forecast Metrics
Users can create multiple forecasts for a single Project and multiple versions for the same forecast type. Therefore, Oracle BI Applications filter all exposed metrics using the following filters:
Primary Forecast Type: One project can have only one Cost Forecast with a forecast type of 'Primary Cost Forecast' and one Revenue Forecast with a Forecast type of 'Primary Revenue Forecast.' Therefore, all Cost and Revenue Forecast metrics are filtered on two flags, Primary Cost Forecast and Primary Revenue Forecast, to make sure we are showing data for only one forecast.
Current or Original Forecast: One Project forecast can have multiple versions. To show only one forecast version at a time, every metric for the Current Version and the Current Original Version is shown. These flags are set automatically in OLTP when the forecast is baselined, but users can update them manually.
Users can still view metrics for any other forecast type or version by bringing the non-filtered metrics from the Fact - Project Forecast fact table into the Presentation area. But to avoid duplicate data, the report must have a filter on Dim - Project Forecast Version.Forecast Type and Dim - Project Forecast Version.Forecast Version.
Before running the ETL for the first time, access the Financial Plan Type page in the HTML client, and select your Primary Cost forecast and Primary Revenue forecast types.
Forecasts Created in Forms Client
For Forecasts entered through the Form client, the PA_BUDGET_ TYPES.PLAN_TYPE column is not populated for the two predefined budget types, 'FC' and 'FR'. Therefore, the following ETL logic is incorporated in SDE_ORA_ProjectBudgetDimension_BudgetType.W_PROJ_BUDGET_DS in the SDE_ORA_ProjectBudgetDimension folder:
DOMAIN_DEFAULT_UNASSIGNED( TO_CHAR(case when ISNULL(SQ_PA_BUDGET_VERSIONS.PLAN_TYPE) then DECODE(SQ_PA_BUDGET_VERSIONS.BUDGET_TYPE_CODE1,'AC','BUDGET','AR','BUDGET','FC','FORECAST','FR', 'FORECAST',SQ_PA_BUDGET_VERSIONS.PLAN_TYPE else SQ_PA_BUDGET_VERSIONS.PLAN_TYPE end ) )
For 'FC' and 'FR' types of Forecast versions created in the Forms client, the PRIMARY_COST_FORECAST _FLAG and PRIMARY_REV_FORECAST_FLAG are not populated in PA_BUDGET_VERSIONS. Therefore, the following ETL logic is incorporated in SDE_ORA_ProjectBudgetDimension_BudgetType.W_PROJ_BUDGET_DS in the SDE_ORA_ProjectBudgetDimension folder:
COALESCE(SQ_PA_BUDGET_VERSIONS.PRIMARY_COST_FORECAST_FLAG, case when SQ_PA_BUDGET_VERSIONS.BUDGET_TYPE_CODE1 = 'FC' THEN 'Y' ELSE NULL END) COALESCE(SQ_PA_BUDGET_VERSIONS.PRIMARY_REV_FORECAST_FLAG, case when SQ_PA_BUDGET_VERSIONS.BUDGET_TYPE_CODE1 = 'FR' THEN 'Y' ELSE NULL END)
For Forms based forecasts, even though the application does not allow the creation of forecast lines in a different currency than the Project Functional currency, we are defaulting the Project Functional Currency in the Document Currency field, so that the Forecast Amounts can also be analyzed in the Global Currencies. For example Doc EAC Raw Cost Amount is populated as:
COALESCE(SQ_PA_BUDGET_LINES.TXN_RAW_COST,IIF(SQ_PA_BUDGET_LINES.TXN_CURRENCY_CODE = SQ_PA_BUDGET_LINES.PROJFUNC_CURRENCY_CODE, SQ_PA_BUDGET_LINES.RAW_COST,NULL))
Forecast Fact Canonical Date: The Forecast fact has the following two sets of Accounting Date and Period WIDs:
PROJ_ACCT_START_DT_WID, PROJ_ACCT_END_DT_WID & PROJ_PERIOD_WID
PROJ_ACCT_START_DT_WID and PROJ_ACCT_END_DT_WID are populated using START_DATE and END_DATE of forecast line only for Forecasts that are time phased using the Project Accounting (PA) Calendar.
GL_ACCT_START_DT_WID, GL_ACCT_END_DT_WID and GL_PERIOD_WID
The GL_ACCT_START_DT_WID and GL_ACCT_END_DT_WID are populated using START_DATE and END_DATE of forecast line for Forecasts time phased by the General Ledger (GL) Calendar.
For Forecasts with a Time Phase equal to 'P' (PA), 'N' (No Time Phase), or 'R' (Date Range), the GL_ACCT_START_DT_WID and GL_PERIOD_WID are resolved using the START_DATE of the forecast line by choosing the Period containing that date in the corresponding GL Calendar.
This approach assumes that for time phase equal 'P', 'N' or 'R', there will always be a period containing the START_DATE for the given GL Calendar in OLTP database.