How to get Bank Details part of Trading Community R12

Banks, Bank accounts, Bank Branches is now part of trading community architecture in oracle applications R12

We can arrive key info like bank name, branch name, bank account no, currency, account name using below re-usuable function


Function Protocol

  FUNCTION get_bank_details( p_info_type    IN VARCHAR2
                           , p_bank_acct_id IN NUMBER) RETURN VARCHAR2;

Function Implementation

FUNCTION get_bank_details( p_info_type    IN VARCHAR2
                         , p_bank_acct_id IN NUMBER) RETURN VARCHAR2 IS

TYPE bank_details_rec IS RECORD
  ( bank_account_name VARCHAR2(300)
  , bank_account_num  VARCHAR2(50)
  , currency_code     VARCHAR2(10)
  , bank_name         VARCHAR2(300)
  , bank_branch_name  VARCHAR2(300) );

  l_bank_details_rec  bank_details_rec;
  l_string            VARCHAR2(300);

BEGIN
  SELECT bank_account_name
       , bank_account_num
       , currency_code
       , bb.bank_name
       , bb.bank_branch_name
  INTO l_bank_details_rec
  FROM ce_bank_accounts ba,
    ce_bank_branches_v bb
  WHERE ba.bank_id       = bb.bank_party_id
  AND ba.bank_branch_id  = bb.branch_party_id
  AND ba.bank_account_id = p_bank_acct_id;

  SELECT
    CASE
      WHEN p_info_type = 'BACCNAME'
      THEN l_bank_details_rec.bank_account_name
      WHEN p_info_type = 'BACCNO'
      THEN l_bank_details_rec.bank_account_num
      WHEN p_info_type = 'BCURR'
      THEN l_bank_details_rec.currency_code
      WHEN p_info_type = 'BNAME'
      THEN l_bank_details_rec.bank_name
      WHEN p_info_type = 'BBNAME'
      THEN l_bank_details_rec.bank_branch_name
    END
  INTO l_string
  FROM dual;

  RETURN l_string;

EXCEPTION
WHEN OTHERS THEN
  RETURN 'Error-in-Getting-Bank-Info';
END;