Invoices Read

pixiGetRemindersCA

Returns invoices with their reminder history, reminder costs, open amount, customer account status and billing address, optionally filtered by reminder level, shop, payment type and date.

Returns one row per invoice that matches the filters, enriched with reminder (dunning) data, customer account (CA) figures and the invoice address.

Parameter normalisation

ReminderLevel = 0, ShopID = '' and PaymentType = '' are treated as NULL (no filter).
ReminderDays = 0 is treated as NULL. If ReminderDays > 0, InvoiceFrom and InvoiceTo are overwritten with the period from now minus ReminderDays days until now.
If InvoiceFrom is set and InvoiceTo is empty, InvoiceTo is set to the current date and time.

Filters (all combined with AND)

ReminderLevel: only invoices that have at least one reminder line with exactly this level. If NULL, all invoices are candidates, including invoices without any reminder.
InvoiceFrom/InvoiceTo: invoice date between both values (inclusive). Only applied if InvoiceFrom is set.
PaymentType: invoice payment type equals the value.
ShopID: the customer belongs to this shop (CustomerOf).
OnlyUnmatched = 1: only invoices whose paid sum differs from the total.
OnlyWhenCAUnbalanced: see notes – the effective value is taken from OnlyUnmatched; when active, only invoices of customers whose customer account balance is not 0.

Only invoices whose customer exists, belongs to an existing shop and whose invoice address exists are returned.

Returned data

Invoice data: key, number, customer and address key, shop, highest order number and the external order number of that order, date and total.
Reminder data: highest reminder level, earliest reminder date per level 1–6 (ReminderDate1 … ReminderDate6), costs of the latest reminder (highest reminder key) and the sum of costs of all reminders of the invoice.
OpenInvoiceAmount = total minus paid sum.
Customer account: CAMatched (1 if all positive or all negative account entries are fully matched, otherwise 0) and CAFinancialBalance (sum of the customer account; with multiple currencies converted with today's exchange rate if multi-currency is enabled).
Customer and address data (external customer number, company, name, street, ZIP, city, country, phone, fax, e-mail).

Rows are sorted by customer key; there is no further sort order within a customer and no paging.

Notes

Pitfall: the code sets OnlyWhenCAUnbalanced to the value of OnlyUnmatched. The value passed for OnlyWhenCAUnbalanced is therefore ignored; sending OnlyUnmatched = 1 activates both the paid-sum filter and the customer-account-balance filter.
Without ReminderLevel the call is not limited to reminded or open invoices. Without further filters it returns every invoice in the database, which can be very slow. Use OnlyUnmatched, a date range or ReminderLevel to narrow the result.
ReminderDays > 0 takes precedence over explicitly passed InvoiceFrom/InvoiceTo. InvoiceTo alone (without InvoiceFrom) has no effect.
CAMatched is stored in a temporary money column, so it is returned as a decimal value (1.00 / 0.00) instead of a boolean.
The customer account figures are calculated for all customers via fPixi_CA_IsMatched and fPixi_CA_FinancialBalance. The currency conversion depends on the setting MultiCurrencyEnabled and today's exchange rate; if no rate exists for today, the converted amount becomes NULL.
All reads use NOLOCK, so uncommitted data may be returned.
Only reminder levels 1 to 6 have their own date column.

Tables used

Invoices · Read Customers · Read ReminderLines · Read Reminders · Read Orderlines · Read InvLines · Read Addresses · Read CustomerAccount · Read Shops · Read ExchRate · Read Settings · Read

Parameter	Type	Default	Description
`ReminderLevel`	int	NULL	Only invoices with at least one reminder line of this reminder level. NULL or 0: no filter (also invoices without reminders). Example: `2`
`PaymentType`	char(1) string	NULL	Payment type code of the invoice. NULL or empty: no filter. Example: `R`
`ShopID`	char(3) string	NULL	Shop the customer belongs to (`CustomerOf`). NULL or empty: all shops. Example: `SHP`
`ReminderDays`	int	0	If > 0, restricts the invoice date to the last N days up to now and overrides `InvoiceFrom`/`InvoiceTo`. 0: not used. Example: `30`
`InvoiceFrom`	datetime dateTime	NULL	Start of the invoice date range (inclusive). NULL: no date filter. Example: `2024-01-01T00:00:00`
`InvoiceTo`	datetime dateTime	NULL	End of the invoice date range (inclusive). Defaults to now if `InvoiceFrom` is set. Ignored without `InvoiceFrom`. Example: `2024-03-31T23:59:59`
`OnlyUnmatched`	bit boolean	0	1: only invoices whose paid sum differs from the total. Also activates the customer account balance filter (see notes). Allowed values `0` – No filter on paid sum `1` – Only invoices not fully paid (paid sum <> total) and customers with balance <> 0 Example: `1`
`OnlyWhenCAUnbalanced`	bit boolean	0	Intended to return only customers with a customer account balance <> 0. The passed value is ignored; the code uses the value of `OnlyUnmatched` instead. Example: `0`

Result set 1 · Always (may be empty)

One row per matching invoice with reminder, customer account and address data, sorted by customer key.

Column	Type	Description
`InvoiceKey`	int	Internal invoice key
`InvoiceNr` Nullable	string	Invoice number
`CustKey`	int	Customer key of the invoice
`AddrKey`	int	Key of the invoice address
`ShopID` Nullable	char(3) string	Shop the customer belongs to
`LastOrderNr` Nullable	int	Highest order number of the order lines on the invoice
`LastOrderNrExternal` Nullable	string	External order number of the order with the highest order number
`InvoiceDate` Nullable	datetime dateTime	Invoice date
`InvoiceTotal` Nullable	money decimal	Invoice total
`CurrentReminderLevel` Nullable	int	Highest reminder level of the invoice; NULL if not reminded
`ReminderDate1` Nullable	datetime dateTime	Earliest reminder date of level 1
`ReminderDate2` Nullable	datetime dateTime	Earliest reminder date of level 2
`ReminderDate3` Nullable	datetime dateTime	Earliest reminder date of level 3
`ReminderDate4` Nullable	datetime dateTime	Earliest reminder date of level 4
`ReminderDate5` Nullable	datetime dateTime	Earliest reminder date of level 5
`ReminderDate6` Nullable	datetime dateTime	Earliest reminder date of level 6
`LastReminderCosts` Nullable	money decimal	Costs of the latest reminder (highest reminder key) of the invoice
`AllReminderCosts` Nullable	money decimal	Sum of the costs of all reminders of the invoice
`OpenInvoiceAmount` Nullable	money decimal	Invoice total minus paid sum
`CAMatched` Nullable	money decimal	1.00 if the customer account is fully matched, otherwise 0.00
`CAFinancialBalance` Nullable	money decimal	Balance of the customer account (converted to base currency if multi-currency is enabled)
`CustomerNrExternal` Nullable	string	External customer number
`Company` Nullable	string	Company (CareOf) of the invoice address
`FirstName` Nullable	string	First name
`LastName` Nullable	string	Last name
`FullName` Nullable	string	Full name
`Street` Nullable	string	Street
`HouseNr` Nullable	string	House number
`AddressLine` Nullable	string	Address line
`ZIP` Nullable	string	Postal code
`City` Nullable	string	City
`State` Nullable	string	State / region
`Country` Nullable	string	Country
`Phone` Nullable	string	Phone number
`Fax` Nullable	string	Fax number
`Email` Nullable	string	E-mail address

Response example

<SqlRowSet1>
  <row>
    <InvoiceKey>48211</InvoiceKey>
    <InvoiceNr>2024010532</InvoiceNr>
    <CustKey>10233</CustKey>
    <AddrKey>55120</AddrKey>
    <ShopID>SHP</ShopID>
    <LastOrderNr>300512</LastOrderNr>
    <LastOrderNrExternal>WEB-100245</LastOrderNrExternal>
    <InvoiceDate>2024-02-03T10:15:00</InvoiceDate>
    <InvoiceTotal>149.9000</InvoiceTotal>
    <CurrentReminderLevel>2</CurrentReminderLevel>
    <ReminderDate1>2024-02-20T08:00:00</ReminderDate1>
    <ReminderDate2>2024-03-05T08:00:00</ReminderDate2>
    <LastReminderCosts>5.0000</LastReminderCosts>
    <AllReminderCosts>7.5000</AllReminderCosts>
    <OpenInvoiceAmount>149.9000</OpenInvoiceAmount>
    <CAMatched>0.0000</CAMatched>
    <CAFinancialBalance>-149.9000</CAFinancialBalance>
    <CustomerNrExternal>K-4711</CustomerNrExternal>
    <FirstName>Anna</FirstName>
    <LastName>Muster</LastName>
    <FullName>Anna Muster</FullName>
    <Street>Hauptstraße</Street>
    <HouseNr>12</HouseNr>
    <ZIP>80331</ZIP>
    <City>München</City>
    <Country>DE</Country>
    <Email>anna.muster@example.com</Email>
  </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:pixiGetRemindersCA>
      <!-- <ReminderLevel>2</ReminderLevel> -->
      <!-- <PaymentType>R</PaymentType> -->
      <!-- <ShopID>SHP</ShopID> -->
      <!-- <ReminderDays>30</ReminderDays> -->
      <!-- <InvoiceFrom>2024-01-01T00:00:00</InvoiceFrom> -->
      <!-- <InvoiceTo>2024-03-31T23:59:59</InvoiceTo> -->
      <!-- <OnlyUnmatched>1</OnlyUnmatched> -->
      <!-- <OnlyWhenCAUnbalanced>0</OnlyWhenCAUnbalanced> -->
    </ns1:pixiGetRemindersCA>
  </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:pixiGetRemindersCA>
      <!-- <ReminderLevel>2</ReminderLevel> -->
      <!-- <PaymentType>R</PaymentType> -->
      <!-- <ShopID>SHP</ShopID> -->
      <!-- <ReminderDays>30</ReminderDays> -->
      <!-- <InvoiceFrom>2024-01-01T00:00:00</InvoiceFrom> -->
      <!-- <InvoiceTo>2024-03-31T23:59:59</InvoiceTo> -->
      <!-- <OnlyUnmatched>1</OnlyUnmatched> -->
      <!-- <OnlyWhenCAUnbalanced>0</OnlyWhenCAUnbalanced> -->
    </ns1:pixiGetRemindersCA>
  </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 = [
    // 'ReminderLevel' => '2',
    // 'PaymentType' => 'R',
    // 'ShopID' => 'SHP',
    // 'ReminderDays' => '30',
    // 'InvoiceFrom' => '2024-01-01T00:00:00',
    // 'InvoiceTo' => '2024-03-31T23:59:59',
    // 'OnlyUnmatched' => '1',
    // 'OnlyWhenCAUnbalanced' => '0',
];

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

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

SQL object:: dbo.pipiGetRemindersCA