[Sommaire] [Imprimer]

   1  <html>
   2  
   3  <head>
   4  <meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
   5  <meta name="GENERATOR" content="Microsoft FrontPage 4.0">
   6  <meta name="ProgId" content="FrontPage.Editor.Document">
   7  <title>New Page 1</title>
   8  </head>
   9  
  10  <body>
  11  
  12  <h1 align="center">Sales tax concept for The Exchange Project</h1>
  13  <h3 align="center">Author: Mike Bradley<br>
  14  Date: 16 July 2001</h3>
  15  <h2>1. Introduction</h2>
  16  <p>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.</p>
  17  <p>Each geographical tax region contains a group of country regions ('zones' in
  18  TEP), countries, states, counties or other defined zones.</p>
  19  <p>&nbsp;</p>
  20  <h2>2. Tax zones, Tax classes and Tax Rates</h2>
  21  <ul>
  22    <li>Tax zone - a geographical region consisting of one or more countries or
  23      regions to which a particular set of tax rules apply.</li>
  24    <li>Tax class - a class of goods to which certain tax rules apply</li>
  25    <li>Tax rate - a percentage rate of tax that applies to a particular class of
  26      goods within a particular tax zone</li>
  27  </ul>
  28  <p>&nbsp;</p>
  29  <h2>3. Compound taxation and tax zone priority</h2>
  30  <p>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.</p>
  31  <p>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.</p>
  32  <p>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.</p>
  33  <p>&nbsp;</p>
  34  <h2>4. Example tax scenarios</h2>
  35  <h3>4.1 US State tax</h3>
  36  <p>A company based in Florida charges sales tax only to residents of
  37  Florida.&nbsp; To do this, we must create a tax zone for Florida</p>
  38  <p><b>SQL</b></p>
  39  <p><font face="Courier New" size="1">DELETE FROM geo_zones;<br>
  40  DELETE FROM zones_to_geo_zones ;<br>
  41  DELETE FROM tax_rates;<br>
  42  <br>
  43  INSERT INTO geo_zones (geo_zone_id,geo_zone_name,geo_zone_description,date_added)&nbsp;<br>
  44  &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; VALUES (1,&quot;Florida&quot;,&quot;Florida
  45  local sales tax zone&quot;,now());<br>
  46  <br>
  47  INSERT INTO zones_to_geo_zones (association_id,zone_country_id,zone_id,geo_zone_id,date_added)&nbsp;<br>
  48  &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; VALUES (1,223,18,1,now()); # USA/Florida<br>

  49  <br>
  50  INSERT INTO tax_rates ( tax_rates_id, tax_zone_id, tax_class_id, tax_rate, tax_description, last_modified, date_added)<br>
  51  &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; VALUES (1, 1, 1, 7.0, 'FL TAX 7.0%', now(), now());<br>
  52  </font></p>
  53  <h3>4.2 European Union VAT</h3>
  54  <p>Consider a company that is VAT registered within the UK.&nbsp; The company is
  55  required to charge a rate of 17.5% VAT to all customers who are resident within
  56  the European Union and zero for non-EU residents.&nbsp; We create a single tax
  57  zone for the European Union, to which we add the list of all the countries in
  58  the EU.</p>
  59  <p>Then we create a tax rate for taxable goods and associate it with the
  60  European Union tax zone.</p>
  61  <p><b>SQL</b></p>
  62  <p><font face="Courier New" size="1">DELETE FROM geo_zones;<br>
  63  DELETE FROM zones_to_geo_zones;<br>
  64  DELETE FROM tax_rates;<br>
  65  <br>
  66  INSERT INTO geo_zones (geo_zone_id,geo_zone_name,geo_zone_description,date_added) VALUES (1,"European Union","EU VAT Zone",now());<br>
  67  <br>
  68  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<br>

  69  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<br>

  70  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<br>

  71  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<br>

  72  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<br>

  73  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<br>

  74  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<br>

  75  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<br>

  76  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<br>

  77  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<br>

  78  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<br>

  79  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<br>

  80  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<br>

  81  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<br>

  82  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<br>

  83  <br>
  84  INSERT INTO tax_rates ( tax_rates_id, tax_zone_id, tax_class_id, tax_rate, tax_description, last_modified, date_added)<br>
  85  &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; VALUES (1, 1, 1, 17.5, 'EU TAX 17.5%', now(), now());</font><br>
  86  </p>
  87  <h3>4.3 Compound tax (multiplicative)</h3>
  88  <p>A retailer is based in Canada and charges 7% sales tax to its Canadian
  89  customers, however because the company is based in Quebec, it also charges an
  90  additional 7.5% for customers who are also based in Quebec.&nbsp; This local tax
  91  is compounded on the federal tax by first calculating the federal tax, then
  92  applying the local tax, giving a total tax rate of 15.025% for Quebec customers.</p>
  93  <p>This arrangement is achieved by creating two tax zones, one for Canada and
  94  one for Quebec.&nbsp; Within the 'Canada' tax zone is simply the whole country
  95  of Canada (represented by Canada/* in the admin page) and within the Quebec tax

  96  zone is the Canada/Quebec geographical zone.&nbsp; Because the Federal tax is

  97  applied first, and the Quebec tax applied to the result, we need to set the

  98  Canada tax rate with a priority of 1 and Quebec with a separate priority of 2.</p>

  99  <p>Two tax rates are created, one for Canada (7%) and one for Quebec (7.5%).</p>

 100  <p><b>Note</b>:<b> </b>in a country where the tax rules are such that both tax

 101  rates are calculated on the original goods value instead of one tax compounding

 102  on the other, then this could be achieved by setting both tax zones to the same

 103  priority.&nbsp; In this instance, the compounding is additive, and the final tax

 104  rate would be 14.5%.</p>

 105  <p><b>SQL</b></p>

 106  <p><font face="Courier New" size="1">DELETE FROM geo_zones;<br>

 107  DELETE FROM zones_to_geo_zones;<br>

 108  DELETE FROM tax_rates;<br>

 109  <br>

 110  INSERT INTO geo_zones (geo_zone_id,geo_zone_name,geo_zone_description,date_added)&nbsp;<br>

 111  &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; VALUES (1,"Canada","Canadian Federal Tax

 112  Zone&quot;,now());<br>

 113  INSERT INTO geo_zones (geo_zone_id,geo_zone_name,geo_zone_description,date_added)&nbsp;<br>

 114  &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; VALUES (2,"Quebec","Quebec Local Tax

 115  Zone&quot;,now());<br>

 116  <br>

 117  INSERT INTO zones_to_geo_zones VALUES (1,38,0,1,now(),now());<br>

 118  INSERT INTO zones_to_geo_zones VALUES (2,38,76,2,now(),now());<br>

 119  <br>

 120  INSERT INTO tax_rates (tax_rates_id, tax_zone_id, tax_class_id, tax_priority,

 121  tax_rate, tax_description, last_modified, date_added)<br>

 122  &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; VALUES (1, 1, 1, 1, 7.0, 'Canada 7%', now(), now());<br>

 123  INSERT INTO tax_rates ( tax_rates_id, tax_zone_id, tax_class_id, tax_priority,tax_rate, tax_description, last_modified, date_added)<br>

 124  &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; VALUES (2, 2, 1, 2, 7.5, 'Quebec 7.5%', now(), now());<br>

 125  </font></p>

 126  <h2>5. New table definitions</h2>

 127  <p>Two new tables are needed for the sales tax scheme.&nbsp; The <b>geo_zones</b>

 128  table contains the names of the geographical zones and their calculation

 129  priorities.&nbsp; The <b>zones_to_geo_zones</b> table contains the list of

 130  countries and/or country regions ('zones' in TEP) assigned to each geographical zone.&nbsp;

 131  No specialisation towards tax schemes is made in these tables - this is with the

 132  intention of allowing geo zones to be used for other purposes, for example

 133  shipping zones.</p>

 134  <p>Note that the meaning of <b>tax_z</b><b>one_id</b> in table <b>tax_rates</b> has now

 135  changed.&nbsp; Instead of pointing to a single country zone (region) in the

 136  <b>zones</b> table, it now points to the more generalised geographical zone in the <b>zones_to_geo_zones</b>

 137  and <b>geo_z</b><b>ones</b> tables.</p>

 138  <p><font face="Courier New" size="1">CREATE TABLE zones_to_geo_zones (<br>

 139     association_id int(5) NOT NULL auto_increment,<br>

 140     zone_country_id int(5) NOT NULL,<br>

 141     zone_id int(5) NULL,<br>

 142  geo_zone_id int(5) NULL,<br>

 143     last_modified datetime NULL,<br>

 144     date_added datetime NOT NULL,<br>

 145     PRIMARY KEY (association_id)<br>

 146  );<br>

 147  <br>

 148  CREATE TABLE geo_zones (<br>

 149  geo_zone_id int(5) NOT NULL auto_increment,<br>

 150  geo_zone_name varchar(32) NOT NULL,<br>

 151  geo_zone_description varchar(255) NOT NULL,<br>

 152    last_modified datetime NULL,<br>

 153    date_added datetime NOT NULL,<br>

 154    PRIMARY KEY (geo_zone_id)<br>

 155  );</font></p>

 156  

 157  <p><font face="Courier New" size="1">

 158  ALTER TABLE tax_rates ADD COLUMN tax_priority int(5) NOT NULL DEFAULT 1 after

 159  tax_class_id;</font></p>

 160  

 161  <h2>5. Migrating from previous versions</h2>

 162  <p>It is usually easy enough to set up the tax zones from scratch, however the

 163  following script may be useful if upgrading the database to the new scheme:</p>

 164  <p><font face="Courier New" size="1">INSERT INTO geo_zones (geo_zone_id,geo_zone_name,geo_zone_description,last_modified,date_added)&nbsp;<br>

 165      SELECT tr.tax_zone_id,zone_name,zone_name,NULL,now() from tax_rates tr,zones z,countries c&nbsp;<br>

 166      WHERE tr.tax_zone_id=z.zone_id AND c.countries_id=z.zone_country_id&nbsp;<br>

 167      GROUP BY tr.tax_zone_id;<br>

 168  <br>

 169  INSERT INTO zones_to_geo_zones (zone_country_id,zone_id,geo_zone_id,date_added)&nbsp;<br>

 170      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<br>

 171      GROUP BY tr.tax_zone_id;<br>

 172  </font></p>

 173  

 174  </body>

 175  

 176  </html>