Payroll Multiple Element Pay Value Query

Multiple Element Pay Value Query  (Before Payroll Run)

SELECT DISTINCT papf.employee_number,

, peev.screen_entry_value Transport_Allowance

,peev1.screen_entry_value Shift_Allowance

FROM apps.per_all_people_f papf

,apps.per_all_assignments_f paaf

,apps.pay_element_types_x petf

,apps.pay_element_types_x petf1

,apps.pay_element_entries_f peef

,apps.pay_element_entries_f peef1

,apps.pay_element_entry_values_x peev

,apps.pay_element_entry_values_x peev1

,apps.pay_input_values_x pivf

,apps.pay_input_values_x pivf1

WHERE

papf.person_id = paaf.person_id

AND paaf.assignment_id = peef.assignment_id

AND paaf.assignment_id = peef1.assignment_id

AND paaf.business_group_id = papf.business_group_id

-- To retrieve the screen entry values from "Transport Allowance"

AND peef.element_entry_id = peev.element_entry_id

AND peef.element_type_id = petf.element_type_id

AND petf.element_Name = 'Transport Allowance'

AND pivf.element_type_id =petf.element_type_id

AND pivf.name = 'Value'

AND peev.input_value_id= pivf.input_value_id

-- To retrieve the screen entry values from "Shift Allowance"

AND peef1.element_entry_id = peev1.element_entry_id

AND peef1.element_type_id = petf1.element_type_id

AND petf1.element_Name = 'Shift Allowance'

AND pivf1.element_type_id =petf1.element_type_id

AND pivf1.name = 'Value'

AND peev1.input_value_id= pivf1.input_value_id

AND (SYSDATE BETWEEN peev.effective_start_date AND peev.effective_end_date)

AND (SYSDATE BETWEEN peev1.effective_start_date AND peev1.effective_end_date)

AND (SYSDATE BETWEEN papf.effective_start_date AND papf.effective_end_date)

ORDER BY papf.employee_number