/*******************************************************************
*PURPOSE: ERP Cloud SQL Query to find Bank Accounts Associated with a Supplier *
*AUTHOR: Shailender Thallam *
*******************************************************************/
SELECT
ps.segment1 AS vendor_num ,
hzp.party_name AS vendor_name ,
pssm.attribute20 AS legacy_vendor_number ,
pssm.vendor_site_code ,
bank.party_name bank_name ,
branch.party_name branch_name ,
ieb.bank_account_name ,
ieb.bank_account_num ,
ieb.currency_code ,
ieb.Bank_Account_Name_alt AS TRANSIT_NUMBER ,
ieb.Secondary_Account_Reference AS ROUTING_NUMBER
FROM
poz_suppliers ps ,
poz_supplier_sites_all_m pssm ,
iby_external_payees_all payee ,
iby_pmt_instr_uses_all uses ,
iby_ext_bank_accounts ieb ,
hz_parties bank ,
hz_parties branch ,
HZ_PARTIES HZP
WHERE
ps.vendor_id = pssm.vendor_id
AND ps.party_id = payee.payee_party_id
AND payee.supplier_site_id = pssm.vendor_site_id
AND uses.instrument_type = 'BANKACCOUNT'
AND payee.ext_payee_id = uses.ext_pmt_party_id
AND uses.payment_function = 'PAYABLES_DISB'
AND uses.instrument_id = ieb.ext_bank_account_id
AND ieb.bank_id = bank.party_id(+)
AND ieb.branch_id = branch.party_id(+)
AND hzp.party_id = ps.party_id
AND SYSDATE BETWEEN NVL(uses.start_date,SYSDATE) AND NVL(uses.end_date,SYSDATE)
AND SYSDATE BETWEEN NVL(ieb.start_date,SYSDATE) AND NVL(ieb.end_date,SYSDATE)
AND NVL(ps.end_date_active,SYSDATE+1) > TRUNC (SYSDATE)
AND NVL(pssm.inactive_date,SYSDATE+1) > TRUNC (SYSDATE)
--and ps.segment1 = '13086';