Summary
Improve EU VAT compliance with Invantive’s latest SQL functions is_eu_vat and is_vies_eu_vat for automated VAT number validation and real-time VIES checks.
Introduction
The introduction of the is_eu_vat and is_vies_eu_vat functions within Invantive’s UniversalSQL SQL-dialect is a significant leap forward to ease administering large numbers of European customers and suppliers by automated validation of VAT (Value Added Tax) numbers. This article delves into the practical applications of these and related functions.
Static VAT validation algorithm versus dynamic VIES checks
The is_eu_vat function is a static algorithm for EU VAT number checks. It validates the format and the mathematical logic behind a European VAT number, ensuring it conforms to the standard structure as defined by the respective European Union’s member states.
is_eu_vat is a very fast algorithm, capable of validating over 100.000 VAT numbers per second. It provides a first filter without the need for real-time network checks.
On the other hand, the is_vies_eu_vat function takes validation a step further by integrating with the VIES (VAT Information Exchange System) to verify the active status of a VAT number. This real-time check is crucial for transactions requiring up-to-date confirmation of a business’s VAT registration status, aiding in compliance with EU regulations and preventing fraudulent activities.
The initial lookup of a VAT number using the is_vies_eu_vat SQL-function peaks at 30 validations per second per parallel thread. Once a VAT number has been validated using VIES, the data is cached for the duration of the database session.
Practical Application
For developers utilizing the SQL-engine offered by Invantive, incorporating the is_eu_vat and is_vies_eu_vat functions into their workflow can significantly enhance the efficiency and reliability of data validation processes. These functions can be seamlessly integrated into SQL queries, providing immediate feedback on VAT number validity.
Popular applications are:
- Sales invoice generation and validation
- Database cleansing
- Automated compliance checks during on-boarding
Database Cleaning
Maintaining a clean and reliable database is paramount for businesses. Developers can use these functions to periodically verify the VAT numbers stored in their databases, identifying and correcting any inaccuracies. This proactive approach ensures that the database remains a reliable source for business operations and reporting.
For instance, the follow Invantive PSQL code verifies the VAT state for all customers in Invantive Estate:
--
-- Print VAT numbers for parties in a EU-country
-- that are found to be invalid according to VIES
-- and/or static algorithms.
--
declare
l_filter varchar2;
l_static_correct boolean;
l_vies_correct boolean;
begin
--
-- Select all EU-countries.
--
for r_cty in
( select country_code
from csvtable
( passing 'AT,BE,BG,CY,CZ,DE,DK,EE,ES,FI,FR,GR,HR'
|| ',HU,IE,IM,IT,LT,LU,LV,MC,MT,NL,PL,PT,RO,SE,SI,SK'
row delimiter ','
column delimiter '#'
columns country_code varchar2 not null position next
)
)
loop
--
-- Retrieve all parties in a country
-- with a VAT code.
--
dbms_output.put_line
( 'Check VAT numbers for the EU country '
|| r_cty.country_code
|| '.'
);
l_filter := r_cty.country_code || '%';
create or replace table PartiesInCountry@inmemorystorage
as
select lvr_btw_nummer
, lvr_naam
, lvr_id
from bubs_leveranciers_v@ora
where lvr_btw_nummer is not null
and lvr_btw_nummer not in ('UNKNOWN', 'NA')
and lvr_btw_nummer like l_filter
and lvr_klant_vlag = 'Y'
;
for r_lvr
in
( select *
from PartiesInCountry@InMemoryStorage
)
loop
--
-- When not a valid or active VAT number: remove it.
--
l_static_correct := is_eu_vat(r_lvr.lvr_btw_nummer);
l_vies_correct := is_vies_eu_vat(r_lvr.lvr_btw_nummer);
if not l_static_correct or not l_vies_correct
then
dbms_output.put_line
( 'VAT number '
|| r_lvr.lvr_btw_nummer
|| ', static correct '
|| to_char(l_static_correct)
|| ', VIES correct '
|| to_char(l_vies_correct)
|| '.'
);
end if;
end loop;
end loop;
end;
Additional EU VAT SQL Functions
Additional SQL function ease the handling of VAT checks:
is_eu_vat_reason: return reason why the static EU VAT number validation failed.vies_eu_vat_name: company name linked to the VAT number.vies_eu_vat_address: address linked to the VAT number.vies_eu_vat_country: country linked to the VAT number.
Related SQL Functions
Other popular data validation functions are:
is_email: verify whether an email address conforms to RFC 822.is_iban: verify whether an IBAN account matches requirements.
See New business validation SQL-functions: is_email (email), is_iban (IBAN) and is_uri (URI) for more information.
A sample to validate IBAN accounts using BlueM is presented on IBAN-validaties met de Bluem IBAN-validatieservice (Dutch).
Belgium
VAT numbers must be activated for cross-country use in the EU. It seems that Belgium VAT numbers are often not activated, leading to a VIES check failing. Contact your business partner to activate it when necessary.