pixiGetPendingPayments

Lists payments booked on customer accounts (CustomerAccount) that were still open, meaning not fully matched, at a given point in time.

Parameter handling

Date: if NULL or earlier than 1990-01-01, the current date and time (GETDATE()) is used.
Details: NULL is treated as 0.
ShopID: an empty string is treated as NULL, which means no shop filter.

Selection

For every customer account entry the latest log entry with event PAY and an EventDate strictly before Date is taken from CustomerAccountLog.
The entry is included only if Amount - Matched of that PAY log entry is at least 0.01.
OpenAmount is the amount of the customer account entry minus the Matched value of the latest log entry of any event type before Date. If there is no such entry, 0 is subtracted.
The payment type comes from the payment method of the Auftrag (delivery address APayment) or the Rechnung (Payment) that is linked to the bank line through BankMatch. If no payment method is found, ZZZ unbekannt is used.
With ShopID set, only customers whose CustomerOf equals ShopID are returned.

Currency

If the setting MultiCurrencyEnabled is not 1, amounts are returned in the original currency of the entry and dbCurrency is NULL.
If MultiCurrencyEnabled = 1, Amount and OpenAmount are converted into the database (home) currency by dividing them by the exchange rate. dbCurrency then holds the code of the database currency.

Output

Details = 1: one row per customer account entry, sorted by PaymentType, CustRef and CustomerAccountKey.
Details = 0 (default): one row per PaymentType and dbCurrency with the summed OpenAmount and the key date that was used, sorted by PaymentType.

Notes

All reads use NOLOCK, so results can include uncommitted data.
The filter uses the latest PAY log entry, but OpenAmount uses the latest log entry of any event type. The two values can therefore differ.
With multi-currency enabled, entries without a matching exchange rate for the database currency (via pixi.ExchRate, source from setting DatabaseCurrencyExchRateSourceId, base currency from setting HomeCurrency) are dropped by inner joins.
In the multi-currency branch, the subquery for the matched amount filters the exchange rate source on the outer alias instead of its own. If several rate sources exist, the TOP 1 rate that is used is not deterministic.
The join to Payments uses an OR on the Auftrag and Rechnung payment codes. If both are linked and differ, an entry can appear twice and is then counted twice in the summary.
In single-currency mode dbCurrency is NULL, so the summary mixes amounts of different original currencies within one payment type.
The key date comparison is strict (EventDate < Date). If only a date is passed, its time part is midnight, so events of that day are excluded.

Tables used

Settings · Read Currency · Read CustomerAccountLog · Read CustomerAccount · Read ExchRate · Read OrderHeader · Read BankMatch · Read Addresses · Read Invoices · Read Payments · Read Customers · Read

Parameter Type Default Description

Parameter	Type	Default	Description
`Date`	datetime dateTime	NULL	Key date. Only log entries strictly before this point in time are considered. `NULL` or values before 1990-01-01 are replaced by the current date and time. Example: `2024-04-01T00:00:00`
`Details`	bit boolean	0	Selects detail rows or a summary per payment type. `NULL` is treated as `0`. Allowed values `0` – Summary: open amount summed per payment type and currency. `1` – Details: one row per customer account entry. Example: `0`
`ShopID`	varchar(3) string	NULL	Optional shop filter on the customer's `CustomerOf`. Empty or `NULL` returns all shops. Example: `SH1`

Date

datetime
dateTime

NULL

Key date. Only log entries strictly before this point in time are considered. NULL or values before 1990-01-01 are replaced by the current date and time.

Example: 2024-04-01T00:00:00

Details

bit
boolean

0

Selects detail rows or a summary per payment type. NULL is treated as 0.

Allowed values

0 – Summary: open amount summed per payment type and currency.
1 – Details: one row per customer account entry.

Example: 0

ShopID

varchar(3)
string

NULL

Optional shop filter on the customer's CustomerOf. Empty or NULL returns all shops.

Example: SH1

Result set 1 · If `Details` = 1

One row per open customer account payment entry.

Column	Type	Description
`CustRef` Nullable	int	Customer key.
`CustomerAccountKey` Nullable	int	Key of the customer account entry.
`Comment` Nullable	varchar(512) string	Comment of the customer account entry.
`Reference` Nullable	varchar(512) string	Reference of the customer account entry.
`Amount` Nullable	money decimal	Amount of the entry, in original currency or converted to the database currency if multi-currency is enabled.
`OpenAmount` Nullable	money decimal	Amount not yet matched at the key date.
`PaymentType` Nullable	varchar(120) string	Payment method text of the linked Auftrag or Rechnung, or `ZZZ unbekannt` if none is found.
`PaymentDate` Nullable	datetime dateTime	Event date of the customer account entry.
`ShopID` Nullable	varchar(3) string	Shop of the customer (`CustomerOf`).
`dbCurrency` Nullable	varchar(3) string	Database currency code if multi-currency is enabled, otherwise `NULL`.

Result set 2 · If `Details` = 0 (default)

Open amounts summed per payment type and currency.

Column	Type	Description
`PaymentType` Nullable	varchar(120) string	Payment method text, or `ZZZ unbekannt`.
`OpenAmount` Nullable	money decimal	Sum of open amounts of this payment type.
`Date`	datetime dateTime	Key date that was actually used.
`dbCurrency` Nullable	varchar(3) string	Database currency code if multi-currency is enabled, otherwise `NULL`.

Response example

<SqlRowSet1>
  <row>
    <PaymentType>Vorkasse</PaymentType>
    <OpenAmount>1250.4000</OpenAmount>
    <Date>2024-04-01T00:00:00</Date>
    <dbCurrency>EUR</dbCurrency>
  </row>
  <row>
    <PaymentType>ZZZ unbekannt</PaymentType>
    <OpenAmount>89.9000</OpenAmount>
    <Date>2024-04-01T00:00:00</Date>
    <dbCurrency>EUR</dbCurrency>
  </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:pixiGetPendingPayments>
      <!-- <Date>2024-04-01T00:00:00</Date> -->
      <!-- <Details>0</Details> -->
      <!-- <ShopID>SH1</ShopID> -->
    </ns1:pixiGetPendingPayments>
  </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:pixiGetPendingPayments>
      <!-- <Date>2024-04-01T00:00:00</Date> -->
      <!-- <Details>0</Details> -->
      <!-- <ShopID>SH1</ShopID> -->
    </ns1:pixiGetPendingPayments>
  </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 = [
    // 'Date' => '2024-04-01T00:00:00',
    // 'Details' => '0',
    // 'ShopID' => 'SH1',
];

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

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

Notes

Tables used

Result set 1 · If Details = 1

Result set 2 · If Details = 0 (default)

Response example

Result set 1 · If `Details` = 1

Result set 2 · If `Details` = 0 (default)