• Post category:BI Publisher
  • Post comments:0 Comments
  • Post last modified:July 30, 2020
  • Reading time:2 mins read
You are currently viewing SQL Query to fetch Bank, Branches and External Bank Accounts
SQL Query to fetch Bank, Branches and External Bank Accounts
- SQL Query to fetch Bank, Branches and External Bank Accounts
Total Visits: 48

In this article we will look into the queries to fetch the Bank and Branch setup details and the External Bank Account details.

Query to fetch Bank Details:

SELECT bank_name
,country
,to_char(start_date,'RRRR-MM-DD') start_date
,to_char(end_date,'RRRR-MM-DD') end_date
FROM ce_banks_v
WHERE :p_effective_date BETWEEN start_date AND end_date
AND NVL(:p_bank_name,bank_name) = bank_name

Query to fetch Bank Branch Details:

SELECT bank_name
,bank_branch_name
,branch_number
,country
,to_char(start_date,'RRRR-MM-DD') start_date
,to_char(end_date,'RRRR-MM-DD') end_date
FROM ce_bank_branches_v
WHERE :p_effective_date BETWEEN start_date AND end_date
/AND nvl(:p_bank_name,bank_name) = bank_name/
AND nvl(:p_branch_name
,bank_branch_name) = bank_branch_name
/AND :p_branch_name = bank_branch_name/

Query to fetch External Bank Account Details:

SELECT country_code
,bank_id
,branch_party_id
,bank_account_num
,iban
,bank_account_type
,to_char(eba.start_date,'RRRR-MM-DD') start_date
,to_char(eba.end_date,'RRRR-MM-DD') end_date
,cb.bank_name
,cbbv.bank_branch_name
,cb.country
FROM ce_banks_v cb
,ce_bank_branches_v cbbv
,iby_ext_bank_accounts eba
WHERE cb.bank_party_id = eba.bank_id
AND cbbv.branch_party_id = eba.branch_id
and :p_effective_Date between eba.start_date and eba.end_date