Items Read

pixiGetItemInfoPickList

Returns item master data for one or more items at a location, plus the open pick list lines and packed box quantities for these items and a combined status code.

Looks up one or more items and returns their master data together with information about pick lists and boxes in which they currently appear.

Validation

If ItemKey, EANUPC, ItemNrSuppl, ItemNrInt and ItemXML are all empty, the call returns a row with Status = 0 and a message, followed by the final status row 0000. LocID alone is not enough. ItemKey = 0 also counts as empty.

Item lookup

A single item is resolved from COALESCE(EANUPC, ItemNrInt, ItemNrSuppl) against the scannable item codes (pixi.ItemCode with a code type where Scannable = 1). The first matching item is used. If ItemKey is passed, it takes precedence over the code lookup.
In addition, ItemXML can list several items (/ITEMS/ITEM with child elements ITEMNRINT, ITEMNRSUPPL, ITEMKEY, EAN). Each entry matches items whose internal number, EAN, item key or supplier number equals one of the given values. These items are added to the single item from the parameters.

Result sets on success (in this order)

Item data from items, joined with the location-specific values from ItemLocationSpecific for LocID (stock, minimum stock, minimum order quantity), category name, VAT level, tags, ABC class, estimated delivery and picture links.
Pick list lines: for each item and pick list, the number of non-expired pick list items whose order line is not in status AUS, NLB, RET, STO or EIN. Filtered by the order line location if LocID is set.
Box items: for each item, box and used pick list number, the summed quantity in boxes for order lines with status EIN. Filtered by the order line location if LocID is set.
A status row with a four-digit code: access / item found / on pick list / in box. Possible values are 0000 (no parameter), 1000 (no item data found), 1100, 1101, 1110 and 1111.

Notes

The item data result set uses an INNER JOIN on ItemLocationSpecific with LocID = @LocID. If LocID is not passed (NULL), no item rows are returned and the status becomes 1000, although the pick list and box result sets are still filled for all locations.
The lookup uses COALESCE, so an empty string in EANUPC (instead of omitting it) prevents the lookup by ItemNrInt or ItemNrSuppl.
The XML matching uses OR conditions; one ITEM entry can match several different items.
If the same item is listed several times (e.g. via parameter and XML), the pick list and box result sets can contain duplicate rows; the item data set does not.
Sum_PickListItem, Sum_BoxItem, PickListNr and BoxNr are wrapped in ISNULL(..., ''); for numeric columns an empty value becomes 0.
All reads use NOLOCK, so uncommitted data may be returned. Pick list and box aggregates are computed for all items before they are filtered, which can be slow on large databases.
Invalid XML in ItemXML raises a SQL error from sp_xml_preparedocument.

Tables used

items · Read Item_EstimatedDelivery · Read TontrArten · Read ItemLocationSpecific · Read Orderlines · Read PLitem · Read boxitems · Read boxes · Read ItemCode · Read ItemCodeType · Read Tag · Read TagMatch · Read

Parameter	Type	Default	Description
`ItemKey`	int	NULL	Internal item key. Takes precedence over the code lookup. `0` is treated as empty. Example: `1234`
`ItemNrInt`	varchar(50) string	NULL	Internal item number; used for the scannable code lookup if `EANUPC` is NULL. Example: `4711`
`EANUPC`	varchar(13) string	NULL	EAN/UPC code; first value used for the scannable code lookup. Example: `4006381333931`
`ItemNrSuppl`	varchar(50) string	NULL	Supplier item number; used for the code lookup if `EANUPC` and `ItemNrInt` are NULL. Example: `SUP-4711`
`LocID`	varchar(3) string	NULL	Location (warehouse) ID. Needed for the item data result set (location-specific values); filters pick list and box data by order line location if set. Example: `001`
`ItemXML`	varchar(max) xml	NULL	Optional list of additional items as XML (`/ITEMS/ITEM`, element-centric). Each entry may contain `ITEMNRINT`, `ITEMNRSUPPL`, `ITEMKEY` and `EAN`; any matching value selects the item. Example: `<ITEMS> <ITEM> <ITEMNRINT>4711</ITEMNRINT> <ITEMNRSUPPL>SUP-4711</ITEMNRSUPPL> <ITEMKEY>1234</ITEMKEY> <EAN>4006381333931</EAN> </ITEM> <ITEM> <ITEMNRINT>4712</ITEMNRINT> <ITEMNRSUPPL>SUP-4712</ITEMNRSUPPL> <ITEMKEY>1235</ITEMKEY> <EAN>4006381333948</EAN> </ITEM> </ITEMS>`

Result set 1 · Only if no item parameter (`ItemKey`, `EANUPC`, `ItemNrSuppl`, `ItemNrInt`, `ItemXML`) is given

Validation error row.

Column	Type	Description
`Status`	varchar(1) string	Always `0`.
`StatusMsg`	varchar(37) string	Error message.

Result set 2 · If at least one item parameter is given

Item master data with location-specific values for LocID; one row per found item. Empty if LocID is NULL or has no location-specific record.

Column	Type	Description
`ItemKey`	int	Internal item key.
`SupplPrice` Nullable	decimal	Supplier (purchase) price.
`ItemName` Nullable	string	Item name.
`VPE`	decimal	Packaging unit; `0` if not set.
`MinStockQty` Nullable	int	Minimum stock at the location, otherwise from the item master.
`MinOrderQty` Nullable	int	Minimum order quantity at the location, otherwise from the item master.
`SupplNR` Nullable	string	Supplier number.
`Category` Nullable	string	Category name from `TontrArten` (via `ConfigType`).
`ItemNrInt` Nullable	string	Internal item number.
`ItemNrSuppl1`	string	Supplier item number, empty string if NULL.
`Quantity` Nullable	int	Stock quantity at the location, otherwise from the item master.
`PriceVK` Nullable	decimal	Sales price (`items.Price`).
`VATLevel`	varchar(9) string	`MwSt_Low` (MwStSatz 2), `MwSt_Null` (MwStSatz 3), otherwise `MwSt_High`.
`Enabled` Nullable	boolean	Item active flag.
`ItemTAGs` Nullable	varchar(8000) string	Tag names of the item, sorted and separated by `\|`.
`EANUPC` Nullable	string	EAN/UPC code.
`ItemNrSuppl` Nullable	string	Supplier item number (raw value).
`ABC` Nullable	char(1) string	ABC class derived from tags `A`, `B` or `C`; empty if none.
`ExportEstimatedDelivery` Nullable	string	Estimated delivery value from `Item_EstimatedDelivery`.
`PicLinkLarge` Nullable	string	Link to the large item picture.
`PicLinkSmall` Nullable	string	Link to the small item picture.

Result set 3 · If at least one item parameter is given

Pick list lines of the found items, one row per item, item number and pick list.

Column	Type	Description
`ItemRef` Nullable	int	Item key of the order line.
`ArtNr` Nullable	string	Item number on the order line.
`PickListNr`	int	Pick list header reference (`PLIheaderRef`).
`Sum_PickListItem`	int	Number of non-expired pick list items.

Result set 4 · If at least one item parameter is given

Box quantities of the found items for order lines in status EIN, one row per item, box and used pick list.

Column	Type	Description
`ItemRef` Nullable	int	Item key of the order line.
`ArtNr` Nullable	string	Item number (maximum over the grouped order lines).
`BoxNr`	string	Box number.
`Sum_BoxItem`	int	Summed quantity of the item in the box.
`pickListNrUsed` Nullable	string	Pick list number used for the order line.

Result set 5 · Always (last result set)

Combined status code: digit 1 = parameters given, 2 = item data found, 3 = on pick list, 4 = in box.

Column	Type	Description
`Status` Nullable	varchar(4) string	`0000`, `1000`, `1100`, `1101`, `1110` or `1111`.

Response example

<SqlRowSet1>
  <row>
    <ItemKey>1234</ItemKey>
    <SupplPrice>4.5000</SupplPrice>
    <ItemName>USB-Kabel 2m</ItemName>
    <VPE>10</VPE>
    <MinStockQty>20</MinStockQty>
    <MinOrderQty>50</MinOrderQty>
    <SupplNR>L100</SupplNR>
    <Category>Zubehör</Category>
    <ItemNrInt>4711</ItemNrInt>
    <ItemNrSuppl1>SUP-4711</ItemNrSuppl1>
    <Quantity>135</Quantity>
    <PriceVK>9.9900</PriceVK>
    <VATLevel>MwSt_High</VATLevel>
    <Enabled>1</Enabled>
    <ItemTAGs>A|Kabel</ItemTAGs>
    <EANUPC>4006381333931</EANUPC>
    <ItemNrSuppl>SUP-4711</ItemNrSuppl>
    <ABC>A</ABC>
    <ExportEstimatedDelivery>2</ExportEstimatedDelivery>
    <PicLinkLarge>https://shop.example.com/img/4711_l.jpg</PicLinkLarge>
    <PicLinkSmall>https://shop.example.com/img/4711_s.jpg</PicLinkSmall>
  </row>
</SqlRowSet1>

Status	Message	Cause
0	Please specify at least one parameter	None of `ItemKey`, `EANUPC`, `ItemNrSuppl`, `ItemNrInt` or `ItemXML` was given (or `ItemKey` was 0). Pass at least one item identifier.

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:pixiGetItemInfoPickList>
      <!-- <ItemKey>1234</ItemKey> -->
      <!-- <ItemNrInt>4711</ItemNrInt> -->
      <!-- <EANUPC>4006381333931</EANUPC> -->
      <!-- <ItemNrSuppl>SUP-4711</ItemNrSuppl> -->
      <!-- <LocID>001</LocID> -->
      <!-- <ItemXML><![CDATA[<ITEMS><ITEM><ITEMNRINT>4711</ITEMNRINT><ITEMNRSUPPL>SUP-4711</ITEMNRSUPPL><ITEMKEY>1234</ITEMKEY><EAN>4006381333931</EAN></ITEM><ITEM><ITEMNRINT>4712</ITEMNRINT><ITEMNRSUPPL>SUP-4712</ITEMNRSUPPL><ITEMKEY>1235</ITEMKEY><EAN>4006381333948</EAN></ITEM></ITEMS>]]></ItemXML> -->
    </ns1:pixiGetItemInfoPickList>
  </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:pixiGetItemInfoPickList>
      <!-- <ItemKey>1234</ItemKey> -->
      <!-- <ItemNrInt>4711</ItemNrInt> -->
      <!-- <EANUPC>4006381333931</EANUPC> -->
      <!-- <ItemNrSuppl>SUP-4711</ItemNrSuppl> -->
      <!-- <LocID>001</LocID> -->
      <!-- <ItemXML><![CDATA[<ITEMS><ITEM><ITEMNRINT>4711</ITEMNRINT><ITEMNRSUPPL>SUP-4711</ITEMNRSUPPL><ITEMKEY>1234</ITEMKEY><EAN>4006381333931</EAN></ITEM><ITEM><ITEMNRINT>4712</ITEMNRINT><ITEMNRSUPPL>SUP-4712</ITEMNRSUPPL><ITEMKEY>1235</ITEMKEY><EAN>4006381333948</EAN></ITEM></ITEMS>]]></ItemXML> -->
    </ns1:pixiGetItemInfoPickList>
  </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 = [
    // 'ItemKey' => '1234',
    // 'ItemNrInt' => '4711',
    // 'EANUPC' => '4006381333931',
    // 'ItemNrSuppl' => 'SUP-4711',
    // 'LocID' => '001',
    // 'ItemXML' => '<ITEMS><ITEM><ITEMNRINT>4711</ITEMNRINT><ITEMNRSUPPL>SUP-4711</ITEMNRSUPPL><ITEMKEY>1234</ITEMKEY><EAN>4006381333931</EAN></ITEM><ITEM><ITEMNRINT>4712</ITEMNRINT><ITEMNRSUPPL>SUP-4712</ITEMNRSUPPL><ITEMKEY>1235</ITEMKEY><EAN>4006381333948</EAN></ITEM></ITEMS>',
];

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

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

SQL object:: dbo.pipiGetItemInfoPickList