Oracle Apps Knowledge Sharing

Query to fetch External Bank accounts, Bank and Bank Branch details in Oracle Apps R12 (IBY_EXTERNAL_BANK_ACCOUNTS_V, IBY_EXT_BANKS_V,IBY_EXT_BANK_BRANCHES_V)

In this post, we have given a query which gives the basic details related to External bank accounts, Bank branches and bank setup details. This inturn helps you to understand the joins between bank related tables/views.

Tables/Views Involved:

iby_external_bank_accounts_v

iby_ext_banks_v

iby_ext_bank_branches_v

fnd_territories_vl

Query:

 

     SELECT ieba.ext_bank_account_id,
            ieba.bank_account_name,
            ieba.bank_account_number,
            ieba.bank_account_num_electronic,
            ieba.currency_code,
            ieba.primary_acct_owner_name,
            ieba.start_date bank_account_start_date,
            ieba.end_Date  bank_account_end_date,   
            ieba.country_code,            
            ft.territory_short_name country,
            ieb.bank_name,
            ieb.address1 bank_address1,
            ieb.address2 bank_address2,
            ieb.city bank_city,
            ieb.state bank_state,
            ieb.postal_code bank_postal_code,
            ieb.country bank_country,
            ieba.bank_branch_name,
            ieba.branch_number,
            iebb.bank_branch_type,
            iebb.start_date branch_start_date,
            iebb.end_date branch_end_date,
            iebb.address_line1 branch_address_line1,
            iebb.address_line2 branch_address_line2,
            iebb.city bank_city,
            iebb.state bank_state,
            iebb.zip bankbranch_postal_code,
            iebb.country bank_branch_country,
            iebb.operation_flag
       FROM apps.iby_external_bank_accounts_v ieba
           ,apps.iby_ext_banks_v              ieb
           ,apps.iby_ext_bank_branches_v      iebb
           ,fnd_territories_vl ft
      WHERE ieba.bank_party_id = ieb.bank_party_id
        AND ieb.bank_party_id = iebb.bank_party_id
        AND ieba.branch_party_id = iebb.branch_party_id
        AND ft.territory_code = ieba.country_code;