pixiGetEKPriceHistory · Pixi API documentation

Reads price history records from ItemsHist and returns them page by page.

Filters

ItemNrInternal: if not empty, only history rows whose internal item number (ItemNrInt) equals this value are returned. Empty or NULL returns all items.
FromDate / ToDate: filter on UpdateDate (inclusive on both ends).
- both set: UpdateDate between FromDate and ToDate
- only FromDate: UpdateDate >= FromDate
- only ToDate: UpdateDate <= ToDate
- none: no date filter

Sort order and paging

Rows are numbered with RowNumber (starting at 1) ordered by UpdateDate, then item key, then purchase price, all ascending (oldest change first).
Only rows with RowNumber between StartRow and StartRow + RowCount (both inclusive) are returned, sorted by RowNumber.
RowCount = 0 is treated as 100.

For each history row the current supplier number (SupplNr) is joined from the item master Items.

Notes

Paging overlap: the page range is inclusive on both ends. With StartRow = 0 and RowCount = 100 rows 1–100 are returned; with StartRow = 100 rows 100–200 (101 rows) are returned, so consecutive pages share one row unless StartRow is advanced by RowCount + 1.
A code comment mentions a fallback of 1000 rows, but the code actually uses 100 when RowCount is 0.
The empty-string default of the datetime parameters is converted to 1900-01-01; passing exactly that date is treated as "no filter".
ToDate is compared including the time part. A date without time (e.g. 2024-06-30T00:00:00) excludes changes later that day.
History rows with NULL in UpdateDate or ItemNrInt are never returned, because the filter conditions are always evaluated.
SupplNr is the supplier currently stored in Items, not the supplier at the time of the change; it is NULL if the item no longer exists.
No NOLOCK hints are used. Without an item filter the numbering runs over the whole history table, which can be slow on large data sets.

Tables used

ItemsHist · Read Items · Read

Parameter	Type	Default	Description
`ItemNrInternal`	varchar(250) string	–	Internal item number to filter on (`ItemNrInt`). Empty or `NULL` returns the history of all items. Example: `4711-BLK`
`FromDate`	datetime dateTime	–	Lower bound (inclusive) for `UpdateDate`. Empty, `NULL` or `1900-01-01` means no lower bound. Example: `2024-01-01T00:00:00`
`ToDate`	datetime dateTime	–	Upper bound (inclusive, including time) for `UpdateDate`. Empty, `NULL` or `1900-01-01` means no upper bound. Example: `2024-06-30T23:59:59`
`RowCount`	int	100	Page size offset. Rows from `StartRow` to `StartRow + RowCount` are returned. `0` is treated as 100. Example: `100`
`StartRow`	int	0	First `RowNumber` of the page (inclusive). Row numbering starts at 1. Example: `0`

Result set 1 · Always (may be empty)

One row per matching price history record within the requested page, ordered by RowNumber.

Column	Type	Description
`RowNumber`	bigint long	Sequential number of the row over the whole filtered result (ordered by update date, item key, purchase price).
`ItemKey` Nullable	int	Internal item key (`ItemRef` in `ItemsHist`).
`SupplNr` Nullable	string	Supplier number currently stored in the item master; `NULL` if the item no longer exists.
`ItemNrSuppl` Nullable	string	Supplier item number from the history record.
`ItemNrInt`	string	Internal item number.
`EANUPC` Nullable	string	EAN/UPC of the item from the history record.
`Price` Nullable	decimal	Sales price stored in the history record.
`EKPrice` Nullable	decimal	Purchase price (`SupplPrice`) stored in the history record.
`UpdateDate`	dateTime	Date and time of the change recorded in the history.

Response example

<SqlRowSet1>
  <row>
    <RowNumber>1</RowNumber>
    <ItemKey>1523</ItemKey>
    <SupplNr>70012</SupplNr>
    <ItemNrSuppl>SUP-88213</ItemNrSuppl>
    <ItemNrInt>4711-BLK</ItemNrInt>
    <EANUPC>4006381333931</EANUPC>
    <Price>19.9900</Price>
    <EKPrice>8.4500</EKPrice>
    <UpdateDate>2024-02-14T09:31:22</UpdateDate>
  </row>
  <row>
    <RowNumber>2</RowNumber>
    <ItemKey>1523</ItemKey>
    <SupplNr>70012</SupplNr>
    <ItemNrSuppl>SUP-88213</ItemNrSuppl>
    <ItemNrInt>4711-BLK</ItemNrInt>
    <EANUPC>4006381333931</EANUPC>
    <Price>21.9900</Price>
    <EKPrice>9.1000</EKPrice>
    <UpdateDate>2024-05-03T14:05:10</UpdateDate>
  </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:pixiGetEKPriceHistory>
      <!-- <ItemNrInternal>4711-BLK</ItemNrInternal> -->
      <!-- <FromDate>2024-01-01T00:00:00</FromDate> -->
      <!-- <ToDate>2024-06-30T23:59:59</ToDate> -->
      <!-- <RowCount>100</RowCount> -->
      <!-- <StartRow>0</StartRow> -->
    </ns1:pixiGetEKPriceHistory>
  </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:pixiGetEKPriceHistory>
      <!-- <ItemNrInternal>4711-BLK</ItemNrInternal> -->
      <!-- <FromDate>2024-01-01T00:00:00</FromDate> -->
      <!-- <ToDate>2024-06-30T23:59:59</ToDate> -->
      <!-- <RowCount>100</RowCount> -->
      <!-- <StartRow>0</StartRow> -->
    </ns1:pixiGetEKPriceHistory>
  </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 = [
    // 'ItemNrInternal' => '4711-BLK',
    // 'FromDate' => '2024-01-01T00:00:00',
    // 'ToDate' => '2024-06-30T23:59:59',
    // 'RowCount' => '100',
    // 'StartRow' => '0',
];

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

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