About Configuring the Payroll Balance Filter
This Parameter is used to selectively extract the balances in to the Warehouse. By limiting the balances extracted, the performance of ETL and reports will be improved. In addition, only certain types of balance are suitable for including in the warehouse. You should only extract run balances, as other types of balances might not be fully additive (for example year-to-date balances cannot be added together).
Both in case of E-Business Suite Payroll and PeopleSoft North American payroll, the Customer has to be provided a mechanism to choose the balances (in case of E-Business Suite Payroll) and earnings/deductions/taxes (in case of PeopleSoft North American Payroll) to be tracked in the Pay Run Balance Detail fact table.
To ensure addivity of measures we will only support run balances. For each payroll run, the actual run balances processed will be stored. Because we are not breaking these down by context we can combine run balances across time to form higher level balances, for example, PTD, MTD, YTD.
Optional or Mandatory
Optional for E-Business Suite Payroll and PeopleSoft North American Payroll, but is highly recommended.
Applies to
E-Business Suite Payroll and PeopleSoft North American Payroll.
Dependency
None.
Instructions
Create a custom table in the OLTP system with the list of balances that need to be extracted for reporting. The SDE ETL will extract only these balances from the source system. For example:
CREATE TABLE OBIA_PAY_BAL_FILTER (BALANCE_ID VARCHAR2 (50));
A parameter HR_PAYROLL_FILTER_CLAUSE is added in ODI which will have a SELECT statement from the custom table that the customer has created in the source system, as shown below.
SELECT <COLUMN_NAME> FROM <SCHEMA>.<TABLE_NAME>
For example: SELECT BALANCE_ID FROM EMDBO.OBIA_PAY_BAL_FILTER
If the customer does not choose to create a custom table in the source system, the SDE extract will fetch all the balances and this could lead to performance issues.
If you need to extract all balances, then you must set this parameter to 1=1 (this is the default value on installation).
You set the value for variable HR_PAYROLL_FILTER_CLAUSE using Oracle BI Applications Configuration Manager.
For e-Business Suite Payroll or PeopleSoft North American Payroll, you use the following settings:
-
To Filter Balances, use SELECT<COLUMN_NAME> FROM <TABLE_NAME>.
-
To Extract All Balances, use 1=1.
HR_PAYROLL_FILTER_CLAUSE parameter in ODI
This parameter is set to refresh the value from Oracle BI Applications Configuration Manager.
HR_PAYROLL_FILTER_CLAUSE parameter in Configuration Manager
