AP_VENDOR_PUB_PKG.UPDATE_VENDOR_SITE
API-Oracle Apps 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,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;