The method of calculating sales tax is designed to enable a wide range of tax scenarios. It is based around 'tax zones' - assigned regions within which certain tax rules apply.
Each geographical tax region contains a group of country regions ('zones' in TEP), countries, states, counties or other defined zones.
Certain tax rules require several rates of tax to be compounded depending on the location of the buyer. For example residents of a country may pay a national rate of sales tax, but residents of a particular state might pay local tax in addition to the national tax rate.
This can be achieved by creating multiple tax zones, one for the whole country and a second zone for the local region. Each of these zones can be assigned a separate rate of taxation for each class of goods.
By default, the tax rates are simply added together to form the final tax rate for the sale. However in certain circumstances, the tax rates need to be multiplied - in these instances, the tax zone priority is used to determine which rate to apply first.
A company based in Florida charges sales tax only to residents of Florida. To do this, we must create a tax zone for Florida
SQL
DELETE FROM geo_zones;
DELETE FROM zones_to_geo_zones ;
DELETE FROM tax_rates;
INSERT INTO geo_zones (geo_zone_id,geo_zone_name,geo_zone_description,date_added)
VALUES (1,"Florida","Florida
local sales tax zone",now());
INSERT INTO zones_to_geo_zones (association_id,zone_country_id,zone_id,geo_zone_id,date_added)
VALUES (1,223,18,1,now()); # USA/Florida
INSERT INTO tax_rates ( tax_rates_id, tax_zone_id, tax_class_id, tax_rate, tax_description, last_modified, date_added)
VALUES (1, 1, 1, 7.0, 'FL TAX 7.0%', now(), now());
Consider a company that is VAT registered within the UK. The company is required to charge a rate of 17.5% VAT to all customers who are resident within the European Union and zero for non-EU residents. We create a single tax zone for the European Union, to which we add the list of all the countries in the EU.
Then we create a tax rate for taxable goods and associate it with the European Union tax zone.
SQL
DELETE FROM geo_zones;
DELETE FROM zones_to_geo_zones;
DELETE FROM tax_rates;
INSERT INTO geo_zones (geo_zone_id,geo_zone_name,geo_zone_description,date_added) VALUES (1,"European Union","EU VAT Zone",now());
INSERT INTO zones_to_geo_zones (association_id,zone_country_id,zone_id,geo_zone_id,date_added) VALUES (1,222,NULL,1,now()); #UK
INSERT INTO zones_to_geo_zones (association_id,zone_country_id,zone_id,geo_zone_id,date_added) VALUES (2,81,NULL,1,now()); #Germany
INSERT INTO zones_to_geo_zones (association_id,zone_country_id,zone_id,geo_zone_id,date_added) VALUES (3,73,NULL,1,now()); #France
INSERT INTO zones_to_geo_zones (association_id,zone_country_id,zone_id,geo_zone_id,date_added) VALUES (4,105,NULL,1,now()); #Italy
INSERT INTO zones_to_geo_zones (association_id,zone_country_id,zone_id,geo_zone_id,date_added) VALUES (5,21,NULL,1,now()); #Belgium
INSERT INTO zones_to_geo_zones (association_id,zone_country_id,zone_id,geo_zone_id,date_added) VALUES (6,150,NULL,1,now()); #Holland
INSERT INTO zones_to_geo_zones (association_id,zone_country_id,zone_id,geo_zone_id,date_added) VALUES (7,195,NULL,1,now()); #Spain
INSERT INTO zones_to_geo_zones (association_id,zone_country_id,zone_id,geo_zone_id,date_added) VALUES (8,203,NULL,1,now()); #Sweden
INSERT INTO zones_to_geo_zones (association_id,zone_country_id,zone_id,geo_zone_id,date_added) VALUES (9,72,NULL,1,now()); #Finland
INSERT INTO zones_to_geo_zones (association_id,zone_country_id,zone_id,geo_zone_id,date_added) VALUES (10,57,NULL,1,now()); #Denmark
INSERT INTO zones_to_geo_zones (association_id,zone_country_id,zone_id,geo_zone_id,date_added) VALUES (11,84,NULL,1,now()); #Greece
INSERT INTO zones_to_geo_zones (association_id,zone_country_id,zone_id,geo_zone_id,date_added) VALUES (12,171,NULL,1,now()); #Portugal
INSERT INTO zones_to_geo_zones (association_id,zone_country_id,zone_id,geo_zone_id,date_added) VALUES (13,103,NULL,1,now()); #Ireland
INSERT INTO zones_to_geo_zones (association_id,zone_country_id,zone_id,geo_zone_id,date_added) VALUES (14,124,NULL,1,now()); #Luxembourg
INSERT INTO zones_to_geo_zones (association_id,zone_country_id,zone_id,geo_zone_id,date_added) VALUES (15,14,NULL,1,now()); #Austria
INSERT INTO tax_rates ( tax_rates_id, tax_zone_id, tax_class_id, tax_rate, tax_description, last_modified, date_added)
VALUES (1, 1, 1, 17.5, 'EU TAX 17.5%', now(), now());
A retailer is based in Canada and charges 7% sales tax to its Canadian customers, however because the company is based in Quebec, it also charges an additional 7.5% for customers who are also based in Quebec. This local tax is compounded on the federal tax by first calculating the federal tax, then applying the local tax, giving a total tax rate of 15.025% for Quebec customers.
This arrangement is achieved by creating two tax zones, one for Canada and one for Quebec. Within the 'Canada' tax zone is simply the whole country of Canada (represented by Canada/* in the admin page) and within the Quebec tax zone is the Canada/Quebec geographical zone. Because the Federal tax is applied first, and the Quebec tax applied to the result, we need to set the Canada tax rate with a priority of 1 and Quebec with a separate priority of 2.
Two tax rates are created, one for Canada (7%) and one for Quebec (7.5%).
Note: in a country where the tax rules are such that both tax rates are calculated on the original goods value instead of one tax compounding on the other, then this could be achieved by setting both tax zones to the same priority. In this instance, the compounding is additive, and the final tax rate would be 14.5%.
SQL
DELETE FROM geo_zones;
DELETE FROM zones_to_geo_zones;
DELETE FROM tax_rates;
INSERT INTO geo_zones (geo_zone_id,geo_zone_name,geo_zone_description,date_added)
VALUES (1,"Canada","Canadian Federal Tax
Zone",now());
INSERT INTO geo_zones (geo_zone_id,geo_zone_name,geo_zone_description,date_added)
VALUES (2,"Quebec","Quebec Local Tax
Zone",now());
INSERT INTO zones_to_geo_zones VALUES (1,38,0,1,now(),now());
INSERT INTO zones_to_geo_zones VALUES (2,38,76,2,now(),now());
INSERT INTO tax_rates (tax_rates_id, tax_zone_id, tax_class_id, tax_priority,
tax_rate, tax_description, last_modified, date_added)
VALUES (1, 1, 1, 1, 7.0, 'Canada 7%', now(), now());
INSERT INTO tax_rates ( tax_rates_id, tax_zone_id, tax_class_id, tax_priority,tax_rate, tax_description, last_modified, date_added)
VALUES (2, 2, 1, 2, 7.5, 'Quebec 7.5%', now(), now());
Two new tables are needed for the sales tax scheme. The geo_zones table contains the names of the geographical zones and their calculation priorities. The zones_to_geo_zones table contains the list of countries and/or country regions ('zones' in TEP) assigned to each geographical zone. No specialisation towards tax schemes is made in these tables - this is with the intention of allowing geo zones to be used for other purposes, for example shipping zones.
Note that the meaning of tax_zone_id in table tax_rates has now changed. Instead of pointing to a single country zone (region) in the zones table, it now points to the more generalised geographical zone in the zones_to_geo_zones and geo_zones tables.
CREATE TABLE zones_to_geo_zones (
association_id int(5) NOT NULL auto_increment,
zone_country_id int(5) NOT NULL,
zone_id int(5) NULL,
geo_zone_id int(5) NULL,
last_modified datetime NULL,
date_added datetime NOT NULL,
PRIMARY KEY (association_id)
);
CREATE TABLE geo_zones (
geo_zone_id int(5) NOT NULL auto_increment,
geo_zone_name varchar(32) NOT NULL,
geo_zone_description varchar(255) NOT NULL,
last_modified datetime NULL,
date_added datetime NOT NULL,
PRIMARY KEY (geo_zone_id)
);
ALTER TABLE tax_rates ADD COLUMN tax_priority int(5) NOT NULL DEFAULT 1 after tax_class_id;
It is usually easy enough to set up the tax zones from scratch, however the following script may be useful if upgrading the database to the new scheme:
INSERT INTO geo_zones (geo_zone_id,geo_zone_name,geo_zone_description,last_modified,date_added)
SELECT tr.tax_zone_id,zone_name,zone_name,NULL,now() from tax_rates tr,zones z,countries c
WHERE tr.tax_zone_id=z.zone_id AND c.countries_id=z.zone_country_id
GROUP BY tr.tax_zone_id;
INSERT INTO zones_to_geo_zones (zone_country_id,zone_id,geo_zone_id,date_added)
SELECT z.zone_country_id, z.zone_id,tr.tax_zone_id,now() FROM tax_rates tr, zones z WHERE z.zone_id=tr.tax_zone_id
GROUP BY tr.tax_zone_id;