详解EBS接口开发之供应商导入（补充）--供应商银行账户更新
2024-10-13 05:58:38
CREATE OR REPLACE PACKAGE BODY update_vendor_account IS PROCEDURE main(errbuf OUT VARCHAR2, retcode OUT VARCHAR2) IS
l_vendor_id NUMBER;
l_acct_id NUMBER;
l_party_id NUMBER;
l_return_status VARCHAR2(30);
l_msg_count NUMBER;
l_msg_data VARCHAR2(30);
l_joint_acct_owner_id NUMBER;
v_count NUMBER;
l_bank_acct_id NUMBER;
--l_result_rec iby_fndcpt_common_pub.result_rec_type;
l_payee_rec iby_disbursement_setup_pub.payeecontext_rec_type;
l_instrument_rec iby_fndcpt_setup_pub.pmtinstrument_rec_type;
l_assignment_attribs_rec iby_fndcpt_setup_pub.pmtinstrassignment_rec_type;
l_result_rec iby_fndcpt_common_pub.result_rec_type; l_ext_bank_acct_rec iby_ext_bankacct_pub.extbankacct_rec_type; l_vendor_site_rec ap_vendor_pub_pkg.r_vendor_site_rec_type;
CURSOR cur_vendor IS
SELECT * FROM cux_oms_vendor h WHERE h.process_status = 'COMPLETE';
BEGIN
FOR rec_vendor IN cur_vendor LOOP
l_party_id := NULL;
BEGIN
SELECT pv.vendor_id
INTO l_vendor_id
FROM po_vendors pv
WHERE pv.segment1 = rec_vendor.rec_vendor_number;
EXCEPTION
WHEN OTHERS THEN RAISE fnd_api.g_exc_unexpected_error;
END; /*BEGIN
SELECT pv.ext_bank_account_id
INTO l_acct_id
FROM iby_ext_bank_accounts pv
WHERE pv.bank_account_num = rec_vendor.bank_account_num;
EXCEPTION
WHEN OTHERS THEN
cux_conc_utl.log_msg('@@@111111111@@@@@@@@@@ 1:' || SQLERRM);
RAISE fnd_api.g_exc_unexpected_error;
END;*/
BEGIN
SELECT pv.party_id
INTO l_party_id
FROM po_vendors pv
WHERE pv.vendor_id = l_vendor_id;
EXCEPTION
WHEN OTHERS THEN RAISE fnd_api.g_exc_unexpected_error;
END; l_ext_bank_acct_rec.country_code := rec_vendors.belong_country;
l_ext_bank_acct_rec.branch_id := rec_vendor.bank_branch_id;
l_ext_bank_acct_rec.bank_id := rec_vendor.bank_id;
l_ext_bank_acct_rec.acct_owner_party_id := l_party_id;
l_ext_bank_acct_rec.bank_account_name := rec_vendor.bank_account_name;
l_ext_bank_acct_rec.bank_account_num := rec_vendor.bank_account_num;
--L_EXT_BANK_ACCT_REC.currency;
l_ext_bank_acct_rec.foreign_payment_use_flag := 'N';
l_ext_bank_acct_rec.object_version_number := 1;
l_ext_bank_acct_rec.start_date := to_date('2013-01-01',
'YYYY-MM-DD'); 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 => l_ext_bank_acct_rec,
p_association_level => 'S', --S:供应商层；SS：供应商地点层；A:Address; AO:Address Operating Unit
p_supplier_site_id => NULL,
p_party_site_id => NULL,
p_org_id => NULL,
p_org_type => NULL, --Bug7136876: new parameter
x_acct_id => l_bank_acct_id,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
x_response => l_result_rec); END LOOP; FOR rec_vendor IN cur_vendor LOOP
l_vendor_id := NULL;
l_acct_id := NULL;
l_party_id := NULL;
v_count := 0;
BEGIN
SELECT pv.vendor_id
INTO l_vendor_id
FROM po_vendors pv
WHERE pv.segment1 = rec_vendor.rec_vendor_number;
EXCEPTION
WHEN OTHERS THEN
RAISE fnd_api.g_exc_unexpected_error;
END; BEGIN
SELECT pv.ext_bank_account_id
INTO l_acct_id
FROM iby_ext_bank_accounts pv
WHERE pv.bank_account_num = rec_vendor.bank_account_num;
EXCEPTION
WHEN OTHERS THEN RAISE fnd_api.g_exc_unexpected_error;
END;
BEGIN
SELECT pv.party_id
INTO l_party_id
FROM po_vendors pv
WHERE pv.vendor_id = l_vendor_id;
EXCEPTION
WHEN OTHERS THEN RAISE fnd_api.g_exc_unexpected_error;
END;
SELECT COUNT(*)
INTO v_count
FROM iby_account_owners
WHERE account_owner_party_id = l_party_id
AND ext_bank_account_id = l_acct_id; IF v_count > 0 THEN
UPDATE cux_oms_vendor h
SET h.process_status = 'ERROR',
h.process_message = 'same party cannot be assigned the same bank account'
WHERE h.header_id = rec_vendor.header_id; ELSE 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 => l_acct_id,
p_acct_owner_party_id => l_party_id,
x_joint_acct_owner_id => l_joint_acct_owner_id,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
x_response => l_result_rec); END IF;
END LOOP;
END;
END update_vendor_account;
最新文章
- 在 Windows7 上按照 MySQL5.7
- [小工具]EquationCalcular
- Javascript中的Prototype到底是啥
- iphone开发之用lipo合并模拟器库和真机库，发布一个通用的静态库
- mongoDB 3.0 安全权限访问控制 -摘自网络
- 银行爱“IOE”爱得有多深
- 使用 jsPlumb 绘制拓扑图 —— 异步载入与绘制的实现
- prototype演变
- C#委托与事件总结
- Java 8 中为什么要引出default方法
- python format()用法
- MySQL复制错误1837的相关缺陷一例
- 【python】mongo删除数据
- c++中关于用stringstream进行的类型转化
- 海量数据中找top K专题
- c# string 扩展方法
- Python3基础 list [] 创建整数列表
- [UE4]移动惯性
- mysql 的S 锁和X锁的区别
- Android中的自定义注解（反射实现-运行时注解）