Oracle API to insert supplier bank account information

Oracle API to insert supplier bank account information

API used: 

IBY_EXT_BANKACCT_PUB.create_ext_bank_acct

iby_disbursement_setup_pub.create_external_payee

iby_disbursement_setup_pub.set_payee_instr_assignment

IBY_EXT_BANKACCT_PUB.check_bank_acct_owner

IBY_EXT_BANKACCT_PUB.add_joint_account_owner

 

1 2 3 4 5 6 7 8 9 10 11 12

DECLARE x_bank_rec iby_ext_bankacct_pub.extbank_rec_type; x_bank_id NUMBER; x_return_status VARCHAR2 (10); x_msg_count NUMBER; x_msg_data VARCHAR2 (256); x_response_rec iby_fndcpt_common_pub.result_rec_type; x_bank_branch_rec iby_ext_bankacct_pub.extbankbranch_rec_type; x_branch_id NUMBER; x_acct_id NUMBER; x_bank_acct_rec iby_ext_bankacct_pub.extbankacct_rec_type; p_ext_payee_tab iby_disbursement_setup_pub.external_payee_tab_type; x_ext_payee_id_tab iby_disbursement_setup_pub.ext_payee_id_tab_type; x_ext_payee_status_tab iby_disbursement_setup_pub.ext_payee_create_tab_type; l_ext_payee_rec iby_disbursement_setup_pub.external_payee_rec_type; l_ext_payee_id_rec_type iby_disbursement_setup_pub.ext_payee_id_rec_type; p_payee iby_disbursement_setup_pub.payeecontext_rec_type; p_assignment_attribs iby_fndcpt_setup_pub.pmtinstrassignment_rec_type; p_instrument iby_fndcpt_setup_pub.pmtinstrument_rec_type; x_assign_id NUMBER; v_bank_party_id NUMBER; v_branch_party_id NUMBER; v_vendor_id NUMBER; v_vendor_site_id NUMBER; v_party_site_id NUMBER; V_EXT_BANK_ACCOUNT_ID NUMBER; v_bank_creation_flag VARCHAR2 (40); v_error_flag VARCHAR2 (40); v_error_message VARCHAR2 (4000); v_party_id number; v_TERRITORY_CODE VARCHAR2(400); x_joint_owner_id number; CURSOR supplier_bank IS SELECT ROWID, xrs.* FROM XXAP_SUPP_BANK_ACCOUNT_STG xrs WHERE processed_flag = 'N'; BEGIN DBMS_OUTPUT.put_line ('Start of procedure.'); fnd_global.apps_initialize (fnd_global.user_id, fnd_global.resp_id, fnd_global.resp_appl_id); DBMS_OUTPUT.put_line ('Procedure initializad.'); FOR i IN supplier_bank LOOP v_bank_creation_flag := 'N'; v_error_flag := 'N'; v_error_message := NULL; v_bank_party_id:=NULL; V_branch_party_id:=NULL; v_ext_bank_account_id:=NULL; v_vendor_id:=NULL; v_party_id:=NULL; v_vendor_site_id:=NULL; v_party_site_id:=NULL; v_TERRITORY_CODE:=NULL; begin select TERRITORY_CODE into v_TERRITORY_CODE from fnd_territories where UPPER(TERRITORY_CODE)=UPPER(i.COUNTRY); exception when others then V_ERROR_FLAG:='Y'; V_ERROR_MESSAGE:=V_ERROR_MESSAGE||'Bank country not exsist'; end; 　 BEGIN SELECT bank_party_id INTO v_bank_party_id FROM ce_banks_v WHERE UPPER(bank_name)= UPPER(i.bank_name) and upper(HOME_COUNTRY)=UPPER(I.COUNTRY) AND ROWNUM=1; EXCEPTION WHEN NO_DATA_FOUND THEN v_error_flag := 'Y'; v_error_message := 'BANK IS NOT EXIST IN THE SYSTEM'; END; BEGIN SELECT branch_party_id INTO v_branch_party_id FROM ce_bank_branches_v where UPPER(BANK_BRANCH_NAME)=UPPER(I.BRANCH_NAME) AND UPPER(bank_name)=UPPER(I.BANK_NAME) AND UPPER(BANK_HOME_COUNTRY)=UPPER(I.COUNTRY) AND ROWNUM=1; EXCEPTION WHEN OTHERS THEN v_error_flag := 'Y'; v_error_message := v_error_message || 'BRANCH IS NOT EXIST IN THE SYSTEM'; END; BEGIN SELECT ext_bank_account_id INTO v_ext_bank_account_id FROM iby_ext_bank_accounts WHERE bank_id = v_bank_party_id AND branch_id = v_branch_party_id AND bank_account_num = i.bank_account_number AND UPPER(bank_account_name) = UPPER(i.bank_account_name); EXCEPTION WHEN no_data_found THEN v_ext_bank_account_id:=null; when others then v_error_flag := 'Y'; v_error_message := v_error_message || 'ERROR IN THE SYSTEM BANK ACCOUNT NUMBER FETCH'; END; BEGIN SELECT vendor_id INTO v_vendor_id FROM po_vendors WHERE upper(vendor_name) = upper(i.vendor_name) and segment1=I.ATTRIBUTE1; EXCEPTION WHEN OTHERS THEN v_error_flag := 'Y'; v_error_message := v_error_message || 'VENDOR NOT EXIST IN THE SYSTEM'; END; BEGIN SELECT party_id INTO v_party_id FROM po_vendors WHERE upper(vendor_name) =upper(i.vendor_name) and segment1=I.ATTRIBUTE1; EXCEPTION WHEN OTHERS THEN v_error_flag := 'Y'; v_error_message := v_error_message || 'VENDOR NOT EXIST IN THE SYSTEM'; END; IF i.vendor_site_code IS NOT NULL THEN BEGIN SELECT vendor_site_id,party_site_id INTO v_vendor_site_id,v_party_site_id FROM ap_supplier_sites_all WHERE UPPER(vendor_site_code) = UPPER(i.vendor_site_code) AND ORG_ID=p_operating_unit AND vendor_id = v_vendor_id; EXCEPTION WHEN OTHERS THEN v_error_flag := 'Y'; v_error_message :=v_error_message || 'VENDOR SITE NOT EXIST IN THE SYSTEM'; END; END IF; 　 IF v_error_flag = 'N' AND v_ext_bank_account_id IS NULL THEN x_bank_acct_rec.country_code := I.COUNTRY ; x_bank_acct_rec.branch_id := v_branch_party_id; x_bank_acct_rec.bank_id := v_bank_party_id; x_bank_acct_rec.acct_owner_party_id := v_party_id; --supplier party id x_bank_acct_rec.iban := i.iban; x_bank_acct_rec.currency := i.currency; x_bank_acct_rec.bank_account_name := i.bank_account_name; x_bank_acct_rec.bank_account_num := i.bank_account_number; x_msg_count := 0; x_msg_data := NULL; x_return_status := NULL; iby_ext_bankacct_pub.create_ext_bank_acct (p_api_version => 1.0, p_init_msg_list => fnd_api.g_true, p_ext_bank_acct_rec => x_bank_acct_rec, x_acct_id => x_acct_id, x_return_status => x_return_status, x_msg_count => x_msg_count, x_msg_data => x_msg_data, x_response => x_response_rec ); IF x_return_status = fnd_api.g_ret_sts_success THEN DBMS_OUTPUT.put_line ('External bank account created.'); DBMS_OUTPUT.put_line ('x_acct_id' || x_acct_id); DBMS_OUTPUT.put_line ('x_return_status' || x_return_status); DBMS_OUTPUT.put_line ('x_msg_count.' || x_msg_count); DBMS_OUTPUT.put_line ('x_msg_data.' || x_msg_data); ELSE DBMS_OUTPUT.put_line ( 'Creation of BANK ACCCOUNT failed:' || x_msg_data ); ROLLBACK; v_error_flag := 'Y'; v_error_message := v_error_message || 'Creation of BANK ACCCOUNT failed:' || x_msg_data; 　 　 　 END IF; l_ext_payee_rec.payee_party_site_id := v_party_site_id; l_ext_payee_rec.payee_party_id := v_party_id; l_ext_payee_rec.payment_function := 'PAYABLES_DISB'; IF i.vendor_site_code IS NOT NULL THEN l_ext_payee_rec.payer_org_id := p_operating_unit; l_ext_payee_rec.payer_org_type := 'OPERATING_UNIT'; ELSE l_ext_payee_rec.payer_org_id := NULL; l_ext_payee_rec.payer_org_type := NULL; END IF; l_ext_payee_rec.exclusive_pay_flag := 'N'; l_ext_payee_rec.default_pmt_method := NVL ('CHECK', i.payment_method); l_ext_payee_rec.supplier_site_id := v_vendor_site_id; x_msg_count := 0; x_msg_data := NULL; x_return_status := NULL; p_ext_payee_tab (0) := l_ext_payee_rec; iby_disbursement_setup_pub.create_external_payee (p_api_version => 1.0, p_init_msg_list => fnd_api.g_true, p_ext_payee_tab => p_ext_payee_tab, x_return_status => x_return_status, x_msg_count => x_msg_count, x_msg_data => x_msg_data, x_ext_payee_id_tab => x_ext_payee_id_tab, x_ext_payee_status_tab => x_ext_payee_status_tab ); IF x_return_status = fnd_api.g_ret_sts_success THEN DBMS_OUTPUT.put_line ('External Payee created.'); DBMS_OUTPUT.put_line ('x_return_status: ' || x_return_status ); DBMS_OUTPUT.put_line ('x_msg_count. ' || x_msg_count); DBMS_OUTPUT.put_line ('x_msg_data. ' || x_msg_data); ELSE DBMS_OUTPUT.put_line ( 'Creation of payee failed:' || x_msg_data ); v_error_message := v_error_message || 'Creation of payee failed:' || x_msg_data; ROLLBACK; v_error_flag := 'Y'; 　 　 　 END IF; p_payee.supplier_site_id := v_vendor_site_id; p_payee.party_id :=v_party_id; p_payee.party_site_id := v_party_site_id; p_payee.payment_function := 'PAYABLES_DISB'; IF i.vendor_site_code IS NOT NULL THEN p_payee.org_id := p_operating_unit; p_payee.org_type := 'OPERATING_UNIT'; ELSE p_payee.org_id := NULL; p_payee.org_type := NULL; END IF; l_ext_payee_id_rec_type := x_ext_payee_id_tab (0); p_instrument.instrument_id := x_acct_id; p_instrument.instrument_type := 'BANKACCOUNT'; p_assignment_attribs.priority := 1; p_assignment_attribs.instrument := p_instrument; x_msg_count := 0; x_msg_data := NULL; x_return_status := NULL; x_response_rec := NULL; iby_disbursement_setup_pub.set_payee_instr_assignment (p_api_version => 1.0, p_init_msg_list => fnd_api.g_true, p_commit => fnd_api.g_true, x_return_status => x_return_status, x_msg_count => x_msg_count, x_msg_data => x_msg_data, p_payee => p_payee, p_assignment_attribs => p_assignment_attribs, x_assign_id => x_assign_id, x_response => x_response_rec ); IF x_return_status = fnd_api.g_ret_sts_success THEN DBMS_OUTPUT.put_line ('Payee_Instr_Assignment.'); DBMS_OUTPUT.put_line ('x_assign_id' || x_assign_id); DBMS_OUTPUT.put_line ('x_return_status' || x_return_status); DBMS_OUTPUT.put_line ('x_msg_count.' || x_msg_count); DBMS_OUTPUT.put_line ('x_msg_data.' || x_msg_data); 　 ELSE DBMS_OUTPUT.put_line ( 'Creation of Payee_Instr_Assignment failed:' || x_msg_data ); v_error_message:= v_error_message||'Creation of Payee_Instr_Assignment failed:' || x_msg_data; ROLLBACK; v_error_flag := 'Y'; 　 　 　 END IF; END IF; IF v_error_flag = 'N' AND v_ext_bank_account_id IS NOT NULL THEN DBMS_OUTPUT.put_line ('CHECKING THAT VENDOR IS THE OWNER OF THIS ACCOUNT'||x_return_status); BEGIN x_msg_count := 0; x_msg_data := NULL; x_return_status := NULL; x_response_rec := NULL; IBY_EXT_BANKACCT_PUB.check_bank_acct_owner (p_api_version => 1.0, p_init_msg_list => FND_API.G_FALSE, p_bank_acct_id => v_ext_bank_account_id, p_acct_owner_party_id =>v_party_id, x_return_status => x_return_status, x_msg_count => x_msg_count, x_msg_data => x_msg_data, x_response => x_response_rec ); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.put_line ('ERROR IN CHECKING VENDOR IS THE OWNER OF THIS ACCOUNT'||x_return_status); END; IF x_return_status = fnd_api.g_ret_sts_success THEN DBMS_OUTPUT.put_line ('already owner'); p_payee.supplier_site_id := v_vendor_site_id; p_payee.party_id :=v_party_id; p_payee.party_site_id := v_party_site_id; p_payee.payment_function := 'PAYABLES_DISB'; IF i.vendor_site_code IS NOT NULL THEN p_payee.org_id := p_operating_unit; p_payee.org_type := 'OPERATING_UNIT'; ELSE p_payee.org_id := NULL; p_payee.org_type := NULL; END IF; p_assignment_attribs.Instrument.instrument_id := v_ext_bank_account_id; p_assignment_attribs.Instrument.instrument_type := 'BANKACCOUNT'; p_assignment_attribs.priority := 1; -- p_assignment_attribs.instrument := p_instrument; x_msg_count := 0; x_msg_data := NULL; x_return_status := NULL; x_response_rec := NULL; iby_disbursement_setup_pub.set_payee_instr_assignment (p_api_version => 1.0, p_init_msg_list => fnd_api.g_true, p_commit => fnd_api.g_true, x_return_status => x_return_status, x_msg_count => x_msg_count, x_msg_data => x_msg_data, p_payee => p_payee, p_assignment_attribs => p_assignment_attribs, x_assign_id => x_assign_id, x_response => x_response_rec ); IF x_return_status = fnd_api.g_ret_sts_success THEN DBMS_OUTPUT.put_line ('Payee_Instr_Assignment.'); DBMS_OUTPUT.put_line ('x_assign_id' || x_assign_id); DBMS_OUTPUT.put_line ('x_return_status' || x_return_status); else DBMS_OUTPUT.put_line ( 'Creation of Payee_Instr_Assignment failed:' || x_msg_data ); v_error_message:= v_error_message||'Creation of Payee_Instr_Assignment failed:' || x_msg_data; ROLLBACK; v_error_flag := 'Y'; end if; ELSE DBMS_OUTPUT.put_line ( 'Creation of joint bank acct owner' ); x_msg_count := 0; x_msg_data := NULL; x_return_status := NULL; x_response_rec := NULL; IBY_EXT_BANKACCT_PUB.add_joint_account_owner ( p_api_version => 1.0, p_init_msg_list => fnd_api.g_true, p_bank_account_id => v_ext_bank_account_id, p_acct_owner_party_id => v_party_id, x_joint_acct_owner_id =>x_joint_owner_id, x_return_status => x_return_status, x_msg_count => x_msg_count, x_msg_data =>x_msg_data, x_response=> x_response_rec); IF x_return_status = fnd_api.g_ret_sts_success THEN DBMS_OUTPUT.put_line ('joint acct created'); p_payee.supplier_site_id := v_vendor_site_id; p_payee.party_id :=v_party_id; p_payee.party_site_id := v_party_site_id; p_payee.payment_function := 'PAYABLES_DISB'; IF i.vendor_site_code IS NOT NULL THEN p_payee.org_id := p_operating_unit; p_payee.org_type := 'OPERATING_UNIT'; ELSE p_payee.org_id := NULL; p_payee.org_type := NULL; END IF; p_assignment_attribs.Instrument.instrument_id := v_ext_bank_account_id; p_assignment_attribs.Instrument.instrument_type := 'BANKACCOUNT'; p_assignment_attribs.priority := 1; x_msg_count := 0; x_msg_data := NULL; x_return_status := NULL; x_response_rec := NULL; iby_disbursement_setup_pub.set_payee_instr_assignment (p_api_version => 1.0, p_init_msg_list => fnd_api.g_true, p_commit => fnd_api.g_true, x_return_status => x_return_status, x_msg_count => x_msg_count, x_msg_data => x_msg_data, p_payee => p_payee, p_assignment_attribs => p_assignment_attribs, x_assign_id => x_assign_id, x_response => x_response_rec ); IF x_return_status = fnd_api.g_ret_sts_success THEN DBMS_OUTPUT.put_line ('Payee_Instr_Assignment.'); DBMS_OUTPUT.put_line ('x_assign_id' || x_assign_id); DBMS_OUTPUT.put_line ('x_return_status' || x_return_status); else DBMS_OUTPUT.put_line ( 'Creation of Payee_Instr_Assignment failed:' || x_msg_data ); v_error_message:= v_error_message||'Creation of Payee_Instr_Assignment failed:' || x_msg_data; ROLLBACK; v_error_flag := 'Y'; end if; else DBMS_OUTPUT.put_line ( 'Creation of joint account owner failed' || x_msg_data ); v_error_message:= v_error_message||'Creation of joint account owner failed' || x_msg_data; ROLLBACK; v_error_flag := 'Y'; end if; end if; end if; 　 IF v_error_flag = 'Y' THEN ROLLBACK; UPDATE XXAP_SUPP_BANK_ACCOUNT_STG SET processed_flag = 'N', error_description = v_error_message WHERE ROWID = i.ROWID; COMMIT; ELSE UPDATE XXAP_SUPP_BANK_ACCOUNT_STG SET processed_flag = 'Y' WHERE ROWID = i.ROWID; COMMIT; END IF; END LOOP; END;