The OPA Project Budget Fact table (W_PROJ_BUDGET_F) is linked to the Financial Resource Dimension (W_FINANCIAL_RESOURCE_D) which provides information such as Expenditure Category and Expenditure Type within OBIEE. The Project Budget Fact table is linked to the Financial Resource Dimension by Expenditure Type. This linkage is performed in the ETL process, within the SDE_ORA_ProjectBudgetFact, by sourcing the PA_RESOURCE_ASSIGNMENTS table and pulling the EXPENDITURE_TYPE field. The EXPENDITURE_TYPE value is used later in the SIL mapping to link up with the Financial Resource Dimension.
The approach described above works fine if the budgeting on the EBS side is being done down to the Expenditure Type level which would populate the Expenditure Type in the PA_RESOURCE_ASSIGNMENTS table. At my client, a resource list named ‘Expenditure Category’ has been created with a group resource type id = 108 (RESOURCE_CLASS_CODE= PRE_DEFINED, RESOURCE_TYPE_CODE = EXPENDITURE_CATEGORY, TABLE_NAME = PA_EXPENDITURE_TYPES_RES_V). This grouping is configured for the budget entry screens and the users are limited to selecting only Expenditure Categories. The Expenditure Category selected actually hangs off of the PA_RESOURCE_ASSIGNMENTS table in the PA_RESOURCE_LIST_MEMBERS table. The result is that the Expenditure Category and Expenditure Type fields in PA_RESOURCE_ASSIGNMENTS are NULL and the Expenditure Category field in PA_RESOURCE_LIST_MEMBERS is populated with the value chosen. The EXPENDITURE_TYPE in PA_RESOURCE_LIST_MEMBERS is also NULL.
Because of this approach for entering budgets at the Expenditure Category level, the OPA ETL Process is not pulling through any Financial Resource Dimension information through and all the Project Budget Fact records end up with a Financial Resource WID = 0 and all expenditure categories end up with an ‘Unspecified’ value.
Is OPA setup in some way to handle the situation above. Our requirement is to be able to access the expenditure category information that is being entered for these budgets. With what we can see for the Financial Resource dimension, it is not coming over that way. Is there a configuration change we can make at the ETL level to adjust this such that the expenditure category information will come over? Is there another location we should be looking to to access this information?
At this point we are looking at making a number of customizations to pull through the expenditure category information from the PA_RESOURCE_LIST_MEMBERS table for the budget lines, but we are hoping to avoid this complexity and additional effort if something exists out of the box to handle this .. either through configuration or in another location.