pixiShopLinkGetItemsStock

Returns one row per item with the internal item number and the quantity that can be exported to the shop.

The behaviour depends on the setting OldStockExport:

OldStockExport = 1: the procedure collects Quantity from Items into a temporary table, but returns no result set (the final SELECT is only in the other branch).
Otherwise (default): the available quantity is calculated per item as ItemQtyIBIExportStock_locations minus a deduction:
- ItemQtyOpenOrderLines if reservations are disabled (ReservePrepaymentOrders <> 1) or ExportAvailableStock_WhenReservationsAreEnabled = 1, and StockExportUsePhysicalStock = 0 and the item is not a bundle;
- ItemQtyOpenReservations if reservations are enabled, StockExportUsePhysicalStock = 0 and the item is not a bundle;
- nothing (0) in all other cases (physical stock export or bundle items).

Filtering and corrections (non-legacy branch):

If ExcludeZeros = 1, only items with a calculated quantity greater than 0 are kept. NULL is treated as 0 (all items).
If ExcludeBinGroups is given (comma-separated list of bin groups), the stock of each item in bins of these groups is summed from InvBinItemLog and subtracted. For bundles whose components lie in these bins, the deduction is the minimum over the components of CEILING(component qty / bundle component qty). The matching to the result row is done via the item code with CodeTypeId = 2 (shop item number) against ItemNrInt.
Negative quantities are set to 0.

The rows are returned without a defined sort order.

Notes

If the setting OldStockExport is 1, the call returns no rows at all; ExcludeZeros and ExcludeBinGroups have no visible effect in that case.
ExcludeZeros is applied before the bin group deduction and the clamping to 0. Items can therefore still appear with AvailableQty = 0 when ExcludeZeros = 1 and ExcludeBinGroups is used.
The bin group deduction matches pixi.ItemCode.Code (code type 2, shop item number) against Items.ItemNrInt. If these numbers differ for an item, no deduction is applied to it.
Settings are read via fpixi_GetSetting; a missing setting counts as 0.
All tables are read WITH (NOLOCK), so the result may contain uncommitted data.
Without paging the call returns every item, which can be a large response for big catalogues.
A large block of older logic is commented out in the code and is not executed.

Tables used

Items · Read InvBin · Read InvBinItemLog · Read ItemCode · Read BundleItems · Read Settings · Read

Parameter Type Default Description

Parameter	Type	Default	Description
`ExcludeZeros`	bit boolean	NULL	If 1, only items with a calculated quantity greater than 0 are returned. NULL is treated as 0. Allowed values `0` – Return all items (default). `1` – Return only items with stock greater than 0 (in the legacy branch: quantity not equal to 0). Example: `1`
`ExcludeBinGroups`	varchar(max) string	NULL	Comma-separated list of bin groups (`InvBin.BinGroup`) whose stock is subtracted from the available quantity. Empty entries are ignored, spaces around entries are trimmed. Example: `RETURNS,DEFECT`

ExcludeZeros

bit
boolean

NULL

If 1, only items with a calculated quantity greater than 0 are returned. NULL is treated as 0.

Allowed values

0 – Return all items (default).
1 – Return only items with stock greater than 0 (in the legacy branch: quantity not equal to 0).

Example: 1

ExcludeBinGroups

varchar(max)
string

NULL

Comma-separated list of bin groups (InvBin.BinGroup) whose stock is subtracted from the available quantity. Empty entries are ignored, spaces around entries are trimmed.

Example: RETURNS,DEFECT

Result set 1 · Only if the setting `OldStockExport` is not 1.

One row per item with the exportable stock quantity.

Column	Type	Description
`ItemNrInt` Nullable	varchar(50) string	Internal item number (`Items.ItemNrInt`).
`AvailableQty` Nullable	int	Available quantity for the stock export after deductions; never negative.

Response example

<SqlRowSet1>
  <row>
    <ItemNrInt>100245</ItemNrInt>
    <AvailableQty>17</AvailableQty>
  </row>
  <row>
    <ItemNrInt>100246</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:pixiShopLinkGetItemsStock>
      <!-- <ExcludeZeros>1</ExcludeZeros> -->
      <!-- <ExcludeBinGroups>RETURNS,DEFECT</ExcludeBinGroups> -->
    </ns1:pixiShopLinkGetItemsStock>
  </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:pixiShopLinkGetItemsStock>
      <!-- <ExcludeZeros>1</ExcludeZeros> -->
      <!-- <ExcludeBinGroups>RETURNS,DEFECT</ExcludeBinGroups> -->
    </ns1:pixiShopLinkGetItemsStock>
  </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',
    // 'ExcludeBinGroups' => 'RETURNS,DEFECT',
];

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

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

Notes

Tables used

Result set 1 · Only if the setting OldStockExport is not 1.

Response example

Result set 1 · Only if the setting `OldStockExport` is not 1.