pixiGetHistoricalSupplierPriceMatching · Pixi API documentation

Returns the historical FIFO matching between stock receipts (with their supplier price) and stock issues (with their selling price), based on the inventory bin item log.

Parameter validation

DateFrom and DateTo must both be passed, even though they are declared with a NULL default.
Both values are reduced to their date part. The end of the range is DateTo + 1 day.
The range from DateFrom to DateTo + 1 day must not exceed 31 days.
DateTo must be at most yesterday; data for the current day cannot be requested.

Selection

If ItemKey is passed, only that item is returned; otherwise all items.
A receipt is included if its creation date or the modification date of its supplier price record lies between DateFrom and DateTo + 1 day (both bounds inclusive, compared as dates at midnight).
Receipts are combined with issues in two ways, and the results of both are added together:
1. issues that reference the receipt record directly,
2. issues that match the receipt by item and FIFO row number.
For each match the supplier price, the selling price, the source descriptions of both prices, the delivery (own delivery or assigned delivery) and the sales document line (invoice line or POS line) are collected.

Output

If no rows were found, a single row with Staus = Warning is returned.
Otherwise the data rows are returned, sorted by InvBinItemLogId. The supplier delivery note number is looked up from the supplier delivery; the sell document date and number come from the invoice or, if there is none, from the POS receipt.
Any error, including the validation errors, is returned as a row with Staus = Error and the error text in Message.

Notes

The status column is spelled Staus (not Status) in both the warning and the error row.
Errors are never returned as SqlMessage; the RAISERROR calls are caught and returned as a normal row. Check the first column name of the result to distinguish data from status rows.
The upper bound is inclusive at midnight of DateTo + 1 day, so records created exactly at 00:00 on the following day are also included.
If DateFrom is later than DateTo, no validation error occurs; the call simply returns the Warning row.
A receipt/issue pair may appear twice if it matches both join paths (direct reference and item + row number); rows are not de-duplicated.
All tables are read with NOLOCK, so uncommitted data may be returned.
The temporary table is dropped via util.TempTableDrop, whose definition is not attached.

Tables used

InvBinItemLog_In · Read InvBinItemLog · Read InvBinItemLog_In_Price · Read InvBinItemLog_QuerySource · Read InvBinItemLog_Out · Read InvBinItemLog_Out_Price · Read SDelHeader · Read Invlines · Read Invoices · Read POSitem2 · Read POSheader2 · Read

Parameter	Type	Default	Description
`ItemKey`	int	NULL	Key of the item to restrict the result to. `NULL` returns data for all items. Example: `4711`
`DateFrom`	datetime dateTime	NULL	Start of the date range (date part only). Mandatory in practice: if omitted, an error row is returned. Example: `2024-03-01T00:00:00`
`DateTo`	datetime dateTime	NULL	End of the date range (date part only, inclusive). Must be at most yesterday, and the range from `DateFrom` to `DateTo` + 1 day must not exceed 31 days. Mandatory in practice. Example: `2024-03-30T00:00:00`

Result set 1 · If matching records were found

FIFO matches of stock receipts and issues with supplier and selling prices, sorted by InvBinItemLogId.

Column	Type	Description
`InvBinItemLogId`	int	Key of the stock receipt log entry.
`ItemID`	int	Key of the item.
`DatePutToStock` Nullable	dateTime	Date the goods were put to stock (update date of the log entry).
`Quantity` Nullable	int	Quantity of the issue matched to this receipt.
`SupplierPrice` Nullable	decimal	Supplier purchase price of the receipt.
`SupplierId` Nullable	int	Key of the supplier.
`RefTypeSupplierPrice` Nullable	string	Description of the source from which the supplier price was determined.
`RefDocumentNumberSupplierPrice` Nullable	string	Delivery note number of the supplier delivery (`SDelHNr`); `NULL` if no delivery is linked.
`SellDocumentDate` Nullable	dateTime	Date of the invoice or, if none, creation date of the POS receipt.
`SellingPrice` Nullable	decimal	Selling price of the issue.
`RefTypeSellingPrice` Nullable	string	Description of the source from which the selling price was determined.
`RefDocumentNumberSellingPrice` Nullable	string	Invoice number or, if none, POS document number.

Result set 2 · If no matching records were found

Single warning row.

Column	Type	Description
`Staus`	varchar(7) string	Always `Warning`.
`Message`	nvarchar(46) string	Warning text.

Result set 3 · On validation error or any other error

Single error row with the error message.

Column	Type	Description
`Staus`	varchar(5) string	Always `Error`.
`Message` Nullable	nvarchar(4000) string	Error message.

Response example

<SqlRowSet1>
  <row>
    <InvBinItemLogId>1583321</InvBinItemLogId>
    <ItemID>4711</ItemID>
    <DatePutToStock>2024-03-04T09:15:22</DatePutToStock>
    <Quantity>2</Quantity>
    <SupplierPrice>12.50</SupplierPrice>
    <SupplierId>17</SupplierId>
    <RefTypeSupplierPrice>Supplier delivery</RefTypeSupplierPrice>
    <RefDocumentNumberSupplierPrice>LS-2024-0815</RefDocumentNumberSupplierPrice>
    <SellDocumentDate>2024-03-12T14:02:10</SellDocumentDate>
    <SellingPrice>24.90</SellingPrice>
    <RefTypeSellingPrice>Invoice</RefTypeSellingPrice>
    <RefDocumentNumberSellingPrice>RE240312001</RefDocumentNumberSellingPrice>
  </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` + 1 day exceeds 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 at most yesterday's date.
Warning	No records found based on specified paramters.	No matching receipt/issue records exist for the item and date range.
Error	{ERROR_MESSAGE()}	Any other SQL error during execution; the original SQL Server error text is returned.

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

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

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