Pixi API documentation

Unofficial · by koempf24.de

Postman collection
Reports Write

pixiReport_InvoicesList

Returns a detailed list of invoices and credit notes in a date range, with amounts, VAT, purchase values, address and payment data.

Wrapper around the internal report procedure Report_InvoicesList. Empty strings in ShopID, Payment, ShipCountry and DocumentType are treated as NULL (no filter). The report always returns one row per invoice; the sum mode and the unpaid-only filter of the internal procedure are not available through this call.

Processing steps:

  1. The supplier price cache is refreshed first (Report_SaveSupplierPrices).
  2. DateFrom is moved to the start of its day and DateTo to the end of its day. Invoices with InvDate in this range are selected.
  3. Optional filters:
    • ShopID: only invoices whose shipping address belongs to this shop (AddrShopID). Invoices without a shipping address are then excluded.
    • Payment: only invoices with this payment method code.
    • ShipCountry: only invoices whose shipping address has this country code.
    • DocumentType: R = invoices only (no reference invoice), G = credit notes only (with reference invoice).
  4. Currency of the amounts:
    • With ShopID: amounts are in the invoice's base currency (divided by the invoice exchange rate if multi-currency is enabled); BaseCurrency is that currency code.
    • Without ShopID: amounts are converted to the database currency using the exchange rate of the database source for the same date and currency; BaseCurrency is the database currency code.
  5. Per invoice, the item quantity (SumQtyItems) and the purchase value (SumEKItems, quantity × historical supplier price) are summed from the invoice lines. The external order number is determined via the order linked to the invoice (for credit notes via the referenced invoice).
  6. Net amounts and VAT amounts are rounded to the number of decimal places from the setting NumberOfDecimalPlacesForPrices (default 2). Total and Total_ShipCost are not rounded.

Name, address, phone and fax come from the shipping address; Country, CountryName and usStID come from the billing address. Missing text values are returned as /. The result is sorted by InvoiceNR. The par_* columns echo the effective filter values (-alle- if no filter was set).

Notes

  • Side effect: before reading, the internal procedure Report_SaveSupplierPrices is executed, which according to the code comment fills a cache table (presumably the supplier price history). Its definition is not attached, so the exact writes are unknown.
  • All reads use NOLOCK; the result may contain uncommitted data.
  • Invoices without a matching exchange rate record are not returned (inner joins on pixi.ExchRate). Without ShopID, an exchange rate of the database source for the same date and currency must exist.
  • A DocumentType other than R or G returns no rows.
  • payed is returned as 1900-01-01T00:00:00 for unpaid invoices, because NULL is replaced by an empty string on a datetime column.
  • SumEKItems is 0 or incomplete if no supplier price history exists for the invoice lines.
  • If the order lookup function returns several orders for one invoice, the invoice can appear in several rows.
  • The behaviour depends on the settings MultiCurrencyEnabled and NumberOfDecimalPlacesForPrices.
  • Large date ranges can be slow because all invoice lines are aggregated.

Tables used

Invoices · Read ExchRate · Read Currency · Read Addresses · Read Customers · Read Payments · Read Invlines · Read SupplierPrice_History · Read Countries · Read
SQL object:
dbo.pipiReport_InvoicesList
Updated at 2026-10-30