Orders Read

pixiReadOrderHeader

Returns order headers selected by key or order number, or by create/update date ranges, with key-based paging.

Reads records from OrderHeader. The parameters select one of several mutually exclusive modes, checked in this order:

Create-or-update range: CreateAndUpdateDateFrom and/or CreateAndUpdateDateTo is set. Returns orders where CreateDate or UpdateDate is >= From, and CreateDate or UpdateDate is < To.
Create range: CreateDateFrom and/or CreateDateTo is set. Filters CreateDate >= From and CreateDate < To.
Update range: UpdateDateFrom and/or UpdateDateTo is set. Filters UpdateDate >= From and UpdateDate < To.
Paging without date filter: RowCount is set and no date parameter is set. Returns the next orders after OrderHeaderKey.
Single order: otherwise. Returns at most one order where OrderHeaderKey equals the given key or OrderNr equals the given order number.

Only the first matching mode is used. Parameters of later modes are ignored. For example, OrderNr has no effect if any date parameter or RowCount is set.

Paging: In modes 1–4, OrderHeaderKey is an exclusive lower bound. Only orders with a key greater than the passed value are returned. If OrderHeaderKey is not passed, it is treated as 0. To read the next page, pass the last OrderHeaderKey you received.

Row limit: The output is limited to RowCount rows. If RowCount is NULL or 0, the limit is 1000 rows. Values above 100000 are capped to 100000. Rows are always sorted by OrderHeaderKey ascending.

Computed columns:

ShipDate is the latest ShipDate of the order's Orderlines.
ShopNote is wrapped in a CDATA section if it starts with <.
ExchRateId is only filled if the setting MultiCurrencyEnabled is 1.

Notes

All reads use NOLOCK, so uncommitted data can be returned.
No filter: A call without any parameter returns no rows, unless an order with key 0 exists. The call falls into the single-order mode with key 0.
Single-order mode: If both OrderHeaderKey and OrderNr are passed, the filter is an OR. One arbitrary matching order is returned, because there is no sort before TOP 1.
Create-or-update mode: The OR logic is loose. The To bound only excludes an order if both CreateDate and UpdateDate are at or after To. This mode collects all matching keys before applying the row limit, which can be slow for wide ranges.
RowCount = 0: In modes 2–4, RowCount = 0 leads to zero candidates, so no rows are returned. Pass NULL instead to use the default of 1000. Negative values cause a SQL error.
The row limit is applied with SET ROWCOUNT.

Tables used

OrderHeader · Read Orderlines · Read Settings · Read

Parameter	Type	Default	Description
`OrderHeaderKey`	int	NULL	In date and paging modes: exclusive lower bound (paging cursor); `NULL` is treated as `0`. In single-order mode: exact key of the order to return. Example: `125430`
`OrderNr`	int	NULL	Order number. Only used in single-order mode, i.e. when no date parameter and no `RowCount` is set. Example: `200345`
`CreateDateFrom`	datetime dateTime	NULL	Inclusive lower bound for `CreateDate` (create range mode). Example: `2024-05-01T00:00:00`
`CreateDateTo`	datetime dateTime	NULL	Exclusive upper bound for `CreateDate` (create range mode). Example: `2024-05-02T00:00:00`
`UpdateDateFrom`	datetime dateTime	NULL	Inclusive lower bound for `UpdateDate`. Only used if no create-or-update or create-range parameter is set. Example: `2024-05-01T00:00:00`
`UpdateDateTo`	datetime dateTime	NULL	Exclusive upper bound for `UpdateDate` (update range mode). Example: `2024-05-02T00:00:00`
`CreateAndUpdateDateFrom`	datetime dateTime	NULL	Lower bound: an order matches if `CreateDate` or `UpdateDate` is on or after this value. Has priority over all other filters. Example: `2024-05-01T00:00:00`
`CreateAndUpdateDateTo`	datetime dateTime	NULL	Upper bound: an order matches if `CreateDate` or `UpdateDate` is before this value. Example: `2024-05-02T00:00:00`
`RowCount`	int	NULL	Maximum number of rows to return (capped at 100000; `NULL` or `0` means 1000). If set without date parameters, it enables paging mode after `OrderHeaderKey`. Example: `500`

Result set 1 · Always; empty if no order matches.

Order headers sorted by OrderHeaderKey ascending, limited by RowCount (default 1000).

Column	Type	Description
`OrderHeaderKey`	int	Internal key of the order header.
`OrderNr` Nullable	int	Order number.
`ShipVendorCode` Nullable	string	Shipping vendor code.
`GiftMessage` Nullable	string	Gift message.
`ShipInstruct` Nullable	string	Shipping instructions.
`Discount` Nullable	decimal	Order discount.
`IPAddress` Nullable	string	IP address of the ordering customer.
`CCPPaymentInfoOK` Nullable	string	Credit card payment info status.
`CCPLastAuthCode` Nullable	string	Last credit card authorization code.
`CCPNumOfTries` Nullable	int	Number of credit card authorization tries.
`CCPReauthorizeYN` Nullable	string	Re-authorization flag.
`CreateEmp` Nullable	string	User who created the order.
`CreateDate` Nullable	dateTime	Creation date.
`UpdateEmp` Nullable	string	User who last updated the order.
`UpdateDate` Nullable	dateTime	Last update date.
`CustRef` Nullable	int	Customer reference.
`AddrRef` Nullable	int	Billing address reference.
`ShipAdrRef` Nullable	int	Shipping address reference.
`rowguid` Nullable	string	Row GUID.
`OhSCostCharged` Nullable	decimal	Shipping cost charged.
`OhShippingNote` Nullable	string	Shipping note.
`OhChargeShipCost` Nullable	string	Flag whether shipping cost is charged.
`OhWrapType` Nullable	string	Gift wrap type.
`OhWrapMessage` Nullable	string	Gift wrap message.
`OhSCostChargeType` Nullable	string	Shipping cost charge type.
`OhSCostFirstItemCharged` Nullable	decimal	Shipping cost charged for the first item.
`OhShipImmed` Nullable	string	Ship-immediately flag.
`OhSLocked` Nullable	string	Shipping lock flag.
`OhGosF1` Nullable	string	Free field 1.
`OhGosF2` Nullable	string	Free field 2.
`OhWaitDays` Nullable	int	Wait days.
`OhShipID` Nullable	string	Shipping ID.
`Address_Remarks` Nullable	string	Address remarks.
`Verfahren` Nullable	string	Shipping procedure.
`Product` Nullable	string	Shipping product.
`Extras` Nullable	string	Shipping extras.
`ShipDate` Nullable	dateTime	Latest `ShipDate` of the order's lines (`Orderlines`); `NULL` if there are no lines.
`ChangeDate` Nullable	dateTime	Change date.
`ChangeSequence` Nullable	int	Change sequence number.
`ChangeReported` Nullable	string	Flag whether the change was reported.
`ExtraShippingCharged` Nullable	decimal	Extra shipping charged.
`ccpPayID` Nullable	string	Credit card payment ID.
`ccpSetFailTries` Nullable	int	Number of failed settlement tries.
`ccpSetFailLastTime` Nullable	dateTime	Time of the last failed settlement.
`PaidBySwiftRef` Nullable	string	Payment reference (SWIFT).
`ShopNote` Nullable	varchar(max) string	Shop note; wrapped in `<![CDATA[...]]>` if it starts with `<`.
`PaidByCustomer` Nullable	string	Paid-by-customer value.
`OhCCPmerchantID` Nullable	string	Credit card merchant ID.
`ShipCost` Nullable	decimal	Shipping cost.
`AutoReservationDone` Nullable	string	Flag whether the automatic reservation was done.
`ForceToPicklistIfNotFULL` Nullable	string	Force to picklist even if not fully available.
`DFType` Nullable	string	Direct fulfillment type.
`ManualResDate` Nullable	dateTime	Manual reservation date.
`OHMultiForceToPicklist` Nullable	string	Multi force-to-picklist flag.
`OHCancelAfterForcedShipOut` Nullable	string	Cancel after forced ship-out flag.
`SubShopLogo` Nullable	string	Sub-shop logo.
`SubShopName` Nullable	string	Sub-shop name.
`Referrer` Nullable	string	Referrer.
`AutoPaymentCreated` Nullable	string	Flag whether an automatic payment was created.
`ResOnLocDate` Nullable	dateTime	Reservation-on-location date.
`ChannelRef` Nullable	int	Sales channel reference.
`OrderNrExternal` Nullable	string	External (shop) order number.
`OrderStatus` Nullable	string	Order status.
`OrderLinesTotal` Nullable	decimal	Total of the order lines.
`OrderLinesDiscount` Nullable	decimal	Discount on the order lines.
`OrderTotal` Nullable	decimal	Order total.
`CurrencyId` Nullable	int	Currency ID.
`ExchRateId` Nullable	int	Exchange rate ID; only filled if setting `MultiCurrencyEnabled` = 1, otherwise `NULL`.

Response example

<SqlRowSet1>
  <row>
    <OrderHeaderKey>125431</OrderHeaderKey>
    <OrderNr>200346</OrderNr>
    <ShipVendorCode>DHL</ShipVendorCode>
    <Discount>0.00</Discount>
    <CreateEmp>API</CreateEmp>
    <CreateDate>2024-05-01T09:15:22</CreateDate>
    <UpdateDate>2024-05-01T10:02:11</UpdateDate>
    <CustRef>48211</CustRef>
    <AddrRef>90122</AddrRef>
    <ShipAdrRef>90123</ShipAdrRef>
    <ShipDate>2024-05-02T14:30:00</ShipDate>
    <ShipCost>4.90</ShipCost>
    <OrderNrExternal>SHOP-100045</OrderNrExternal>
    <OrderStatus>AUF</OrderStatus>
    <OrderLinesTotal>59.80</OrderLinesTotal>
    <OrderTotal>64.70</OrderTotal>
    <CurrencyId>1</CurrencyId>
  </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:pixiReadOrderHeader>
      <!-- <OrderHeaderKey>125430</OrderHeaderKey> -->
      <!-- <OrderNr>200345</OrderNr> -->
      <!-- <CreateDateFrom>2024-05-01T00:00:00</CreateDateFrom> -->
      <!-- <CreateDateTo>2024-05-02T00:00:00</CreateDateTo> -->
      <!-- <UpdateDateFrom>2024-05-01T00:00:00</UpdateDateFrom> -->
      <!-- <UpdateDateTo>2024-05-02T00:00:00</UpdateDateTo> -->
      <!-- <CreateAndUpdateDateFrom>2024-05-01T00:00:00</CreateAndUpdateDateFrom> -->
      <!-- <CreateAndUpdateDateTo>2024-05-02T00:00:00</CreateAndUpdateDateTo> -->
      <!-- <RowCount>500</RowCount> -->
    </ns1:pixiReadOrderHeader>
  </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:pixiReadOrderHeader>
      <!-- <OrderHeaderKey>125430</OrderHeaderKey> -->
      <!-- <OrderNr>200345</OrderNr> -->
      <!-- <CreateDateFrom>2024-05-01T00:00:00</CreateDateFrom> -->
      <!-- <CreateDateTo>2024-05-02T00:00:00</CreateDateTo> -->
      <!-- <UpdateDateFrom>2024-05-01T00:00:00</UpdateDateFrom> -->
      <!-- <UpdateDateTo>2024-05-02T00:00:00</UpdateDateTo> -->
      <!-- <CreateAndUpdateDateFrom>2024-05-01T00:00:00</CreateAndUpdateDateFrom> -->
      <!-- <CreateAndUpdateDateTo>2024-05-02T00:00:00</CreateAndUpdateDateTo> -->
      <!-- <RowCount>500</RowCount> -->
    </ns1:pixiReadOrderHeader>
  </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 = [
    // 'OrderHeaderKey' => '125430',
    // 'OrderNr' => '200345',
    // 'CreateDateFrom' => '2024-05-01T00:00:00',
    // 'CreateDateTo' => '2024-05-02T00:00:00',
    // 'UpdateDateFrom' => '2024-05-01T00:00:00',
    // 'UpdateDateTo' => '2024-05-02T00:00:00',
    // 'CreateAndUpdateDateFrom' => '2024-05-01T00:00:00',
    // 'CreateAndUpdateDateTo' => '2024-05-02T00:00:00',
    // 'RowCount' => '500',
];

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

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

SQL object:: dbo.pipiReadOrderHeader