PAY ROLL PERSON PAY METHOD

 SELECT   papf.person_number,

         ppnf.display_name person_name,

         paam.assignment_number,

         pps.date_start hire_date,

         pppmf.priority,

         pppmf.name,

         popmt.org_payment_method_name,

         pptt.payment_type_name,

         hr_general.decode_lookup('ORA_PAY_PPM_PAY_AMNT_TYPE', pppmf.payment_amount_type) payment_type,

         pppmf.percentage,

         pppmf.amount,

         popmf.currency_code,

         pba.bank_account_num

FROM     pay_bank_accounts pba,

         pay_payment_types_tl pptt,

         pay_org_pay_methods_f popmf,

         pay_org_pay_methods_tl popmt,

         pay_person_pay_methods_f pppmf,

         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    1 = 1

AND      pba.bank_account_id(+) = pppmf.bank_account_id

AND      pptt.language = 'US'

AND      pptt.payment_type_id = popmf.payment_type_id

AND      :p_end_date BETWEEN popmf.effective_start_date AND popmf.effective_end_date

AND      popmf.org_payment_method_id = pppmf.org_payment_method_id

AND      popmt.language = 'US'

AND      popmt.org_payment_method_id = pppmf.org_payment_method_id

AND      :p_end_date BETWEEN pppmf.effective_start_date AND pppmf.effective_end_date

AND      pppmf.payroll_relationship_id = ppa.payroll_relationship_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 lpad(papf.person_number, 10, '0'),

         paam.assignment_number