pixiSatisfiedCustomers

Selects invoice lines joined with their order lines and the invoice address. Results can be used to contact customers who recently received goods.

Date window: the day parameters are relative to the current server date (GETDATE()):

MaxDaysSinceDelivery: invoice date (InvDate) must be on or after the start of the day N days ago.
MinDaysSinceDelivery: invoice date must be on or before the end of the day N days ago.
MaxDaysSinceOrder / MinDaysSinceOrder: the same for the order date of the order line.
A NULL value disables the respective bound.

Further filters:

Invoices referenced by another invoice (RefToInvoiceKey, e.g. a credit note) with an invoice date on or after the MaxDaysSinceDelivery date are excluded.
Lines whose order line has status MIN and an order date on or after the MaxDaysSinceDelivery date are excluded.
ExcludePartialDeliveries = 1: orders (by OrderNr) that still have order lines in status ANG or EIN are excluded.
NoOrderSince = 1: customers who have any order line with an order date on or after the MinDaysSinceDelivery date are excluded.
Only lines with ItemQty >= 0.

Two modes:

CustomerNrExternal is NULL: one row per external customer number (GROUP BY Addresses.CustomerNrExternal), sorted by customer number. ItemQty is the number of matching invoice lines.
CustomerNrExternal is set: only lines of that customer, one row per invoice line (GROUP BY Invlinekey), no explicit sort order. ItemQty is the quantity of the line.

Notes

All tables are read WITH (NOLOCK); results may contain uncommitted data.
In customer mode every column is aggregated independently with MAX/MIN, so InvoiceNr, ItemText, ItemPrice, ItemNrShop etc. can come from different invoices or lines of the same customer.
Addresses without an external customer number are grouped together into one NULL row in customer mode.
The credit-note filter and the MIN status filter only work when MaxDaysSinceDelivery is set; with NULL the comparison never matches and nothing is excluded.
NoOrderSince = 1 only has an effect when MinDaysSinceDelivery is set, because it compares against that date.
The table owning ItemQty, ItemText, ItemPrice, orderdate and Status is not qualified in the code; presumably invoice lines resp. order lines.
The meaning of the order line statuses MIN, ANG and EIN is not defined in the code.

Tables used

Invoices · Read Invlines · Read Orderlines · Read Addresses · Read Items · Read

Parameter	Type	Default	Description
`MaxDaysSinceDelivery`	int	NULL	Oldest invoice date as days before today; invoice date must be on or after the start of that day. NULL = no lower bound. Example: `20`
`MinDaysSinceDelivery`	int	NULL	Newest invoice date as days before today; invoice date must be on or before the end of that day. Also used by NoOrderSince. NULL = no upper bound. Example: `10`
`MaxDaysSinceOrder`	int	NULL	Oldest order date as days before today (start of day). NULL = no lower bound.
`MinDaysSinceOrder`	int	NULL	Newest order date as days before today (end of day). NULL = no upper bound.
`CustomerNrExternal`	varchar(60) string	NULL	External customer number of the invoice address. If set, returns one row per invoice line of this customer; if NULL, one row per customer. Example: `K100234`
`ExcludePartialDeliveries`	bit boolean	0	Excludes orders that still have order lines in status ANG or EIN. Allowed values `0` – Include partially delivered orders `1` – Exclude orders with open lines (ANG, EIN) Example: `1`
`NoOrderSince`	bit boolean	0	Excludes customers who placed any order on or after the MinDaysSinceDelivery date. Allowed values `0` – No restriction `1` – Only customers without newer orders (requires MinDaysSinceDelivery) Example: `0`

Result set 1 · If `CustomerNrExternal` is NULL

One row per external customer number, sorted by customer number. Values are aggregated per column.

Column	Type	Description
`InvoiceNr` Nullable	string	Highest invoice number of the customer
`InvoiceKey` Nullable	int	Highest invoice key of the customer
`CustomerNRExternal` Nullable	string	External customer number
`ShopID` Nullable	string	Shop ID of the address (Addresses.AddrShopID)
`Anrede`	string	Salutation, empty string if missing
`Name` Nullable	string	Customer name
`email` Nullable	string	E-mail address
`LastDeliveryDate` Nullable	datetime dateTime	Latest invoice date in the selection
`FirstOrderDate` Nullable	datetime dateTime	Earliest order date in the selection
`ItemText` Nullable	string	Highest item text (alphabetically) of the lines
`ItemQty`	int	Number of matching invoice lines
`ItemNrShop` Nullable	string	Items.ItemNrInt of the highest item key among the lines
`ItemPrice` Nullable	decimal	Highest item price of the lines

Result set 2 · If `CustomerNrExternal` is set

One row per invoice line of the given customer, without explicit sort order.

Column	Type	Description
`InvoiceNr` Nullable	string	Invoice number
`InvoiceKey` Nullable	int	Invoice key
`CustomerNRExternal` Nullable	string	External customer number
`ShopID` Nullable	string	Shop ID of the address
`Anrede`	string	Salutation, empty string if missing
`Name` Nullable	string	Customer name
`email` Nullable	string	E-mail address
`LastDeliveryDate` Nullable	datetime dateTime	Invoice date
`FirstOrderDate` Nullable	datetime dateTime	Order date of the order line
`ItemText` Nullable	string	Item text of the line
`ItemQty` Nullable	decimal	Quantity of the line
`ItemNrShop` Nullable	string	Items.ItemNrInt of the line's item
`ItemPrice` Nullable	decimal	Item price of the line

Response example

<SqlRowSet1>
  <row>
    <InvoiceNr>RE2024-10452</InvoiceNr>
    <InvoiceKey>88213</InvoiceKey>
    <CustomerNRExternal>K100234</CustomerNRExternal>
    <ShopID>SHOP1</ShopID>
    <Anrede>Frau</Anrede>
    <Name>Anna Müller</Name>
    <email>anna.mueller@example.de</email>
    <LastDeliveryDate>2024-05-03T14:22:10</LastDeliveryDate>
    <FirstOrderDate>2024-04-29T09:15:00</FirstOrderDate>
    <ItemText>Trekkingschuh Alpin</ItemText>
    <ItemQty>2</ItemQty>
    <ItemNrShop>TS-ALP-42</ItemNrShop>
    <ItemPrice>89.9000</ItemPrice>
  </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:pixiSatisfiedCustomers>
      <!-- <MaxDaysSinceDelivery>20</MaxDaysSinceDelivery> -->
      <!-- <MinDaysSinceDelivery>10</MinDaysSinceDelivery> -->
      <!-- <MaxDaysSinceOrder>?</MaxDaysSinceOrder> -->
      <!-- <MinDaysSinceOrder>?</MinDaysSinceOrder> -->
      <!-- <CustomerNrExternal>K100234</CustomerNrExternal> -->
      <!-- <ExcludePartialDeliveries>1</ExcludePartialDeliveries> -->
      <!-- <NoOrderSince>0</NoOrderSince> -->
    </ns1:pixiSatisfiedCustomers>
  </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:pixiSatisfiedCustomers>
      <!-- <MaxDaysSinceDelivery>20</MaxDaysSinceDelivery> -->
      <!-- <MinDaysSinceDelivery>10</MinDaysSinceDelivery> -->
      <!-- <MaxDaysSinceOrder>?</MaxDaysSinceOrder> -->
      <!-- <MinDaysSinceOrder>?</MinDaysSinceOrder> -->
      <!-- <CustomerNrExternal>K100234</CustomerNrExternal> -->
      <!-- <ExcludePartialDeliveries>1</ExcludePartialDeliveries> -->
      <!-- <NoOrderSince>0</NoOrderSince> -->
    </ns1:pixiSatisfiedCustomers>
  </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 = [
    // 'MaxDaysSinceDelivery' => '20',
    // 'MinDaysSinceDelivery' => '10',
    // 'MaxDaysSinceOrder' => '?',
    // 'MinDaysSinceOrder' => '?',
    // 'CustomerNrExternal' => 'K100234',
    // 'ExcludePartialDeliveries' => '1',
    // 'NoOrderSince' => '0',
];

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

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

Notes

Tables used

Result set 1 · If CustomerNrExternal is NULL

Result set 2 · If CustomerNrExternal is set

Response example

Result set 1 · If `CustomerNrExternal` is NULL

Result set 2 · If `CustomerNrExternal` is set