pixiShowSpecialCommentCustomers

Reads customer account entries (CustomerAccount) and joins the customer, the invoice and the invoice currency. Each row of the result is one customer account entry.

The call works in two modes:

InvoiceNr given: returns the entries whose invoice has exactly this invoice number. The customer note CHisNote is not checked in this mode, so customers without a note are returned as well. No sort order is applied.
InvoiceNr empty or omitted: returns all entries whose customer has a non-empty CHisNote and whose invoice has a non-empty invoice number. The rows are sorted by invoice date, newest first (InvDate DESC).

The number of returned rows is limited by Rows (default 300). An empty string for InvoiceNr is treated like NULL, and a NULL value for Rows is replaced by 300.

Notes

The row limit is applied with SET ROWCOUNT. Passing Rows = 0 therefore removes the limit and returns all matching rows, which can be slow on large databases.
All tables are read WITH (NOLOCK); uncommitted data may appear.
Although the joins are LEFT JOINs, the WHERE conditions on the invoice (and in list mode on the customer) make them behave like inner joins: entries without invoice are never returned.
CHisNote != '' excludes customers whose note is NULL.
If a customer account has several entries referring to the same invoice, that invoice appears several times.
Addresses is joined but none of its columns is returned.
According to the header comment, the call was created for a specific customer project (ticket PC-95).

Tables used

CustomerAccount · Read Customers · Read Invoices · Read Addresses · Read Currency · Read

Parameter	Type	Default	Description
`Rows`	int	300	Maximum number of rows to return (applied via `SET ROWCOUNT`). `NULL` is replaced by 300; `0` means no limit. Example: `100`
`InvoiceNr`	varchar(64) string	NULL	Invoice number to look up. If set, only entries for this invoice are returned and the customer note is not checked. Empty string is treated as `NULL` (list mode). Example: `RE2024-004711`

Result set 1 · Always; filtered by `InvoiceNr` if given, otherwise customers with non-empty `CHisNote` sorted by `InvDate` descending.

One row per customer account entry with customer data, customer note and invoice data.

Column	Type	Description
`SName` Nullable	string	Customer name.
`SCareOf` Nullable	string	Care-of / additional name line of the customer.
`SZIP` Nullable	string	Postal code of the customer.
`SCity` Nullable	string	City of the customer.
`CHisNote` Nullable	string	Customer history note (special comment) of the customer.
`InvoiceNr` Nullable	string	Invoice number.
`total` Nullable	decimal	Invoice total.
`InvDate` Nullable	dateTime	Invoice date.
`DATEVAccountNr` Nullable	varchar(10) string	DATEV account number of the customer, converted to text.
`CurrencyCode` Nullable	string	Alphabetic currency code of the invoice (e.g. EUR).

Response example

<SqlRowSet1>
  <row>
    <SName>Müller GmbH</SName>
    <SCareOf>z. Hd. Frau Schmidt</SCareOf>
    <SZIP>80331</SZIP>
    <SCity>München</SCity>
    <CHisNote>Nur Vorkasse</CHisNote>
    <InvoiceNr>RE2024-004711</InvoiceNr>
    <total>249.90</total>
    <InvDate>2024-05-14T10:23:00</InvDate>
    <DATEVAccountNr>10234</DATEVAccountNr>
    <CurrencyCode>EUR</CurrencyCode>
  </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:pixiShowSpecialCommentCustomers>
      <!-- <Rows>100</Rows> -->
      <!-- <InvoiceNr>RE2024-004711</InvoiceNr> -->
    </ns1:pixiShowSpecialCommentCustomers>
  </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:pixiShowSpecialCommentCustomers>
      <!-- <Rows>100</Rows> -->
      <!-- <InvoiceNr>RE2024-004711</InvoiceNr> -->
    </ns1:pixiShowSpecialCommentCustomers>
  </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 = [
    // 'Rows' => '100',
    // 'InvoiceNr' => 'RE2024-004711',
];

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

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

Notes

Tables used

Result set 1 · Always; filtered by InvoiceNr if given, otherwise customers with non-empty CHisNote sorted by InvDate descending.

Response example

Result set 1 · Always; filtered by `InvoiceNr` if given, otherwise customers with non-empty `CHisNote` sorted by `InvDate` descending.