api to update supplier sites address in r12

api to update supplier sites address in r12

In this post , we will be discuss about api to update supplier sites address in r12. This api will help to update the supplier site address in r12. We can mass update the supplier site address using this api in r12. This api is the standard api , which will help to update supplier site address in r12. Here below is the complete api script to update supplier sites address in r12.

Important API to update Supplier site address

AP_VENDOR_PUB_PKG.UPDATE_VENDOR_SITE

Detail PLSQL script to update supplier sites address in r12

DECLARE

v_api_version   NUMBER;

v_init_msg_list VARCHAR2(200);

v_commit VARCHAR2(200);

v_validation_level NUMBER;

x_return_status VARCHAR2(200);

x_msg_count NUMBER;

v_vendor  NUMBER;

x_msg_data VARCHAR2(200);

l_vendor_site_rec apps.ap_vendor_pub_pkg.r_vendor_site_rec_type;

l_existing_vendor_site_rec ap_supplier_sites_all%ROWTYPE;

v_vendor_site_id NUMBER;

v_calling_prog VARCHAR2(200);

v_error_flag  VARCHAR2(200):='N';

v_error_message  VARCHAR2(4000):='N';

cursor c is

select VENDOR_NAME,address_line1,address_line2,address_line2,city,country from XX_VENDOR_SITES_STG;

CURSOR C1(P_VENDOR VARCHAR) IS SELECT VENDOR_SITE_ID

FROM ap_supplier_sites_all assa

WHERE assa.vendor_id =P_VENDOR;

BEGIN

for i in c loop

BEGIN

v_error_flag:='N';

v_error_message:=null;

select vendor_id into v_vendor from ap_suppliers

where upper(vendor_name)=upper(i.VENDOR_NAME);

EXCEPTION

WHEN OTHERS THEN

v_error_flag:='Y';

v_error_message:='Unable to find the supplier site information for site id';

DBMS_OUTPUT.put_line('Unable to find the supplier site information for site id' ||v_vendor_site_id);

END;

IF v_error_flag='N' THEN

FOR J IN C1(v_vendor) LOOP

fnd_global.apps_initialize(610970,50111,200);

mo_global.init('SQLAP');

fnd_client_info.set_org_context(4556);

v_api_version := 1.0;

v_init_msg_list := fnd_api.g_true;

v_commit := fnd_api.g_true;

v_validation_level := fnd_api.g_valid_level_full;

v_vendor_site_id := J.VENDOR_SITE_ID; -- to be end dated

v_calling_prog := 'Supplier Address Update';

l_vendor_site_rec.vendor_site_id := l_existing_vendor_site_rec.vendor_site_id;

l_vendor_site_rec.last_update_date := SYSDATE;

l_vendor_site_rec.last_updated_by := 6147023;

l_vendor_site_rec.vendor_id := l_existing_vendor_site_rec.vendor_id;

l_vendor_site_rec.org_id := l_existing_vendor_site_rec.org_id;

l_vendor_site_rec.address_line1 :=I.address_line1;

l_vendor_site_rec.address_line2 :=I.address_line2;

l_vendor_site_rec.address_line3 :=I.address_line3;

l_vendor_site_rec.city :=I.city;

AP_VENDOR_PUB_PKG.UPDATE_VENDOR_SITE(p_api_version => v_api_version,

p_init_msg_list => v_init_msg_list,

p_commit => v_commit,

p_validation_level => v_validation_level,

x_return_status => x_return_status,

x_msg_count => x_msg_count,

x_msg_data => x_msg_data,

p_vendor_site_rec => l_vendor_site_rec,

p_vendor_site_id => v_vendor_site_id,

p_calling_prog => v_calling_prog);

DBMS_OUTPUT.put_line('X_RETURN_STATUS = ' || x_return_status);

DBMS_OUTPUT.put_line('X_MSG_COUNT = ' || x_msg_count);

DBMS_OUTPUT.put_line('X_MSG_DATA = ' || x_msg_data);

END LOOP;

UPDATE XX_VENDOR_SITES_STG

SET STATUS='SUCCESS'

WHERE VENDOR_NAME=I.VENDOR_NAME;

END IF;

IF v_error_flag='Y' THEN

UPDATE XX_VENDOR_SITES_STG

SET STATUS='ERROR',

ATTRIBUTE1=v_error_message

WHERE VENDOR_NAME=I.VENDOR_NAME;

END IF;

COMMIT;

END LOOP;

END;