Pixi API documentation

Unofficial · by koempf24.de

Postman collection
Orderlines Read

pixiGetOrderline

Returns order lines filtered by key, order, status, shop, payment type, dates or invoice, including prices, VAT, shipping data and available stock.

Returns order lines (Orderlines) and bundle order lines (BundleOrderlines) that match all given filters, sorted by OrderlineKey.

Input normalisation

  • OrderlineKey and OrderNR with value 0 and empty strings for the text and date parameters are treated as not set.
  • RowCount NULL becomes 1000.
  • If OrderNRExternal contains DROP, ALTER, DELETE, TRUNCATE or UPDATE, the procedure returns immediately without any result set.

Filters (combined with AND, only applied when set)

  • OrderlineKey, OrderNR, OrderNRExternal, Status: exact match on the order line.
  • ShopID: CustomerOF of the customer of the order line.
  • PaymentType: APayment of the address of the order line.
  • OrderDateFrom/OrderDateTo and UpdateDateFrom/UpdateDateTo: exclusive bounds (> and <).
  • InvoiceNr: only lines that appear as invoice lines of the invoice with this number.

Bundle handling (ShowOrderlines)

  • 1: bundles are not processed as normal items; all order lines, including the components of bundles, are returned.
  • 0: bundles are processed as normal items; component lines (with a bundle reference) are skipped and the bundle line itself is returned from BundleOrderlines.
  • not set: taken from the setting ProcessBundleItemsAsNormalItems (default 0).

Bundle lines are selected with the same filters; the date and invoice filters are checked against the component order lines, and the OrderNR/OrderNRExternal filters only match bundle lines when bundles are processed as normal items.

Row limit: first up to RowCount normal order lines are collected (ascending OrderlineKey), then bundle lines fill the remaining quota. There is no offset; for paging, use OrderlineKey or date filters.

Computed values

  • FullPrice = Qty × Price; FullPriceForItem is the stored full price of the line.
  • VATRate and PriceNoVAT (rounded to 2 decimals) come from the first invoice line of the order line if present, otherwise from the item VAT class (H, L, N) and the net price of the line.
  • ShipDate is the scan-out date of the invoice (or the invoice it refers to).
  • AvailStockOrderLine = stock quantity of the item at the order line's location minus the number of earlier reservations of the same item by order lines in active (non-final) states other than EIN, FUT, FUS.
  • ReservedCount = the smaller of Qty and AvailStockOrderLine, but not below 0.

Bundle lines have no shipping, stock or estimated-delivery data (NULL).

Notes

  • Read-only; all tables are read with NOLOCK, so uncommitted data may appear.
  • The filter query is built as dynamic SQL but executed with parameters via sp_executesql.
  • The keyword check on OrderNRExternal returns silently (no rows, no error) – an external order number that merely contains e.g. UPDATE cannot be searched.
  • RowCount limits the candidate lines before the final joins. Order lines without an order header, currency, item, order state or an ItemLocationSpecific row for their item and location are dropped, so fewer rows than RowCount may be returned.
  • If ShowOrderlines = 1 (or the setting is 0) and no OrderNR/OrderNRExternal filter is set, bundle lines from BundleOrderlines can appear in addition to their component lines.
  • ReservedCount is NULL for bundle lines because AvailStockOrderLine is NULL there.

Tables used

OrderStates · Read Orderlines · Read Customers · Read Addresses · Read Invlines · Read Invoices · Read BundleOrderlines · Read Reservation · Read OrderHeader · Read Currency · Read Items · Read ItemLocationSpecific · Read Orderline_EstimatedDelivery · Read Settings · Read
SQL object:
dbo.pipiGetOrderline
Updated at 2026-10-30