pixiGetReturns · Pixi API documentation

Reads returns from the pixi.Return table and returns one row per return.

Filters

StatusId: only returns with this status ID. NULL returns all statuses.
InvoiceNr: the invoice number is first resolved to its invoice key. Only returns of that invoice are listed.
ChangeDateFrom / ChangeDateTo: the return's RecordModifiedOn must lie between both values, inclusive.
- If ChangeDateFrom is NULL, the range starts at 00:00 on the same day one month ago.
- If ChangeDateTo is NULL, the range ends at the end of today (23:59:59.997).

Credit note The credit note referenced by the return (CreditInvoiceId) is joined optionally. Its number and total are returned as CreditNumber and CreditedAmount. Both are empty if the return has no credit note.

Paging and sort order

All matching returns are numbered in ascending order of their Id.
Rows with a row number greater than Offset are returned, at most RowCount of them.
The result is sorted by Id.

Additional data Each row also contains:

the status code,
the invoice number and customer reference of the original invoice,
the names of the users who created and last modified the return.

Notes

Unknown invoice number: If InvoiceNr does not match any invoice, the invoice filter is silently ignored. The call then returns all returns that match the other filters. The same happens for an empty string that matches no invoice.
Duplicate invoice numbers: If several invoices share the same number, only one of them is used; which one is not defined.
Paging gaps: Row numbers are assigned before the status, invoice and user data are joined. These joins are inner joins. A return whose status, original invoice, creating user or modifying user cannot be found is dropped from the output. A page can therefore contain fewer than RowCount rows even though later pages still have data. For paging, increase Offset by RowCount each time; do not use the number of rows received.
Time precision: RecordModifiedOn is held as smalldatetime (minute precision).
Dirty reads: All tables are read with NOLOCK, so uncommitted data may appear.

Tables used

Return · Read Invoices · Read Status · Read UCS_USERS · Read

Parameter	Type	Default	Description
`StatusId`	int	NULL	Status ID of the returns (`pixi.Status.Id`). `NULL` returns all statuses. Example: `1`
`ChangeDateFrom`	datetime dateTime	NULL	Start of the modification date range, inclusive. If `NULL`, the range starts at 00:00 on the same day one month ago. Example: `2024-05-01T00:00:00`
`ChangeDateTo`	datetime dateTime	NULL	End of the modification date range, inclusive. If `NULL`, the range ends at the end of today. Example: `2024-05-31T23:59:59`
`InvoiceNr`	varchar(20) string	NULL	Number of the original invoice. Only returns of this invoice are listed. If no invoice with this number exists, the filter is ignored. Example: `RE2024001234`
`Offset`	int	0	Number of matching returns to skip, counted in ascending `Id` order. Example: `0`
`RowCount`	int	1000	Maximum number of rows returned. Example: `500`

Result set 1 · Always (can be empty)

One row per return, sorted by Id.

Column	Type	Description
`Id`	int	ID of the return.
`StatusId`	int	Status ID of the return.
`StatusCode` Nullable	string	Code of the status from `pixi.Status`.
`InvoiceId`	int	Key of the original invoice.
`InvoiceNumber` Nullable	varchar(20) string	Number of the original invoice.
`CustomerId` Nullable	string	Customer reference (`CustRef`) of the original invoice.
`RecordCreatedOn` Nullable	smalldatetime dateTime	Creation date of the return.
`RecordCreatedByUserName` Nullable	string	Name of the user who created the return.
`RecordModifiedOn`	smalldatetime dateTime	Date of the last modification of the return.
`RecordModifiedByUserName` Nullable	string	Name of the user who last modified the return.
`CreditNumber` Nullable	varchar(20) string	Number of the credit note linked to the return. Empty if there is none.
`CreditedAmount` Nullable	money decimal	Total amount of the credit note. Empty if there is none.

Response example

<SqlRowSet1>
  <row>
    <Id>1532</Id>
    <StatusId>2</StatusId>
    <StatusCode>RETURN_RECEIVED</StatusCode>
    <InvoiceId>884512</InvoiceId>
    <InvoiceNumber>RE2024001234</InvoiceNumber>
    <CustomerId>100245</CustomerId>
    <RecordCreatedOn>2024-05-10T09:15:00</RecordCreatedOn>
    <RecordCreatedByUserName>lager01</RecordCreatedByUserName>
    <RecordModifiedOn>2024-05-12T14:30:00</RecordModifiedOn>
    <RecordModifiedByUserName>service02</RecordModifiedByUserName>
    <CreditNumber>GS2024000311</CreditNumber>
    <CreditedAmount>49.9000</CreditedAmount>
  </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:pixiGetReturns>
      <!-- <StatusId>1</StatusId> -->
      <!-- <ChangeDateFrom>2024-05-01T00:00:00</ChangeDateFrom> -->
      <!-- <ChangeDateTo>2024-05-31T23:59:59</ChangeDateTo> -->
      <!-- <InvoiceNr>RE2024001234</InvoiceNr> -->
      <!-- <Offset>0</Offset> -->
      <!-- <RowCount>500</RowCount> -->
    </ns1:pixiGetReturns>
  </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:pixiGetReturns>
      <!-- <StatusId>1</StatusId> -->
      <!-- <ChangeDateFrom>2024-05-01T00:00:00</ChangeDateFrom> -->
      <!-- <ChangeDateTo>2024-05-31T23:59:59</ChangeDateTo> -->
      <!-- <InvoiceNr>RE2024001234</InvoiceNr> -->
      <!-- <Offset>0</Offset> -->
      <!-- <RowCount>500</RowCount> -->
    </ns1:pixiGetReturns>
  </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 = [
    // 'StatusId' => '1',
    // 'ChangeDateFrom' => '2024-05-01T00:00:00',
    // 'ChangeDateTo' => '2024-05-31T23:59:59',
    // 'InvoiceNr' => 'RE2024001234',
    // 'Offset' => '0',
    // 'RowCount' => '500',
];

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

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