Items Read

pixiGetItemInfo

Returns master data, stock, supplier, VAT, tag and customs information for one or more items found by item key, EAN, item numbers or an XML list.

Looks up items and returns one row per matching item with its master data.

Input normalisation

ItemKey = 0 and empty strings are treated as NULL.
If none of ItemKey, ItemNrInt, EANUPC, ItemNrSuppl, ItemXML or InternalItemNumber is set, a single error row is returned instead of item data. LocID alone is not enough.

Search criteria

The scalar parameters and every /ITEMS/ITEM entry of ItemXML form a list of search criteria. Each criterion is matched against pixi.ItemCode:

ItemKey / ITEMKEY: item id
EANUPC / EAN: code type 1 (EAN)
ItemNrInt / ITEMNRINT: code type 2 (internal item number)
InternalItemNumber / INTERNALITEMNUMBER: code type 3
ItemNrSuppl / ITEMNRSUPPL: code type 4 (supplier item number)

All matches are combined with OR semantics (union): an item is returned if it matches any of the given values. Duplicate matches are removed.

Output values

LocID selects location-specific values from ItemLocationSpecific: MinStockQty, Quantity, SafetyStock, TargetStock. Without a location (or without a location record) the global item values are used and SafetyStock/TargetStock are 0.
ConsiderFavouriteSupplier = 1 uses the active preferred supplier (AddItems.PreferredSupplier = 1, supplier not disabled) for SupplPrice, SupplNr, ItemNrSuppl, EANUPC and MinOrderQty. With the default 0, the item's own values and the AddItems record of the item's main supplier are used.
VATLevel is derived from the VAT rate: 2 = MwSt_Low, 3 = MwSt_Null, any other value = MwSt_High.
ItemTAGs lists all tags of the item separated by |. ABC contains only the system tags A, B or C.

The rows are returned without a defined sort order.

Notes

All tables are read WITH (NOLOCK); results may contain uncommitted data.
The code comment marks ConsiderFavouriteSupplier as "not used anymore", but the code still evaluates it: with 1 the preferred-supplier values are used and MinOrderQty becomes 0 if no active preferred supplier exists.
Lookup by ItemKey also goes through pixi.ItemCode. An item without any entry in pixi.ItemCode is not found, even with a valid key.
Combining several parameters widens the result (OR), it does not narrow it.
In ItemXML the EAN element is named EAN, not EANUPC. Tabs are removed from ITEMKEY. A non-numeric ITEMKEY or malformed XML raises a SQL error.
EANUPC is limited to 13 characters.
Because of the outer joins (e.g. several code-type-3 entries or several active preferred suppliers), an item can appear more than once in the result.
No data is changed; only temporary tables are used.

Tables used

ItemCode · Read AddItems · Read Suppliers · Read TagMatch · Read Tag · Read Items · Read TontrArten · Read ItemLocationSpecific · Read Item_EstimatedDelivery · Read

Parameter	Type	Default	Description
`ItemKey`	int	NULL	Item key (item id) to look up. 0 is treated as not set. Example: `1234`
`ItemNrInt`	varchar(50) string	NULL	Internal item number (code type 2 in `pixi.ItemCode`). Example: `ART-10001`
`EANUPC`	varchar(13) string	NULL	EAN/UPC code (code type 1 in `pixi.ItemCode`). Example: `4006381333931`
`ItemNrSuppl`	varchar(50) string	NULL	Supplier item number (code type 4 in `pixi.ItemCode`). Example: `SUP-778-B`
`LocID`	varchar(3) string	NULL	Location (warehouse) ID for location-specific stock values (`MinStockQty`, `Quantity`, `SafetyStock`, `TargetStock`). Does not act as a search criterion. Example: `001`
`ItemXML`	varchar(max) xml	NULL	XML list of items to look up: one `ITEM` element per criterion set below `ITEMS`, with optional child elements `ITEMKEY`, `ITEMNRSUPPL`, `ITEMNRINT`, `EAN`, `INTERNALITEMNUMBER`. Example: `<ITEMS> <ITEM> <ITEMKEY>1234</ITEMKEY> <ITEMNRSUPPL/> <ITEMNRINT/> <EAN/> <INTERNALITEMNUMBER/> </ITEM> <ITEM> <ITEMKEY/> <ITEMNRSUPPL>SUP-778-B</ITEMNRSUPPL> <ITEMNRINT>ART-10002</ITEMNRINT> <EAN>4006381333931</EAN> <INTERNALITEMNUMBER>INT-5501</INTERNALITEMNUMBER> </ITEM> </ITEMS>`
`InternalItemNumber`	varchar(50) string	NULL	Additional internal item number (code type 3 in `pixi.ItemCode`). Example: `INT-5501`
`ConsiderFavouriteSupplier`	bit boolean	0	If 1, supplier values are taken from the active preferred supplier. Marked as "not used anymore" in a code comment, but still evaluated. Allowed values `0` – Use the item's own supplier values and the `AddItems` record of its main supplier. `1` – Use values of the active preferred supplier; `MinOrderQty` is 0 if none exists. Example: `0`

Result set 1 · If at least one search parameter is set

One row per matching item (may be empty if nothing matches).

Column	Type	Description
`ItemKey`	int	Item key.
`SupplPrice` Nullable	money decimal	Supplier purchase price (preferred supplier if `ConsiderFavouriteSupplier` = 1, otherwise item value).
`ItemName` Nullable	string	Item name.
`VPE`	int	Packaging unit; 0 if not set.
`OrderUnit`	int	Order unit; 0 if not set.
`MinStockQty` Nullable	int	Minimum stock quantity, location-specific if available for `LocID`, otherwise from the item.
`MinOrderQty`	int	Minimum order quantity from the preferred supplier (if `ConsiderFavouriteSupplier` = 1) or from the main supplier's `AddItems` record or the item; 0 if none.
`SupplNr` Nullable	varchar(4) string	Supplier number.
`Category` Nullable	string	Category text from `TontrArten` for the item's configuration type.
`ItemNrInt` Nullable	string	Internal item number.
`ItemNrSuppl`	varchar(50) string	Supplier item number; empty string if not set.
`Quantity` Nullable	int	Stock quantity, location-specific if available for `LocID`, otherwise total item quantity.
`PriceVK` Nullable	decimal	Sales price of the item.
`VATLevel` Nullable	varchar(10) string	VAT level: `MwSt_Low` (rate 2), `MwSt_Null` (rate 3) or `MwSt_High` (any other rate).
`Enabled` Nullable	boolean	Whether the item is enabled.
`ItemTAGs`	varchar(1000) string	All tags of the item, separated by `\|`; empty string if none.
`EANUPC` Nullable	varchar(13) string	EAN/UPC from the preferred supplier, the main supplier's `AddItems` record or the item.
`ABC`	varchar(1000) string	ABC classification from system tags `A`, `B`, `C`, separated by `\|`; empty string 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.
`InternalItemNumber` Nullable	string	Code of type 3 from `pixi.ItemCode`.
`SafetyStock`	int	Safety stock for `LocID`; 0 if no location-specific record.
`TargetStock`	int	Target stock for `LocID`; 0 if no location-specific record.
`ItemDescription` Nullable	string	Item description.
`ImputedSupplierPrice` Nullable	decimal	Imputed supplier price of the item.
`Weight` Nullable	decimal	Item weight.
`Heigth` Nullable	decimal	Item height (column name spelled `Heigth`).
`Width` Nullable	decimal	Item width.
`Length` Nullable	decimal	Item length.
`CustomsTariffNumber` Nullable	string	Customs tariff number.
`CustomsCountryOfOrigin` Nullable	string	Country of origin for customs.
`CustomsTariffText` Nullable	string	Customs tariff text.
`Manufacturer` Nullable	string	Manufacturer (item field `Artist`).

Result set 2 · If no search parameter is set (only `LocID` or nothing)

Single error row.

Column	Type	Description
`Error`	varchar(5) string	Always `Error`.
`ErrorMsg`	varchar(37) string	Error text.

Response example

<SqlRowSet1>
  <row>
    <ItemKey>1234</ItemKey>
    <SupplPrice>4.2000</SupplPrice>
    <ItemName>USB-C Cable 1m</ItemName>
    <VPE>1</VPE>
    <OrderUnit>10</OrderUnit>
    <MinStockQty>20</MinStockQty>
    <MinOrderQty>50</MinOrderQty>
    <SupplNr>L001</SupplNr>
    <Category>Accessories</Category>
    <ItemNrInt>ART-10001</ItemNrInt>
    <ItemNrSuppl>SUP-778-B</ItemNrSuppl>
    <Quantity>148</Quantity>
    <PriceVK>9.9900</PriceVK>
    <VATLevel>MwSt_High</VATLevel>
    <Enabled>true</Enabled>
    <ItemTAGs>A|Bestseller</ItemTAGs>
    <EANUPC>4006381333931</EANUPC>
    <ABC>A</ABC>
    <ExportEstimatedDelivery>2</ExportEstimatedDelivery>
    <PicLinkLarge>https://shop.example.com/img/1234_l.jpg</PicLinkLarge>
    <PicLinkSmall>https://shop.example.com/img/1234_s.jpg</PicLinkSmall>
    <InternalItemNumber>INT-5501</InternalItemNumber>
    <SafetyStock>10</SafetyStock>
    <TargetStock>200</TargetStock>
    <ItemDescription>USB-C to USB-C cable, 1 m, black</ItemDescription>
    <ImputedSupplierPrice>4.5000</ImputedSupplierPrice>
    <Weight>0.05</Weight>
    <Heigth>2</Heigth>
    <Width>10</Width>
    <Length>15</Length>
    <CustomsTariffNumber>85444290</CustomsTariffNumber>
    <CustomsCountryOfOrigin>CN</CustomsCountryOfOrigin>
    <CustomsTariffText>Electric conductors</CustomsTariffText>
    <Manufacturer>CableCorp</Manufacturer>
  </row>
</SqlRowSet1>

Status	Message	Cause
Error	Please specify at least one parameter	None of `ItemKey`, `ItemNrInt`, `EANUPC`, `ItemNrSuppl`, `ItemXML`, `InternalItemNumber` was given (or all were 0/empty). Provide at least one search value.

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:pixiGetItemInfo>
      <!-- <ItemKey>1234</ItemKey> -->
      <!-- <ItemNrInt>ART-10001</ItemNrInt> -->
      <!-- <EANUPC>4006381333931</EANUPC> -->
      <!-- <ItemNrSuppl>SUP-778-B</ItemNrSuppl> -->
      <!-- <LocID>001</LocID> -->
      <!-- <ItemXML><![CDATA[<ITEMS><ITEM><ITEMKEY>1234</ITEMKEY><ITEMNRSUPPL></ITEMNRSUPPL><ITEMNRINT></ITEMNRINT><EAN></EAN><INTERNALITEMNUMBER></INTERNALITEMNUMBER></ITEM><ITEM><ITEMKEY></ITEMKEY><ITEMNRSUPPL>SUP-778-B</ITEMNRSUPPL><ITEMNRINT>ART-10002</ITEMNRINT><EAN>4006381333931</EAN><INTERNALITEMNUMBER>INT-5501</INTERNALITEMNUMBER></ITEM></ITEMS>]]></ItemXML> -->
      <!-- <InternalItemNumber>INT-5501</InternalItemNumber> -->
      <!-- <ConsiderFavouriteSupplier>0</ConsiderFavouriteSupplier> -->
    </ns1:pixiGetItemInfo>
  </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:pixiGetItemInfo>
      <!-- <ItemKey>1234</ItemKey> -->
      <!-- <ItemNrInt>ART-10001</ItemNrInt> -->
      <!-- <EANUPC>4006381333931</EANUPC> -->
      <!-- <ItemNrSuppl>SUP-778-B</ItemNrSuppl> -->
      <!-- <LocID>001</LocID> -->
      <!-- <ItemXML><![CDATA[<ITEMS><ITEM><ITEMKEY>1234</ITEMKEY><ITEMNRSUPPL></ITEMNRSUPPL><ITEMNRINT></ITEMNRINT><EAN></EAN><INTERNALITEMNUMBER></INTERNALITEMNUMBER></ITEM><ITEM><ITEMKEY></ITEMKEY><ITEMNRSUPPL>SUP-778-B</ITEMNRSUPPL><ITEMNRINT>ART-10002</ITEMNRINT><EAN>4006381333931</EAN><INTERNALITEMNUMBER>INT-5501</INTERNALITEMNUMBER></ITEM></ITEMS>]]></ItemXML> -->
      <!-- <InternalItemNumber>INT-5501</InternalItemNumber> -->
      <!-- <ConsiderFavouriteSupplier>0</ConsiderFavouriteSupplier> -->
    </ns1:pixiGetItemInfo>
  </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' => 'ART-10001',
    // 'EANUPC' => '4006381333931',
    // 'ItemNrSuppl' => 'SUP-778-B',
    // 'LocID' => '001',
    // 'ItemXML' => '<ITEMS><ITEM><ITEMKEY>1234</ITEMKEY><ITEMNRSUPPL></ITEMNRSUPPL><ITEMNRINT></ITEMNRINT><EAN></EAN><INTERNALITEMNUMBER></INTERNALITEMNUMBER></ITEM><ITEM><ITEMKEY></ITEMKEY><ITEMNRSUPPL>SUP-778-B</ITEMNRSUPPL><ITEMNRINT>ART-10002</ITEMNRINT><EAN>4006381333931</EAN><INTERNALITEMNUMBER>INT-5501</INTERNALITEMNUMBER></ITEM></ITEMS>',
    // 'InternalItemNumber' => 'INT-5501',
    // 'ConsiderFavouriteSupplier' => '0',
];

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

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

SQL object:: dbo.pipiGetItemInfo