Customers Read

pixiEmillGetCustomers2

Returns a list of customers with their billing address and latest order data, filtered by item, order, payment and customer attributes.

Returns one row per customer who has at least one order line matching all given filters. The customer is joined with the customer's address (AddrRef), the order lines, the item, the item category (tontrArten via configType) and optionally the invoice, supplier and additional item data. All filters are combined with AND. A filter whose parameter is NULL is not applied.

Shop

ShopID: only customers whose CustomerOf equals this value. An empty string counts as NULL.

Item filters (applied to the order lines)

ArticleName with ArticleNameCmp: 0 = exact match on the item name of the order line, 1 = name contains the value.
ArticleNr with ArticleNrCmp: matches the supplier item number, the EAN/UPC or the internal item number. 0 = exact, 1 = contains.
ArtCategory: key of the item category (tontrArtKey).
ArtSupplier: supplier number of the order line (SupplInfo) or supplier name.

Order filters

orderStatus: status of the order line, e.g. AUS or RET.
ShipDateFrom/ShipDateTo: ship date of the order line between both values. The filter applies only if both are set.
ShipDateDays with ShipDateDaysCmp: number of days between the ship date and now. 0 = more than, 1 = exactly, 2 = fewer than ShipDateDays days.
PaymentType: payment type of the invoice linked to the order line.

Customer filters

CustTurnover with CustTurnoverCmp: sum of all invoice totals of the customer. 0 = turnover greater than the value, 1 = equal, 2 = less than.
CustReturn: 0 = ignore, 1 = only customers with at least one order line in status RET, -1 = only customers without such lines.
CustBlocked: 0 = only customers that are not blocked, 1 = blocked and unblocked customers.
CustOpenOrders: 0 = ignore, 1 = only customers with at least one order whose overall state is OPEN.

The columns OrderDate, ShipDate and Status are the maximum values over the matching order lines of the customer. The result is sorted by CustKey.

Notes

All tables are read with NOLOCK, so uncommitted data can appear.
resumeKey is declared but not used; there is no paging.
Status is the alphabetically highest status of the matching order lines, not necessarily the status of the newest order. OrderDate and ShipDate are computed independently of each other.
Comparison parameters with values other than those documented (e.g. ArticleNameCmp = 2 with ArticleName set, or CustBlocked = 2) make the filter match nothing, so the result is empty.
CustTurnover excludes customers without any invoice, because their turnover sum is NULL.
Setting PaymentType only returns order lines linked to an invoice of the same customer.
Order lines whose configType has no entry in tontrArten are never considered.
CustOpenOrders = 1 calls the scalar function fPixi_GetWholeOrderState per order of the customer, which can be slow for customers with many orders.

Parameters

Parameter	Type	Default	Description
`ArticleName`	varchar(100) string	NULL	Item name of the order line to filter by; compared according to `ArticleNameCmp`. Example: `T-Shirt`
`ArticleNameCmp`	int	0	Comparison mode for `ArticleName`. `NULL` is treated as `0`. Allowed values `0` – Exact match `1` – Name contains the value Example: `1`
`ArticleNr`	varchar(50) string	NULL	Item number to filter by; compared with supplier item number, EAN/UPC and internal item number according to `ArticleNrCmp`. Example: `4006381333931`
`ArticleNrCmp`	int	0	Comparison mode for `ArticleNr`. `NULL` is treated as `0`. Allowed values `0` – Exact match `1` – Number contains the value Example: `0`
`ArtCategory`	int	NULL	Key of the item category (`tontrArtKey` in `tontrArten`). Example: `3`
`ArtSupplier`	varchar(50) string	NULL	Supplier number of the order line or supplier name. Example: `LIEF01`
`orderStatus`	varchar(3) string	NULL	Status of the order line, e.g. `AUS` or `RET`. Example: `AUS`
`ShipDateFrom`	datetime dateTime	NULL	Start of the ship date range. Applied only together with `ShipDateTo`. Example: `2024-01-01T00:00:00`
`ShipDateTo`	datetime dateTime	NULL	End of the ship date range (inclusive). Applied only together with `ShipDateFrom`. Example: `2024-03-31T23:59:59`
`ShipDateDays`	int	NULL	Number of days since the ship date of the order line; compared according to `ShipDateDaysCmp`. Example: `30`
`ShipDateDaysCmp`	int	1	Comparison mode for `ShipDateDays`. `NULL` is treated as `1`. Allowed values `0` – Shipped more than `ShipDateDays` days ago `1` – Shipped exactly `ShipDateDays` days ago `2` – Shipped fewer than `ShipDateDays` days ago Example: `0`
`PaymentType`	varchar(30) string	NULL	Payment type of the invoice linked to the order line. Example: `PayPal`
`CustTurnover`	int	NULL	Turnover threshold, compared with the sum of all invoice totals of the customer according to `CustTurnoverCmp`. Example: `500`
`CustTurnoverCmp`	int	1	Comparison mode for `CustTurnover`. `NULL` is treated as `1`. Allowed values `0` – Customer turnover greater than `CustTurnover` `1` – Customer turnover equal to `CustTurnover` `2` – Customer turnover less than `CustTurnover` Example: `0`
`CustReturn`	int	0	Filter on returns of the customer. `NULL` is treated as `0`. Allowed values `0` – Ignore returns `1` – Only customers with at least one order line in status `RET` `-1` – Only customers without order lines in status `RET` Example: `-1`
`CustBlocked`	int	0	Controls whether blocked customers are included. `NULL` is treated as `0`. Allowed values `0` – Only customers that are not blocked (`SLocked` empty or `N`) `1` – Blocked and unblocked customers Example: `0`
`CustOpenOrders`	int	0	Filter on open orders of the customer. `NULL` is treated as `0`. Allowed values `0` – Ignore open orders `1` – Only customers with at least one order whose overall state is `OPEN` Example: `1`
`resumeKey`	int	NULL	Not used by the procedure.
`ShopID`	varchar(3) string	NULL	Shop the customer belongs to (`CustomerOf`). Empty string or `NULL` means all shops. Example: `SHP`

Response

Result set 1 · Always (may be empty)

One row per matching customer, sorted by CustKey.

Column	Type	Description
`CustKey`	int	Internal customer key.
`CustomerNrExternal` Nullable	string	External customer number.
`CeMail` Nullable	string	E-mail address of the customer.
`firstname` Nullable	string	First name from the customer's address.
`lastname` Nullable	string	Last name from the customer's address.
`Address` Nullable	string	Street and house number.
`ZIP` Nullable	string	Postal code.
`City` Nullable	string	City.
`State` Nullable	string	State or region.
`OrderDate` Nullable	dateTime	Latest order date among the matching order lines.
`ShipDate` Nullable	dateTime	Latest ship date among the matching order lines.
`Status` Nullable	string	Alphabetically highest order line status among the matching order lines.
`ShopID` Nullable	string	Shop the customer belongs to (`CustomerOf`).

Response example

<SqlRowSet1>
  <row>
    <CustKey>10452</CustKey>
    <CustomerNrExternal>K-000815</CustomerNrExternal>
    <CeMail>anna.mueller@example.de</CeMail>
    <firstname>Anna</firstname>
    <lastname>Müller</lastname>
    <Address>Hauptstraße 12</Address>
    <ZIP>80331</ZIP>
    <City>München</City>
    <State>Bayern</State>
    <OrderDate>2024-02-10T14:23:00</OrderDate>
    <ShipDate>2024-02-12T09:05:00</ShipDate>
    <Status>AUS</Status>
    <ShopID>SHP</ShopID>
  </row>
</SqlRowSet1>

Examples

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:pixiEmillGetCustomers2>
      <!-- <ArticleName>T-Shirt</ArticleName> -->
      <!-- <ArticleNameCmp>1</ArticleNameCmp> -->
      <!-- <ArticleNr>4006381333931</ArticleNr> -->
      <!-- <ArticleNrCmp>0</ArticleNrCmp> -->
      <!-- <ArtCategory>3</ArtCategory> -->
      <!-- <ArtSupplier>LIEF01</ArtSupplier> -->
      <!-- <orderStatus>AUS</orderStatus> -->
      <!-- <ShipDateFrom>2024-01-01T00:00:00</ShipDateFrom> -->
      <!-- <ShipDateTo>2024-03-31T23:59:59</ShipDateTo> -->
      <!-- <ShipDateDays>30</ShipDateDays> -->
      <!-- <ShipDateDaysCmp>0</ShipDateDaysCmp> -->
      <!-- <PaymentType>PayPal</PaymentType> -->
      <!-- <CustTurnover>500</CustTurnover> -->
      <!-- <CustTurnoverCmp>0</CustTurnoverCmp> -->
      <!-- <CustReturn>-1</CustReturn> -->
      <!-- <CustBlocked>0</CustBlocked> -->
      <!-- <CustOpenOrders>1</CustOpenOrders> -->
      <!-- <resumeKey>?</resumeKey> -->
      <!-- <ShopID>SHP</ShopID> -->
    </ns1:pixiEmillGetCustomers2>
  </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:pixiEmillGetCustomers2>
      <!-- <ArticleName>T-Shirt</ArticleName> -->
      <!-- <ArticleNameCmp>1</ArticleNameCmp> -->
      <!-- <ArticleNr>4006381333931</ArticleNr> -->
      <!-- <ArticleNrCmp>0</ArticleNrCmp> -->
      <!-- <ArtCategory>3</ArtCategory> -->
      <!-- <ArtSupplier>LIEF01</ArtSupplier> -->
      <!-- <orderStatus>AUS</orderStatus> -->
      <!-- <ShipDateFrom>2024-01-01T00:00:00</ShipDateFrom> -->
      <!-- <ShipDateTo>2024-03-31T23:59:59</ShipDateTo> -->
      <!-- <ShipDateDays>30</ShipDateDays> -->
      <!-- <ShipDateDaysCmp>0</ShipDateDaysCmp> -->
      <!-- <PaymentType>PayPal</PaymentType> -->
      <!-- <CustTurnover>500</CustTurnover> -->
      <!-- <CustTurnoverCmp>0</CustTurnoverCmp> -->
      <!-- <CustReturn>-1</CustReturn> -->
      <!-- <CustBlocked>0</CustBlocked> -->
      <!-- <CustOpenOrders>1</CustOpenOrders> -->
      <!-- <resumeKey>?</resumeKey> -->
      <!-- <ShopID>SHP</ShopID> -->
    </ns1:pixiEmillGetCustomers2>
  </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 = [
    // 'ArticleName' => 'T-Shirt',
    // 'ArticleNameCmp' => '1',
    // 'ArticleNr' => '4006381333931',
    // 'ArticleNrCmp' => '0',
    // 'ArtCategory' => '3',
    // 'ArtSupplier' => 'LIEF01',
    // 'orderStatus' => 'AUS',
    // 'ShipDateFrom' => '2024-01-01T00:00:00',
    // 'ShipDateTo' => '2024-03-31T23:59:59',
    // 'ShipDateDays' => '30',
    // 'ShipDateDaysCmp' => '0',
    // 'PaymentType' => 'PayPal',
    // 'CustTurnover' => '500',
    // 'CustTurnoverCmp' => '0',
    // 'CustReturn' => '-1',
    // 'CustBlocked' => '0',
    // 'CustOpenOrders' => '1',
    // 'resumeKey' => '?',
    // 'ShopID' => 'SHP',
];

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

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

Tables used

customers · Read addresses · Read orderlines · Read tontrArten · Read Invoices · Read items · Read suppliers · Read additems · Read OrderHeader · Read OrderStates · Read

SQL object:: dbo.pipiEmillGetCustomers2