Orders Read

pixiGetOrderTotals

Returns the order lines of an order together with the order totals, VAT and shipping amounts, matching what the invoice would produce.

Looks up an order and returns one row per order line, with the order header totals repeated on every row.

Selecting the order

If OrderNr is set (not 0/NULL), the lines of that Pixi order number are used. OrderNrExternal is then ignored.
Otherwise, if OrderNrExternal is not empty, the lines with that external order number are used.
If neither is given, nothing is found.
CustKey optionally restricts the result to lines of that customer. 0 or NULL disables this filter.

Joins

The lines must have a matching article, billing address, shipping address, customer, shop of the customer, payment method of the billing address, order header and order currency. Lines that miss any of these are not returned.

Status evaluation

If no lines are found, a single row with ErrorMessage = No results found is returned.
If all found lines have status STO (cancelled), a single row with ErrorMessage = Only orderlines with status STO found is returned.
Otherwise all lines whose status is not STO are returned with line values (EAN, supplier item number, name, quantity, price, net values, note) and header values (order total, discount, VAT total/high/low including shipping cost VAT, net total of the lines, shipping cost gross and net, tax rates, currency code).

No sort order is applied.

Notes

All tables are read with NOLOCK, so uncommitted data can be returned.
Header values (OrderTotal, Discount, VATTotal, ShipCost etc.) are repeated on every line row; do not sum them across rows.
VATTotal, VATHigh and VATLow include the VAT on shipping costs, while NettoTotal is the net total of the order lines only and does not include shipping.
Several amounts are rounded to 2 decimals and converted to float.
Lines with a NULL status are counted neither as cancelled nor as not cancelled and are not returned.
The first result set has a different structure depending on the outcome: either the line columns or a single ErrorMessage column. Check for ErrorMessage before reading the data.
Only cancelled lines are excluded from the output; the status itself is not returned.

Tables used

Orderlines · Read items · Read Addresses · Read Customers · Read Shops · Read Payments · Read OrderHeader · Read Currency · Read

Parameter	Type	Default	Description
`OrderNr`	int	0	Pixi order number. Takes precedence over `OrderNrExternal`; `0` means not set. Example: `123456`
`OrderNrExternal`	varchar(50) string	–	External (shop) order number. Used only if `OrderNr` is `0` or `NULL`; empty means not set. Example: `SHOP-100045`
`CustKey`	int	NULL	Optional customer key; restricts the result to lines of this customer. `0` or `NULL` disables the filter. Example: `4711`

Result set 1 · If at least one non-cancelled order line is found

One row per order line with status other than STO, including the order header totals.

Column	Type	Description
`OrderNr`	int	Pixi order number.
`EANUPC` Nullable	string	EAN/UPC of the article.
`ItemNrSuppl` Nullable	string	Supplier item number of the article.
`ArtName` Nullable	string	Article name as stored on the order line.
`Qty` Nullable	int	Ordered quantity.
`Price` Nullable	decimal	Unit price of the line.
`Subtotal` Nullable	decimal	Line amount (`OrderLineAmount`).
`OrderNrExternal` Nullable	string	External order number.
`ItemNote` Nullable	string	Special note of the line item.
`ItemPriceNoVat` Nullable	float decimal	Net unit price, rounded to 2 decimals.
`ItemAmountNoVat` Nullable	float decimal	Net line amount, rounded to 2 decimals.
`OrderTotal` Nullable	decimal	Order total from the order header.
`Discount` Nullable	decimal	Discount from the order header.
`VATTotal` Nullable	float decimal	Total VAT (high and low rate, including VAT on shipping costs), rounded to 2 decimals.
`VATHigh` Nullable	float decimal	VAT at the high rate including shipping cost VAT, rounded to 2 decimals.
`VATLow` Nullable	float decimal	VAT at the low rate including shipping cost VAT, rounded to 2 decimals.
`NettoTotal` Nullable	decimal	Net total of the order lines (without shipping), rounded to 2 decimals.
`ShipCost` Nullable	decimal	Shipping costs (gross) from the order header.
`ShipCostNoVAT` Nullable	float decimal	Net shipping costs, rounded to 2 decimals.
`TaxRateHigh` Nullable	decimal	High VAT rate of the order.
`TaxRateLow` Nullable	decimal	Low VAT rate of the order.
`OrderCurrencyCode` Nullable	string	Currency code (`CodeA`) of the order currency.

Result set 2 · If no lines are found or all found lines have status `STO`

Single status row with an error message (returned instead of the line result set).

Column	Type	Description
`ErrorMessage`	varchar(200) string	Reason why no lines are returned.

Response example

<SqlRowSet1>
  <row>
    <OrderNr>123456</OrderNr>
    <EANUPC>4006381333931</EANUPC>
    <ItemNrSuppl>STB-200</ItemNrSuppl>
    <ArtName>Stabilo Boss Textmarker gelb</ArtName>
    <Qty>2</Qty>
    <Price>11.9</Price>
    <Subtotal>23.8</Subtotal>
    <OrderNrExternal>SHOP-100045</OrderNrExternal>
    <ItemNote/>
    <ItemPriceNoVat>10</ItemPriceNoVat>
    <ItemAmountNoVat>20</ItemAmountNoVat>
    <OrderTotal>28.75</OrderTotal>
    <Discount>0</Discount>
    <VATTotal>4.59</VATTotal>
    <VATHigh>4.59</VATHigh>
    <VATLow>0</VATLow>
    <NettoTotal>20</NettoTotal>
    <ShipCost>4.95</ShipCost>
    <ShipCostNoVAT>4.16</ShipCostNoVAT>
    <TaxRateHigh>19</TaxRateHigh>
    <TaxRateLow>7</TaxRateLow>
    <OrderCurrencyCode>EUR</OrderCurrencyCode>
  </row>
</SqlRowSet1>

Status	Message	Cause
ERROR	No results found	No order line matches the given `OrderNr`/`OrderNrExternal` (and `CustKey`), neither identifier was given, or required related data (article, addresses, customer, shop, payment method, header, currency) is missing. Check the order number and customer key.
ERROR	Only orderlines with status STO found	The order exists, but all its lines are cancelled (status `STO`).

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:pixiGetOrderTotals>
      <!-- <OrderNr>123456</OrderNr> -->
      <!-- <OrderNrExternal>SHOP-100045</OrderNrExternal> -->
      <!-- <CustKey>4711</CustKey> -->
    </ns1:pixiGetOrderTotals>
  </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:pixiGetOrderTotals>
      <!-- <OrderNr>123456</OrderNr> -->
      <!-- <OrderNrExternal>SHOP-100045</OrderNrExternal> -->
      <!-- <CustKey>4711</CustKey> -->
    </ns1:pixiGetOrderTotals>
  </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' => '123456',
    // 'OrderNrExternal' => 'SHOP-100045',
    // 'CustKey' => '4711',
];

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

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

SQL object:: dbo.pipiGetOrderTotals