pixiCustomersOpenCases

Returns a single row that sums up the open business of the customer identified by CustKey.

The behaviour depends on the setting MultiCurrencyEnabled.

Multi-currency disabled (setting is not 1):

Locked is 1 if the customer's SLocked flag is set to anything other than N, otherwise 0.
OpenOrders is the number of distinct order numbers that have at least one order line of this customer (Orderlines.CustRef) whose status is not a final state in OrderStates (FinalState = 'Y').
OpenOrderAmount is the sum of OrderLineAmount of these non-final order lines. The amounts are added up as stored, without currency conversion.
UnpaidInvoices is the number of invoices of the customer where the paid sum is not equal to the invoice total. A missing paid sum counts as 0.
DebtByInvoices is the sum of total - PayedSum over all invoices of the customer.
BaseCurrency is the database currency code. It comes from the setting HomeCurrency and must be a parent currency in pixi.Currency.

Multi-currency enabled (setting is 1):

The counts are calculated in the same way.
Each order line amount is divided by the exchange rate of its order header (OrderHeader.ExchRateId).
Each invoice debt is divided by the exchange rate of the invoice (Invoices.ExchRateId).
The results are therefore amounts in base currency.
BaseCurrency is the code of the base currency of the customer's shop (Shops.BaseCurrencyId).

If no customer with the given CustKey exists, the result set is empty.

Notes

All tables are read with NOLOCK, so the figures may include uncommitted data.
Without multi-currency, order and invoice amounts in different currencies are added up unconverted. BaseCurrency then only gives the database currency code; it is not the actual currency of the sum.
With multi-currency enabled, the following records are left out of the amounts and counts: order lines whose order header has no matching exchange rate, and invoices without a matching exchange rate. No row is returned if the customer's shop or its base currency cannot be found.
Invoices with a NULL paid sum are counted in UnpaidInvoices. Their debt is not part of DebtByInvoices, because total - NULL is ignored by SUM.
Overpaid invoices are also counted as unpaid and reduce DebtByInvoices.
DebtByInvoices is 0 if the customer has no invoices.
The setting value is converted to BIT. A non-numeric value other than true/false causes a conversion error.

Tables used

Settings · Read Currency · Read Customers · Read Orderlines · Read OrderStates · Read Invoices · Read OrderHeader · Read ExchRate · Read Shops · Read

Parameter	Type	Default	Description
`CustKey` Required	int	–	Key of the customer (`Customers.CustKey`). Example: `10452`

Result set 1 · If setting `MultiCurrencyEnabled` is not `1` and the customer exists

One row with the customer's open orders and invoices. Amounts are not converted.

Column	Type	Description
`Locked`	int	`1` if the customer is locked (`SLocked` other than `N`), otherwise `0`.
`OpenOrders`	int	Number of distinct orders with at least one order line in a non-final state.
`UnpaidInvoices`	int	Number of invoices whose paid sum differs from the total.
`DebtByInvoices`	decimal	Sum of `total - PayedSum` over all invoices of the customer.
`OpenOrderAmount`	decimal	Sum of `OrderLineAmount` of all non-final order lines, unconverted.
`BaseCurrency` Nullable	varchar(3) string	Code of the database currency (setting `HomeCurrency`).

Result set 2 · If setting `MultiCurrencyEnabled` is `1` and the customer, its shop and the shop's base currency exist

One row with the customer's open orders and invoices. Amounts are converted into the base currency.

Column	Type	Description
`Locked`	int	`1` if the customer is locked (`SLocked` other than `N`), otherwise `0`.
`OpenOrders`	int	Number of distinct orders with at least one non-final order line that has an exchange rate.
`UnpaidInvoices`	int	Number of invoices with an exchange rate whose paid sum differs from the total.
`DebtByInvoices`	decimal	Sum of `(total - PayedSum) / ExchRate`, i.e. the invoice debt in base currency.
`OpenOrderAmount`	decimal	Sum of `OrderLineAmount / ExchRate` of all non-final order lines, in base currency.
`BaseCurrency` Nullable	string	Code (`CodeA`) of the base currency of the customer's shop.

Response example

<SqlRowSet1>
  <row>
    <Locked>0</Locked>
    <OpenOrders>2</OpenOrders>
    <UnpaidInvoices>1</UnpaidInvoices>
    <DebtByInvoices>149.9000</DebtByInvoices>
    <OpenOrderAmount>312.5000</OpenOrderAmount>
    <BaseCurrency>EUR</BaseCurrency>
  </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:pixiCustomersOpenCases>
      <CustKey>10452</CustKey>
    </ns1:pixiCustomersOpenCases>
  </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:pixiCustomersOpenCases>
      <CustKey>10452</CustKey>
    </ns1:pixiCustomersOpenCases>
  </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' => '10452',
];

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

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

Notes

Tables used

Result set 1 · If setting MultiCurrencyEnabled is not 1 and the customer exists

Result set 2 · If setting MultiCurrencyEnabled is 1 and the customer, its shop and the shop's base currency exist

Response example

Result set 1 · If setting `MultiCurrencyEnabled` is not `1` and the customer exists

Result set 2 · If setting `MultiCurrencyEnabled` is `1` and the customer, its shop and the shop's base currency exist