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

Oracle HR and Payroll Related Queries

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.full_name,ppa.TIME_PERIOD_ID,effective_date,–To_Number(Wassa_HR_PACKAGE.Nid_Salary_By_Date(paf.assignment_id,ppa.EFFECTIVE_DATE,’N')),
ppf.employee_number,ppf.person_id ,–PD.SEGMENT5 ,PD.SEGMENT6,
TP.period_name,paf.ORGANIZATION_ID

 **************************