DECLARE
l_flag varchar2(10) default 'Y';
--
lv_return_status VARCHAR2 (1);
lv_msg_count NUMBER;
lv_msg_data VARCHAR2 (2000);
--
lv_vendor_CONTACT_id ap_supplier_CONTACTS.vendor_CONTACT_id%TYPE;
lv_vendor_site_id ap_supplier_sites.vendor_site_id%TYPE;
lv_vendor_id ap_suppliers.vendor_id%TYPE;
-- lv_vendor_site_rec ap_vendor_pub_pkg.r_vendor_site_rec_type;
-- lv_vendor_rec ap_vendor_pub_pkg.r_vendor_rec_type;
lv_vendor_contact_rec ap_vendor_pub_pkg.r_vendor_contact_rec_type;
ln_vendor_id NUMBER;
ln_vendor_site_id NUMBER;
ln_vendor_CONTACT_id NUMBER;
--
v_user_id NUMBER;
v_resp_id NUMBER;
v_resp_appl_id NUMBER;
----
cursor cur_vendor is SELECT *
FROM apps.XX_SUPPLIER_SITES_CONTACT WHERE SUPPLIER_CONTACT_ID = 294 ;
begin
fnd_global.apps_initialize (1110,50622,200);
mo_global.init ('S');
-- lv_vendor_site_id := 4959;
-- lv_vendor_id := 32652;
-- lv_vendor_CONTACT_id := 294;
for i in cur_vendor loop
SELECT vendor_id
INTO ln_vendor_id
FROM ap_suppliers
WHERE vendor_id = i.SUPPLIER_id;
SELECT vendor_site_id
INTO ln_vendor_site_id
FROM ap_supplier_sites_all
WHERE vendor_id = ln_vendor_id
AND vendor_site_ID= i.SUPPLIER_site_ID;
SELECT VENDOR_CONTACT_ID
INTO ln_vendor_contact_id
FROM ap_supplier_Contacts WHERE
VENDOR_CONTACT_ID = I.SUPPLIER_CONTACT_ID;
lv_vendor_site_id := ln_vendor_site_id;
lv_vendor_id := ln_vendor_id;
lv_vendor_CONTACT_id := ln_vendor_contact_id;
SELECT VENDOR_CONTACT_ID ,
VENDOR_SITE_ID ,
VENDOR_ID
, PER_PARTY_ID ,
RELATIONSHIP_ID ,
REL_PARTY_ID ,
PARTY_SITE_ID ,
ORG_CONTACT_ID ,
ORG_PARTY_SITE_ID
INTO
lv_vendor_contact_rec.vendor_contact_id,
lv_vendor_contact_rec.VENDOR_SITE_ID,
lv_vendor_contact_rec.VENDOR_ID,
lv_vendor_contact_rec.PER_PARTY_ID,
lv_vendor_contact_rec.RELATIONSHIP_ID,
lv_vendor_contact_rec.REL_PARTY_ID,
lv_vendor_contact_rec.PARTY_SITE_ID,
lv_vendor_contact_rec.ORG_CONTACT_ID,
lv_vendor_contact_rec.ORG_PARTY_SITE_ID
FROM po_vendor_contacts
where vendor_contact_id= lv_vendor_CONTACT_id AND VENDOR_SITE_ID = lv_vendor_site_id AND VENDOR_ID = lv_vendor_id ;
lv_vendor_contact_rec.inactive_date := trunc(sysdate-5); --fnd_api.g_miss_date;
--lV_vendor_contact_rec.person_middle_name := 'Sekhar';
ap_vendor_pub_pkg.update_vendor_contact
(p_api_version => 1.0,
p_init_msg_list => fnd_api.g_FALSE,
p_commit => fnd_api.g_false,
p_validation_level => fnd_api.g_valid_level_full,
p_vendor_contact_rec => lv_vendor_contact_rec,
x_return_status => lv_return_status,
x_msg_count => lv_msg_count,
x_msg_data => lv_msg_data
);
COMMIT;
end loop;
dbms_output.put_line('return_status: '||lV_return_status);
dbms_output.put_line('msg_data: '||lV_msg_data);
dbms_output.put_line('msg_count: '||lV_msg_count);
IF lV_return_status ='U'
THEN
lV_msg_data := SUBSTR (lv_msg_data || SQLERRM, 1, 4000);
END IF;
DBMS_OUTPUT.put_line ('l_msg_data: ' || lV_msg_data);
-- END;
FOR I IN 1..lv_msg_count
LOOP
lv_msg_data := lv_msg_count||SUBSTR(FND_MSG_PUB.GET(p_encoded=>'T'),1,255);
fnd_file.put_line(fnd_file.log,'Error occured while updating'||lv_msg_data);
END LOOP ;
exception
when others then
fnd_file.put_line(fnd_file.log,sqlcode||','||sqlerrm);
end;
