EU VAT changes for sales to consumers in other EU countries

The intended process to handle the EU VAT switch 2015-01-01 is to first a day or a few days before the end of the year disable the creation of renewal invoices.

This is done by going to "Settings" / "Scheduled task settings" in the AdminPanel (URL is https://admin.somedomain.com/ScheduledTaskSettings), searching

for "Subscription", checking the row called "Subscription renewal" and then clicking the pause button.

Once done the row for "Subscription renewal" will have the text "Paused" in the "Next run" column.

The VAT rules configured should then be changed as soon as possible after 2015-01-01, and once done the renewals should be reenabled on the same page

with the "Run now / Resume" button. The process for changing the VAT rules are described below. Also a query for checking if affected (to private

persons in other EU countries than the reseller) orders where placed between 2015-01-01 00:00 and when the VAT rules were updated exists at the

bottom of this document.

Our VAT rules are stored in the table tax_rules for items having the RuleBasedTaxPlugin (default). They are evaluated in order based on the priority field and the first matching rule wins.

Before 2015-01-01 the VAT rules for companies in the EU (or countries with similar rules) we configured where:

priority 1 = customer country is reseller country => reseller country vat is charged for both company and consumer

priority 2 = customer country is in EU and vatno is specified => no VAT is charged (company)

priority 3 = customer country is in EU and vatno is not specified => reseller country vat is charged (consumer)

priority 4 = customer is outside of reseller country and EU => no vat is charged

After 2015-01-01 the rules are to be:

priority 1 (unchanged) = customer country is reseller country => reseller country vat is charged for both company and consumer

priority 2 (unchanged) = customer country is in EU and vatno is specified => no VAT is charged (company)prio

priority 3..31 (changed) = customer country is from a specific EU member state and vatno is not specified => customer country vat is charged (consumer)

priority 32 (unchanged, but prio moved) = customer is outside of reseller country and EU => no vat is charged

The rules have to be changed according to this for each fk_tax_profile_id + reseller_country_code in our customer's environment that currently has EU tax rules configured.

In the case of multiple resellers and/or multiple tax profiles, the following queries should be changed to reflect that.

1.Tax profiles can be found with: 

SELECT * FROM tax_profile

2.It is important to first verify that the fk_tax_profile_id + reseller_country_code used has four rules matching the description above, i.e. like the following for a Swedish reseller (SE country code).

SELECT [name]

      ,[priority]

      ,[reseller_country_code]

      ,[customer_country_code]

      ,[customer_country_tag]

      ,[vat_number_present]

      ,[tax_rate]

FROM [AtomiaBilling].[dbo].[tax_rule]

WHERE fk_tax_profile_id = '2DAC8D7E-32C4-4A40-9E69-0422F8BEB505' 

AND reseller_country_code = 'SE' -- should be changed to the reseller's country code for each reseller

ORDER BY priority

3.Here is the update script with variables set to update a

------------------------------------------------------------ Start of EU VAT change script.

BEGIN TRANSACTION

DECLARE @reseller_country_to_update varchar(255)

DECLARE @tax_profile_to_update uniqueidentifier

SET @reseller_country_to_update = 'SE' -- should be changed to the reseller's country code for each reseller

SET @tax_profile_to_update = '2DAC8D7E-32C4-4A40-9E69-0422F8BEB505'

-- Move non EU => no VAT rule last

UPDATE AtomiaBilling..tax_rule SET priority = 32 WHERE fk_tax_profile_id = @tax_profile_to_update AND reseller_country_code = @reseller_country_to_update AND priority = 4

-- Delete current third rule (EU consumer outside of reseller country)

DELETE AtomiaBilling..tax_rule WHERE fk_tax_profile_id = @tax_profile_to_update AND reseller_country_code = @reseller_country_to_update AND priority = 3

-- Insert the new rules

INSERT INTO AtomiaBilling..tax_rule (id, fk_tax_profile_id, name, priority, tax_part, reseller_country_code, customer_country_code, customer_country_tag, vat_number_present, vat_number_valid, tax_rate)

VALUES (NEWID(), @tax_profile_to_update, @reseller_country_to_update + '-AT-' + '20%', 3, 0, @reseller_country_to_update, 'AT', 'EU', 2, 0, 20),

(NEWID(), @tax_profile_to_update, @reseller_country_to_update + '-BE-' + '21%', 4, 0, @reseller_country_to_update, 'BE', 'EU', 2, 0, 21),

(NEWID(), @tax_profile_to_update, @reseller_country_to_update + '-BG-' + '20%', 5, 0, @reseller_country_to_update, 'BG', 'EU', 2, 0, 20),

(NEWID(), @tax_profile_to_update, @reseller_country_to_update + '-HR-' + '25%', 6, 0, @reseller_country_to_update, 'HR', 'EU', 2, 0, 25),

(NEWID(), @tax_profile_to_update, @reseller_country_to_update + '-CY-' + '19%', 7, 0, @reseller_country_to_update, 'CY', 'EU', 2, 0, 19),

(NEWID(), @tax_profile_to_update, @reseller_country_to_update + '-CZ-' + '21%', 8, 0, @reseller_country_to_update, 'CZ', 'EU', 2, 0, 21),

(NEWID(), @tax_profile_to_update, @reseller_country_to_update + '-DK-' + '25%', 9, 0, @reseller_country_to_update, 'DK', 'EU', 2, 0, 25),

(NEWID(), @tax_profile_to_update, @reseller_country_to_update + '-EE-' + '20%', 10, 0, @reseller_country_to_update, 'EE', 'EU', 2, 0, 20),

(NEWID(), @tax_profile_to_update, @reseller_country_to_update + '-FI-' + '24%', 11, 0, @reseller_country_to_update, 'FI', 'EU', 2, 0, 24),

(NEWID(), @tax_profile_to_update, @reseller_country_to_update + '-FR-' + '20%', 12, 0, @reseller_country_to_update, 'FR', 'EU', 2, 0, 20),

(NEWID(), @tax_profile_to_update, @reseller_country_to_update + '-DE-' + '19%', 13, 0, @reseller_country_to_update, 'DE', 'EU', 2, 0, 19),

(NEWID(), @tax_profile_to_update, @reseller_country_to_update + '-GR-' + '23%', 14, 0, @reseller_country_to_update, 'GR', 'EU', 2, 0, 23),

(NEWID(), @tax_profile_to_update, @reseller_country_to_update + '-HU-' + '27%', 15, 0, @reseller_country_to_update, 'HU', 'EU', 2, 0, 27),

(NEWID(), @tax_profile_to_update, @reseller_country_to_update + '-IE-' + '23%', 16, 0, @reseller_country_to_update, 'IE', 'EU', 2, 0, 23),

(NEWID(), @tax_profile_to_update, @reseller_country_to_update + '-IT-' + '22%', 17, 0, @reseller_country_to_update, 'IT', 'EU', 2, 0, 22),

(NEWID(), @tax_profile_to_update, @reseller_country_to_update + '-LV-' + '21%', 18, 0, @reseller_country_to_update, 'LV', 'EU', 2, 0, 21),

(NEWID(), @tax_profile_to_update, @reseller_country_to_update + '-LT-' + '21%', 19, 0, @reseller_country_to_update, 'LT', 'EU', 2, 0, 21),

(NEWID(), @tax_profile_to_update, @reseller_country_to_update + '-LU-' + '15%', 20, 0, @reseller_country_to_update, 'LU', 'EU', 2, 0, 15),

(NEWID(), @tax_profile_to_update, @reseller_country_to_update + '-MT-' + '18%', 21, 0, @reseller_country_to_update, 'MT', 'EU', 2, 0, 18),

(NEWID(), @tax_profile_to_update, @reseller_country_to_update + '-NL-' + '21%', 22, 0, @reseller_country_to_update, 'NL', 'EU', 2, 0, 21),

(NEWID(), @tax_profile_to_update, @reseller_country_to_update + '-PL-' + '23%', 23, 0, @reseller_country_to_update, 'PL', 'EU', 2, 0, 23),

(NEWID(), @tax_profile_to_update, @reseller_country_to_update + '-PT-' + '23%', 24, 0, @reseller_country_to_update, 'PT', 'EU', 2, 0, 23),

(NEWID(), @tax_profile_to_update, @reseller_country_to_update + '-RO-' + '24%', 25, 0, @reseller_country_to_update, 'RO', 'EU', 2, 0, 24),

(NEWID(), @tax_profile_to_update, @reseller_country_to_update + '-SK-' + '20%', 26, 0, @reseller_country_to_update, 'SK', 'EU', 2, 0, 20),

(NEWID(), @tax_profile_to_update, @reseller_country_to_update + '-SI-' + '22%', 27, 0, @reseller_country_to_update, 'SI', 'EU', 2, 0, 22),

(NEWID(), @tax_profile_to_update, @reseller_country_to_update + '-ES-' + '21%', 28, 0, @reseller_country_to_update, 'ES', 'EU', 2, 0, 21),

(NEWID(), @tax_profile_to_update, @reseller_country_to_update + '-SE-' + '25%', 29, 0, @reseller_country_to_update, 'SE', 'EU', 2, 0, 25),

(NEWID(), @tax_profile_to_update, @reseller_country_to_update + '-UK-' + '20%', 30, 0, @reseller_country_to_update, 'UK', 'EU', 2, 0, 20),

(NEWID(), @tax_profile_to_update, @reseller_country_to_update + '-GB-' + '20%', 31, 0, @reseller_country_to_update, 'GB', 'EU', 2, 0, 20)

COMMIT TRANSACTION

------------------------------------------------------------ End of EU VAT change script.

4.Which after the update should give a result like:

SELECT [name]

      ,[priority]

      ,[reseller_country_code]

      ,[customer_country_code]

      ,[customer_country_tag]

      ,[vat_number_present]

      ,[tax_rate]

FROM [AtomiaBilling].[dbo].[tax_rule]

WHERE fk_tax_profile_id = '2DAC8D7E-32C4-4A40-9E69-0422F8BEB505' AND reseller_country_code = 'SE'

ORDER BY priority

Here is an SQL query to list all orders made after 2015-01-01 before the tax rules are updated. These should be checked and their invoices corrected/credited if a wrong tax is applied.

select reseller.id ResellerId, reseller.name ResellerNo, r_address.company_name ResellerName, r_country.name + ' (' + r_country.code + ')' ResellerCountry,

 customer.id CustomerId, customer.name CustomerNo, c_country.name + ' (' + c_country.code + ')' CustomerCountry, o.id OrderId, o.number OrderNo, o.created_time OrderCreationTime

from AtomiaBilling..order_data o

inner join AtomiaAccount..account reseller on reseller.id = o.reseller_id

inner join AtomiaAccount..account_address r_address on r_address.id = reseller.fk_main_address_id

inner join AtomiaAccount..country r_country on r_address.fk_country_id = r_country.id and r_country.tag = 'EU'

inner join AtomiaAccount..account customer on customer.id = o.customer_id

inner join AtomiaAccount..account_address c_address on customer.fk_main_address_id = c_address.id

inner join AtomiaAccount..country c_country on c_address.fk_country_id = c_country.id and c_country.tag = 'EU'

where o.created_time > '2015-01-01' and c_country.code <> r_country.code

order by reseller.name, o.created_time

All of the above queries are for SQL database and they have the numeric mark next to each query.

Below you will find all these queries for PostgreSQL database where their numerics match numerics of SQL queries. 

IMPORTANT: You need to be INTO AtomiaBilling database!!!

1.Tax profiles can be found with:

          SELECT * FROM tax_profile;

2.It is important to first verify that the fk_tax_profile_id + reseller_country_code used has four rules matching the description above, i.e. like the following for a Swedish reseller (SE country code).

         SELECT name, priority, reseller_country_code, customer_country_code, customer_country_tag, vat_number_present, tax_rate

         FROM tax_rule

         WHERE fk_tax_profile_id = '2DAC8D7E-32C4-4A40-9E69-0422F8BEB505'

         AND reseller_country_code = 'SE'

        ORDER BY priority;

3.Here is the update script with variables set to update a

        --you need first to create function with required reseller country code

        CREATE or REPLACE function reseller_country() returns text language plpgsql AS $$

        DECLARE

        res_count text;

        BEGIN

        res_count := 'SE';

        return res_count;

        END;

        $$

        --run below query to check did you set function to correct reseller country code

        SELECT reseller_country();

 --you need first to create function with required tax_profile_id

CREATE or REPLACE function tax_profile() returns uuid language plpgsql AS $$

DECLARE

tax_prof uuid;

BEGIN

tax_prof := '2DAC8D7E-32C4-4A40-9E69-0422F8BEB505';

return tax_prof;

END;

$$

 --run below query to check did you set function to correct tax_profile_id

select tax_profile();

>Firsto to run this query

CREATE EXTENSION "uuid-ossp";

>Then to run this query

BEGIN;

UPDATE tax_rule SET priority = 32 WHERE fk_tax_profile_id = tax_profile() AND reseller_country_code = reseller_country() AND priority = 4; 

-- Delete current third rule (EU consumer outside of reseller country)

DELETE from tax_rule WHERE fk_tax_profile_id = tax_profile() AND reseller_country_code = reseller_country() AND priority = 3;

-- Insert the new rules

INSERT INTO tax_rule (id, fk_tax_profile_id, name, priority, tax_part, reseller_country_code, customer_country_code, customer_country_tag, vat_number_present, vat_number_valid, tax_rate)

VALUES (uuid_generate_v4(), tax_profile(), reseller_country() || '-AT-' || '20%', 3, 0, reseller_country(), 'AT', 'EU', 2, 0, 20),

(uuid_generate_v4(), tax_profile(), reseller_country() || '-BE-' || '21%', 4, 0, reseller_country(), 'BE', 'EU', 2, 0, 21),

(uuid_generate_v4(), tax_profile(), reseller_country() || '-BG-' || '20%', 5, 0, reseller_country(), 'BG', 'EU', 2, 0, 20),

(uuid_generate_v4(), tax_profile(), reseller_country() || '-HR-' || '25%', 6, 0, reseller_country(), 'HR', 'EU', 2, 0, 25),

(uuid_generate_v4(), tax_profile(), reseller_country() || '-CY-' || '19%', 7, 0, reseller_country(), 'CY', 'EU', 2, 0, 19),

(uuid_generate_v4(), tax_profile(), reseller_country() || '-CZ-' || '21%', 8, 0, reseller_country(), 'CZ', 'EU', 2, 0, 21),

(uuid_generate_v4(), tax_profile(), reseller_country() || '-DK-' || '25%', 9, 0, reseller_country(), 'DK', 'EU', 2, 0, 25),

(uuid_generate_v4(), tax_profile(), reseller_country() || '-EE-' || '20%', 10, 0, reseller_country(), 'EE', 'EU', 2, 0, 20),

(uuid_generate_v4(), tax_profile(), reseller_country() || '-FI-' || '24%', 11, 0, reseller_country(), 'FI', 'EU', 2, 0, 24),

(uuid_generate_v4(), tax_profile(), reseller_country() || '-FR-' || '20%', 12, 0, reseller_country(), 'FR', 'EU', 2, 0, 20),

(uuid_generate_v4(), tax_profile(), reseller_country() || '-DE-' || '19%', 13, 0, reseller_country(), 'DE', 'EU', 2, 0, 19),

(uuid_generate_v4(), tax_profile(), reseller_country() || '-GR-' || '23%', 14, 0, reseller_country(), 'GR', 'EU', 2, 0, 23),

(uuid_generate_v4(), tax_profile(), reseller_country() || '-HU-' || '27%', 15, 0, reseller_country(), 'HU', 'EU', 2, 0, 27),

(uuid_generate_v4(), tax_profile(), reseller_country() || '-IE-' || '23%', 16, 0, reseller_country(), 'IE', 'EU', 2, 0, 23),

(uuid_generate_v4(), tax_profile(), reseller_country() || '-IT-' || '22%', 17, 0, reseller_country(), 'IT', 'EU', 2, 0, 22),

(uuid_generate_v4(), tax_profile(), reseller_country() || '-LV-' || '21%', 18, 0, reseller_country(), 'LV', 'EU', 2, 0, 21),

(uuid_generate_v4(), tax_profile(), reseller_country() || '-LT-' || '21%', 19, 0, reseller_country(), 'LT', 'EU', 2, 0, 21),

(uuid_generate_v4(), tax_profile(), reseller_country() || '-LU-' || '15%', 20, 0, reseller_country(), 'LU', 'EU', 2, 0, 15),

(uuid_generate_v4(), tax_profile(), reseller_country() || '-MT-' || '18%', 21, 0, reseller_country(), 'MT', 'EU', 2, 0, 18),

(uuid_generate_v4(), tax_profile(), reseller_country() || '-NL-' || '21%', 22, 0, reseller_country(), 'NL', 'EU', 2, 0, 21),

(uuid_generate_v4(), tax_profile(), reseller_country() || '-PL-' || '23%', 23, 0, reseller_country(), 'PL', 'EU', 2, 0, 23),

(uuid_generate_v4(), tax_profile(), reseller_country() || '-PT-' || '23%', 24, 0, reseller_country(), 'PT', 'EU', 2, 0, 23),

(uuid_generate_v4(), tax_profile(), reseller_country() || '-RO-' || '24%', 25, 0, reseller_country(), 'RO', 'EU', 2, 0, 24),

(uuid_generate_v4(), tax_profile(), reseller_country() || '-SK-' || '20%', 26, 0, reseller_country(), 'SK', 'EU', 2, 0, 20),

(uuid_generate_v4(), tax_profile(), reseller_country() || '-SI-' || '22%', 27, 0, reseller_country(), 'SI', 'EU', 2, 0, 22),

(uuid_generate_v4(), tax_profile(), reseller_country() || '-ES-' || '21%', 28, 0, reseller_country(), 'ES', 'EU', 2, 0, 21),

(uuid_generate_v4(), tax_profile(), reseller_country() || '-SE-' || '25%', 29, 0, reseller_country(), 'SE', 'EU', 2, 0, 25),

(uuid_generate_v4(), tax_profile(), reseller_country() || '-UK-' || '20%', 30, 0, reseller_country(), 'UK', 'EU', 2, 0, 20),

(uuid_generate_v4(), tax_profile(), reseller_country() || '-GB-' || '20%', 31, 0, reseller_country(), 'GB', 'EU', 2, 0, 20);

END;

COMMIT TRANSACTION;

4.Which after the update should give a result like:

         SELECT name, priority, reseller_country_code, customer_country_code, customer_country_tag, vat_number_present, tax_rate

         FROM tax_rule

         WHERE fk_tax_profile_id = '2DAC8D7E-32C4-4A40-9E69-0422F8BEB505'

         AND reseller_country_code = 'SE'

        ORDER BY priority;

5.In order to have tax rules to work properly, table tax_item_category in AtomiaBilling must be UPDATED. To update tax_item_category table you must run next script.

INSERT INTO tax_item_category(id, fk_tax_profile_id, name, pretty_name)

VALUES (uuid_generate_v4(), '2dac8d7e-32c4-4a40-9e69-0422f8beb505', 'Hosting', 'Hosting'),

(uuid_generate_v4(), '2dac8d7e-32c4-4a40-9e69-0422f8beb505', 'DNS', 'DNS'),

(uuid_generate_v4(), '2dac8d7e-32c4-4a40-9e69-0422f8beb505', 'Domain', 'Domain'),

(uuid_generate_v4(), '2dac8d7e-32c4-4a40-9e69-0422f8beb505', 'ExtraService', 'ExtraService'),

(uuid_generate_v4(), '2dac8d7e-32c4-4a40-9e69-0422f8beb505', 'DomainTransfer', 'DomainTransfer'),

(uuid_generate_v4(), '2dac8d7e-32c4-4a40-9e69-0422f8beb505', 'DomainOwnerChange', 'DomainOwnerChange'),

(uuid_generate_v4(), '2dac8d7e-32c4-4a40-9e69-0422f8beb505', 'DomainRedemptionRestore', 'DomainRedemptionRestore'),

(uuid_generate_v4(), '2dac8d7e-32c4-4a40-9e69-0422f8beb505', 'VPS', 'VPS'),

(uuid_generate_v4(), '2dac8d7e-32c4-4a40-9e69-0422f8beb505', 'Cloud', 'Cloud'),

(uuid_generate_v4(), '2dac8d7e-32c4-4a40-9e69-0422f8beb505', 'VDC', 'VDC'),

(uuid_generate_v4(), '2dac8d7e-32c4-4a40-9e69-0422f8beb505', 'SSL', 'SSL'),

(uuid_generate_v4(), '2dac8d7e-32c4-4a40-9e69-0422f8beb505', 'PrepaidCredit', 'PrepaidCredit'),

(uuid_generate_v4(), '2dac8d7e-32c4-4a40-9e69-0422f8beb505', 'Billing', 'Billing'),

(uuid_generate_v4(), '2dac8d7e-32c4-4a40-9e69-0422f8beb505', 'Campaign', 'Campaign');

NOTE: Results of all queries for PostgreSQL are the same as for SQL queries.