Suppliers Read

pixiGetDeliveryItem

Returns the lines of supplier deliveries (goods receipts) with item data, prices, tags and batch details, filtered by delivery, item and date range.

Reads delivery lines from SDelDetail together with their delivery header (SDelHeader) and returns one row per line.

Parameter normalisation

0 for SDelHKey/DeliveryNumber and empty strings for ItemNrInternal/EANUPC are treated as not set.
RowCount defaults to 100 and Offset to 0 if passed as NULL.

Resolving the filters

Item: ItemNrInternal is looked up as an internal item number and EANUPC as an EAN code in the item code table. If both are given, the first item that matches either code is used.
Delivery: if SDelHKey is not set, DeliveryNumber is resolved to the key of the first delivery with that number. If SDelHKey is set, DeliveryNumber is ignored.
Start date: if DateFrom is empty and a delivery is known, the start date is the day of the oldest line of that delivery. Otherwise it defaults to 700 days before today.
End date: DateTo defaults to 365 days after today.

Selection Lines are selected when all of these apply:

SDelDkey > Offset.
They belong to the resolved delivery, if any.
They belong to the resolved item, if any.
Their creation date is in the range DateFrom (inclusive) to DateTo (exclusive).

The first RowCount lines in ascending SDelDkey order are taken. Only lines that also exist in the goods receipt view pvGoodsReceiptItemDbo are returned; that view supplies the internal supplier order number.

Enrichment

Item master data (internal and supplier item number, EAN, picture links) comes from items.
DeliveryItemNrSuppl is the supplier item number of the delivering supplier from AddItems.
ItemTAGs lists all tag names of the item, separated by | and sorted alphabetically.
BatchManagementDetails is filled only for items tagged with tag -6 or -7 (batch/best-before management). It contains an XML fragment with one row element per bin item log entry of that delivery and item that carries a batch number or best-before date. Each row has the attribute BinItemLogId and the child elements BinItemLogId, LocationId, BinRef, ItemRef, DeliveredQty, BatchNumber and BestBeforeDate.

Paging Paging is keyset-based. To get the next page, pass the highest SDelDkey of the previous page as Offset.

Notes

All reads use NOLOCK, so uncommitted data can appear.
Unresolved filters: if DeliveryNumber, ItemNrInternal or EANUPC cannot be resolved, the corresponding filter is silently dropped. In that case the call returns lines of all deliveries or items in the date range instead of an empty result.
Delivery without lines: if a delivery key is known but DateFrom is empty and the delivery has no lines, the start date becomes NULL and no rows are returned.
DateTo is exclusive and not extended to the end of the day. A date-only value such as 2024-05-31 excludes that day.
Default start date: the default is 700 days back, not exactly two years.
Offset is not a row count to skip. It is compared with SDelDkey.
Sort order: the final result has no ORDER BY, so the output order is not guaranteed. Sort by SDelDkey on the client side if needed.
Fewer rows than RowCount: lines whose item does not exist in items are dropped after the RowCount limit has been applied, so a page can contain fewer rows.
Duplicate rows: if AddItems holds several rows for the same item and supplier, a line can be returned more than once.
Batch details per delivery, not per line: bin item log entries are matched by delivery and item only. If an item occurs on several lines of one delivery, each line shows all entries. DeliveredQty inside the XML is converted to an integer.

Tables used

ItemCode · Read SDelHeader · Read SDelDetail · Read pvGoodsReceiptItemDbo · Read TagMatch · Read Tag · Read InvBinItemLog · Read items · Read AddItems · Read

Parameter	Type	Default	Description
`SDelHKey`	int	NULL	Internal key of the supplier delivery (goods receipt header). `0` or NULL means no delivery filter. Takes precedence over `DeliveryNumber`. Example: `15432`
`DeliveryNumber`	int	NULL	Delivery number (`SDelHNr`). Used only if `SDelHKey` is not set and is resolved to the first matching delivery. If no delivery matches, no delivery filter is applied. Example: `20240315`
`ItemNrInternal`	varchar(50) string	NULL	Internal item number used to filter by item. An empty string means no filter. If the number is not found, no item filter is applied. Example: `ART-10045`
`EANUPC`	varchar(13) string	NULL	EAN/UPC code used to filter by item. If combined with `ItemNrInternal`, the first item matching either code is used. Example: `4006381333931`
`RowCount`	int	100	Maximum number of delivery lines to return (page size). NULL is treated as 100. Example: `100`
`Offset`	int	0	Keyset paging cursor. Only lines with `SDelDkey` greater than this value are returned; pass the last `SDelDkey` of the previous page. Example: `0`
`DateFrom`	datetime dateTime	NULL	Lower bound (inclusive) for the line creation date. Default: the day of the oldest line of the given delivery, otherwise 700 days before today. Example: `2024-01-01T00:00:00`
`DateTo`	datetime dateTime	NULL	Upper bound (exclusive) for the line creation date. Default: 365 days after today. Example: `2024-04-01T00:00:00`

Result set 1 · Always (may be empty).

One row per supplier delivery line, with header data, item data, prices, tags and batch information.

Column	Type	Description
`SDelDkey` Nullable	int	Key of the delivery line; use as paging cursor.
`DeliveryNumber` Nullable	int	Delivery number (`SDelHNr`).
`SDelHKey` Nullable	int	Key of the delivery header.
`ItemKey` Nullable	int	Internal item key.
`ItemNrInt` Nullable	string	Internal item number from the item master.
`ItemNrSuppl` Nullable	string	Supplier item number from the item master.
`DeliveryItemNrSuppl` Nullable	string	Supplier item number of the delivering supplier (from `AddItems`).
`ItemName` Nullable	varchar(200) string	Item name as stored on the delivery line.
`EANUPC` Nullable	string	EAN/UPC of the item.
`DeliveredQty` Nullable	money decimal	Delivered quantity.
`OrderedQty` Nullable	money decimal	Ordered quantity.
`MidPrice` Nullable	money decimal	Average price of the line (`SDelDsMidPrice`).
`Discount` Nullable	money decimal	Discount of the line.
`EK` Nullable	money decimal	Purchase price of the line.
`FinalPrice` Nullable	money decimal	Final purchase price after discount.
`ItemTAGs` Nullable	varchar(8000) string	Tag names of the item, separated by `\|`, sorted alphabetically.
`PicLinkLarge` Nullable	string	Link to the large item picture.
`PicLinkSmall` Nullable	string	Link to the small item picture.
`DeliveryCreateDate` Nullable	datetime dateTime	Creation date of the delivery header.
`DeliveryItemCreateDate` Nullable	datetime dateTime	Creation date of the delivery line.
`DeliveryItemLastUpdateDate` Nullable	datetime dateTime	Last update date of the delivery line.
`DeliveryStatus` Nullable	varchar(3) string	Status code of the delivery header.
`BatchNumber` Nullable	varchar(50) string	Batch number stored on the delivery line.
`BestBeforeDate` Nullable	datetime dateTime	Best-before date stored on the delivery line.
`BatchManagementDetails` Nullable	xml	XML fragment of `row` elements with bin item log entries (bin, location, quantity, batch, best-before date) for items tagged `-6`/`-7`; NULL otherwise.
`InternalNumber` Nullable	varchar(50) string	Internal number of the supplier order the line belongs to.
`ExternalReceiptNr` Nullable	varchar(50) string	External receipt number of the delivery.

Response example

<SqlRowSet1>
  <row>
    <SDelDkey>88231</SDelDkey>
    <DeliveryNumber>20240315</DeliveryNumber>
    <SDelHKey>15432</SDelHKey>
    <ItemKey>4711</ItemKey>
    <ItemNrInt>ART-10045</ItemNrInt>
    <ItemNrSuppl>SUP-998</ItemNrSuppl>
    <DeliveryItemNrSuppl>SUP-998</DeliveryItemNrSuppl>
    <ItemName>Bio Olive Oil 500ml</ItemName>
    <EANUPC>4006381333931</EANUPC>
    <DeliveredQty>24.0000</DeliveredQty>
    <OrderedQty>24.0000</OrderedQty>
    <MidPrice>4.1000</MidPrice>
    <Discount>0.0000</Discount>
    <EK>4.2000</EK>
    <FinalPrice>4.2000</FinalPrice>
    <ItemTAGs>Batch|Food</ItemTAGs>
    <PicLinkLarge>https://shop.example.com/img/4711_l.jpg</PicLinkLarge>
    <PicLinkSmall>https://shop.example.com/img/4711_s.jpg</PicLinkSmall>
    <DeliveryCreateDate>2024-03-15T08:12:00</DeliveryCreateDate>
    <DeliveryItemCreateDate>2024-03-15T08:14:31</DeliveryItemCreateDate>
    <DeliveryItemLastUpdateDate>2024-03-15T09:02:10</DeliveryItemLastUpdateDate>
    <DeliveryStatus>BOO</DeliveryStatus>
    <BatchNumber>L2403-17</BatchNumber>
    <BestBeforeDate>2025-09-30T00:00:00</BestBeforeDate>
    <BatchManagementDetails>
      <row BinItemLogId="551203">
        <BinItemLogId>551203</BinItemLogId>
        <LocationId>001</LocationId>
        <BinRef>3021</BinRef>
        <ItemRef>4711</ItemRef>
        <DeliveredQty>24</DeliveredQty>
        <BatchNumber>L2403-17</BatchNumber>
        <BestBeforeDate>2025-09-30T00:00:00</BestBeforeDate>
      </row>
    </BatchManagementDetails>
    <InternalNumber>PO-2024-0312</InternalNumber>
    <ExternalReceiptNr>LS-778812</ExternalReceiptNr>
  </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:pixiGetDeliveryItem>
      <!-- <SDelHKey>15432</SDelHKey> -->
      <!-- <DeliveryNumber>20240315</DeliveryNumber> -->
      <!-- <ItemNrInternal>ART-10045</ItemNrInternal> -->
      <!-- <EANUPC>4006381333931</EANUPC> -->
      <!-- <RowCount>100</RowCount> -->
      <!-- <Offset>0</Offset> -->
      <!-- <DateFrom>2024-01-01T00:00:00</DateFrom> -->
      <!-- <DateTo>2024-04-01T00:00:00</DateTo> -->
    </ns1:pixiGetDeliveryItem>
  </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:pixiGetDeliveryItem>
      <!-- <SDelHKey>15432</SDelHKey> -->
      <!-- <DeliveryNumber>20240315</DeliveryNumber> -->
      <!-- <ItemNrInternal>ART-10045</ItemNrInternal> -->
      <!-- <EANUPC>4006381333931</EANUPC> -->
      <!-- <RowCount>100</RowCount> -->
      <!-- <Offset>0</Offset> -->
      <!-- <DateFrom>2024-01-01T00:00:00</DateFrom> -->
      <!-- <DateTo>2024-04-01T00:00:00</DateTo> -->
    </ns1:pixiGetDeliveryItem>
  </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 = [
    // 'SDelHKey' => '15432',
    // 'DeliveryNumber' => '20240315',
    // 'ItemNrInternal' => 'ART-10045',
    // 'EANUPC' => '4006381333931',
    // 'RowCount' => '100',
    // 'Offset' => '0',
    // 'DateFrom' => '2024-01-01T00:00:00',
    // 'DateTo' => '2024-04-01T00:00:00',
];

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

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

SQL object:: dbo.pipiGetDeliveryItem