Suppliers Read

pixiGetDelivery

Returns supplier delivery headers (goods receipts), filtered by supplier, delivery number, item, location, status, dates and Picco lock, with key-based paging.

Returns rows from the supplier delivery header table (SDelHeader) together with the supplier name from Suppliers (left join on SDelHSupplNr).

Parameter handling

Empty strings are treated as NULL for all text filters. For SDelHKey and DeliveryNumber, the value 0 is treated as "no filter".
RowCount defaults to 100. NULL and 0 also mean 100.
Offset is not a row offset. It is a key cursor: only deliveries with SDelHKey > Offset are returned. 0 or NULL means no cursor.

Filters (all combined with AND)

SDelHKey, SupplNr, DeliveryNumber (SDelHNr), LocID (SDelHLocationID) and ExternalReceiptNr: exact match.
Status: by default, closed deliveries (SDelHStatus = CLS) and invoiced deliveries (SDelHStatus = INV) are excluded. Set Closed = 1 and/or Invoice = 1 to include them.
Creation date: SDelHCreateDate between CreateDateFrom and CreateDateTo. If CreateDateTo has the time 00:00:00, it is moved to the end of that day.
Update date: SDelHUpdateDate between UpdateDateFrom and UpdateDateTo, with the same end-of-day rule.
Item filter: if at least one of ItemNrInt, ItemNrSuppl, EANUPC or ItemName is set, only deliveries that contain at least one delivery line (SDelDetail) for a matching item are returned. All given item values must match exactly on the same item.
Picco user: if PiccoUser is set, deliveries locked by Picco (LockedByPicco = Y) for a different Picco user are excluded. Unlocked deliveries and deliveries locked by the given user are returned.

Sorting and paging The result is sorted by SDelHKey ascending, and at most RowCount rows are returned. To read the next page, pass the last SDelHKey received as Offset.

Notes

All tables are read WITH (NOLOCK). Results can contain uncommitted data.
Date ranges need both bounds. If only CreateDateFrom or only CreateDateTo is set, the comparison uses BETWEEN with NULL and no rows are returned. The same applies to the update date pair.
The end-of-day adjustment uses fPixi_GetDateEndOfDay. Its definition is not attached, so the exact end-of-day time is not known. CreateDateFrom/UpdateDateFrom are used as passed; they are not moved to the start of the day.
Deliveries locked by Picco with an empty PiccoUser value are also excluded when PiccoUser is passed.
A negative RowCount causes a SQL error from TOP.
The item filter uses exact equality; ItemName does not support wildcards.

Tables used

SDelHeader · Read Suppliers · Read SDelDetail · Read items · Read

Parameter	Type	Default	Description
`SDelHKey`	int	NULL	Internal key of a supplier delivery. `0` or NULL means no filter. Example: `15234`
`SupplNr`	varchar(200) string	NULL	Supplier number (`SDelHSupplNr`), exact match. Example: `L1001`
`DeliveryNumber`	int	NULL	Delivery number (`SDelHNr`). `0` or NULL means no filter. Example: `4711`
`ItemNrInt`	varchar(200) string	NULL	Internal item number. Returns only deliveries with a line for this item. Example: `ART-10045`
`ItemNrSuppl`	varchar(200) string	NULL	Supplier item number. Returns only deliveries with a line for a matching item. Example: `SUP-998877`
`EANUPC`	varchar(200) string	NULL	EAN/UPC of the item. Returns only deliveries with a line for a matching item. Example: `4006381333931`
`ItemName`	varchar(200) string	NULL	Item name, exact match. Returns only deliveries with a line for a matching item. Example: `Kugelschreiber blau`
`RowCount`	int	NULL	Maximum number of rows. NULL or `0` means 100. Example: `100`
`Offset`	int	NULL	Key cursor for paging: only deliveries with `SDelHKey` greater than this value are returned. Pass the last `SDelHKey` of the previous page. Example: `15200`
`Closed`	bit boolean	0	Include closed deliveries (status `CLS`). Allowed values `0` – Exclude deliveries with status `CLS` `1` – Include deliveries with status `CLS` Example: `0`
`Invoice`	bit boolean	0	Include invoiced deliveries (status `INV`). Allowed values `0` – Exclude deliveries with status `INV` `1` – Include deliveries with status `INV` Example: `0`
`LocID`	varchar(3) string	NULL	Location ID (`SDelHLocationID`), exact match. Example: `001`
`PiccoUser`	varchar(50) string	NULL	Picco user. If set, deliveries locked by Picco for another user are excluded. Example: `lager01`
`CreateDateFrom`	datetime dateTime	NULL	Start of the creation date range. Must be used together with `CreateDateTo`. Example: `2024-03-01T00:00:00`
`CreateDateTo`	datetime dateTime	NULL	End of the creation date range. If the time is 00:00:00, it is moved to the end of that day. Example: `2024-03-31T00:00:00`
`UpdateDateFrom`	datetime dateTime	NULL	Start of the update date range. Must be used together with `UpdateDateTo`. Example: `2024-03-01T00:00:00`
`UpdateDateTo`	datetime dateTime	NULL	End of the update date range. If the time is 00:00:00, it is moved to the end of that day. Example: `2024-03-31T00:00:00`
`ExternalReceiptNr`	varchar(50) string	NULL	External receipt number, exact match. Example: `WE-2024-00315`

Result set 1 · Always (may be empty)

Supplier delivery headers matching the filters, sorted by SDelHKey ascending, at most RowCount rows.

Column	Type	Description
`SDelHKey`	int	Internal key of the supplier delivery.
`SDelHSupplNr` Nullable	string	Supplier number.
`SDelHDate` Nullable	dateTime	Delivery date.
`SDelHLocationID` Nullable	string	Location ID of the delivery.
`SDelHNr` Nullable	int	Delivery number.
`SDelHNote` Nullable	string	Note on the delivery.
`SDelHStatus` Nullable	string	Delivery status, e.g. `CLS` (closed) or `INV` (invoiced).
`SDelHCreateDate` Nullable	datetime dateTime	Creation date.
`SDelHCreateEmp` Nullable	string	Employee who created the delivery.
`SDelHUpdateDate` Nullable	datetime dateTime	Date of the last change.
`SDelHUpdateEmp` Nullable	string	Employee who last changed the delivery.
`SDelHextInvoiceNr` Nullable	string	External (supplier) invoice number.
`SDelHextOrderNr` Nullable	string	External order number.
`SDelHfinalDicsount` Nullable	decimal	Final discount of the delivery (column name as spelled in the database).
`PiccoUser` Nullable	string	Picco user who holds the lock.
`LockedByPicco` Nullable	string	`Y` if the delivery is locked by Picco, otherwise `N` or empty.
`SupplName` Nullable	string	Supplier name from `Suppliers`; empty if the supplier does not exist.
`ExternalReceiptNr` Nullable	string	External receipt number.

Response example

<SqlRowSet1>
  <row>
    <SDelHKey>15234</SDelHKey>
    <SDelHSupplNr>L1001</SDelHSupplNr>
    <SDelHDate>2024-03-12T00:00:00</SDelHDate>
    <SDelHLocationID>001</SDelHLocationID>
    <SDelHNr>4711</SDelHNr>
    <SDelHNote>Teillieferung</SDelHNote>
    <SDelHStatus>OPN</SDelHStatus>
    <SDelHCreateDate>2024-03-12T08:15:22</SDelHCreateDate>
    <SDelHCreateEmp>MM</SDelHCreateEmp>
    <SDelHUpdateDate>2024-03-12T09:40:03</SDelHUpdateDate>
    <SDelHUpdateEmp>MM</SDelHUpdateEmp>
    <SDelHextInvoiceNr>RE-88231</SDelHextInvoiceNr>
    <SDelHextOrderNr>B-2024-118</SDelHextOrderNr>
    <SDelHfinalDicsount>0</SDelHfinalDicsount>
    <PiccoUser>lager01</PiccoUser>
    <LockedByPicco>N</LockedByPicco>
    <SupplName>Muster Bürobedarf GmbH</SupplName>
    <ExternalReceiptNr>WE-2024-00315</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:pixiGetDelivery>
      <!-- <SDelHKey>15234</SDelHKey> -->
      <!-- <SupplNr>L1001</SupplNr> -->
      <!-- <DeliveryNumber>4711</DeliveryNumber> -->
      <!-- <ItemNrInt>ART-10045</ItemNrInt> -->
      <!-- <ItemNrSuppl>SUP-998877</ItemNrSuppl> -->
      <!-- <EANUPC>4006381333931</EANUPC> -->
      <!-- <ItemName>Kugelschreiber blau</ItemName> -->
      <!-- <RowCount>100</RowCount> -->
      <!-- <Offset>15200</Offset> -->
      <!-- <Closed>0</Closed> -->
      <!-- <Invoice>0</Invoice> -->
      <!-- <LocID>001</LocID> -->
      <!-- <PiccoUser>lager01</PiccoUser> -->
      <!-- <CreateDateFrom>2024-03-01T00:00:00</CreateDateFrom> -->
      <!-- <CreateDateTo>2024-03-31T00:00:00</CreateDateTo> -->
      <!-- <UpdateDateFrom>2024-03-01T00:00:00</UpdateDateFrom> -->
      <!-- <UpdateDateTo>2024-03-31T00:00:00</UpdateDateTo> -->
      <!-- <ExternalReceiptNr>WE-2024-00315</ExternalReceiptNr> -->
    </ns1:pixiGetDelivery>
  </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:pixiGetDelivery>
      <!-- <SDelHKey>15234</SDelHKey> -->
      <!-- <SupplNr>L1001</SupplNr> -->
      <!-- <DeliveryNumber>4711</DeliveryNumber> -->
      <!-- <ItemNrInt>ART-10045</ItemNrInt> -->
      <!-- <ItemNrSuppl>SUP-998877</ItemNrSuppl> -->
      <!-- <EANUPC>4006381333931</EANUPC> -->
      <!-- <ItemName>Kugelschreiber blau</ItemName> -->
      <!-- <RowCount>100</RowCount> -->
      <!-- <Offset>15200</Offset> -->
      <!-- <Closed>0</Closed> -->
      <!-- <Invoice>0</Invoice> -->
      <!-- <LocID>001</LocID> -->
      <!-- <PiccoUser>lager01</PiccoUser> -->
      <!-- <CreateDateFrom>2024-03-01T00:00:00</CreateDateFrom> -->
      <!-- <CreateDateTo>2024-03-31T00:00:00</CreateDateTo> -->
      <!-- <UpdateDateFrom>2024-03-01T00:00:00</UpdateDateFrom> -->
      <!-- <UpdateDateTo>2024-03-31T00:00:00</UpdateDateTo> -->
      <!-- <ExternalReceiptNr>WE-2024-00315</ExternalReceiptNr> -->
    </ns1:pixiGetDelivery>
  </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' => '15234',
    // 'SupplNr' => 'L1001',
    // 'DeliveryNumber' => '4711',
    // 'ItemNrInt' => 'ART-10045',
    // 'ItemNrSuppl' => 'SUP-998877',
    // 'EANUPC' => '4006381333931',
    // 'ItemName' => 'Kugelschreiber blau',
    // 'RowCount' => '100',
    // 'Offset' => '15200',
    // 'Closed' => '0',
    // 'Invoice' => '0',
    // 'LocID' => '001',
    // 'PiccoUser' => 'lager01',
    // 'CreateDateFrom' => '2024-03-01T00:00:00',
    // 'CreateDateTo' => '2024-03-31T00:00:00',
    // 'UpdateDateFrom' => '2024-03-01T00:00:00',
    // 'UpdateDateTo' => '2024-03-31T00:00:00',
    // 'ExternalReceiptNr' => 'WE-2024-00315',
];

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

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

SQL object:: dbo.pipiGetDelivery