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

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

 

This below script helps to update Customer Sites and Assign Vat code/Tax Code and Vat Registration No/ Tax Registration No in Customer Master in Oracle.

This is the complete API to Update Customer Sites Information in Oracle Apps.

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

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

DECLARE

x_return_status varchar2(10);

l_init_msg_list VARCHAR2 (1000) := FND_API.G_TRUE;

x_msg_count number(10);

x_msg_data varchar2(1200);

p_object_version_number number(10):=18;

l_cust_site_use_rec APPS.hz_cust_account_site_v2pub.cust_site_use_rec_type;

begin

fnd_global.apps_initialize(61477,61595,222);

dbms_output.put_line('API STARTED' );

mo_global.set_policy_context('S',5674);

fnd_profile.initialize(61470,61515,222);

l_cust_site_use_rec.SITE_USE_ID:=264031;

l_cust_site_use_rec.cust_acct_site_id:=225246;

l_cust_site_use_rec.TAX_CODE := ‘XX_VAT_5';

--l_cust_site_use_rec.status := 'I';

dbms_output.put_line('API STARTED EXECUTION' );

hz_cust_account_site_v2pub.update_cust_site_use(fnd_api.g_true,

l_cust_site_use_rec,

p_object_version_number,

x_return_status,

x_msg_count,

x_msg_data);IF x_return_status = 'S' THEN

dbms_output.put_line(' Now site use is active' );

ELSE

IF NVL (x_msg_count, 0) > 1 THEN

 FOR i IN 1 .. x_msg_count LOOP

 dbms_output.put_line(' Error Status ' ||x_return_status);

 dbms_output.put_line(' Error message ' ||x_msg_data);

 END LOOP;

 ELSE

 dbms_output.put_line(' Error message ' ||x_msg_data);

 END IF;

 END IF;

 commit;

 exception when others then

 dbms_output.put_line(' Error Here'||sqlcode||sqlerrm);

 end;

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

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;