ORACLE MASTERMINDS: API to Create Bank Account Details

Thursday 4 January 2018

API to Create Bank Account Details

Below procedure will create Supplir Bank account details

PROCEDURE create_banks_accounts(p_change_type VARCHAR2)
AS
ln_count NUMBER;
ln_success_count NUMBER;
ln_error_count NUMBER;
ln_x_bank_id NUMBER;
ln_x_branch_id NUMBER;
ln_bulk_error_cnt NUMBER;

gc_ret_status VARCHAR2(15);
gn_msg_count NUMBER;
gc_msg_data VARCHAR2(3000);

lr_response IBY_FNDCPT_COMMON_PUB.Result_rec_type;
lc_msg_data VARCHAR2(3000);
ln_acct_id NUMBER;
ln_msg_count NUMBER;
lc_return_status VARCHAR2(10);
lc_org_type VARCHAR2(30);
ln_org_id NUMBER;
ln_party_site_id NUMBER;
ln_supplier_site_id NUMBER;
lc_association_level VARCHAR(15);
lr_extbank_rec IBY_EXT_BANKACCT_PUB.ExtBankAcct_rec_type;
x_data VARCHAR2(3000);
x_msg_count NUMBER;
x_msg_index_out NUMBER;
--Declartion payee type variables
lr_External_Payee_Tab_Type apps.iby_disbursement_setup_pub.External_Payee_Tab_Type;
lr_external_payee_rec_type apps.iby_disbursement_setup_pub.external_payee_rec_type;
lr_Ext_Payee_ID_Tab_Type apps.iby_disbursement_setup_pub.Ext_Payee_ID_Tab_Type;
lr_Ext_Payee_Create_Tab_Type apps.iby_disbursement_setup_pub.Ext_Payee_Create_Tab_Type;
lr_result_rec_type apps.IBY_FNDCPT_COMMON_PUB.Result_rec_type;
ln_account_assign_id NUMBER;

ln_x_msg_count NUMBER;
lc_x_msg_data VARCHAR2(3000);
lc_return_sts VARCHAR2(5);
ln_joint_owner_id NUMBER;

--Setting payee Instrument Assignment varibales
lr_payee_rec IBY_DISBURSEMENT_SETUP_PUB.PayeeContext_rec_type;
lr_assg_attr IBY_FNDCPT_SETUP_PUB.PmtInstrAssignment_rec_type;
--lr_response IBY_FNDCPT_COMMON_PUB.Result_rec_type;
lc_pay_instr_ret_status VARCHAR2(15);
ln_assg_id NUMBER;
ln_pay_instr_msg_count NUMBER;
lc_pay_instr_msg_data VARCHAR2(3000);

ln_sl_bank_party_id NUMBER; --second level validation
ln_sl_branch_party_id NUMBER; --second level validation
ln_sl_account_id NUMBER; --second level validation

--========================
--Cursor to fetch count
--========================
CURSOR lcu_count(cp_status VARCHAR2)
IS
SELECT COUNT(1) FROM xxkly_ap_bank_accounts_stg WHERE status = cp_status;
--========================
--Cursor to validate Banks
--========================

CURSOR lcu_banks(cp_bank_name VARCHAR2, cp_home_country VARCHAR2)
IS
SELECT bank_party_id
FROM ce_banks_v
WHERE home_country = cp_home_country
AND UPPER(bank_name) = UPPER(cp_bank_name)
AND end_date IS NULL;

--==============================
--Cursor to validate Bank branch
--==============================
CURSOR lcu_branch_name (cp_branch_number VARCHAR2, cp_country VARCHAR2, cp_bank_num VARCHAR2) -- cp_bank_party_id NUMBER,
IS
SELECT --bank_party_id,
branch_party_id
--, bank_home_country
FROM ce_bank_branches_v
WHERE UPPER(branch_number) = UPPER(cp_branch_number)
--AND bank_party_id = cp_bank_party_id
AND BANK_HOME_COUNTRY = cp_country
AND NVL(BANK_NUMBER, 'X') = NVL(cp_bank_num, 'X');

--==============================
--Cursor validate Bank branch
--==============================
CURSOR lcu_account(cp_account_number VARCHAR2, cp_bank_id NUMBER, cp_country_code VARCHAR2)
IS
SELECT ext_bank_account_id bank_account_id
--, branch_id
--, bank_id
--, country_code
FROM iby_ext_bank_accounts
WHERE UPPER(bank_account_num ) = UPPER(cp_account_number)
AND bank_id = cp_bank_id
AND country_code = cp_country_code;

--=======================================================
--Cursor to Check if supplier is the owner of the account
--=======================================================

CURSOR lcu_account_assign( cp_vendor_party_id NUMBER
,cp_vendor_site_party_id NUMBER
,cp_vendor_site_id NUMBER
,cp_org_id NUMBER
,cp_account_id NUMBER
)
IS
SELECT instr_assignment_id
FROM apps.iby_payee_assigned_bankacct_v
WHERE party_id = cp_vendor_party_id
AND party_site_id = cp_vendor_site_party_id
AND supplier_site_id = cp_vendor_site_id
AND org_id = cp_org_id
AND ext_bank_account_id = cp_account_id;

--============================================================
--Cursor to fetch data to create banks, branches and accounts
--============================================================
CURSOR lcu_data (cp_status VARCHAR2)
IS
SELECT * FROM xxkly_ap_bank_accounts_stg WHERE status = cp_status;

TYPE ap_supp_banks_stg IS TABLE OF xxkly_ap_bank_accounts_stg%ROWTYPE INDEX BY BINARY_INTEGER;
lt_ap_banks_stg ap_supp_banks_stg;

BEGIN
apps.fnd_file.put_line(apps.fnd_file.log, '---------------------Start of Create Banks Procedure---------------');
ln_count := NULL;
ln_success_count := NULL;
ln_error_count := NULL;

OPEN lcu_count('V');
FETCH lcu_count INTO ln_count;
CLOSE lcu_count;

OPEN lcu_data('V');
LOOP
lt_ap_banks_stg.DELETE;
FETCH lcu_data BULK COLLECT INTO lt_ap_banks_stg LIMIT 10000;
IF lt_ap_banks_stg.COUNT >0 THEN
--Creating Bank
FOR i in 1..lt_ap_banks_stg.COUNT LOOP
apps.fnd_file.put_line(apps.fnd_file.log,'***************Start of Supplier Bank Detials***************');
ln_x_bank_id := NULL;
ln_x_branch_id := NULL;
lt_ap_banks_stg(i).error_message := NULL;
ln_acct_id := NULL;
ln_account_assign_id := NULL;
fnd_global.apps_initialize(fnd_global.user_id,fnd_global.resp_id,fnd_global.resp_appl_id);
mo_global.init('AR');

IF lt_ap_banks_stg(i).create_bank_flag = 'Y' THEN
apps.fnd_file.put_line(apps.fnd_file.log,'Track 1');

--Second level of validatio of banks
ln_sl_bank_party_id := NULL;
OPEN lcu_banks(lt_ap_banks_stg(i).bank_name,lt_ap_banks_stg(i).home_country_code );
FETCH lcu_banks INTO ln_sl_bank_party_id;
CLOSE lcu_banks;

IF ln_sl_bank_party_id IS NULL THEN
BEGIN
apps.fnd_file.put_line(apps.fnd_file.log,'==>Invoking Create Bank API<==');
apps.ce_bank_pub.create_bank(
p_init_msg_list => apps.fnd_api.g_true
,p_country_code => lt_ap_banks_stg(i).home_country_code
,p_bank_name => lt_ap_banks_stg(i).bank_name
,p_bank_number => lt_ap_banks_stg(i).bank_number
,p_alternate_bank_name => lt_ap_banks_stg(i).bank_name_alt
,p_short_bank_name => lt_ap_banks_stg(i).short_bank_name
,p_description => lt_ap_banks_stg(i).description
-- ,p_tax_payer_id => p_tax_payer_id
-- ,p_tax_registration_number => p_tax_registration_number
,x_bank_id => ln_x_bank_id
,x_return_status => gc_ret_status
,x_msg_count => gn_msg_count
,x_msg_data => gc_msg_data
);

IF gc_ret_status = 'S' THEN

apps.fnd_file.put_line(apps.fnd_file.log, 'Bank Created: '||ln_x_bank_id);
lt_ap_banks_stg(i).bank_party_id:= ln_x_bank_id;
lt_ap_banks_stg(i).error_message := lt_ap_banks_stg(i).error_message ||', Bank Created' ;
lt_ap_banks_stg(i).status := 'P';

ELSE

apps.fnd_file.put_line(apps.fnd_file.log,'API-MSG-> Bank creation Failed: '|| gc_ret_status||' with '||gc_msg_data);
lt_ap_banks_stg(i).error_message:=lt_ap_banks_stg(i).error_message||', BankNot Created!';
lt_ap_banks_stg(i).status := 'E';

END IF;
apps.fnd_file.put_line(apps.fnd_file.log,'Track Create Bank1');
EXCEPTION
WHEN OTHERS THEN
gc_ret_status := apps.fnd_api.g_ret_sts_error;
apps.fnd_file.put_line(apps.fnd_file.log,'API-MSG-> create_bank: '|| gc_ret_status);
lt_ap_banks_stg(i).error_message:=lt_ap_banks_stg(i).error_message||', CREXTBNK-> '||SQLERRM;
lt_ap_banks_stg(i).status := 'E';

END;

ELSE
lt_ap_banks_stg(i).error_message := lt_ap_banks_stg(i).error_message||', Bank Exists';
lt_ap_banks_stg(i).create_bank_flag := 'N';
lt_ap_banks_stg(i).status := 'S';
lt_ap_banks_stg(i).bank_party_id := ln_sl_bank_party_id;

END IF;
END IF;
apps.fnd_file.put_line(apps.fnd_file.log,'Track 2: Status:'|| lt_ap_banks_stg(i).status);
--Creating Branch
IF NVL(lt_ap_banks_stg(i).status, 'X') <> 'E'
AND lt_ap_banks_stg(i).create_branch_flag = 'Y' THEN
apps.fnd_file.put_line(apps.fnd_file.log,'Track 3: Creating Branch');

--Second level of validatio of branches
ln_sl_branch_party_id := NULL;
OPEN lcu_branch_name (lt_ap_banks_stg(i).branch_number, lt_ap_banks_stg(i).home_country_code, NULL);
FETCH lcu_branch_name INTO ln_sl_branch_party_id;
CLOSE lcu_branch_name;

apps.fnd_file.put_line(apps.fnd_file.log,'Track 3A : Branch Party ID: '|| ln_sl_branch_party_id);

IF ln_sl_branch_party_id IS NULL THEN
BEGIN
apps.fnd_file.put_line(apps.fnd_file.log,'==>Invoking Create Bank Branch API<==');
-- Create Bank Branch
gc_ret_status := NULL;
gn_msg_count := NULL;
gc_msg_data := NULL;
apps.fnd_file.put_line(apps.fnd_file.log,'Track Create Branch3') ;

apps.ce_bank_pub.create_bank_branch(
p_init_msg_list => apps.fnd_api.g_true
, p_bank_id => lt_ap_banks_stg(i).bank_party_id
, p_branch_name => lt_ap_banks_stg(i).bank_branch_name
, p_branch_number => lt_ap_banks_stg(i).branch_number --p_branch_number
--,p_branch_type => lt_ap_banks_stg(i).p_branch_type
,p_alternate_branch_name => lt_ap_banks_stg(i).bank_branch_name_alt
,p_description =>lt_ap_banks_stg(i).br_description
-- ,p_bic =>
-- ,p_eft_number =>
-- ,p_rfc_identifier =>
-- Out Parameters
, x_branch_id => ln_x_branch_id
, x_return_status => gc_ret_status
, x_msg_count => gn_msg_count
, x_msg_data => gc_msg_data
);

IF gc_ret_status = 'S' THEN

apps.fnd_file.put_line(apps.fnd_file.log,'Bank Branch Created @ 1: '||ln_x_branch_id);
lt_ap_banks_stg(i).br_branch_party_id := ln_x_branch_id;
lt_ap_banks_stg(i).error_message := lt_ap_banks_stg(i).error_message ||', Branch Created';
lt_ap_banks_stg(i).status := 'P';

ELSE

apps.fnd_file.put_line(apps.fnd_file.log,'API-MSG-> Bank Branch creation Failed @ 1: '|| gc_ret_status||' with '||gc_msg_data);
lt_ap_banks_stg(i).status := 'E';
lt_ap_banks_stg(i).error_message := lt_ap_banks_stg(i).error_message ||'Bank Branch creation Failed: '||gc_msg_data ;

END IF;

EXCEPTION
WHEN OTHERS THEN
gc_ret_status := apps.fnd_api.g_ret_sts_error;
apps.fnd_file.put_line(apps.fnd_file.log,'API_ERR-MSG-> create_bank_branch - 1: '|| gc_ret_status||' with '||gc_msg_data);
lt_ap_banks_stg(i).error_message:= lt_ap_banks_stg(i).error_message||', CREXTBRNCH-> '||SQLERRM;
END;

ELSE
lt_ap_banks_stg(i).br_branch_party_id := ln_sl_branch_party_id;
lt_ap_banks_stg(i).create_branch_flag := 'N';
lt_ap_banks_stg(i).status := 'S';
lt_ap_banks_stg(i).error_message := lt_ap_banks_stg(i).error_message ||', Branch Exists';

END IF;

END IF;
--Creating account
apps.fnd_file.put_line(apps.fnd_file.log,'Track4: Status :'||lt_ap_banks_stg(i).status) ;
IF lt_ap_banks_stg(i).create_account_flag = 'Y'
AND lt_ap_banks_stg(i).status <> 'E' THEN
NULL;
apps.fnd_file.put_line(apps.fnd_file.log,'Track5: Creating Account');
ln_sl_account_id := NULL;
OPEN lcu_account(lt_ap_banks_stg(i).bank_account_num, lt_ap_banks_stg(i).bank_party_id, lt_ap_banks_stg(i).home_country_code) ;
FETCH lcu_account INTO ln_sl_account_id;
CLOSE lcu_account;

IF ln_sl_account_id IS NULL THEN
BEGIN
apps.fnd_file.put_line(apps.fnd_file.log,'==>Invoking Create External Bank Account API<==');
fnd_global.apps_initialize(fnd_global.user_id,fnd_global.resp_id,fnd_global.resp_appl_id);
mo_global.init('AR');
--apps.fnd_file.put_line(apps.fnd_file.log,'Track Create Account4') ;
lc_org_type := 'OPERATING_UNIT';
--ln_org_id := 101;

lr_extbank_rec.bank_account_id := NULL ; -- iby_ext_bank_accounts.EXT_BANK_ACCOUNT_ID%TYPE,
lr_extbank_rec.country_code := nvl(lt_ap_banks_stg(i).br_home_country, lt_ap_banks_stg(i).home_country_code); --NULL ; -- iby_ext_bank_accounts.COUNTRY_CODE%TYPE,
lr_extbank_rec.branch_id := lt_ap_banks_stg(i).br_branch_party_id ; -- iby_ext_bank_accounts.BRANCH_ID%TYPE,
lr_extbank_rec.bank_id := lt_ap_banks_stg(i).bank_party_id ; -- iby_ext_bank_accounts.BANK_ID%TYPE,
lr_extbank_rec.acct_owner_party_id := lt_ap_banks_stg(i).vendor_party_id ; --Supplier party id-- iby_account_owners.ACCOUNT_OWNER_PARTY_ID%TYPE,
lr_extbank_rec.bank_account_name := lt_ap_banks_stg(i).bank_account_name ; -- iby_ext_bank_accounts.BANK_ACCOUNT_NAME%TYPE,
lr_extbank_rec.bank_account_num := lt_ap_banks_stg(i).bank_account_num ; -- iby_ext_bank_accounts.BANK_ACCOUNT_NUM%TYPE,
lr_extbank_rec.currency := lt_ap_banks_stg(i).currency_code ; -- iby_ext_bank_accounts.CURRENCY_CODE%TYPE,
lr_extbank_rec.iban := lt_ap_banks_stg(i).iban ; -- iby_ext_bank_accounts.IBAN%TYPE,
lr_extbank_rec.check_digits := lt_ap_banks_stg(i).check_digits ; -- iby_ext_bank_accounts.CHECK_DIGITS%TYPE,
lr_extbank_rec.multi_currency_allowed_flag := NULL ; -- varchar2(1),
lr_extbank_rec.alternate_acct_name := lt_ap_banks_stg(i).bank_account_name_alt ; -- iby_ext_bank_accounts.BANK_ACCOUNT_NAME_ALT%TYPE,
lr_extbank_rec.short_acct_name := lt_ap_banks_stg(i).short_acct_name ; -- iby_ext_bank_accounts.SHORT_ACCT_NAME%TYPE,
lr_extbank_rec.acct_type := NULL ; -- iby_ext_bank_accounts.BANK_ACCOUNT_TYPE%TYPE,
lr_extbank_rec.acct_suffix := NULL ; -- iby_ext_bank_accounts.ACCOUNT_SUFFIX%TYPE,
lr_extbank_rec.description := lt_ap_banks_stg(i).acc_description ; -- iby_ext_bank_accounts.DESCRIPTION%TYPE,
lr_extbank_rec.agency_location_code := NULL ; -- iby_ext_bank_accounts.AGENCY_LOCATION_CODE%TYPE,
--lr_extbank_rec.foreign_payment_use_flag :='N' ; --iby_ext_bank_accounts.FOREIGN_PAYMENT_USE_FLAG%TYPE,
lr_extbank_rec.exchange_rate_agreement_num := NULL ; -- iby_ext_bank_accounts.EXCHANGE_RATE_AGREEMENT_NUM%TYPE,
lr_extbank_rec.exchange_rate_agreement_type := NULL ; --iby_ext_bank_accounts.EXCHANGE_RATE_AGREEMENT_TYPE%TYPE,
lr_extbank_rec.exchange_rate := NULL ; -- iby_ext_bank_accounts.EXCHANGE_RATE%TYPE,
lr_extbank_rec.payment_factor_flag := NULL ; -- iby_ext_bank_accounts.PAYMENT_FACTOR_FLAG%TYPE,
lr_extbank_rec.status := NULL ; --varchar2(1),
lr_extbank_rec.end_date := NULL ;--lt_ap_banks_stg(i).acc_end_date ; --iby_ext_bank_accounts.END_DATE%TYPE,
lr_extbank_rec.START_DATE := lt_ap_banks_stg(i).start_date ; --iby_ext_bank_accounts.START_DATE%TYPE,
lr_extbank_rec.hedging_contract_reference := NULL ; --iby_ext_bank_accounts.HEDGING_CONTRACT_REFERENCE%TYPE,
lr_extbank_rec.attribute_category := NULL ; -- iby_ext_bank_accounts.ATTRIBUTE_CATEGORY%TYPE,
lr_extbank_rec.attribute1 := NULL ; -- iby_ext_bank_accounts.ATTRIBUTE1%TYPE,
lr_extbank_rec.attribute2 := NULL ; -- iby_ext_bank_accounts.ATTRIBUTE2%TYPE,
lr_extbank_rec.attribute3 := NULL ; -- iby_ext_bank_accounts.ATTRIBUTE3%TYPE,
lr_extbank_rec.attribute4 := NULL ; --iby_ext_bank_accounts.ATTRIBUTE4%TYPE,
lr_extbank_rec.attribute5 := NULL ; --iby_ext_bank_accounts.ATTRIBUTE5%TYPE,
lr_extbank_rec.attribute6 := NULL ; --iby_ext_bank_accounts.ATTRIBUTE6%TYPE,
lr_extbank_rec.attribute7 := NULL ; --iby_ext_bank_accounts.ATTRIBUTE7%TYPE,
lr_extbank_rec.attribute8 := NULL ; --iby_ext_bank_accounts.ATTRIBUTE8%TYPE,
lr_extbank_rec.attribute9 := NULL ; --iby_ext_bank_accounts.ATTRIBUTE9%TYPE,
lr_extbank_rec.attribute10 := NULL ; --iby_ext_bank_accounts.ATTRIBUTE10%TYPE,
lr_extbank_rec.attribute11 := NULL ; --iby_ext_bank_accounts.ATTRIBUTE11%TYPE,
lr_extbank_rec.attribute12 := NULL ; --iby_ext_bank_accounts.ATTRIBUTE12%TYPE,
lr_extbank_rec.attribute13 := NULL ; --iby_ext_bank_accounts.ATTRIBUTE13%TYPE,
lr_extbank_rec.attribute14 := NULL ; --iby_ext_bank_accounts.ATTRIBUTE14%TYPE,
lr_extbank_rec.attribute15 := NULL ; --iby_ext_bank_accounts.ATTRIBUTE15%TYPE,
lr_extbank_rec.object_version_number := NULL ; --iby_ext_bank_accounts.OBJECT_VERSION_NUMBER%TYPE,
lr_extbank_rec.secondary_account_reference := NULL ; -- iby_ext_bank_accounts.SECONDARY_ACCOUNT_REFERENCE%TYPE, -- Bug 7408747(Added New Parameter to save Secondary Account Reference),
lr_extbank_rec.contact_name := NULL ; --iby_ext_bank_accounts.CONTACT_NAME%TYPE, -- New columns for CLM Reference Data Management uptake.
lr_extbank_rec.contact_phone := NULL ; -- iby_ext_bank_accounts.CONTACT_PHONE%TYPE,
lr_extbank_rec.contact_email := NULL ; -- iby_ext_bank_accounts.CONTACT_EMAIL%TYPE,
lr_extbank_rec.contact_fax := NULL ; --iby_ext_bank_accounts.CONTACT_FAX%TYPE

IBY_EXT_BANKACCT_PUB.create_ext_bank_acct (
p_api_version => 1.0 --IN NUMBER,
,p_init_msg_list => fnd_api.g_true --IN VARCHAR2,
,p_ext_bank_acct_rec => lr_extbank_rec --IN ExtBankAcct_rec_type,
,p_association_level => 'SS' --IN VARCHAR2,
,p_supplier_site_id => lt_ap_banks_stg(i).vendor_site_id --1010 --IN NUMBER, --vendor site code SONY CHAKKALAKK
,p_party_site_id => lt_ap_banks_stg(i).vendor_site_party_id--5024 --IN NUMBER,
,p_org_id => lt_ap_banks_stg(i).org_id --IN NUMBER,
,p_org_type => lc_org_type --IN VARCHAR2 default NULL, --Bug7136876: new parameter
,x_acct_id => ln_acct_id --OUT NOCOPY NUMBER,
,x_return_status => lc_return_status --OUT NOCOPY VARCHAR2,
,x_msg_count => ln_msg_count --OUT NOCOPY NUMBER,
,x_msg_data => lc_msg_data --OUT NOCOPY VARCHAR2,
,x_response => lr_response --OUT NOCOPY IBY_FNDCPT_COMMON_PUB.Result_rec_type
);
apps.fnd_file.put_line(apps.fnd_file.log,'Oracle Bank Account ID: '||ln_acct_id);
apps.fnd_file.put_line(apps.fnd_file.log,'Result rec type Code: '|| lr_response.Result_Code);
apps.fnd_file.put_line(apps.fnd_file.log,'Result rec type Category: '||lr_response.Result_Category);
apps.fnd_file.put_line(apps.fnd_file.log,'Result rec type Message: '|| lr_response.Result_Message);
apps.fnd_file.put_line(apps.fnd_file.log,'Return Status: '|| lc_return_status);

IF lc_return_status = 'S' THEN
lt_ap_banks_stg(i).bank_account_id := ln_acct_id;
lt_ap_banks_stg(i).error_message := lt_ap_banks_stg(i).error_message ||', Bank Account Created';
lt_ap_banks_stg(i).status := 'P';
END IF;

IF lc_return_status <> 'S' THEN
lt_ap_banks_stg(i).status := 'E';
lt_ap_banks_stg(i).error_message := lt_ap_banks_stg(i).error_message ||', CRTACCT- '||lc_return_status||' with '||lc_msg_data;
apps.fnd_file.put_line(apps.fnd_file.log,'API-MSG-> Bank Accnt creation Failed: '|| lc_return_status||' with '||lc_msg_data);
apps.fnd_file.put_line(apps.fnd_file.log,'Bank Accnt Status : '||lc_return_status);
apps.fnd_file.put_line(apps.fnd_file.log,'Bank Accnt Count : '||ln_msg_count);
apps.fnd_file.put_line(apps.fnd_file.log,'Bank Accnt Data : '||lc_msg_data);

x_data := NULL;
FOR i IN 1..ln_msg_count LOOP
apps.pa_interface_utils_pub.get_messages(p_msg_data => lc_msg_data
,p_encoded =>'F'
,p_msg_index => i -- ln_msg_count
,p_data => x_data
,p_msg_count => x_msg_count
,p_msg_index_out => x_msg_index_out
);
apps.fnd_file.put_line(apps.fnd_file.log,' Create Accnt :-> '||ln_msg_count || ', '|| lc_msg_data||'>'||x_data);
END LOOP;

lt_ap_banks_stg(i).error_message:=lt_ap_banks_stg(i).error_message||', CRTACCNT-> '||x_data;

END IF;

END;
ELSE
lt_ap_banks_stg(i).error_message :=lt_ap_banks_stg(i).error_message||', Account Exist for same bank, same country';
lt_ap_banks_stg(i).status := 'S';
lt_ap_banks_stg(i).bank_account_id := ln_sl_account_id;
lt_ap_banks_stg(i).create_account_flag := 'N';

END IF;
--*/

END IF;
--Creating supplier as ext payee
apps.fnd_file.put_line(apps.fnd_file.log,'Track 6: Status: ' ||lt_ap_banks_stg(i).status);
IF lt_ap_banks_stg(i).create_ext_payee_flag = 'Y'
AND lt_ap_banks_stg(i).status <> 'E' THEN
apps.fnd_file.put_line(apps.fnd_file.log,'==>Invoking Create External Payee API<==');
NULL;
gc_ret_status := 'X';
ln_msg_count := 0;
lc_msg_data := NULL;
-- apps.IBY_DISBURSEMENT_SETUP_PUB.Create_External_Payee
lr_External_Payee_Rec_Type.Payee_Party_Id := lt_ap_banks_stg(i).vendor_party_id;
lr_External_Payee_Rec_Type.Payee_Party_Site_Id := lt_ap_banks_stg(i).vendor_site_party_id;
lr_External_Payee_Rec_Type.payment_function := 'PAYABLES_DISB';

lr_External_Payee_Rec_Type.payer_org_id := lt_ap_banks_stg(i).org_id;
lr_External_Payee_Rec_Type.payer_org_type := 'OPERATING_UNIT';
lr_External_Payee_Rec_Type.Exclusive_Pay_Flag := 'N';
--lr_External_Payee_Rec_Type.Default_Pmt_method := 'CHECK';
lr_External_Payee_Rec_Type.Supplier_Site_Id := lt_ap_banks_stg(i).vendor_site_id;

lr_External_Payee_Tab_Type(0) := lr_External_Payee_Rec_Type;

BEGIN
apps.iby_disbursement_setup_pub.Create_External_Payee(
p_api_version => 1.0,
p_init_msg_list => apps.fnd_api.g_false,
p_ext_payee_tab => lr_external_payee_tab_type,
x_return_status => gc_ret_status,
x_msg_count => ln_msg_count,
x_msg_data => lc_msg_data,
x_ext_payee_id_tab => lr_ext_payee_id_tab_type,
x_ext_payee_status_tab => lr_ext_payee_create_tab_type
);

EXCEPTION
WHEN OTHERS THEN
apps.fnd_file.put_line(apps.fnd_file.log,'API-MSG-> Create_External_Payee: '|| gc_ret_status||':> '||ln_acct_id ||', '
|| ln_msg_count|| ' with ' ||lc_msg_data);
apps.fnd_file.put_line(apps.fnd_file.log,'API-MSG-> Create_External_Payee: '||SQLERRM);
lt_ap_banks_stg(i).error_message:=lt_ap_banks_stg(i).error_message||', CREXTPYE-> '||SQLERRM;
gc_ret_status := apps.fnd_api.g_ret_sts_error;
END;

END IF;
--Checking if supplier is the owner of the account
ln_account_assign_id := NULL;
OPEN lcu_account_assign(lt_ap_banks_stg(i).vendor_party_id,
lt_ap_banks_stg(i).vendor_site_party_id,
lt_ap_banks_stg(i).vendor_site_id,
lt_ap_banks_stg(i).org_id,
lt_ap_banks_stg(i).bank_account_id
);
FETCH lcu_account_assign INTO ln_account_assign_id;
CLOSE lcu_account_assign;
apps.fnd_file.put_line(apps.fnd_file.log,'Track 7: Status: ' ||lt_ap_banks_stg(i).status);
IF lt_ap_banks_stg(i).status <> 'E' THEN
IF ln_account_assign_id IS NOT NULL THEN

apps.fnd_file.put_line(apps.fnd_file.log,'Track 8');
--/*
lt_ap_banks_stg(i).error_message := lt_ap_banks_stg(i).error_message||', AccountAssignment Already Exists ';
lt_ap_banks_stg(i).status := 'P';
lt_ap_banks_stg(i).instr_assignment_id:= ln_account_assign_id;
--*/
ELSE
apps.fnd_file.put_line(apps.fnd_file.log,'Track 9');
--/*
ln_msg_count := NULL;
lc_msg_data := NULL;
gc_ret_status := 'X';
lr_result_rec_type := NULL;
BEGIN
apps.fnd_file.put_line(apps.fnd_file.log,'Bank Account ID: ' || lt_ap_banks_stg(i).bank_account_id);
apps.fnd_file.put_line(apps.fnd_file.log,'Vendor Party ID: ' || lt_ap_banks_stg(i).vendor_party_id);
apps.IBY_EXT_BANKACCT_PUB.check_bank_acct_owner(p_api_version => 1.0,
p_init_msg_list => apps.fnd_api.g_false,
p_bank_acct_id => lt_ap_banks_stg(i).bank_account_id,
p_acct_owner_party_id => lt_ap_banks_stg(i).vendor_party_id,
x_return_status => gc_ret_status,
x_msg_count => ln_msg_count,
x_msg_data => lc_msg_data,
x_response => lr_result_rec_type
);
EXCEPTION WHEN OTHERS THEN

apps.fnd_file.put_line(apps.fnd_file.log,'Error in API checking Acct Owner:' ||SQLCODE ||': '||SQLERRM);
lt_ap_banks_stg(i).status := 'E';
lt_ap_banks_stg(i).error_message := lt_ap_banks_stg(i).error_message ||', Error in API checking Acct Owner' ;

END;
apps.fnd_file.put_line(apps.fnd_file.log,'Track 10: Acct Status: '|| gc_ret_status);
--Assign the Supplier as the account owner
IF gc_ret_status <> 'S' THEN

BEGIN
ln_joint_owner_id := NULL;
ln_x_msg_count := NULL;
lc_x_msg_data := NULL;
lc_return_sts := 'X';

apps.IBY_EXT_BANKACCT_PUB.add_joint_account_owner(p_api_version => 1.0,
p_init_msg_list => apps.fnd_api.g_true,
p_bank_account_id => lt_ap_banks_stg(i).bank_account_id,
p_acct_owner_party_id => lt_ap_banks_stg(i).vendor_party_id,
x_joint_acct_owner_id => ln_joint_owner_id,
x_return_status => lc_return_sts,
x_msg_count => ln_x_msg_count,
x_msg_data => lc_x_msg_data,
x_response => lr_result_rec_type
);

apps.fnd_file.put_line(apps.fnd_file.log,'Track 11 Adding Joint Owner Status: '|| lc_return_sts);
IF lc_return_sts = 'S' THEN

lt_ap_banks_stg(i).status := 'SS';
lt_ap_banks_stg(i).error_message := lt_ap_banks_stg(i).error_message ||', JointID- '||ln_joint_owner_id;
lt_ap_banks_stg(i).joint_owner_id := ln_joint_owner_id;
apps.fnd_file.put_line(apps.fnd_file.log,'JointAccount Created for Account '||ln_acct_id||' with JointID '|| ln_joint_owner_id);

ELSE

lt_ap_banks_stg(i).status := 'E';
lt_ap_banks_stg(i).error_message := lt_ap_banks_stg(i).error_message ||', JointID not Created '||lc_x_msg_data;
apps.fnd_file.put_line(apps.fnd_file.log,'JointAccount not Created for Account '||ln_acct_id||' with error '|| lc_x_msg_data);

END IF;

EXCEPTION WHEN OTHERS THEN
apps.fnd_file.put_line(apps.fnd_file.log,'Error in API Adding Joint Acct Owner:' ||SQLCODE ||': '||SQLERRM);
lt_ap_banks_stg(i).status := 'E';
lt_ap_banks_stg(i).error_message := lt_ap_banks_stg(i).error_message ||', Error in API Adding Joint Acct Owner' ;

END;

END IF;
--*/
END IF; --<<end_if_vendor_account_assignment>>
END IF;
apps.fnd_file.put_line(apps.fnd_file.log,'Track 12: Status '||lt_ap_banks_stg(i).status);
--Disbursement Instrument Assignment Setting Payee instruments
IF lt_ap_banks_stg(i).bank_account_id IS NOT NULL
-- AND lt_ap_banks_stg(i).status <> 'E'
THEN

apps.fnd_file.put_line(apps.fnd_file.log,'Track 13');
NULL;
--/*
BEGIN
apps.fnd_file.put_line(apps.fnd_file.log,'==>Invoking SET_PAYEE_INSTR_ASSIGNMENT API<==');
lr_payee_rec.Payment_Function := 'PAYABLES_DISB';
lr_payee_rec.Party_Id := lt_ap_banks_stg(i).vendor_party_id;
lr_payee_rec.Org_Type := 'OPERATING_UNIT';
lr_payee_rec.Org_Id := lt_ap_banks_stg(i).org_id;
lr_payee_rec.Party_Site_id := lt_ap_banks_stg(i).vendor_site_party_id;
lr_payee_rec.Supplier_Site_id := lt_ap_banks_stg(i).vendor_site_id;

lr_assg_attr.Instrument.Instrument_Type:='BANKACCOUNT';
lr_assg_attr.Instrument.Instrument_Id := lt_ap_banks_stg(i).bank_account_id;

--lr_assg_attr.Pmt_Channel_Code := NULL;
--lr_assg_attr.Default_Flag := NULL;
--lr_assg_attr.Inactive_Date := NULL;
lr_assg_attr.Priority := 1;
lr_assg_attr.Start_Date := lt_ap_banks_stg(i).acc_start_date;
lr_assg_attr.End_Date := NULL;--lt_ap_banks_stg(i).acc_start_date;

IBY_DISBURSEMENT_SETUP_PUB.Set_Payee_Instr_Assignment
(
p_api_version => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_TRUE,
x_return_status => lc_return_status,
x_msg_count => ln_msg_count,
x_msg_data => lc_msg_data,
p_payee => lr_payee_rec,
p_assignment_attribs => lr_assg_attr,
x_assign_id => ln_assg_id,
x_response => lr_response
);

apps.fnd_file.put_line(apps.fnd_file.log,'Oracle Assignement ID: '||ln_assg_id);
apps.fnd_file.put_line(apps.fnd_file.log,'Result rec type Code: '|| lr_response.Result_Code);
apps.fnd_file.put_line(apps.fnd_file.log,'Result rec type Category: '||lr_response.Result_Category);
apps.fnd_file.put_line(apps.fnd_file.log,'Result rec type Message: '|| lr_response.Result_Message);
apps.fnd_file.put_line(apps.fnd_file.log,'Return Status: '|| lc_return_status);

apps.fnd_file.put_line(apps.fnd_file.log,'Assignmnet ID : ' || ln_assg_id);

Exception when others then

apps.fnd_file.put_line(apps.fnd_file.log,'Error in SET_PAYEE_INSTR_ASSIGNMENT: '||SQLERRM);
lt_ap_banks_stg(i).status := 'E';
END;
--*/
--Updating default Payment method code to RTGS
/*IF lt_ap_banks_stg(i).status <> 'E' THEN
update_default_pmt_method(p_vendor_id => lt_ap_banks_stg(i).vendor_id
,p_vendor_site_id => lt_ap_banks_stg(i).vendor_site_id
,p_party_site_id => lt_ap_banks_stg(i).vendor_site_party_id
,p_pay_method => 'RTGS'
);
END IF;*/
END IF;
apps.fnd_file.put_line(apps.fnd_file.log,'Track 14: Status: '||lt_ap_banks_stg(i).status);
apps.fnd_file.put_line(apps.fnd_file.log,'************End of Supplier Bank Detials**************');

IF lt_ap_banks_stg(i).status <>'E' THEN
lt_ap_banks_stg(i).status := 'P';
END IF;
END LOOP; --<<end_for_loop>>

--Updating Staging table
BEGIN
FORALL i in 1..lt_ap_banks_stg.COUNT SAVE EXCEPTIONS
UPDATE xxkly_ap_bank_accounts_stg
SET status = lt_ap_banks_stg(i).status
, error_message = LTRIM(lt_ap_banks_stg(i).error_message, ',')
, bank_account_id = lt_ap_banks_stg(i).bank_account_id
, instr_assignment_id = lt_ap_banks_stg(i).instr_assignment_id
, joint_owner_id = lt_ap_banks_stg(i).joint_owner_id
, assignment_id = lt_ap_banks_stg(i).assignment_id
, bank_party_id = lt_ap_banks_stg(i).bank_party_id
, br_bank_party_id = lt_ap_banks_stg(i).br_bank_party_id
, create_bank_flag = lt_ap_banks_stg(i).create_bank_flag
, create_branch_flag = lt_ap_banks_stg(i).create_branch_flag
, create_account_flag = lt_ap_banks_stg(i).create_account_flag
WHERE record_id = lt_ap_banks_stg(i).record_id;
EXCEPTION WHEN OTHERS THEN
apps.fnd_file.put_line(apps.fnd_file.log,'Error while..Updating XXKLY_AP_BANK_ACCOUNTS_STG Table');
ln_bulk_error_cnt := SQL%BULK_EXCEPTIONS.COUNT;
apps.fnd_file.put_line(apps.fnd_file.log,'Number of statements that failed: ' || ln_bulk_error_cnt);
FOR i IN 1..ln_bulk_error_cnt LOOP
apps.fnd_file.put_line(apps.fnd_file.log,'Error #' || i || ' occurred during '|| 'iteration #' || SQL%BULK_EXCEPTIONS(i).ERROR_INDEX);
apps.fnd_file.put_line(apps.fnd_file.log,'Error message is ' || SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
END LOOP;
END;

END IF; --<<end_if_count>>
EXIT WHEN lcu_data%NOTFOUND;
END LOOP; --<<end_of_lcu_data>>
CLOSE lcu_data;

apps.fnd_file.put_line(apps.fnd_file.log, '---------------------End of Create Bank Accounts Procedure---------------');
EXCEPTION WHEN OTHERS THEN
apps.fnd_file.put_line(apps.fnd_file.log, 'Error in Create Bank Accounts Procedure: '||SQLCODE||'-'||SQLERRM);
END create_banks_accounts;

3 comments:

Digihost14 May 2018 at 01:18
Thanks for sharing such a great article about Punchout CXML, it will help me in my work.
Punchout CXML Catlog

ReplyDelete
Replies
Shyam29 October 2018 at 22:26
what is xxkly_ap_bank_accounts_stg table structure?
ReplyDelete
Replies
Anonymous2 January 2024 at 03:23
Very Nice Blog. Thanks for sharing with us.

API Seva is a Providing best Account Number Verification API Service in India. Our Bank account verification API offers an instant verification of active savings and current bank account, given account number and IFSC code.Visit our website for more information
ReplyDelete
Replies

Add comment