pixiReport_LogisticQuality_Details

Measures how many days (in 24-hour steps) it took to ship orders created in the last DaysBack days.

Date range: FromDate is the start of the day DaysBack days ago, ToDate is the end of today.

Order selection:

Orders whose header CreateDate is on or after FromDate.
The order's creation date is the earliest ANG entry in OrderHistory within the date range. The ship date is the earliest ShipDate of its order lines.
The order header ShipDate must be NULL.
At least one order line must have status ANG or AUS. If ShopID is set, that line must also belong to this shop (OrdShopID).
Orders of customers that were locked (HSLocked = 'Y') in CustomersHist after FromDate are excluded.

Further exclusions (the whole order is removed if one line qualifies):

Order lines created more than 5 minutes apart. Such orders count as changed manually after the import.
Not enough stock on the order date. Demand is accumulated per item from order creation and shipment events. Stock is calculated from InvBinItemLog in locations without special stock (SpecialStock = 0). If demand is greater than or equal to stock, the order is removed.
Lines on ANG whose available stock is <= 1. Available stock is ItemLocationSpecific.LocationQuantity if special-stock locations exist, otherwise Items.Quantity. With CalcSeal = 0, reservations made earlier by open orders (non-final states except EIN, FUT, FUS) are subtracted.

Date adjustments:

Orders created on Friday, Saturday or Sunday are moved to the following Monday. The time of day is kept.
Holidays are taken as days without invoices that have a positive total. An order is moved to the first invoice time of the next day with invoices if one of these applies:
- its creation day has no such invoices;
- the following day has no such invoices.

Output (only orders with a ship date):

CalcSeal = 0 (pixi* Report client): one row per order with DaysToShip, order number and dates, sorted by DaysToShip and CreateDate. With ExcludeZeros = 1, only orders with DaysToShip > 0 are returned.
CalcSeal = 1 (Seal / Dashboard apps): one row per DaysToShip value with the order count, its share of all packages and the total. An extra row with DaysToShip = 99 and zero values is always added. The rows are sorted by DaysToShip.

Notes

The parameter OnlyItemsOnStock is declared but not used anywhere in the code.
All permanent tables are read with NOLOCK, so results can include uncommitted data.
SET DATEFIRST 1 is applied inside the procedure. Weekdays 5–7 are Friday to Sunday.
The reported CreateDate is the adjusted date (weekend/holiday shift), not the original order date.
Orders whose header ShipDate is set are not included. The ship date used is the earliest line ShipDate.
Orders whose ANG lines have no matching ItemLocationSpecific row for the line's location are not removed by the reservation-stock check.
Holiday detection depends on invoices with total > 0. Days without invoices are treated as holidays.
The code comment states that changes must also be applied to pixiReport_LogisticQuality_NonCached.
The calculation runs over many temporary tables and window functions. Large DaysBack values can be slow.

pixiReport_LogisticQuality_NonCached

Tables used

Location · Read OrderHeader · Read Orderlines · Read OrderHistory · Read CustomersHist · Read Reservation · Read InvBinItemLog · Read OrderStates · Read ItemLocationSpecific · Read Items · Read Invoices · Read

Parameter	Type	Default	Description
`DaysBack`	int	14	Number of days back from today that define the evaluation period. Example: `14`
`ShopID`	varchar(3) string	–	Restricts the report to orders with an `ANG`/`AUS` line of this shop. Empty means all shops. Example: `SHP`
`ExcludeZeros`	bit boolean	0	Only with `CalcSeal = 0`: if 1, orders shipped within the first 24 hours (`DaysToShip = 0`) are omitted. Allowed values `0` – Return all orders `1` – Return only orders with `DaysToShip > 0` Example: `0`
`OnlyItemsOnStock`	bit boolean	1	Not used by the code. Example: `1`
`CalcSeal`	bit boolean	0	Selects the output mode: detail rows per order or aggregated distribution for the shipping seal. Allowed values `0` – Detail rows per order (pixi* Report client), reservation check against open orders `1` – Aggregated count and percentage per `DaysToShip` (Seal/Dashboard apps) Example: `0`

Result set 1 · If `CalcSeal` = 0 (default)

One row per evaluated, shipped order, sorted by DaysToShip and CreateDate.

Column	Type	Description
`DaysToShip`	int	Full 24-hour periods between adjusted creation date and ship date. Negative values become 0, missing values 999.
`OrderNr` Nullable	int	Order number.
`CreateDate` Nullable	datetime dateTime	Order creation date (first `ANG` status), adjusted for weekends and holidays.
`Shipdate`	datetime dateTime	Earliest ship date of the order lines.
`par_ShopId`	varchar(6) string	Shop filter used, or `-alle-` if none.
`par_DaysBack` Nullable	int	Value of `DaysBack` used.

Result set 2 · If `CalcSeal` = 1

Distribution of shipped orders per DaysToShip, plus an extra row with DaysToShip = 99, sorted by DaysToShip.

Column	Type	Description
`DaysToShip` Nullable	int	Full 24-hour periods from adjusted creation to shipment (negative values become 0). 99 marks the added sentinel row.
`NRofORders`	int	Number of orders with this `DaysToShip` value.
`Percentage` Nullable	float decimal	Share of all packages as a fraction (0–1).
`AllPackages` Nullable	float decimal	Total number of evaluated shipped orders.

Response example

<SqlRowSet1>
  <row>
    <DaysToShip>0</DaysToShip>
    <OrderNr>100234</OrderNr>
    <CreateDate>2024-05-06T09:12:00</CreateDate>
    <Shipdate>2024-05-06T15:40:00</Shipdate>
    <par_ShopId>-alle-</par_ShopId>
    <par_DaysBack>14</par_DaysBack>
  </row>
  <row>
    <DaysToShip>1</DaysToShip>
    <OrderNr>100251</OrderNr>
    <CreateDate>2024-05-07T11:05:00</CreateDate>
    <Shipdate>2024-05-08T14:20:00</Shipdate>
    <par_ShopId>-alle-</par_ShopId>
    <par_DaysBack>14</par_DaysBack>
  </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:pixiReport_LogisticQuality_Details>
      <!-- <DaysBack>14</DaysBack> -->
      <!-- <ShopID>SHP</ShopID> -->
      <!-- <ExcludeZeros>0</ExcludeZeros> -->
      <!-- <OnlyItemsOnStock>1</OnlyItemsOnStock> -->
      <!-- <CalcSeal>0</CalcSeal> -->
    </ns1:pixiReport_LogisticQuality_Details>
  </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:pixiReport_LogisticQuality_Details>
      <!-- <DaysBack>14</DaysBack> -->
      <!-- <ShopID>SHP</ShopID> -->
      <!-- <ExcludeZeros>0</ExcludeZeros> -->
      <!-- <OnlyItemsOnStock>1</OnlyItemsOnStock> -->
      <!-- <CalcSeal>0</CalcSeal> -->
    </ns1:pixiReport_LogisticQuality_Details>
  </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 = [
    // 'DaysBack' => '14',
    // 'ShopID' => 'SHP',
    // 'ExcludeZeros' => '0',
    // 'OnlyItemsOnStock' => '1',
    // 'CalcSeal' => '0',
];

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

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

Notes

Related calls

Tables used

Result set 1 · If CalcSeal = 0 (default)

Result set 2 · If CalcSeal = 1

Response example

Result set 1 · If `CalcSeal` = 0 (default)

Result set 2 · If `CalcSeal` = 1