SELECT papf.person_number,
--paam.assignment_number,
pppmf.name,
--popmt.org_payment_method_name,
TO_CHAR (pppmf.effective_start_date,'DD-Mon-YYYY','NLS_DATE_LANGUAGE=AMERICAN')effective_start_date,
--TO_CHAR (pppmf.effective_end_date,'DD-Mon-YYYY','NLS_DATE_LANGUAGE=AMERICAN')effective_end_date,
pptt.payment_type_name,
pppmf.percentage,
pppmf.amount,
pppmf.PRIORITY,
pba.account_source,
--pba.bank_account_name,
pba.clear_bank_account_number,
pba.clear_iban,
--pba.currency_code,
--pba.bank_home_country,
pba.bank_number,
pba.branch_number,
pba.bank_name,
pba.bank_branch_name,
pppmf.personal_payment_method_id,
pba.BANK_ACCOUNT_TYPE,
pba.BANK_ACCOUNT_NUM
FROM per_all_people_f papf
,per_all_assignments_m paam
,pay_payroll_assignments ppa
,pay_bank_accounts pba
,pay_payment_types_tl pptt
,pay_payment_types ppt
,pay_org_pay_methods_tl popmt
,pay_org_pay_methods_f popmf
,pay_person_pay_methods_f pppmf
WHERE pppmf.effective_start_date BETWEEN papf.effective_start_date AND papf.effective_end_date
AND papf.person_id = paam.person_id
AND pppmf.effective_start_date BETWEEN paam.effective_start_date AND paam.effective_end_date
AND paam.effective_latest_change = 'Y'
AND paam.assignment_id = ppa.hr_assignment_id
AND PAAM.LEGISLATION_CODE='US'
AND ppa.payroll_relationship_id = pppmf.payroll_relationship_id
AND pba.bank_account_id(+) = pppmf.bank_account_id
AND pptt.language = 'US'
AND pptt.payment_type_id = ppt.payment_type_id
AND ppt.payment_type_id = popmf.payment_type_id
AND popmt.language = 'US'
AND popmt.org_payment_method_id = popmf.org_payment_method_id
AND pppmf.effective_start_date BETWEEN popmf.effective_start_date AND popmf.effective_end_date
AND popmf.org_payment_method_id = pppmf.org_payment_method_id
-- AND PAPF.PERSON_NUMBER='90396'
AND popmt.org_payment_method_name='Direct Deposit'
ORDER BY papf.person_number
,pppmf.effective_start_date
