SELECT
papf.person_number,
ele.element_name,
piv.name,
prrv.result_value,
prr.run_result_id
FROM
pay_element_types_vl ele,
pay_run_results prr,
pay_run_result_values prrv,
pay_input_values_vl piv,
pay_payroll_rel_actions ppra,
pay_payroll_assignments ppasg,
per_all_assignments_f paaf,
per_all_people_f papf,
pay_payroll_actions ppa,
pay_all_payrolls_f pap,
pay_time_periods ptp
WHERE
1 = 1
AND ele.element_type_id = prr.element_type_id
AND prr.run_result_id = prrv.run_result_id
AND prrv.input_value_id = piv.input_value_id
AND prr.payroll_rel_action_id = ppra.payroll_rel_action_id
AND ppra.payroll_relationship_id = ppasg.payroll_relationship_id
AND ppasg.hr_assignment_id = paaf.assignment_id
AND paaf.person_id = papf.person_id
AND ppra.payroll_action_id = ppa.payroll_action_id
AND ppa.payroll_id = pap.payroll_id
AND pap.payroll_id = ptp.payroll_id
AND ((ppra.run_type_id IS NOT NULL AND ppa.action_type in ('R','Q','V'))
OR ppa.action_type in ('B'))
AND ppra.RETRO_COMPONENT_ID is NULL
AND paaf.assignment_type = 'E'
AND paaf.primary_flag = 'Y'
AND ele.element_name = 'Annual Salary Earnings Results'
AND ptp.period_category = 'E'
AND to_date('31-12-2022','dd-mm-rrrr') BETWEEN pap.effective_start_date AND pap.effective_end_date
AND to_date('31-12-2022','dd-mm-rrrr') BETWEEN papf.effective_start_date AND papf.effective_end_date
AND to_date('31-12-2022','dd-mm-rrrr') BETWEEN paaf.effective_start_date AND paaf.effective_end_date
AND to_date('31-12-2022','dd-mm-rrrr') BETWEEN ele.effective_start_date AND ele.effective_end_date
AND to_date('31-12-2022','dd-mm-rrrr') BETWEEN piv.effective_start_date AND piv.effective_end_date
AND to_date('31-12-2022','dd-mm-rrrr') BETWEEN ptp.start_date AND ptp.end_date
AND ppa.effective_date BETWEEN ptp.start_date AND ptp.end_date
AND papf.person_number = '12345'
