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ée 2006:
| Province/Territoire | Taxe 1 | Taxe 2 | Taux |
|---|---|---|---|
| Alberta | TVH: 6% | Aucune | 6% |
| Colombie-Britannique | 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/territoire | MID | Taxe 1 | Taxe 2 | Taux |
| Québec | QC | TPS:6% | TVQ:7.5% | 13.95% |
| Nouvelle-Écosse | NS | TVH:14% | 14% | |
| Nouveau-Brunswick | NB | TVH:14% | 14% | |
| Terre-Neuve/Labrador | NF | TVH:14% | 14% | |
| Colombie-Britanique | BC | TPS:6% | TVP:7% | 13% |
| Alberta | AB | TVH:6% | 6% | |
| Manitoba | MB | TPS:6% | TVP:7% | 13% |
| Territoire du Nord Ouest | NT | TVH:6% | 6% | |
| Nunavut | NU | TVH:6% | 6% | |
| Ontario | ON | TPS:6% | TVP:8% | 14% |
| Ile-du-Prince-Édouard | PE | TPS:6% | TVP:10% | 16.6% |
| Saskatchewan | SK | TPS:6% | TVP:7% | 13% |
| Territoire du Yukon | YT | TVH:6% | 6% |
Voir également
Article - Les géants de l'informatique - Oracle
Dernière mise à jour : Mercredi, le 24 janvier 2006