Receivables & Collections

Get Involved. Join the Conversation.

Topic

    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
    Title:
    Query to get customer site bank details
    Content:

    Hi,

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

    Attached screenshot

    Thanks

    Document:
    bank.PNG (15KB)

    Best Comment

    Sudhakara Rao Kovuru

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

    Comment

     

    • 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, 
             ipiu.payment_flow, 
             ipiu.ext_pmt_party_id, 
             ipiu.instrument_type, 
             ipiu.instrument_id, 
             cb.bank_name, 
             cb.bank_branch_name, 
             ieb.currency_code, 
             ieb.bank_account_name, 
             ieb.bank_account_num, 
             iep.org_id, 
             hp.party_name customer_name, 
             hca.account_number 
      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, 
                 ipiu.payment_flow, 
                 ipiu.ext_pmt_party_id, 
                 ipiu.instrument_type, 
                 ipiu.instrument_id, 
                 cb.bank_name, 
                 cb.bank_branch_name, 
                 ieb.currency_code, 
                 ieb.bank_account_name, 
                 ieb.bank_account_num, 
                 iep.org_id, 
                 hp.party_name customer_name, 
                 hca.account_number 
          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