Using API to create Bank its Branch, supplier account and account linking to supplier.
The approach is to call main procedure on the package xx_create_bank_branch_acc.main and pass required parameter like Supplier name, Bank Name, Branch Name and Account number of Supplier.
In first process Package will check the existence of bank, if not exist then it will call function to create Bank, then main will check for Bank Branch, if not exist then create it. Now we will create Bank Account with name of Supplier and then on success call API to Assign Account with Supplier.
create or replace package xx_create_bank_branch_acc is
function create_bank(p_bank_name varchar2) return number;
Function create_bank_branch(p_bank_id number, p_bank_branch_name varchar2) return number;
function create_bank_account(p_supplier_name varchar2,
p_bank_id number,
p_bank_branch_id number,
p_bank_account_name varchar2,
p_bank_account_no number) return number;
procedure main(p_Supplier_Name varchar2, p_bank_name varchar2, p_bank_branch_name varchar2, p_account_number number);
end xx_create_bank_branch_acc;
/
create or replace package body xx_create_bank_branch_acc is
function create_bank(p_bank_name varchar2) return number is
lr_bank_type_rec iby_ext_bankacct_pub.extbank_rec_type;
lrx_response IBY_FNDCPT_COMMON_PUB.Result_rec_type;
lnx_bank_id NUMBER;
lcx_bnk_status VARCHAR2(10);
lnx_bnk_msg_cnt NUMBER;
lcx_bnk_msg_data VARCHAR2(1000);
begin
lr_bank_type_rec.institution_type := 'BANK';
lr_bank_type_rec.bank_name := p_bank_name;
lr_bank_type_rec.country_code := 'US';
lcx_bnk_msg_data := NULL;
iby_ext_bankacct_pub.create_ext_bank(p_api_version => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_ext_bank_rec => lr_bank_type_rec,
x_bank_id => lnx_bank_id,
x_return_status => lcx_bnk_status,
x_msg_count => lnx_bnk_msg_cnt,
x_msg_data => lcx_bnk_msg_data,
x_response => lrx_response);
if lcx_bnk_status = 'S' then
commit;
DBMS_OUTPUT.put_line('BANK CREATED : ' || lnx_bank_id);
else
IF lnx_bnk_msg_cnt > 1 THEN
dbms_output.put_line('Error while creating Bank.');
FOR I IN 1 .. lnx_bnk_msg_cnt LOOP
dbms_output.put_line(I || '.' || SubStr(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE), 1, 255));
END LOOP;
END IF;
end if;
return lnx_bank_id;
end create_bank;
function create_bank_branch(p_bank_id number, p_bank_branch_name varchar2) return number is
lr_bank_branch_type_rec iby_ext_bankacct_pub.extbankbranch_rec_type; -- for bank branch creation --
lrx_response IBY_FNDCPT_COMMON_PUB.Result_rec_type;
lnx_branch_id number;
lcx_status VARCHAR2(10);
lnx_msg_cnt NUMBER;
lcx_msg_data VARCHAR2(1000);
begin
lcx_status := NULL;
lcx_msg_data := NULL;
lnx_msg_cnt := NULL;
--******** Creating Bank Branch ****************
lr_bank_branch_type_rec.bank_party_id := p_bank_id;
lr_bank_branch_type_rec.branch_name := p_bank_branch_name;
lr_bank_branch_type_rec.branch_type := 'OTHER';
iby_ext_bankacct_pub.CREATE_EXT_BANK_BRANCH(p_api_version => 1.0,
p_init_msg_list => 'T',
p_ext_bank_branch_rec => lr_bank_branch_type_rec,
x_branch_id => lnx_branch_id,
x_return_status => lcx_status,
x_msg_count => lnx_msg_cnt,
x_msg_data => lcx_msg_data,
x_response => lrx_response);
if lcx_status = 'S' then
commit;
apps.fnd_file.put_line(apps.fnd_file.log, 'Branch ID : ' || lnx_branch_id);
else
IF lnx_msg_cnt > 1 THEN
dbms_output.put_line('Error while creating bank branch.');
FOR I IN 1 .. lnx_msg_cnt LOOP
dbms_output.put_line(I || '.' || SubStr(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE), 1, 255));
END LOOP;
END IF;
end if;
DBMS_OUTPUT.put_line('BANK BRANCH CREATED : ' || lnx_branch_id);
return lnx_branch_id;
end;
function create_bank_account(p_supplier_name varchar2,
p_bank_id number,
p_bank_branch_id number,
p_bank_account_name varchar2,
p_bank_account_no number) return number is
lr_extbankacct_rec_type apps.iby_ext_bankacct_pub.extbankacct_rec_type;
x_response IBY_FNDCPT_COMMON_PUB.Result_rec_type;
x_response2 IBY_FNDCPT_COMMON_PUB.Result_rec_type;
lr_payeecontext_rec_type apps.iby_disbursement_setup_pub.payeecontext_rec_type;
lr_pmtinstrument_rec_type apps.iby_fndcpt_setup_pub.pmtinstrument_rec_type;
lr_pmtinstrassignment_rec_type apps.iby_fndcpt_setup_pub.pmtinstrassignment_rec_type;
l_supp_vendor_id number;
l_supp_party_id number;
gc_ret_status char(1);
x_acct_id number;
x_assign_id number;
x_msg_count number;
x_msg_count2 number;
x_msg_data1 varchar2(1000);
x_msg_data2 varchar2(1000);
begin
SELECT vendor_id, party_id
into l_supp_vendor_id, l_supp_party_id
FROM apps.po_vendors
WHERE UPPER(vendor_name) = UPPER(p_supplier_name);
lr_ExtBankAcct_rec_type.country_code := 'US';
lr_ExtBankAcct_rec_type.branch_id := p_bank_branch_id;
lr_ExtBankAcct_rec_type.bank_id := p_bank_id;
lr_ExtBankAcct_rec_type.acct_owner_party_id := l_supp_party_id;
lr_ExtBankAcct_rec_type.bank_account_name := p_bank_account_name;
lr_ExtBankAcct_rec_type.bank_account_num := p_bank_account_no;
lr_ExtBankAcct_rec_type.currency := 'USD';
lr_ExtBankAcct_rec_type.start_date := sysdate;
gc_ret_status := 'X';
x_acct_id := NULL;
x_msg_count := 0;
x_msg_data1 := NULL;
x_msg_data2 := null;
apps.iby_ext_bankacct_pub.create_ext_bank_acct(p_api_version => 1,
p_init_msg_list => apps.fnd_api.g_true,
p_ext_bank_acct_rec => lr_extbankacct_rec_type,
x_acct_id => x_acct_id,
x_return_status => gc_ret_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data1,
x_response => x_response);
--*
if gc_ret_status = 'S' then
lr_PayeeContext_rec_type.Party_Id := l_supp_party_id;
lr_PayeeContext_rec_type.Payment_Function := 'PAYABLES_DISB';
lr_PmtInstrument_rec_type.Instrument_Id := x_acct_id;
lr_PmtInstrument_rec_type.Instrument_Type := 'BANKACCOUNT';
lr_PmtInstrAssignment_rec_type.start_date := SYSDATE;
lr_PmtInstrAssignment_rec_type.Instrument := lr_PmtInstrument_rec_type;
BEGIN
x_msg_data2 := NULL;
x_assign_id := NULL;
x_msg_count2 := NULL;
apps.iby_disbursement_setup_pub.set_payee_instr_assignment(p_api_version => 1.0,
p_init_msg_list => apps.fnd_api.g_false,
p_commit => apps.fnd_api.g_true,
x_return_status => gc_ret_status,
x_msg_count => x_msg_count2,
x_msg_data => x_msg_data2,
p_payee => lr_payeecontext_rec_type,
p_assignment_attribs => lr_pmtinstrassignment_rec_type,
x_assign_id => x_assign_id,
x_response => x_response2);
if gc_ret_status = 'S' then
commit;
apps.fnd_file.put_line(apps.fnd_file.log, 'AssignID : ' || x_assign_id);
else
IF x_msg_count2 > 1 THEN
dbms_output.put_line('Error while assingning Account to supplier.');
FOR I IN 1 .. x_msg_count2 LOOP
dbms_output.put_line(I || '.' || SubStr(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE), 1, 255));
END LOOP;
END IF;
end if;
apps.fnd_file.put_line(apps.fnd_file.log, 'AssignID : ' || x_assign_id);
end;
else
IF x_msg_count > 1 THEN
dbms_output.put_line('Error while supplier account.');
FOR I IN 1 .. x_msg_count LOOP
dbms_output.put_line(I || '.' || SubStr(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE), 1, 255));
END LOOP;
END IF;
end if;
return x_acct_id;
end;
procedure main(p_Supplier_Name varchar2, p_bank_name varchar2, p_bank_branch_name varchar2, p_account_number number) is
l_bank_id number;
l_bank_branch_id number;
l_account_id number;
begin
begin
SELECT hz_hp.party_id
INTO l_bank_id
FROM HZ_PARTIES hz_hp, HZ_ORGANIZATION_PROFILES hz_org, HZ_CODE_ASSIGNMENTS hz_ca
WHERE hz_ca.owner_table_id = hz_hp.party_id
AND hz_ca.owner_table_name = 'HZ_PARTIES'
AND hz_ca.class_category = 'BANK_INSTITUTION_TYPE'
AND hz_ca.class_code = 'BANK'
AND hz_hp.party_id = hz_org.party_id
AND SYSDATE BETWEEN TRUNC(hz_org.effective_start_date) AND NVL(TRUNC(hz_org.effective_end_date), SYSDATE + 1)
AND UPPER(hz_org.home_country) = UPPER('US')
AND UPPER(hz_hp.party_name) = UPPER(p_Supplier_Name);
exception
when no_data_found then
l_bank_id := create_bank(p_bank_name);
end;
begin
-- branch
SELECT hz_branch.party_id
INTO l_bank_branch_id
FROM HZ_PARTIES hz_branch, HZ_RELATIONSHIPS hz_rel, HZ_CODE_ASSIGNMENTS hz_branchca
WHERE hz_branchca.owner_table_name = 'HZ_PARTIES'
AND hz_branchca.owner_table_id = hz_branch.party_id
AND hz_branchca.class_category = 'BANK_INSTITUTION_TYPE'
AND hz_branchca.class_code = 'BANK_BRANCH'
AND NVL(hz_branchca.status, 'A') = 'A'
AND hz_rel.object_id = l_bank_id
AND hz_branch.party_id = hz_rel.subject_id
AND hz_rel.relationship_type = 'BANK_AND_BRANCH'
AND hz_rel.relationship_code = 'BRANCH_OF'
AND hz_rel.status = 'A'
AND hz_rel.subject_table_name = 'HZ_PARTIES'
AND hz_rel.subject_type = 'ORGANIZATION'
AND hz_rel.object_table_name = 'HZ_PARTIES'
AND hz_rel.object_type = 'ORGANIZATION'
AND UPPER(hz_branch.party_name) = UPPER(p_bank_branch_name);
exception
when no_data_found then
l_bank_branch_id := create_bank_branch(l_bank_id, p_bank_branch_name);
end; -- branch
l_account_id := create_bank_account(p_Supplier_Name,
l_bank_id,
l_bank_branch_id,
p_Supplier_Name, --p_bank_account_name
p_account_number);
dbms_output.put_line('Bank Account Created with AccountID - ' || l_account_id);
end;
end xx_create_bank_branch_acc;
/
TAGS---------------------------------------------------------------------------------
Oracle apps create bank API,
Oracle apps create bank branch API,
Oracle apps bank account API,
Oracle apps supplier account API,
Oracle ebs create bank API,
Oracle ebs create bank branch API,
Oracle ebs bank account API,
Oracle ebs supplier account API,
bank API,
branch API,
bank account API,
supplier account API,
iby_ext_bankacct_pub.create_ext_bank API,
iby_ext_bankacct_pub.CREATE_EXT_BANK_BRANCH API,
iby_ext_bankacct_pub.create_ext_bank_acct API,
iby_disbursement_setup_pub.set_payee_instr_assignment API,
create_ext_bank API,
CREATE_EXT_BANK_BRANCH API,
create_ext_bank_acct API,
set_payee_instr_assignment API
No comments:
Post a Comment