原文地址 使用API失效供应商地址Demo
DECLARE lv_return_status VARCHAR2(1) := NULL; ln_msg_count NUMBER; lv_errmsg VARCHAR2(5000); lt_vendor_site_rec ap_vendor_pub_pkg.r_vendor_site_rec_type; l_party_site_rec hz_party_site_v2pub.party_site_rec_type; lt_location_rec hz_location_v2pub.location_rec_type; ln_object_version_number NUMBER; ln_object_version_number2 NUMBER; ln_vendor_site_id NUMBER := NULL; ln_party_site_id NUMBER := NULL; lv_party_site_name VARCHAR2(240); lv_party_site_number VARCHAR2(30); ln_location_id NUMBER; -- WHO columns l_user_id NUMBER := -1; l_resp_id NUMBER := -1; l_application_id NUMBER := -1; l_row_cnt NUMBER := 1; l_user_name VARCHAR2(30) := &user_name; l_resp_name VARCHAR2(30) := &resp_name; lv_vendor_site_code VARCHAR2(15) := &vendor_site_code; ln_org_id NUMBER := &org_id; /*CURSOR sup_site IS SELECT vendor_site_code_de FROM xxwe_mg_supplier_sites_xross WHERE batch_id = 234 AND segment1_so NOT LIKE 'NON%';*/ BEGIN -- Get the user_id SELECT user_id INTO l_user_id FROM fnd_user WHERE upper(user_name) = upper(l_user_name); -- Get the application_id and responsibility_id SELECT application_id, responsibility_id INTO l_application_id, l_resp_id FROM fnd_responsibility_vl WHERE upper(responsibility_name) = upper(l_resp_name); -- Initialize applications information fnd_global.apps_initialize(l_user_id, l_resp_id, l_application_id); -- Mfg / Mfg & Dist Mgr / INV dbms_output.put_line('Initialized applications context: ' || l_user_id || ' ' || l_resp_id || ' ' || l_application_id); -- dbms_output.put_line('------------------Start Update-------------------'); --FOR rec IN sup_site LOOP --dbms_output.put_line('Supplier site code:' || rec.vendor_site_code_de); --dbms_output.put_line('Supplier org id:' || ln_org_id); --SELECT NECESSARY INFORMATION BEGIN SELECT pvs.vendor_site_id, pvs.party_site_id, hps.party_site_name, hps.object_version_number, hps.party_site_number, pvs.location_id, hl.object_version_number INTO ln_vendor_site_id, ln_party_site_id, lv_party_site_name, ln_object_version_number, lv_party_site_number, ln_location_id, ln_object_version_number2 FROM ap_supplier_sites_all pvs, hz_party_sites hps, hz_locations hl WHERE pvs.vendor_site_code = lv_vendor_site_code--rec.vendor_site_code_de AND pvs.org_id = ln_org_id AND pvs.party_site_id = hps.party_site_id AND pvs.location_id = hl.location_id; EXCEPTION WHEN no_data_found THEN lv_errmsg := 'Can not find the vendor site need to update!The site code is:' || lv_vendor_site_code/*rec.vendor_site_code_de*/; dbms_output.put_line(lv_errmsg); END; -- lt_vendor_site_rec.vendor_site_id := ln_vendor_site_id; lt_vendor_site_rec.vendor_site_code := substr(lv_vendor_site_code, length(lv_vendor_site_code) - 7, 8) || 'disable'; lt_vendor_site_rec.inactive_date := SYSDATE; --UPDATE vendor_site_code ap_vendor_pub_pkg.update_vendor_site(p_api_version => 1.0, p_init_msg_list => fnd_api.g_true, p_commit => fnd_api.g_false, p_validation_level => fnd_api.g_valid_level_full, x_return_status => lv_return_status, x_msg_count => ln_msg_count, x_msg_data => lv_errmsg, p_vendor_site_rec => lt_vendor_site_rec, p_vendor_site_id => ln_vendor_site_id); IF (nvl(lv_return_status, fnd_api.g_ret_sts_error) <> fnd_api.g_ret_sts_success) THEN ln_msg_count := fnd_msg_pub.count_msg; IF ln_msg_count > 0 THEN lv_errmsg := substr(fnd_msg_pub.get(fnd_msg_pub.g_first, fnd_api.g_false), 1, 512); FOR i IN 1 .. (ln_msg_count - 1) LOOP lv_errmsg := lv_errmsg || chr(10) || substr(fnd_msg_pub.get(fnd_msg_pub.g_next, fnd_api.g_false), 1, 512); END LOOP; fnd_msg_pub.delete_msg(); END IF; dbms_output.put_line('------------------Update Error-------------------'); dbms_output.put_line('Error1 :' || lv_errmsg); END IF; --UPDATE party_site_name l_party_site_rec.party_site_id := ln_party_site_id; l_party_site_rec.party_site_name := lv_party_site_name || 'disable'; l_party_site_rec.status := 'I'; -- hz_party_site_v2pub.update_party_site(p_init_msg_list => fnd_api.g_false, p_party_site_rec => l_party_site_rec, p_object_version_number => ln_object_version_number, x_return_status => lv_return_status, x_msg_count => ln_msg_count, x_msg_data => lv_errmsg); IF (nvl(lv_return_status, fnd_api.g_ret_sts_error) <> fnd_api.g_ret_sts_success) THEN ln_msg_count := fnd_msg_pub.count_msg; IF ln_msg_count > 0 THEN lv_errmsg := substr(fnd_msg_pub.get(fnd_msg_pub.g_first, fnd_api.g_false), 1, 512); FOR i IN 1 .. (ln_msg_count - 1) LOOP lv_errmsg := lv_errmsg || chr(10) || substr(fnd_msg_pub.get(fnd_msg_pub.g_next, fnd_api.g_false), 1, 512); END LOOP; fnd_msg_pub.delete_msg(); END IF; dbms_output.put_line('------------------Update Error-------------------'); dbms_output.put_line('Error2 :' || lv_errmsg); END IF; --update location lt_location_rec.address_style := 'POSTAL_ADDR_DEF'; lt_location_rec.location_id := ln_location_id; hz_location_v2pub.update_location(p_init_msg_list => fnd_api.g_true, p_location_rec => lt_location_rec, p_object_version_number => ln_object_version_number2, x_return_status => lv_return_status, x_msg_count => ln_msg_count, x_msg_data => lv_errmsg); IF (nvl(lv_return_status, fnd_api.g_ret_sts_error) <> fnd_api.g_ret_sts_success) THEN ln_msg_count := fnd_msg_pub.count_msg; IF ln_msg_count > 0 THEN lv_errmsg := substr(fnd_msg_pub.get(fnd_msg_pub.g_first, fnd_api.g_false), 1, 512); FOR i IN 1 .. (ln_msg_count - 1) LOOP lv_errmsg := lv_errmsg || chr(10) || substr(fnd_msg_pub.get(fnd_msg_pub.g_next, fnd_api.g_false), 1, 512); END LOOP; fnd_msg_pub.delete_msg(); END IF; dbms_output.put_line('------------------Update Error-------------------'); dbms_output.put_line('Error3 :' || lv_errmsg); END IF; -- IF lv_return_status = fnd_api.g_ret_sts_success THEN dbms_output.put_line('------------------Update Success-------------------'); DELETE xxwe_mg_supplier_sites_xross x WHERE x.party_site_number_de = lv_party_site_number AND x.org_id_de = ln_org_id; END IF; --END LOOP; dbms_output.put_line('------------------Update End-------------------'); END;
使用API失效客户地址Demo
--Disable customer site DECLARE lv_party_site_number VARCHAR2(30) := &p_party_site_number; lr_party_site_rec_type hz_party_site_v2pub.party_site_rec_type; lr_cust_acct_site_rec hz_cust_account_site_v2pub.cust_acct_site_rec_type; lr_cust_site_use_rec_type hz_cust_account_site_v2pub.cust_site_use_rec_type; lr_orig_sys_reference_rec hz_orig_system_ref_pub.orig_sys_reference_rec_type; ln_version_num NUMBER; ln_version_num_party_site NUMBER; x_return_status VARCHAR2(10); x_msg_count NUMBER; x_msg_data VARCHAR2(1000); ln_failed_count NUMBER := 0; api_exception EXCEPTION; lv_errmsg VARCHAR2(2000); BEGIN dbms_output.put_line('Customer site number: ' || lv_party_site_number); --begin to disable customer site use SELECT hcas.cust_acct_site_id, hcas.org_id INTO lr_cust_acct_site_rec.cust_acct_site_id, lr_cust_acct_site_rec.org_id FROM hz_cust_accounts hca, hz_cust_acct_sites_all hcas, hz_party_sites hps WHERE hca.cust_account_id = hcas.cust_account_id AND hcas.party_site_id = hps.party_site_id AND hps.party_site_number = lv_party_site_number; mo_global.init('AR'); mo_global.set_policy_context('S', lr_cust_acct_site_rec.org_id); dbms_output.put_line('Org id : ' || lr_cust_acct_site_rec.org_id); dbms_output.put_line(' '); dbms_output.put_line('==========================Disable site use==========================================='); FOR cur_site_use IN (SELECT hcsu.site_use_id, hcsu.location, hcsu.site_use_code, hcsu.object_version_number FROM hz_cust_site_uses_all hcsu WHERE hcsu.cust_acct_site_id = lr_cust_acct_site_rec.cust_acct_site_id ORDER BY hcsu.site_use_id DESC) LOOP lr_cust_site_use_rec_type.cust_acct_site_id := lr_cust_acct_site_rec.cust_acct_site_id; lr_cust_site_use_rec_type.site_use_id := cur_site_use.site_use_id; lr_cust_site_use_rec_type.location := substr(cur_site_use.location, 1, 38) || '_d'; --lr_cust_site_use_rec_type.location := cur_site_use.location; lr_cust_site_use_rec_type.site_use_code := cur_site_use.site_use_code; lr_cust_site_use_rec_type.status := 'I'; hz_cust_account_site_v2pub.update_cust_site_use(p_init_msg_list => fnd_api.g_true, p_cust_site_use_rec => lr_cust_site_use_rec_type, p_object_version_number => cur_site_use.object_version_number, x_return_status => x_return_status, x_msg_count => x_msg_count, x_msg_data => x_msg_data); dbms_output.put_line('Site use id: ' || lr_cust_site_use_rec_type.site_use_id); dbms_output.put_line('Location: ' || lr_cust_site_use_rec_type.location); dbms_output.put_line('site_use_code: ' || lr_cust_site_use_rec_type.site_use_code); IF x_return_status = 'S' THEN dbms_output.put_line('Disable customer site use successfully !'); ELSE ln_failed_count := ln_failed_count + 1; dbms_output.put_line('Failed to disable customer site use !'); dbms_output.put_line('x_msg_count : ' || x_msg_count); dbms_output.put_line('Error message : ' || x_msg_data); END IF; dbms_output.put_line('--------------------------'); IF ln_failed_count > 0 THEN RAISE api_exception; END IF; END LOOP; --begin to disable customer site dbms_output.put_line(' '); dbms_output.put_line('==========================Disable customer site======================================='); SELECT hcas.cust_acct_site_id, hcas.org_id, hcas.object_version_number INTO lr_cust_acct_site_rec.cust_acct_site_id, lr_cust_acct_site_rec.org_id, ln_version_num FROM hz_cust_accounts hca, hz_cust_acct_sites_all hcas, hz_party_sites hps WHERE hca.cust_account_id = hcas.cust_account_id AND hcas.party_site_id = hps.party_site_id AND hps.party_site_number = lv_party_site_number; lr_cust_acct_site_rec.status := 'I'; --call api to disable customer site hz_cust_account_site_v2pub.update_cust_acct_site(p_init_msg_list => fnd_api.g_true, p_cust_acct_site_rec => lr_cust_acct_site_rec, p_object_version_number => ln_version_num, x_return_status => x_return_status, x_msg_count => x_msg_count, x_msg_data => x_msg_data); IF x_return_status = 'S' THEN --delete customer xross table DELETE FROM xxwe_mg_customer_xross xmcx WHERE party_site_number_de = lv_party_site_number AND org_id = lr_cust_acct_site_rec.org_id; dbms_output.put_line('Disable customer site successfully !'); ELSE ln_failed_count := ln_failed_count + 1; dbms_output.put_line('Failed to disable customer site !'); dbms_output.put_line('Error message : ' || x_msg_data); RAISE api_exception; END IF; dbms_output.put_line(' '); dbms_output.put_line('==========================Disable party site========================================='); --begin to disable party site SELECT hps.party_site_id, hps.party_site_name || '_d', hps.object_version_number INTO lr_party_site_rec_type.party_site_id, lr_party_site_rec_type.party_site_name, ln_version_num_party_site FROM hz_cust_accounts hca, hz_cust_acct_sites_all hcas, hz_party_sites hps WHERE hca.cust_account_id = hcas.cust_account_id AND hcas.party_site_id = hps.party_site_id AND hps.party_site_number = lv_party_site_number; lr_party_site_rec_type.status := 'I'; hz_party_site_v2pub.update_party_site(p_init_msg_list => fnd_api.g_true, p_party_site_rec => lr_party_site_rec_type, p_object_version_number => ln_version_num_party_site, x_return_status => x_return_status, x_msg_count => x_msg_count, x_msg_data => x_msg_data); IF x_return_status = 'S' THEN dbms_output.put_line('Disable party site successfully !'); ELSE dbms_output.put_line('Failed to disable party site !'); dbms_output.put_line('Error message : ' || x_msg_data); RAISE api_exception; END IF; EXCEPTION WHEN no_data_found THEN dbms_output.put_line('No data found!'); ROLLBACK; WHEN api_exception THEN dbms_output.put_line('Error occured when execute oracle api!'); ROLLBACK; WHEN OTHERS THEN dbms_output.put_line('Unexpected exception occured!'); ROLLBACK; END;