--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;
| 
转播
