About Configuring Project Budget Fact for E-Business Suite

In E-Business Suite, baselined Budgets are extracted into the Budget Fact (W_PROJ_BUDGET_F) table, the grain of which is Budget Line. Because only the baselined budgets are extracted, the records in this table are not updated after they are loaded into Oracle Business Analytics Warehouse; only new records are inserted during the incremental ETL run. Budgets are stored in the Budget dimension (W_PROJ_BUDGET_D).

Note:

For E-Business Suite, Transaction Currency is the Document Currency for this fact.

Defining Filters on Budget Metrics

A user can create multiple budgets for a single Project and multiple versions for the same budget type. Therefore, all exposed metrics are filtered by these filters:

  • Approved Budget Type

    A project can have only one Cost Budget with a budget type as 'Approved Cost Budget' and one Revenue Budget with a budget type as 'Approved Revenue Budget.' Therefore, all Cost Budget metrics are filtered by the Approved Cost Budget and Approved Revenue Budget flags to ensure that the metrics include data from one budget only.

  • Current or Original Budget

    Each Project forecast can have multiple versions. The Current Version might not be the same as the Original version. Therefore, to show only one Forecast version at a time, there are separate metrics for Current version and Original version. These flags are set automatically in OLTP when the forecast is baselined, but users can update them manually.

The user can still see the metrics for any other budget type or version by bringing the non-filtered metrics from Fact - Project Budget fact table into the Presentation area. But to avoid duplicate data, the report must have a filter on 'Dim - Project Budget Version.Budget Type' and 'Dim - Project Budget Version.Budget Version'.

Before running the ETL for the first time, then go to the Financial Plan Type page in the HTML application and set your Approved Cost Budget Type and your Approved Revenue Budget Types.

Budgets Created in Forms Client

For budgets entered through the Form client, the PA_BUDGET_ TYPES.PLAN_TYPE column is not populated for the two predefined budget types, AC and AR. Therefore, the following ETL logic is incorporated in interface 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 )

Budget Fact Canonical Date

The Budget Fact contains the following two sets of Accounting Date and Period WIDs:

  • PROJ_ACCT_START_DT_WID, PROJ_ACCT_END_DT_WID, and PROJ_PERIOD_WID

    PROJ_ACCT_START_DT_WID and PROJ_ACCT_END_DT_WID are populated using START_DATE and END_DATE of budget line only for budgets 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 the START_DATE and END_DATE of budget line for budgets that are time-phased by the General Ledger (GL) Calendar.

    For budgets defined with Time Phase equal '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 budget line by choosing the period containing that date in the GL Calendar (pinned by the GL_MCAL_CAL_WID).

    This approach assumes that for time phase 'P','N', and 'R', there is a period containing the START_DATE for the given GL Calendar in the OLTP database.

For Forms -based budgets, even though the application does not allow creating budget lines in a different currency than the Project Functional currency, the currency from Project Functional Currency is used for the default value of the Document Currency field. This enables Budget Amounts to be analyzed in the Global Currencies. For example, Doc 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))