pixiGetStockForItems · Pixi API documentation

Returns one row per item with its internal item number and an available quantity. The calculation depends on Pixi settings read at runtime.

Old stock export (OldStockExport = 1)

Returns every item from Items with Quantity as AvailableQty. NULL becomes 0.

Default mode (any other value of OldStockExport)

The stock location is taken from the setting ExportStock_locations:
- setting missing or 0: location 001
- empty string: all locations (ALL)
- any other value: that location ID
For every item, the bin stock is the sum of BinItemQty over all bins of the selected location, or of all locations. Locations with SpecialStock = 1 are always left out.
Open demand is subtracted from the bin stock:
- Setting ReservePrepaymentOrders ≠ 1: the summed Qty of order lines with status ANG is subtracted. Order lines with status HAL also count if their OrderDate is within the last HalOrdersActiveDays days. A value of 0 for that setting means 7 days.
- Setting ReservePrepaymentOrders = 1: the number of reservation records for the item is subtracted. Only reservations whose order line status is not a final state are counted, and the statuses EIN, FUT and FUS are also excluded.
Negative results are returned as 0.
Bundle items (items that have entries in BundleItems) are added with a UNION. Their quantity is not taken from bins. It is Items.Quantity for ALL, or LocationQuantity from ItemLocationSpecific for the selected location. Nothing is subtracted for open orders or reservations, and negative values become 0.

Filtering

If ExcludeZeros = 1, all rows with AvailableQty = 0 are removed before the result is returned.

The result has no defined sort order.

Notes

All reads use NOLOCK, so the figures can include uncommitted data.
Bundles can appear twice. In default mode the first query covers all items, including bundles. A bundle therefore gets one row from the bin-based calculation and one row from the bundle calculation. UNION only removes rows that are completely identical.
Missing HalOrdersActiveDays. If the setting does not exist, the value is NULL, not 0. In that case the 7-day fallback does not apply and HAL order lines are never subtracted.
Reservations are counted, not summed. With reservations enabled, the number of reservation records is subtracted, not a quantity.
The setting ShowLocationStockInItemSearch is read but has no effect on the result.
ExportStock_locations is stored in a varchar(3) variable, so longer values are truncated to 3 characters.
The call computes correlated subqueries per item over all items and can be slow on large databases.

Tables used

Settings · Read Items · Read invBinitem · Read InvBin · Read Location · Read Orderlines · Read Reservation · Read orderstates · Read BundleItems · Read ItemLocationSpecific · Read

Parameter Type Default Description

Parameter	Type	Default	Description
`ExcludeZeros`	bit boolean	NULL	If `1`, items with an available quantity of `0` are removed from the result. Allowed values `1` – Exclude items with `AvailableQty` = 0. `0` – Return all items, including those with 0 (same as `NULL`). Example: `1`

ExcludeZeros

bit
boolean

NULL

If 1, items with an available quantity of 0 are removed from the result.

Allowed values

1 – Exclude items with `AvailableQty` = 0.
0 – Return all items, including those with 0 (same as `NULL`).

Example: 1

Result set 1 · Always

One row per item with its available quantity. In default mode, bundle items can appear twice.

Column	Type	Description
`ItemNrInt` Nullable	string	Internal item number from `Items.ItemNrInt`.
`AvailableQty` Nullable	int	Available quantity, never negative. In old export mode it is `Items.Quantity`. In default mode it is bin stock minus open demand, or for bundles `Quantity`/`LocationQuantity`.

Response example

<SqlRowSet1>
  <row>
    <ItemNrInt>100234</ItemNrInt>
    <AvailableQty>17</AvailableQty>
  </row>
  <row>
    <ItemNrInt>100235</ItemNrInt>
    <AvailableQty>0</AvailableQty>
  </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:pixiGetStockForItems>
      <!-- <ExcludeZeros>1</ExcludeZeros> -->
    </ns1:pixiGetStockForItems>
  </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:pixiGetStockForItems>
      <!-- <ExcludeZeros>1</ExcludeZeros> -->
    </ns1:pixiGetStockForItems>
  </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 = [
    // 'ExcludeZeros' => '1',
];

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

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