pixiGetCountriesInvoice · Pixi API documentation

Collects the country of the address linked to each invoice and returns each country once, with its code and name.

Every invoice in Invoices is joined to its address in Addresses via AddrRef = AddrKey. Invoices without a matching address are left out.
The country value of the address is looked up in the countries table by CntCode.
The result is deduplicated (DISTINCT) and sorted by CntName ascending.

The call has no parameters and always covers all invoices in the database.

Notes

The lookup in countries is a LEFT JOIN. If an address contains a country value that does not exist in countries (or is empty/NULL), a row with CntCode and CntName both NULL is returned. Because of the ascending sort on CntName, this row appears first.
The unmatched country value itself is not returned, only the NULL columns.
Invoices and Addresses are read with NOLOCK (dirty reads possible); countries is read without a locking hint.
The query scans all invoices; on large databases the call can take some time.

Tables used

Invoices · Read Addresses · Read countries · Read

This call has no parameters.

Result set 1 · Always

One row per distinct country found in invoice addresses, sorted by CntName ascending.

Column	Type	Description
`CntCode` Nullable	string	Country code from `countries`. NULL if the address country is not found in `countries`.
`CntName` Nullable	string	Country name from `countries`. NULL if the address country is not found in `countries`.

Response example

<SqlRowSet1>
  <row>
    <CntCode>AT</CntCode>
    <CntName>Austria</CntName>
  </row>
  <row>
    <CntCode>DE</CntCode>
    <CntName>Germany</CntName>
  </row>
</SqlRowSet1>

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:pixiGetCountriesInvoice/>
  </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:pixiGetCountriesInvoice/>
  </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 = [
];

$soapArguments = [];
foreach ($arguments as $name => $value) {
    $soapArguments[] = new SoapVar($value, XSD_STRING, '', '', $name);
}

$response = $soapClient->__soapCall('pixiGetCountriesInvoice', $soapArguments);