Customers Read

pixiGetCustomerList

Returns up to 10,000 customers with orders, starting at a customer key, with order, turnover, return and reminder statistics plus main address data.

Builds a customer list with key figures, driven by the orders in OrderHeader.

StartCustKey (NULL is treated as 0) selects the first customer. The call takes the first 10,000 distinct CustRef values from OrderHeader with CustRef >= StartCustKey, in ascending order, and counts their orders (OrderCount). Customers without any order are never listed.
For each of these customers it calculates:
- DaysSinceLastOrder / DaysSinceFirstOrder: days between today and the latest OrderDate in Orderlines of the order with the highest / lowest order number of the customer.
- From Invoices: Turnover_all (sum of invoices with a positive total), Turnover_avgPerOrder (average of positive invoice totals), ReturnCount (number of invoices with a negative total), ReturnValue (sum of negative totals) and ReminderLevel (highest reminder level).
- From Invlines: ItemCount_all, the sum of all positive ItemQty values.
Currency handling depends on the setting MultiCurrencyEnabled:
- not 1: amounts are summed in the original invoice currency, BaseCurrency is NULL.
- 1: each invoice total is divided by the exchange rate of the invoice (pixi.ExchRate), and BaseCurrency holds the ISO code of the base currency.
The result is joined with Customers and the customer's main address (Customers.AddrRef → Addresses) and sorted by CustKey ascending.

Paging: to read the next block, call again with StartCustKey = last returned CustKey + 1 (the filter is inclusive).

Notes

All permanent tables are read with NOLOCK; figures may include uncommitted data.
Without multi-currency, Turnover_all, Turnover_avgPerOrder and ReturnValue can mix different currencies if a customer has invoices in several currencies.
With multi-currency enabled, invoices without a matching exchange rate record are ignored. If a customer's invoices refer to more than one base currency, the unique index on the temporary invoice table fails and the call ends with a SQL error.
Turnover_avgPerOrder is the average per positive invoice, not per order.
Days since first/last order are based on the lowest/highest order number, not on the earliest/latest order date; they are NULL if that order has no lines.
The block of 10,000 is taken from OrderHeader before the join with Customers/Addresses; customers missing there are dropped, so a block can contain fewer than 10,000 rows even if more customers follow. Continue paging until an empty result is returned.
The address is the main address (AddrRef), not the shipping address.
The aggregation over all orders and invoices can be expensive on large databases.

Tables used

Settings · Read OrderHeader · Read Orderlines · Read Invoices · Read ExchRate · Read Currency · Read Invlines · Read Customers · Read Addresses · Read

Parameter	Type	Default	Description
`StartCustKey`	int	NULL	Lowest customer key (`CustKey`) to include (inclusive). NULL or omitted starts at 0. For paging pass the last returned `CustKey` + 1. Example: `10001`

Result set 1 · Always (empty if no customer with orders has a key >= `StartCustKey`)

One row per customer with orders, at most 10,000 rows, sorted by CustKey ascending.

Column	Type	Description
`CustKey`	int	Internal customer key.
`CustomerNrExternal` Nullable	string	External customer number (e.g. from the shop).
`OrderCount`	int	Number of orders of the customer in `OrderHeader`.
`Turnover_all` Nullable	money decimal	Sum of all invoice totals > 0 (converted to base currency if multi-currency is enabled).
`ItemCount_all` Nullable	decimal	Sum of all positive item quantities on invoice lines of the customer.
`Turnover_avgPerOrder` Nullable	money decimal	Average of invoice totals > 0.
`ReturnCount`	int	Number of invoices with a negative total (credit notes); 0 if none.
`ReturnValue` Nullable	money decimal	Sum of negative invoice totals (negative value).
`ReminderLevel` Nullable	int	Highest reminder level of all invoices of the customer.
`DaysSinceLastOrder` Nullable	int	Days since the order date of the order with the highest order number.
`DaysSinceFirstOrder` Nullable	int	Days since the order date of the order with the lowest order number.
`ShopID` Nullable	string	Shop the customer belongs to (`Customers.CustomerOf`).
`eMail` Nullable	string	E-mail address of the main address.
`Salutation` Nullable	string	Salutation (`Addresses.Anrede`).
`FirstName` Nullable	string	First name.
`LastName` Nullable	string	Last name.
`Name` Nullable	string	Name / company name of the address.
`CareOf` Nullable	string	Care-of line of the address.
`Address` Nullable	string	Street and house number.
`ZIP` Nullable	string	Postal code.
`Country` Nullable	string	Country of the address.
`ShipLock` Nullable	string	Shipping lock flag of the customer (`Customers.SLocked`).
`BaseCurrency` Nullable	varchar(3) string	ISO code of the base currency of the amounts if multi-currency is enabled; otherwise NULL.

Response example

<SqlRowSet1>
  <row>
    <CustKey>10001</CustKey>
    <CustomerNrExternal>SHOP-4711</CustomerNrExternal>
    <OrderCount>5</OrderCount>
    <Turnover_all>489.7000</Turnover_all>
    <ItemCount_all>12</ItemCount_all>
    <Turnover_avgPerOrder>97.9400</Turnover_avgPerOrder>
    <ReturnCount>1</ReturnCount>
    <ReturnValue>-39.9000</ReturnValue>
    <ReminderLevel>0</ReminderLevel>
    <DaysSinceLastOrder>14</DaysSinceLastOrder>
    <DaysSinceFirstOrder>412</DaysSinceFirstOrder>
    <ShopID>WEB</ShopID>
    <eMail>max.mustermann@example.com</eMail>
    <Salutation>Herr</Salutation>
    <FirstName>Max</FirstName>
    <LastName>Mustermann</LastName>
    <Name>Max Mustermann</Name>
    <CareOf/>
    <Address>Hauptstraße 1</Address>
    <ZIP>80331</ZIP>
    <Country>DE</Country>
    <ShipLock>0</ShipLock>
    <BaseCurrency>EUR</BaseCurrency>
  </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:pixiGetCustomerList>
      <!-- <StartCustKey>10001</StartCustKey> -->
    </ns1:pixiGetCustomerList>
  </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:pixiGetCustomerList>
      <!-- <StartCustKey>10001</StartCustKey> -->
    </ns1:pixiGetCustomerList>
  </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 = [
    // 'StartCustKey' => '10001',
];

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

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

SQL object:: dbo.pipiGetCustomerList

pixiGetCustomerList

Notes

Tables used

Result set 1 · Always (empty if no customer with orders has a key >= StartCustKey)

Response example

Result set 1 · Always (empty if no customer with orders has a key >= `StartCustKey`)