Stock Read

pixiGetChangedItemStock

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

Builds a delta list of stock changes for a stock export.

Time window and locations

Since to DateTo defines the window. DateTo defaults to the current time; values up to 1990-01-01 are also replaced by the current time.
LocID restricts to one location; empty means all locations.

Candidates

Non-bundle items with entries in InvBinItemLog in the window at the selected locations. StockChange is the sum of the logged quantities, UpdateDate the latest log date.
If IncludeOrderLineChanges = 1, items with order lines updated in the window at the selected locations are added (if not already candidates). They need at least one InvBinItemLog entry; StockChange is then the sum of all logged quantities at the locations, UpdateDate is items.LastCalculateStockDate (fallback: latest log date, then now).
For every candidate that is a component of a bundle (BundleItems), the bundle item is added too, with StockChange = component change divided (integer) by the component quantity.

Sort and paging Candidates are numbered by UpdateDate ascending, then by the latest InvBinItemLog key. Rows with number >= Start + 1 are taken, at most Rowcount. Bundle items that appear without the bundle flag and items no longer in items are skipped. The returned RowNr is renumbered from 1 within the page.

Stock values

PhysicalStock = items.Quantity.
MinStockQty = sum of non-negative ItemLocationSpecific.MinStockQty for the location (or all locations).
For non-bundles: AvailableStock = sum of ItemLocationSpecific.LocationQuantity over all locations without special stock, minus either open order quantities (ANG lines plus HAL lines not older than HalOrdersActiveDays days) or the number of reservations on non-final order lines (excluding states EIN, FUT, FUS), depending on settings. OpenSupplierOrderQTY is the open quantity of supplier order lines whose status is fully open; EstimatedDelivery comes from Item_EstimatedDelivery.ExportEstimatedDelivery.
For bundles: AvailableStock stays items.Quantity, OpenSupplierOrderQTY 0, EstimatedDelivery NULL.

Excluded bin groups If ExcludeBinGroups is given (comma-separated), the quantity logged up to DateTo in bins of these groups (locations without special stock) is subtracted from AvailableStock, PhysicalStock and StockChange; for bundles the quantity is derived via the component quantity. PhysicalStockLoc is then the bin quantity at the selected locations outside these groups. Without ExcludeBinGroups, PhysicalStockLoc is the sum of ItemLocationSpecific.LocationQuantity at the selected locations for non-bundles (0 for bundles).

Notes

Read-only; all permanent tables are read with NOLOCK, so values may reflect uncommitted data.
Passing 1900-01-01 (value 0) as Since turns it into NULL; the window filter then matches nothing and the result is empty.
Settings read at runtime: StockExportUsePhysicalStock (if not 0, no order or reservation quantities are subtracted), ReservePrepaymentOrders and ExportAvailableStock_WhenReservationsAreEnabled (choose between open order quantities and reservation count), HalOrdersActiveDays (0 means 7; if the setting is missing, HAL lines are not counted). ExportStock_locations is read but not used.
Reservation counts are only applied to items that also have open ANG/HAL order lines, because of the way the intermediate results are joined.
AvailableStock for non-bundles uses all locations without special stock, regardless of LocID; only PhysicalStockLoc, MinStockQty and the candidate selection respect LocID.
For paging, increase Start by Rowcount for the next page; the returned RowNr always starts at 1. Because UpdateDate ascending is the sort key, new changes during paging can shift positions.

Tables used

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 Settings · Read

Parameter	Type	Default	Description
`Since` Required	datetime dateTime	–	Start of the change window (inclusive). Example: `2024-05-01T00:00:00`
`Rowcount`	int	1000	Maximum number of rows per page. Example: `500`
`LocID`	varchar(3) string	–	Location ID to restrict to; empty or NULL for all locations. Example: `001`
`DateTo`	datetime dateTime	NULL	End of the change window (inclusive). NULL 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). Example: `0`
`ExcludeBinGroups`	varchar(max) string	NULL	Comma-separated list of bin groups whose quantities are subtracted from the stock values. Example: `RETURNS,QUARANTINE`
`IncludeOrderLineChanges`	smallint int	0	If 1, items with order lines changed in the window are also returned. Allowed values `0` – Only stock movements from the bin log. `1` – Additionally include items with changed order lines. Example: `1`

Result set 1 · Always (may be empty).

One row per changed item, 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	Item quantity (`items.Quantity`), minus excluded bin group quantity.
`AvailableStock` Nullable	int	Available stock after subtracting order or reservation quantities and excluded bin groups.
`StockChange` Nullable	int	Sum of logged stock movements for the item.
`EstimatedDelivery` Nullable	datetime dateTime	Estimated delivery date for export; NULL for bundles.
`MinStockQty`	int	Reorder level (sum over location(s)).
`Enabled` Nullable	bit boolean	Whether the item is enabled.
`OpenSupplierOrderQTY`	int	Open quantity on open supplier orders.
`UpdateDate` Nullable	datetime dateTime	Date of the latest change; sort key.
`OriginalUpdateDate` Nullable	datetime dateTime	Last stock calculation date of the item (`LastCalculateStockDate`).
`BundleItem`	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 at the selected location(s).

Response example

<SqlRowSet1>
  <row>
    <ItemKey>4711</ItemKey>
    <ItemNrInt>ART-1001</ItemNrInt>
    <EANUPC>4006381333931</EANUPC>
    <ItemNrSuppl>SUP-55</ItemNrSuppl>
    <PhysicalStock>25</PhysicalStock>
    <AvailableStock>21</AvailableStock>
    <StockChange>-3</StockChange>
    <EstimatedDelivery>2024-05-10T00:00:00</EstimatedDelivery>
    <MinStockQty>5</MinStockQty>
    <Enabled>true</Enabled>
    <OpenSupplierOrderQTY>50</OpenSupplierOrderQTY>
    <UpdateDate>2024-05-01T08:15:00</UpdateDate>
    <OriginalUpdateDate>2024-05-01T08:20:00</OriginalUpdateDate>
    <BundleItem>false</BundleItem>
    <RowNr>1</RowNr>
    <PhysicalStockLoc>25</PhysicalStockLoc>
  </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:pixiGetChangedItemStock>
      <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,QUARANTINE</ExcludeBinGroups> -->
      <!-- <IncludeOrderLineChanges>1</IncludeOrderLineChanges> -->
    </ns1:pixiGetChangedItemStock>
  </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:pixiGetChangedItemStock>
      <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,QUARANTINE</ExcludeBinGroups> -->
      <!-- <IncludeOrderLineChanges>1</IncludeOrderLineChanges> -->
    </ns1:pixiGetChangedItemStock>
  </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,QUARANTINE',
    // 'IncludeOrderLineChanges' => '1',
];

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

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

SQL object:: dbo.pipiGetChangedItemStock