--更新供应商地点 PROCEDURE update_vendor_site(p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_false, x_return_status OUT NOCOPY VARCHAR2, x_msg_count OUT NOCOPY NUMBER, x_msg_data OUT NOCOPY VARCHAR2, p_cust_vendor_id IN NUMBER) IS l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_VENDOR_SITE'; l_savepoint_name CONSTANT VARCHAR2(30) := 'UPDATE_VENDOR_SITE'; l_return_status VARCHAR2(1); l_msg_count NUMBER; l_msg_data VARCHAR2(4000); v_return_mes VARCHAR2(4000); l_message VARCHAR2(4000); lv_vendor_site_id NUMBER := 0; lv_vendor_site_rec_c ap_vendor_pub_pkg.r_vendor_site_rec_type; lv_vendor_site_rec ap_vendor_pub_pkg.r_vendor_site_rec_type; l_vendor_site_id NUMBER; l_party_site_id NUMBER; l_location_id NUMBER; l_success_count NUMBER := 0; l_error_count NUMBER := 0; l_party_id NUMBER; BEGIN x_return_status := hss_api.start_activity(p_pkg_name => g_pkg_name, p_api_name => l_api_name, p_init_msg_list => p_init_msg_list); IF (x_return_status = fnd_api.g_ret_sts_unexp_error) THEN RAISE fnd_api.g_exc_unexpected_error; ELSIF (x_return_status = fnd_api.g_ret_sts_error) THEN RAISE fnd_api.g_exc_error; END IF; FOR rec_pt IN (SELECT cva.party_id, cva.party_name, cva.attribute15, cia.org_id FROM cux_dis_cust_vendor_all cva, cux_dis_org_info_all cia WHERE cva.cust_vendor_id = p_cust_vendor_id AND cva.party_id = cia.party_id AND cva.attribute15 = cia.attribute15 AND cia.new_discription_flag = 'Y') LOOP FOR i IN (SELECT * FROM (SELECT pvs.* FROM po_vendors pv, po_vendor_sites_all pvs WHERE pvs.vendor_id = pv.vendor_id AND pv.vendor_name = rec_pt.party_name ORDER BY pvs.last_update_date DESC) WHERE rownum = 1) LOOP --log('org_id' || l_line.org_id); --log('vendor_site_code' || l_line.vendor_site_code); --log('vendor_id' || l_line.vendor_id); lv_vendor_site_rec := lv_vendor_site_rec_c; fnd_global.apps_initialize(user_id => fnd_global.user_id, resp_id => fnd_global.resp_id, resp_appl_id => fnd_global.resp_appl_id); mo_global.set_policy_context('S', rec_pt.org_id); --======================================================================== lv_vendor_site_id := i.vendor_site_id; -- 供应商地点ID lv_vendor_site_rec.vendor_site_id := i.vendor_site_id; lv_vendor_site_rec.vendor_id := i.vendor_id; -- 供应商ID lv_vendor_site_rec.vendor_site_code := i.vendor_site_code; lv_vendor_site_rec.vendor_site_code_alt := i.vendor_site_code_alt; lv_vendor_site_rec.inactive_date := trunc(SYSDATE); -- 失效日期 lv_vendor_site_rec.org_id := rec_pt.org_id; lv_vendor_site_rec.location_id := i.location_id; lv_vendor_site_rec.party_site_id := i.party_site_id; lv_vendor_site_rec.address_style := i.address_style; lv_vendor_site_rec.address_line1 := i.address_line1; lv_vendor_site_rec.address_line2 := i.address_line2; lv_vendor_site_rec.address_line3 := i.address_line3; lv_vendor_site_rec.address_line4 := i.address_line4; lv_vendor_site_rec.address_lines_alt := i.address_lines_alt; lv_vendor_site_rec.province := i.province; lv_vendor_site_rec.country := i.country; lv_vendor_site_rec.county := i.county; lv_vendor_site_rec.city := i.city; lv_vendor_site_rec.state := i.state; lv_vendor_site_rec.area_code := i.area_code; lv_vendor_site_rec.ship_to_location_id := i.ship_to_location_id; lv_vendor_site_rec.bill_to_location_id := i.bill_to_location_id; lv_vendor_site_rec.terms_date_basis := i.terms_date_basis; lv_vendor_site_rec.accts_pay_code_combination_id := i.accts_pay_code_combination_id; lv_vendor_site_rec.prepay_code_combination_id := i.prepay_code_combination_id; lv_vendor_site_rec.payment_priority := i.payment_priority; lv_vendor_site_rec.terms_id := i.terms_id; lv_vendor_site_rec.pay_date_basis_lookup_code := i.pay_date_basis_lookup_code; lv_vendor_site_rec.fax := i.fax; lv_vendor_site_rec.phone := i.phone; lv_vendor_site_rec.pcard_site_flag := i.pcard_site_flag; lv_vendor_site_rec.match_option := i.match_option; lv_vendor_site_rec.country_of_origin_code := i.country_of_origin_code; lv_vendor_site_rec.future_dated_payment_ccid := i.future_dated_payment_ccid; lv_vendor_site_rec.email_address := i.email_address; lv_vendor_site_rec.primary_pay_site_flag := i.primary_pay_site_flag; lv_vendor_site_rec.ship_via_lookup_code := i.ship_via_lookup_code; lv_vendor_site_rec.freight_terms_lookup_code := i.freight_terms_lookup_code; lv_vendor_site_rec.fob_lookup_code := i.fob_lookup_code; lv_vendor_site_rec.pay_group_lookup_code := i.pay_group_lookup_code; lv_vendor_site_rec.invoice_currency_code := i.invoice_currency_code; lv_vendor_site_rec.payment_currency_code := i.payment_currency_code; lv_vendor_site_rec.vat_code := i.vat_code; lv_vendor_site_rec.auto_tax_calc_flag := i.auto_tax_calc_flag; lv_vendor_site_rec.pay_site_flag := 'Y'; --i.pay_site_flag; lv_vendor_site_rec.purchasing_site_flag := 'Y'; --i.purchasing_site_flag; pos_vendor_pub_pkg.create_vendor_site(x_return_status => l_return_status, x_msg_count => l_msg_count, x_msg_data => l_msg_data, p_vendor_site_rec => lv_vendor_site_rec, x_vendor_site_id => l_vendor_site_id, x_party_site_id => l_party_site_id, x_location_id => l_location_id); IF (x_return_status <> fnd_api.g_ret_sts_success) THEN l_msg_data := fnd_message.get || l_msg_data; v_return_mes := cux_dis_cust_vdr_ipt_pkg.get_return_message(l_msg_count, l_msg_data); l_message := l_message || '.' || '新增地点：' || v_return_mes; l_msg_data := l_message || '创建供应商地点失败!'; -- cux_conc_utl.log_msg('创建供应商地点失败!' || '-' || l_vendor_site_id || '-' || l_party_site_id || '-' || l_location_id || '-' || l_msg_data); log('创建供应商地点失败!' || '-' || l_vendor_site_id || '-' || l_party_site_id || '-' || l_location_id || '-' || l_msg_data); RAISE fnd_api.g_exc_error; ELSE --导入后将party_id回写到客制化表中，并将状态更新为'已导入' UPDATE cux_dis_cust_vendor_all cva SET cva.status_code = 'ENABLED', cva.attribute15 = 'Y' WHERE cva.cust_vendor_id = p_cust_vendor_id; log('party_id:=' || '-' || rec_pt.party_id || '-' || 'org_id:=-' || '-' || rec_pt.org_id); UPDATE cux_dis_org_info_all cia SET cia.discription_flag = 'Y', cia.new_discription_flag = NULL, cia.attribute15 = 'Y' WHERE cia.party_id = rec_pt.party_id AND cia.org_id = rec_pt.org_id AND cia.new_discription_flag = 'Y'; END IF; END LOOP; END LOOP; --============================================================================ l_return_status := hss_api.end_activity(p_pkg_name => g_pkg_name, p_api_name => l_api_name, x_msg_count => x_msg_count, x_msg_data => x_msg_data); EXCEPTION WHEN fnd_api.g_exc_error THEN ROLLBACK; UPDATE cux_dis_cust_vendor_all cva SET cva.status_code = 'IMPORT_FAILED' WHERE cva.cust_vendor_id = p_cust_vendor_id; x_return_status := hss_api.handle_exceptions(p_pkg_name => g_pkg_name, p_api_name => l_api_name, p_savepoint_name => l_savepoint_name, p_exc_name => hss_api.g_exc_name_error, x_msg_count => x_msg_count, x_msg_data => x_msg_data); x_msg_data := x_msg_data || l_msg_data; WHEN fnd_api.g_exc_unexpected_error THEN ROLLBACK; UPDATE cux_dis_cust_vendor_all cva SET cva.status_code = 'IMPORT_FAILED' WHERE cva.cust_vendor_id = p_cust_vendor_id; x_return_status := hss_api.handle_exceptions(p_pkg_name => g_pkg_name, p_api_name => l_api_name, p_savepoint_name => l_savepoint_name, p_exc_name => hss_api.g_exc_name_unexp, x_msg_count => x_msg_count, x_msg_data => x_msg_data); x_msg_data := x_msg_data || l_msg_data; WHEN OTHERS THEN ROLLBACK; UPDATE cux_dis_cust_vendor_all cva SET cva.status_code = 'IMPORT_FAILED' WHERE cva.cust_vendor_id = p_cust_vendor_id; x_return_status := hss_api.handle_exceptions(p_pkg_name => g_pkg_name, p_api_name => l_api_name, p_savepoint_name => l_savepoint_name, p_exc_name => hss_api.g_exc_name_others, x_msg_count => x_msg_count, x_msg_data => x_msg_data); x_msg_data := x_msg_data || l_msg_data; END update_vendor_site;
2088
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
