Orders Read

pixiGetOrdersByDeliveryDate

Returns orders with their shipping address whose delivery date (ShipDate) is a given number of days ahead, equals a given date, or lies in the future.

Selects orders from OrderHeader joined with their shipping address (Addresses via ShipAdrRef) and returns order number, order date, delivery date, shipping vendor and the shipping address fields.

The date filter is a combination of three alternatives joined with OR:

DaysToDeliveryDate: orders whose ShipDate lies exactly this many calendar days after today (day boundaries via DATEDIFF(DAY, ...); 0 = today, negative values = past days).
DeliveryDate: orders whose ShipDate is exactly equal to the given datetime value.
If both DaysToDeliveryDate and DeliveryDate are omitted, all orders whose ShipDate is later than the current date and time are returned.

If both date parameters are passed, orders matching either of them are returned.

Additional filters (combined with AND):

ShipVendor: only orders with this ShipVendorCode.
ShopID: only orders whose shipping address has this AddrShopID.

No sort order is defined; rows are returned in arbitrary order. There is no paging.

Notes

DeliveryDate is compared with ShipDate by exact equality including the time part. If ShipDate contains a time component, pass the exact value or use DaysToDeliveryDate instead.
The shop filter uses the shop of the shipping address (AddrShopID), not a shop field of the order header.
Orders without a matching shipping address are not returned (inner join).
The order status is not filtered, so cancelled or already shipped orders can be included.
Without any parameters, all orders with a future delivery date are returned, which can be a large result.
The header comment of the procedure ("Template for getting items...") is a leftover and does not describe the procedure.

Tables used

OrderHeader · Read Addresses · Read

Parameter	Type	Default	Description
`DaysToDeliveryDate`	int	NULL	Number of calendar days from today to the delivery date (`ShipDate`). `0` = today, `1` = tomorrow. Combined with `DeliveryDate` by OR. Example: `2`
`DeliveryDate`	datetime dateTime	NULL	Exact delivery date; matched by equality with `ShipDate` including the time part. Combined with `DaysToDeliveryDate` by OR. Example: `2024-06-14T00:00:00`
`ShipVendor`	varchar(3) string	NULL	Shipping vendor code (`ShipVendorCode`) to filter by. `NULL` = all vendors. Example: `DHL`
`ShopID`	varchar(3) string	NULL	Shop ID of the shipping address (`AddrShopID`) to filter by. `NULL` = all shops. Example: `SHP`

Result set 1 · Always (may be empty)

One row per matching order with its shipping address.

Column	Type	Description
`OrderNr`	int	Order number from `OrderHeader`.
`OrderDate` Nullable	dateTime	Order date.
`DeliveryDate` Nullable	dateTime	Delivery date (`OrderHeader.ShipDate`).
`ShipVendorCode` Nullable	string	Shipping vendor code of the order.
`eMail` Nullable	string	E-mail address of the shipping address.
`FirstName` Nullable	string	First name of the shipping address.
`LastName` Nullable	string	Last name of the shipping address.
`Name` Nullable	string	Name field of the shipping address (e.g. company).
`CareOf` Nullable	string	Care-of line of the shipping address.
`Address` Nullable	string	Address line of the shipping address.
`Street` Nullable	string	Street of the shipping address.
`HouseNr` Nullable	string	House number of the shipping address.
`ZIP` Nullable	string	Postal code of the shipping address.
`City` Nullable	string	City of the shipping address.
`Country` Nullable	string	Country of the shipping address.
`AddrShopID` Nullable	string	Shop ID of the shipping address.

Response example

<SqlRowSet1>
  <row>
    <OrderNr>104523</OrderNr>
    <OrderDate>2024-06-10T09:15:00</OrderDate>
    <DeliveryDate>2024-06-14T00:00:00</DeliveryDate>
    <ShipVendorCode>DHL</ShipVendorCode>
    <eMail>anna.mueller@example.de</eMail>
    <FirstName>Anna</FirstName>
    <LastName>Müller</LastName>
    <Name>Anna Müller</Name>
    <CareOf/>
    <Address>Hauptstraße 12</Address>
    <Street>Hauptstraße</Street>
    <HouseNr>12</HouseNr>
    <ZIP>80331</ZIP>
    <City>München</City>
    <Country>DE</Country>
    <AddrShopID>SHP</AddrShopID>
  </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:pixiGetOrdersByDeliveryDate>
      <!-- <DaysToDeliveryDate>2</DaysToDeliveryDate> -->
      <!-- <DeliveryDate>2024-06-14T00:00:00</DeliveryDate> -->
      <!-- <ShipVendor>DHL</ShipVendor> -->
      <!-- <ShopID>SHP</ShopID> -->
    </ns1:pixiGetOrdersByDeliveryDate>
  </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:pixiGetOrdersByDeliveryDate>
      <!-- <DaysToDeliveryDate>2</DaysToDeliveryDate> -->
      <!-- <DeliveryDate>2024-06-14T00:00:00</DeliveryDate> -->
      <!-- <ShipVendor>DHL</ShipVendor> -->
      <!-- <ShopID>SHP</ShopID> -->
    </ns1:pixiGetOrdersByDeliveryDate>
  </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 = [
    // 'DaysToDeliveryDate' => '2',
    // 'DeliveryDate' => '2024-06-14T00:00:00',
    // 'ShipVendor' => 'DHL',
    // 'ShopID' => 'SHP',
];

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

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

SQL object:: dbo.pipiGetOrdersByDeliveryDate