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

SELECT

ppf.employee_number,

ppf.person_id,

ppf.full_name,

ppa.TIME_PERIOD_ID,

ppa.EFFECTIVE_DATE,

TP.PERIOD_NAME,

paf.ORGANIZATION_ID,

sum(decode(pec.CLASSIFICATION_NAME,’Earnings’,to_number(rrv.result_value),0)) Earnings,

sum(decode(pec.CLASSIFICATION_NAME,’Voluntary Deductions’,to_number(rrv.result_value),

                                  ‘Involuntary Deductions’,to_number(rrv.result_value),

                                  ‘Employer Charges’,to_number(rrv.result_value),

        0)

) Deductions

-- ety.element_name,ety.CLASSIFICATION_ID

-- PD.SEGMENT5  POSITION_NO,PD.SEGMENT6 POSITION_NAME,

FROM per_people_x ppf,

per_assignments_x paf,

pay_assignment_actions pas,

pay_payroll_actions ppa,

pay_run_results rr,

pay_run_result_values rrv,

pay_element_types_f ety,

pay_input_values_F I,

PER_TIME_PERIODS TP,

PAY_ELEMENT_CLASSIFICATIONS_VL pec

WHERE ppf.person_id = paf.person_id

AND paf.assignment_id = pas.assignment_id

AND pas.assignment_action_id = rr.assignment_action_id

AND ppa.payroll_action_id = pas.payroll_action_id

AND rr.element_type_id = ety.element_type_id

AND i.element_type_id = ety.element_type_id

AND rrv.run_result_id = rr.run_result_id

AND rrv.input_value_id = i.input_value_id

and  TP.TIME_PERIOD_ID = PPA.TIME_PERIOD_ID

and ety.CLASSIFICATION_ID=pec.CLASSIFICATION_ID

AND i.name = ‘Pay Value’

-- AND HR_GENERAL.DECODE_LATEST_POSITION_DEF_ID(PAF.POSITION_ID) = PD.POSITION_DEFINITION_ID

and ppa.EFFECTIVE_DATE  BETWEEN    :p_st_effect_date  AND  :p_end_effect_date

and ppf.employee_number  = nvl(:p_emp_number,ppf.employee_number)

group by 

ppf.employee_number,

ppf.person_id,

ppf.full_name,

ppa.TIME_PERIOD_ID,

ppa.EFFECTIVE_DATE,

TP.PERIOD_NAME,

paf.ORGANIZATION_ID,