Single Insertion scripts for R12 Supplier Banks

This Document provides the guidance to the user with the necessary information for creating Banks through Single Insert in accurate way.
The APIs used here are:

1. IBY_EXT_BANKACCT_PUB.CREATE_EXT_BANK
2. IBY_EXT_BANKACCT_PUB.CREATE_EXT_BANK_BRANCH
3. IBY_EXT_BANKACCT_PUB.CREATE_EXT_BANK_ACCT

Following are the steps that must be followed, in order, to perform the Single insertions for Banks:
Step 1 : Currency code should be define
SELECT DISTINCT currency_code FROM fnd_currencies_tl ;

Step 2 : Country code should be define.
SELECT DISTINCT territory_code FROM fnd_territories_tl
WHERE LANGUAGE = 'US'

Step 3 : Select organization_id from HR_OPERATING_UNITS where organization_id= 98;
--For Example Choose
ORGANIZATION_ID=98

Step 4 : Select user_id from fnd_user where user_name='CONVERSION'
USER_ID= 1451

-: BANKS CREATION :-

DECLARE
x_response_rec apps.iby_fndcpt_common_pub.result_rec_type;
l_init_msg_list VARCHAR2 (2000);
o_bank_id NUMBER;
x_return_status VARCHAR2 (3000);
l_msg_count NUMBER;
l_msg_data VARCHAR2 (3000);
l_msg_dummy VARCHAR2 (3000);
l_output VARCHAR2 (3000);
l_extbank_rec apps.iby_ext_bankacct_pub.extbank_rec_type;
exec_bank EXCEPTION;

BEGIN
x_return_status := '';
l_msg_count := '';
l_msg_data := '';
apps.fnd_msg_pub.delete_msg (NULL);
apps.fnd_msg_pub.initialize ();
l_extbank_rec.bank_name := 'Test Supp Bank' ;
l_extbank_rec.bank_number := 'TSB0000001';
l_extbank_rec.country_code:= 'IN';
APPS.IBY_EXT_BANKACCT_PUB.CREATE_EXT_BANK (1.0,
'F',
l_extbank_rec,
o_bank_id,
x_return_status,
l_msg_count,
l_msg_data,
x_response_rec
);
l_output := ' ';
IF x_return_status <> 'S'
THEN
FOR i IN 1 .. l_msg_count
LOOP
apps.fnd_msg_pub.get (i, apps.fnd_api.g_false,
l_msg_data,l_msg_dummy );
l_output := l_output || (TO_CHAR (i) || ': ' || SUBSTR
(l_msg_data, 1, 250));
END LOOP;
apps.fnd_file.put_line (apps.fnd_file.output, 'Error Occured
while Creating Bank: ');
END IF;
COMMIT;
END ;

Step 6 : COMMIT;

Step 7 : Check the base tables APPS.CE_BANKS_V whether the records are populated .

Step 8 : BANK BRANCH CREATION :- Get the bank id from banks

-: BANK BRANCH CREATION :-

DECLARE
x_response_rec apps.iby_fndcpt_common_pub.result_rec_type;
l_init_msg_list VARCHAR2 (2000);
o_branch_id NUMBER;
x_return_status VARCHAR2 (3000);
l_msg_count NUMBER;
l_msg_data VARCHAR2 (3000);
l_msg_dummy VARCHAR2 (3000);
l_output VARCHAR2 (3000);
l_extbank_rec apps.iby_ext_bankacct_pub.extbank_rec_type;
l_ext_bank_branch_rec
apps.iby_ext_bankacct_pub.extbankbranch_rec_type;
exec_bank_branch EXCEPTION;
BEGIN
x_return_status := '';
l_msg_count := '';
l_msg_data := '';
apps.fnd_msg_pub.delete_msg (NULL);
apps.fnd_msg_pub.initialize ();
l_ext_bank_branch_rec.bank_party_id := '70117';
l_ext_bank_branch_rec.branch_name := 'Test Supp Bank Branch';
l_ext_bank_branch_rec.branch_number := 'TSBB000001';
l_ext_bank_branch_rec.branch_type := 'SWIFT';
l_ext_bank_branch_rec.bic := 'TSBBBIC001';
APPS.IBY_EXT_BANKACCT_PUB.CREATE_EXT_BANK_BRANCH
(1.0,
'F',
l_ext_bank_branch_rec,
o_branch_id,
x_return_status,
l_msg_count,
l_msg_data,
x_response_rec
);
l_output := ' ';
IF x_return_status <> 'S'
THEN
FOR i IN 1 .. l_msg_count
LOOP
apps.fnd_msg_pub.get (i,apps.fnd_api.g_false,
l_msg_data,l_msg_dummy );
l_output := l_output || (TO_CHAR (i) || ': ' || SUBSTR
(l_msg_data, 1, 250));
END LOOP;
apps.fnd_file.put_line(apps.fnd_file.output,'Error Occured
while Creating Bank Branches: ');
END IF;
COMMIT;
END;

Step 10 : COMMIT;

Step 11 : Check the base tables APPS.CE_BANK_BRANCHES_V whether the records are populated .

Step 12 : BANK ACCOUNT CREATION :- Get the Bank ID and Branch ID from Branches.

-: BANK ACCOUNT CREATION :-

DECLARE
l_bank_acct_rec apps.iby_ext_bankacct_pub.extbankacct_rec_type;
out_mesg apps.iby_fndcpt_common_pub.result_rec_type;
l_acct NUMBER;
l_assign apps.iby_fndcpt_setup_pub.pmtinstrassignment_tbl_type;
l_payee_rec apps.iby_disbursement_setup_pub.payeecontext_rec_type;
l_return_status VARCHAR2 (30);
l_msg_count NUMBER;
l_msg_data VARCHAR2 (3000);
l_msg_dummy VARCHAR2 (3000);
l_output VARCHAR2 (3000);
l_bank_id NUMBER;
l_branch_id NUMBER;
l_bank VARCHAR2 (1000);
l_acct_owner_party_id NUMBER;
l_supplier_site_id NUMBER;
l_party_site_id NUMBER;
exec_bank_acct EXCEPTION;
BEGIN
l_return_status := '';
l_msg_count := '';
l_msg_data := '';
apps.fnd_msg_pub.delete_msg (NULL);
apps.fnd_msg_pub.initialize ();
---------------------------------------------------------------------
l_bank_acct_rec.branch_id := null;
l_bank_acct_rec.bank_id := null;
l_bank_acct_rec.acct_owner_party_id := null;
l_bank_acct_rec.bank_account_name := null;
l_bank_acct_rec.bank_account_num := null;
l_bank_acct_rec.iban := null;
l_bank_acct_rec.start_date := SYSDATE;
l_bank_acct_rec.country_code := null;
l_bank_acct_rec.currency := null;
l_bank_acct_rec.foreign_payment_use_flag := null;
l_bank_acct_rec.alternate_acct_name := null;
--------------------------------------------------------------------
l_bank_acct_rec.branch_id := 70120 ;
l_bank_acct_rec.bank_id := 70117 ;
l_bank_acct_rec.acct_owner_party_id := 69060 ;
l_bank_acct_rec.bank_account_name := 'Apps Test Supp 001';
l_bank_acct_rec.bank_account_num := 'TSB0000001';
l_bank_acct_rec.iban := 'TSBIBAN001';
l_bank_acct_rec.country_code := 'IN';
l_bank_acct_rec.currency := 'INR';
l_bank_acct_rec.foreign_payment_use_flag := 'Y';
apps.iby_ext_bankacct_pub.create_ext_bank_acct
(p_api_version => 1.0,
p_init_msg_list => 'F',
p_ext_bank_acct_rec => l_bank_acct_rec,
p_association_level => 'SS',
p_supplier_site_id => 5448,
p_party_site_id => 63040,
p_org_id => 124,
p_org_type => 'OPERATING_UNIT',
x_acct_id => l_acct,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
x_response => out_mesg
);
l_output := ' ';
IF l_return_status = fnd_api.g_ret_sts_error
THEN
FOR i IN 1 .. l_msg_count
LOOP
apps.fnd_msg_pub.get (i,
apps.fnd_api.g_false,
l_msg_data,
l_msg_dummy
);
l_output := l_output || (TO_CHAR (i) || ': ' || SUBSTR
(l_msg_data, 1, 250));
END LOOP;
apps.fnd_file.put_line
(apps.fnd_file.output,'Error Occured while
Creating Bank Account: ');
END IF;
END;

Step 14 : COMMIT ;

Step 15 : Check the base tables IBY_EXTERNAL_PAYEES_ALL whether the records are populated .