Orders Read

pixiGetShippedOrders

Returns the shipped orders (invoiced with history status EIN) for a date range, either as detail rows or as order count and total per currency.

Determines all invoices of a period that count as shipped and returns them either in detail or aggregated.

Selection of invoices

An invoice qualifies if at least one of its invoice lines (Invlines) refers to an order line that has an OrderHistory entry with status EIN.
The period is checked with BETWEEN @FromDate AND @ToDate against Invoices.InvDate (default) or against Invoices.ScanOutDate if SearchByScanOutDate = 1.
By default only invoices with total > 0 are returned; with IncludeOrdersWithTotalZero = 1 invoices with total >= 0 are included. Negative totals are never returned.
If CustomerNrExternal is filled (it is trimmed; an empty value means no filter), only invoices of the customer with that external customer number are returned.

For each selected invoice the distinct external order numbers (Orderlines.OrderNrExternal) of its lines are determined.

ShowDetails = 1 (default) Returns one row per combination of invoice and external order number, sorted by InvDate. An invoice that covers several orders therefore appears several times. The rows contain invoice data, shipping address, currency, carrier, tracking ID and the employee who scanned the invoice out (ScanOutEmp: alphabetically smallest UpdateEmp of the OrderHistory entries with status AUS).

Any other value of ShowDetails Returns one row per currency with the number of orders (invoice/order number combinations) and the sum of the invoice totals, where each invoice total is counted only once.

Notes

All reads use NOLOCK; results may contain uncommitted data.
FromDate and ToDate are strings that are implicitly converted for the BETWEEN comparison. A date without time (e.g. 2024-01-31) means midnight, so invoices later on that day are excluded. Pass a time such as 2024-01-31 23:59:59 or the following day. Use an unambiguous format such as ISO yyyy-mm-ddThh:mi:ss.
In detail mode, invoices without a matching shipping address (ShipAdrRef) or currency are dropped because of inner joins.
In summary mode no row is returned if no invoice matches (the query uses GROUP BY).
ScanOutEmp is empty if no OrderHistory entry with status AUS exists for the invoice lines.

Tables used

Invoices · Read Customers · Read Invlines · Read OrderHistory · Read Orderlines · Read Addresses · Read Currency · Read Countries · Read

Parameter	Type	Default	Description
`ShowDetails`	int	1	Controls the output: `1` returns detail rows, any other value returns count and total per currency. Allowed values `1` – Detail rows per invoice and external order number `0` – Summary: number of orders and total per currency (applies to every value other than 1) Example: `1`
`FromDate` Required	varchar(25) string	–	Start of the period (inclusive), compared with the invoice date or scan-out date. Example: `2024-01-01T00:00:00`
`ToDate` Required	varchar(25) string	–	End of the period (inclusive). Without a time component it means midnight of that day. Example: `2024-01-31T23:59:59`
`CustomerNrExternal`	varchar(60) string	–	Optional external customer number to restrict the result to one customer. Empty means all customers. Example: `K-100245`
`IncludeOrdersWithTotalZero`	bit boolean	0	If `1`, invoices with a total of zero are included; otherwise only invoices with a positive total. Allowed values `0` – Only invoices with total > 0 `1` – Invoices with total >= 0 Example: `0`
`SearchByScanOutDate`	bit boolean	0	If `1`, the period is applied to `ScanOutDate`; otherwise to the invoice date `InvDate`. Allowed values `0` – Filter by invoice date `1` – Filter by scan-out date Example: `0`

Result set 1 · If `ShowDetails` = 1

One row per invoice and external order number, sorted by invoice date.

Column	Type	Description
`InvoiceNr`	string	Invoice number
`InvDate` Nullable	dateTime	Invoice date
`ScanOutDate` Nullable	dateTime	Date the invoice was scanned out
`total` Nullable	decimal	Invoice total
`ShipmentTrackingID` Nullable	string	Shipment tracking ID
`SName` Nullable	string	Customer name
`SAddress` Nullable	string	Street of the shipping address
`SCity` Nullable	string	City of the shipping address
`SZIP` Nullable	string	ZIP code of the shipping address
`SCountry` Nullable	string	Country code of the shipping address as stored in Pixi
`OrderNrExternal` Nullable	string	External order number
`CurrencyCode`	string	Currency code of the invoice
`ShipVendor` Nullable	string	Shipping carrier
`CustomerNrExternal` Nullable	string	External customer number
`ScanOutEmp` Nullable	varchar(30) string	Employee who scanned the invoice out (smallest `UpdateEmp` of history entries with status `AUS`)
`SCountryISO2` Nullable	string	ISO 3166 alpha-2 code of the shipping country
`SCountryISO3` Nullable	string	ISO 3166 alpha-3 code of the shipping country

Result set 2 · If `ShowDetails` <> 1 and at least one invoice matches

One row per currency with order count and total.

Column	Type	Description
`NrOfOrders`	int	Number of orders (invoice/external order number combinations)
`Total` Nullable	decimal	Sum of invoice totals, each invoice counted once
`CurrencyCode`	string	Currency code

Response example

<SqlRowSet1>
  <row>
    <InvoiceNr>240117</InvoiceNr>
    <InvDate>2024-01-15T10:24:00</InvDate>
    <ScanOutDate>2024-01-15T14:02:00</ScanOutDate>
    <total>89.90</total>
    <ShipmentTrackingID>00340434161234567890</ShipmentTrackingID>
    <SName>Muster GmbH</SName>
    <SAddress>Hauptstraße 12</SAddress>
    <SCity>München</SCity>
    <SZIP>80331</SZIP>
    <SCountry>D</SCountry>
    <OrderNrExternal>SHOP-100512</OrderNrExternal>
    <CurrencyCode>EUR</CurrencyCode>
    <ShipVendor>DHL</ShipVendor>
    <CustomerNrExternal>K-100245</CustomerNrExternal>
    <ScanOutEmp>MMUSTER</ScanOutEmp>
    <SCountryISO2>DE</SCountryISO2>
    <SCountryISO3>DEU</SCountryISO3>
  </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:pixiGetShippedOrders>
      <!-- <ShowDetails>1</ShowDetails> -->
      <FromDate>2024-01-01T00:00:00</FromDate>
      <ToDate>2024-01-31T23:59:59</ToDate>
      <!-- <CustomerNrExternal>K-100245</CustomerNrExternal> -->
      <!-- <IncludeOrdersWithTotalZero>0</IncludeOrdersWithTotalZero> -->
      <!-- <SearchByScanOutDate>0</SearchByScanOutDate> -->
    </ns1:pixiGetShippedOrders>
  </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:pixiGetShippedOrders>
      <!-- <ShowDetails>1</ShowDetails> -->
      <FromDate>2024-01-01T00:00:00</FromDate>
      <ToDate>2024-01-31T23:59:59</ToDate>
      <!-- <CustomerNrExternal>K-100245</CustomerNrExternal> -->
      <!-- <IncludeOrdersWithTotalZero>0</IncludeOrdersWithTotalZero> -->
      <!-- <SearchByScanOutDate>0</SearchByScanOutDate> -->
    </ns1:pixiGetShippedOrders>
  </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 = [
    // 'ShowDetails' => '1',
    'FromDate' => '2024-01-01T00:00:00',
    'ToDate' => '2024-01-31T23:59:59',
    // 'CustomerNrExternal' => 'K-100245',
    // 'IncludeOrdersWithTotalZero' => '0',
    // 'SearchByScanOutDate' => '0',
];

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

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

SQL object:: dbo.pipiGetShippedOrders