Customer bank account query in Oracle apps

Customer bank account query in Oracle apps

Here we are going to Discuss about the SQL query to find the Customer bank accounts information's in oracle apps. In oracle , Customer and Supplier bank accounts information's stores in IBY tables. All the Supplier and Customer bank accounts we can get in the Table 'IBY_EXT_BANK_ACCOUNTS'. But to reach this table from customer master is the main challenge. Here in this post , I will share the SQL query to find the Customer banking information's in oracle.

 

 

3 Important Tables Involved in the Customer Banking Information

1.iby_external_payers_all 

2. iby_pmt_instr_uses_all

3.iby_ext_bank_accounts

 

 

Customer Header bank account SQL query in Oracle apps

 

SELECT ca.account_number      "Customer Number",
      ar_cust.customer_name   "Customer Name",
      'Customer Header Banking Information' "RECORD_TYP",
      , IBY_PAYEE_USES.start_date "Bank Account Start Date"
      , IBY_PAYEE_USES.end_date  "Bank Account End Date"
      , IBY_EBA.bank_account_num  "Account Number"
      , IBY_EBA.bank_account_name "Account Name"
  FROM hz_cust_accounts        ca,
       ar_customers    ar_cust,
     , iby_external_payers_all IBY_PAYEE,
     , iby_pmt_instr_uses_all  IBY_PAYEE_USES,
     , iby_ext_bank_accounts  IBY_EBA
 WHERE ca.cust_account_id      = IBY_PAYEE.cust_account_id
   AND ar_cust.customer_id    =   ca.cust_account_id
   AND IBY_PAYEE_USES.ext_pmt_party_id    = IBY_PAYEE.ext_payer_id
   AND IBY_PAYEE.acct_site_use_id    is null
   AND IBY_PAYEE_USES.payment_function    = 'CUSTOMER_PAYMENT'
   AND IBY_EBA.ext_bank_account_id = IBY_PAYEE_USES.instrument_id
   AND ar_cust.customer_name=:P_CUSTOMER_NAME

Customer Site Level bank account query in Oracle apps

SELECT ca.account_number,
  ar_cust.customer_name   "Customer Name",
 (SELECT address1 || ' ' || address2||' '||address3||' '||address4||' '||loc.city || ' ' || loc.state || '-' || loc.postal_code
           FROM hz_locations loc
              , hz_party_sites hps
          WHERE loc.location_id = hps.location_id
            AND hps.party_site_id = cas.party_site_id
        ) addr
      , IBY_PAYEE_USES.start_date "Bank Account Start Date"
      , IBY_PAYEE_USES.end_date  "Bank Account End Date"
      , eba.bank_account_num "Account Number"
      , eba.bank_account_name "Account Name"
  FROM hz_cust_site_uses_all   csu,
   ar_customers    ar_cust,
     iby_external_payers_all IBY_PAYEE,
     iby_pmt_instr_uses_all IBY_PAYEE_USES,
     iby_ext_bank_accounts   eba,
      hz_cust_acct_sites_all  cas,
      hz_cust_accounts        ca
 WHERE 1=1
   AND cas.cust_account_id     = ca.cust_account_id
  AND ar_cust.customer_id    =   ca.cust_account_id
   AND csu.cust_acct_site_id   = cas.cust_acct_site_id
   AND IBY_PAYEE.acct_site_use_id    = csu.site_use_id  
   AND IBY_PAYEE_USES.ext_pmt_party_id    = IBY_PAYEE.ext_payer_id
   AND IBY_PAYEE_USES.payment_function    = 'CUSTOMER_PAYMENT'
   AND ar_cust.customer_name=:P_CUSTOMER_NAME
   AND eba.ext_bank_account_id = IBY_PAYEE_USES.instrument_id