To extract payroll balances into Oracle Business Analytics Warehouse, the balances must be assigned to the BI Balance Group in the Fusion Applications system and Elements to Element Group in PeopleSoft Global Payroll.
For PeopleSoft North American Payroll and E-Business Suite Payroll, it is strongly recommended to create a custom table in the OLTP environment with all the balances/earnings/deductions/taxes that needs to be extracted into Oracle Business Analytics 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).
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
Mandatory for Fusion Payroll and PeopleSoft Global Payroll. Optional, but highly recommended for E-Business Suite Payroll and PeopleSoft North American Payroll.
For Fusion and Global Payroll, the ETL is configured to extract only the balances that are assigned to the 'BI Balance Group' and 'GLOBAL BI BALGRP' Element Group respectively.
Applies to
Fusion Payroll, PeopleSoft Global Payroll, PeopleSoft North American Payroll and E-Business Suite Payroll.
Tasks
Refer to the appropriate section for your source system:
Adding Payroll Balances to Balance Groups for Fusion Applications
Adding Payroll Balances to Balance Groups for PeopleSoft Global Payroll
Script for Adding Payroll Balances to Balance Groups for PeopleSoft North America Payroll
Script for Adding Payroll Balances to Balance Groups for E-Business Suite
You can add balances to the BI Balance Group for inclusion in Oracle Business Analytics Warehouse. More details are provided in the Payroll Administration documentation, which covers exceptions and verification reports to validate any setup.
Prerequisites:
Access to Oracle Fusion Applications Payroll Administration area.
Office 2007 with Oracle ADF 11g Plug In.
List of defined balances required to add to BI Balance Group.
Listed by Balance Dimension (which must be Run) and Balance Type.
Listed by Legislative Data Group.
You can add payroll balances to Payroll balance groups for PeopleSoft Global Payroll.
For PeopleSoft North American Payroll and E-Business Suite Payroll, it is strongly recommended to create a custom table in the OLTP environment with all balances/earnings/deductions/taxes that need to be extracted into Oracle Business Analytics Warehouse. You can use the PeopleSoft North American Payroll Custom Table script.
CREATE TABLE OBIA_PAY_BAL_FILTER (BALANCE_ID VARCHAR2 (50)); INSERT INTO OBIA_PAY_BAL_FILTER(BALANCE_ID) SELECT DISTINCT A.BALANCE_CODE FROM ( SELECT D.DEDCD AS BALANCE_CODE FROM PS_DEDUCTION_TBL D WHERE D.DEDCD IN ('401','B00-23','B10-02','B10-15','B10-16') UNION SELECT E.ERNCD AS BALANCE_CODE FROM PS_EARNINGS_TBL E WHERE E.ERNCD IN ('001','007','B14','B30') UNION SELECT S.ERNCD_SPCL AS BALANCE_CODE FROM PS_SPCL_EARNS_TBL S WHERE S.ERNCD_SPCL IN ('100','142','143','145') UNION SELECT ST.STATE AS BALANCE_CODE FROM PS_STATE_TAX_TBL ST WHERE ST.STATE IN ('AK','AL','AR','AS') UNION SELECT CT.PROVINCE AS BALANCE_CODE FROM PS_CAN_TAX_PROV CT WHERE CT.PROVINCE IN ('AB','BC','MB','NB') ) A; CREATE UNIQUE INDEX OBIA_PAY_BAL_FILTER_U1 ON OBIA_PAY_BAL_FILTER (BALANCE_ID);
Add all the Earnings/Deductions/Taxes in the IN clause of the above query respectively.
Use the E-Business Suite Payroll Custom Table script to add payroll balances to balance groups.
CREATE TABLE OBIA_PAY_BAL_FILTER (BALANCE_ID VARCHAR2 (50)); INSERT INTO OBIA_PAY_BAL_FILTER (BALANCE_ID) SELECT DISTINCT DB.DEFINED_BALANCE_ID FROM PAY_BALANCE_TYPES BT, PAY_DEFINED_BALANCES DB, PAY_BALANCE_DIMENSIONS BD WHERE BT.BALANCE_TYPE_ID = DB.BALANCE_TYPE_ID AND DB.BALANCE_DIMENSION_ID = BD.BALANCE_DIMENSION_ID AND BT.BALANCE_NAME IN ('Payments','Overtime','Regular Earnings','Regular Salary'); CREATE UNIQUE INDEX OBIA_PAY_BAL_FILTER_U1 ON OBIA_PAY_BAL_FILTER (BALANCE_ID);
BT.BALANCE_NAME IN ('Payments','Overtime','Regular Earnings','Regular Salary')
– List of All balances that need to be extracted into Oracle Business Analytics Warehouse).