pixiShopLinkGetItemsStockRange

Returns one row per item with its available quantity for a stock export to a shop. The behaviour depends on the settings OldStockExport, StockExportUsePhysicalStock, ReservePrepaymentOrders and ExportAvailableStock_WhenReservationsAreEnabled. A missing setting counts as 0.

Parameter defaults: FromItemKey NULL becomes 0, RowCount NULL becomes 1000, ExcludeZeros NULL becomes 0.

Standard mode (OldStockExport is not 1):

Only items with ItemKey >= FromItemKey are read. At most RowCount rows are returned, sorted by ItemKey ascending.
AvailableQty is the precomputed export stock of the item (ItemQtyIBIExportStock_locations) minus a deduction:
- The open order line quantity (ItemQtyOpenOrderLines) if reservations are disabled, or if they are enabled and ExportAvailableStock_WhenReservationsAreEnabled = 1. This applies only if StockExportUsePhysicalStock = 0 and the item is not a bundle.
- The open reservation quantity (ItemQtyOpenReservations) if reservations are enabled and the other setting is not 1. This also applies only if StockExportUsePhysicalStock = 0 and the item is not a bundle.
- Nothing in all other cases: physical stock is used, or the item is a bundle.
With ExcludeZeros = 1, only items with AvailableQty > 0 are returned.
With ExcludeZeros = 0, all items in the range are returned and negative quantities are set to 0.

Old mode (OldStockExport = 1):

Returns ItemNrInt, Quantity as AvailableQty (NULL becomes 0) and MinStockQty for all items.
There is no ItemKey column, no paging and no sort order.
With ExcludeZeros = 1, items with a quantity of 0 are left out.

Paging: in standard mode, call again with FromItemKey = last returned ItemKey + 1 until fewer than RowCount rows come back.

Notes

All reads use NOLOCK, so results may include uncommitted data.
The quantities come from precomputed columns of Items (ItemQtyIBIExportStock_locations, ItemQtyOpenOrderLines, ItemQtyOpenReservations). Which stock locations or bins they include is defined elsewhere and is not visible in this procedure.
Old mode differences:
- FromItemKey and RowCount are ignored and all items are returned in one call.
- ItemKey is missing from the result.
- With ExcludeZeros = 1, negative quantities are still returned.
- With ExcludeZeros = 0, negative quantities are not set to 0.
In standard mode with ExcludeZeros = 1, items whose computed quantity is NULL are also left out.
RowCount = 0 returns no rows. A negative value causes a SQL error from TOP.
The value of OldStockExport is converted to int. A non-numeric setting value would cause a conversion error.
The large commented-out legacy block in the source has no effect.

Tables used

Items · Read Settings · Read

Parameter Type Default Description

Parameter	Type	Default	Description
`FromItemKey`	int	NULL	Smallest `ItemKey` to return (inclusive). NULL means `0`. Ignored in old mode. Example: `0`
`RowCount`	int	NULL	Maximum number of rows to return. NULL means `1000`. Ignored in old mode. Example: `500`
`ExcludeZeros`	bit boolean	NULL	Controls whether items without stock are left out. NULL means `0`. Allowed values `0` – Return all items. In standard mode, negative quantities are returned as 0. `1` – Standard mode: only items with `AvailableQty > 0`. Old mode: only items with a quantity other than 0. Example: `1`

FromItemKey

int

NULL

Smallest ItemKey to return (inclusive). NULL means 0. Ignored in old mode.

Example: 0

RowCount

int

NULL

Maximum number of rows to return. NULL means 1000. Ignored in old mode.

Example: 500

ExcludeZeros

bit
boolean

NULL

Controls whether items without stock are left out. NULL means 0.

Allowed values

0 – Return all items. In standard mode, negative quantities are returned as 0.
1 – Standard mode: only items with `AvailableQty > 0`. Old mode: only items with a quantity other than 0.

Example: 1

Result set 1 · Standard mode (`OldStockExport` not 1) and `ExcludeZeros` = 0 or NULL

Up to RowCount items from FromItemKey on, sorted by ItemKey. Negative quantities are set to 0.

Column	Type	Description
`ItemKey`	int	Internal item key.
`ItemNrInt` Nullable	varchar(50) string	Internal item number.
`AvailableQty` Nullable	int	Exportable available quantity, never negative.
`MinStockQty` Nullable	int	Minimum stock quantity of the item.

Result set 2 · Standard mode (`OldStockExport` not 1) and `ExcludeZeros` = 1

Up to RowCount items from FromItemKey on with a positive available quantity, sorted by ItemKey.

Column	Type	Description
`ItemKey`	int	Internal item key.
`ItemNrInt` Nullable	string	Internal item number.
`AvailableQty`	int	Exportable available quantity, always greater than 0.
`MinStockQty` Nullable	int	Minimum stock quantity of the item.

Result set 3 · Old mode (`OldStockExport` = 1)

All items, or only those with a quantity other than 0 if ExcludeZeros = 1. No paging and no sort order.

Column	Type	Description
`ItemNrInt` Nullable	string	Internal item number.
`AvailableQty`	int	Item quantity (`Quantity`), 0 if NULL. May be negative.
`MinStockQty` Nullable	int	Minimum stock quantity of the item.

Response example

<SqlRowSet1>
  <row>
    <ItemKey>1001</ItemKey>
    <ItemNrInt>ART-10001</ItemNrInt>
    <AvailableQty>25</AvailableQty>
    <MinStockQty>5</MinStockQty>
  </row>
  <row>
    <ItemKey>1002</ItemKey>
    <ItemNrInt>ART-10002</ItemNrInt>
    <AvailableQty>0</AvailableQty>
    <MinStockQty>0</MinStockQty>
  </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:pixiShopLinkGetItemsStockRange>
      <!-- <FromItemKey>0</FromItemKey> -->
      <!-- <RowCount>500</RowCount> -->
      <!-- <ExcludeZeros>1</ExcludeZeros> -->
    </ns1:pixiShopLinkGetItemsStockRange>
  </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:pixiShopLinkGetItemsStockRange>
      <!-- <FromItemKey>0</FromItemKey> -->
      <!-- <RowCount>500</RowCount> -->
      <!-- <ExcludeZeros>1</ExcludeZeros> -->
    </ns1:pixiShopLinkGetItemsStockRange>
  </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 = [
    // 'FromItemKey' => '0',
    // 'RowCount' => '500',
    // 'ExcludeZeros' => '1',
];

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

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

Notes

Tables used

Result set 1 · Standard mode (OldStockExport not 1) and ExcludeZeros = 0 or NULL

Result set 2 · Standard mode (OldStockExport not 1) and ExcludeZeros = 1

Result set 3 · Old mode (OldStockExport = 1)

Response example

Result set 1 · Standard mode (`OldStockExport` not 1) and `ExcludeZeros` = 0 or NULL

Result set 2 · Standard mode (`OldStockExport` not 1) and `ExcludeZeros` = 1

Result set 3 · Old mode (`OldStockExport` = 1)