Pixi API documentation

Unofficial · by koempf24.de

Postman collection
Reports Read

pixiReportCustomerSales

Returns invoiced item lines with customer, country, net line total, currency and last sale date, filtered by item, shop, payment type, customer and invoice date.

Sales report on invoice line level. The header comment describes it as a customer evaluation for customer BAF, so it was written for a specific installation.

Parameter handling

  • ItemNrSuppl, ShopID, PaymentType and CustomerNrExternal: an empty string is treated like NULL, which means no filter.
  • DateFrom: if it is NULL or earlier than 1990-01-01, the current date and time minus one day is used.
  • DateTo: if it is NULL or earlier than 1990-01-01, the current date and time is used.

Filters (all combined with AND)

  • ItemNrSuppl must match the item's supplier item number (Items.ItemNrSuppl).
  • ShopID must match the invoice shop (Invoices.InvShopID).
  • PaymentType must match the payment type stored on the billing address (Addresses.APayment), not the payment type of the invoice.
  • CustomerNrExternal must match the external customer number of the billing address.
  • The invoice date must satisfy InvDate >= DateFrom and InvDate < DateTo. The upper bound is exclusive.

Output One row is returned per invoice line. Each row contains:

  • the item's supplier number and EAN;
  • company (CareOf), name and external customer number from the billing address;
  • the quantity;
  • the net line total;
  • the ISO3 country codes of the billing address and the shipping address;
  • the currency code of the invoice;
  • LastSaleDate: the latest invoice date of any invoice that contains the item.

The net line total is calculated like this: the unit price of the line is divided by 1 + VAT rate, rounded to 2 decimals and then multiplied by the quantity. The VAT rate is the invoice's high rate for VAT code H, the low rate for L, and 0 for any other code.

The result has no defined sort order.

Notes

  • All tables are read with NOLOCK, so uncommitted data can appear in the result.
  • Without date parameters, the report covers exactly the last 24 hours up to the moment of the call. This is a rolling window, not the previous calendar day. To get whole days, pass DateTo as midnight of the day after the last day you want.
  • All joins are inner joins. Lines are left out if the invoice has no billing or shipping address, if a country cannot be resolved in Countries, or if the invoice currency is not found in pixi.Currency.
  • LastSaleDate ignores all filters, including the date range. It always shows the latest invoice date of the item across all invoices. Because it is a correlated subquery per row, large date ranges can be slow.
  • The net calculation uses VATHighPerc / VATLowPerc directly as 1 + rate. The result is only correct if these rates are stored as fractions (e.g. 0.19).
  • The unit net price is rounded before it is multiplied by the quantity, so small rounding differences against the invoice totals are possible.

Tables used

Invlines · Read Items · Read Invoices · Read Addresses · Read Countries · Read Currency · Read
SQL object:
dbo.pipiReportCustomerSales
Updated at 2026-10-30