About Adding Payroll Balances to BI Payroll Balance Group
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
Adding Payroll Balances to Balance Groups for Oracle Fusion Applications
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.
Adding Payroll Balances to Balance Groups for PeopleSoft Global Payroll
You can add payroll balances to Payroll balance groups for PeopleSoft Global Payroll.
Script for Adding Payroll Balances to Balance Groups for PeopleSoft North America 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.
Script for Adding Payroll Balances to Balance Groups for E-Business Suite
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).