SELECTpvs.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.STATUSFROMap_supplier_sites_all pvs,
hz_party_sites hps,
hz_locations hlWHERE pvs.vendor_site_code = '采购'-- AND pvs.org_id = 82 -- AND pvs.party_site_id =hps.party_site_idAND pvs.location_id =hl.location_idand rownum =1;--供应商地址失效
DECLAREl_return_statusVARCHAR2(1) := NULL;
l_msg_countNUMBER;
l_errmsgVARCHAR2(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_numberNUMBER;
l_object_version_number2NUMBER;
l_vendor_site_idNUMBER := NULL;
l_party_site_idNUMBER := NULL;
l_party_site_nameVARCHAR2(240);
l_party_site_numberVARCHAR2(30);
l_location_idNUMBER;BEGINfnd_global.apps_initialize(0, 50738, 20003);BEGIN
SELECTpvs.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_numberINTOl_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_number2FROMap_supplier_sites_all pvs,
hz_party_sites hps,
hz_locations hlWHERE pvs.vendor_site_code = '采购'-- AND pvs.org_id = 82 -- AND pvs.party_site_id =hps.party_site_idAND pvs.location_id =hl.location_idand rownum =1;
EXCEPTIONWHEN no_data_found THENl_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) THENl_msg_count :=fnd_msg_pub.count_msg;IF l_msg_count > 0 THENl_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);ENDLOOP;
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) THENl_msg_count :=fnd_msg_pub.count_msg;IF l_msg_count > 0 THENl_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);ENDLOOP;
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) THENl_msg_count :=fnd_msg_pub.count_msg;IF l_msg_count > 0 THENl_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);ENDLOOP;
fnd_msg_pub.delete_msg();END IF;
dbms_output.put_line('------------------Update Error-------------------');
dbms_output.put_line('Error3 :' ||l_errmsg);END IF;--
END;