Orders Read

pixiGetOrderHeadersForCustomer

Returns the order headers of a customer, identified by customer key or by external customer number or e-mail, with payment, voucher and shipping data.

Returns one row per order (OrderHeader) of a customer, sorted by OrderDate descending.

Customer selection

If CustKey is not 0, only orders with CustRef = CustKey are returned. CustomerNrExternal and ShopID are ignored in this case.
If CustKey is 0 (default), orders of all customers whose CustomerNrExternal or e-mail address (CeMail) equals CustomerNrExternal are returned. If ShopID is given, the customer must also belong to that shop (CustomerOf). An empty ShopID is treated as NULL (no shop filter).

Further filters

OrderDateFrom: only orders with OrderDate on or after this value. NULL or empty means 1900-01-01.
OrderStatus: OPEN, HOLD or CLOSED filters on the order status. Any other value (or NULL) disables the status filter.

Returned data per order

Header data such as order number, external order number, totals, ship cost, shop note, address remarks and currency code (OrderCurrencyCode from the currency of the order).
Payment data from the payment address (AddrRef): payment type, credit card and bank account fields, ZIP.
ShopID is the shop the customer belongs to (CustomerOf).
OrderLocked is the lock flag (ASlocked) of the shipping address, N if not set.
VoucherID / VoucherAmount: highest voucher ID and highest voucher amount found on the order lines (vouchers referenced by order lines); NULL if the order has no voucher.
Shipdate / LastShipdate: earliest and latest scan-out date of all invoices linked to the order lines.
TrackingID / ShipVendor: taken from the invoice with the highest InvoiceKey; the tracking ID falls back to the drop-shipment tracking ID of the order line if the invoice has none. NULL if the order has not been invoiced.

Notes

All tables are read WITH (NOLOCK); results may contain uncommitted data.
If CustKey is 0 and CustomerNrExternal is left empty, the filter matches customers with an empty external customer number or an empty e-mail address. Always pass either CustKey or a non-empty CustomerNrExternal.
Because the e-mail address is also matched, several customers (e.g. in different shops) can match; use ShopID to restrict.
The intermediate temporary table has unique indexes on OrderNr and AddrRef. If two selected orders share the same payment address or more than one has OrderNr NULL, the call fails with a SQL duplicate-key error.
VoucherID and VoucherAmount are aggregated independently with MAX; with several vouchers on one order they may come from different vouchers.
If the latest invoice has several lines with different fallback tracking IDs, the selection is not deterministic and an order may in rare cases appear more than once.
The result contains sensitive payment data (card number, bank account).

Tables used

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

Parameter	Type	Default	Description
`CustKey`	int	0	Internal customer key. If not `0`, orders are selected by this key and `CustomerNrExternal`/`ShopID` are ignored. Example: `10234`
`CustomerNrExternal`	varchar(60) string	–	External customer number or customer e-mail address. Used only if `CustKey` is `0`. Example: `max.mustermann@example.com`
`ShopID`	varchar(3) string	NULL	Shop the customer must belong to. Used only if `CustKey` is `0`; empty or `NULL` means all shops. Example: `SH1`
`OrderStatus`	varchar(10) string	NULL	Optional filter on the order status. Other values than the listed ones disable the filter. Allowed values `OPEN` – Only open orders `HOLD` – Only orders on hold `CLOSED` – Only closed orders Example: `OPEN`
`OrderDateFrom`	datetime dateTime	1900-01-01	Only orders with an order date on or after this value. `NULL` or empty means `1900-01-01`. Example: `2024-01-01T00:00:00`

Result set 1 · Always (may be empty)

One row per matching order, sorted by OrderDate descending.

Column	Type	Description
`OrderHeaderKey`	int	Internal key of the order
`OrderNr` Nullable	int	Pixi order number
`CreateDate` Nullable	datetime dateTime	Creation date of the order record
`OrderNrExternal` Nullable	varchar(50) string	External (shop) order number
`OrderTotal` Nullable	money decimal	Order total
`OrderLinesTotal` Nullable	money decimal	Total of the order lines
`WholeOrderState` Nullable	varchar(6) string	Order status 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(8000) string	Tracking ID of the latest invoice, or drop-shipment tracking ID of the order line as fallback
`ShipVendor` Nullable	varchar(3) string	Shipping vendor of the latest invoice
`ShopID` Nullable	varchar(3) string	Shop the customer belongs to (`CustomerOf`)
`OrderDate` Nullable	datetime dateTime	Order date
`ShipCost` Nullable	money decimal	Shipping cost of the order
`PaymentAddressId` Nullable	int	Key of the payment (billing) address
`ShipmentAddressId` Nullable	int	Key of the shipping address
`ShopNote` Nullable	varchar(max) string	Note from the shop
`PaymentType` Nullable	char(1) string	Payment type of the billing address
`VoucherID` Nullable	varchar(50) string	Highest voucher ID used in the order lines
`VoucherAmount` Nullable	money decimal	Highest voucher amount of the vouchers used in the order lines
`CcCardType` Nullable	varchar(2) string	Credit card type
`CcCardNr` Nullable	varchar(25) string	Credit card number
`CcCardName` Nullable	varchar(60) string	Credit card holder
`CcCardExp` Nullable	varchar(4) string	Credit card expiry
`Account` Nullable	varchar(10) string	Bank account number
`Blz` Nullable	varchar(8) string	Bank code (BLZ)
`BankName` Nullable	varchar(50) string	Bank name
`AccountName` Nullable	varchar(27) string	Account holder
`AddressRemarks` Nullable	varchar(255) string	Address remarks of the order
`AddrRef` Nullable	int	Key of the billing address (same as `PaymentAddressId`)
`ZIP` Nullable	varchar(10) string	ZIP code of the billing address
`CustKey` Nullable	int	Internal customer key
`OrderLocked` Nullable	varchar(1) string	Lock flag of the shipping address, `N` if not set
`OrderCurrencyCode` Nullable	varchar(3) string	Currency code of the order (e.g. `EUR`)

Response example

<SqlRowSet1>
  <row>
    <OrderHeaderKey>558812</OrderHeaderKey>
    <OrderNr>400123</OrderNr>
    <CreateDate>2024-03-11T09:14:22</CreateDate>
    <OrderNrExternal>WEB-100245</OrderNrExternal>
    <OrderTotal>89.9000</OrderTotal>
    <OrderLinesTotal>84.9000</OrderLinesTotal>
    <WholeOrderState>CLOSED</WholeOrderState>
    <Shipdate>2024-03-12T15:02:10</Shipdate>
    <LastShipdate>2024-03-12T15:02:10</LastShipdate>
    <TrackingID>00340434161094042557</TrackingID>
    <ShipVendor>DHL</ShipVendor>
    <ShopID>SH1</ShopID>
    <OrderDate>2024-03-11T09:10:00</OrderDate>
    <ShipCost>5.0000</ShipCost>
    <PaymentAddressId>912345</PaymentAddressId>
    <ShipmentAddressId>912346</ShipmentAddressId>
    <ShopNote/>
    <PaymentType>R</PaymentType>
    <AddressRemarks/>
    <AddrRef>912345</AddrRef>
    <ZIP>80331</ZIP>
    <CustKey>10234</CustKey>
    <OrderLocked>N</OrderLocked>
    <OrderCurrencyCode>EUR</OrderCurrencyCode>
  </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:pixiGetOrderHeadersForCustomer>
      <!-- <CustKey>10234</CustKey> -->
      <!-- <CustomerNrExternal>max.mustermann@example.com</CustomerNrExternal> -->
      <!-- <ShopID>SH1</ShopID> -->
      <!-- <OrderStatus>OPEN</OrderStatus> -->
      <!-- <OrderDateFrom>2024-01-01T00:00:00</OrderDateFrom> -->
    </ns1:pixiGetOrderHeadersForCustomer>
  </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:pixiGetOrderHeadersForCustomer>
      <!-- <CustKey>10234</CustKey> -->
      <!-- <CustomerNrExternal>max.mustermann@example.com</CustomerNrExternal> -->
      <!-- <ShopID>SH1</ShopID> -->
      <!-- <OrderStatus>OPEN</OrderStatus> -->
      <!-- <OrderDateFrom>2024-01-01T00:00:00</OrderDateFrom> -->
    </ns1:pixiGetOrderHeadersForCustomer>
  </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 = [
    // 'CustKey' => '10234',
    // 'CustomerNrExternal' => 'max.mustermann@example.com',
    // 'ShopID' => 'SH1',
    // 'OrderStatus' => 'OPEN',
    // 'OrderDateFrom' => '2024-01-01T00:00:00',
];

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

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

SQL object:: dbo.pipiGetOrderHeadersForCustomer