pixiItemSearch · Pixi API documentation

Wrapper around the internal procedure GOSItemSearch1. It passes its parameters on and always requests supplier prices and statistics. No user name is passed, so supplier (purchase) prices are always visible.

Steps:

If SupplNr is empty, ShowOnlyItemsForExactSupplier is forced to 0.
RowCount = 0 is replaced by the setting ItemSearchLimit. If that setting is also 0, 300 is used.
If EANUPC is set, the item IDs whose code in pixi.ItemCode matches exactly are collected. Only code types flagged as scannable count.
If ItemName is set and the EAN step found nothing, ItemName is compared with all item codes:
- If exactly one code matches exactly, that item is the result. The free-text search is then switched off and code type 1 is used.
- Otherwise all items whose code starts with ItemName are collected. ItemName is then also used as a smart search term.
ConfigType is passed on as the configuration type filter (TonTrArt).
SupplNr is passed on as the supplier filter. LocID selects the location (default 001).
The database currency is always used. No exchange rate is passed.
For each item, the open quantity on supplier orders is calculated: ordered minus delivered minus undelivered quantity. Only supplier orders with a fully open status and quantities for the selected location are counted. If SupplNr is given, only that supplier's orders count.
The final query returns SELECT DISTINCT TOP (RowCount) rows. It is built dynamically.

The query has no ORDER BY; the ORDER BY clause is commented out in the code. The row order is therefore not guaranteed.

Notes

The result columns, the item filters (supplier, configuration type, smart search) and the joins come from the procedure pixi_ItemSearchPrepareDynamicQuery. Its definition is not attached, so the exact column list cannot be documented here.
All reads use NOLOCK, so results may include uncommitted data.
Behaviour depends on the settings ItemSearchLimit, MultiCurrencyEnabled and HalOrdersActiveDays. The legacy setting ShowSupplPriceOnlyToTheseUsers is read but has no effect, because no user name is passed.
ShowStatistics = 1 is passed to GOSItemSearch1, but the attached code of that procedure does not use this parameter.
Pitfall: on an exact single code match via ItemName, the code sets ItemName to NULL first and then copies it into EANUPC. As a result EANUPC also becomes NULL.
Single quotes in ItemName and SupplNr are escaped before they are put into the dynamic SQL.

Tables used

Settings · Read Location · Read ItemCode · Read ItemCodeType · Read Suppliers · Read Items · Read SupplierOrderLine · Read SupplierOrder · Read SupplierOrderStatus · Read SupplierOrderLineLocQty · Read

Parameter	Type	Default	Description
`RowCount`	int	0	Maximum number of rows. `0` uses the setting `ItemSearchLimit`; if that is also `0`, the limit is 300. Example: `50`
`ItemName`	varchar(100) string	–	Search term. An exact single match on any item code returns that item. Otherwise items whose code starts with the term are collected, and the term is also used as a smart search on item data. Example: `T-Shirt`
`ConfigType`	varchar(4) string	–	Configuration type filter, passed on as `TonTrArt`. Empty means no filter.
`SupplNr`	varchar(4) string	–	Supplier number. Used as the supplier filter and to limit the open supplier order quantities to this supplier. Example: `L001`
`EANUPC`	varchar(13) string	–	EAN/UPC code. Matched exactly against item codes whose code type is scannable. Example: `4006381333931`
`LocID`	varchar(3) string	001	Location (Lager) ID used for stock data and open supplier order quantities. Example: `001`
`ShowOnlyItemsForExactSupplier`	bit boolean	0	If `1`, only items of exactly the given supplier are returned. It is ignored (set to `0`) when `SupplNr` is empty. Allowed values `0` – No restriction to the exact supplier `1` – Only items of the given supplier Example: `0`

Result set 1 · Always (may be empty)

Item rows from the dynamic query, limited to RowCount distinct rows, without a guaranteed order. The columns are defined by pixi_ItemSearchPrepareDynamicQuery, which is not attached. They include item data, supplier prices and stock data for the location; the open supplier order data is computed in a temporary table.

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:pixiItemSearch>
      <!-- <RowCount>50</RowCount> -->
      <!-- <ItemName>T-Shirt</ItemName> -->
      <!-- <ConfigType>?</ConfigType> -->
      <!-- <SupplNr>L001</SupplNr> -->
      <!-- <EANUPC>4006381333931</EANUPC> -->
      <!-- <LocID>001</LocID> -->
      <!-- <ShowOnlyItemsForExactSupplier>0</ShowOnlyItemsForExactSupplier> -->
    </ns1:pixiItemSearch>
  </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:pixiItemSearch>
      <!-- <RowCount>50</RowCount> -->
      <!-- <ItemName>T-Shirt</ItemName> -->
      <!-- <ConfigType>?</ConfigType> -->
      <!-- <SupplNr>L001</SupplNr> -->
      <!-- <EANUPC>4006381333931</EANUPC> -->
      <!-- <LocID>001</LocID> -->
      <!-- <ShowOnlyItemsForExactSupplier>0</ShowOnlyItemsForExactSupplier> -->
    </ns1:pixiItemSearch>
  </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 = [
    // 'RowCount' => '50',
    // 'ItemName' => 'T-Shirt',
    // 'ConfigType' => '?',
    // 'SupplNr' => 'L001',
    // 'EANUPC' => '4006381333931',
    // 'LocID' => '001',
    // 'ShowOnlyItemsForExactSupplier' => '0',
];

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

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