Gladir.com - DB2 - Gouvernement - Taxe de vente canadienne

DB2   Oracle   Sybase

Le Canada est sans doute l'un des pays ou les taxes de vente sur les produits et services sont les plus compliqués à appliquer. Et pour cause: Elle varie d'une province ou territoire à l'autre, elle est parfois composé ou séparé. Voici leur représentation en date du mois septembre de l'années 2006:
Province/Territoire Taxe 1 Taxe 2 Taux
Alberta TVH: 6% Aucune 6%
Colombie-Britanique TVH: 6% TVP: 7% 13%
Ile-du-Prince-Édouard TPS: 6% TVP: 10% 16.6%
Manitoba TPS: 6% TVP: 7% 13%
Nouveau-Brunswick TVH: 14% Aucune 14%
Nouvelle-Écosse TVH: 14% Aucune 14%
Nunavut TVH: 6% Aucune 6%
Ontario TPS: 6% TVP: 8% 14%
Québec TPS: 6% TVQ: 7.5% 13.95%
Saskatchewan TPS: 6% TVP: 7% 13%
Terre-Neuve/Labrador TVH: 14% Aucune 14%
Territoire du Nord Ouest TVH: 6% Aucune 6%
Territoire du Yukon TVH: 6% Aucune 6%
Et que vous avez entrée les données suivantes:
CREATE TABLE ADMINISTRATOR.cdmtax (
 Cin   CHAR(3) DEFAULT '000' not null, 
 TaxId    BIGINT           not null, 
 Compute  CHAR(1),
 Status    CHAR(1),
 CountryId  BIGINT,
 RegionId  BIGINT,
 TaxName1    VARCHAR(255),
 TaxRate1  FLOAT,
 TaxName2     VARCHAR(255),
 TaxRate2  FLOAT, 
 TaxName3     VARCHAR(255),
 TaxRate3  FLOAT, 
 Description     CLOB,
 CreateDate      CHAR(10),
 CreateUserId  CHAR(20),
 LastUpdate      CHAR(10),
 UpdateUserId  CHAR(20),
 
 CONSTRAINT PKTAX PRIMARY KEY (Cin, TaxId)
);

Insert Into cdmtax(Cin,TaxId,Compute,Status,CountryId,RegionId,TaxName1,TaxRate1,TaxName2,TaxRate2,TaxName3,TaxRate3,Description,CreateDate,CreateUserId,LastUpdate,UpdateUserId) Values ('000',64256,'C','A',10851,11066,'TPS',6.0000,'TVQ',7.5000,'',0.0000,'','2006/06/14','GLADIR','2007/01/04','GLADIR');
Insert Into cdmtax(Cin,TaxId,Compute,Status,CountryId,RegionId,TaxName1,TaxRate1,TaxName2,TaxRate2,TaxName3,TaxRate3,Description,CreateDate,CreateUserId,LastUpdate,UpdateUserId) Values ('000',64257,'C','A',10851,11062,'TVH',14.0000,'',0.0000,'',0.0000,'','2006/06/14','GLADIR','2007/01/04','GLADIR');
Insert Into cdmtax(Cin,TaxId,Compute,Status,CountryId,RegionId,TaxName1,TaxRate1,TaxName2,TaxRate2,TaxName3,TaxRate3,Description,CreateDate,CreateUserId,LastUpdate,UpdateUserId) Values ('000',64258,'C','A',10851,11060,'TVH',14.0000,'',0.0000,'',0.0000,'','2006/06/14','GLADIR','2007/01/04','GLADIR');
Insert Into cdmtax(Cin,TaxId,Compute,Status,CountryId,RegionId,TaxName1,TaxRate1,TaxName2,TaxRate2,TaxName3,TaxRate3,Description,CreateDate,CreateUserId,LastUpdate,UpdateUserId) Values ('000',64259,'C','A',10851,11061,'TVH',14.0000,'',0.0000,'',0.0000,'','2006/06/14','GLADIR','2007/01/04','GLADIR');
Insert Into cdmtax(Cin,TaxId,Compute,Status,CountryId,RegionId,TaxName1,TaxRate1,TaxName2,TaxRate2,TaxName3,TaxRate3,Description,CreateDate,CreateUserId,LastUpdate,UpdateUserId) Values ('000',64260,'S','A',10851,11058,'TPS',6.0000,'TVP',7.0000,'',0.0000,'','2006/06/14','GLADIR','2007/01/04','GLADIR');
Insert Into cdmtax(Cin,TaxId,Compute,Status,CountryId,RegionId,TaxName1,TaxRate1,TaxName2,TaxRate2,TaxName3,TaxRate3,Description,CreateDate,CreateUserId,LastUpdate,UpdateUserId) Values ('000',64261,'C','A',10851,11057,'TVH',6.0000,'',0.0000,'',0.0000,'','2006/06/14','GLADIR','2007/01/04','GLADIR');
Insert Into cdmtax(Cin,TaxId,Compute,Status,CountryId,RegionId,TaxName1,TaxRate1,TaxName2,TaxRate2,TaxName3,TaxRate3,Description,CreateDate,CreateUserId,LastUpdate,UpdateUserId) Values ('000',64262,'S','A',10851,11059,'TPS',6.0000,'TVP',7.0000,'',0.0000,'','2006/06/14','GLADIR','2007/01/04','GLADIR');
Insert Into cdmtax(Cin,TaxId,Compute,Status,CountryId,RegionId,TaxName1,TaxRate1,TaxName2,TaxRate2,TaxName3,TaxRate3,Description,CreateDate,CreateUserId,LastUpdate,UpdateUserId) Values ('000',64263,'S','A',10851,11068,'TVH',6.0000,'',0.0000,'',0.0000,'','2006/06/14','GLADIR','2007/01/04','GLADIR');
Insert Into cdmtax(Cin,TaxId,Compute,Status,CountryId,RegionId,TaxName1,TaxRate1,TaxName2,TaxRate2,TaxName3,TaxRate3,Description,CreateDate,CreateUserId,LastUpdate,UpdateUserId) Values ('000',64264,'S','A',10851,11063,'TVH',6.0000,'',0.0000,'',0.0000,'','2006/06/14','GLADIR','2007/01/04','GLADIR');
Insert Into cdmtax(Cin,TaxId,Compute,Status,CountryId,RegionId,TaxName1,TaxRate1,TaxName2,TaxRate2,TaxName3,TaxRate3,Description,CreateDate,CreateUserId,LastUpdate,UpdateUserId) Values ('000',64265,'S','A',10851,11064,'TPS',6.0000,'TVP',8.0000,'',0.0000,'','2006/06/14','GLADIR','2007/01/04','GLADIR');
Insert Into cdmtax(Cin,TaxId,Compute,Status,CountryId,RegionId,TaxName1,TaxRate1,TaxName2,TaxRate2,TaxName3,TaxRate3,Description,CreateDate,CreateUserId,LastUpdate,UpdateUserId) Values ('000',64266,'C','A',10851,11065,'TPS',6.0000,'TVP',10.0000,'',0.0000,'','2006/06/14','GLADIR','2007/01/04','GLADIR');
Insert Into cdmtax(Cin,TaxId,Compute,Status,CountryId,RegionId,TaxName1,TaxRate1,TaxName2,TaxRate2,TaxName3,TaxRate3,Description,CreateDate,CreateUserId,LastUpdate,UpdateUserId) Values ('000',64267,'S','A',10851,11067,'TPS',6.0000,'TVP',7.0000,'',0.0000,'','2006/06/14','GLADIR','2007/01/04','GLADIR');
Insert Into cdmtax(Cin,TaxId,Compute,Status,CountryId,RegionId,TaxName1,TaxRate1,TaxName2,TaxRate2,TaxName3,TaxRate3,Description,CreateDate,CreateUserId,LastUpdate,UpdateUserId) Values ('000',64268,'S','A',10851,11069,'TVH',6.0000,'',0.0000,'',0.0000,'','2006/06/14','GLADIR','2007/01/04','GLADIR');
Insert Into cdmtax(Cin,TaxId,Compute,Status,CountryId,RegionId,TaxName1,TaxRate1,TaxName2,TaxRate2,TaxName3,TaxRate3,Description,CreateDate,CreateUserId,LastUpdate,UpdateUserId) Values ('000',64585,'S','A',10887,0,'TVA',19.6000,'',0.0000,'',0.0000,'','2006/06/19','GLADIR','2006/06/19','GLADIR');

CREATE TABLE ADMINISTRATOR.cdmcountryregion(
 CountryId  BIGINT       not null, 
 RegionId  BIGINT       not null, 
 MID         CHARACTER(10),
 Name_EN     VARCHAR(255),
 Name_FR     VARCHAR(255),
 Description     CLOB,
 Status  CHARACTER(1),
 CreateDate      CHARACTER(10),
 CreateUserId  CHARACTER(20),
 LastUpdate      CHARACTER(10),
 UpdateUserId  CHARACTER(20),

 CONSTRAINT PKCOUNTRYREGION PRIMARY KEY (CountryId,RegionId)
);

Insert Into cdmcountryregion(CountryId,RegionId,MID,Name_EN,Name_FR,Description,Status,CreateDate,CreateUserId,LastUpdate,UpdateUserId) Values (0000010851,0000011057,'AB','Alberta','Alberta','','A','2005/08/18','GLADIR','2005/08/22','GLADIR');
Insert Into cdmcountryregion(CountryId,RegionId,MID,Name_EN,Name_FR,Description,Status,CreateDate,CreateUserId,LastUpdate,UpdateUserId) Values (0000010851,0000011058,'BC','British Columbia','Colombie-Britanique','','A','2005/08/18','GLADIR','2005/08/22','GLADIR');
Insert Into cdmcountryregion(CountryId,RegionId,MID,Name_EN,Name_FR,Description,Status,CreateDate,CreateUserId,LastUpdate,UpdateUserId) Values (0000010851,0000011059,'MB','Manitoba','Manitoba','','A','2005/08/19','GLADIR','2005/08/22','GLADIR');
Insert Into cdmcountryregion(CountryId,RegionId,MID,Name_EN,Name_FR,Description,Status,CreateDate,CreateUserId,LastUpdate,UpdateUserId) Values (0000010851,0000011060,'NB','New Brunswick','Nouveau-Brunswick','','A','2005/08/19','GLADIR','2005/08/22','GLADIR');
Insert Into cdmcountryregion(CountryId,RegionId,MID,Name_EN,Name_FR,Description,Status,CreateDate,CreateUserId,LastUpdate,UpdateUserId) Values (0000010851,0000011061,'NF','Newfoundland','Terre-Neuve/Labrador','','A','2005/08/19','GLADIR','2005/08/22','GLADIR');
Insert Into cdmcountryregion(CountryId,RegionId,MID,Name_EN,Name_FR,Description,Status,CreateDate,CreateUserId,LastUpdate,UpdateUserId) Values (0000010851,0000011062,'NS','Nova Scotia','Nouvelle-Écosse','','A','2005/08/19','GLADIR','2005/08/22','GLADIR');
Insert Into cdmcountryregion(CountryId,RegionId,MID,Name_EN,Name_FR,Description,Status,CreateDate,CreateUserId,LastUpdate,UpdateUserId) Values (0000010851,0000011063,'NU','Nunavut','Nunavut','','A','2005/08/19','GLADIR','2005/08/22','GLADIR');
Insert Into cdmcountryregion(CountryId,RegionId,MID,Name_EN,Name_FR,Description,Status,CreateDate,CreateUserId,LastUpdate,UpdateUserId) Values (0000010851,0000011064,'ON','Ontario','Ontario','','A','2005/08/19','GLADIR','2005/08/22','GLADIR');
Insert Into cdmcountryregion(CountryId,RegionId,MID,Name_EN,Name_FR,Description,Status,CreateDate,CreateUserId,LastUpdate,UpdateUserId) Values (0000010851,0000011065,'PE','Prince Edward Island','Ile-du-Prince-Édouard','','A','2005/08/19','GLADIR','2005/08/22','GLADIR');
Insert Into cdmcountryregion(CountryId,RegionId,MID,Name_EN,Name_FR,Description,Status,CreateDate,CreateUserId,LastUpdate,UpdateUserId) Values (0000010851,0000011066,'QC','Quebec','Québec','','A','2005/08/19','GLADIR','2005/08/22','GLADIR');
Insert Into cdmcountryregion(CountryId,RegionId,MID,Name_EN,Name_FR,Description,Status,CreateDate,CreateUserId,LastUpdate,UpdateUserId) Values (0000010851,0000011067,'SK','Saskatchewan','Saskatchewan','','A','2005/08/19','GLADIR','2005/08/22','GLADIR');
Insert Into cdmcountryregion(CountryId,RegionId,MID,Name_EN,Name_FR,Description,Status,CreateDate,CreateUserId,LastUpdate,UpdateUserId) Values (0000010851,0000011068,'NT','Northwest Territories','Territoire du Nord Ouest','','A','2005/08/19','GLADIR','2005/08/22','GLADIR');
Insert Into cdmcountryregion(CountryId,RegionId,MID,Name_EN,Name_FR,Description,Status,CreateDate,CreateUserId,LastUpdate,UpdateUserId) Values (0000010851,0000011069,'YT','Yukon Territory','Territoire du Yukon','','A','2005/08/19','GLADIR','2005/08/22','GLADIR');
Tout d'abord, voici un premier exemple permet d'afficher des données complètes sur les taxes en Oracle:
SELECT  
 cdmcountryregion.Name_FR As RegionName,
 cdmcountryregion.MID As MID,
 cdmtax.TaxName1,cdmtax.TaxRate1,
 cdmtax.TaxRate2,
  CASE WHEN Compute = 'S' THEN 
   cdmtax.TaxRate1+cdmtax.TaxRate2
  ELSE 
  cdmtax.TaxRate1+((cdmtax.TaxRate2*(100.0+cdmtax.TaxRate1))/100.0)
  END As Taux
FROM cdmtax
LEFT JOIN cdmcountryregion ON cdmcountryregion.CountryId=cdmtax.CountryId and cdmcountryregion.RegionId=cdmtax.RegionId;
on obtiendra le résultat suivant:
Nom de la province/territoireMIDTaxe 1Taxe 2Taux
QuébecQCTPS:6%TVQ:7.5%13.95%
Nouvelle-ÉcosseNSTVH:14% 14%
Nouveau-BrunswickNBTVH:14% 14%
Terre-Neuve/LabradorNFTVH:14% 14%
Colombie-BritaniqueBCTPS:6%TVP:7%13%
AlbertaABTVH:6% 6%
ManitobaMBTPS:6%TVP:7%13%
Territoire du Nord OuestNTTVH:6% 6%
NunavutNUTVH:6% 6%
OntarioONTPS:6%TVP:8%14%
Ile-du-Prince-ÉdouardPETPS:6%TVP:10%16.6%
SaskatchewanSKTPS:6%TVP:7%13%
Territoire du YukonYTTVH:6% 6%


Dernière mise à jour: Mercredi, le 24 janvier 2006