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))

Suppliers, Supplier Sites, Contacts and Banks Conversion Using Standard Import and API

Interface Programs:
Supplier Open Interface Import
Supplier Sites Open Interface Import
Supplier Site Contacts Open Interface Import

Interface Tables:
AP_SUPPLIERS_INT
AP_SUPPLIER_SITES_INT
AP_SUP_SITE_CONTACT_INT
IBY_TEMP_EXT_BANK_ACCTS

Rejections Table:
AP_SUPPLIER_INT_REJECTIONS


Using APIs:
APIs involved:


-- API to validate vendor
AP_VENDOR_PUB_PKG.Validate_Vendor

-- API to validate vendor site
AP_VENDOR_PUB_PKG.Validate_Vendor_Site

-- API to validate vendor contact
AP_VENDOR_PUB_PKG.Validate_Vendor_Contact

-- API to  vendor
AP_VENDOR_PUB_PKG.Create_Vendor
AP_VENDOR_PUB_PKG.Create_Vendor_Site
AP_VENDOR_PUB_PKG.Create_Vendor_Contact


-- API call to create bank account
iby_ext_bankacct_pub.create_ext_bank_acct

-- API to  make the party as joint account holder
iby_ext_bankacct_pub.add_joint_account_owner

-- API call to assign bank account to the supplier site
iby_disbursement_setup_pub.set_payee_instr_assignment

-- API to Create Party Tax Profile
zx_party_tax_profile_pkg.update_row


You can use the following APIs if Supplier Management is implemented. Also you can use this for iSupplier specific data like business classifications.
POS_VENDOR_PUB_PKG.CREATE_VENDOR
POS_VENDOR_PUB_PKG.CREATE_VENDOR_SITE
POS_VENDOR_PUB_PKG.Update_Vendor
POS_VENDOR_PUB_PKG.Validate_Vendor
POS_VENDOR_PUB_PKG.Update_Vendor_Site
POS_VENDOR_PUB_PKG.Validate_Vendor_Site
POS_VENDOR_PUB_PKG.Create_Vendor_Contact
POS_SUPP_CLASSIFICATION_PKG.add_bus_class_attr
POS_SUPPLIER_ADDRESS_PKG.assign_address_to_contact

To Update payment method code:
--------------------------------
l_vendor_rec.ext_payee_rec.default_pmt_method := 'EFT'

Important Links:
1. R12: Assigning Bank Account to Supplier -- Supplier Import [ID 466334.1]
https://support.oracle.com/CSP/ui/flash.html#tab=KBHome(page=KBHome&id=()),(page=KBNavigator&id=(bmDocTitle=R12:%20Assigning%20Bank%20Account%20to%20Supplier%20--%20Supplier%20Import&bmDocDsrc=KB&bmDocID=466334.1&from=BOOKMARK&viewingMode=1143&bmDocType=HOWTO))


2. R12: Supplier Sites Open Interface Fails to Import Records - No Data Found in Report - AP_VENDOR_ID_NULL Rejection Reason in AP_SUPPLIER_INT_REJECTIONS [ID 1311279.1]
https://support.oracle.com/CSP/ui/flash.html#tab=KBHome(page=KBHome&id=()),(page=KBNavigator&id=(bmDocTitle=R12:%20Supplier%20Sites%20Open%20Interface%20Fails%20to%20Import%20Records%20-%20No%20Data%20Found%20in%20Report%20-%20AP_VENDOR_ID_NULL%20Rejection%20Reason%20in%20AP_SUPPLIER_INT_REJECTIONS&bmDocDsrc=KB&bmDocID=1311279.1&from=BOOKMARK&viewingMode=1143&bmDocType=PROBLEM))


3. Documentation on Importing Internal and External Bank Account In R12: Bank API's [ID 948993.1]
4. R12: Supplier Site Contacts Open Interface Import REP-4 - Inconsistent Data Entered As Compared To TCA Records [ID 1195815.1]
if it wont work, try the below:

a.    Log in to TCA Community Manager (Receivables).

b.    Navigate to Trading Community>Administration> Geography Hierarchy

c.    Query country code for US and click on “Manage Validations”

d.    Change the validation from Error to Warning.