Oracle ERP Cloud SQL Query to find Bank Accounts Associated with a Supplier

/*******************************************************************
*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';

/******************************************************************* *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';