Configuration of the Project Revenue Fact for E-Business Suite consists of these tasks.
Actual Costs are extracted from the Cost Distribution Lines table in the Project Costing module in E-Business Suite and loaded into the Cost Line Fact (W_PROJ_COST_LINE_F) table.
For E-Business Suite, Transaction Currency is the Document Currency for this fact.
Business Suite concurrent programs (such as PRC: Distribute Labor Costs and PRC: Distribute Usage and Miscellaneous Costs) for distributing Cost should be run before running the ETL to load Oracle Business Analytics Warehouse. If the Cost Distribution program is not run before every incremental ETL run, the data in Cost Distribution Fact will not be synchronized with the actual expenditures in the Expenditure Fact table.
Expenditure Fact
The Expenditure Fact (W_PROJ_EXP_LINE_F) is based on PA_EXPENDITURE_ITEMS_ALL. It shows the actual expenditure data before distribution. This fact should be used by customers who do not distribute their Expenditure on a daily basis, but who have some users who need to see a frequently updated view of Expenditure data.
The GL Date is assigned to the Cost Distribution Line only (during Cost distribution) and not to the Expenditure Item records. Therefore, the Expenditure data can only be analyzed by the Enterprise Calendar dimension and not by the GL calendar. Also, the Expenditure data cannot be analyzed by the GL Account because the GL account is associated only when the data is distributed.
Cost Fact Canonical Date
The Canonical Date dimension for the Cost fact is based on the PRVDR_GL_DATE from Distribution Line table, whereas the Canonical Date dimension for the Expenditure fact is based on the EXPENDITURE_DATE from the Expenditure Items table.
The multi calendar date dimension contains calendars for multiple organizations. It is essential that all records in a report analyzing data by the Fiscal Calendar (Dim - Fiscal Calendar) point to the same calendar. For this reason, all reports in the dashboard are filtered on the Project Business Unit. To make all Cost records in a Project Business Unit point to the same calendar, the RCVR_GL_DATE and RCVR_PA_DATE columns are used to populate the GL_ACCOUNTING_DT_WID and PROJ_ACCOUNTING_DT_WID columns in the fact table respectively. Expenditure OU view (in Cost Fact) can be built using Enterprise Calendar as well.
About Domain Values for Cost Fact
The Project Cost Transfer Status has been modeled as a domain value and can be configured in FSM.
Incremental Logic for Cost Fact
The incremental extract logic for the Cost fact table depends on the 'REQUEST_ID' field of the Cost Distribution Lines table. The W_PROJ_ETL_PS parameter table facilitates this logic.
Using a separate ODI interface, the maximum Request Id in the source table at the time of the ETL run is stored in this table, which is subsequently used to populate the SDE task (SDE_ORA_PROJECTCOSTLINE) level ODI variable #EBS_REQUEST_ID_1. It is initialized using the following query:
SELECT COALESCE((SELECT PRE_REQUEST_ID FROM QUALIFY_DS(W_PROJ_ETL_PS) WHERE TBL_NAME = 'PA_COST_DISTRIBUTION_LINES_ALL'),0) FROM_DUAL()
If you are missing Cost records in W_PROJ_COST_LINE_F after an incremental update, download patch 9896800 from My Oracle Support. The Tech Note included with the patch explains the scenarios where this can happen, and the proposed solution.
The Project Cost aggregate table (W_PROJ_COST_A) is used to capture information about the project cost distributions for the expenditure items. You need to configure the Project Cost Lines aggregate table before the initial ETL run and subsequent incremental ETL.
Before the initial ETL run, you need to configure the COST_TIME_GRAIN parameter in FSM for the time aggregation level in the Project Cost Lines aggregate fact table.
By default, the COST_TIME_GRAIN parameter has a value of PERIOD. The possible values for the COST_TIME_GRAIN parameter are:
PERIOD
QUARTER
YEAR
The Project Cost Lines aggregate table is fully loaded from the base table in the initial ETL run. The table can grow to millions of records. Therefore, the Project Cost aggregate table is not fully reloaded from the base table after each incremental ETL run. The Oracle Business Analytics Warehouse minimizes the incremental aggregation effort by modifying the aggregate table incrementally as the base table is updated, as described below.
Actual Revenue Line records are extracted from the Revenue/Event Distribution Lines tables (PA_CUST_REV_DISTRIB_LINES_ALL and PA_CUST_EVENT_DIST_ALL) in the Project Costing module in E-Business Suite and are loaded into the Revenue Line Fact (W_PROJ_REVENUE_LINE_F) table.
For E-Business Suite, Revenue Transaction Currency Code is the Document Currency Code for this fact.
Note:
E-Business Suite concurrent programs (such as PRC: Generate Draft Revenue for a Single Project or PRC: Generate Draft Revenue for a Range of Projects) for distributing revenue should be run before the ETL is run to load Oracle Business Analytics Warehouse.For the Revenue Header Fact (W_PROJ_REVENUE_HDR_F), the primary source is the PA_DRAFT_REVENUES table. Revenue line metrics, such as Bill and Revenue amounts, are aggregated in this table as well.
Revenue Fact Canonical Date
The Canonical Date dimension is based on the GL_DATE from the Draft Revenues table.
Revenue Facts Staging Table
This is a common staging table that loads both the header and the line level revenue fact tables.
Revenue Fact Multicurrency Support
Some metrics such as Unearned Revenue, Unbilled Receivables, Realized Gains, and Realized Losses are only available in Local Currency and Global Currencies. There are three columns in w_proj_revenue_line_f and w_proj_revenue_hdr_f respectively for revenue amounts in global currencies.
Revenue Fact Domain Values
The project revenue status has been modeled as a domain value and can be configured in FSM.
Incremental Logic for Revenue Fact
The incremental extract logic for the Revenue fact table depends on the REQUEST_ID field of the Revenue Distribution Lines table. The W_PROJ_ETL_PS parameter facilitates this logic, and through a separate ODI process, the maximum Request Id in the source table at the time of the ETL run is stored in this table, which is subsequently used to populate the following variables for the SDE_ORA_ProjectRevenueLine task in ODI:
#EBS_REQUEST_ID_2
This variable is initialized using this query:
SELECT COALESCE((SELECT COALESCE(PRE_REQUEST_ID,0) FROM QUALIFY_DS(W_PROJ_ETL_PS) WHERE TBL_NAME ='PA_CUST_EVENT_RDL_ALL'),0) FROM_DUAL()
#EBS_REQUEST_ID_4
This variable is initialized using this query:
SELECT COALESCE((SELECT COALESCE(PRE_REQUEST_ID,0) FROM QUALIFY_DS(W_PROJ_ETL_PS) WHERE TBL_NAME ='PA_CUST_REV_DIST_LINES_ALL'),0) FROM_DUAL()
#EBS_REQUEST_ID_4
This variable is initialized using this query:
SELECT COALESCE((SELECT COALESCE(PRE_REQUEST_ID,0) FROM QUALIFY_DS(W_PROJ_ETL_PS) WHERE TBL_NAME ='PA_DRAFT_REVENUES_ALL'),0) FROM_DUAL()
The Project Cost aggregate table (W_PROJ_REVENUE_A) is used to capture information about the project revenue distributions. You need to configure the Project Revenue Lines aggregate table before the initial ETL run and subsequent incremental ETL.
Before the initial ETL run, you need to configure the REVENUE_TIME_GRAIN parameter in FSM for the time aggregation level in the Project Revenue Lines aggregate fact table.
By default, the REVENUE _TIME_GRAIN parameter has a value of PERIOD. The possible values for the REVENUE_TIME_GRAIN parameter are:
PERIOD
QUARTER
YEAR
The Project Revenue Lines aggregate table is fully loaded from the base table in the initial ETL run. The table can grow to millions of records. Therefore, the Project Revenue aggregate table is not fully reloaded from the base table after each incremental ETL run. The Oracle Business Analytics Warehouse minimizes the incremental aggregation effort by modifying the aggregate table incrementally as the base table is updated.