CREATE OR REPLACE PROCEDURE APPS.xxhi_supplier_bank_api (
errbuf OUT VARCHAR2,
retcode OUT NUMBER
)
IS
x_result_rec_type iby_fndcpt_common_pub.result_rec_type;
v_error_reason VARCHAR2 (2000) := NULL;
v_msg_data VARCHAR2 (1000) := NULL;
v_msg_count NUMBER := NULL;
v_return_status VARCHAR2 (100) := NULL;
v_extbank_rec_type iby_ext_bankacct_pub.extbank_rec_type;
v_extbankbranch_rec_type iby_ext_bankacct_pub.extbankbranch_rec_type;
p_ext_bank_acct_rec iby_ext_bankacct_pub.extbankacct_rec_type;
v_bank_id NUMBER;
x_bank_id NUMBER;
v_branch_id NUMBER;
x_branch_id NUMBER;
v_acct_id NUMBER;
x_acct_id NUMBER;
v_party_id NUMBER;
v_vendor_site_id NUMBER;
v_party_site_id NUMBER;
v_vendor_id NUMBER;
v_bank_count NUMBER;
v_error VARCHAR2 (250);
CURSOR c1
IS
SELECT DISTINCT xxhi.lb_name_of_bank, xxhi.lb_bank_code
FROM xxhi.xxhi_ap_suppliers_int xxhi, iby_ext_banks_v ib
WHERE NVL (bank_account_flag, 'N') = 'N'
AND UPPER (xxhi.lb_name_of_bank) = UPPER (ib.bank_name(+))
AND ib.bank_name IS NULL
AND bank_branch_name IS NOT NULL;
CURSOR c2
IS
SELECT DISTINCT xxhi.bank_branch_name, ib.bank_party_id, ib.bank_name
FROM xxhi.xxhi_ap_suppliers_int xxhi,
iby_ext_banks_v ib,
iby_ext_bank_branches_v ibb
WHERE UPPER (xxhi.bank_branch_name) = UPPER (ibb.bank_branch_name(+))
AND UPPER (xxhi.lb_name_of_bank) = UPPER (ib.bank_name)
AND ibb.bank_branch_name IS NULL
AND NVL (bank_account_flag, 'N') = 'N'
AND xxhi.bank_branch_name IS NOT NULL;
CURSOR c3
IS
SELECT DISTINCT s.party_id, xxhi.lb_account_number, s.vendor_name,
xxhi.fcra_fcra_reg_no, xxhi.lb_account_name,
eb.bank_party_id, ebb.branch_party_id
FROM xxhi.xxhi_ap_suppliers_int xxhi,
ap.ap_suppliers s,
ap.ap_supplier_sites_all ss,
apps.iby_ext_banks_v eb,
apps.iby_ext_bank_branches_v ebb
WHERE UPPER (xxhi.vendor_name) = UPPER (s.vendor_name)
AND UPPER (eb.bank_name) = UPPER (xxhi.lb_name_of_bank)
AND UPPER (xxhi.bank_branch_name) =
UPPER (ebb.bank_branch_name)
AND s.vendor_id = ss.vendor_id
AND eb.bank_party_id = ebb.bank_party_id
AND NVL (bank_account_flag, 'N') = 'N'
AND xxhi.bank_branch_name IS NOT NULL;
BEGIN
FOR i IN c1
LOOP
BEGIN
v_error_reason := NULL;
v_return_status := NULL;
v_msg_count := NULL;
v_msg_data := NULL;
v_extbank_rec_type.object_version_number := 1.0;
v_extbank_rec_type.bank_name := i.lb_name_of_bank;
v_extbank_rec_type.bank_number := i.lb_bank_code;
--v_extbank_rec_type.institution_type := i.institution_type;
v_extbank_rec_type.country_code := 'IN';
--v_extbank_rec_type.description := i.description;
fnd_file.put_line (fnd_file.LOG,
'Bank name....' || v_extbank_rec_type.bank_name
);
fnd_file.put_line (fnd_file.LOG,
'Bank Number....'
|| v_extbank_rec_type.bank_number
);
fnd_file.put_line (fnd_file.LOG,
'Country Code...'
|| v_extbank_rec_type.country_code
);
fnd_file.put_line (fnd_file.LOG, 'BEFORE BANK API');
iby_ext_bankacct_pub.create_ext_bank
(p_api_version => 1.0,
p_init_msg_list => fnd_api.g_true,
p_ext_bank_rec => v_extbank_rec_type,
x_bank_id => x_bank_id,
x_return_status => v_return_status,
x_msg_count => v_msg_count,
x_msg_data => v_msg_data,
x_response => x_result_rec_type
);
fnd_file.put_line (fnd_file.LOG, 'AFTER BANK API');
fnd_file.put_line (fnd_file.LOG,
'Return Status...' || v_return_status
);
fnd_file.put_line (fnd_file.LOG, 'Msg Count...' || v_msg_count);
fnd_file.put_line (fnd_file.LOG, 'Msg Data...' || v_msg_data);
IF v_return_status <> fnd_api.g_ret_sts_success
THEN
IF v_msg_count >= 1
THEN
FOR i IN 1 .. v_msg_count
LOOP
IF v_error_reason IS NULL
THEN
v_error_reason :=
SUBSTR
(fnd_msg_pub.get (p_encoded => fnd_api.g_false),
1,
255
);
ELSE
v_error_reason :=
v_error_reason
|| ' ,'
|| SUBSTR
(fnd_msg_pub.get (p_encoded => fnd_api.g_false),
1,
255
);
END IF;
fnd_file.put_line (fnd_file.LOG,
'BANK API ERROR-' || v_error_reason
);
END LOOP;
END IF;
ROLLBACK;
ELSE
v_bank_id := x_bank_id;
fnd_file.put_line (fnd_file.LOG, 'BANK ID-' || v_bank_id);
COMMIT;
END IF;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG, SQLERRM || '-' || SQLCODE);
END;
END LOOP;
FOR j IN c2
LOOP
BEGIN
v_error_reason := NULL;
v_return_status := NULL;
v_msg_count := NULL;
v_msg_data := NULL;
v_extbankbranch_rec_type.bank_party_id := j.bank_party_id;
v_extbankbranch_rec_type.branch_name := j.bank_branch_name;
v_extbankbranch_rec_type.branch_type := 'OTHER';
--v_extbankbranch_rec_type.attribute14 := 123;
--v_extbankbranch_rec_type.attribute15 := 'DOMESTIC';
fnd_file.put_line (fnd_file.LOG, 'BEFORE BRANCH API');
iby_ext_bankacct_pub.create_ext_bank_branch
(p_api_version => 1.0,
p_init_msg_list => fnd_api.g_true,
p_ext_bank_branch_rec => v_extbankbranch_rec_type,
x_branch_id => x_branch_id,
x_return_status => v_return_status,
x_msg_count => v_msg_count,
x_msg_data => v_msg_data,
x_response => x_result_rec_type
);
fnd_file.put_line (fnd_file.LOG, 'AFTER BRANCH API');
fnd_file.put_line (fnd_file.LOG,
'Return Status...' || v_return_status
);
fnd_file.put_line (fnd_file.LOG, 'Msg Count...' || v_msg_count);
fnd_file.put_line (fnd_file.LOG, 'Msg Data...' || v_msg_data);
IF v_return_status <> fnd_api.g_ret_sts_success
THEN
IF v_msg_count >= 1
THEN
FOR i IN 1 .. v_msg_count
LOOP
IF v_error_reason IS NULL
THEN
v_error_reason :=
SUBSTR
(fnd_msg_pub.get (p_encoded => fnd_api.g_false),
1,
255
);
ELSE
v_error_reason :=
v_error_reason
|| ' ,'
|| SUBSTR
(fnd_msg_pub.get (p_encoded => fnd_api.g_false),
1,
255
);
END IF;
END LOOP;
fnd_file.put_line (fnd_file.LOG,
'BRANCH API ERROR-' || v_error_reason
);
END IF;
ROLLBACK;
ELSE
v_branch_id := x_branch_id;
fnd_file.put_line (fnd_file.LOG, 'BRANCH ID-' || v_branch_id);
COMMIT;
END IF;
END;
END LOOP;
FOR k IN c3
LOOP
BEGIN
v_error_reason := NULL;
v_return_status := NULL;
v_msg_count := NULL;
v_msg_data := NULL;
x_acct_id := NULL;
p_ext_bank_acct_rec.object_version_number := 1.0;
p_ext_bank_acct_rec.acct_owner_party_id := k.party_id;
p_ext_bank_acct_rec.bank_account_num := k.lb_account_number;
p_ext_bank_acct_rec.check_digits := k.fcra_fcra_reg_no;
p_ext_bank_acct_rec.bank_id := k.bank_party_id;
p_ext_bank_acct_rec.branch_id := k.branch_party_id;
p_ext_bank_acct_rec.start_date := SYSDATE;
p_ext_bank_acct_rec.country_code := 'IN';
p_ext_bank_acct_rec.currency := 'INR';
p_ext_bank_acct_rec.foreign_payment_use_flag := 'Y';
p_ext_bank_acct_rec.payment_factor_flag := 'N';
p_ext_bank_acct_rec.bank_account_name := k.lb_account_name;
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 => p_ext_bank_acct_rec,
x_acct_id => x_acct_id,
x_return_status => v_return_status,
x_msg_count => v_msg_count,
x_msg_data => v_msg_data,
x_response => x_result_rec_type
);
IF v_return_status <> fnd_api.g_ret_sts_success
THEN
IF v_msg_count >= 1
THEN
FOR i IN 1 .. v_msg_count
LOOP
IF v_error_reason IS NULL
THEN
v_error_reason :=
SUBSTR
(fnd_msg_pub.get (p_encoded => fnd_api.g_false),
1,
255
);
ELSE
v_error_reason :=
v_error_reason
|| ' ,'
|| SUBSTR
(fnd_msg_pub.get (p_encoded => fnd_api.g_false),
1,
255
);
END IF;
END LOOP;
fnd_file.put_line (fnd_file.LOG,
'Bank Account API ERROR-' || v_error_reason
);
END IF;
ROLLBACK;
ELSE
v_acct_id := x_acct_id;
fnd_file.put_line (fnd_file.LOG,
'Vendor Name...' || k.vendor_name
);
fnd_file.put_line (fnd_file.LOG, 'Account ID-' || x_acct_id);
COMMIT;
UPDATE xxhi.xxhi_ap_suppliers_int
SET bank_account_flag = 'Y'
WHERE UPPER (vendor_name) = UPPER (k.vendor_name);
END IF;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG, 'ERROR INSIDE LOOP-' || SQLERRM);
v_error := SQLERRM;
UPDATE xxhi.xxhi_ap_suppliers_int
SET bank_account_flag = 'N',
ora_error_desc = v_error
WHERE UPPER (vendor_name) = UPPER (k.vendor_name);
END;
END LOOP;
END;
/
Supplier_bank_Query
SELECT s.vendor_name, ss.vendor_site_code, eb.bank_name, ebb.bank_branch_name,
ebb.branch_number, eba.bank_account_num, eba.bank_account_name,
pmt.payment_method_code
FROM hz_parties hz,
ap.ap_suppliers s,
ap.ap_supplier_sites_all ss,
apps.iby_ext_bank_accounts eba,
apps.iby_account_owners ao,
apps.iby_ext_banks_v eb,
apps.iby_ext_bank_branches_v ebb,
apps.iby_ext_party_pmt_mthds pmt
WHERE hz.party_id = s.party_id
AND s.vendor_id = ss.vendor_id
AND ao.account_owner_party_id = s.party_id
AND eba.ext_bank_account_id = ao.ext_bank_account_id
AND eb.bank_party_id = ebb.bank_party_id
AND eba.branch_id = ebb.branch_party_id
AND eba.bank_id = eb.bank_party_id
AND pmt.ext_pmt_party_id(+) = s.vendor_id
errbuf OUT VARCHAR2,
retcode OUT NUMBER
)
IS
x_result_rec_type iby_fndcpt_common_pub.result_rec_type;
v_error_reason VARCHAR2 (2000) := NULL;
v_msg_data VARCHAR2 (1000) := NULL;
v_msg_count NUMBER := NULL;
v_return_status VARCHAR2 (100) := NULL;
v_extbank_rec_type iby_ext_bankacct_pub.extbank_rec_type;
v_extbankbranch_rec_type iby_ext_bankacct_pub.extbankbranch_rec_type;
p_ext_bank_acct_rec iby_ext_bankacct_pub.extbankacct_rec_type;
v_bank_id NUMBER;
x_bank_id NUMBER;
v_branch_id NUMBER;
x_branch_id NUMBER;
v_acct_id NUMBER;
x_acct_id NUMBER;
v_party_id NUMBER;
v_vendor_site_id NUMBER;
v_party_site_id NUMBER;
v_vendor_id NUMBER;
v_bank_count NUMBER;
v_error VARCHAR2 (250);
CURSOR c1
IS
SELECT DISTINCT xxhi.lb_name_of_bank, xxhi.lb_bank_code
FROM xxhi.xxhi_ap_suppliers_int xxhi, iby_ext_banks_v ib
WHERE NVL (bank_account_flag, 'N') = 'N'
AND UPPER (xxhi.lb_name_of_bank) = UPPER (ib.bank_name(+))
AND ib.bank_name IS NULL
AND bank_branch_name IS NOT NULL;
CURSOR c2
IS
SELECT DISTINCT xxhi.bank_branch_name, ib.bank_party_id, ib.bank_name
FROM xxhi.xxhi_ap_suppliers_int xxhi,
iby_ext_banks_v ib,
iby_ext_bank_branches_v ibb
WHERE UPPER (xxhi.bank_branch_name) = UPPER (ibb.bank_branch_name(+))
AND UPPER (xxhi.lb_name_of_bank) = UPPER (ib.bank_name)
AND ibb.bank_branch_name IS NULL
AND NVL (bank_account_flag, 'N') = 'N'
AND xxhi.bank_branch_name IS NOT NULL;
CURSOR c3
IS
SELECT DISTINCT s.party_id, xxhi.lb_account_number, s.vendor_name,
xxhi.fcra_fcra_reg_no, xxhi.lb_account_name,
eb.bank_party_id, ebb.branch_party_id
FROM xxhi.xxhi_ap_suppliers_int xxhi,
ap.ap_suppliers s,
ap.ap_supplier_sites_all ss,
apps.iby_ext_banks_v eb,
apps.iby_ext_bank_branches_v ebb
WHERE UPPER (xxhi.vendor_name) = UPPER (s.vendor_name)
AND UPPER (eb.bank_name) = UPPER (xxhi.lb_name_of_bank)
AND UPPER (xxhi.bank_branch_name) =
UPPER (ebb.bank_branch_name)
AND s.vendor_id = ss.vendor_id
AND eb.bank_party_id = ebb.bank_party_id
AND NVL (bank_account_flag, 'N') = 'N'
AND xxhi.bank_branch_name IS NOT NULL;
BEGIN
FOR i IN c1
LOOP
BEGIN
v_error_reason := NULL;
v_return_status := NULL;
v_msg_count := NULL;
v_msg_data := NULL;
v_extbank_rec_type.object_version_number := 1.0;
v_extbank_rec_type.bank_name := i.lb_name_of_bank;
v_extbank_rec_type.bank_number := i.lb_bank_code;
--v_extbank_rec_type.institution_type := i.institution_type;
v_extbank_rec_type.country_code := 'IN';
--v_extbank_rec_type.description := i.description;
fnd_file.put_line (fnd_file.LOG,
'Bank name....' || v_extbank_rec_type.bank_name
);
fnd_file.put_line (fnd_file.LOG,
'Bank Number....'
|| v_extbank_rec_type.bank_number
);
fnd_file.put_line (fnd_file.LOG,
'Country Code...'
|| v_extbank_rec_type.country_code
);
fnd_file.put_line (fnd_file.LOG, 'BEFORE BANK API');
iby_ext_bankacct_pub.create_ext_bank
(p_api_version => 1.0,
p_init_msg_list => fnd_api.g_true,
p_ext_bank_rec => v_extbank_rec_type,
x_bank_id => x_bank_id,
x_return_status => v_return_status,
x_msg_count => v_msg_count,
x_msg_data => v_msg_data,
x_response => x_result_rec_type
);
fnd_file.put_line (fnd_file.LOG, 'AFTER BANK API');
fnd_file.put_line (fnd_file.LOG,
'Return Status...' || v_return_status
);
fnd_file.put_line (fnd_file.LOG, 'Msg Count...' || v_msg_count);
fnd_file.put_line (fnd_file.LOG, 'Msg Data...' || v_msg_data);
IF v_return_status <> fnd_api.g_ret_sts_success
THEN
IF v_msg_count >= 1
THEN
FOR i IN 1 .. v_msg_count
LOOP
IF v_error_reason IS NULL
THEN
v_error_reason :=
SUBSTR
(fnd_msg_pub.get (p_encoded => fnd_api.g_false),
1,
255
);
ELSE
v_error_reason :=
v_error_reason
|| ' ,'
|| SUBSTR
(fnd_msg_pub.get (p_encoded => fnd_api.g_false),
1,
255
);
END IF;
fnd_file.put_line (fnd_file.LOG,
'BANK API ERROR-' || v_error_reason
);
END LOOP;
END IF;
ROLLBACK;
ELSE
v_bank_id := x_bank_id;
fnd_file.put_line (fnd_file.LOG, 'BANK ID-' || v_bank_id);
COMMIT;
END IF;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG, SQLERRM || '-' || SQLCODE);
END;
END LOOP;
FOR j IN c2
LOOP
BEGIN
v_error_reason := NULL;
v_return_status := NULL;
v_msg_count := NULL;
v_msg_data := NULL;
v_extbankbranch_rec_type.bank_party_id := j.bank_party_id;
v_extbankbranch_rec_type.branch_name := j.bank_branch_name;
v_extbankbranch_rec_type.branch_type := 'OTHER';
--v_extbankbranch_rec_type.attribute14 := 123;
--v_extbankbranch_rec_type.attribute15 := 'DOMESTIC';
fnd_file.put_line (fnd_file.LOG, 'BEFORE BRANCH API');
iby_ext_bankacct_pub.create_ext_bank_branch
(p_api_version => 1.0,
p_init_msg_list => fnd_api.g_true,
p_ext_bank_branch_rec => v_extbankbranch_rec_type,
x_branch_id => x_branch_id,
x_return_status => v_return_status,
x_msg_count => v_msg_count,
x_msg_data => v_msg_data,
x_response => x_result_rec_type
);
fnd_file.put_line (fnd_file.LOG, 'AFTER BRANCH API');
fnd_file.put_line (fnd_file.LOG,
'Return Status...' || v_return_status
);
fnd_file.put_line (fnd_file.LOG, 'Msg Count...' || v_msg_count);
fnd_file.put_line (fnd_file.LOG, 'Msg Data...' || v_msg_data);
IF v_return_status <> fnd_api.g_ret_sts_success
THEN
IF v_msg_count >= 1
THEN
FOR i IN 1 .. v_msg_count
LOOP
IF v_error_reason IS NULL
THEN
v_error_reason :=
SUBSTR
(fnd_msg_pub.get (p_encoded => fnd_api.g_false),
1,
255
);
ELSE
v_error_reason :=
v_error_reason
|| ' ,'
|| SUBSTR
(fnd_msg_pub.get (p_encoded => fnd_api.g_false),
1,
255
);
END IF;
END LOOP;
fnd_file.put_line (fnd_file.LOG,
'BRANCH API ERROR-' || v_error_reason
);
END IF;
ROLLBACK;
ELSE
v_branch_id := x_branch_id;
fnd_file.put_line (fnd_file.LOG, 'BRANCH ID-' || v_branch_id);
COMMIT;
END IF;
END;
END LOOP;
FOR k IN c3
LOOP
BEGIN
v_error_reason := NULL;
v_return_status := NULL;
v_msg_count := NULL;
v_msg_data := NULL;
x_acct_id := NULL;
p_ext_bank_acct_rec.object_version_number := 1.0;
p_ext_bank_acct_rec.acct_owner_party_id := k.party_id;
p_ext_bank_acct_rec.bank_account_num := k.lb_account_number;
p_ext_bank_acct_rec.check_digits := k.fcra_fcra_reg_no;
p_ext_bank_acct_rec.bank_id := k.bank_party_id;
p_ext_bank_acct_rec.branch_id := k.branch_party_id;
p_ext_bank_acct_rec.start_date := SYSDATE;
p_ext_bank_acct_rec.country_code := 'IN';
p_ext_bank_acct_rec.currency := 'INR';
p_ext_bank_acct_rec.foreign_payment_use_flag := 'Y';
p_ext_bank_acct_rec.payment_factor_flag := 'N';
p_ext_bank_acct_rec.bank_account_name := k.lb_account_name;
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 => p_ext_bank_acct_rec,
x_acct_id => x_acct_id,
x_return_status => v_return_status,
x_msg_count => v_msg_count,
x_msg_data => v_msg_data,
x_response => x_result_rec_type
);
IF v_return_status <> fnd_api.g_ret_sts_success
THEN
IF v_msg_count >= 1
THEN
FOR i IN 1 .. v_msg_count
LOOP
IF v_error_reason IS NULL
THEN
v_error_reason :=
SUBSTR
(fnd_msg_pub.get (p_encoded => fnd_api.g_false),
1,
255
);
ELSE
v_error_reason :=
v_error_reason
|| ' ,'
|| SUBSTR
(fnd_msg_pub.get (p_encoded => fnd_api.g_false),
1,
255
);
END IF;
END LOOP;
fnd_file.put_line (fnd_file.LOG,
'Bank Account API ERROR-' || v_error_reason
);
END IF;
ROLLBACK;
ELSE
v_acct_id := x_acct_id;
fnd_file.put_line (fnd_file.LOG,
'Vendor Name...' || k.vendor_name
);
fnd_file.put_line (fnd_file.LOG, 'Account ID-' || x_acct_id);
COMMIT;
UPDATE xxhi.xxhi_ap_suppliers_int
SET bank_account_flag = 'Y'
WHERE UPPER (vendor_name) = UPPER (k.vendor_name);
END IF;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG, 'ERROR INSIDE LOOP-' || SQLERRM);
v_error := SQLERRM;
UPDATE xxhi.xxhi_ap_suppliers_int
SET bank_account_flag = 'N',
ora_error_desc = v_error
WHERE UPPER (vendor_name) = UPPER (k.vendor_name);
END;
END LOOP;
END;
/
Supplier_bank_Query
SELECT s.vendor_name, ss.vendor_site_code, eb.bank_name, ebb.bank_branch_name,
ebb.branch_number, eba.bank_account_num, eba.bank_account_name,
pmt.payment_method_code
FROM hz_parties hz,
ap.ap_suppliers s,
ap.ap_supplier_sites_all ss,
apps.iby_ext_bank_accounts eba,
apps.iby_account_owners ao,
apps.iby_ext_banks_v eb,
apps.iby_ext_bank_branches_v ebb,
apps.iby_ext_party_pmt_mthds pmt
WHERE hz.party_id = s.party_id
AND s.vendor_id = ss.vendor_id
AND ao.account_owner_party_id = s.party_id
AND eba.ext_bank_account_id = ao.ext_bank_account_id
AND eb.bank_party_id = ebb.bank_party_id
AND eba.branch_id = ebb.branch_party_id
AND eba.bank_id = eb.bank_party_id
AND pmt.ext_pmt_party_id(+) = s.vendor_id
No comments:
Post a Comment