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
