Orders Read

pixiGetOrderHeadersByOrderDate

Returns order headers with payment, shipping, voucher and tracking data for all orders that have order lines with an order date in the given range.

Returns one row per order header for all orders that have at least one order line whose Orderdate lies between FromDate and ToDate (inclusive, BETWEEN).

Filter

ShopID restricts the order lines to those whose CustomerOF equals the given shop. An empty string is treated like NULL, which means all shops.
Only orders for which a customer, a payment address (AddrRef) and a currency exist are returned (inner joins on Customers, Addresses and pixi.Currency).

Building the result

Basic data is taken from the order header, the customer, the payment address (bank and credit card data, ZIP) and the currency (CurrencyCode). OrderLocked is the lock flag of the shipping address, N if none is found.
PaymentType is filled from the payment type of the payment address.
OrderNrExternal, OrderDate, VoucherID and VoucherAmount are taken from the order lines (and the voucher), ShipCost from the order header.
TrackingID (shipment tracking ID, otherwise the Direct-Fulfillment tracking ID) and ShipVendor are taken from the invoices of the order's lines.
Shipdate is the earliest and LastShipdate the latest scan-out date of all invoices belonging to the order's lines.

The result is sorted by OrderDate ascending. There is no paging.

Notes

All reads use NOLOCK, so uncommitted data may be returned.
BETWEEN is inclusive: if ToDate is a date without time (midnight), order lines later on that day are not included. Pass e.g. 2024-01-31T23:59:59 to cover the whole day.
The shop filter applies to CustomerOF of the order lines, while the returned ShopID is the CustomerOf of the customer.
For orders with several lines or several invoices, OrderNrExternal, OrderDate, VoucherID, VoucherAmount, TrackingID and ShipVendor are taken from an arbitrary matching row. OrderDate may therefore come from a line outside the requested date range.
Shipdate, LastShipdate, TrackingID and ShipVendor are NULL as long as no invoice exists.
The result contains sensitive payment data (credit card number, bank account) of the payment address.
No paging: large date ranges can produce large results and run slowly.

Tables used

OrderHeader · Read Customers · Read Addresses · Read Currency · Read Orderlines · Read Voucher · Read Invoices · Read InvLines · Read

Parameter	Type	Default	Description
`FromDate` Required	datetime dateTime	–	Start of the range for the order date of the order lines (inclusive). Example: `2024-01-01T00:00:00`
`ToDate` Required	datetime dateTime	–	End of the range for the order date of the order lines (inclusive). Example: `2024-01-31T23:59:59`
`ShopID`	varchar(3) string	NULL	Shop ID used to filter the order lines (`CustomerOF`). `NULL` or an empty string returns orders of all shops. Example: `SHP`

Result set 1 · Always (may be empty).

One row per order header matching the filter, sorted by OrderDate ascending.

Column	Type	Description
`OrderHeaderKey`	int	Internal key of the order header.
`OrderNr`	int	Pixi order number.
`CreateDate` Nullable	dateTime	Creation date of the order header.
`OrderNrExternal` Nullable	varchar(50) string	External (shop) order number from an order line.
`OrderTotal` Nullable	money decimal	Order total from the order header.
`OrderlinesTotal` Nullable	money decimal	Total of the order lines from the order header.
`WholeOrderState` Nullable	varchar(6) string	Status of the whole order (`OrderStatus` of the order header).
`Shipdate` Nullable	datetime dateTime	Earliest scan-out date of the invoices of the order.
`LastShipdate` Nullable	datetime dateTime	Latest scan-out date of the invoices of the order.
`TrackingID` Nullable	varchar(512) string	Shipment tracking ID of an invoice, otherwise its Direct-Fulfillment tracking ID.
`ShipVendor` Nullable	varchar(3) string	Shipping vendor of an invoice of the order.
`ShopID` Nullable	string	Shop of the customer (`CustomerOf`).
`OrderDate` Nullable	datetime dateTime	Order date from an order line of the order.
`ShipCost` Nullable	money decimal	Shipping costs from the order header.
`PaymentAddressId` Nullable	int	Key of the payment address (`AddrRef`).
`ShipmentAddressId` Nullable	int	Key of the shipping address (`ShipAdrRef`).
`ShopNote` Nullable	string	Shop note of the order.
`PaymentType` Nullable	char(1) string	Payment type of the payment address (`APayment`).
`VoucherID` Nullable	varchar(50) string	Voucher ID from an order line.
`VoucherAmount` Nullable	money decimal	Amount of the voucher.
`CcCardType` Nullable	string	Credit card type of the payment address.
`CcCardNr` Nullable	string	Credit card number of the payment address.
`CcCardName` Nullable	string	Credit card holder name.
`CcCardExp` Nullable	string	Credit card expiry.
`Account` Nullable	string	Bank account number of the payment address.
`Blz` Nullable	string	Bank code (BLZ).
`BankName` Nullable	string	Name of the bank.
`AccountName` Nullable	string	Account holder name.
`AddressRemarks` Nullable	string	Address remarks of the order.
`AddrRef` Nullable	int	Key of the payment address (same as `PaymentAddressId`).
`ZIP` Nullable	string	ZIP code of the payment address.
`CustKey`	int	Key of the customer.
`OrderLocked`	string	Lock flag (`ASlocked`) of the shipping address, `N` if no shipping address is found.
`CurrencyCode` Nullable	string	Alphabetic currency code of the order.

Response example

<SqlRowSet1>
  <row>
    <OrderHeaderKey>48211</OrderHeaderKey>
    <OrderNr>100532</OrderNr>
    <CreateDate>2024-01-15T10:22:31</CreateDate>
    <OrderNrExternal>WEB-778812</OrderNrExternal>
    <OrderTotal>129.9000</OrderTotal>
    <OrderlinesTotal>124.9000</OrderlinesTotal>
    <WholeOrderState>AUS</WholeOrderState>
    <Shipdate>2024-01-16T14:05:00</Shipdate>
    <LastShipdate>2024-01-16T14:05:00</LastShipdate>
    <TrackingID>00340434161094042557</TrackingID>
    <ShipVendor>DHL</ShipVendor>
    <ShopID>SHP</ShopID>
    <OrderDate>2024-01-15T10:20:00</OrderDate>
    <ShipCost>4.9900</ShipCost>
    <PaymentAddressId>77120</PaymentAddressId>
    <ShipmentAddressId>77121</ShipmentAddressId>
    <ShopNote>Bitte klingeln</ShopNote>
    <PaymentType>R</PaymentType>
    <AddressRemarks/>
    <AddrRef>77120</AddrRef>
    <ZIP>10115</ZIP>
    <CustKey>30455</CustKey>
    <OrderLocked>N</OrderLocked>
    <CurrencyCode>EUR</CurrencyCode>
  </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:pixiGetOrderHeadersByOrderDate>
      <FromDate>2024-01-01T00:00:00</FromDate>
      <ToDate>2024-01-31T23:59:59</ToDate>
      <!-- <ShopID>SHP</ShopID> -->
    </ns1:pixiGetOrderHeadersByOrderDate>
  </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:pixiGetOrderHeadersByOrderDate>
      <FromDate>2024-01-01T00:00:00</FromDate>
      <ToDate>2024-01-31T23:59:59</ToDate>
      <!-- <ShopID>SHP</ShopID> -->
    </ns1:pixiGetOrderHeadersByOrderDate>
  </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 = [
    'FromDate' => '2024-01-01T00:00:00',
    'ToDate' => '2024-01-31T23:59:59',
    // 'ShopID' => 'SHP',
];

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

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

SQL object:: dbo.pipiGetOrderHeadersByOrderDate