Returns Read

pixiReadReturnLines

Returns return lines (Retourenpositionen) in pages ordered by Id, optionally filtered by creation and/or modification date.

Reads rows from the table ReturnLine (return lines) and returns them sorted by Id ascending. The call is designed for keyset paging and delta synchronisation.

Paging

Id is an exclusive lower bound: only lines with Id > @Id are returned. If Id is omitted or NULL, it is treated as 0, so reading starts at the first line.
At most RowCount rows are returned (default 1000; NULL is also treated as 1000).
To read the next page, pass the highest Id of the previous page as Id.

Date filters (only one group is applied, checked in this order):

CreateAndUpdateDateFrom / CreateAndUpdateDateTo: if at least one is set, a line matches if its creation or modification date is >= the from value and its creation or modification date is < the to value.
Otherwise CreateDateFrom / CreateDateTo: filters on RecordCreatedOn (from inclusive, to exclusive).
Otherwise UpdateDateFrom / UpdateDateTo: filters on RecordModifiedOn (from inclusive, to exclusive).
If no date parameter is set, all lines with Id > @Id are returned (limited by RowCount).

Each bound of a group can be omitted to get an open-ended range. Parameters of lower-priority groups are ignored when a higher-priority group is used.

Notes

The table is read with NOLOCK; uncommitted or inconsistent data may be returned.
Id cannot be used to fetch a single line: the code contains a branch for Id = @Id, but it is unreachable because Id is always set (NULL becomes 0) and RowCount is never NULL. Passing an Id always returns lines with a greater Id.
The combined filter (CreateAndUpdateDate…) checks both bounds with separate OR conditions. With both bounds set, a line created before the range and modified after the range can still match (e.g. created before To and modified after From).
A negative RowCount causes a SQL error from TOP; 0 returns an empty result.
Column data types are not visible in the code (table definition not attached).

Tables used

ReturnLine · Read

Parameter	Type	Default	Description
`Id`	int	NULL	Exclusive lower bound for the return line Id (keyset paging). Only lines with a greater Id are returned. NULL is treated as 0. Example: `0`
`CreateDateFrom`	datetime dateTime	NULL	Returns lines created on or after this date (inclusive). Used only if no CreateAndUpdateDate parameter is set. Example: `2024-01-01T00:00:00`
`CreateDateTo`	datetime dateTime	NULL	Returns lines created before this date (exclusive). Used only if no CreateAndUpdateDate parameter is set. Example: `2024-02-01T00:00:00`
`UpdateDateFrom`	datetime dateTime	NULL	Returns lines modified on or after this date (inclusive). Used only if no CreateAndUpdateDate or CreateDate parameter is set. Example: `2024-01-15T00:00:00`
`UpdateDateTo`	datetime dateTime	NULL	Returns lines modified before this date (exclusive). Used only if no CreateAndUpdateDate or CreateDate parameter is set. Example: `2024-01-16T00:00:00`
`CreateAndUpdateDateFrom`	datetime dateTime	NULL	Returns lines whose creation or modification date is on or after this date. Takes precedence over all other date parameters. Example: `2024-01-15T00:00:00`
`CreateAndUpdateDateTo`	datetime dateTime	NULL	Returns lines whose creation or modification date is before this date (exclusive). Takes precedence over all other date parameters. Example: `2024-01-16T00:00:00`
`RowCount`	int	1000	Maximum number of rows returned per call (page size). NULL is treated as 1000. Example: `500`

Result set 1 · Always (may be empty)

Return lines matching the filters, ordered by Id ascending, limited to RowCount rows.

Column	Type	Description
`Id`	int	Unique Id of the return line; use the highest value as `Id` for the next page.
`ReasonId` Nullable	int	Id of the return reason.
`InvoiceLineId` Nullable	int	Id of the invoice line the return refers to (column `InvLineId`).
`IsSpecialCase` Nullable	boolean	Flag marking the return line as a special case.
`CustomerComment` Nullable	string	Comment from the customer.
`UserComment` Nullable	string	Internal comment of the user.
`RecordModifiedOn` Nullable	dateTime	Date and time of the last modification.
`ReturnId` Nullable	int	Id of the return (header) this line belongs to.
`InvBinItemLogId` Nullable	int	Reference to the stock (bin item) log entry of the return line.
`InvoiceItemQty` Nullable	decimal	Quantity on the invoice line.
`ReturnItemQty` Nullable	decimal	Returned quantity.
`ItemPriceGross` Nullable	decimal	Gross item price.
`ItemPriceNet` Nullable	decimal	Net item price.
`RecordCreatedOn` Nullable	dateTime	Date and time the line was created.
`RecordCreatedByUserId` Nullable	int	Id of the user who created the line.
`RecordModifiedByUserId` Nullable	int	Id of the user who last modified the line.
`ItemQualityClassificationId` Nullable	int	Id of the quality classification of the returned item.

Response example

<SqlRowSet1>
  <row>
    <Id>1201</Id>
    <ReasonId>3</ReasonId>
    <InvoiceLineId>458812</InvoiceLineId>
    <IsSpecialCase>0</IsSpecialCase>
    <CustomerComment>Size too small</CustomerComment>
    <UserComment>Checked, item ok</UserComment>
    <RecordModifiedOn>2024-01-15T10:24:11</RecordModifiedOn>
    <ReturnId>845</ReturnId>
    <InvBinItemLogId>99231</InvBinItemLogId>
    <InvoiceItemQty>2</InvoiceItemQty>
    <ReturnItemQty>1</ReturnItemQty>
    <ItemPriceGross>29.99</ItemPriceGross>
    <ItemPriceNet>25.20</ItemPriceNet>
    <RecordCreatedOn>2024-01-14T16:02:45</RecordCreatedOn>
    <RecordCreatedByUserId>12</RecordCreatedByUserId>
    <RecordModifiedByUserId>15</RecordModifiedByUserId>
    <ItemQualityClassificationId>1</ItemQualityClassificationId>
  </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:pixiReadReturnLines>
      <!-- <Id>0</Id> -->
      <!-- <CreateDateFrom>2024-01-01T00:00:00</CreateDateFrom> -->
      <!-- <CreateDateTo>2024-02-01T00:00:00</CreateDateTo> -->
      <!-- <UpdateDateFrom>2024-01-15T00:00:00</UpdateDateFrom> -->
      <!-- <UpdateDateTo>2024-01-16T00:00:00</UpdateDateTo> -->
      <!-- <CreateAndUpdateDateFrom>2024-01-15T00:00:00</CreateAndUpdateDateFrom> -->
      <!-- <CreateAndUpdateDateTo>2024-01-16T00:00:00</CreateAndUpdateDateTo> -->
      <!-- <RowCount>500</RowCount> -->
    </ns1:pixiReadReturnLines>
  </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:pixiReadReturnLines>
      <!-- <Id>0</Id> -->
      <!-- <CreateDateFrom>2024-01-01T00:00:00</CreateDateFrom> -->
      <!-- <CreateDateTo>2024-02-01T00:00:00</CreateDateTo> -->
      <!-- <UpdateDateFrom>2024-01-15T00:00:00</UpdateDateFrom> -->
      <!-- <UpdateDateTo>2024-01-16T00:00:00</UpdateDateTo> -->
      <!-- <CreateAndUpdateDateFrom>2024-01-15T00:00:00</CreateAndUpdateDateFrom> -->
      <!-- <CreateAndUpdateDateTo>2024-01-16T00:00:00</CreateAndUpdateDateTo> -->
      <!-- <RowCount>500</RowCount> -->
    </ns1:pixiReadReturnLines>
  </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 = [
    // 'Id' => '0',
    // 'CreateDateFrom' => '2024-01-01T00:00:00',
    // 'CreateDateTo' => '2024-02-01T00:00:00',
    // 'UpdateDateFrom' => '2024-01-15T00:00:00',
    // 'UpdateDateTo' => '2024-01-16T00:00:00',
    // 'CreateAndUpdateDateFrom' => '2024-01-15T00:00:00',
    // 'CreateAndUpdateDateTo' => '2024-01-16T00:00:00',
    // 'RowCount' => '500',
];

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

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

SQL object:: dbo.pipiReadReturnLines