Query to get Employee Bank Account Details tagged to personal payment method in Oracle HCM Cloud

iby_ext_bank_accounts_v is the view which stores the bank account details and linked to a personal payment method of the employee. The column to be joined is primary_acct_owner_party_id of iby_ext_bank_accounts_v with party_id column of hz_parties. The column orig_system_reference of hz_parties is then joined with person_id of per_all_people_f.


SELECT papf.person_number
  ,pprd.payroll_relationship_number
  ,paaf.assignment_number
  ,iebc.EXT_BANK_ACCOUNT_ID
  ,iebc.primary_acct_owner_name
  ,iebc.bank_account_name
  ,iebc.bank_account_number
  ,iebc.bank_account_type
  ,iebc.bank_name
  ,iebc.bank_number
  ,iebc.bank_branch_name
  ,iebc.branch_number
  ,iebc.country_code
  ,iebc.CHECK_DIGITS
  , PPPM.PERCENTAGE
  , PPPM.PRIORITY
  , POPM.ORG_PAYMENT_METHOD_NAME
  , PPPM.amount
  FROM hz_parties hz_per,
       per_all_people_f papf,
   iby_ext_bank_accounts_v iebc,
   per_all_assignments_f paaf,
   per_periods_of_service ppos,
   pay_pay_relationships_dn pprd,
   pay_payroll_assignments ppasg,
   PAY_PERSONAL_PAYMENT_METHODS_F PPPM,
   PAY_ORG_PAY_METHODS_VL POPM
 WHERE hz_per.orig_system_reference = papf.person_id
   AND hz_per.party_id = iebc.primary_acct_owner_party_id
   AND papf.person_id = pprd.person_id
   and paaf.assignment_id = ppasg.hr_assignment_id
   AND ppos.date_start=(SELECT MAX(ppos1.date_start)
   FROM per_periods_of_service  ppos1
  WHERE ppos1.person_id=ppos.person_id
    --AND ppos1.period_of_service_id = ppos.period_of_service_id
)
  AND ppos.period_of_service_id = paaf.period_of_service_id
  AND paaf.person_id = papf.person_id
  AND paaf.assignment_type IN ('C','N','E')
  AND paaf.primary_flag = 'Y'
  AND NVL(PPOS.actual_termination_date,trunc(sysdate)) BETWEEN paaf.effective_start_date AND paaf.effective_end_date
  AND NVL(PPOS.actual_termination_date,trunc(sysdate)) BETWEEN papf.effective_start_date AND papf.effective_end_date
  and NVL(PPOS.actual_termination_date,trunc(sysdate)) between ppasg.start_date and ppasg.end_date
  --AND TRUNC(SYSDATE) <= NVL(ppos.actual_termination_date, TO_DATE('12314712','MMDDYYYY'))   
  AND iebc.country_code = 'GB'
  AND iebc.BANK_ACCOUNT_ID = PPPM.BANK_ACCOUNT_ID
  AND pprd.payroll_relationship_id = pppm.payroll_relationship_id
  and pppm.PAYROLL_RELATIONSHIP_ID = ppasg.PAYROLL_RELATIONSHIP_ID----------------------------------most important
  AND POPM.ORG_PAYMENT_METHOD_ID = PPPM.ORG_PAYMENT_METHOD_ID
  AND TRUNC(SYSDATE) BETWEEN PPPM.effective_start_date AND PPPM.effective_end_date
  AND TRUNC(SYSDATE) BETWEEN POPM.effective_start_date AND POPM.effective_end_date 
ORDER BY papf.person_number