pixiReportShippingEfficiency · Pixi API documentation

Calculates two shipping efficiency KPIs for the CEO dashboard. The values are returned as one row and also through the two OUTPUT parameters.

Sampling window: The window starts at midnight of the day that lies @SamplingPeriod days before today and ends at the current time. If @SamplingPeriod is NULL, 7 days are used.
Orders considered: An order counts as shipped if it has at least one order line with status AUS and a ShipDate inside the window, and none of its lines has a status other than AUS. Orders that are only partly shipped or that contain lines in any other status are ignored.
Throughput time per order: For the shipped lines inside the window, the code reads the ANG and AUS entries from the order history. The throughput time of an order is the difference in hours (DATEDIFF(hh, …)) between the earliest ANG status change and the latest AUS status change.
KPIs:
- AVGThroughputTimeInDays: the sum of the per-order throughput times converted to days (hoursPassed / 24), divided by the number of shipped orders. The result is rounded to 2 decimals.
- PercThroughputTimeIn24h: the number of orders with a throughput time of at most 24 hours, divided by the number of shipped orders and multiplied by 100. The result is rounded to 2 decimals.
If no shipped orders are found, both values are 0.

Notes

All reads use NOLOCK, so the values can include uncommitted data.
hoursPassed is an integer, so hoursPassed / 24 is an integer division. Each order contributes only full days to the average, and fractions of a day are cut off. An order with 30 hours counts as 1 day, and an order with 20 hours counts as 0 days.
DATEDIFF(hh, …) counts hour boundaries that are crossed. It does not measure exact elapsed time.
The divisor is the number of all fully shipped orders. This also includes orders that have no ANG/AUS history entries. Such orders have no throughput time or a NULL throughput time. They lower both KPIs.
Edge case: if shipped orders exist but none has matching history entries, the sum is NULL. AVGThroughputTimeInDays is then returned as NULL and PercThroughputTimeIn24h as 0.
Only lines with a ShipDate inside the window are used to determine the earliest ANG and the latest AUS date.
The call can be expensive on large order histories, because orderhistory is scanned for all ANG/AUS entries.

Tables used

orderlines · Read orderheader · Read orderhistory · Read

Parameter	Type	Default	Description
`SamplingPeriod`	int	7	Number of days back from today over which the KPIs are calculated. The window starts at midnight of that day. `NULL` is treated as 7. Example: `30`
`AVGThroughputTimeInDays` OUTPUT	float decimal	NULL	Output: average throughput time in days (full days per order), rounded to 2 decimals. Any input value is ignored.
`PercThroughputTimeIn24h` OUTPUT	float decimal	NULL	Output: percentage of shipped orders with a throughput time of at most 24 hours, rounded to 2 decimals. Any input value is ignored.

Result set 1 · Always

One row with the two calculated KPIs.

Column	Type	Description
`AVGThroughputTimeInDays` Nullable	float decimal	Average throughput time from offer/entry (`ANG`) to shipped (`AUS`) in days, rounded to 2 decimals. The value is `0` if no orders were shipped.
`PercThroughputTimeIn24h` Nullable	float decimal	Percentage (0–100) of shipped orders with a throughput time of at most 24 hours, rounded to 2 decimals.

Response example

<SqlRowSet1>
  <row>
    <AVGThroughputTimeInDays>1.37</AVGThroughputTimeInDays>
    <PercThroughputTimeIn24h>62.5</PercThroughputTimeIn24h>
  </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:pixiReportShippingEfficiency>
      <!-- <SamplingPeriod>30</SamplingPeriod> -->
      <!-- <AVGThroughputTimeInDays>?</AVGThroughputTimeInDays> -->
      <!-- <PercThroughputTimeIn24h>?</PercThroughputTimeIn24h> -->
    </ns1:pixiReportShippingEfficiency>
  </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:pixiReportShippingEfficiency>
      <!-- <SamplingPeriod>30</SamplingPeriod> -->
      <!-- <AVGThroughputTimeInDays>?</AVGThroughputTimeInDays> -->
      <!-- <PercThroughputTimeIn24h>?</PercThroughputTimeIn24h> -->
    </ns1:pixiReportShippingEfficiency>
  </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 = [
    // 'SamplingPeriod' => '30',
    // 'AVGThroughputTimeInDays' => '?',
    // 'PercThroughputTimeIn24h' => '?',
];

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

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