Ap supplier bank is completing with 2 steps, you can follow below steps
1- iby_ext_bankacct_pub.create_ext_bank_acct
2-iby_disbursement_setup_pub.set_payee_instr_assignment
1-
DECLARE
p_api_version NUMBER;
p_init_msg_list VARCHAR2(200);
p_ext_bank_acct_rec apps.iby_ext_bankacct_pub.extbankacct_rec_type;
x_acct_id NUMBER;
x_return_status VARCHAR2(200);
x_msg_count NUMBER;
x_msg_data VARCHAR2(200);
x_response apps.iby_fndcpt_common_pub.result_rec_type;
lv_bank_name VARCHAR2(100);
lv_bank_branch VARCHAR2(100);
lv_bank_acct_num VARCHAR2(100);
lv_bank_acct_name VARCHAR2(100);
lv_vendor_name VARCHAR2(100);
ln_bank_id NUMBER;
ln_branch_id NUMBER;
lv_bank_home_country_code VARCHAR2(100);
ln_acct_owner_party_id NUMBER;
l_msg VARCHAR2(200);
l_count NUMBER;
CURSOR l_data IS
select vendor_name,
vendor_id vendor_id,
vendor_type_lookup_code,
ext_bank_account_id,
xiba.BANK_PARTY_ID,
xiba.COUNTRY_CODE,
xiba.BANK_NAME,
xiba.BANK_NUMBER,
xiba.BRANCH_PARTY_ID,
xiba.BANK_BRANCH_NAME,
xiba.BRANCH_NUMBER,
xiba.BRANCH_TYPE,
xiba.BANK_BRANCH_ADDRESS_ID,
xiba.BANK_ACCOUNT_ID,
xiba.BANK_ACCOUNT_NAME,
xiba.BANK_ACCOUNT_NUMBER,
xiba.CURRENCY_CODE,
xiba.DESCRIPTION,
ALTERNATE_ACCOUNT_NAME,
SHORT_ACCT_NAME,
-- xiba.PRIMARY_ACCT_OWNER_PARTY_ID,
xiba.PRIMARY_ACCT_OWNER_NAME,
(SELECT party_id FROM ap_suppliers WHERE vendor_name = xiba.vendor_name) PRIMARY_ACCT_OWNER_id,
xiba.ACCOUNT_CLASSIFICATION,
iban_number,
xiba.START_DATE,
xiba.END_DATE,
BANK_CODE, --account_type
vat_registration_num, --vergi_no
sube_swift,
BANKA_SWIFT,
vendor_creation,
site_creation ,
new_ext_bank_account_id,
xiba.status ,
ORDER_OF_PREFERENCE
from XX.XX_IBY_BANK_ACCOUNTS xiba
where 1=1 and status is null ;
BEGIN
-- Initialize apps session
fnd_global.apps_initialize (user_id => 1110,
resp_id => 50779,
resp_appl_id => 200);
mo_global.init ('SQLAP');
-- fnd_client_info.set_org_context (101);
-- Nullify temp variables
ln_bank_id := NULL;
ln_branch_id := NULL;
lv_bank_home_country_code := NULL;
-- ln_acct_owner_party_id := NULL;
-- Get bank details
FOR n_data IN l_data LOOP
BEGIN
SELECT count(1)
INTO l_count
FROM ce_bank_branches_v
WHERE bank_number = n_data.bank_number
AND BRANCH_NUMBER = n_data.BRANCH_NUMBER;
DBMS_OUTPUT.PUT_LINE('1- count= '|| l_count );
IF l_count > 0 THEN
SELECT bank_home_country, bank_party_id, pk_id
INTO lv_bank_home_country_code, ln_bank_id, ln_branch_id
FROM ce_bank_branches_v
WHERE bank_number = n_data.bank_number
AND BRANCH_NUMBER = n_data.BRANCH_NUMBER
AND ROWNUM=1;
DBMS_OUTPUT.PUT_LINE('2- bank_id= '|| ln_bank_id || ' branch_id= ' || ln_branch_id );
ELSE
SELECT bank_home_country, bank_party_id, pk_id
INTO lv_bank_home_country_code, ln_bank_id, ln_branch_id
FROM ce_bank_branches_v
WHERE bank_number = n_data.bank_number
AND BRANCH_NUMBER = '0';
DBMS_OUTPUT.PUT_LINE('3- bank_id= '|| ln_bank_id || ' branch_id= ' || ln_branch_id );
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
BEGIN
SELECT bank_home_country, bank_party_id, pk_id
INTO lv_bank_home_country_code, ln_bank_id, ln_branch_id
FROM ce_bank_branches_v
WHERE BANK_NAME = n_data.bank_name
AND BANK_BRANCH_NAME = n_data.bank_branch_name;
DBMS_OUTPUT.PUT_LINE (
'4- bank_id= '
|| ln_bank_id
|| ' branch_id= '
|| ln_branch_id);
EXCEPTION
WHEN NO_DATA_FOUND
THEN
DBMS_OUTPUT.put_line (
'Unable to derive Bank and Branch details-----'
|| SQLERRM
|| ' ----->'
|| n_data.bank_name
|| '-'
|| n_data.BANK_BRANCH_NAME);
END;
END;
-- Assign API parameters
p_api_version := 1.0;
p_init_msg_list := fnd_api.g_true;
p_ext_bank_acct_rec.country_code := n_data.country_code;
p_ext_bank_acct_rec.bank_id := ln_bank_id; -- bank_id
p_ext_bank_acct_rec.branch_id := ln_branch_id; -- branch_id
p_ext_bank_acct_rec.acct_owner_party_id := n_data.PRIMARY_ACCT_OWNER_id;
-- p_ext_bank_acct_rec.bank_account_id := n_data.bank_account_id;
p_ext_bank_acct_rec.iban := n_data.IBAN_NUMBER;
p_ext_bank_acct_rec.bank_account_name := n_data.bank_account_name;
p_ext_bank_acct_rec.bank_account_num := n_data.BANK_ACCOUNT_NUMBER;
p_ext_bank_acct_rec.currency := n_data.currency_code; -- bank_acct_currency
p_ext_bank_acct_rec.start_date := n_data.start_date;
p_ext_bank_acct_rec.foreign_payment_use_flag := 'Y';
p_ext_bank_acct_rec.object_version_number :=1;
iby_ext_bankacct_pub.create_ext_bank_acct(p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
p_ext_bank_acct_rec => p_ext_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);
IF x_return_status ='S' THEN
update XX.XX_IBY_BANK_ACCOUNTS set new_ext_bank_account_id = x_acct_id ,
status ='UPDATE' where EXT_BANK_ACCOUNT_ID= n_data.EXT_BANK_ACCOUNT_ID;
COMMIT;
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);
DBMS_OUTPUT.put_line('X_RESPONSE.Result_Code = ' ||
x_response.result_code);
DBMS_OUTPUT.put_line('X_RESPONSE.Result_Category = ' ||
x_response.result_category);
DBMS_OUTPUT.put_line('X_RESPONSE.Result_Message = ' ||
x_response.result_message);
END IF;
IF (x_return_status <> fnd_api.g_ret_sts_success) THEN
FOR i IN 1 .. fnd_msg_pub.count_msg LOOP
l_msg := fnd_msg_pub.get(p_msg_index => i,
p_encoded => fnd_api.g_false);
DBMS_OUTPUT.put_line('The API call failed with error ' || l_msg || 'Vendor Name: ' || n_data.vendor_name || ' Account No: ' || n_data.BANK_ACCOUNT_NUMBER);
END LOOP;
ELSE
DBMS_OUTPUT.put_line('The API call ended with SUCESSS status');
END IF;
END LOOP;
END;
2-
/* Formatted on 12/7/2020 2:20:29 PM (QP5 v5.277) */
DECLARE
-- IBY_DISBURSEMENT_SETUP_PUB.Set_Payee_Instr_Assignment
p_instrument iby_fndcpt_setup_pub.pmtinstrument_rec_type;
p_assignment_attribs iby_fndcpt_setup_pub.pmtinstrassignment_rec_type;
x_response iby_fndcpt_common_pub.result_rec_type;
x_assign_id NUMBER;
x_return_status VARCHAR2 (2000);
x_msg_count NUMBER (5);
x_msg_data VARCHAR2 (100);
p_payee iby_disbursement_setup_pub.payeecontext_rec_type;
l_msg VARCHAR2 (200);
l_vendor_site_id NUMBER;
l_org_id NUMBER;
CURSOR l_data
IS
SELECT ieb.ext_bank_account_id,
iep.payee_party_id,
ieb.start_date,
EXT_PAYEE_ID,
xiba.ORDER_OF_PREFERENCE
FROM iby_external_payees_all iep,
IBY_EXT_BANK_ACCOUNTS_V ieb,
XX.XX_IBY_BANK_ACCOUNTS xiba
WHERE iep.payee_party_id = ieb.PRIMARY_ACCT_OWNER_PARTY_ID
AND xiba.NEW_EXT_BANK_ACCOUNT_ID=ieb.EXT_BANK_ACCOUNT_ID
AND ieb.end_date IS NULL
AND iep.inactive_date IS NULL
AND iep.ORG_TYPE is null
and payee_party_id!=38466;
BEGIN
FOR n_data IN l_data
LOOP
p_payee.party_id := n_data.payee_party_id; --acct_owner_party_id or payee_party_id
p_payee.payment_function := 'PAYABLES_DISB';
p_payee.org_type := 'OPERATING_UNIT';
p_instrument.instrument_id := n_data.ext_bank_account_id; -- ext_bank_account_id
p_instrument.instrument_type := 'BANKACCOUNT';
p_assignment_attribs.priority := n_data.ORDER_OF_PREFERENCE;
p_assignment_attribs.start_date := n_data.start_date;
p_assignment_attribs.instrument := p_instrument;
iby_disbursement_setup_pub.set_payee_instr_assignment (
1.0,
fnd_api.g_true,
fnd_api.g_false, -- p_commit
x_return_status,
x_msg_count,
x_msg_data,
p_payee,
p_assignment_attribs,
x_assign_id,
x_response);
IF (x_return_status <> fnd_api.g_ret_sts_success)
THEN
FOR i IN 1 .. fnd_msg_pub.count_msg
LOOP
l_msg :=
fnd_msg_pub.get (p_msg_index => i, p_encoded => fnd_api.g_false);
DBMS_OUTPUT.put_line ('The API call failed with error ' || l_msg);
END LOOP;
ELSE
DBMS_OUTPUT.put_line ('The API call ended with SUCESSS status');
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);
DBMS_OUTPUT.put_line ('X_RESPONSE.Result_Code = ' || x_response.result_code);
DBMS_OUTPUT.put_line ('X_RESPONSE.Result_Category = ' || x_response.result_category);
DBMS_OUTPUT.put_line ('X_RESPONSE.Result_Message = ' || x_response.result_message);
END IF;
END LOOP;
END;
No comments:
Post a Comment