pixiGetChangedItemStockSince · Pixi API documentation

Delta export of item stock based on the item column LastCalculateStockDate.

If Since is NULL, it is treated as 0 (1900-01-01), so all items qualify. A NULL Start is treated as 0.
The first Start + Rowcount items with LastCalculateStockDate >= Since are collected, ordered by LastCalculateStockDate, then ItemKey. Each row gets a sequential RowNr in this order.
For the rows with RowNr > Start the item data is filled in: item numbers, EAN, physical stock (Quantity), minimum stock, enabled flag, bundle flag, the estimated delivery date from the estimated delivery table and the available stock.
Available stock is the export stock of the item's locations minus a deduction that depends on settings:
- If StockExportUsePhysicalStock is '1', nothing is deducted.
- Otherwise, if reservations (ReservePrepaymentOrders = '1') are disabled, or they are enabled and ExportAvailableStock_WhenReservationsAreEnabled = 1, the quantity of open order lines is deducted.
- Otherwise, if reservations are enabled, the quantity of open reservations is deducted.
- Bundle items never get a deduction.
The open supplier order quantity is calculated per item as the sum of ordered minus delivered minus undelivered quantity over supplier order lines whose supplier order has a status flagged as open (IsAllOpen = 1) and whose consideration start date is empty or not in the future.
Up to Rowcount rows with RowNr > Start are returned, sorted by RowNr.

Paging: Start is the number of rows to skip, Rowcount the page size. For the next page pass Start + Rowcount with the same Since, or start over with the last UpdateDate as new Since.

Notes

All reads use NOLOCK; results may contain uncommitted data and can shift between paging calls if stock is recalculated meanwhile (the order depends on LastCalculateStockDate).
The filter is >= Since; when continuing with the last UpdateDate as Since, items with exactly that timestamp are returned again.
The column OpenSupplierOrderQTY is never filled and is always 0. The actual open supplier order quantity is in OpenSupplOrdersQty, which is NULL if the item has no open supplier order lines.
Rowcount must not be NULL; TOP with NULL causes an SQL error.
Settings are read via fpixi_GetSetting; missing settings count as '0'. The setting ExportAvailableStock_WhenReservationsAreEnabled is converted to int, a non-numeric value causes a conversion error.
Cost grows with Start, because Start + Rowcount rows are prepared on every call.

Tables used

items · Read Item_EstimatedDelivery · Read SupplierOrder · Read SupplierOrderStatus · Read SupplierOrderLine · Read Settings · Read

Parameter	Type	Default	Description
`Since` Required	datetime dateTime	–	Lower bound (inclusive) for the item's last stock calculation date. `NULL` returns all items. Example: `2024-05-01T08:00:00`
`Rowcount`	int	1000	Maximum number of rows to return (page size). Example: `500`
`Start`	int	0	Number of rows to skip; only rows with `RowNr > Start` are returned. `NULL` is treated as `0`. Example: `0`

Result set 1 · Always (may be empty).

One row per item whose stock was recalculated since Since, ordered by RowNr.

Column	Type	Description
`RowNr`	int	Sequential row number in the order of `LastCalculateStockDate`, `ItemKey`; use for paging.
`ItemKey`	int	Internal key of the item.
`ItemNrInt` Nullable	varchar(50) string	Internal item number.
`EANUPC` Nullable	varchar(13) string	EAN/UPC of the item.
`ItemNrSuppl` Nullable	varchar(50) string	Supplier item number.
`PhysicalStock` Nullable	int	Physical stock (`Quantity` of the item).
`AvailableStock` Nullable	int	Export stock of the locations minus open order lines or open reservations, depending on settings; no deduction for bundles or when physical stock export is configured.
`EstimatedDelivery` Nullable	datetime dateTime	Estimated delivery date for export; `NULL` if none is stored.
`MinStockQty` Nullable	int	Minimum stock quantity of the item.
`Enabled` Nullable	bit boolean	Whether the item is enabled.
`OpenSupplierOrderQTY` Nullable	int	Not filled by the code; always `0`. Use `OpenSupplOrdersQty` instead.
`UpdateDate` Nullable	datetime dateTime	Last stock calculation date of the item (`LastCalculateStockDate`).
`BundleItem` Nullable	bit boolean	Whether the item is a bundle.
`OpenSupplOrdersQty` Nullable	int	Open quantity on open supplier orders (ordered minus delivered minus undelivered); `NULL` if there are none.

Response example

<SqlRowSet1>
  <row>
    <RowNr>1</RowNr>
    <ItemKey>10452</ItemKey>
    <ItemNrInt>ART-10452</ItemNrInt>
    <EANUPC>4006381333931</EANUPC>
    <ItemNrSuppl>SUP-7781</ItemNrSuppl>
    <PhysicalStock>25</PhysicalStock>
    <AvailableStock>21</AvailableStock>
    <EstimatedDelivery>2024-05-10T00:00:00</EstimatedDelivery>
    <MinStockQty>5</MinStockQty>
    <Enabled>true</Enabled>
    <OpenSupplierOrderQTY>0</OpenSupplierOrderQTY>
    <UpdateDate>2024-05-01T08:15:22.380</UpdateDate>
    <BundleItem>false</BundleItem>
    <OpenSupplOrdersQty>40</OpenSupplOrdersQty>
  </row>
  <row>
    <RowNr>2</RowNr>
    <ItemKey>10987</ItemKey>
    <ItemNrInt>ART-10987</ItemNrInt>
    <EANUPC>4006381345675</EANUPC>
    <ItemNrSuppl>SUP-9012</ItemNrSuppl>
    <PhysicalStock>0</PhysicalStock>
    <AvailableStock>0</AvailableStock>
    <MinStockQty>0</MinStockQty>
    <Enabled>true</Enabled>
    <OpenSupplierOrderQTY>0</OpenSupplierOrderQTY>
    <UpdateDate>2024-05-01T08:16:03.120</UpdateDate>
    <BundleItem>false</BundleItem>
  </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:pixiGetChangedItemStockSince>
      <Since>2024-05-01T08:00:00</Since>
      <!-- <Rowcount>500</Rowcount> -->
      <!-- <Start>0</Start> -->
    </ns1:pixiGetChangedItemStockSince>
  </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:pixiGetChangedItemStockSince>
      <Since>2024-05-01T08:00:00</Since>
      <!-- <Rowcount>500</Rowcount> -->
      <!-- <Start>0</Start> -->
    </ns1:pixiGetChangedItemStockSince>
  </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',
    // 'Rowcount' => '500',
    // 'Start' => '0',
];

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

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