Pixi API documentation

Unofficial · by koempf24.de

Postman collection
Orders Read

pixiGetOrder

Returns full details of one or more orders, including addresses, customer, order lines, bundle lines, invoices and customer account entries as nested XML.

Collects a set of order numbers from the three input parameters and returns one row per distinct order number with the order header fields and several nested XML documents.

Selecting orders (all parameters can be combined; the results are merged and de-duplicated):

  • OrderNr: used directly if it is greater than 0. It is not checked against OrderHeader.
  • OrderNrExternal: every order whose OrderNrExternal matches exactly is added.
  • OrderNrXML: an XML document with root OrderNrs; each OrderNr element adds that order number, each OrderNrExternal element adds all orders with this external number.

If no order number is collected, the call returns no result set at all.

Per order the row contains:

  • Order header fields from OrderHeader (shipping vendor, gift message, status, totals, references, flags, OrderType, ShipCost …).
  • BillingAddress and ShippingAddress: XML of the address referenced by AddrRef / ShipAdrRef, enriched with country names and country codes (ISO2, ISO3, Afterbuy, Plenty).
  • Customer: XML of the customer referenced by CustRef.
  • Orderlines: XML of all order lines with currency code (OrderCurrency), item data and PickListNr.
  • BundleOrderlines: XML of the bundle order lines of the order.
  • Invoices: XML of all invoices that contain at least one invoice line referencing an order line of this order; each invoice contains its InvoiceLines.
  • CustomerAccount: XML of customer account entries of the order's customer that either belong to one of these invoices, or have no invoice and are linked to the order via a bank match; sorted by CustomerAccountKey ascending.
  • OnPicklist: picklist state of the order lines (None, Partial, All).
  • VoucherID: the highest VoucherID found on the order lines.

The order of the rows is not defined (no ORDER BY).

Notes

  • All reads use NOLOCK; data can be uncommitted or inconsistent.
  • A non-existing OrderNr still produces a row: OrderNr is filled, all header fields and XML columns are NULL.
  • Malformed OrderNrXML is silently ignored (empty CATCH). The XML document handle is never released with sp_xml_removedocument.
  • OnPicklist logic: None if no line is on a picklist, Partial if some lines have a picklist and some do not, All if all lines are on one or more picklists. It is NULL for orders without order lines.
  • Order lines and bundle lines are joined to Items and Currency with inner joins; lines whose item no longer exists are missing from the XML.
  • The nested XML uses FOR XML PATH without a row name, so every record is a row element; NULL values are omitted.
  • Fields ending in _OrderCurr (e.g. Price_OrderCurr, Total_OrderCurr) are duplicates of the base fields and are marked in the code to be removed; do not rely on them.
  • Fetching many orders at once can be expensive because of the many correlated subqueries per order.

Tables used

OrderHeader · Read Orderlines · Read BundleOrderlines · Read Addresses · Read Countries · Read Customers · Read Currency · Read Items · Read Invoices · Read Invlines · Read CustomerAccount · Read BankMatch · Read
SQL object:
dbo.pipiGetOrder
Updated at 2026-10-30