Items Read

pixiReadItems

Returns Artikel master data for a single item, a key range or items created/updated in a date window, ordered by ItemKey.

Reads Artikel records from items, enriched with the estimated delivery export value, the supplier name and the configuration type text. All modes return the same columns.

First, if RowCount is non-zero, the number of returned rows is limited to that value. A missing ItemKey is treated as 0.

The first matching mode is used:

Created or updated (CreateAndUpdateDateFrom and/or CreateAndUpdateDateTo set): items with ItemKey > ItemKey where CreateDate or UpdateDate is >= the from value, and CreateDate or UpdateDate is < the to value. Sorted by ItemKey ascending.
Created (CreateDateFrom and/or CreateDateTo set): items with ItemKey > ItemKey and CreateDate in [from, to). Sorted by ItemKey.
Updated (UpdateDateFrom and/or UpdateDateTo set): items with ItemKey > ItemKey and UpdateDate in [from, to). Sorted by ItemKey.
Key range (RowCount is not NULL): all items with ItemKey > ItemKey, sorted by ItemKey.
Single item (otherwise): the item whose ItemKey equals the parameter.

In modes 1–4, ItemKey is an exclusive lower bound. This allows keyset paging: pass the last ItemKey of the previous page together with RowCount.

The date checks only apply the bounds that are given. The to values are exclusive.

Notes

All tables are read WITH (NOLOCK). Results can contain uncommitted or inconsistent data.
Calling without any parameter falls into mode 5 with ItemKey = 0. This normally returns an empty result. The final ELSE branch of the code, which returns an empty result set, is never reached.
RowCount = 0 selects mode 4 (no date filter) without a row limit. It returns all items with ItemKey > ItemKey, which can be very large.
RowCount is applied via SET ROWCOUNT, so it also limits mode 5. That mode returns at most one row anyway.
In the combined mode, the from and to bounds are checked independently against CreateDate or UpdateDate. For example, an item created before the from date but updated after it matches the from condition.
Date parameters of different modes are not combined. If combined dates are set, the separate create/update dates are ignored, and create dates take precedence over update dates.
If several rows exist in Item_EstimatedDelivery for one item, the item is returned multiple times.

Tables used

items · Read Item_EstimatedDelivery · Read Suppliers · Read TontrArten · Read

Parameter	Type	Default	Description
`ItemKey`	int	NULL	Internal Artikel key. In single-item mode it is the exact key. In range and date modes it is an exclusive lower bound (`ItemKey > value`). NULL is treated as 0. Example: `12345`
`CreateDateFrom`	datetime dateTime	NULL	Returns items with `CreateDate >=` this value (inclusive). Example: `2024-01-01T00:00:00`
`CreateDateTo`	datetime dateTime	NULL	Returns items with `CreateDate <` this value (exclusive). Example: `2024-02-01T00:00:00`
`UpdateDateFrom`	datetime dateTime	NULL	Returns items with `UpdateDate >=` this value. Ignored if a create date or combined date is given. Example: `2024-03-01T00:00:00`
`UpdateDateTo`	datetime dateTime	NULL	Returns items with `UpdateDate <` this value (exclusive). Ignored if a create date or combined date is given. Example: `2024-03-02T00:00:00`
`CreateAndUpdateDateFrom`	datetime dateTime	NULL	Returns items whose `CreateDate` or `UpdateDate` is `>=` this value. Takes precedence over all other date parameters. Example: `2024-03-01T00:00:00`
`CreateAndUpdateDateTo`	datetime dateTime	NULL	Returns items whose `CreateDate` or `UpdateDate` is `<` this value (exclusive). Takes precedence over all other date parameters. Example: `2024-03-02T00:00:00`
`RowCount`	int	NULL	Maximum number of rows (page size). Non-zero values limit the result. Any non-NULL value without date filters selects key-range mode. `0` means no limit. Example: `500`

Result set 1 · Always; content depends on the selected mode (date window, key range or single item).

Artikel master data, one row per item (per estimated delivery row), sorted by ItemKey in range and date modes.

Column	Type	Description
`ItemKey`	int	Internal Artikel key.
`EANUPC` Nullable	string	EAN/barcode of the item.
`UPC` Nullable	string	UPC code.
`ItemNrSuppl` Nullable	string	Item number of the Lieferant.
`ItemNrInt` Nullable	string	Internal item number.
`ItemNrOld` Nullable	string	Old/previous item number.
`SupplNr` Nullable	string	Supplier number.
`DataSuppl` Nullable	string	Column `items.DataSuppl` (data supplier).
`ItemName` Nullable	string	Item name.
`ItemDescription` Nullable	string	Item description.
`ConfigType` Nullable	string	Configuration type code, joined to `TontrArten.TontrArt`.
`Price` Nullable	decimal	Sales price.
`SupplPrice` Nullable	decimal	Purchase price from the Lieferant.
`Curr_Source` Nullable	string	Source currency.
`MwStSatz` Nullable	decimal	VAT rate.
`AutoPrice` Nullable	string	Column `items.AutoPrice` (automatic pricing flag).
`RelDate` Nullable	dateTime	Release date.
`DisDate` Nullable	dateTime	Discontinuation date.
`Weight` Nullable	decimal	Weight.
`Heigth` Nullable	decimal	Height (column name spelled `Heigth`).
`Width` Nullable	decimal	Width.
`Length` Nullable	decimal	Length.
`PicLinkLarge` Nullable	string	Link to large picture.
`PicLinkSmall` Nullable	string	Link to small picture.
`VPE` Nullable	int	Packaging unit (Verpackungseinheit).
`MinOrderQty` Nullable	int	Minimum order quantity.
`CreateDate` Nullable	dateTime	Creation date of the item.
`UpdateDate` Nullable	dateTime	Last update date of the item.
`AvgDeliveryTime` Nullable	int	Average delivery time.
`Quantity` Nullable	int	Column `items.Quantity` (stock quantity).
`MinStockQty` Nullable	int	Minimum stock quantity.
`BookAccountEu` Nullable	string	Revenue account for EU sales.
`BookAccountNonEu` Nullable	string	Revenue account for non-EU sales.
`BookAccountEuCompanies` Nullable	string	Revenue account for EU companies.
`SerialNrRequired` Nullable	boolean	Whether a serial number is required.
`OrderUnit` Nullable	string	Order unit.
`ExportQtyAvail` Nullable	int	Exported available quantity.
`ExportReportedQtyAvail` Nullable	int	Last reported available quantity for export.
`ExportEstimatedDelivery` Nullable	string	Estimated delivery value from `Item_EstimatedDelivery`. NULL if no row exists.
`Enabled` Nullable	boolean	Whether the item is enabled.
`BookAccountEU_Suppl` Nullable	string	Purchase account for EU suppliers.
`BookAccountNonEU_Suppl` Nullable	string	Purchase account for non-EU suppliers.
`BookAccountEUCompanies_Suppl` Nullable	string	Purchase account for EU supplier companies.
`CustomsTariffNumber` Nullable	string	Customs tariff number.
`CustomsCountryOfOrigin` Nullable	string	Country of origin for customs.
`CustomsTariffText` Nullable	string	Customs tariff text.
`CustomsPreviousProcess` Nullable	string	Customs previous procedure.
`UpdateEmp` Nullable	string	Employee who last updated the item.
`LastImportDate` Nullable	dateTime	Date of the last import.
`IsBundle` Nullable	boolean	Whether the item is a bundle.
`SupplName` Nullable	string	Name of the Lieferant from `Suppliers`. NULL if not found.
`TontrText`	string	Text of the configuration type from `TontrArten`. `-` if not found.
`Manufacturer` Nullable	string	Manufacturer (column `items.Artist`).
`ImputedSupplierPrice` Nullable	decimal	Imputed supplier price.

Response example

<SqlRowSet1>
  <row>
    <ItemKey>12346</ItemKey>
    <EANUPC>4006381333931</EANUPC>
    <ItemNrSuppl>STB-100</ItemNrSuppl>
    <ItemNrInt>A-100234</ItemNrInt>
    <SupplNr>SUP01</SupplNr>
    <ItemName>Ballpoint pen blue</ItemName>
    <ConfigType>STD</ConfigType>
    <Price>2.49</Price>
    <SupplPrice>0.95</SupplPrice>
    <MwStSatz>19</MwStSatz>
    <Weight>0.02</Weight>
    <CreateDate>2024-01-15T10:22:00</CreateDate>
    <UpdateDate>2024-03-01T08:10:00</UpdateDate>
    <Quantity>120</Quantity>
    <Enabled>1</Enabled>
    <IsBundle>0</IsBundle>
    <SupplName>Office Supplies GmbH</SupplName>
    <TontrText>Standard</TontrText>
    <Manufacturer>Stabilo</Manufacturer>
  </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:pixiReadItems>
      <!-- <ItemKey>12345</ItemKey> -->
      <!-- <CreateDateFrom>2024-01-01T00:00:00</CreateDateFrom> -->
      <!-- <CreateDateTo>2024-02-01T00:00:00</CreateDateTo> -->
      <!-- <UpdateDateFrom>2024-03-01T00:00:00</UpdateDateFrom> -->
      <!-- <UpdateDateTo>2024-03-02T00:00:00</UpdateDateTo> -->
      <!-- <CreateAndUpdateDateFrom>2024-03-01T00:00:00</CreateAndUpdateDateFrom> -->
      <!-- <CreateAndUpdateDateTo>2024-03-02T00:00:00</CreateAndUpdateDateTo> -->
      <!-- <RowCount>500</RowCount> -->
    </ns1:pixiReadItems>
  </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:pixiReadItems>
      <!-- <ItemKey>12345</ItemKey> -->
      <!-- <CreateDateFrom>2024-01-01T00:00:00</CreateDateFrom> -->
      <!-- <CreateDateTo>2024-02-01T00:00:00</CreateDateTo> -->
      <!-- <UpdateDateFrom>2024-03-01T00:00:00</UpdateDateFrom> -->
      <!-- <UpdateDateTo>2024-03-02T00:00:00</UpdateDateTo> -->
      <!-- <CreateAndUpdateDateFrom>2024-03-01T00:00:00</CreateAndUpdateDateFrom> -->
      <!-- <CreateAndUpdateDateTo>2024-03-02T00:00:00</CreateAndUpdateDateTo> -->
      <!-- <RowCount>500</RowCount> -->
    </ns1:pixiReadItems>
  </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' => '12345',
    // 'CreateDateFrom' => '2024-01-01T00:00:00',
    // 'CreateDateTo' => '2024-02-01T00:00:00',
    // 'UpdateDateFrom' => '2024-03-01T00:00:00',
    // 'UpdateDateTo' => '2024-03-02T00:00:00',
    // 'CreateAndUpdateDateFrom' => '2024-03-01T00:00:00',
    // 'CreateAndUpdateDateTo' => '2024-03-02T00:00:00',
    // 'RowCount' => '500',
];

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

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

SQL object:: dbo.pipiReadItems