pixiFederalStatisticReport · Pixi API documentation

Builds a report of goods received from suppliers for the federal foreign-trade statistics.

Time span

If DateFrom is omitted, it defaults to the first day of the previous calendar month.
If DateTo is omitted, it defaults to the last day of the previous calendar month.
DateFrom is moved to the start of its day (00:00:00) and DateTo to the end of its day (23:59:59.997), so only the date part matters.
If DateTo is not after DateFrom, or the span covers more than 24 months, an error row is returned instead of the report.

Selection

Supplier deliveries (SDelHeader) with status INV (invoiced) or CLS (closed).
The delivery header's last update date (SDelHUpdateDate) must lie within the time span.
Only delivery lines with a delivered quantity greater than 0.
Optional filter on the supplier number SupplNR; it is trimmed, and an empty value means all suppliers.

Aggregation Rows are grouped by year and month of the update date, the supplier country (ISO2 code, N/A if the supplier has no known country), the Artikel's customs country of origin, customs tariff number, customs tariff text (falls back to the Artikel name if empty) and the supplier's home page URL. For each group the call returns the summed weight (Artikel weight × delivered quantity, cast to int), the summed delivered quantity and the summed amount (delivered quantity × final supplier price, as money).

The result is sorted by year, month and supplier country.

Notes

All tables are read WITH (NOLOCK); results may include uncommitted data.
Errors (including the parameter checks) are caught and returned as a row with Status = Error and Message, not as a SOAP fault.
The period is based on SDelHUpdateDate (last change of the delivery header), not on a separate receipt date. A later change to an old delivery moves it into a later period.
If only DateFrom is passed, DateTo still defaults to the end of the previous month; a DateFrom in the current month therefore leads to the error about DateTo being smaller.
The 24-month check uses DATEDIFF(MONTH, ...), which counts month boundaries, not full months.
The supplier number is not part of the grouping, but HomePageUrl is: several suppliers from the same country with different home page URLs produce separate rows.
Artikel without weight (NULL) do not contribute to DeliveredWeight. The weight is truncated to an integer after summing.
The helper util.TempTableDrop used to drop the temporary table is not attached.

Tables used

SDelHeader · Read SDelDetail · Read Items · Read Suppliers · Read Countries · Read

Parameter	Type	Default	Description
`DateFrom`	datetime dateTime	NULL	Start of the period (date part only, start of day). Default: first day of the previous month. Example: `2024-05-01T00:00:00`
`DateTo`	datetime dateTime	NULL	End of the period (date part only, end of day). Default: last day of the previous month. Must be after `DateFrom`; the span may not exceed 24 months. Example: `2024-05-31T00:00:00`
`SupplNR`	varchar(4) string	NULL	Optional supplier number to restrict the report. Trimmed; empty or NULL means all suppliers. Example: `S001`

Result set 1 · On success (valid time span)

Aggregated delivered quantities, weights and amounts per month, supplier country and customs data.

Column	Type	Description
`Year`	int	Year of the delivery header's last update date.
`Month`	int	Month (1–12) of the delivery header's last update date.
`SupplierCountry`	varchar(3) string	ISO2 code of the supplier's country, `N/A` if unknown.
`CustomsCountryOfOrigin`	string	Customs country of origin of the Artikel; empty string if not set.
`CustomsTariffNumber`	string	Customs tariff number of the Artikel; empty string if not set.
`CustomsTariffText` Nullable	string	Customs tariff text of the Artikel, or the Artikel name if the text is empty.
`DeliveredWeight` Nullable	int	Sum of Artikel weight × delivered quantity, cast to integer.
`DeliveredQty` Nullable	decimal	Sum of the delivered quantities.
`DeliveredAmount` Nullable	money decimal	Sum of delivered quantity × final supplier price.
`HomePageUrl` Nullable	string	Home page URL of the supplier.

Result set 2 · On error (invalid time span or any SQL error)

Status row with the error message.

Column	Type	Description
`Status`	varchar(5) string	Always `Error`.
`Message` Nullable	nvarchar(4000) string	Error message.

Response example

<SqlRowSet1>
  <row>
    <Year>2024</Year>
    <Month>5</Month>
    <SupplierCountry>CN</SupplierCountry>
    <CustomsCountryOfOrigin>CN</CustomsCountryOfOrigin>
    <CustomsTariffNumber>85176200</CustomsTariffNumber>
    <CustomsTariffText>Bluetooth headset</CustomsTariffText>
    <DeliveredWeight>48</DeliveredWeight>
    <DeliveredQty>240</DeliveredQty>
    <DeliveredAmount>3120.0000</DeliveredAmount>
    <HomePageUrl>www.example-supplier.cn</HomePageUrl>
  </row>
  <row>
    <Year>2024</Year>
    <Month>5</Month>
    <SupplierCountry>DE</SupplierCountry>
    <CustomsCountryOfOrigin>DE</CustomsCountryOfOrigin>
    <CustomsTariffNumber>49019900</CustomsTariffNumber>
    <CustomsTariffText>Printed books</CustomsTariffText>
    <DeliveredWeight>150</DeliveredWeight>
    <DeliveredQty>300</DeliveredQty>
    <DeliveredAmount>2850.0000</DeliveredAmount>
    <HomePageUrl>www.verlag-beispiel.de</HomePageUrl>
  </row>
</SqlRowSet1>

Status	Message	Cause
Error	DateTo parameter is smaller then DateFrom parameter.	`DateTo` (end of day) is not after `DateFrom` (start of day). Pass a `DateTo` on or after `DateFrom`; note that an omitted `DateTo` defaults to the end of the previous month.
Error	Time span of more than 24 months is not alowed.	The period spans more than 24 month boundaries. Split the request into shorter periods.

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:pixiFederalStatisticReport>
      <!-- <DateFrom>2024-05-01T00:00:00</DateFrom> -->
      <!-- <DateTo>2024-05-31T00:00:00</DateTo> -->
      <!-- <SupplNR>S001</SupplNR> -->
    </ns1:pixiFederalStatisticReport>
  </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:pixiFederalStatisticReport>
      <!-- <DateFrom>2024-05-01T00:00:00</DateFrom> -->
      <!-- <DateTo>2024-05-31T00:00:00</DateTo> -->
      <!-- <SupplNR>S001</SupplNR> -->
    </ns1:pixiFederalStatisticReport>
  </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 = [
    // 'DateFrom' => '2024-05-01T00:00:00',
    // 'DateTo' => '2024-05-31T00:00:00',
    // 'SupplNR' => 'S001',
];

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

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