OracleAppsDNA

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

Exit mobile version