原文地址 使用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;