oracle ebs ap,Oracle EBS AP更新供应商地址

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;