Saturday, May 2, 2015

Query to Find out all Earning and Deduction Elements and values after running Payroll

select ppx.employee_number "Employee Number",
         ppx.full_name "Employee Name",  
         to_char(ppa.effective_date,'dd/mm/yyyy') "Effective Date",
         ptp.period_name "Period Name",  
         sum(decode(pecv.classification_name,'Earnings', prrv.result_value,0)) Earnings,
         sum(decode(pecv.classification_name,'Voluntary Deductions',
                                         prrv.result_value, 'Involuntary Deductions',
                                         prrv.result_value, 'Employer Charges',
                                         prrv.result_value, 0
                            )
               ) Deductions
  from per_people_x ppx,
          per_assignments_x pax,
          pay_assignment_actions paa,
          pay_payroll_actions ppa,
          pay_run_results prr,
          pay_run_result_values prrv,
          pay_element_types_f petf,
          pay_input_values_F pivf,
          per_time_periods ptp, '
          pay_element_classifications_vl pecv
where ppx.person_id = pax.person_id
    and pax.assignment_id = paa.assignment_id
    and paa.assignment_action_id = prr.assignment_action_id 
    and ppa.payroll_action_id = paa.payroll_action_id
    and prr.element_type_id = petf.element_type_id
    and pivf.element_type_id = petf.element_type_id
    and prrv.run_result_id = prr.run_result_id
   and prrv.input_value_id = pivf.input_value_id
   and ptp.time_period_id = ppa.time_period_id
   and petf.classification_id = pecv.classification_id
   and pivf.name = 'Pay Value'
   and ppx.person_id = 5
group by ppx.employee_number,
          ppx.person_id,
          ppx.full_name,
          ppa.time_period_id,
          ppa.effective_date,
          ptp.period_name,
          pax.organization_id;