Stock Write

pixiGetChangedItemStock_LocID

Returns items whose stock changed in a time window, with physical and available stock, optionally restricted to one location and excluding bin groups.

Delta stock export per location. The call collects changed items, calculates their stock figures and returns one page of rows.

1. Candidate items

Candidates are non-bundle items that have entries in the stock movement log InvBinItemLog.
The change date is COALESCE(items.LastCalculateStockDate, InvBinItemLog.UpdateDate, now). It must lie between Since and DateTo.
If LocID is not empty, only log entries of that location count.
StockChange is the sum of the logged quantities.

2. Bundles

For every changed component, the bundle (parent) items it belongs to are added with BundleItem = 1.
Their StockChange is the component change divided by the component quantity in the bundle (integer division).

3. Paging

Rows are numbered (RowNr) by change date ascending, then by the highest log key.
Start is a zero-based offset. Rows from Start + 1 onward are kept, and at most Rowcount rows are returned.

4. Cleanup

Rows for items that no longer exist are removed.
Rows for items that are now bundles but were collected as non-bundles are removed.

5. Item data

ItemNrInt, EANUPC, ItemNrSuppl, Enabled and MinStockQty come from the item master.
PhysicalStock is the sum of all positive LocationQuantity values when LocID is empty, otherwise the location quantity of LocID.
AvailableStock is first set to items.Quantity.

6. Non-bundle items only

AvailableStock is recalculated as the sum of bin quantities (InvBinItem) in the location (or all locations), minus a deduction:

If setting ReservePrepaymentOrders is not 1: the order line quantities with status ANG or HAL are subtracted.
Otherwise: the number of reservation records is subtracted, counting order lines in non-final statuses except EIN, FUT and FUS.
Both deductions apply only when setting StockExportUsePhysicalStock is 0.

The call also fills:

OpenSupplierOrderQTY: open quantity of supplier order lines whose status has IsAllOpen = 1.
EstimatedDelivery: from Item_EstimatedDelivery.ExportEstimatedDelivery.

7. Excluded bin groups

If ExcludeBinGroups is set (comma-separated list), the logged quantities of all bins in these groups are summed per item. Only log entries up to DateTo count.
For bundles, the result is converted to bundle units.
This quantity is subtracted from AvailableStock, PhysicalStock and StockChange.

8. Result

The result is sorted by RowNr.

Notes

Side effect

Every call writes one entry into LogTable with Since, Rowcount and the duration. The entry is labelled pipiGetChangedItemStock.

Locking

All reads use NOLOCK, so results may include uncommitted data.

Pitfalls

Since = 0 / 1900-01-01 is turned into NULL. The date filter then matches nothing and the result is empty.
DateTo values that are NULL, 0 or on or before 1990-01-01 are replaced by the current time.
The time window is mainly based on items.LastCalculateStockDate, not on the individual log entry date.
The HAL order line filter subtracts a datetime from a datetime. With setting HalOrdersActiveDays (default 7 if 0), this effectively includes practically all HAL lines instead of only the last N days.
When reservations are enabled, the number of reservation rows is subtracted, not a quantity.
Items without any ItemLocationSpecific row (or none for LocID) keep empty item numbers, 0 stock and NULL Enabled.
PhysicalStock can be NULL if there is no positive location quantity and LocID is empty.
The bin group exclusion is not restricted to LocID.
For bundles, OpenSupplierOrderQTY stays 0 and EstimatedDelivery stays NULL.
Settings ShowLocationStockInItemSearch and OldStockExport are read but not used.

pixiGetChangedItemStock

Tables used

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

Parameter	Type	Default	Description
`Since` Required	datetime dateTime	–	Start of the change window (inclusive). `0` results in an empty result. 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 and stock to. Empty or NULL means all locations. Example: `001`
`DateTo`	datetime dateTime	NULL	End of the change window (inclusive). NULL, `0` or a date on or before 1990-01-01 means the current time. Example: `2024-05-02T00:00:00`
`Start`	int	0	Zero-based offset for paging. Rows with `RowNr` greater than `Start` are returned. 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: `RETOURE,SPERR`

Result set 1 · Always

Changed items with stock figures, sorted by RowNr.

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: the location quantity of `LocID`, or the sum of positive location quantities when `LocID` is empty. Reduced by excluded bin groups.
`AvailableStock` Nullable	int	Available stock. For non-bundles: bin quantity minus open order lines or reservations. For bundles: `items.Quantity`. Reduced by excluded bin groups.
`StockChange` Nullable	int	Sum of the logged stock movements; for bundles converted to bundle units.
`EstimatedDelivery` Nullable	datetime dateTime	Estimated delivery date for export (non-bundles only).
`MinStockQty` Nullable	int	Minimum stock quantity of the item.
`Enabled` Nullable	bit boolean	Whether the item is enabled.
`OpenSupplierOrderQTY` Nullable	int	Open quantity in supplier orders whose status has `IsAllOpen = 1` (non-bundles only, otherwise 0).
`UpdateDate` Nullable	datetime dateTime	Change date used for the window and the sort order.
`OriginalUpdateDate` Nullable	datetime dateTime	`items.LastCalculateStockDate` of the item (for bundles falls back to `UpdateDate`).
`BundleItem` Nullable	bit boolean	1 if the row is a bundle item derived from a changed component.
`RowNr` Nullable	int	Running number used for paging.

Response example

<SqlRowSet1>
  <row>
    <ItemKey>4711</ItemKey>
    <ItemNrInt>ART-1001</ItemNrInt>
    <EANUPC>4006381333931</EANUPC>
    <ItemNrSuppl>SUP-55</ItemNrSuppl>
    <PhysicalStock>25</PhysicalStock>
    <AvailableStock>20</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:15:00</OriginalUpdateDate>
    <BundleItem>false</BundleItem>
    <RowNr>1</RowNr>
  </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_LocID>
      <Since>2024-05-01T00:00:00</Since>
      <!-- <Rowcount>500</Rowcount> -->
      <!-- <LocID>001</LocID> -->
      <!-- <DateTo>2024-05-02T00:00:00</DateTo> -->
      <!-- <Start>0</Start> -->
      <!-- <ExcludeBinGroups>RETOURE,SPERR</ExcludeBinGroups> -->
    </ns1:pixiGetChangedItemStock_LocID>
  </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_LocID>
      <Since>2024-05-01T00:00:00</Since>
      <!-- <Rowcount>500</Rowcount> -->
      <!-- <LocID>001</LocID> -->
      <!-- <DateTo>2024-05-02T00:00:00</DateTo> -->
      <!-- <Start>0</Start> -->
      <!-- <ExcludeBinGroups>RETOURE,SPERR</ExcludeBinGroups> -->
    </ns1:pixiGetChangedItemStock_LocID>
  </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' => 'RETOURE,SPERR',
];

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

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

SQL object:: dbo.pipiGetChangedItemStock_LocID

pixiGetChangedItemStock_LocID

Notes

Related calls

Tables used

Result set 1 · Always

Response example