pixiGetChangedItems · Pixi API documentation

Returns one row per item in Items whose UpdateDate is greater than or equal to @Since. This makes the call suitable for delta synchronisation of stock levels, for example to an online shop.

For each item the call calculates:

AvailableStock: the item quantity (Items.Quantity) minus open order quantities minus open reservations. Negative results are returned as 0.
- Open orders are computed by fPIXI_OpenOrdersCountForItem_ForStockExport. It sums OrderLines.Qty for lines with status ANG or BES, plus lines with status HAL whose order date is at most HalOrdersActiveDays days old.
- Open reservations are the sum of InvBinItem.BinItemQty over pick list items whose pick list header has a ReservedOrderNr and an expiry date (PLHexpiryDate) in the future.
EstimatedDelivery: the earliest EstimatedDeliveryDate of supplier order lines for the item that still have an open quantity, meaning ordered minus delivered minus undelivered is greater than 0. Only supplier orders whose status has IsOrderedConfirmed = 1 count; a code comment names these as ordered/confirmed. The value is NULL if there is no such line.

The call does not sort the rows and has no paging.

Notes

All tables are read WITH (NOLOCK), so results may include uncommitted data.
The HAL window comes from the setting HalOrdersActiveDays, read via fPIXI_GetSetting (not attached). If the setting is 0 or empty, 7 days are used.
Only changes to Items.UpdateDate select an item. The code does not show whether new orders, reservations or supplier orders update this column. Stock changes caused by those records may therefore not be reported.
If @Since is NULL, no rows are returned.
The calculation runs a scalar function and two subqueries per item. Use a recent @Since to keep the result small.

Tables used

Items · Read PLHeader · Read PLitem · Read InvBinItem · Read SupplierOrderLine · Read SupplierOrder · Read SupplierOrderStatus · Read OrderLines · Read

Parameter	Type	Default	Description
`Since` Required	datetime dateTime	–	Lower bound for `Items.UpdateDate` (inclusive). Only items changed at or after this time are returned. Example: `2024-05-01T08:00:00`

Result set 1 · Always (may be empty)

One row per item changed since @Since, with available stock and the earliest expected delivery date.

Column	Type	Description
`ItemNrInt` Nullable	string	Internal item number.
`ItemKey`	int	Internal key of the item.
`EANUPC` Nullable	string	EAN/UPC barcode of the item.
`ItemNrSuppl` Nullable	string	Item number of the supplier.
`AvailableStock`	int	Quantity minus open orders minus open reservations; never below 0.
`EstimatedDelivery` Nullable	datetime dateTime	Earliest estimated delivery date of open, ordered/confirmed supplier order lines for the item; `NULL` if none.

Response example

<SqlRowSet1>
  <row>
    <ItemNrInt>A-10023</ItemNrInt>
    <ItemKey>10023</ItemKey>
    <EANUPC>4012345678901</EANUPC>
    <ItemNrSuppl>SUP-7781</ItemNrSuppl>
    <AvailableStock>14</AvailableStock>
    <EstimatedDelivery>2024-05-15T00:00:00</EstimatedDelivery>
  </row>
  <row>
    <ItemNrInt>A-10045</ItemNrInt>
    <ItemKey>10045</ItemKey>
    <EANUPC>4012345678925</EANUPC>
    <ItemNrSuppl>SUP-7790</ItemNrSuppl>
    <AvailableStock>0</AvailableStock>
  </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:pixiGetChangedItems>
      <Since>2024-05-01T08:00:00</Since>
    </ns1:pixiGetChangedItems>
  </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:pixiGetChangedItems>
      <Since>2024-05-01T08:00:00</Since>
    </ns1:pixiGetChangedItems>
  </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 = [
    'Since' => '2024-05-01T08:00:00',
];

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

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