Banks and Branch Conversion

Base Tables:
IBY_EXT_BANKS_V
IBY_EXT_BANK_BRANCHES_V

APIs:
IBY_EXT_BANKACCT_PUB.create_ext_bank
IBY_EXT_BANKACCT_PUB.create_ext_bank_branch

Sample Code:
To create Bank:

PROCEDURE create_bank
IS
/* ************************************************************************* */
/*                                                                           */
/* Name      : create_bank                     */
/* Created On: 06-JUL-2011                                                   */
/* Created By: Vijay Mallareddy                                              */
/* Purpose   : checks for the bank, if exists do nothing else create bank    */
/*                                                                           */
/*                                                                           */
/* ************************************************************************* */
/* Modification History:                                                     */
/* Version   By                Date        Comments                          */
/*     1.0   Vijay Mallareddy  06-JUL-2011 Created                           */
/*                                                                           */
/* ***************************************************************************/
lv_ext_bank_rec      apps.IBY_EXT_BANKACCT_PUB.ExtBank_rec_type;
lv_response_rec apps.IBY_FNDCPT_COMMON_PUB.Result_rec_type;
lvx_bank_id NUMBER;
lvx_return_status VARCHAR2(10);
lvx_msg_count NUMBER;
lvx_msg_data VARCHAR2(2000);
lv_msg_index     NUMBER      := 0;


BEGIN
dbms_output.put_line('inside begin 1');
lv_ext_bank_rec.bank_name := 'Twitter SBI';
lv_ext_bank_rec.bank_number := 'SBI0102';
lv_ext_bank_rec.institution_type := 'OTHER';
lv_ext_bank_rec.country_code := 'US';
lv_ext_bank_rec.object_version_number := 1;
dbms_output.put_line('Calling API create_ext_bank');
apps.IBY_EXT_BANKACCT_PUB.create_ext_bank (
    p_api_version              => 1.0,
p_init_msg_list            => apps.FND_API.G_TRUE,
p_ext_bank_rec             => lv_ext_bank_rec,
x_bank_id                  => lvx_bank_id,
x_return_status            => lvx_return_status,
x_msg_count                => lvx_msg_count,
x_msg_data                 => lvx_msg_data,
x_response                 => lv_response_rec
  );
dbms_output.put_line('After API create_ext_bank');
  IF lvx_return_status IN ('E', 'U')
THEN

FOR i IN 1 .. lvx_msg_count
LOOP
 apps.fnd_msg_pub.get (i,
  apps.fnd_api.g_false,
  lvx_msg_data,
  lv_msg_index
 );
 lvx_msg_data := lvx_msg_data || '~Bank API Error ';
 dbms_output.put_line(lvx_msg_data);
END LOOP;

ELSIF lvx_return_status = 'S'
THEN
dbms_output.put_line('Bank Created: and id: '||lvx_bank_id);

END IF;

EXCEPTION
WHEN OTHERS THEN
  dbms_output.put_line('Others Exception: '||SQLERRM);
END create_bank;

PROCEDURE create_bank_branch
IS
/* ************************************************************************* */
/*                                                                           */
/* Name      : main                             */
/* Created On: 06-JUL-2011                                                   */
/* Created By: Vijay Mallareddy                                              */
/* Purpose   : checks for the bank branch, if exists do nothing else create  */
/*             bank branch   */
/*                                                                           */
/* ************************************************************************* */
/* Modification History:                                                     */
/* Version   By                Date        Comments                          */
/*     1.0   Vijay Mallareddy  06-JUL-2011 Created                           */
/*                                                                           */
/* ***************************************************************************/

lv_ext_bank_branch_rec      apps.IBY_EXT_BANKACCT_PUB.ExtBankBranch_rec_type;
lv_response_rec apps.IBY_FNDCPT_COMMON_PUB.Result_rec_type;
lvx_branch_id NUMBER;
lvx_return_status VARCHAR2(10);
lvx_msg_count NUMBER;
lvx_msg_data VARCHAR2(2000);
lv_msg_index     NUMBER      := 0;


BEGIN
dbms_output.put_line('inside begin 1');
lv_ext_bank_branch_rec.bank_party_id := 378946;
lv_ext_bank_branch_rec.branch_name := 'Twitter SBI Branch';
lv_ext_bank_branch_rec.branch_number := '076401251';
lv_ext_bank_branch_rec.branch_type := 'OTHER';
lv_ext_bank_branch_rec.bch_object_version_number := 1;

dbms_output.put_line('Calling API create_ext_bank_branch');
apps.IBY_EXT_BANKACCT_PUB.create_ext_bank_branch (
   p_api_version                => 1.0,
   p_init_msg_list              => apps.FND_API.G_TRUE,
   p_ext_bank_branch_rec        => lv_ext_bank_branch_rec,
   x_branch_id                  => lvx_branch_id,
   x_return_status             => lvx_return_status,
   x_msg_count                 => lvx_msg_count,
   x_msg_data                 => lvx_msg_data,
   x_response                 => lv_response_rec
  );
dbms_output.put_line('After API create_ext_bank_branch');
  IF lvx_return_status IN ('E', 'U')
THEN

FOR i IN 1 .. lvx_msg_count
LOOP
 apps.fnd_msg_pub.get (i,
  apps.fnd_api.g_false,
  lvx_msg_data,
  lv_msg_index
 );
 lvx_msg_data := lvx_msg_data || '~Bank API Error ';
 dbms_output.put_line(lvx_msg_data);
END LOOP;

ELSIF lvx_return_status = 'S'
THEN
dbms_output.put_line('Bank Branch Created: and id: '||lvx_branch_id);

END IF;

EXCEPTION
WHEN OTHERS THEN
  dbms_output.put_line('Others Exception: '||SQLERRM);
END create_bank_branch;

Issues:
1. Branch number will be validated against Country.
Useful links:
R12: Federal Financials: "Invalid Routing Number" Errors [ID 1319252.1]
https://support.oracle.com/CSP/ui/flash.html#tab=KBHome(page=KBHome&id=()),(page=KBNavigator&id=(bmDocDsrc=KB&bmDocTitle=R12:%20Federal%20Financials:%20%22Invalid%20Routing%20Number%22%20Errors&from=BOOKMARK&bmDocID=1319252.1&viewingMode=1143&bmDocType=PROBLEM))