pixiGetVATByCountries
Returns the high and low VAT rates per country that are valid at a given point in time, filtered by ISO country code and/or VAT charge flag.
Looks up VAT rates from the VAT master data (VATs) joined with the country master data (Countries) on VATs.Country = Countries.CntCode.
Parameter handling
- Empty strings in
CntCodeISO2,CntCodeISO3andChargeVATare treated as not specified (NULL). - A
Datetimeearlier than1990-01-01is replaced by the current date and time.
Validation
An error row is returned instead of data if Datetime is not specified, or if none of CntCodeISO2, CntCodeISO3 and ChargeVAT is specified. A date plus at least one further filter is therefore required.
Filtering
CntCodeISO2: only countries with this ISO 3166 alpha-2 code.CntCodeISO3: only countries with this ISO 3166 alpha-3 code.ChargeVAT: only VAT records with thisChargeVATvalue.Datetime: only VAT records whose validity period contains the date (DateStart<=Datetime<=DateEnd, both bounds inclusive).
All given filters are combined with AND. The result has no explicit sort order.
Notes
- Both tables are read with
NOLOCK, so uncommitted changes may be visible. - VAT records with a
NULLinDateStartorDateEndnever match, because the date comparison is not true forNULL. - Passing a very old date (before 1990) silently returns the currently valid rates instead of historical ones.
DateEndis compared inclusively including the time part; ifDateEndis stored as midnight, aDatetimelater on that day no longer matches.- The meaning of the
ChargeVATvalues is not defined in this procedure; it depends on the content of theVATstable. - Several rows per country can be returned, e.g. one per
ChargeVATvalue, if only a country code is given.
Tables used
| Parameter | Type | Default | Description |
|---|---|---|---|
CntCodeISO2
|
char(2) string |
NULL |
ISO 3166 alpha-2 country code to filter by. Empty string means no filter. Example: |
CntCodeISO3
|
char(3) string |
NULL |
ISO 3166 alpha-3 country code to filter by. Empty string means no filter. Example: |
ChargeVAT
|
char(1) string |
NULL |
Value of the |
Datetime
|
datetime dateTime |
NULL |
Point in time for which the VAT rates must be valid. Effectively mandatory: without it an error row is returned. Values before 1990-01-01 are replaced by the current date and time. Example: |
Result set 1 · If Datetime and at least one of CntCodeISO2, CntCodeISO3, ChargeVAT are specified
VAT rates valid at the given time, one row per matching VAT record.
| Column | Type | Description |
|---|---|---|
CntName
Nullable |
string |
Country name from the country master data. |
CntCodeISO2
Nullable |
string |
ISO 3166 alpha-2 country code. |
CntCodeISO3
Nullable |
string |
ISO 3166 alpha-3 country code. |
ChargeVAT
Nullable |
string |
|
PHigh
Nullable |
decimal |
High (standard) VAT rate. |
PLow
Nullable |
decimal |
Low (reduced) VAT rate. |
Result set 2 · If Datetime is missing or none of CntCodeISO2, CntCodeISO3, ChargeVAT is specified
Error status row.
| Column | Type | Description |
|---|---|---|
ReturnCode
|
varchar(5) string |
Always |
ErrorMsg
|
varchar(66) string |
Error text. |
Response example
<SqlRowSet1>
<row>
<CntName>Deutschland</CntName>
<CntCodeISO2>DE</CntCodeISO2>
<CntCodeISO3>DEU</CntCodeISO3>
<ChargeVAT>Y</ChargeVAT>
<PHigh>19.00</PHigh>
<PLow>7.00</PLow>
</row>
</SqlRowSet1>| Status | Message | Cause |
|---|---|---|
| ERROR | Please specify the datetime and at least one additional parameter | `Datetime` was not passed (or is NULL), or none of `CntCodeISO2`, `CntCodeISO3` and `ChargeVAT` was given (or all are empty). Pass a date and at least one filter. |
POST https://apigateway.descartes.com/tms/pixi/
Content-Type: text/xml; charset=utf-8
Authorization: Basic BASE64(USERNAME:PASSWORD)
<?xml version="1.0" encoding="UTF-8"?>
<SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" xmlns:ns1="https://apigateway.descartes.com/tms/pixi/">
<SOAP-ENV:Body>
<ns1:pixiGetVATByCountries>
<!-- <CntCodeISO2>DE</CntCodeISO2> -->
<!-- <CntCodeISO3>DEU</CntCodeISO3> -->
<!-- <ChargeVAT>?</ChargeVAT> -->
<!-- <Datetime>2024-03-15T12:00:00</Datetime> -->
</ns1:pixiGetVATByCountries>
</SOAP-ENV:Body>
</SOAP-ENV:Envelope>curl -X POST 'https://apigateway.descartes.com/tms/pixi/' \
--user 'USERNAME:PASSWORD' \
--header 'Content-Type: text/xml; charset=utf-8' \
--data-binary @- <<'XML'
<?xml version="1.0" encoding="UTF-8"?>
<SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" xmlns:ns1="https://apigateway.descartes.com/tms/pixi/">
<SOAP-ENV:Body>
<ns1:pixiGetVATByCountries>
<!-- <CntCodeISO2>DE</CntCodeISO2> -->
<!-- <CntCodeISO3>DEU</CntCodeISO3> -->
<!-- <ChargeVAT>?</ChargeVAT> -->
<!-- <Datetime>2024-03-15T12:00:00</Datetime> -->
</ns1:pixiGetVATByCountries>
</SOAP-ENV:Body>
</SOAP-ENV:Envelope>
XML<?php
$soapClient = new SoapClient(null, [
'login' => 'USERNAME',
'password' => 'PASSWORD',
'location' => 'https://apigateway.descartes.com/tms/pixi/',
'uri' => 'https://apigateway.descartes.com/tms/pixi/',
]);
$arguments = [
// 'CntCodeISO2' => 'DE',
// 'CntCodeISO3' => 'DEU',
// 'ChargeVAT' => '?',
// 'Datetime' => '2024-03-15T12:00:00',
];
$soapArguments = [];
foreach ($arguments as $name => $value) {
$soapArguments[] = new SoapVar($value, XSD_STRING, '', '', $name);
}
$response = $soapClient->__soapCall('pixiGetVATByCountries', $soapArguments);- SQL object:
- dbo.pipiGetVATByCountries