pixiReportReturnsCount · Pixi API documentation

Calculates four return figures for a dashboard. A return is counted as an invoice line with a negative quantity (ItemQty < 0). The figure is the sum of the absolute quantities (SUM(ABS(ItemQty))), not the number of documents.

Steps:

The reference day is @EndDate. If it is NULL, the current date is used. The time is set to the end of that day (23:59:59.997).
From the reference day, four periods are derived and matched against the invoice date (InvDate):
- Given day: start to end of the reference day.
- Previous day: the full calendar day before the reference day.
- Given month: from the first day of the reference month up to the end of the reference day (month to date).
- Previous month: the full calendar month before the reference month.
Invoice lines are joined to their invoice and the invoice's customer. If @ShopID is filled, only customers whose CustomerOf equals @ShopID are counted. If it is empty or NULL, all shops are included.

The call returns one row with the four values.

Notes

@StartDate is declared but not used. All periods are derived only from @EndDate.
All reads use NOLOCK. The figures can include uncommitted data.
Returns are taken from invoice lines (credit lines with negative quantity), not from a separate returns table.
The values are stored in varchar(10) variables and are returned as strings. Depending on the data type of ItemQty, they can contain decimal places.
If a period has no matching lines, its value is NULL. In the SOAP response the element is then usually missing.
If an error occurs while calculating, the procedure ends with return code -1 and returns no result set. One possible cause is a sum that does not fit into varchar(10).

Tables used

Invlines · Read Invoices · Read Customers · Read

Parameter	Type	Default	Description
`StartDate`	datetime dateTime	NULL	Not used by the procedure. It has no effect on the result.
`EndDate`	datetime dateTime	NULL	Reference day. All four periods are derived from it. The time part is ignored and set to the end of the day. If `NULL`, the current date is used. Example: `2024-03-15T00:00:00`
`ShopID`	varchar(3) string	–	Optional shop filter, compared with `Customers.CustomerOf` of the invoice customer. Empty or `NULL` includes all shops. Example: `SH1`

Result set 1 · On success (no error during calculation)

One row with the summed returned quantities for the four periods.

Column	Type	Description
`ReturnsOnGivenDay` Nullable	varchar(10) string	Sum of the returned quantities on the reference day.
`ReturnsOnPreviousDay` Nullable	varchar(10) string	Sum of the returned quantities on the day before the reference day.
`ReturnsInGivenMonth` Nullable	varchar(10) string	Sum of the returned quantities from the first day of the reference month up to the reference day.
`ReturnsInPreviousMonth` Nullable	varchar(10) string	Sum of the returned quantities in the full previous calendar month.

Response example

<SqlRowSet1>
  <row>
    <ReturnsOnGivenDay>4</ReturnsOnGivenDay>
    <ReturnsOnPreviousDay>7</ReturnsOnPreviousDay>
    <ReturnsInGivenMonth>58</ReturnsInGivenMonth>
    <ReturnsInPreviousMonth>131</ReturnsInPreviousMonth>
  </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:pixiReportReturnsCount>
      <!-- <StartDate>?</StartDate> -->
      <!-- <EndDate>2024-03-15T00:00:00</EndDate> -->
      <!-- <ShopID>SH1</ShopID> -->
    </ns1:pixiReportReturnsCount>
  </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:pixiReportReturnsCount>
      <!-- <StartDate>?</StartDate> -->
      <!-- <EndDate>2024-03-15T00:00:00</EndDate> -->
      <!-- <ShopID>SH1</ShopID> -->
    </ns1:pixiReportReturnsCount>
  </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 = [
    // 'StartDate' => '?',
    // 'EndDate' => '2024-03-15T00:00:00',
    // 'ShopID' => 'SH1',
];

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

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