Pixi API documentation

Unofficial · by koempf24.de

Postman collection
Customers Read

pixiGetCRMSales

Returns the cumulated net sales per customer from invoices, reduced by referencing credit notes, optionally filtered by shops and invoice date, with keyset paging.

Calculates a sales figure per customer (CustKey) from the invoice table and returns one row per customer and base currency.

Steps

  1. An empty CustomerOf is treated as NULL. If given, it is parsed as XML (/SHOPS/SHOP, value SHOPID, as element or attribute). Only shop IDs that exist in Shops are kept.
  2. Invoices are selected if:
    • the gross total Total is greater than 0 (credit notes with negative totals are not counted as primary invoices),
    • the invoice date InvDate lies within CalculationStart … CalculationEnd (each bound inclusive and optional),
    • when CustomerOf is set, the customer's CustomerOf shop is one of the given shops.
  3. For each invoice, the net total (Total_Net) and the net total without shipping costs (Total_Net - Total_ShipCost_Net) are taken. The net totals of all invoices that reference it via RefToInvoiceKey (typically credit notes) are added to these values.
  4. Currency handling depends on the setting MultiCurrencyEnabled:
    • not 1: amounts stay in the original invoice currency and BaseCurrency is NULL;
    • 1: every amount is divided by the exchange rate of the invoice (pixi.ExchRate) and BaseCurrency contains the code of the base currency of that rate.
  5. The results are grouped by CustKey and BaseCurrency.

Paging

Paging uses keys: only customers with CustKey greater than Offset are returned, sorted by CustKey ascending, limited to Rows rows. For the next page, pass the last returned CustKey as Offset. Offset is not a row count.

Notes

  • All reads use NOLOCK, so results may include uncommitted data.
  • If CustomerOf is set but none of the shop IDs exist in Shops, no rows are returned. Invalid XML causes an error raised by sp_xml_preparedocument.
  • Without multi-currency (MultiCurrencyEnabled not 1), amounts in different invoice currencies are summed together without conversion.
  • With multi-currency, invoices without an exchange rate (ExchRateId not found in pixi.ExchRate) are excluded, and referencing credit notes without an exchange rate are ignored in the sums. Converted amounts are stored with 9 decimal places.
  • With multi-currency a customer can appear in several rows (one per base currency). Because Rows limits rows, not customers, a page can end in the middle of a customer's rows; using that CustKey as the next Offset skips the remaining rows of that customer.
  • The date filter is applied only to the primary invoice; referencing credit notes are included regardless of their date.
  • A total-return flag is calculated internally but not returned.
  • The database currency is read via pixi.GetDatabaseCurrency(), but the values are not used in the result.

Parameters

Parameter Type Default Description
CustomerOf
varchar(max)
xml		 NULL

XML list of pixi* shops (/SHOPS/SHOP/SHOPID). Only customers belonging to these shops are evaluated. NULL or empty: all customers.

Example:

<SHOPS>
  <SHOP>
    <SHOPID>ABC</SHOPID>
  </SHOP>
  <SHOP>
    <SHOPID>XYZ</SHOPID>
  </SHOP>
</SHOPS>
CalculationStart
datetime
dateTime		 NULL

Earliest invoice date (inclusive). NULL: no lower bound.

Example: 2024-01-01T00:00:00
CalculationEnd
datetime
dateTime		 NULL

Latest invoice date (inclusive). NULL: no upper bound.

Example: 2024-12-31T23:59:59
Rows
int 1000

Maximum number of rows returned.

Example: 1000
Offset
int 0

Only customers with CustKey greater than this value are returned. Pass the last CustKey of the previous page.

Example: 0

Response

Result set 1 · Always (may be empty).

Cumulated sales per customer and base currency, sorted by CustKey ascending.

Column Type Description
CustKey
Nullable
 int

Customer key (Invoices.CustRef).
TotalCompound
Nullable
 decimal(38,9)
decimal

Sum of net invoice totals including the net totals of referencing credit notes; converted to the base currency if multi-currency is enabled.
TotalWithoutShipcostsCompound
Nullable
 numeric(17,4)
decimal

Same as TotalCompound, but without net shipping costs.
BaseCurrency
Nullable
 varchar(3)
string

Code of the base currency of the amounts if multi-currency is enabled; otherwise NULL (amounts in original invoice currency).

Response example

<SqlRowSet1>
  <row>
    <CustKey>1001</CustKey>
    <TotalCompound>1523.450000000</TotalCompound>
    <TotalWithoutShipcostsCompound>1488.4500</TotalWithoutShipcostsCompound>
    <BaseCurrency>EUR</BaseCurrency>
  </row>
  <row>
    <CustKey>1002</CustKey>
    <TotalCompound>89.900000000</TotalCompound>
    <TotalWithoutShipcostsCompound>84.9000</TotalWithoutShipcostsCompound>
    <BaseCurrency>EUR</BaseCurrency>
  </row>
</SqlRowSet1>

Examples

POST https://apigateway.descartes.com/tms/pixi/
Content-Type: text/xml; charset=utf-8
Authorization: Basic BASE64(USERNAME:PASSWORD)

<?xml version="1.0" encoding="UTF-8"?>
<SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" xmlns:ns1="https://apigateway.descartes.com/tms/pixi/">
  <SOAP-ENV:Body>
    <ns1:pixiGetCRMSales>
      <!-- <CustomerOf><![CDATA[<SHOPS><SHOP><SHOPID>ABC</SHOPID></SHOP><SHOP><SHOPID>XYZ</SHOPID></SHOP></SHOPS>]]></CustomerOf> -->
      <!-- <CalculationStart>2024-01-01T00:00:00</CalculationStart> -->
      <!-- <CalculationEnd>2024-12-31T23:59:59</CalculationEnd> -->
      <!-- <Rows>1000</Rows> -->
      <!-- <Offset>0</Offset> -->
    </ns1:pixiGetCRMSales>
  </SOAP-ENV:Body>
</SOAP-ENV:Envelope>

Tables used

Invoices · Read Customers · Read Shops · Read ExchRate · Read Currency · Read Settings · Read
SQL object:
dbo.pipiGetCRMSales
Procedure last changed on 2026-10-05
Documentation written by claude-opus-5-5 on 2026-10-30