Orders Read

pixiGetOrderHeader

Returns order headers with customer, billing and shipping address, invoice, shipment, voucher and tag data, selected by order number, external order number or filters.

The call selects order headers in one of three modes and returns one row per order.

Parameter normalisation

OrderNr = 0, CustKey = 0 and empty strings for OrderNrExternal, ShopID, PaymentType, ShippingCountry are treated as not set.
Date parameters (FromTime, UntilTime, FromUpdateTime) that lie more than 100 years in the past (e.g. 1900-01-01) are treated as not set.
RowCount = NULL becomes 1000.

Selection modes (the first matching rule wins)

OrderNr set: the order with this OrderNr. Optionally restricted by CustKey and ShopID (the customer's CustomerOf). All other filters are ignored.
OrderNrExternal set: orders with this external order number, optionally restricted by CustKey and ShopID. All other filters are ignored.
Any of FromTime, UntilTime, ShopID, PaymentType, ShippingCountry, CustKey, FromUpdateTime set: filter search, combined with AND:
- OrderDate > FromTime and OrderDate < UntilTime (both exclusive)
- UpdateDate > FromUpdateTime (exclusive)
- CustRef = CustKey
- customer's CustomerOf = ShopID
- country of the shipping address = ShippingCountry
- payment type (APayment) of the billing address = PaymentType
At most RowCount orders are taken, oldest CreateDate first.
No parameter set: an empty result is returned (backward compatibility).

Returned data

Header fields from the order, customer data, billing address payment/bank data and shipping address name/address data.
Aggregates over the order lines: Shipdate/LastShipdate (earliest/latest ScanOutDate of linked invoices), TrackingID, ShipVendor, LastInvoiceNr (highest invoice number), VoucherID (highest voucher ID on the lines) with VoucherAmount from the voucher table, EstimatedDelivery (latest estimated delivery date) and TotalDiscount (sum of DiscountValue × Qty for lines not in status STO or NLB).
Only invoices that are not credit notes (no reference to another invoice) are considered. Lines in status MIN are always considered; other lines only if the invoice line quantity is > 0 or no invoice line exists.
Gender is derived from the salutation of the shipping address: 1 for Herr, Hr., M., Monsieur, Mr, Mr.; 2 for Mrs, Mrs., Ms, Ms., Frau, Fr.; otherwise 0.
Tags contains the names of the order's tags, sorted by name and separated by |.

The rows are sorted by CreateDate ascending and limited to RowCount. If no order matches, an empty result set with the same columns is returned.

Notes

All tables are read WITH (NOLOCK); uncommitted data may be returned.
The filter search (mode 3) is built as dynamic SQL; only the set filters are added to the WHERE clause.
For paging/incremental export use FromUpdateTime or FromTime together with RowCount. Note that the selection is ordered by CreateDate, not by UpdateDate, and the time filters are exclusive (>/<).
RowCount = 0 returns no rows; a negative value causes a SQL error from TOP.
Orders without matching currency, customer, billing address or shipping address record are not returned (inner joins). Orders without order lines are returned with empty aggregate columns.
If an order line is linked to several invoice lines, its discount is counted once per invoice line in TotalDiscount.
OrderTotal_OrderCurr contains the same value as OrderTotal. OrderLocked comes from the lock flag of the shipping address (N if empty). CustomerNr is the customer's DATEV account number.
The settings NumberOfDecimalPlacesForPrices and UseInitialOrderDate are read but do not affect the result.
If the call runs longer than 5 seconds, the parameters and the dynamic SQL are passed to LOG.EventDurationInsert (definition not attached; presumably writes a duration log entry).

Tables used

OrderHeader · Read Customers · Read Addresses · Read Orderlines · Read Orderline_EstimatedDelivery · Read Invlines · Read Invoices · Read Voucher · Read Currency · Read Settings · Read Tag · Read TagMatch · Read

Parameter	Type	Default	Description
`OrderNr`	int	0	Pixi order number. If set (not 0), exactly this order is searched (mode 1); only `CustKey` and `ShopID` are applied as additional restrictions. Example: `100234`
`OrderNrExternal`	varchar(50) string	–	External order number (e.g. shop order number). Used if `OrderNr` is not set (mode 2); only `CustKey` and `ShopID` are applied as additional restrictions. Example: `SHOP-2024-004711`
`CustKey`	int	NULL	Customer key. Restricts the result to orders of this customer. 0 is treated as not set. Example: `5821`
`FromTime`	datetime dateTime	NULL	Filter search only: returns orders with `OrderDate` later than this value (exclusive). Dates more than 100 years in the past are ignored. Example: `2024-05-01T00:00:00`
`UntilTime`	datetime dateTime	NULL	Filter search only: returns orders with `OrderDate` earlier than this value (exclusive). Dates more than 100 years in the past are ignored. Example: `2024-06-01T00:00:00`
`ShopID`	char(3) string	NULL	Shop ID. Restricts the result to orders of customers whose `CustomerOf` equals this value. Applied in all modes. Example: `SHP`
`PaymentType`	char(1) string	NULL	Filter search only: payment type code of the billing address (`APayment`). Example: `V`
`ShippingCountry`	varchar(3) string	NULL	Filter search only: country code of the shipping address. Example: `DE`
`RowCount`	int	1000	Maximum number of orders returned. NULL is treated as 1000. Example: `500`
`FromUpdateTime`	datetime dateTime	NULL	Filter search only: returns orders with `UpdateDate` later than this value (exclusive). Dates more than 100 years in the past are ignored. Example: `2024-05-15T08:30:00`

Result set 1 · Always; empty (same columns) if no order matches or no selection parameter is set.

One row per order, sorted by CreateDate ascending, at most RowCount rows.

Column	Type	Description
`OrderHeaderKey`	int	Internal key of the order header.
`OrderNr` Nullable	int	Pixi order number.
`CreateDate` Nullable	datetime dateTime	Creation date of the order in Pixi.
`OrderNrExternal` Nullable	varchar(50) string	External order number.
`OrderTotal` Nullable	money decimal	Order total.
`OrderlinesTotal` Nullable	money decimal	Total of the order lines.
`WholeOrderState` Nullable	varchar(10) string	Status of the order (`OrderStatus`).
`Shipdate` Nullable	datetime dateTime	Earliest scan-out date of the linked invoices.
`LastShipdate` Nullable	datetime dateTime	Latest scan-out date of the linked invoices.
`TrackingID` Nullable	varchar(8000) string	Shipment tracking ID of the linked invoices (highest value).
`ShipVendor` Nullable	varchar(3) string	Shipping vendor of the linked invoices (highest value).
`ShopID` Nullable	varchar(3) string	Shop of the customer (`CustomerOf`).
`OrderDate` Nullable	datetime dateTime	Order date.
`ShipCost` Nullable	money decimal	Shipping costs.
`PaymentAddressId`	int	Key of the billing address.
`ShipmentAddressId`	int	Key of the shipping address.
`ShopNote` Nullable	varchar(max) string	Note from the shop.
`PaymentType` Nullable	varchar(1) string	Payment type of the billing address.
`VoucherID` Nullable	varchar(50) string	Voucher ID used in the order lines (highest value).
`VoucherAmount` Nullable	money decimal	Amount of the voucher from the voucher table.
`CcCardType` Nullable	varchar(2) string	Credit card type of the billing address.
`CcCardNr` Nullable	varchar(25) string	Credit card number of the billing address.
`CcCardName` Nullable	varchar(60) string	Name of the credit card holder.
`CcCardExp` Nullable	varchar(4) string	Credit card expiry.
`Account`	varchar(10) string	Bank account number of the billing address; empty string if not set.
`Blz`	varchar(8) string	Bank code of the billing address; empty string if not set.
`BankName` Nullable	varchar(50) string	Bank name of the billing address.
`AccountName` Nullable	varchar(27) string	Account holder of the billing address.
`AddressRemarks` Nullable	varchar(255) string	Address remarks of the order.
`CustKey`	int	Customer key.
`SalutationText` Nullable	varchar(60) string	Salutation of the shipping address.
`Gender`	int	Gender derived from the salutation: 1 = male, 2 = female, 0 = unknown.
`LastName` Nullable	varchar(60) string	Last name of the shipping address.
`FirstName` Nullable	varchar(60) string	First name of the shipping address.
`ShippingCountry` Nullable	varchar(3) string	Country of the shipping address.
`ZIP` Nullable	varchar(10) string	Postal code of the shipping address.
`City` Nullable	varchar(50) string	City of the shipping address.
`Street` Nullable	varchar(100) string	Street of the shipping address.
`HouseNr` Nullable	varchar(12) string	House number of the shipping address.
`BirthDate` Nullable	datetime dateTime	Birth date stored at the shipping address.
`CustomerNr` Nullable	varchar(10) string	Customer number (DATEV account number of the customer).
`SubShopName` Nullable	varchar(50) string	Sub-shop name of the order.
`SubShopLogo` Nullable	varchar(50) string	Sub-shop logo of the order.
`Referrer` Nullable	varchar(50) string	Referrer of the order.
`GiftMessage` Nullable	varchar(8000) string	Gift message.
`OrderCurrency` Nullable	varchar(3) string	Currency code of the order.
`OrderTotal_OrderCurr` Nullable	money decimal	Order total; same value as `OrderTotal`.
`CustomerEmail` Nullable	varchar(256) string	E-mail address of the customer.
`CustomerNrExternal` Nullable	varchar(60) string	External customer number.
`EstimatedDelivery` Nullable	datetime dateTime	Latest estimated delivery date of the order lines; NULL if none.
`OrderLocked`	varchar(1) string	Lock flag of the shipping address; `N` if not set.
`ShipInstruct` Nullable	varchar(255) string	Shipping instructions.
`OrderShipVendor` Nullable	varchar(3) string	Shipping vendor code stored on the order.
`DeliveryDate` Nullable	datetime dateTime	Ship date stored on the order header (`Shipdate`).
`LastInvoiceNr` Nullable	varchar(20) string	Highest invoice number linked to the order (credit notes excluded).
`TotalDiscount` Nullable	money decimal	Sum of discount × quantity of the order lines not in status `STO` or `NLB`.
`OrderType` Nullable	varchar(3) string	Order type.
`Tags` Nullable	varchar(8000) string	Names of the order's tags, sorted by name and separated by `\|`; NULL if none.

Response example

<SqlRowSet1>
  <row>
    <OrderHeaderKey>48211</OrderHeaderKey>
    <OrderNr>100234</OrderNr>
    <CreateDate>2024-05-14T10:22:31</CreateDate>
    <OrderNrExternal>SHOP-2024-004711</OrderNrExternal>
    <OrderTotal>89.9000</OrderTotal>
    <OrderlinesTotal>84.9000</OrderlinesTotal>
    <WholeOrderState>AUF</WholeOrderState>
    <Shipdate>2024-05-15T14:03:12</Shipdate>
    <LastShipdate>2024-05-15T14:03:12</LastShipdate>
    <TrackingID>00340434161094015902</TrackingID>
    <ShipVendor>DHL</ShipVendor>
    <ShopID>SHP</ShopID>
    <OrderDate>2024-05-14T10:20:00</OrderDate>
    <ShipCost>5.0000</ShipCost>
    <PaymentAddressId>77120</PaymentAddressId>
    <ShipmentAddressId>77121</ShipmentAddressId>
    <PaymentType>V</PaymentType>
    <Account/>
    <Blz/>
    <CustKey>5821</CustKey>
    <SalutationText>Frau</SalutationText>
    <Gender>2</Gender>
    <LastName>Müller</LastName>
    <FirstName>Anna</FirstName>
    <ShippingCountry>DE</ShippingCountry>
    <ZIP>80331</ZIP>
    <City>München</City>
    <Street>Marienplatz</Street>
    <HouseNr>1</HouseNr>
    <CustomerNr>10025821</CustomerNr>
    <OrderCurrency>EUR</OrderCurrency>
    <OrderTotal_OrderCurr>89.9000</OrderTotal_OrderCurr>
    <CustomerEmail>anna.mueller@example.com</CustomerEmail>
    <OrderLocked>N</OrderLocked>
    <LastInvoiceNr>RE240512</LastInvoiceNr>
    <TotalDiscount>0.0000</TotalDiscount>
    <OrderType>STD</OrderType>
    <Tags>Express|VIP</Tags>
  </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:pixiGetOrderHeader>
      <!-- <OrderNr>100234</OrderNr> -->
      <!-- <OrderNrExternal>SHOP-2024-004711</OrderNrExternal> -->
      <!-- <CustKey>5821</CustKey> -->
      <!-- <FromTime>2024-05-01T00:00:00</FromTime> -->
      <!-- <UntilTime>2024-06-01T00:00:00</UntilTime> -->
      <!-- <ShopID>SHP</ShopID> -->
      <!-- <PaymentType>V</PaymentType> -->
      <!-- <ShippingCountry>DE</ShippingCountry> -->
      <!-- <RowCount>500</RowCount> -->
      <!-- <FromUpdateTime>2024-05-15T08:30:00</FromUpdateTime> -->
    </ns1:pixiGetOrderHeader>
  </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:pixiGetOrderHeader>
      <!-- <OrderNr>100234</OrderNr> -->
      <!-- <OrderNrExternal>SHOP-2024-004711</OrderNrExternal> -->
      <!-- <CustKey>5821</CustKey> -->
      <!-- <FromTime>2024-05-01T00:00:00</FromTime> -->
      <!-- <UntilTime>2024-06-01T00:00:00</UntilTime> -->
      <!-- <ShopID>SHP</ShopID> -->
      <!-- <PaymentType>V</PaymentType> -->
      <!-- <ShippingCountry>DE</ShippingCountry> -->
      <!-- <RowCount>500</RowCount> -->
      <!-- <FromUpdateTime>2024-05-15T08:30:00</FromUpdateTime> -->
    </ns1:pixiGetOrderHeader>
  </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 = [
    // 'OrderNr' => '100234',
    // 'OrderNrExternal' => 'SHOP-2024-004711',
    // 'CustKey' => '5821',
    // 'FromTime' => '2024-05-01T00:00:00',
    // 'UntilTime' => '2024-06-01T00:00:00',
    // 'ShopID' => 'SHP',
    // 'PaymentType' => 'V',
    // 'ShippingCountry' => 'DE',
    // 'RowCount' => '500',
    // 'FromUpdateTime' => '2024-05-15T08:30:00',
];

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

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

SQL object:: dbo.pipiGetOrderHeader