pixiGetItemsTopReturnReason · Pixi API documentation

Analyses return reasons recorded on invoice lines and lists the items that are returned repeatedly for the same reason.

For every item in Items, the procedure looks at all invoice lines (invlines joined to invoices via InvoiceRef = InvoiceKey) that reference the item (ItemRef) and have a non-NULL ReturnReason. These lines are grouped by ReturnReason, and the reason with the highest number of lines is taken as the item's top return reason.

An item is included only if the count of its top return reason is strictly greater than MinNrOfReturns. With the default of 2, the top reason must occur at least 3 times.
Items without any invoice line with a return reason are not returned.
ReturnReason holds the most frequent reason, NrOfReturns the number of invoice lines with that reason (not the total of all returns of the item).

The result is sorted by item name. There is no paging.

Notes

All tables are read with NOLOCK, so uncommitted data may appear.
The count is based on invoice lines, not on quantities; a line with several returned units counts once.
If two reasons have the same highest count, which one is shown is not deterministic (no tie-breaker in the ordering).
The procedure evaluates correlated subqueries for every item in Items, which can be slow on large databases.
The header comment of the procedure ("Checks pixi* user credentials") does not match its actual function.
The code does not qualify column names; from which of the tables invoices or invlines the columns ReturnReason and ItemRef come cannot be determined from the code alone.

Tables used

Items · Read invoices · Read invlines · Read

Parameter	Type	Default	Description
`MinNrOfReturns`	int	2	Threshold for the number of invoice lines with the item's most frequent return reason. Only items whose count is strictly greater than this value are returned. Example: `5`

Result set 1 · Always (may be empty)

One row per item whose top return reason exceeds the threshold, sorted by item name.

Column	Type	Description
`itemkey`	int	Internal key of the item (`Items.ItemKey`).
`EANUPC` Nullable	string	EAN/UPC barcode of the item.
`ItemNrInt` Nullable	string	Internal item number.
`ItemNAme` Nullable	string	Name of the item.
`ReturnReason` Nullable	string	Most frequent return reason recorded on the item's invoice lines.
`NrOfReturns` Nullable	int	Number of invoice lines with the most frequent return reason.

Response example

<SqlRowSet1>
  <row>
    <itemkey>10452</itemkey>
    <EANUPC>4006381333931</EANUPC>
    <ItemNrInt>ART-10452</ItemNrInt>
    <ItemNAme>Laufschuh Pro 42</ItemNAme>
    <ReturnReason>Zu klein</ReturnReason>
    <NrOfReturns>7</NrOfReturns>
  </row>
  <row>
    <itemkey>20817</itemkey>
    <EANUPC>4012345678901</EANUPC>
    <ItemNrInt>ART-20817</ItemNrInt>
    <ItemNAme>Regenjacke M</ItemNAme>
    <ReturnReason>Gefällt nicht</ReturnReason>
    <NrOfReturns>4</NrOfReturns>
  </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:pixiGetItemsTopReturnReason>
      <!-- <MinNrOfReturns>5</MinNrOfReturns> -->
    </ns1:pixiGetItemsTopReturnReason>
  </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:pixiGetItemsTopReturnReason>
      <!-- <MinNrOfReturns>5</MinNrOfReturns> -->
    </ns1:pixiGetItemsTopReturnReason>
  </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 = [
    // 'MinNrOfReturns' => '5',
];

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

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