Receivables & Collections

Get Involved. Join the Conversation.


    Mohammed Rafi
    Query to get customer site bank detailsAnswered
    Topic posted August 15, 2019 by Mohammed RafiBlack Diamond: 60,000+ Points 
    35 Views, 5 Comments
    Query to get customer site bank details


    Please help me to locate the tables and underlying links to get the bank account information of the customer site

    Attached screenshot


    bank.PNG (15KB)

    Best Comment

    Sudhakara Rao Kovuru

    Hope this should resolve your ask... please review and close it. Thanks



    • Sudhakara Rao Kovuru

      Here is the Customer and its Bank Account details, if you need any more details then you can add other tables to it.

      SELECT ipiu.instrument_payment_use_id, 
             hp.party_name customer_name, 
      FROM   iby_pmt_instr_uses_all ipiu, 
             iby_ext_bank_accounts ieb, 
             iby_external_payers_all iep, 
             ce_bank_branches_v cb, 
             hz_parties hp, 
             hz_cust_accounts hca, 
             hz_cust_acct_sites_all hsa, 
             hz_cust_site_uses_all hsu 
      WHERE  ieb.ext_bank_account_id = ipiu.instrument_id 
             AND ipiu.ext_pmt_party_id = iep.ext_payer_id 
             AND cb.bank_party_id = ieb.bank_id 
             AND iep.cust_account_id = hca.cust_account_id 
             AND hp.party_id = hca.party_id 
             AND hca.cust_account_id = hsa.cust_account_id 
             AND hsa.cust_acct_site_id = hsu.cust_acct_site_id 
             AND iep.acct_site_use_id = hsu.site_use_id 
             AND iep.org_id = hsu.org_id 

    • Sudhakara Rao Kovuru

      Is this helpful?

      • Mohammed Rafi

        Helpful how do I connect this with Party Site as the bank details are attached at customer site

        • Sudhakara Rao Kovuru
          Its already connected to party site level so plz try below and let me know....
          SELECT ipiu.instrument_payment_use_id, 
                 hp.party_name customer_name, 
          FROM   iby_pmt_instr_uses_all ipiu, 
                 iby_ext_bank_accounts ieb, 
                 iby_external_payers_all iep, 
                 ce_bank_branches_v cb, 
                 hz_parties hp, 
                 hz_cust_accounts hca, 
                 hz_cust_acct_sites_all hsa,
                 hz_party_sites hps,    
                 hz_cust_site_uses_all hsu 
          WHERE  ieb.ext_bank_account_id = ipiu.instrument_id 
                 AND ipiu.ext_pmt_party_id = iep.ext_payer_id 
                 AND cb.bank_party_id = ieb.bank_id 
                 AND iep.cust_account_id = hca.cust_account_id 
                 AND hp.party_id = hca.party_id 
                 AND hca.cust_account_id = hsa.cust_account_id 
                 AND hsa.cust_acct_site_id = hsu.cust_acct_site_id 
             AND hsa.party_site_id = hps.party_site_id
                 AND iep.acct_site_use_id = hsu.site_use_id 
                 AND iep.org_id = hsu.org_id