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;