pixiGetShippedOrdersPerLocation

Determines shipped orders through their invoices and returns them per location (LocationID of the order lines).

Step 1 – select invoices. An invoice qualifies if:

at least one of its invoice lines belongs to an order line that has an OrderHistory entry with status EIN,
its date is between @FromDate and @ToDate (inclusive). With @SearchByScanOutDate = 1 the scan-out date (ScanOutDate) is used, otherwise the invoice date (InvDate),
its total is greater than 0. With @IncludeOrdersWithTotalZero = 1 invoices with a total of exactly 0 are included as well. Negative totals are never included,
if @CustomerNrExternal is set (blanks are trimmed, empty means no filter), the customer of the invoice has this external customer number.

Step 2 – orders per invoice and location. For each selected invoice, the distinct combinations of LocationID and OrderNrExternal of the order lines on that invoice are collected. If @LocId is set, only order lines of this location are considered. Each combination is numbered per invoice, ordered by OrderNrExternal.

Step 3 – output.

@ShowDetails = 1 (default): one row per invoice, location and external order number with invoice data, customer name, shipping address, currency, shipping vendor and tracking ID. Invoice values are repeated if an invoice contains several external orders or locations. Sorted by InvDate.
Any other value: one row per location and currency with the number of rows (NrOfOrders) and the sum of the invoice totals (Total). No sort order is defined.

Notes

All tables are read with NOLOCK; results may contain uncommitted data.
@FromDate and @ToDate are varchar values compared with BETWEEN. A @ToDate without time (e.g. 2024-01-31) means midnight, so invoices later on that day are excluded. Pass 2024-01-31 23:59:59 or the following day. Use an unambiguous format such as yyyy-mm-dd hh:mi:ss.
In summary mode NrOfOrders counts the combinations of invoice, location and external order number, not distinct invoices.
In summary mode an invoice total is counted only once per invoice: it is added to the location of the combination with the alphabetically first OrderNrExternal on that invoice. If an invoice contains order lines of several locations, the whole total is assigned to one location only. With @LocId set, numbering is done only over the filtered rows, so the full invoice total is attributed to that location.
Invoices without a matching customer are excluded; in detail mode also invoices without a matching shipping address (ShipAdrRef) or currency are excluded (inner joins).
Invoices whose order lines have no OrderHistory status EIN are not returned, regardless of the date.

Tables used

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

Parameter	Type	Default	Description
`ShowDetails`	int	1	Output mode: `1` returns detail rows, any other value returns counts and totals per location and currency. Allowed values `1` – Detail rows per invoice, location and external order number `0` – Summary per location and currency (any value other than 1 behaves the same) Example: `1`
`FromDate` Required	varchar(25) string	–	Start of the period (inclusive), compared with the invoice date or scan-out date. Example: `2024-01-01 00:00:00`
`ToDate` Required	varchar(25) string	–	End of the period (inclusive). Without a time part it means midnight at the start of that day. Example: `2024-01-31 23:59:59`
`CustomerNrExternal`	varchar(60) string	–	Optional external customer number; only invoices of this customer are returned. Empty or blank means no filter. Example: `K-100245`
`IncludeOrdersWithTotalZero`	bit boolean	0	If `1`, invoices with a total of 0 are included; otherwise only invoices with a total greater than 0. Allowed values `0` – Only invoices with total > 0 `1` – Invoices with total >= 0 Example: `0`
`SearchByScanOutDate`	bit boolean	0	Selects which date the period refers to. Allowed values `0` – Filter by invoice date (`InvDate`) `1` – Filter by scan-out date (`ScanOutDate`) Example: `0`
`LocId`	varchar(3) string	–	Optional location ID; only order lines of this location are considered. Empty means all locations. Example: `001`

Result set 1 · If `@ShowDetails` = 1

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

Column	Type	Description
`InvoiceNr` Nullable	string	Invoice number
`InvDate` Nullable	dateTime	Invoice date
`ScanOutDate` Nullable	dateTime	Scan-out date of the invoice
`total` Nullable	decimal	Invoice total (repeated for each row of the same invoice)
`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	Postal code of the shipping address
`SCountry` Nullable	string	Country of the shipping address
`OrderNrExternal` Nullable	varchar(50) string	External order number
`CurrencyCode` Nullable	string	Currency code of the invoice
`ShipVendor` Nullable	string	Shipping vendor of the invoice
`CustomerNrExternal` Nullable	string	External customer number
`LocationID` Nullable	varchar(3) string	Location of the order lines

Result set 2 · If `@ShowDetails` is not 1

One row per location and currency with count and total.

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

Response example

<SqlRowSet1>
  <row>
    <InvoiceNr>240117</InvoiceNr>
    <InvDate>2024-01-15T10:22:00</InvDate>
    <ScanOutDate>2024-01-15T14:05:00</ScanOutDate>
    <total>89.90</total>
    <ShipmentTrackingID>00340434161234567890</ShipmentTrackingID>
    <SName>Max Mustermann</SName>
    <SAddress>Hauptstr. 12</SAddress>
    <SCity>München</SCity>
    <SZIP>80331</SZIP>
    <SCountry>DE</SCountry>
    <OrderNrExternal>SHOP-100512</OrderNrExternal>
    <CurrencyCode>EUR</CurrencyCode>
    <ShipVendor>DHL</ShipVendor>
    <CustomerNrExternal>K-100245</CustomerNrExternal>
    <LocationID>001</LocationID>
  </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:pixiGetShippedOrdersPerLocation>
      <!-- <ShowDetails>1</ShowDetails> -->
      <FromDate>2024-01-01 00:00:00</FromDate>
      <ToDate>2024-01-31 23:59:59</ToDate>
      <!-- <CustomerNrExternal>K-100245</CustomerNrExternal> -->
      <!-- <IncludeOrdersWithTotalZero>0</IncludeOrdersWithTotalZero> -->
      <!-- <SearchByScanOutDate>0</SearchByScanOutDate> -->
      <!-- <LocId>001</LocId> -->
    </ns1:pixiGetShippedOrdersPerLocation>
  </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:pixiGetShippedOrdersPerLocation>
      <!-- <ShowDetails>1</ShowDetails> -->
      <FromDate>2024-01-01 00:00:00</FromDate>
      <ToDate>2024-01-31 23:59:59</ToDate>
      <!-- <CustomerNrExternal>K-100245</CustomerNrExternal> -->
      <!-- <IncludeOrdersWithTotalZero>0</IncludeOrdersWithTotalZero> -->
      <!-- <SearchByScanOutDate>0</SearchByScanOutDate> -->
      <!-- <LocId>001</LocId> -->
    </ns1:pixiGetShippedOrdersPerLocation>
  </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-01 00:00:00',
    'ToDate' => '2024-01-31 23:59:59',
    // 'CustomerNrExternal' => 'K-100245',
    // 'IncludeOrdersWithTotalZero' => '0',
    // 'SearchByScanOutDate' => '0',
    // 'LocId' => '001',
];

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

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

Notes

Tables used

Result set 1 · If @ShowDetails = 1

Result set 2 · If @ShowDetails is not 1

Response example

Result set 1 · If `@ShowDetails` = 1

Result set 2 · If `@ShowDetails` is not 1