Orders Read

pixiGetOrderHeaderList

Returns header data for several orders identified by their external order numbers, including customer, address, payment, shipping and invoice information.

Takes a list of external order numbers (OrderNrExternal) as XML and returns one row per matching order from OrderHeader.

Steps:

The XML is split into values. Every child element of the root element is read, whatever its name. Duplicate values are removed.
If the list is empty, the procedure prints No Data! and returns no result set.
Two settings are read:
- UseInitialOrderDate: if 1, OrderDate is the earliest order date of the order lines, otherwise the latest.
- NumberOfDecimalPlacesForPrices: number of decimals used to round Qty * Price per order line (default 2 if the setting is missing or 0).
Orders are matched on OrderHeader.OrderNrExternal. External numbers that do not match an order are skipped without notice.
An order is returned only if it has a currency, a customer, a billing address (AddrRef) and a shipping address (ShipAdrRef). These joins are inner joins.
Per order, the order lines are aggregated together with their invoice lines, their invoices (only invoices with RefToInvoiceKey IS NULL) and their estimated delivery dates:
- shipping dates (min/max ScanOutDate), tracking ID, carrier, last invoice number;
- Paid/PayedSum from the invoices;
- OrderTotal_OrderCurr = sum of rounded Qty * Price of all lines except status STO and NLB, plus ShipCost, minus Discount.
Gender is derived from the salutation (Anrede) of the shipping address:
- 1 for Herr, Hr., M., Monsieur, Mr, Mr.
- 2 for Mrs, Mrs., Ms, Ms., Frau, Fr.
- 0 for any other value.

The result is sorted by CreateDate ascending.

Notes

Read-only. Most tables are read WITH (NOLOCK), so uncommitted data can appear. The subquery for OrderLocked on Addresses runs without NOLOCK.
Values are shredded as varchar(256) but stored in a varchar(50) key column. An external order number longer than 50 characters can make the call fail with a truncation error.
Empty input does not return an error row. It only produces the PRINT message No Data! and no rows.
EstimatedDelivery is filled only if every order line has an estimated delivery date. In that case the latest date is returned. If any line has no date, the column is NULL. Internally, a placeholder date of now + 999 days is used for missing dates and then turned back into NULL.
Paid is computed per joined line. Order lines that have no invoice also count as paid, so Paid can be 1 even if the order is not invoiced at all. PayedSum is the highest paid sum among invoices whose PayedSum equals their total rounded to 2 decimals.
The order lines are joined to invoice lines and estimated delivery rows. If one order line has several invoice lines or several delivery rows, it is counted more than once in OrderTotal_OrderCurr.
Line prices come from Price (not the order-currency price), although the column is called OrderTotal_OrderCurr.
The source table of SubShopName and SubShopLogo is not qualified in the code. Most likely they come from OrderHeader.
Gender mapping uses exact matches on the salutation. Whether the match is case-sensitive depends on the database collation.

Tables used

OrderHeader · Read Currency · Read Customers · Read Addresses · Read Orderlines · Read Invlines · Read Invoices · Read Orderline_EstimatedDelivery · Read Settings · Read

Parameter Type Default Description

Parameter	Type	Default	Description
`OrderNrExternalXml` Required	xml	–	XML list of external order numbers. Each child element of the root element holds one number. Element names are not checked; the code comment uses `<x><i>…</i></x>`. Example: `<x> <i>SHOP-100234</i> <i>SHOP-100235</i> </x>`

OrderNrExternalXml

Required

xml

–

XML list of external order numbers. Each child element of the root element holds one number. Element names are not checked; the code comment uses <x><i>…</i></x>.

Example:

<x>
  <i>SHOP-100234</i>
  <i>SHOP-100235</i>
</x>

Result set 1 · If the XML contains at least one value; may contain zero rows if no order matches.

One row per matching order, sorted by CreateDate 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.
`OrderNrExternal` Nullable	string	External order number (e.g. from the shop).
`OrderTotal` Nullable	decimal	Order total as stored in the order header.
`OrderlinesTotal` Nullable	decimal	Total of the order lines as stored in the order header.
`WholeOrderState` Nullable	string	Status of the whole order (`OrderHeader.OrderStatus`).
`Shipdate` Nullable	dateTime	Earliest scan-out date of the related invoices.
`LastShipdate` Nullable	dateTime	Latest scan-out date of the related invoices.
`TrackingID` Nullable	string	Highest shipment tracking ID of the related invoices.
`ShipVendor` Nullable	string	Carrier of the related invoices (maximum value).
`ShopID` Nullable	string	Shop the customer belongs to (`Customers.CustomerOf`).
`OrderDate` Nullable	dateTime	Order date of the order lines: earliest if setting `UseInitialOrderDate` = 1, otherwise latest.
`ShipCost` Nullable	decimal	Shipping costs of the order.
`PaymentAddressId` Nullable	int	Key of the billing address (`AddrRef`).
`ShipmentAddressId` Nullable	int	Key of the shipping address (`ShipAdrRef`).
`ShopNote` Nullable	string	Note from the shop.
`PaymentType` Nullable	string	Payment type stored on the billing address.
`VoucherID` Nullable	string	Voucher ID of the order lines (maximum value).
`VoucherAmount` Nullable	decimal	Discount amount of the order (`OrderHeader.Discount`).
`CcCardType` Nullable	string	Credit card type from the billing address.
`CcCardNr` Nullable	string	Credit card number from the billing address.
`CcCardName` Nullable	string	Credit card holder name.
`CcCardExp` Nullable	string	Credit card expiry.
`Account` Nullable	string	Bank account number.
`Blz` Nullable	string	Bank code.
`BankName` Nullable	string	Bank name.
`AccountName` Nullable	string	Account holder name.
`AddressRemarks` Nullable	string	Address remarks of the order.
`CustKey`	int	Internal customer key.
`SalutationText` Nullable	string	Salutation of the shipping address.
`Gender`	int	Gender derived from the salutation: 1 = male, 2 = female, 0 = unknown.
`LastName` Nullable	string	Last name of the shipping address.
`FirstName` Nullable	string	First name of the shipping address.
`ShippingCountry` Nullable	string	Country of the shipping address.
`ZIP` Nullable	string	Postal code of the shipping address.
`City` Nullable	string	City of the shipping address.
`Street` Nullable	string	Street of the shipping address.
`BirthDate` Nullable	dateTime	Birth date stored on the shipping address.
`CustomerNr` Nullable	varchar(10) string	Customer number (`Customers.DatevAccNr`) as text.
`SubShopName` Nullable	string	Name of the sub shop.
`SubShopLogo` Nullable	string	Logo of the sub shop.
`Referrer` Nullable	string	Referrer of the order.
`GiftMessage` Nullable	string	Gift message of the order.
`OrderCurrency` Nullable	string	Currency code of the order (`Currency.CodeA`).
`OrderTotal_OrderCurr`	decimal	Computed total: rounded line values (except status `STO`, `NLB`) + shipping costs − discount.
`Paid` Nullable	bit boolean	1 if at least one joined line counts as paid (see notes).
`PayedSum` Nullable	decimal	Highest paid sum of fully paid invoices.
`CustomerEmail` Nullable	string	E-mail address of the customer.
`CustomerNrExternal` Nullable	string	External customer number.
`EstimatedDelivery` Nullable	datetime dateTime	Latest estimated delivery date; NULL if any order line has none.
`OrderLocked`	string	Lock flag of the shipping address (`ASlocked`), `N` if empty.
`LastInvoiceNr` Nullable	int	Highest invoice number of the order (invoices with `RefToInvoiceKey IS NULL`).

Response example

<SqlRowSet1>
  <row>
    <OrderHeaderKey>48211</OrderHeaderKey>
    <OrderNr>100512</OrderNr>
    <CreateDate>2024-03-11T09:14:22</CreateDate>
    <OrderNrExternal>SHOP-100234</OrderNrExternal>
    <OrderTotal>84.90</OrderTotal>
    <OrderlinesTotal>79.90</OrderlinesTotal>
    <WholeOrderState>AUS</WholeOrderState>
    <Shipdate>2024-03-12T14:02:00</Shipdate>
    <LastShipdate>2024-03-12T14:02:00</LastShipdate>
    <TrackingID>00340434161234567890</TrackingID>
    <ShipVendor>DHL</ShipVendor>
    <ShopID>WEB</ShopID>
    <OrderDate>2024-03-11T09:14:22</OrderDate>
    <ShipCost>5.00</ShipCost>
    <PaymentAddressId>70211</PaymentAddressId>
    <ShipmentAddressId>70212</ShipmentAddressId>
    <PaymentType>PayPal</PaymentType>
    <VoucherAmount>0.00</VoucherAmount>
    <CustKey>33120</CustKey>
    <SalutationText>Frau</SalutationText>
    <Gender>2</Gender>
    <LastName>Muster</LastName>
    <FirstName>Anna</FirstName>
    <ShippingCountry>DE</ShippingCountry>
    <ZIP>80331</ZIP>
    <City>München</City>
    <Street>Marienplatz 1</Street>
    <CustomerNr>10045</CustomerNr>
    <OrderCurrency>EUR</OrderCurrency>
    <OrderTotal_OrderCurr>84.90</OrderTotal_OrderCurr>
    <Paid>true</Paid>
    <PayedSum>84.90</PayedSum>
    <CustomerEmail>anna.muster@example.com</CustomerEmail>
    <CustomerNrExternal>C-55812</CustomerNrExternal>
    <OrderLocked>N</OrderLocked>
    <LastInvoiceNr>200781</LastInvoiceNr>
  </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:pixiGetOrderHeaderList>
      <OrderNrExternalXml><![CDATA[<x><i>SHOP-100234</i><i>SHOP-100235</i></x>]]></OrderNrExternalXml>
    </ns1:pixiGetOrderHeaderList>
  </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:pixiGetOrderHeaderList>
      <OrderNrExternalXml><![CDATA[<x><i>SHOP-100234</i><i>SHOP-100235</i></x>]]></OrderNrExternalXml>
    </ns1:pixiGetOrderHeaderList>
  </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 = [
    'OrderNrExternalXml' => '<x><i>SHOP-100234</i><i>SHOP-100235</i></x>',
];

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

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

SQL object:: dbo.pipiGetOrderHeaderList