Stock Read

pixiGetItemsStockHistory

Returns the bin stock movement log for all items in a date range, with optional filters by location, order, delivery and relocation transaction.

Returns entries of the bin item log (stock movements on bins), similar to the bin history in the Pixi client but for all items at once.

Steps:

Normalises the input: an empty or missing DateTo becomes the current date and time, blank Location and OrderNumberExternal become NULL (after trimming), DeliveryNumber = 0 is treated as no filter, RowCount defaults to 1000 and IbilKey to 0.
Selects log entries with:
- IBILkey greater than IbilKey (for paging),
- UpdateDate >= DateFrom and < DateTo (the upper bound is exclusive),
- optionally LocID = Location,
- optionally the external order number of the linked order line = OrderNumberExternal,
- optionally the number of the linked supplier delivery = DeliveryNumber,
- optionally the linked relocation transaction = RelocateTransactionID,
- entries written by the user AutoExport are always excluded.
Enriches each entry with item data (internal item number, EAN, supplier item number), bin name, stock movement type and operation, batch number, best-before date, relocation transaction ID, delivery number and external order number.
Returns at most RowCount rows, sorted by IBILkey ascending.

Paging: pass the highest IBILkey of the previous response as IbilKey to fetch the next page.

Notes

All tables are read with NOLOCK, so uncommitted movements may appear and results are not guaranteed to be consistent.
The procedure is created WITH RECOMPILE; a new execution plan is built on every call.
Entries whose UpdateEmp is AutoExport or NULL are never returned (the comparison <> 'AutoExport' excludes NULL).
Bins are joined with a LEFT JOIN, so entries for deleted bins are returned with an empty BinName. Log entries whose item no longer exists in Items are not returned.
Setting OrderNumberExternal, DeliveryNumber or RelocateTransactionID implicitly returns only entries that are linked to an order line, delivery or relocation transaction.
If a log entry is linked to several relocation transactions, it can appear more than once.
A negative RowCount causes a SQL error from TOP.

Tables used

InvBinItemLog · Read InvBin · Read Items · Read StockMovementOperations · Read StockMovementTypes · Read RelocateLogTransactionMatch · Read Orderlines · Read SDelHeader · Read

Parameter	Type	Default	Description
`DateFrom` Required	datetime dateTime	–	Start of the period (inclusive), compared with the log entry's `UpdateDate`. Example: `2024-05-01T00:00:00`
`DateTo`	datetime dateTime	NULL	End of the period (exclusive). If `NULL` or empty, the current date and time is used. Example: `2024-05-02T00:00:00`
`Location`	varchar(3) string	NULL	Optional location (warehouse) ID; only entries with this `LocID` are returned. Blank means all locations. Example: `001`
`OrderNumberExternal`	varchar(50) string	NULL	Optional external order number; only entries linked to an order line with this `OrderNrExternal` are returned. Blank means no filter. Example: `SHOP-100234`
`DeliveryNumber`	int	NULL	Optional supplier delivery number (`SDelHNr`); only entries linked to this delivery are returned. `0` means no filter. Example: `4711`
`RelocateTransactionID`	uniqueidentifier string	NULL	Optional relocation transaction ID; only entries linked to this relocation transaction are returned. Example: `3F2504E0-4F89-11D3-9A0C-0305E82C3301`
`RowCount`	int	1000	Maximum number of rows returned. `NULL` is treated as `1000`. Example: `500`
`IbilKey`	int	0	Paging cursor: only entries with `IBILkey` greater than this value are returned. Pass the last `IBILkey` of the previous page. Example: `0`

Result set 1 · Always (may be empty).

Bin item log entries matching the filters, at most RowCount rows, sorted by IBILkey ascending.

Column	Type	Description
`IBILkey`	int	Key of the bin item log entry; use for paging.
`ItemKey`	int	Internal item key.
`ItemNrInt` Nullable	string	Internal item number.
`EANUPC` Nullable	string	EAN/UPC of the item.
`ItemNrSuppl` Nullable	string	Supplier item number.
`Qty` Nullable	int	Quantity moved (sign indicates direction).
`UpdateEmp`	string	Employee or process that wrote the entry.
`UpdateDate`	datetime dateTime	Date and time of the movement.
`BinName` Nullable	string	Name of the bin; empty if the bin has been deleted.
`LocID` Nullable	varchar(3) string	Location (warehouse) ID.
`ForOrderline` Nullable	int	Key of the order line the movement belongs to, if any.
`Reason` Nullable	string	Reason text of the movement.
`SMType` Nullable	string	Stock movement type.
`SMOperation` Nullable	string	Stock movement operation as `Application/Name`.
`BatchNumber` Nullable	string	Batch (Charge) number.
`BestBeforeDate` Nullable	dateTime	Best-before date.
`RelocateTransactionID` Nullable	uniqueidentifier string	ID of the linked relocation transaction.
`DeliveryNumber` Nullable	int	Number of the linked supplier delivery (`SDelHNr`).
`OrderNrExternal` Nullable	varchar(50) string	External order number of the linked order line.

Response example

<SqlRowSet1>
  <row>
    <IBILkey>1520345</IBILkey>
    <ItemKey>8812</ItemKey>
    <ItemNrInt>ART-10023</ItemNrInt>
    <EANUPC>4012345678901</EANUPC>
    <ItemNrSuppl>SUP-5521</ItemNrSuppl>
    <Qty>-1</Qty>
    <UpdateEmp>MMU</UpdateEmp>
    <UpdateDate>2024-05-01T09:15:22</UpdateDate>
    <BinName>A-01-03</BinName>
    <LocID>001</LocID>
    <ForOrderline>334455</ForOrderline>
    <Reason>Picking</Reason>
    <SMType>OUT</SMType>
    <SMOperation>PickApp/Pick</SMOperation>
    <OrderNrExternal>SHOP-100234</OrderNrExternal>
  </row>
  <row>
    <IBILkey>1520346</IBILkey>
    <ItemKey>9021</ItemKey>
    <ItemNrInt>ART-20077</ItemNrInt>
    <EANUPC>4012345678918</EANUPC>
    <ItemNrSuppl>SUP-7710</ItemNrSuppl>
    <Qty>24</Qty>
    <UpdateEmp>KOB</UpdateEmp>
    <UpdateDate>2024-05-01T10:02:05</UpdateDate>
    <BinName>B-12-01</BinName>
    <LocID>001</LocID>
    <Reason>Goods receipt</Reason>
    <SMType>IN</SMType>
    <SMOperation>Client/Booking</SMOperation>
    <BatchNumber>CH2024-05</BatchNumber>
    <BestBeforeDate>2025-12-31T00:00:00</BestBeforeDate>
    <DeliveryNumber>4711</DeliveryNumber>
  </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:pixiGetItemsStockHistory>
      <DateFrom>2024-05-01T00:00:00</DateFrom>
      <!-- <DateTo>2024-05-02T00:00:00</DateTo> -->
      <!-- <Location>001</Location> -->
      <!-- <OrderNumberExternal>SHOP-100234</OrderNumberExternal> -->
      <!-- <DeliveryNumber>4711</DeliveryNumber> -->
      <!-- <RelocateTransactionID>3F2504E0-4F89-11D3-9A0C-0305E82C3301</RelocateTransactionID> -->
      <!-- <RowCount>500</RowCount> -->
      <!-- <IbilKey>0</IbilKey> -->
    </ns1:pixiGetItemsStockHistory>
  </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:pixiGetItemsStockHistory>
      <DateFrom>2024-05-01T00:00:00</DateFrom>
      <!-- <DateTo>2024-05-02T00:00:00</DateTo> -->
      <!-- <Location>001</Location> -->
      <!-- <OrderNumberExternal>SHOP-100234</OrderNumberExternal> -->
      <!-- <DeliveryNumber>4711</DeliveryNumber> -->
      <!-- <RelocateTransactionID>3F2504E0-4F89-11D3-9A0C-0305E82C3301</RelocateTransactionID> -->
      <!-- <RowCount>500</RowCount> -->
      <!-- <IbilKey>0</IbilKey> -->
    </ns1:pixiGetItemsStockHistory>
  </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 = [
    'DateFrom' => '2024-05-01T00:00:00',
    // 'DateTo' => '2024-05-02T00:00:00',
    // 'Location' => '001',
    // 'OrderNumberExternal' => 'SHOP-100234',
    // 'DeliveryNumber' => '4711',
    // 'RelocateTransactionID' => '3F2504E0-4F89-11D3-9A0C-0305E82C3301',
    // 'RowCount' => '500',
    // 'IbilKey' => '0',
];

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

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

SQL object:: dbo.pipiGetItemsStockHistory