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.

For E-Business Suite Payroll and PeopleSoft North American Payroll, refer to the Help Topic ID 243 for more information.

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

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 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.

To create the batch:
  1. Log into Fusion Applications and navigate to the Payroll Administration area (Navigator, then Payroll, then Payroll Administration).
  2. In the Task pane select Batch Processing, then Batch Loader.
  3. Click the Download button to open the Batch Loader Spreadsheet, re-entering your login details as requested.
  4. In the Batch Header Sheet tab, enter a name for the batch and the Legislative Data Group, then click Save.
  5. Double-click the batch name to select the batch and open the Batch Content Sheet tab.
  6. Click the Add button and select the Add a Defined Balance action.
  7. Enter the details for each defined balance to be added to the BI Balance Group:

    • Line Sequence.

    • Attribute Definition – 'Global BI Attribute'.

    • Legislative Data Group – as entered in step 4.

    • Balance Dimension – balance dimension name; this should be a simple run balance without any contexts.

    • Balance Type – balance type name for the defined balance

  8. Click Save.

Transferring the Batch

Once you create a batch, you can transfer it.

  1. In Fusion Applications navigate to the Checklists page (Navigator, then Payroll, then Checklists).
  2. In the Task pane select Payroll Flows, then Submit a Process or Report.
  3. Select the Legislative Data Group for the batch.
  4. Select the Transfer Batch process and click Next.
  5. Give a name for the Payroll Flow.

    For the batch parameter, select the batch name you indicated.

  6. Submit the process.

Adding Payroll Balances to Balance Groups for PeopleSoft Global Payroll

You can add payroll balances to Payroll balance groups for PeopleSoft Global Payroll.

  1. Navigate to Setup HRMS, then Product Related, then Global Payroll & Absence Mgmt, then Elements, then Element Groups, then Add a New Value.
  2. Provide the Name of the Element Group as 'GLOBAL BI BALGRP' and provide any meaningful description.
  3. Click on Element Group Members tab.
  4. Add the Earnings/Deductions that need to be extracted into Oracle Business Analytics Warehouse, to the Element Group created.

    The Global Payroll ETL is configured to extract only the Earnings/Deductions that are assigned to the Element Group GLOBAL BI BALGRP. The screenshot shows how to assign Earnings/Deductions to an Element Group.

    Element Group Name dialog.
    Description of the illustration GUID-5CE353D1-3131-4977-92BC-5BCA9A3556F1-default.gif

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).