Gladir.com - Oracle Database SQL - 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 cdmtax (
 Cin   CHAR(3) DEFAULT '000' not null, 
 TaxId    NUMERIC(10)           not null, 
 Compute  CHAR(1),
 Status    CHAR(1),
 CountryId  NUMERIC(10),
 RegionId  NUMERIC(10),
 TaxName1    VARCHAR2(255),
 TaxRate1  FLOAT,
 TaxName2     VARCHAR2(255),
 TaxRate2  FLOAT, 
 TaxName3     VARCHAR2(255),
 TaxRate3  FLOAT, 
 Description     VARCHAR(4000),
 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 cdmcountryregion(
 CountryId  NUMERIC(10)       not null, 
 RegionId  NUMERIC(10)        not null, 
 MID         CHAR(10),
 Name_EN     VARCHAR2(255),
 Name_FR     VARCHAR2(255),
 Description     VARCHAR2(4000),
 Status  CHAR(1),
 CreateDate      CHAR(10),
 CreateUserId  CHAR(20),
 LastUpdate      CHAR(10),
 UpdateUserId  CHAR(20),

 CONSTRAINT pkcdmcountryregion 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 
   Cast((cdmtax.TaxRate1+cdmtax.TaxRate2) As VARCHAR(255))
  ELSE 
  Cast(cdmtax.TaxRate1+((cdmtax.TaxRate2*(100.0+cdmtax.TaxRate1))/100.0) As VARCHAR(255))
  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%


Voir également

Article - Les géants de l'informatique - Oracle

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