Payroll Balance Listing

Description

Columns: Payroll Name, Period Name, Pay Advice Date, Full Name, Assignment Number, Balance Name, Amount ...

Lists payroll balances for employees between two dates.
Uses standard HR security.

Copy

select payr.payroll_name,
       ptp.period_name,
       ptp.pay_advice_date,
       ppf.full_name,
       paf.assignment_number,
       pbt.balance_name,
       nvl(sum(prrv.result_value * pbf.scale),0) amount
  from pay_payroll_actions    ppa,
       pay_assignment_actions      paa,
       pay_run_results             prr,
       pay_run_result_values       prrv,
       pay_balance_feeds_f         pbf,
       pay_balance_types           pbt,
       per_assignments_f           paf,
       per_people_f                ppf,
       per_time_periods            ptp,
       pay_payrolls_x              payr
 where ptp.time_period_id = ppa.time_period_id
   and paa.payroll_action_id = ppa.payroll_action_id
   and paa.assignment_id = paf.assignment_id
   and paf.person_id = ppf.person_id
   and prr.assignment_action_id = paa.assignment_action_id
   and pbf.balance_type_id = pbt.balance_type_id
   and pbf.input_value_id = prrv.input_value_id
   and payr.payroll_id = ppa.payroll_id
   and prr.status in ('P','PA')
   and prrv.run_result_id = prr.run_result_id
   and ppa.effective_date between ppf.effective_start_date and ppf.effective_end_date
   and ppa.effective_date between paf.effective_start_date and paf.effective_end_date
   and ptp.pay_advice_date between :date_from and :date_to
   and (payr.payroll_id = :payroll_id or :payroll_id is null)
group by payr.payroll_name,
         ptp.period_name,
         ptp.pay_advice_date,
         ppf.full_name,
         paf.assignment_number,
         pbt.balance_name
order by payroll_name, period_name, full_name, assignment_number, balance_name