You are currently viewing SQL Query to pull the Payroll Balance Feeds
SQL Query to pull the Payroll Balance Feeds

In this article we will look into how to pull the Payroll Balance Feed details.

Payroll balances show the accumulation of values over a period of time. Payroll processes, such as Calculate Payroll and Calculate Gross Earnings, update the balance values. The values can be an amount, hours, or any other numeric value. You manage balance definitions through the Balances task in the Payroll Calculation work area.

Most of the balances we require are predefined, and additional balances are automatically created when we create elements. We can edit these balance definitions or create additional definitions for calculations or reporting.

A Payroll balance can be fed by multiple elements, some of them contributing positively and some of them contributing negatively to make up the total balance.

SQL Query:

select pbt.balance_name, pbt.base_balance_name, 
       pet.element_name, pet.base_element_name, input_name, piv.base_name base_input_name, 
       decode(pbf.scale, 1, 'Add', -1, 'Subtract') feed_scale, 
       to_char(pbf.effective_start_date, 'yyyy-mm-dd') feed_start_date,
       to_char(pbf.effective_end_date, 'yyyy-mm-dd') feed_end_date, ldg_name, pbf.legislation_code
  from pay_balance_types_vl pbt, 
       pay_balance_feeds_f pbf, 
       pay_element_types_vl pet, 
       pay_input_values_vl piv, 
       per_legislative_data_groups_vl ldg
 where pbt.balance_type_id = pbf.balance_type_id
   and pbf.input_value_id = piv.input_value_id
   and piv.element_type_id = pet.element_type_id
   and pbf.effective_start_date between piv.effective_start_date
                                    and piv.effective_end_date
   and pbf.effective_start_date between pet.effective_start_date
                                    and pet.effective_end_date
   and pbt.legislation_code = ldg.legislation_code
   and pbt.balance_name like nvl(:Param_BalanceName, '%')
   and pbf.legislative_data_group_id = ldg.legislative_data_group_id
order by 1, 3, 5, 7
If you like the content, please follow us on LinkedInFacebook, and Twitter to get updated with the latest content.