Tuesday, August 19, 2014

API to create bank account in Oracle Account Payables

API to create bank account in Oracle AP


Important Note: Please visit my posts
'API to create bank in Oracle Applications R12' in this blog to know how to create bank  in Oracle Applications.
'API to create bank branch in Oracle Applications R12' in this blog to know how to create bank branch in Oracle Applications.
'API to associate bank account to supplier site in Oracle Applications R12' in this blog to know how to associate created bank account to supllier site.

--Declaration
r_ext_bank_acct_rec iby_ext_bankacct_pub.extbankacct_rec_type;
x_acct_id NUMBER;
l_party_id NUMBER;
v_account_exist NUMBER;
v_bank_party_id NUMBER;
v_branch_party_id NUMBER;
x_bank_branch_count NUMBER;
v_return_status_account VARCHAR2 (100);
x_msg_count NUMBER;
x_response_rec iby_fndcpt_common_pub.result_rec_type;
x_msg_data VARCHAR2 (2000);
x_error_message VARCHAR2 (2000);
--c_validate_vendor_site_rec is cursor type of cursor crated on site table

--Derive party id of supplier by passing vendor id or name

SELECT party_id
INTO l_party_id
FROM ap_suppliers
WHERE vendor_id = c_validate_vendor_site_rec.vendor_id;

--Derive bank party id and branch party id by passing branch number

SELECT COUNT (1), branch_party_id,bank_party_id
INTO x_bank_branch_count, v_branch_party_id,v_bank_party_id
FROM iby_ext_bank_branches_v
WHERE UPPER (branch_number) =UPPER(c_validate_vendor_site_rec.branch_number)
GROUP BY branch_party_id, bank_party_id;

-- Verify bank account already exists or not by passing bank account number

SELECT COUNT (*)
INTO v_account_exist
FROM iby_ext_bank_accounts
WHERE bank_id = v_bank_party_id
AND branch_id = v_branch_party_id
AND bank_account_num =c_validate_vendor_site_rec.bank_account_number;


/* Please pass information as per your requirement*/
r_ext_bank_acct_rec.bank_account_id := NULL;
r_ext_bank_acct_rec.country_code :=c_validate_vendor_site_rec.country;
r_ext_bank_acct_rec.branch_id :=v_branch_party_id;
r_ext_bank_acct_rec.bank_id := v_bank_party_id;
r_ext_bank_acct_rec.acct_owner_party_id :=l_party_id;
r_ext_bank_acct_rec.bank_account_name :=p_bank_site;
r_ext_bank_acct_rec.bank_account_num :=
c_validate_vendor_site_rec.bank_account_number;
r_ext_bank_acct_rec.currency :=c_validate_vendor_site_rec.payment_currency_code;
r_ext_bank_acct_rec.iban := NULL;
r_ext_bank_acct_rec.check_digits := NULL;
r_ext_bank_acct_rec.multi_currency_allowed_flag := NULL;
r_ext_bank_acct_rec.alternate_acct_name := NULL;
r_ext_bank_acct_rec.short_acct_name := NULL;
r_ext_bank_acct_rec.acct_type := NULL;
r_ext_bank_acct_rec.acct_suffix := NULL;
r_ext_bank_acct_rec.description := NULL;
r_ext_bank_acct_rec.agency_location_code :=NULL;
r_ext_bank_acct_rec.foreign_payment_use_flag :=NULL;
r_ext_bank_acct_rec.exchange_rate_agreement_num := NULL;
r_ext_bank_acct_rec.exchange_rate_agreement_type :=NULL;
r_ext_bank_acct_rec.exchange_rate := NULL;
r_ext_bank_acct_rec.payment_factor_flag := NULL;
r_ext_bank_acct_rec.status := NULL;
r_ext_bank_acct_rec.end_date := NULL;
r_ext_bank_acct_rec.start_date := SYSDATE;
r_ext_bank_acct_rec.hedging_contract_reference := NULL;
r_ext_bank_acct_rec.attribute_category := NULL;
r_ext_bank_acct_rec.attribute1 :=c_validate_vendor_site_rec.attribute7;
r_ext_bank_acct_rec.attribute3 := NULL;
r_ext_bank_acct_rec.attribute4 := NULL;
r_ext_bank_acct_rec.attribute5 := NULL;
r_ext_bank_acct_rec.attribute6 := NULL;
r_ext_bank_acct_rec.attribute7 := NULL;
r_ext_bank_acct_rec.attribute8 := NULL;
r_ext_bank_acct_rec.attribute9 := NULL;
r_ext_bank_acct_rec.attribute10 := NULL;
r_ext_bank_acct_rec.attribute11 := NULL;
r_ext_bank_acct_rec.attribute12 := NULL;
r_ext_bank_acct_rec.attribute13 := NULL;
r_ext_bank_acct_rec.attribute14 := NULL;
x_error_message varchar2(2000);
r_ext_bank_acct_rec.object_version_number :=1.0;

iby_ext_bankacct_pub.create_ext_bank_acct
(p_api_version => 1.0,
p_init_msg_list => fnd_api.g_false,
p_ext_bank_acct_rec => r_ext_bank_acct_rec,
x_acct_id => x_acct_id,
x_return_status => v_return_status_account,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_response => x_response_rec
); /* For Bank Account Creation */

COMMIT;

IF v_return_status_account <> 'S'
THEN
--error
--print error message or use x_msg_data
x_error_message:=fnd_msg_pub.get_detail (x_msg_count, 'F' );
FOR i IN x_msg_count .. 1
LOOP
fnd_msg_pub.delete_msg (i);
END LOOP;
else
--success
end if;

2 comments:

  1. i am not able to upload supplier bank details in oracle apps r12. i am getting "bank account info invalid" error message.

    ReplyDelete
  2. Regards
    Sridevi Koduru (Senior Oracle Apps Trainer Oracleappstechnical.com)
    LinkedIn profile - https://in.linkedin.com/in/sridevi-koduru-9b876a8b
    Please Contact for One to One Online Training on Oracle Apps Technical, Financials, SCM, SQL, PL/SQL, D2K at training@oracleappstechnical.com | +91 - 9581017828.

    ReplyDelete