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;
