Stock Read

pixiGetChangedItemStock2

Returns items whose stock changed in a time window, with physical, available and per-location stock, reorder level and open supplier order quantity, with paging.

Delta export of stock changes, typically used to sync shop stock levels.

1. Locations. All locations, or only LocID if it is set.

2. Changed items. Non-bundle items with entries in the bin item log (InvBinItemLog) whose UpdateDate is between Since and DateTo in the selected locations.

StockChange is the sum of the logged quantities in the window.
UpdateDate is the latest log date.
OriginalUpdateDate is the item's LastCalculateStockDate, or the latest log date if that is empty.

3. Order line changes. With IncludeOrderLineChanges = 1, items with order lines that were updated in the window are added if they are not already candidates. The line's LocationID must be one of the selected locations, and the item must have bin log entries in those locations.

For these items StockChange is the sum of all log entries in those locations, not only those in the window.
UpdateDate is LastCalculateStockDate, otherwise the latest log date, otherwise the current time.

4. Bundles. For every changed component, the bundle items that contain it are added (BundleItem = 1). Their StockChange is the largest component change divided (integer division) by the component quantity in the bundle. Items that are bundle heads are only returned as bundle rows.

5. Sorting and paging. Candidates are numbered by UpdateDate ascending, then by the highest log key. Rows with a number of at least Start + 1 are returned, at most Rowcount of them.

6. Stock figures.

PhysicalStock: items.Quantity.
MinStockQty: sum of the non-negative MinStockQty values in ItemLocationSpecific for LocID, or for all locations.
For non-bundles:
- AvailableStock = the sum of LocationQuantity over all locations that are not special stock, minus the reserved quantity (see notes).
- OpenSupplierOrderQTY = sum of Qty - DeliveredQty - UndeliveredQty over supplier orders whose status has IsAllOpen = 1.
- EstimatedDelivery comes from Item_EstimatedDelivery.ExportEstimatedDelivery.
For bundles, AvailableStock stays items.Quantity, OpenSupplierOrderQTY is 0 and EstimatedDelivery is empty.

7. Excluded bin groups. If ExcludeBinGroups is set, the quantity in bins of these groups is subtracted from PhysicalStock, AvailableStock and StockChange. This quantity is the sum of all log entries up to DateTo. For bundles it is derived from the components. PhysicalStockLoc is then the sum of the bin quantities in the selected locations, without the excluded bin groups.

Without ExcludeBinGroups, PhysicalStockLoc is the sum of LocationQuantity in the selected locations, for non-bundles only.

The returned RowNr is renumbered from 1 within the returned page.

Notes

Read-only. All reads use NOLOCK, so results can contain uncommitted data.

Settings read at runtime:

StockExportUsePhysicalStock: only if 0, open order quantities and reservations are subtracted from AvailableStock.
ReservePrepaymentOrders and ExportAvailableStock_WhenReservationsAreEnabled:
- If reservations are off, or both settings are 1, the subtracted quantity is the sum of order lines in status ANG, plus HAL lines with an order date within the last HalOrdersActiveDays days (default 7).
- Otherwise it is the number of reservations whose order line has a non-final status other than EIN, FUT and FUS. Because of the join, reservations only count if the item also has ANG/HAL lines.
ExportStock_locations is read but not used.

Pitfalls:

Since must be a real date. The value 0 (1900-01-01) is turned into NULL and then no log entries match.
DateTo values up to 1990-01-01 are replaced by the current time.
The base for AvailableStock covers all non-special locations, even when LocID is set.
The bin group exclusion considers bins of all locations.
For the next page, increase Start by the number of rows received, or continue with a later Since. The returned RowNr is page-relative.
The comment in the code says bundles use LocationQuantity, but the code updates PhysicalStockLoc only for non-bundles; for bundles it stays 0.

Parameters

Parameter	Type	Default	Description
`Since` Required	datetime dateTime	–	Start of the change window. Bin log entries and, if enabled, order line changes from this time on are considered. Example: `2024-05-01T00:00:00`
`Rowcount`	int	1000	Maximum number of rows returned (page size). Example: `500`
`LocID`	varchar(3) string	–	Location ID to restrict changes, reorder level and `PhysicalStockLoc`. Empty means all locations. Example: `001`
`DateTo`	datetime dateTime	NULL	End of the change window. NULL, 0 or a date up to 1990-01-01 means the current time. Example: `2024-05-02T00:00:00`
`Start`	int	0	Number of candidate rows to skip (paging offset). 0 starts with the first row. Example: `0`
`ExcludeBinGroups`	varchar(max) string	NULL	Comma-separated list of bin groups whose quantities are subtracted from the stock figures. Empty or NULL disables the exclusion. Example: `RETURNS,DEFECT`
`IncludeOrderLineChanges`	smallint int	0	If 1, items with order lines updated in the window are also returned. Allowed values `0` – Only bin log changes are considered. `1` – Items with order line changes in the window are added. Example: `1`

Response

Result set 1 · Always (may be empty)

One row per changed item or affected bundle, ordered by change date.

Column	Type	Description
`ItemKey`	int	Internal item key.
`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 from `items.Quantity`, minus excluded bin groups if given.
`AvailableStock` Nullable	int	Available stock: location quantity minus open orders or reservations (non-bundles); `items.Quantity` for bundles.
`StockChange` Nullable	int	Net stock change in the window (sum of bin log quantities).
`EstimatedDelivery` Nullable	datetime dateTime	Estimated delivery date for export; NULL for bundles or if not set.
`MinStockQty` Nullable	int	Reorder level summed over the selected location(s).
`Enabled` Nullable	bit boolean	Whether the item is enabled.
`OpenSupplierOrderQTY` Nullable	int	Open quantity in open supplier orders (0 for bundles).
`UpdateDate` Nullable	datetime dateTime	Latest change date used for sorting.
`OriginalUpdateDate` Nullable	datetime dateTime	The item's `LastCalculateStockDate`, or the latest log date.
`BundleItem` Nullable	bit boolean	1 if the row is a bundle item.
`RowNr`	bigint long	Row number within the returned page, starting at 1.
`PhysicalStockLoc` Nullable	int	Physical stock in the selected location(s), without excluded bin groups if given; 0 if not determined.

Response example

<SqlRowSet1>
  <row>
    <ItemKey>10452</ItemKey>
    <ItemNrInt>ART-10452</ItemNrInt>
    <EANUPC>4012345678901</EANUPC>
    <ItemNrSuppl>SUP-889</ItemNrSuppl>
    <PhysicalStock>25</PhysicalStock>
    <AvailableStock>21</AvailableStock>
    <StockChange>-3</StockChange>
    <MinStockQty>5</MinStockQty>
    <Enabled>true</Enabled>
    <OpenSupplierOrderQTY>50</OpenSupplierOrderQTY>
    <UpdateDate>2024-05-01T09:15:22</UpdateDate>
    <OriginalUpdateDate>2024-05-01T09:16:00</OriginalUpdateDate>
    <BundleItem>false</BundleItem>
    <RowNr>1</RowNr>
    <PhysicalStockLoc>25</PhysicalStockLoc>
  </row>
  <row>
    <ItemKey>20877</ItemKey>
    <ItemNrInt>SET-20877</ItemNrInt>
    <EANUPC>4012345678918</EANUPC>
    <ItemNrSuppl/>
    <PhysicalStock>0</PhysicalStock>
    <AvailableStock>4</AvailableStock>
    <StockChange>-1</StockChange>
    <MinStockQty>0</MinStockQty>
    <Enabled>true</Enabled>
    <OpenSupplierOrderQTY>0</OpenSupplierOrderQTY>
    <UpdateDate>2024-05-01T09:15:22</UpdateDate>
    <OriginalUpdateDate>2024-05-01T09:15:22</OriginalUpdateDate>
    <BundleItem>true</BundleItem>
    <RowNr>2</RowNr>
    <PhysicalStockLoc>0</PhysicalStockLoc>
  </row>
</SqlRowSet1>

Examples

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:pixiGetChangedItemStock2>
      <Since>2024-05-01T00:00:00</Since>
      <!-- <Rowcount>500</Rowcount> -->
      <!-- <LocID>001</LocID> -->
      <!-- <DateTo>2024-05-02T00:00:00</DateTo> -->
      <!-- <Start>0</Start> -->
      <!-- <ExcludeBinGroups>RETURNS,DEFECT</ExcludeBinGroups> -->
      <!-- <IncludeOrderLineChanges>1</IncludeOrderLineChanges> -->
    </ns1:pixiGetChangedItemStock2>
  </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:pixiGetChangedItemStock2>
      <Since>2024-05-01T00:00:00</Since>
      <!-- <Rowcount>500</Rowcount> -->
      <!-- <LocID>001</LocID> -->
      <!-- <DateTo>2024-05-02T00:00:00</DateTo> -->
      <!-- <Start>0</Start> -->
      <!-- <ExcludeBinGroups>RETURNS,DEFECT</ExcludeBinGroups> -->
      <!-- <IncludeOrderLineChanges>1</IncludeOrderLineChanges> -->
    </ns1:pixiGetChangedItemStock2>
  </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-01T00:00:00',
    // 'Rowcount' => '500',
    // 'LocID' => '001',
    // 'DateTo' => '2024-05-02T00:00:00',
    // 'Start' => '0',
    // 'ExcludeBinGroups' => 'RETURNS,DEFECT',
    // 'IncludeOrderLineChanges' => '1',
];

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

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

Tables used

Settings · Read Location · Read OrderStates · Read items · Read InvBinItemLog · Read Orderlines · Read BundleItems · Read ItemLocationSpecific · Read SupplierOrderLine · Read SupplierOrder · Read SupplierOrderStatus · Read Reservation · Read Item_EstimatedDelivery · Read InvBin · Read InvBinItem · Read

SQL object:: dbo.pipiGetChangedItemStock2