Oracle EBS AP 供应商地点失效
时间:2021-07-01 10:21:17
帮助过:5人阅读
供应商地点失效
creation: created by jenrry 20161108 1.00
*/
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;
p_vendor_rec ap_vendor_pub_pkg.r_vendor_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;
l_object_version_number number;
ln_party_site_id NUMBER :
= NULL;
lv_party_site_name VARCHAR2(
240);
lv_party_site_number VARCHAR2(
30);
ln_location_id NUMBER;
x_profile_id number;
l_party_rec hz_party_v2pub.party_rec_type;
l_organization_rec hz_party_v2pub.organization_rec_type;
l_status VARCHAR2(
1) :
= ‘A‘;
cursor cur_tmp
is
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 object_version_number2,
po.VENDOR_NAME,
pvs.VENDOR_SITE_CODE,
po.VENDOR_ID,
po.PARTY_ID,
pvs.INACTIVE_DATE
FROM ap_supplier_sites_all pvs,
hz_party_sites hps,
hz_locations hl,
ap_suppliers PO
WHERE pvs.party_site_id
= hps.party_site_id
AND pvs.location_id
= hl.location_id
and po.VENDOR_ID
= pvs.VENDOR_ID
and po.ATTRIBUTE13
= ‘S201608160069‘;
BEGIN
--
fnd_global.apps_initialize(0,
50738,
20003);
dbms_output.put_line(‘------------------Start Update-------------------‘);
for cur
in cur_tmp loop
--
lt_vendor_site_rec.vendor_site_id :
= cur.vendor_site_id;
if l_status
= ‘I‘ then
lt_vendor_site_rec.inactive_date := SYSDATE;
else
lt_vendor_site_rec.inactive_date := fnd_api.G_NULL_DATE;
--sysdate + 30;--to_date(‘3033-12-07‘,‘YYYY-MM-DD‘);
end if;
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 => cur.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;
IF lv_return_status
= fnd_api.g_ret_sts_success
THEN
dbms_output.put_line(‘------------------Update vendor Success-------------------‘);
END IF;
l_party_site_rec.party_site_id := cur.party_site_id;
l_party_site_rec.status := l_status;
--
begin
select hps.OBJECT_VERSION_NUMBER
into ln_object_version_number
from hz_party_sites hps
where hps.PARTY_SITE_ID
= cur.party_site_id;
end;
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;
IF lv_return_status
= fnd_api.g_ret_sts_success
THEN
dbms_output.put_line(‘------------------Update party site Success-------------------‘);
END IF;
--update location
lt_location_rec.address_style :
= ‘POSTAL_ADDR_DEF‘;
lt_location_rec.location_id := cur.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 => cur.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 location Success-------------------‘);
END IF;
--update vendor name
p_vendor_rec.vendor_id :
= cur.vendor_id;
if l_status
= ‘I‘ then
p_vendor_rec.VENDOR_NAME := cur.VENDOR_NAME
|| ‘(作废)‘;
p_vendor_rec.END_DATE_ACTIVE := sysdate;
else
p_vendor_rec.VENDOR_NAME := replace(cur.VENDOR_NAME,
‘(作废)‘,
‘‘);
p_vendor_rec.END_DATE_ACTIVE := fnd_api.G_NULL_DATE;
--sysdate + 30; --to_date(‘3033-12-07‘,‘YYYY-MM-DD‘);
p_vendor_rec.START_DATE_ACTIVE :
= sysdate;
end if;
ap_vendor_pub_pkg.update_vendor(p_api_version => 1.0,
p_init_msg_list => fnd_api.g_false,
p_commit => fnd_api.g_false,
p_validation_level => NULL,
x_return_status => lv_return_status,
x_msg_count => ln_msg_count,
x_msg_data => lv_errmsg,
p_vendor_rec => p_vendor_rec,
p_vendor_id => cur.vendor_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 vendor name Error-------------------‘);
dbms_output.put_line(‘Error1 :‘ || lv_errmsg);
END IF;
IF lv_return_status
= fnd_api.g_ret_sts_success
THEN
dbms_output.put_line(‘------------------Update vendor name Success-------------------‘);
END IF;
--
begin
SELECT hp.object_version_number
INTO l_object_version_number
FROM hz_parties hp
WHERE hp.party_id
= cur.party_id
and rownum
= 1;
end;
l_party_rec.party_id := cur.party_id;
l_organization_rec.party_rec := l_party_rec;
if l_status
= ‘I‘ then
l_organization_rec.organization_name := cur.vendor_name
|| ‘(作废)‘;
else
l_organization_rec.organization_name := replace(cur.vendor_name ,
‘(作废)‘,
‘‘);
end if;
cux_ap_vendors_api.update_organization(p_init_msg_list => fnd_api.g_false,
p_commit => fnd_api.g_false,
p_party_id => cur.party_id,
p_organization_rec => l_organization_rec,
x_profile_id => x_profile_id,
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 vendor party name Error-------------------‘);
dbms_output.put_line(‘Error1 :‘ || lv_errmsg);
END IF;
IF lv_return_status
= fnd_api.g_ret_sts_success
THEN
dbms_output.put_line(‘------------------Update vendor party name Success-------------------‘);
END IF;
END LOOP;
dbms_output.put_line(‘------------------Update End-------------------‘);
END;
Oracle EBS AP 供应商地点失效
标签:oracl file org glob HERE pkg line ini cat