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,
hps.STATUS
FROM ap_supplier_sites_all pvs,
hz_party_sites hps,
hz_locations hl
WHERE pvs.vendor_site_code
= ‘采购‘--
AND pvs.org_id
= 82 --
AND pvs.party_site_id
= hps.party_site_id
AND pvs.location_id
= hl.location_id
and rownum
=1;
--供应商地址失效
DECLARE
l_return_status VARCHAR2(
1) :
= NULL;
l_msg_count NUMBER;
l_errmsg VARCHAR2(
5000);
l_vendor_site_rec ap_vendor_pub_pkg.r_vendor_site_rec_type;
l_party_site_rec hz_party_site_v2pub.party_site_rec_type;
l_location_rec hz_location_v2pub.location_rec_type;
l_object_version_number NUMBER;
l_object_version_number2 NUMBER;
l_vendor_site_id NUMBER :
= NULL;
l_party_site_id NUMBER :
= NULL;
l_party_site_name VARCHAR2(
240);
l_party_site_number VARCHAR2(
30);
l_location_id NUMBER;
BEGIN
fnd_global.apps_initialize(0,
50738,
20003);
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 l_vendor_site_id,
l_party_site_id,
l_party_site_name,
l_object_version_number,
l_party_site_number,
l_location_id,
l_object_version_number2
FROM ap_supplier_sites_all pvs,
hz_party_sites hps,
hz_locations hl
WHERE pvs.vendor_site_code
= ‘采购‘--
AND pvs.org_id
= 82 --
AND pvs.party_site_id
= hps.party_site_id
AND pvs.location_id
= hl.location_id
and rownum
=1;
EXCEPTION
WHEN no_data_found
THEN
l_errmsg := ‘Can not find the vendor site need to update!The site code is:‘ || ‘采购‘;
dbms_output.put_line(l_errmsg);
END;
--
l_vendor_site_rec.vendor_site_id :
= l_vendor_site_id;
l_vendor_site_rec.vendor_site_code := ‘采购‘;
l_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 => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_errmsg,
p_vendor_site_rec => l_vendor_site_rec,
p_vendor_site_id => l_vendor_site_id);
IF (nvl(l_return_status, fnd_api.g_ret_sts_error)
<> fnd_api.g_ret_sts_success)
THEN
l_msg_count := fnd_msg_pub.count_msg;
IF l_msg_count
> 0 THEN
l_errmsg := substr(fnd_msg_pub.get(fnd_msg_pub.g_first, fnd_api.g_false),
1,
512);
FOR i
IN 1 .. (l_msg_count
- 1) LOOP
l_errmsg := l_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 :‘ || l_errmsg);
END IF;
--UPDATE party_site_name
l_party_site_rec.party_site_id :
= l_party_site_id;
l_party_site_rec.party_site_name := l_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 => l_object_version_number,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_errmsg);
IF (nvl(l_return_status, fnd_api.g_ret_sts_error)
<> fnd_api.g_ret_sts_success)
THEN
l_msg_count := fnd_msg_pub.count_msg;
IF l_msg_count
> 0 THEN
l_errmsg := substr(fnd_msg_pub.get(fnd_msg_pub.g_first, fnd_api.g_false),
1,
512);
FOR i
IN 1 .. (l_msg_count
- 1) LOOP
l_errmsg := l_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 :‘ || l_errmsg);
END IF;
--update location
l_location_rec.address_style :
= ‘POSTAL_ADDR_DEF‘;
l_location_rec.location_id := l_location_id;
hz_location_v2pub.update_location(p_init_msg_list => fnd_api.g_true,
p_location_rec => l_location_rec,
p_object_version_number => l_object_version_number2,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_errmsg);
IF (nvl(l_return_status, fnd_api.g_ret_sts_error)
<> fnd_api.g_ret_sts_success)
THEN
l_msg_count := fnd_msg_pub.count_msg;
IF l_msg_count
> 0 THEN
l_errmsg := substr(fnd_msg_pub.get(fnd_msg_pub.g_first, fnd_api.g_false),
1,
512);
FOR i
IN 1 .. (l_msg_count
- 1) LOOP
l_errmsg := l_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 :‘ || l_errmsg);
END IF;
--
END;
Oracle EBS AP更新供应商地址
标签：output evel err can style return sele substr except
