Customers Read

pixiGetCustomerEvents

Returns the customer account events (debits, credits, payments, direct debits, manual bookings) filtered by customer, event type and date range.

Reads entries from the customer account ledger (CustomerAccount) and enriches them with display and status information.

Filters (all optional, combined with AND; a parameter left out or NULL does not filter):

CustKey: only entries whose CustRef equals this customer key.
EventID: only entries of this event type.
DateFrom / DateTo: only entries with EventDate in the range (both bounds inclusive).

If no parameter is given, all customer account entries are returned.

Enrichment per entry:

EventName: description of the event type from CustomerAccountEvents, translated via the translation table (language ID 2, falls back to the untranslated text). For entries linked to a direct debit (DTAUSref set), the translated direct debit state is appended in square brackets, e.g. Lastschrift [Exportiert].
Number: a human-readable reference depending on the linked object, checked in this order: invoice number (InvoiceRef), bank line text BankText01 (BanklineRef), account number of the direct debit (DTAUSref), reference of the manual booking (ManualBookingRef), otherwise an empty string.
OpenTOMatch: Amount minus the already matched amount (NULL counts as 0).
AllowToDelete: 1 if the entry may be deleted, otherwise 0. An entry is deletable only if it is not matched with other entries and either the event type and the direct debit state allow deletion, or it belongs to an invoice that is not paid, not paid by credit card (CCPPaymentDone not Y), has no reminder lines and whose lines are all in status EIN or all in status RET (or has no lines).
CCPTranLogKey: a credit card transaction log entry of the same customer whose invoice number equals the invoice number or the referenced invoice number of the linked invoice.
OrderCurrency: currency code (CodeA) of the entry.
IsDeleted: always 0.

The result has no defined sort order.

Notes

All reads use NOLOCK, so uncommitted data may appear.
The setting CaEnabled changes the "paid" check for AllowToDelete: if it is 0 or missing, Invoices.Payed is used; otherwise the latest creation date in CustomerAccountMatch for the entry is used.
The deletion rule is an inline copy of fPixi_CA_canIdeleteEvent, but without that function's additional check on the invoice scan-out date (ScanOutDate). Results may therefore differ from the function.
Entries whose CurrencyId has no row in pixi.Currency are not returned (inner join).
If the event type has no row in CustomerAccountEvents, EventName and AllowToDelete are NULL.
DateTo is compared with <=; a pure date such as 2024-03-31 means midnight, so entries later that day are excluded.
If several returned entries reference the same direct debit (DTAUSref), the creation of a unique index on an internal temporary table can fail with a SQL error; filtering by customer usually avoids this.
The view PV_CustomerAccountMatchGroup is based on PV_CustomerAccountBond, whose definition is not attached.

Tables used

CustomerAccount · Read CustomerAccountEvents · Read CustomerAccountMatch · Read CustomerAccountManualBookings · Read DTAUS · Read DTAUSstates · Read Invoices · Read InvLines · Read OrderLines · Read ReminderLines · Read CCPTranLog · Read BankLines · Read Currency · Read Settings · Read Translation · Read

Parameter	Type	Default	Description
`CustKey`	int	NULL	Customer key (`CustRef`). Only entries of this customer are returned. NULL = all customers. Example: `10452`
`EventID`	varchar(3) string	NULL	Event type ID from `CustomerAccountEvents`. NULL = all event types. Example: `INV`
`DateFrom`	datetime dateTime	NULL	Lower bound for `EventDate` (inclusive). NULL = no lower bound. Example: `2024-01-01T00:00:00`
`DateTo`	datetime dateTime	NULL	Upper bound for `EventDate` (inclusive). NULL = no upper bound. Example: `2024-03-31T23:59:59`

Result set 1 · Always (may be empty)

One row per matching customer account entry.

Column	Type	Description
`CustomerAccountKey`	int	Key of the customer account entry.
`CustRef` Nullable	int	Customer key.
`EventDate` Nullable	dateTime	Date of the event.
`EventID` Nullable	string	Event type ID.
`EventName` Nullable	varchar(200) string	Translated event type name; for direct debit entries followed by the translated direct debit state in square brackets.
`Number` Nullable	string	Invoice number, bank line text, direct debit account number or manual booking reference, depending on the linked object; empty string if none.
`Reference` Nullable	string	Reference text of the entry.
`Amount` Nullable	decimal	Amount of the entry.
`OpenTOMatch` Nullable	decimal	Amount still open for matching (`Amount` minus `Matched`).
`DoNotRefund` Nullable	boolean	Flag that the amount must not be refunded.
`InvoiceRef` Nullable	int	Key of the linked invoice.
`BanklineRef` Nullable	int	Key of the linked bank line.
`DTAUSref` Nullable	int	Key of the linked direct debit (`DTAUS`).
`ManualBookingRef` Nullable	int	Key of the linked manual booking.
`AllowToDelete` Nullable	int	1 = entry may be deleted, 0 = not; NULL if the event type is unknown.
`IsDeleted`	bit boolean	Always 0.
`CCPTranLogKey` Nullable	int	Key of a matching credit card transaction log entry, if any.
`OrderCurrency` Nullable	string	Currency code (`CodeA` from `pixi.Currency`).

Response example

<SqlRowSet1>
  <row>
    <CustomerAccountKey>88231</CustomerAccountKey>
    <CustRef>10452</CustRef>
    <EventDate>2024-02-14T10:22:00</EventDate>
    <EventID>INV</EventID>
    <EventName>Rechnung</EventName>
    <Number>RE2024-004711</Number>
    <Reference>Auftrag 500123</Reference>
    <Amount>-59.90</Amount>
    <OpenTOMatch>-59.90</OpenTOMatch>
    <DoNotRefund>false</DoNotRefund>
    <InvoiceRef>45871</InvoiceRef>
    <AllowToDelete>0</AllowToDelete>
    <IsDeleted>false</IsDeleted>
    <OrderCurrency>EUR</OrderCurrency>
  </row>
  <row>
    <CustomerAccountKey>88240</CustomerAccountKey>
    <CustRef>10452</CustRef>
    <EventDate>2024-02-16T08:05:00</EventDate>
    <EventID>BNK</EventID>
    <EventName>Zahlungseingang</EventName>
    <Number>RE2024-004711 Mustermann</Number>
    <Reference/>
    <Amount>59.90</Amount>
    <OpenTOMatch>0.00</OpenTOMatch>
    <DoNotRefund>false</DoNotRefund>
    <BanklineRef>3321</BanklineRef>
    <AllowToDelete>0</AllowToDelete>
    <IsDeleted>false</IsDeleted>
    <OrderCurrency>EUR</OrderCurrency>
  </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:pixiGetCustomerEvents>
      <!-- <CustKey>10452</CustKey> -->
      <!-- <EventID>INV</EventID> -->
      <!-- <DateFrom>2024-01-01T00:00:00</DateFrom> -->
      <!-- <DateTo>2024-03-31T23:59:59</DateTo> -->
    </ns1:pixiGetCustomerEvents>
  </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:pixiGetCustomerEvents>
      <!-- <CustKey>10452</CustKey> -->
      <!-- <EventID>INV</EventID> -->
      <!-- <DateFrom>2024-01-01T00:00:00</DateFrom> -->
      <!-- <DateTo>2024-03-31T23:59:59</DateTo> -->
    </ns1:pixiGetCustomerEvents>
  </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',
    // 'EventID' => 'INV',
    // 'DateFrom' => '2024-01-01T00:00:00',
    // 'DateTo' => '2024-03-31T23:59:59',
];

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

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

SQL object:: dbo.pipiGetCustomerEvents