API to Update Supplier Sites and assign Tax code in Oracle Apps

API to Update Supplier Sites and assign Tax code in Oracle Apps

This below script helps to update Supplier Vat Tax code/Tax Code and Vat Registration No/ Tax Registration No in Supplier and Supplier Site Master in Oracle E-Business suit . Using this below script you can also update other Supplier sites Information through this API.

We are using Standard Oracle API to Achieve this Below requirement.

API to Update Supplier Sites and assign Tax code in Oracle apps

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,TAX_REGISTRATION_NO,TAX_CODE from XX_VENDOR_SITES_STG

GROUP BY VENDOR_NAME,REMITTANCE_EMAIL,TAX_CODE;

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 Tax code 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.vat_code :=I.TAX_CODE;

l_vendor_site_rec.AUTO_TAX_CALC_FLAG:='Y';

l_vendor_site_rec.VAT_REGISTRATION_NUM:=I.TAX_REGISTRATION_NO;

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;

Please share your comments if you like this post and pls email me if you are getting any issue in that.