Pixi API documentation

Unofficial · by koempf24.de

Postman collection
Reports Read

pixiReport_LogisticQuality

Returns the distribution of recent orders by the number of days between order receipt and first shipment, as a logistics quality KPI.

Calculates how many days it took to ship recent orders and groups the orders by that number.

1. Order selection

An order is included if:

  • its Orderheader.CreateDate is at most @DaysBack days ago;
  • it has at least one order line in status ANG or AUS. If @ShopID is not empty, the line's OrdShopId must also match;
  • it has no line in status ANG/AUS (created within @DaysBack days, same shop filter) whose Artikel had no stock booked in InvBinItemLog before the line was created;
  • its customer is not locked (HSLocked = 'Y' in CustomersHist).

The order's reference date is the earliest StatusChangeDate of an ANG entry in Orderhistory for its lines.

2. Stock check

The procedure collects all lines of the selected orders. It removes an order if, for one of its Artikel, the summed quantity of all collected lines is higher than the stock booked in InvBinItemLog up to the order's reference date. Only lines created up to that date and not shipped before it (or not shipped at all) are summed.

3. Date adjustments

  • Weekend (with DATEFIRST 1): orders from Friday after 14:59 move 3 days later. Saturday orders move 2 days later and Sunday orders 1 day later. The time of day stays the same.
  • Days without shipping: a day counts as a shipping day if it has more than 2 invoices with total > 0 within @DaysBack days. An order whose original receipt day is not a shipping day gets a new reference date: the earliest invoice time of the next shipping day. This replaces the weekend adjustment.

4. Calculation

For each order, DaysToShip is the whole number of 24-hour periods from the adjusted reference date to the earliest ShipDate of its lines. Negative values are set to 0.

Unshipped orders are excluded, both from the grouping and from the total AllPackages.

5. Result

One row per DaysToShip value with:

  • the number of orders;
  • their share of AllPackages;
  • the total AllPackages.

A fixed extra row with DaysToShip = 99 and 0 orders is always added. Rows are sorted by DaysToShip ascending.

Notes

  • The parameters @OnlyItemsOnStock and @MinutesSinceRefresh are declared but not used by the code.
  • @ShopID filters on the order line field OrdShopId, not on the order header.
  • The code comment says Friday from 14:00. The code uses hour > 14, so the weekend shift starts at 15:00.
  • The comment calls the derived days "holidays" (days without shipment). The table actually contains days with more than 2 invoices. Orders created on any other day are moved to the first invoice time of the next such day. If no later shipping day exists, the reference date becomes NULL and the order counts as unshipped.
  • If CustomersHist.HCustRef contains NULL values, the NOT IN filter excludes all orders.
  • The row with DaysToShip = 99 is a fixed marker row with NRofORders = 0 and Percentage = 0. It is always returned, even if no order qualifies.
  • Percentage is a fraction between 0 and 1, not a percent value.
  • Most reads use NOLOCK, so the figures may include uncommitted data. The first read on Orderheader does not use NOLOCK.
  • The procedure uses many correlated subqueries on Orderhistory, Orderlines and InvBinItemLog. Large values for @DaysBack can be slow.
  • The procedure only writes to temporary tables.

Tables used

Orderheader · Read Orderhistory · Read Orderlines · Read Items · Read InvBinItemLog · Read CustomersHist · Read Invoices · Read
SQL object:
dbo.pipiReport_LogisticQuality
Updated at 2026-10-30