PAY ROLL RUN RESULTS

 SELECT   papf.person_number,

         paam.assignment_number,

         papfp.payroll_name,

         ptp.period_name,

         pfi.instance_name,

         pect.classification_name,

         pett.element_name,

         pivt.name input_name,

         prrv.result_value

FROM     pay_input_values_tl pivt,

         pay_input_values_f pivf,

         pay_run_result_values prrv,

         pay_ele_classifications_tl pect,

         pay_element_types_tl pett,

         pay_element_types_f petf,

         pay_run_results prr,

         per_all_people_f papf,

         per_all_assignments_m paam,

         pay_payroll_assignments ppaa,

         pay_payroll_rel_actions ppra,

         pay_time_periods ptp,

         pay_all_payrolls_f papfp,

         pay_payroll_actions ppa,

         pay_requests pr,

         pay_flow_instances pfi

WHERE    pivt.language = 'US'

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

AND      pivt.input_value_id = prrv.input_value_id

AND      ppa.date_earned BETWEEN pivf.effective_start_date AND pivf.effective_end_date

AND      pivf.input_value_id = prrv.input_value_id

AND      prrv.run_result_id = prr.run_result_id

AND      pect.classification_name = NVL(:p_classification_name, pect.classification_name)

AND      pect.language = 'US'

AND      pect.classification_id = petf.classification_id

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

AND      pett.language = 'US'

AND      pett.element_type_id = prr.element_type_id

AND      ppa.date_earned BETWEEN petf.effective_start_date AND petf.effective_end_date

AND      petf.element_type_id = prr.element_type_id

AND      prr.payroll_rel_action_id = ppra.payroll_rel_action_id

AND      ppa.date_earned BETWEEN papf.effective_start_date AND papf.effective_end_date

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

AND      papf.person_id = paam.person_id

AND      ppa.date_earned 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_id = ppaa.hr_assignment_id

AND      ppaa.payroll_relationship_id = ppra.payroll_relationship_id

AND      ppra.action_status = 'C'

AND      ppra.payroll_action_id = ppa.payroll_action_id

AND      ptp.period_name = NVL(:p_period_name, ptp.period_name)

AND      ptp.time_period_id = ppa.earn_time_period_id

AND      ppa.date_earned BETWEEN papfp.effective_start_date AND papfp.effective_end_date

AND      papfp.payroll_name = NVL(:p_payroll_name, papfp.payroll_name)

AND      papfp.payroll_id = ppa.payroll_id

AND      ppa.action_status = 'C'

AND      ppa.pay_request_id = pr.pay_request_id

AND      pr.flow_instance_id = pfi.flow_instance_id

AND      pfi.instance_name = NVL(:p_flow_name, pfi.instance_name)

ORDER BY papfp.payroll_name,

         ptp.period_name,

         lpad(papf.person_number, 10, '0'),

         paam.assignment_number,

         pect.classification_name,

         pett.element_name,

         prr.sequence_number,

         pivf.display_sequence;