Pixi API documentation

Unofficial · by koempf24.de

Postman collection
Reports Read

pixiReportStockRange

Returns a paged stock range report per item: current stock, sales in a period, sales per day and the resulting stock range in days, sorted by stock range descending.

Calculates how many days the current stock of each item will last based on past sales, and returns the items whose stock range is at least MinStockRange days. It is typically used to find slow-moving or overstocked items.

Calculation

  1. For every item that has at least one stock record (InvBinItem joined to InvBin), the procedure computes:
    • Stock: sum of BinItemQty over all bins of the item.
    • Sales: sum of ItemQty of the invoice lines (Invlines) of the item that were created within the last SalesDaysBack days (calendar days). By default only lines with a positive quantity are counted. With DeductReturns = 1, all lines are counted, so negative lines such as credit notes reduce the sum.
    • LastStockInc: number of days since the most recent UpdateDate of the item's stock records.
  2. SalesPerDay = Sales / SalesDaysBack.
  3. StockRange = Stock / SalesPerDay, in days. If Sales is 0 or less, StockRange is set to 9999.

Filters (all combined with AND)

  • SupplNr: only items of this supplier (items.SupplNR).
  • PriceMin / PriceMax: limits on the item sales price (items.Price).
  • LastStockIncDays: only items whose LastStockInc is greater than this value.
  • StockMin: only items with Stock >= this value (default 0, so items with negative stock are excluded).
  • MinStockRange: only items with StockRange >= this value.
  • ExcludeInhouseEAN = 1: items whose EANUPC starts with 2 (in-house EAN numbers) are excluded.

Sorting and paging

The filtered rows are numbered in Row by StockRange descending, then ItemName. The call returns at most RowCount rows with Row > StartRow, in the same order. To fetch the next page, pass the last Row received as StartRow.

Notes

  • All tables are read WITH (NOLOCK), so the figures can include uncommitted data.
  • The calculation runs over all items with stock records and uses a correlated subquery on Invlines per item, so every call (also every page) recalculates the full report. On large databases this can be slow.
  • SalesDaysBack = 0 causes a division-by-zero SQL error, returned as SqlMessage. With SalesDaysBack = NULL no sales are counted, SalesPerDay is NULL and every item gets StockRange 9999.
  • PriceMin and PriceMax are normalised with NULLIF(…, ''); the empty string converts to the money value 0, so a value of 0 means no filter. In particular PriceMax = 0 does not restrict the result.
  • SupplNr = empty string is treated as NULL (no filter). StartRow, StockMin and MinStockRange = NULL are treated as 0.
  • RowCount is applied with SET ROWCOUNT; RowCount = 0 returns all matching rows.
  • With ExcludeInhouseEAN = 1, items without an EANUPC (NULL) are also excluded, because the NOT LIKE comparison is not true for NULL.
  • Despite its name, LastStockInc is based on the latest UpdateDate of the stock records, not specifically on the last stock increase.

Tables used

items · Read InvBinItem · Read InvBin · Read Invlines · Read
SQL object:
dbo.pipiReportStockRange
Updated at 2026-10-30