pixiGetCustomerAccountBalance · Pixi API documentation

Calculates the balance of the customer account (CustomerAccount) for one or more customers.

Customer lookup

CustKey is compared with the external customer number (CustomerNrExternal) and, if it can be converted to an integer, with the internal customer key (CustKey). A customer matches if either comparison is true.
If ShopId is given (not empty), only customers whose CustomerOf equals this shop are considered.

Date limit

If DateTo is given, it is extended to the end of that day (23:59:59.997). Only account entries with an EventDate up to this point, or without an EventDate, are summed.
Without DateTo, all entries are summed.

Currency handling

The setting MultiCurrencyEnabled is read at runtime.
If it is 1, each entry amount is divided by the exchange rate of the entry (ExchRateId) and the result is grouped by the base currency of that exchange rate (BaseCurrency).
Otherwise amounts are summed as stored and BaseCurrency is the database (home) currency from the setting HomeCurrency.

Result One row per matching customer (and per base currency in multi-currency mode) with the balance CAbalance (as money) and the date of the latest included account entry LastEventDate. Customers without any account entries are returned with balance 0 and LastEventDate = NULL. No row is returned if no customer matches.

Notes

All tables are read with NOLOCK; the balance may include uncommitted data.
Because CustKey is matched against both the external number and the internal key, more than one customer can be returned (e.g. one customer with internal key 123 and another with external number 123). Use ShopId or check CustomerId in the result.
Pitfall with DateTo: the date filter is applied after the outer join. A customer whose account entries all lie after DateTo is not returned at all (no row instead of a row with balance 0).
Multi-currency mode: entries without an exchange rate produce NULL in the division and are ignored in the sum; they also form their own group with BaseCurrency = NULL. Entries with different base currencies yield separate rows for the same customer.
An empty ShopId is treated like NULL (no shop filter).

Tables used

Customers · Read CustomerAccount · Read ExchRate · Read Currency · Read Settings · Read

Parameter	Type	Default	Description
`CustKey` Required	varchar(200) string	–	Customer identifier: either the external customer number (`CustomerNrExternal`) or the internal customer key (`CustKey`, if numeric). Example: `10045`
`DateTo`	datetime dateTime	NULL	Optional cut-off date. Only account entries up to the end of this day are included. NULL means all entries. Example: `2024-03-31T00:00:00`
`ShopId`	varchar(3) string	NULL	Optional shop filter on the customer's `CustomerOf`. NULL or empty means all shops. Example: `SH1`

Result set 1 · Always; one row per matching customer (and base currency in multi-currency mode), empty if no customer matches.

Customer account balance per customer.

Column	Type	Description
`CustomerId`	int	Internal customer key (`Customers.CustKey`).
`CustomerNrExternal` Nullable	string	External customer number.
`ShopId` Nullable	string	Shop the customer belongs to (`Customers.CustomerOf`).
`CAbalance` Nullable	money decimal	Sum of the included account entry amounts; in multi-currency mode converted by the exchange rate of each entry.
`BaseCurrency` Nullable	varchar(3) string	Currency of the balance: base currency of the exchange rate (multi-currency mode) or the database home currency.
`LastEventDate` Nullable	datetime dateTime	Latest `EventDate` of the included account entries; NULL if there are none.

Response example

<SqlRowSet1>
  <row>
    <CustomerId>10045</CustomerId>
    <CustomerNrExternal>K-778812</CustomerNrExternal>
    <ShopId>SH1</ShopId>
    <CAbalance>-125.5000</CAbalance>
    <BaseCurrency>EUR</BaseCurrency>
    <LastEventDate>2024-03-28T14:12:05</LastEventDate>
  </row>
</SqlRowSet1>

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:pixiGetCustomerAccountBalance>
      <CustKey>10045</CustKey>
      <!-- <DateTo>2024-03-31T00:00:00</DateTo> -->
      <!-- <ShopId>SH1</ShopId> -->
    </ns1:pixiGetCustomerAccountBalance>
  </SOAP-ENV:Body>
</SOAP-ENV:Envelope>

curl -X POST 'https://apigateway.descartes.com/tms/pixi/' \
  --user 'USERNAME:PASSWORD' \
  --header 'Content-Type: text/xml; charset=utf-8' \
  --data-binary @- <<'XML'
<?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:pixiGetCustomerAccountBalance>
      <CustKey>10045</CustKey>
      <!-- <DateTo>2024-03-31T00:00:00</DateTo> -->
      <!-- <ShopId>SH1</ShopId> -->
    </ns1:pixiGetCustomerAccountBalance>
  </SOAP-ENV:Body>
</SOAP-ENV:Envelope>
XML

<?php

$soapClient = new SoapClient(null, [
    'login' => 'USERNAME',
    'password' => 'PASSWORD',
    'location' => 'https://apigateway.descartes.com/tms/pixi/',
    'uri' => 'https://apigateway.descartes.com/tms/pixi/',
]);

$arguments = [
    'CustKey' => '10045',
    // 'DateTo' => '2024-03-31T00:00:00',
    // 'ShopId' => 'SH1',
];

$soapArguments = [];
foreach ($arguments as $name => $value) {
    $soapArguments[] = new SoapVar($value, XSD_STRING, '', '', $name);
}

$response = $soapClient->__soapCall('pixiGetCustomerAccountBalance', $soapArguments);