pixiGetHistoricalSupplierPrices · Pixi API documentation

Returns the supplier prices that Pixi assigned to stock-in movements (FIFO pricing), together with the item, the quantity, the supplier and the reference document.

Validation (each failure returns a single Error row, see errors):

DateFrom and DateTo are mandatory although they default to NULL.
Both values are reduced to their date part. The end of the range is the day after DateTo, so DateTo is inclusive.
The range from DateFrom to DateTo may cover at most 31 calendar days (inclusive).
DateTo must be yesterday or earlier; data for the current day cannot be requested.

Selection:

If ItemKey is given, only movements of that item are returned; otherwise all items.
A record is included if either the stock-in date of the movement (DatePutToStock) or the last modification date of its price record lies within the range. Records whose price was recalculated in the range are therefore returned even if the item was put to stock earlier.
RefType describes the source the price was taken from; RefDocumentNumber is the number of the linked supplier delivery (the directly assigned delivery, or as fallback the delivery assigned later), if any.

Result: the rows are sorted by InvBinItemLogId. If nothing matches, a single Warning row is returned instead.

Notes

All tables are read with NOLOCK, so uncommitted data may appear.
Errors are not returned as SqlMessage: validation errors and any other runtime error are caught and returned as a normal row with the columns Staus (sic, misspelled) and Message. Check for a Staus column before reading data rows.
If DateFrom is later than DateTo, no validation error occurs; the call simply returns the Warning row.
The range end is compared with <= against midnight of the day after DateTo, so records stamped exactly at that midnight are included as well.
Whether a stock-in movement can have more than one price record (and thus appear in several rows) depends on the data in InvBinItemLog_In_Price and cannot be seen from the code.

Tables used

InvBinItemLog · Read InvBinItemLog_In_Price · Read InvBinItemLog_QuerySource · Read SDelHeader · Read

Parameter	Type	Default	Description
`ItemKey`	int	NULL	Internal item key (`ItemRef` of the stock movement). `NULL` returns movements of all items. Example: `4711`
`DateFrom`	datetime dateTime	NULL	Start of the date range (inclusive, time part is ignored). Effectively mandatory: if omitted, an `Error` row is returned. Example: `2024-05-01T00:00:00`
`DateTo`	datetime dateTime	NULL	End of the date range (inclusive, time part is ignored). Effectively mandatory; must be yesterday or earlier and at most 31 days (inclusive) after `DateFrom`. Example: `2024-05-31T00:00:00`

Result set 1 · If validation passes and at least one record matches

Stock-in movements with their FIFO supplier prices, sorted by InvBinItemLogId.

Column	Type	Description
`InvBinItemLogId`	int	Key of the stock movement (`InvBinItemLog`) the price belongs to.
`ItemID`	int	Internal item key.
`DatePutToStock` Nullable	dateTime	Date and time of the stock movement (`UpdateDate`).
`Quantity` Nullable	decimal	Quantity of the stock movement.
`SupplierPrice` Nullable	decimal	Supplier price assigned to the movement.
`SupplierId` Nullable	int	Key of the supplier the price comes from.
`RefType` Nullable	string	Description of the source the price was determined from (`MatchDescription` of `InvBinItemLog_QuerySource`).
`RefDocumentNumber` Nullable	string	Number of the linked supplier delivery (`SDelHNr`); `NULL` if no delivery is linked.

Result set 2 · If validation passes but no record matches

Single warning row.

Column	Type	Description
`Staus`	varchar(7) string	Always `Warning` (column name is misspelled in the code).
`Message`	nvarchar(46) string	Warning text.

Result set 3 · If validation fails or any other error occurs

Single error row with the error message.

Column	Type	Description
`Staus`	varchar(5) string	Always `Error` (column name is misspelled in the code).
`Message` Nullable	nvarchar(4000) string	Error message (`ERROR_MESSAGE()`).

Response example

<SqlRowSet1>
  <row>
    <InvBinItemLogId>1583321</InvBinItemLogId>
    <ItemID>4711</ItemID>
    <DatePutToStock>2024-05-06T09:14:22</DatePutToStock>
    <Quantity>24</Quantity>
    <SupplierPrice>12.50</SupplierPrice>
    <SupplierId>17</SupplierId>
    <RefType>Supplier delivery</RefType>
    <RefDocumentNumber>LS-2024-00871</RefDocumentNumber>
  </row>
  <row>
    <InvBinItemLogId>1583402</InvBinItemLogId>
    <ItemID>4711</ItemID>
    <DatePutToStock>2024-05-08T14:02:10</DatePutToStock>
    <Quantity>6</Quantity>
    <SupplierPrice>12.80</SupplierPrice>
    <SupplierId>17</SupplierId>
    <RefType>Last purchase price</RefType>
  </row>
</SqlRowSet1>

Status	Message	Cause
Error	Paramters @DateFrom and @DateTo are mandatory.	`DateFrom` or `DateTo` was not passed or is `NULL`. Always pass both dates.
Error	Maximum date range peroid is limited to 31 days.	The range from `DateFrom` to `DateTo` (inclusive) covers more than 31 days. Split the request into smaller ranges.
Error	It is not possible to get data for current day. Max value for paramter @DateTo is: "current date -1".	`DateTo` is today or in the future. Use yesterday or an earlier date.
Error	{ERROR_MESSAGE()}	Any other runtime error (e.g. invalid date conversion) is caught and returned with its original message.
Warning	No records found based on specified paramters.	No stock movement or price record matches the item and date range, or `DateFrom` is later than `DateTo`.

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:pixiGetHistoricalSupplierPrices>
      <!-- <ItemKey>4711</ItemKey> -->
      <!-- <DateFrom>2024-05-01T00:00:00</DateFrom> -->
      <!-- <DateTo>2024-05-31T00:00:00</DateTo> -->
    </ns1:pixiGetHistoricalSupplierPrices>
  </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:pixiGetHistoricalSupplierPrices>
      <!-- <ItemKey>4711</ItemKey> -->
      <!-- <DateFrom>2024-05-01T00:00:00</DateFrom> -->
      <!-- <DateTo>2024-05-31T00:00:00</DateTo> -->
    </ns1:pixiGetHistoricalSupplierPrices>
  </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' => '4711',
    // 'DateFrom' => '2024-05-01T00:00:00',
    // 'DateTo' => '2024-05-31T00:00:00',
];

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

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