更新供应商地点层的付款方法API
DECLARE --API 参数 l_external_payee_rec_type iby_disbursement_setup_pub.external_payee_rec_type; l_ext_payee_id_rec_type iby_disbursement_setup_pub.ext_payee_id_rec_type; l_ext_payee_tab iby_disbursement_setup_pub.external_payee_tab_type; l_ext_payee_id_tab iby_disbursement_setup_pub.ext_payee_id_tab_type; l_ext_payee_status_tab iby_disbursement_setup_pub.ext_payee_update_tab_type; --变量 l_api_version CONSTANT NUMBER := 1; l_msg_data VARCHAR2(2000); l_msg VARCHAR2(4000); l_msg_count NUMBER; l_return_status VARCHAR2(1); l_method_code VARCHAR2(50); l_vendor_id NUMBER; l_party_id NUMBER; l_party_site_id NUMBER; l_location_id NUMBER; l_vendor_site_id NUMBER; l_object_version_number NUMBER; l_loc_version_number NUMBER; g_def_exclusive_pay_flag VARCHAR2(1) := ‘Y‘; --l_party_id NUMBER; --游标 CURSOR cur_cux_vendor IS SELECT l.vendor_site_code, l.attribute2, l.line_id FROM cux_oms_vendor_site l WHERE l.attribute2 IS NULL FOR UPDATE NOWAIT; CURSOR cur_ap_vendor(p_vendor_site_code IN VARCHAR2) IS SELECT l.vendor_id, l.vendor_site_id, l.org_id, l.party_site_id FROM ap_supplier_sites_all l WHERE l.vendor_site_code = p_vendor_site_code; CURSOR c_payment_method(p_vendor_id IN VARCHAR2, p_vendor_site_id IN NUMBER) IS SELECT a.ext_payee_id, a.payment_function, a.supplier_site_id, a.party_site_id, a.org_id, a.org_type, a.object_version_number, pv.party_id, s.attribute2 FROM ap_supplier_sites_all s, iby_external_payees_all a, po_vendors pv WHERE (s.inactive_date IS NULL OR trunc(s.inactive_date) >= trunc(SYSDATE)) AND a.supplier_site_id(+) = s.vendor_site_id AND s.vendor_id = pv.vendor_id AND a.payee_party_id = pv.party_id AND s.org_id = a.org_id(+) AND s.party_site_id = a.party_site_id(+) AND s.vendor_site_id = p_vendor_site_id AND pv.vendor_id = p_vendor_id; BEGIN fnd_global.apps_initialize(user_id => 1110, resp_id => 20639, resp_appl_id => 200); FOR rec_cux_vendor IN cur_cux_vendor LOOP FOR rec_ap_vendor IN cur_ap_vendor(p_vendor_site_code => rec_cux_vendor.vendor_site_code) LOOP --初始化参数 mo_global.set_policy_context(p_access_mode => ‘S‘, p_org_id => rec_ap_vendor.org_id); FOR r_payment_method IN c_payment_method(p_vendor_id => rec_ap_vendor.vendor_id, p_vendor_site_id => rec_ap_vendor.vendor_site_id) LOOP l_ext_payee_id_rec_type := NULL; l_ext_payee_id_tab.delete; l_external_payee_rec_type := NULL; l_ext_payee_tab.delete; SELECT l.party_id INTO l_party_id FROM ap_suppliers l WHERE l.vendor_id = rec_ap_vendor.vendor_id; --update site level ext pay infor l_ext_payee_id_rec_type := NULL; l_ext_payee_id_tab.delete; l_external_payee_rec_type := NULL; l_ext_payee_tab.delete; --init param p_ext_payee_id_tab l_ext_payee_id_rec_type.ext_payee_id := r_payment_method.ext_payee_id; l_ext_payee_id_tab(1) := l_ext_payee_id_rec_type; --init param p_ext_payee_tab l_external_payee_rec_type.payee_party_site_id := r_payment_method.party_site_id; l_external_payee_rec_type.payee_party_id := r_payment_method.party_id; --p_party_id; l_external_payee_rec_type.payment_function := r_payment_method.payment_function; l_external_payee_rec_type.payer_org_id := r_payment_method.org_id; l_external_payee_rec_type.supplier_site_id := r_payment_method.supplier_site_id; l_external_payee_rec_type.payer_org_type := r_payment_method.org_type; l_external_payee_rec_type.exclusive_pay_flag := g_def_exclusive_pay_flag; /*SELECT pmv.payment_method_code INTO l_method_code FROM iby_payment_methods_vl pmv WHERE pmv.payment_method_name = ‘银行电汇‘ AND SYSDATE < nvl(pmv.inactive_date, SYSDATE + 1);*/ l_external_payee_rec_type.default_pmt_method := ‘BZ_CHECK‘; l_ext_payee_tab(1) := l_external_payee_rec_type; iby_disbursement_setup_pub.update_external_payee(p_api_version => l_api_version, p_init_msg_list => ‘T‘, p_ext_payee_tab => l_ext_payee_tab, p_ext_payee_id_tab => l_ext_payee_id_tab, x_return_status => l_return_status, x_msg_count => l_msg_count, x_msg_data => l_msg_data, x_ext_payee_status_tab => l_ext_payee_status_tab); IF l_return_status != fnd_api.g_ret_sts_success THEN IF l_msg_data IS NULL THEN l_msg_data := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_last, p_encoded => ‘F‘); --get last message dbms_output.put_line(‘l_msg_data:‘ || SQLERRM); END IF; --IF l_msg_data IS NULL THEN END IF; --IF l_return_status != fnd_api.g_ret_sts_success THEN END LOOP; --FOR r_payment_method IN c_payment_method(p_vendor_id =>rec_ap_vendor.vendor_id, END LOOP; --for rec_ap_vendor in cur_ap_vendor(p_vendor_site_code =>rec_cux_vendor.vendor_site_code) loop UPDATE cux_oms_vendor_site L SET L.ATTRIBUTE2 = ‘BZ_CHECK‘ WHERE L.LINE_ID = rec_cux_vendor.LINE_ID; END LOOP; --for rec_cux_vendor in cur_cux_vendor loop END;