How to store balance values in PAY_RUN_BALANCES table?

To store a balance in PAY_RUN_BALANCES, the column SAVE_RUN_BALANCE on PAY_DEFINED_BALANCES must be set to ‘Y’. This column will not be displayed on the user interface.

Reference : Balance Reporting Architecture Technical Brief (Phase 1) (Doc ID 245295.1)

By default, the Status field will be blank.

Query to get balance dimension details (Q1):

select pbt.balance_name,pbd.DIMENSION_NAME,pdb.DEFINED_BALANCE_ID,pdb.SAVE_RUN_BALANCE from
pay_defined_balances pdb
,pay_balance_types pbt
,pay_balance_dimensions pbd
where pbt.balance_type_id = pdb.balance_type_id
and pbd.BALANCE_DIMENSION_ID = pdb.BALANCE_DIMENSION_ID
and pbt.balance_name = ‘XX Test Earnings’;

Run payroll.

Check balance value in PAY_RUN_BALANCES table

Query to fetch balance value for an assignment_action_id (Q2):

select pbt.balance_name,pdb.SAVE_RUN_BALANCE,prb.* from PAY_RUN_BALANCES prb
,pay_assignment_actions paa
,pay_payroll_actions ppa
,pay_defined_balances pdb
,pay_balance_types pbt
where 1=1
and paa.assignment_action_id = :p_assignment_action_id
and prb.assignment_action_id = paa.assignment_action_id
and ppa.payroll_action_id = paa.payroll_action_id
and pdb.DEFINED_BALANCE_ID = prb.DEFINED_BALANCE_ID
and pbt.balance_type_id = pdb.balance_type_id;

The new balance is not inserted into PAY_RUN_BALANCES.

Update SAVE_RUN_BALANCE column to Y in table PAY_DEFINED_BALANCES.

update pay_defined_balances
set SAVE_RUN_BALANCE = ‘Y’
where DEFINED_BALANCE_ID = :p_defined_balance_id;  /*get defined_balance_id from Q1*/

Re-query the Balance and click on Dimensions

The Status field will become Invalid

The Status will show as Invalid because of the below reason (Reference Doc ID 245295.1):

Submit the concurrent program Generate Run Balances

After successful completion of the program, Re-query the balance and click on Dimensions

The Status field will become Valid

 Rerun Payroll

Query the PAY_RUN_BALANCES table through the query Q2

The new balance is inserted into PAY_RUN_BALANCES.