CREATE OR REPLACE PACKAGE ebiz_ven_bank_assn_pkg
IS
PROCEDURE ebiz_vendor_prc (retcode OUT VARCHAR, errbuf OUT VARCHAR);
END ebiz_ven_bank_assn_pkg;
/
CREATE OR REPLACE PACKAGE BODY ebiz_ven_bank_assn_pkg
IS
PROCEDURE ebiz_vendor_prc (retcode OUT VARCHAR, errbuf OUT VARCHAR)
IS
p_api_version NUMBER;
p_init_msg_list VARCHAR2 (200);
p_commit VARCHAR2 (200);
x_return_status VARCHAR2 (200);
x_msg_count NUMBER;
x_msg_data VARCHAR2 (200);
p_payee apps.iby_disbursement_setup_pub.payeecontext_rec_type;
p_assignment_attribs apps.iby_fndcpt_setup_pub.pmtinstrassignment_rec_type;
lr_ext_bank_acct_dtl iby_ext_bank_accounts%ROWTYPE;
x_assign_id NUMBER;
x_response apps.iby_fndcpt_common_pub.result_rec_type;
lv_vendor_site_code VARCHAR2 (100);
lv_vendor_name VARCHAR2 (100);
lv_bank_acct_name VARCHAR2 (100);
lv_supp_site_id VARCHAR2 (100);
lv_supp_party_site_id VARCHAR2 (100);
lv_acct_owner_party_id VARCHAR2 (100);
lv_org_id VARCHAR2 (100);
l_msg VARCHAR2 (200);
CURSOR xxstg
IS
SELECT ebiz_payee_name_code, bank_account_num, bank_sort_code
FROM ebiz_cas_payee_secure;
CURSOR xxvendor_sites (l_vendor_id NUMBER, lv_org_id NUMBER)
IS
SELECT vendor_site_code
FROM ap_supplier_sites_all
WHERE vendor_id = l_vendor_id AND org_id = lv_org_id;
ln_vendor_id NUMBER;
ln_vendor_site_id NUMBER;
ln_vendor_site_code VARCHAR2 (100);
BEGIN
FOR xxstg_cur IN xxstg
LOOP
BEGIN
SELECT vendor_id
INTO ln_vendor_id
FROM ap_suppliers
WHERE vendor_name = xxstg_cur.ebiz_payee_name_code;
EXCEPTION
WHEN OTHERS
THEN
FND_FILE.put_line (FND_FILE.LOG,
'error in retreving in vendorid');
DBMS_OUTPUT.put_line ('error');
END;
--Vendor sites retriving
FOR xxvendor_sites_cur IN xxvendor_sites (ln_vendor_id, lv_org_id)
LOOP
BEGIN
lv_org_id := 204;
SELECT vendor_site_code
INTO ln_vendor_site_code
FROM ap_supplier_sites_all
WHERE vendor_id = ln_vendor_id AND org_id = lv_org_id;
END;
BEGIN
SELECT *
INTO lr_ext_bank_acct_dtl
FROM iby_ext_bank_accounts
WHERE bank_account_name = xxstg_cur.bank_account_num;
EXCEPTION
WHEN OTHERS
THEN
FND_FILE.put_line (
FND_FILE.LOG,
'Unable to derive the external bank details:' || SQLERRM);
DBMS_OUTPUT.put_line (
'Unable to derive the external bank details:' || SQLERRM);
END;
-- get supplier details
BEGIN
SELECT assa.vendor_site_id,
assa.party_site_id,
aps.party_id,
assa.org_id
INTO lv_supp_site_id,
lv_supp_party_site_id,
lv_acct_owner_party_id,
lv_org_id
FROM ap_suppliers aps, ap_supplier_sites_all assa
WHERE aps.vendor_id = assa.vendor_id
AND aps.vendor_name = xxstg_cur.ebiz_payee_name_code
AND assa.vendor_site_code = lv_vendor_site_code;
EXCEPTION
WHEN OTHERS
THEN
FND_FILE.put_line (
FND_FILE.LOG,
'Error- Get supp_site_id and supp_party_site_id'
|| SQLCODE
|| SQLERRM);
DBMS_OUTPUT.put_line (
'Error- Get supp_site_id and supp_party_site_id'
|| SQLCODE
|| SQLERRM);
END;
-- Assign payee values
p_payee.supplier_site_id := lv_supp_site_id;
p_payee.party_id := lv_acct_owner_party_id;
p_payee.party_site_id := lv_supp_party_site_id;
p_payee.payment_function := 'PAYABLES_DISB';
p_payee.org_id := lv_org_id;
p_payee.org_type := 'OPERATING_UNIT';
-- Assignment Values
p_assignment_attribs.instrument.instrument_type := 'BANKACCOUNT';
p_assignment_attribs.instrument.instrument_id :=
lr_ext_bank_acct_dtl.ext_bank_account_id;
-- External Bank Account ID
p_assignment_attribs.priority := 1;
p_assignment_attribs.start_date := SYSDATE;
iby_disbursement_setup_pub.set_payee_instr_assignment (
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
p_commit => p_commit,
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);
FND_FILE.put_line (FND_FILE.LOG,
'X_RETURN_STATUS = ' || x_return_status);
FND_FILE.put_line (FND_FILE.LOG, 'X_MSG_COUNT = ' || x_msg_count);
FND_FILE.put_line (FND_FILE.LOG, 'X_MSG_DATA = ' || x_msg_data);
FND_FILE.put_line (FND_FILE.LOG, 'X_ASSIGN_ID = ' || x_assign_id);
FND_FILE.put_line (
FND_FILE.LOG,
'X_RESPONSE.Result_Category = ' || x_response.result_category);
FND_FILE.put_line (
FND_FILE.LOG,
'X_RESPONSE.Result_Message = ' || x_response.result_message);
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);
FND_FILE.put_line (
FND_FILE.LOG,
'The API call failed with error ' || l_msg);
END LOOP;
ELSE
FND_FILE.put_line (FND_FILE.LOG,
'The API call ended with SUCESSS status');
END IF;
END LOOP;
END LOOP;
END ebiz_vendor_prc;
END ebiz_ven_bank_assn_pkg;
/
SHOW ERR
