pixiGetRelocations

Returns stock movements created when items are relocated from one location to another.

Defaults and cleanup: if ChangeDateFrom is empty, the start of the current month (00:00) is used; if ChangeDateTo is empty, the end of the current day (23:59:59.997) is used. FromLocation and ToLocation are trimmed; empty strings are treated as "no filter".
Validation: if the period between ChangeDateFrom and ChangeDateTo is more than 31 days (counted as day boundaries), a single status row with Status = Error is returned and the call ends.
Candidates: from the bin item log, all entries with UpdateDate within the period are read in two groups:
- take-out entries (SMTRef = 19, SMORef = 11),
- put-in entries (SMTRef = 11, SMORef = 12). Each group is numbered separately in the order of the log key.
Pairing: the n-th take-out entry is paired with the n-th put-in entry. Only pairs where a put-in entry exists and the item of the take-out entry exists in the item master are returned. FromLocation is the location of the take-out entry, ToLocation the location of the put-in entry.
Location filters: FromLocation filters on the source location, ToLocation on the target location; both can be combined.
Paging: the remaining rows are numbered again (in log key order) in RowNumber. The call returns at most RowCount rows with RowNumber > Offset, sorted by RowNumber.

Qty is the quantity of the take-out entry multiplied by -1, i.e. the relocated quantity as a positive number.

Notes

Take-out and put-in entries are paired by position only (n-th take-out with n-th put-in in log key order), not by item or relocation document. If the numbers of take-out and put-in entries in the period differ, or a relocation spans the period boundary, pairs can be shifted and ToLocation may belong to another movement.
The meaning of the codes SMTRef/SMORef (19/11 and 11/12) is not documented in the code; the interpretation as take-out/put-in comes from the temporary table names.
If ChangeDateTo is passed as a date without time, it means midnight at the start of that day, so movements later that day are excluded.
All reads use NOLOCK, so uncommitted data can appear.
The helper procedures util.IndexCreate and util.TempTableDrop are used for temporary tables only; their definitions are not attached.
RowNumber is computed after the location filters, so paging with Offset is consistent for the same filter values.

Tables used

InvBinItemLog · Read Items · Read

Parameter	Type	Default	Description
`FromLocation`	varchar(3) string	NULL	Source location (location ID of the take-out entry). Empty or NULL means all source locations. Example: `001`
`ToLocation`	varchar(3) string	NULL	Target location (location ID of the put-in entry). Empty or NULL means all target locations. Example: `002`
`ChangeDateFrom`	datetime dateTime	NULL	Start of the period (inclusive) on `UpdateDate`. NULL means the start of the current month. Example: `2024-05-01T00:00:00`
`ChangeDateTo`	datetime dateTime	NULL	End of the period (inclusive) on `UpdateDate`. NULL means the end of the current day. The period may not exceed 31 days. Example: `2024-05-31T23:59:59`
`Offset`	int	0	Number of rows to skip; rows with `RowNumber` greater than this value are returned. Example: `0`
`RowCount`	int	1000	Maximum number of rows returned. Example: `1000`

Result set 1 · If the period between `ChangeDateFrom` and `ChangeDateTo` exceeds 31 days

Error status row; no data is returned.

Column	Type	Description
`Status`	varchar(5) string	Always `Error`.
`Message`	varchar(34) string	Error text.

Result set 2 · If the period is at most 31 days

One row per relocation (take-out/put-in pair), paged by Offset and RowCount, sorted by RowNumber.

Column	Type	Description
`RowNumber`	bigint long	Sequential number of the row within the filtered result, used for paging.
`ItemKey`	int	Internal key of the item.
`UpdateDate` Nullable	datetime dateTime	Timestamp of the take-out log entry.
`EANUPC` Nullable	string	EAN/UPC of the item.
`ItemName` Nullable	string	Name of the item.
`FromLocation` Nullable	string	Location ID of the take-out entry (source location).
`ToLocation` Nullable	string	Location ID of the paired put-in entry (target location).
`Qty` Nullable	int	Relocated quantity (quantity of the take-out entry multiplied by -1).

Response example

<SqlRowSet1>
  <row>
    <RowNumber>1</RowNumber>
    <ItemKey>10452</ItemKey>
    <UpdateDate>2024-05-03T10:15:22.473</UpdateDate>
    <EANUPC>4006381333931</EANUPC>
    <ItemName>Ballpoint pen blue</ItemName>
    <FromLocation>001</FromLocation>
    <ToLocation>002</ToLocation>
    <Qty>25</Qty>
  </row>
  <row>
    <RowNumber>2</RowNumber>
    <ItemKey>10987</ItemKey>
    <UpdateDate>2024-05-03T11:02:05.110</UpdateDate>
    <EANUPC>4006381333948</EANUPC>
    <ItemName>Notebook A5</ItemName>
    <FromLocation>001</FromLocation>
    <ToLocation>003</ToLocation>
    <Qty>10</Qty>
  </row>
</SqlRowSet1>

Status	Message	Cause
Error	Time period is limited to 31 days.	The difference between `ChangeDateFrom` and `ChangeDateTo` (or their defaults) is more than 31 days. Split the request into periods of at most 31 days.

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:pixiGetRelocations>
      <!-- <FromLocation>001</FromLocation> -->
      <!-- <ToLocation>002</ToLocation> -->
      <!-- <ChangeDateFrom>2024-05-01T00:00:00</ChangeDateFrom> -->
      <!-- <ChangeDateTo>2024-05-31T23:59:59</ChangeDateTo> -->
      <!-- <Offset>0</Offset> -->
      <!-- <RowCount>1000</RowCount> -->
    </ns1:pixiGetRelocations>
  </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:pixiGetRelocations>
      <!-- <FromLocation>001</FromLocation> -->
      <!-- <ToLocation>002</ToLocation> -->
      <!-- <ChangeDateFrom>2024-05-01T00:00:00</ChangeDateFrom> -->
      <!-- <ChangeDateTo>2024-05-31T23:59:59</ChangeDateTo> -->
      <!-- <Offset>0</Offset> -->
      <!-- <RowCount>1000</RowCount> -->
    </ns1:pixiGetRelocations>
  </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 = [
    // 'FromLocation' => '001',
    // 'ToLocation' => '002',
    // 'ChangeDateFrom' => '2024-05-01T00:00:00',
    // 'ChangeDateTo' => '2024-05-31T23:59:59',
    // 'Offset' => '0',
    // 'RowCount' => '1000',
];

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

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

Notes

Tables used

Result set 1 · If the period between ChangeDateFrom and ChangeDateTo exceeds 31 days

Result set 2 · If the period is at most 31 days

Response example

Result set 1 · If the period between `ChangeDateFrom` and `ChangeDateTo` exceeds 31 days