Items Read

pixiDispoGetItems

Returns a batch of items with preferred supplier, prices, stock and location-specific planning data for export to the Logcontrol Dispo software.

Exports item master data for the Logcontrol disposition (purchase planning) software. The call works in batches of items.

Candidate selection: the first ItemCount items with ItemKey greater than ItemKeyStart are taken from items, ordered by ItemKey ascending, together with their default supplier.
Preferred supplier: if an AddItems record with PreferredSupplier = 1 exists, its supplier replaces the default supplier of the item.
Additional data per item:
- item tags (tags of type TypeId = 1) as a comma-separated list,
- the earliest OrderDate of all order lines of the item,
- the summed bin quantity (BinItemQTY) over all bins in locations that are not special stock (SpecialStock <> 1).
Result: one row per item and location from ItemLocationSpecific. Only rows are returned where
- the supplier exists in Suppliers,
- an AddItems record exists for the item and this supplier,
- the location is not a special stock location,
- and, if LocationIds is given, the LocID is contained in the comma-separated list.

The result is sorted by ItemKey ascending. Decimal values (SupplPrice, Price) are returned as text with a comma as decimal separator; dates are formatted as dd.mm.yyyy. Single and double quotes are removed from the item name.

Paging: call again with ItemKeyStart set to the highest ItemKey of the previous batch.

Notes

All reads use NOLOCK; results may contain uncommitted data.
ItemCount limits the number of candidate items, not the number of result rows. Because of the per-location rows the result can contain more rows; because of the inner joins (supplier, AddItems, ItemLocationSpecific, non-special-stock location, location filter) it can contain fewer or even none, although further items exist. An empty result therefore does not necessarily mean the end of the item list; a caller should advance ItemKeyStart by the batch range rather than stop.
Quantity is joined via the order line data: for items without any order line Quantity is always 0, even if stock exists.
Quantity is the total over all non-special-stock locations, not the quantity of the location in LocationId (see LocationQuantity for that).
Tags starts with a leading space (only the first comma is removed), and special characters such as & are XML-escaped by the concatenation.
An empty or whitespace-only LocationIds is treated like NULL (no location filter). List entries are trimmed; each entry is compared as varchar(10).
VATLevel: L for MwStSatz = 2, 0 for MwStSatz = 3, otherwise H. Typ is always 0100.
Temporary tables are dropped via util.TempTableDrop (not attached).

Tables used

items · Read AddItems · Read TagMatch · Read Tag · Read Orderlines · Read InvBinItem · Read InvBin · Read Location · Read TontrArten · Read Suppliers · Read ItemLocationSpecific · Read

Parameter	Type	Default	Description
`ItemKeyStart`	int	0	Only items with an `ItemKey` greater than this value are considered. Use `0` for the first batch and the highest returned `ItemKey` for the next one. Example: `0`
`LocationIds`	varchar(max) string	NULL	Optional comma-separated list of location IDs (`LocID`). Only rows for these locations are returned. `NULL` or empty returns all locations that are not special stock. Example: `001,002`
`ItemCount`	int	1000	Maximum number of candidate items read from `items` per call (batch size). Example: `1000`

Result set 1 · Always (may be empty)

One row per item and location with supplier, price, stock and planning data, sorted by ItemKey.

Column	Type	Description
`ItemKey`	int	Internal item key.
`SupplPrice` Nullable	varchar(30) string	Purchase price of the supplier from `AddItems`, as text with comma as decimal separator.
`ITemName` Nullable	string	Item name with single and double quotes removed.
`VPE`	decimal	Packaging/order unit (`OrderUnit`), `0` if not set.
`MinStockQty`	decimal	Minimum stock of the item at this location, `0` if not set.
`MinOrderQty`	decimal	Minimum order quantity at this location, `0` if not set.
`SupplNr`	varchar(4) string	Supplier number (preferred supplier if defined, otherwise default supplier of the item).
`TontrText`	string	Text of the configuration type (`TontrArten`) of the item, `-` if none.
`ItemNrInt` Nullable	string	Internal item number.
`ItemNrSuppl` Nullable	string	Supplier item number from `AddItems`, falling back to the internal item number stored there.
`Quantity`	int	Total bin quantity over all non-special-stock locations; `0` if none or if the item has no order lines.
`ExportDate`	varchar(10) string	Current date in format `dd.mm.yyyy`.
`Typ`	varchar(4) string	Constant record type `0100`.
`SupplName` Nullable	string	Supplier name.
`CreateDate` Nullable	varchar(10) string	Creation date of the item in format `dd.mm.yyyy`.
`OrderDate`	varchar(10) string	Earliest order date of an order line for the item in format `dd.mm.yyyy`, empty if none.
`Tags`	varchar(4000) string	Comma-separated item tags (with leading space), empty if none.
`SupplierDeliveryDays`	int	Default delivery days of the supplier, `7` if not set.
`ACTIVE` Nullable	boolean	Enabled flag of the item (`Enabled`).
`Price` Nullable	varchar(30) string	Sales price of the item as text with comma as decimal separator.
`VATLevel`	varchar(1) string	VAT level: `L` (reduced, `MwStSatz = 2`), `0` (zero, `MwStSatz = 3`), otherwise `H` (full).
`IsBundle` Nullable	boolean	Indicates whether the item is a bundle.
`LocationId` Nullable	string	Location ID (`LocID`) of the location-specific data.
`LocationQuantity` Nullable	decimal	Location-specific quantity from `ItemLocationSpecific`.
`TargetStock`	decimal	Target stock at this location, `0` if not set.
`SafetyStock`	decimal	Safety stock at this location, `0` if not set.

Response example

<SqlRowSet1>
  <row>
    <ItemKey>1024</ItemKey>
    <SupplPrice>4,2500</SupplPrice>
    <ITemName>Kaffeebecher Keramik 300 ml</ITemName>
    <VPE>6</VPE>
    <MinStockQty>20</MinStockQty>
    <MinOrderQty>12</MinOrderQty>
    <SupplNr>L001</SupplNr>
    <TontrText>-</TontrText>
    <ItemNrInt>KB-300</ItemNrInt>
    <ItemNrSuppl>MUG-300-W</ItemNrSuppl>
    <Quantity>148</Quantity>
    <ExportDate>14.05.2024</ExportDate>
    <Typ>0100</Typ>
    <SupplName>Keramik Großhandel GmbH</SupplName>
    <CreateDate>03.02.2021</CreateDate>
    <OrderDate>11.02.2021</OrderDate>
    <Tags> Küche, Geschenk</Tags>
    <SupplierDeliveryDays>5</SupplierDeliveryDays>
    <ACTIVE>1</ACTIVE>
    <Price>9,9500</Price>
    <VATLevel>H</VATLevel>
    <IsBundle>0</IsBundle>
    <LocationId>001</LocationId>
    <LocationQuantity>148</LocationQuantity>
    <TargetStock>200</TargetStock>
    <SafetyStock>30</SafetyStock>
  </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:pixiDispoGetItems>
      <!-- <ItemKeyStart>0</ItemKeyStart> -->
      <!-- <LocationIds>001,002</LocationIds> -->
      <!-- <ItemCount>1000</ItemCount> -->
    </ns1:pixiDispoGetItems>
  </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:pixiDispoGetItems>
      <!-- <ItemKeyStart>0</ItemKeyStart> -->
      <!-- <LocationIds>001,002</LocationIds> -->
      <!-- <ItemCount>1000</ItemCount> -->
    </ns1:pixiDispoGetItems>
  </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 = [
    // 'ItemKeyStart' => '0',
    // 'LocationIds' => '001,002',
    // 'ItemCount' => '1000',
];

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

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

SQL object:: dbo.pipiDispoGetItems