Items Read

pixiReadAddItems

Returns additional item records (supplier-specific item data such as EAN, supplier item number and prices) from `AddItems`, filtered by key, date range and supplier.

Reads rows from the table AddItems. These rows hold supplier-specific item data: EAN/UPC, supplier item number, internal item number, prices, VAT rate and the preferred-supplier flag.

The procedure first prepares the parameters:

If RowCount is set and not 0, the number of returned rows is limited to RowCount.
If AddItemKey is not passed, it is set to 0.
If SupplNr is empty or contains only spaces, it is treated as not set.

It then runs exactly one of the following queries. The first branch whose condition matches wins:

Create or update date (CreateAndUpdateDateFrom and/or CreateAndUpdateDateTo set): returns records with AddItemKey > AddItemKey.
- The from bound matches if CreateDate or UpdateDate is >= CreateAndUpdateDateFrom.
- The to bound matches if CreateDate or UpdateDate is < CreateAndUpdateDateTo.
Create date (CreateDateFrom and/or CreateDateTo set): returns records with AddItemKey > AddItemKey and CreateDate >= from and < to.
Update date (UpdateDateFrom and/or UpdateDateTo set): returns records with AddItemKey > AddItemKey and UpdateDate >= from and < to.
Key range (RowCount passed, even as 0): returns records with AddItemKey > AddItemKey.
Single key (otherwise): returns the record with AddItemKey = AddItemKey.

In all branches the optional SupplNr filter limits the result to one supplier. Branches 1 to 4 are sorted by AddItemKey ascending. This allows paging: pass the last received AddItemKey together with RowCount to get the next page.

Notes

The table is read with NOLOCK, so uncommitted changes may be visible.
Without any parameter, AddItemKey becomes 0 and the single-key branch runs. This normally returns no rows.
Passing RowCount = 0 selects the key-range branch without a row limit. This returns all matching records, which can be slow on large tables.
The bounds of the combined create/update filter are each checked against either date column. A record can therefore match even if neither date lies inside the range, for example when CreateDate is before the range and UpdateDate is after it.
Only blank values of SupplNr are normalised. Leading spaces are not trimmed and can cause no match.
Date filters take precedence over each other in this order: combined create/update, create, update. Lower-priority date parameters are ignored when a higher-priority one is set.
All upper bounds are exclusive (<).
The header comment says "supplier data", but the procedure returns additional item records.
Column types are not visible in the code. Types marked unknown are assumed from the column names.

Tables used

AddItems · Read

Parameter	Type	Default	Description
`AddItemKey`	int	NULL	Key of the additional item. Without a date filter or `RowCount`, the record with exactly this key is returned. Otherwise only records with a larger key are returned (paging start). `NULL` is treated as `0`. Example: `0`
`CreateDateFrom`	datetime dateTime	NULL	Lower bound (inclusive) for `CreateDate`. Used only if no combined create/update date is set. Example: `2024-01-01T00:00:00`
`CreateDateTo`	datetime dateTime	NULL	Upper bound (exclusive) for `CreateDate`. Used only if no combined create/update date is set. Example: `2024-02-01T00:00:00`
`UpdateDateFrom`	datetime dateTime	NULL	Lower bound (inclusive) for `UpdateDate`. Used only if neither a combined date nor a create date is set. Example: `2024-01-01T00:00:00`
`UpdateDateTo`	datetime dateTime	NULL	Upper bound (exclusive) for `UpdateDate`. Used only if neither a combined date nor a create date is set. Example: `2024-02-01T00:00:00`
`CreateAndUpdateDateFrom`	datetime dateTime	NULL	Lower bound (inclusive). Matches if `CreateDate` or `UpdateDate` is on or after this value. Takes precedence over all other date parameters. Example: `2024-03-01T00:00:00`
`CreateAndUpdateDateTo`	datetime dateTime	NULL	Upper bound (exclusive). Matches if `CreateDate` or `UpdateDate` is before this value. Takes precedence over all other date parameters. Example: `2024-03-02T00:00:00`
`RowCount`	int	NULL	Maximum number of rows returned (via `SET ROWCOUNT`). If passed without a date filter, the key-range query runs (`AddItemKey` > given key). `0` means no limit. Example: `500`
`SupplNr`	varchar(4) string	NULL	Supplier number. If set, only records of this supplier are returned. Empty or blank values are ignored. Example: `LI01`

Result set 1 · Always; exactly one of the query branches runs. The result may be empty.

Additional item records from AddItems. Sorted by AddItemKey ascending, except in the single-key query.

Column	Type	Description
`AddItemKey`	int	Primary key of the additional item record.
`EANUPC` Nullable	string	EAN/UPC code.
`UPC` Nullable	string	UPC code.
`ItemNrSuppl` Nullable	string	Item number of the supplier.
`ItemNrInt` Nullable	string	Internal item number.
`ItemNrOld` Nullable	string	Old item number.
`SupplNr` Nullable	varchar(4) string	Supplier number.
`Price` Nullable	decimal	Price.
`SupplPrice` Nullable	decimal	Supplier price (purchase price).
`Curr_Source` Nullable	string	Source currency of the supplier price.
`MwStSatz` Nullable	decimal	VAT rate.
`AutoPrice` Nullable	string	Automatic price flag.
`CreateDate` Nullable	datetime dateTime	Creation date of the record.
`UpdateDate` Nullable	datetime dateTime	Date of the last change.
`ItemRef` Nullable	int	Reference to the Pixi item.
`LastImportDate` Nullable	dateTime	Date of the last import.
`SKU` Nullable	string	Stock keeping unit.
`PreferredSupplier` Nullable	string	Flag indicating whether this supplier is the preferred supplier for the item.
`Active` Nullable	string	Active flag of the record.

Response example

<SqlRowSet1>
  <row>
    <AddItemKey>1201</AddItemKey>
    <EANUPC>4006381333931</EANUPC>
    <UPC/>
    <ItemNrSuppl>SUP-77812</ItemNrSuppl>
    <ItemNrInt>100245</ItemNrInt>
    <ItemNrOld/>
    <SupplNr>LI01</SupplNr>
    <Price>19.99</Price>
    <SupplPrice>8.50</SupplPrice>
    <Curr_Source>EUR</Curr_Source>
    <MwStSatz>19.00</MwStSatz>
    <AutoPrice>N</AutoPrice>
    <CreateDate>2024-03-01T09:15:00</CreateDate>
    <UpdateDate>2024-03-05T14:20:00</UpdateDate>
    <ItemRef>5531</ItemRef>
    <LastImportDate>2024-03-05T14:20:00</LastImportDate>
    <SKU>SKU-100245</SKU>
    <PreferredSupplier>Y</PreferredSupplier>
    <Active>Y</Active>
  </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:pixiReadAddItems>
      <!-- <AddItemKey>0</AddItemKey> -->
      <!-- <CreateDateFrom>2024-01-01T00:00:00</CreateDateFrom> -->
      <!-- <CreateDateTo>2024-02-01T00:00:00</CreateDateTo> -->
      <!-- <UpdateDateFrom>2024-01-01T00:00:00</UpdateDateFrom> -->
      <!-- <UpdateDateTo>2024-02-01T00:00:00</UpdateDateTo> -->
      <!-- <CreateAndUpdateDateFrom>2024-03-01T00:00:00</CreateAndUpdateDateFrom> -->
      <!-- <CreateAndUpdateDateTo>2024-03-02T00:00:00</CreateAndUpdateDateTo> -->
      <!-- <RowCount>500</RowCount> -->
      <!-- <SupplNr>LI01</SupplNr> -->
    </ns1:pixiReadAddItems>
  </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:pixiReadAddItems>
      <!-- <AddItemKey>0</AddItemKey> -->
      <!-- <CreateDateFrom>2024-01-01T00:00:00</CreateDateFrom> -->
      <!-- <CreateDateTo>2024-02-01T00:00:00</CreateDateTo> -->
      <!-- <UpdateDateFrom>2024-01-01T00:00:00</UpdateDateFrom> -->
      <!-- <UpdateDateTo>2024-02-01T00:00:00</UpdateDateTo> -->
      <!-- <CreateAndUpdateDateFrom>2024-03-01T00:00:00</CreateAndUpdateDateFrom> -->
      <!-- <CreateAndUpdateDateTo>2024-03-02T00:00:00</CreateAndUpdateDateTo> -->
      <!-- <RowCount>500</RowCount> -->
      <!-- <SupplNr>LI01</SupplNr> -->
    </ns1:pixiReadAddItems>
  </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 = [
    // 'AddItemKey' => '0',
    // 'CreateDateFrom' => '2024-01-01T00:00:00',
    // 'CreateDateTo' => '2024-02-01T00:00:00',
    // 'UpdateDateFrom' => '2024-01-01T00:00:00',
    // 'UpdateDateTo' => '2024-02-01T00:00:00',
    // 'CreateAndUpdateDateFrom' => '2024-03-01T00:00:00',
    // 'CreateAndUpdateDateTo' => '2024-03-02T00:00:00',
    // 'RowCount' => '500',
    // 'SupplNr' => 'LI01',
];

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

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

SQL object:: dbo.pipiReadAddItems