pixiGetCAEventsAndReminders

Collects all customer account events of the customers of shop ShopId that happened in a date range and returns them as one event list. Reminder sendings and full payments of invoices are treated as additional events.

Date range

If StartDate is NULL, the range starts on the first day of the current month. Otherwise it starts at 00:00 of StartDate.
If EndDate is NULL, the range ends at the end of today. Otherwise it ends at 23:59:59.997 of EndDate.

Event types collected

Invoice events: CustomerAccount entries with EventID = 'INV' whose EventDate is in the range. EventKey = CAE_<CustomerAccountKey>.
Reminder events: reminders (Reminders joined with ReminderLines) whose ReminderSentDate is in the range. They are returned with EventID = 'REM'. The amount is the negative of reminder total minus reminder costs, and ReminderLevel comes from the reminder line. EventKey = REM_<ReminderKey>.
Match events: CustomerAccountMatch records created in the range that link an invoice entry (EventID = 'INV') with another, different customer account entry. Both link directions are taken into account. The row carries the EventID and EventDate of the counterpart entry (for example a payment). The amount is the absolute match amount, and EventUpdateDate is the match creation date. EventKey = CAM_<CustomerAccountMatchKey>.
Virtual full-payment events: for an invoice of a returned customer whose matches up to EndDate add up exactly to the invoice total, a virtual event is created with EventID = 'REM' and amount 0. Its date is the last match creation date plus 1 ms. The customer must also have at least one match event in the range, and the event is dropped if its date is before the range start. EventKey = FPY_<CustomerAccountKey>.

Customer filter

Only customers whose CustomerOf equals ShopId are returned.
If CustomerNumberIsNumeric = 1, only customers whose CustomerNrExternal consists solely of digits are returned. With 0 (default, or NULL) all customers of the shop are returned.

Reminder levels

For every invoice without a virtual full-payment event, the highest HighestReminderLevel of all reminders containing the invoice is determined (invoice dated before EndDate).
This value is written to ReminderLevel of all non-REM events of the invoice.
CustomerReminderLevel is the maximum of these levels over all open invoices of the customer. It is set on all events of the customer.

Amounts and currencies

Balance_ShopCurr is the sum of all customer account amounts with EventDate before EndDate.
If the setting MultiCurrencyEnabled is 1, these amounts are converted with the exchange rate of the current day.
Amount_ShopCurr is the event amount converted the same way. A missing rate counts as 1.

Output

Each event row contains the external customer number, invoice number and external order number.
Only events that can be linked to an invoice in Invoices are returned.
The result is DISTINCT and sorted by EventUpdateDate, then EventDate.

Notes

All reads use NOLOCK; results may contain uncommitted data.
Exchange rates are always taken for the current date (ExchDate = today), not for the event date. With MultiCurrencyEnabled = 1 and no rate for today, entries in foreign currency are left out of Balance_ShopCurr (division by NULL). Amount_ShopCurr falls back to a rate of 1 in that case.
The setting DoNOTChargeShipCostOnSecondDelivery is read but not used.
An invoice with order lines from several external orders produces one row per OrderNrExternal.
Reminder rows are built by joining the reminder lines twice. A reminder with several lines can therefore return several rows with different InvoiceNr/ReminderLevel combinations.
Virtual FPY_ events are produced once per customer account entry of the invoice, so more than one FPY_ row per invoice is possible.
An unknown ShopId returns an empty result, not an error.
Temporary tables are dropped at the end via util.TempTableDrop (not attached).

Tables used

CustomerAccount · Read Reminders · Read ReminderLines · Read CustomerAccountMatch · Read Customers · Read Shops · Read Invoices · Read Currencies · Read ExchRate · Read Currency · Read Invlines · Read Orderlines · Read Settings · Read

Parameter	Type	Default	Description
`StartDate`	datetime dateTime	NULL	Start of the date range (time part is ignored, 00:00 is used). NULL means the first day of the current month. Example: `2024-03-01T00:00:00`
`EndDate`	datetime dateTime	NULL	End of the date range (end of the given day is used). NULL means the end of today. Example: `2024-03-31T00:00:00`
`ShopId` Required	varchar(3) string	–	Shop whose customers are returned (compared with `Customers.CustomerOf`). Must not be empty. Example: `SHP`
`CustomerNumberIsNumeric`	bit boolean	0	Restricts the result to customers with a purely numeric external customer number. NULL is treated as 0. Allowed values `0` – All customers of the shop `1` – Only customers whose `CustomerNrExternal` contains digits only Example: `0`

Result set 1 · If `ShopId` is NULL or empty

Error status row; no further result is returned.

Column	Type	Description
`Status`	varchar(5) string	Always `Error`.
`Message`	varchar(66) string	Error text.

Result set 2 · If `ShopId` is provided

List of customer account events, reminder events and virtual full-payment events, sorted by EventUpdateDate, EventDate.

Column	Type	Description
`CustomerNrExternal` Nullable	string	External customer number (`Customers.CustomerNrExternal`).
`InvoiceNr` Nullable	string	Number of the invoice the event belongs to.
`OrderNrExternal` Nullable	string	External order number from the invoice's order lines; NULL if none is found.
`EventID`	string	Event type: `INV` for invoices, `REM` for reminders and virtual full-payment events, or the event type of the matched counterpart entry for match events.
`Amount` Nullable	numeric(19,4) decimal	Amount in event currency: account amount (`CAE_`), negative reminder amount without costs (`REM_`), absolute match amount (`CAM_`) or 0 (`FPY_`).
`EventCurr` Nullable	string	Currency code of the event (`pixi.Currency.CodeA`).
`EventDate` Nullable	dateTime	Date of the event (for match events: date of the counterpart account entry).
`Amount_ShopCurr` Nullable	numeric(19,4) decimal	Amount converted into the shop's base currency at today's rate (only with multi-currency enabled, otherwise equal to `Amount`).
`ShopCurr` Nullable	string	Base currency code of the shop (`Currencies.CurCode`).
`Balance_ShopCurr` Nullable	money decimal	Customer account balance up to `EndDate` in shop currency.
`ReminderLevel` Nullable	int	For `REM_` events the level of the reminder line; for other events the highest reminder level of the open invoice, otherwise 0.
`CustomerReminderLevel` Nullable	int	Highest reminder level over all open invoices of the customer, otherwise 0.
`EventKey`	varchar(24) string	Unique event key with prefix `CAE_`, `REM_`, `CAM_` or `FPY_` followed by the source record key.
`EventUpdateDate` Nullable	dateTime	Date the event became effective (match creation date for `CAM_`, otherwise the event date); primary sort key.

Response example

<SqlRowSet1>
  <row>
    <CustomerNrExternal>100234</CustomerNrExternal>
    <InvoiceNr>RE2024-00451</InvoiceNr>
    <OrderNrExternal>WEB-77812</OrderNrExternal>
    <EventID>INV</EventID>
    <Amount>119.0000</Amount>
    <EventCurr>EUR</EventCurr>
    <EventDate>2024-03-04T10:15:00</EventDate>
    <Amount_ShopCurr>119.0000</Amount_ShopCurr>
    <ShopCurr>EUR</ShopCurr>
    <Balance_ShopCurr>119.0000</Balance_ShopCurr>
    <ReminderLevel>1</ReminderLevel>
    <CustomerReminderLevel>1</CustomerReminderLevel>
    <EventKey>CAE_558812</EventKey>
    <EventUpdateDate>2024-03-04T10:15:00</EventUpdateDate>
  </row>
  <row>
    <CustomerNrExternal>100234</CustomerNrExternal>
    <InvoiceNr>RE2024-00451</InvoiceNr>
    <OrderNrExternal>WEB-77812</OrderNrExternal>
    <EventID>REM</EventID>
    <Amount>-119.0000</Amount>
    <EventCurr>EUR</EventCurr>
    <EventDate>2024-03-20T08:00:00</EventDate>
    <Amount_ShopCurr>-119.0000</Amount_ShopCurr>
    <ShopCurr>EUR</ShopCurr>
    <Balance_ShopCurr>119.0000</Balance_ShopCurr>
    <ReminderLevel>1</ReminderLevel>
    <CustomerReminderLevel>1</CustomerReminderLevel>
    <EventKey>REM_3311</EventKey>
    <EventUpdateDate>2024-03-20T08:00:00</EventUpdateDate>
  </row>
</SqlRowSet1>

Status	Message	Cause
Error	Parameter @ShopId is mandatory parameter and needs to be provided.	`ShopId` was omitted, NULL or empty. Provide a valid shop ID.

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:pixiGetCAEventsAndReminders>
      <!-- <StartDate>2024-03-01T00:00:00</StartDate> -->
      <!-- <EndDate>2024-03-31T00:00:00</EndDate> -->
      <ShopId>SHP</ShopId>
      <!-- <CustomerNumberIsNumeric>0</CustomerNumberIsNumeric> -->
    </ns1:pixiGetCAEventsAndReminders>
  </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:pixiGetCAEventsAndReminders>
      <!-- <StartDate>2024-03-01T00:00:00</StartDate> -->
      <!-- <EndDate>2024-03-31T00:00:00</EndDate> -->
      <ShopId>SHP</ShopId>
      <!-- <CustomerNumberIsNumeric>0</CustomerNumberIsNumeric> -->
    </ns1:pixiGetCAEventsAndReminders>
  </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 = [
    // 'StartDate' => '2024-03-01T00:00:00',
    // 'EndDate' => '2024-03-31T00:00:00',
    'ShopId' => 'SHP',
    // 'CustomerNumberIsNumeric' => '0',
];

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

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

Notes

Tables used

Result set 1 · If ShopId is NULL or empty

Result set 2 · If ShopId is provided

Response example

Result set 1 · If `ShopId` is NULL or empty

Result set 2 · If `ShopId` is provided