Fusion - Query - PAY - Element Entries

 SELECT   papf.person_number,

         ppnf.display_name person_name,

         paam.assignment_number,

         pps.date_start hire_date,

         pett.element_name,

         peef.multiple_entry_count,

         peef.effective_start_date ee_esd,

         peef.effective_end_date ee_eed,

         pivt.name input_name,

         peevf.screen_entry_value

FROM     pay_input_values_f pivf,

         pay_input_values_tl pivt,

         pay_element_entry_values_f peevf,

         pay_element_types_tl pett,

         pay_entry_usages peu,

         pay_element_entries_f peef,

         pay_payroll_assignments ppa,

         per_periods_of_service pps,

         per_all_assignments_m paam,

         per_person_names_f ppnf,

         per_all_people_f papf

WHERE    pivf.user_display_flag = 'Y'

AND      peef.effective_start_date BETWEEN pivf.effective_start_date AND pivf.effective_end_date

AND      pivf.input_value_id = pivt.input_value_id

AND      pivt.name = NVL(:p_input_name, pivt.name)

AND      pivt.language = 'US'

AND      pivt.input_value_id = peevf.input_value_id

AND      peef.effective_start_date BETWEEN peevf.effective_start_date AND peevf.effective_end_date

AND      peevf.element_entry_id = peef.element_entry_id

AND      pett.element_name = NVL(:p_element_name, pett.element_name)

AND      pett.language = 'US'

AND      pett.element_type_id = peef.element_type_id

AND      peef.effective_start_date BETWEEN peu.date_from AND peu.date_to

AND      peu.element_entry_id = peef.element_entry_id

AND      NVL(peu.payroll_assignment_id, ppa.payroll_assignment_id) = ppa.payroll_assignment_id

AND      peu.payroll_relationship_id = ppa.payroll_relationship_id

AND      peef.effective_start_date <= :p_end_date

AND      peef.effective_end_date >= :p_start_date

AND      peef.person_id = paam.person_id

AND      ppa.hr_assignment_id = paam.assignment_id

AND      pps.period_of_service_id = paam.period_of_service_id

AND      :p_end_date BETWEEN paam.effective_start_date AND paam.effective_end_date

AND      paam.assignment_number = NVL(:p_assignment_number, paam.assignment_number)

AND      paam.effective_latest_change = 'Y'

AND      paam.assignment_type = 'E'

AND      paam.person_id = papf.person_id

AND      :p_end_date BETWEEN ppnf.effective_start_date AND ppnf.effective_end_date

AND      ppnf.name_type = 'GLOBAL'

AND      ppnf.person_id = papf.person_id

AND      :p_end_date BETWEEN papf.effective_start_date AND papf.effective_end_date

AND      papf.person_number = NVL(:p_person_number, papf.person_number)

ORDER BY papf.person_number,

         paam.assignment_number,

         pett.element_name,

         peef.multiple_entry_count,

         pivf.display_sequence