Pixi API documentation

Unofficial · by koempf24.de

Postman collection
Invoices Read

pixiGetInvoiceLinesExtended

Returns invoice headers combined with their invoice lines in one flat result set, filtered by invoice, shop, order, customer, payment and date criteria.

Combines invoice header data and invoice line data into one call. Each result row is one invoice line, with all header, billing address and payment fields repeated on every line of the same invoice.

Parameter normalisation

  • Empty strings are treated as NULL for all string and date filters; OrderNR = 0 is treated as NULL.
  • RowCount defaults to 1000; NULL, 0 or negative values are also replaced by 1000.

Step 1 – candidate invoices (dynamic SQL, executed with sp_executesql) Up to RowCount * 3 invoices are selected from Invoices (joined to Customers, left-joined to InvLines), ordered by invoice date. Only the filters that are set are added:

  • InvoiceNr: exact invoice number.
  • ShopID: Customers.CustomerOf.
  • PaymentType: Invoices.Payment.
  • InvDateFrom / InvDateTo: invoice date strictly greater / strictly less than the value.
  • UpdateDateFrom / UpdateDateTo: strictly greater / less than the value, on InvLines.UpdateDate (default) or on Invoices.UpdateDate if ConsiderInvoiceUpdates = 1. Records with UpdateDate NULL never match.
  • OrderNR: the invoice has at least one invoice line linked to an order line of that Auftrag.
  • OrderNRExternal: resolved to an internal order number via OrderHeader.OrderNrExternal, then filtered like OrderNR.
  • OnlyUnpaidInvoices = 1: paid sum differs from the total, and there is no open CustomerAccount entry for the invoice marked DoNotRefund = 1 (without bank line, DTAUS or manual booking reference).
  • CustomerNrExternal: Customers.CustomerNrExternal.

Step 2 – headers and lines

  • The first RowCount candidates (ordered by invoice date and invoice key) are loaded as headers. Only invoices with a matching currency, a payment code that exists in Payments and a billing address in Addresses are kept.
  • Lines are read from the view PV_Invlines, which also contains pseudo lines: ItemKey = -2 shipping costs, -1 vouchers, -3 manual invoices without lines. Supplier price and supplier number are taken from SupplierPrice_History if present.
  • If AddCCPPayID = 1, the ccpPayID values of all Aufträge on the invoice are concatenated with commas into CcpPayID.

Step 3 – final filter and output

  • Pseudo lines (ItemKey -1, -2, -3) are only returned if IncludeSpecialInvLines = 1.
  • Status filters on the order line status of the line (AUS if the line has no status).
  • IsReplacementOnly = 1 returns only lines whose order line has status MIN or has an OrderHistory entry with status MIN (replacement deliveries).
  • At most RowCount rows (lines) are returned, ordered by invoice date and invoice key.
  • UpdateDate in the output is the line's update/create date (falling back to the invoice date) by default, or the invoice's update/create date if ConsiderInvoiceUpdates = 1.

Notes

  • All reads use NOLOCK, so uncommitted data can appear.
  • RowCount limits invoices and the final line rows. Because the final TOP counts lines, the lines of the last invoice can be cut off. For complete invoices, query by InvoiceNr or use a RowCount well above the expected number of lines.
  • Date bounds are exclusive (> and <), not inclusive.
  • With the default ConsiderInvoiceUpdates = 0, the update date filters work on invoice lines, so invoices without lines (e.g. manual invoices) never match them.
  • If OrderNRExternal cannot be resolved in OrderHeader, the result is empty. If several orders share the external number, only one of them is used.
  • Lines whose ItemKey is NULL (invoice lines without order line or item) are dropped by the NOT IN (-1,-2,-3) test unless IncludeSpecialInvLines = 1.
  • With IsReplacementOnly = 1, pseudo lines are never returned because they have no order line.
  • CcpPayID is truncated to 50 characters.
  • Currency is always the constant EUR; the actual order currency is in OrderCurrency.
  • OrderDate returns 1900-01-01 if the line has no Auftrag (empty string converted to a date). EANUPC is 0 for lines without an Artikel.
  • OrderNr appears twice in each row.
  • Paid is 1 only if the paid sum equals the invoice total exactly.

Tables used

OrderHeader · Read Invoices · Read Customers · Read InvLines · Read Orderlines · Read CustomerAccount · Read SupplierPrice_History · Read Currency · Read Payments · Read Addresses · Read items · Read OrderHistory · Read Suppliers · Read TontrArten · Read
SQL object:
dbo.pipiGetInvoiceLinesExtended
Updated at 2026-10-30