Payments Read

pixiCAGetEvents

Returns customer account events (bookings), filtered by event type, dates, amounts, invoice, payment method or customer, optionally including deleted events.

Reads entries from the customer account (CustomerAccount) together with the currency code from pixi.Currency.

All filter parameters are optional and combined with AND. A filter is applied only if its parameter is not NULL:

EventID: exact match on the event type.
CreateDateFrom/CreateDateTo, UpdateDateFrom/UpdateDateTo, EventDateFrom/EventDateTo: inclusive ranges (>= / <=) on CreateDate, UpdateDate and EventDate.
MinAmount/MaxAmount: inclusive range on Amount.
MinMatchedAmount/MaxMatchedAmount: inclusive range on Matched.
InvoiceRef: exact match on the linked invoice.
DoNotRefund: exact match on the flag.
PaymentCode: joins Invoices via InvoiceRef and keeps only events whose invoice has this Payment code.
CustRef: applied only if the value is greater than 0.

If ShowDeleted = 1, deleted events are added from the history table CustomerAccountLog (rows with HistRemark = DELETE), using the same filters. These rows have IsDeleted = 1; active rows have IsDeleted = 0.

Without any filter, all customer account events are returned. The result has no defined sort order.

Notes

The query is built as dynamic SQL and executed with sp_executesql; all values are passed as parameters.
All tables are read with NOLOCK, so uncommitted data may appear.
Active and deleted rows are combined with UNION (not UNION ALL), so completely identical rows are returned only once.
Although Invoices is joined with a LEFT JOIN, the PaymentCode filter excludes all events without a linked invoice.
Events whose CurrencyId has no match in pixi.Currency are not returned (inner join).
Without filters, the call may return a very large number of rows; restricting by customer or date is recommended.

Tables used

CustomerAccount · Read CustomerAccountLog · Read Currency · Read Invoices · Read

Parameter	Type	Default	Description
`EventID`	varchar(3) string	NULL	Event type to filter on (exact match). `NULL` = all event types. Example: `PAY`
`CreateDateFrom`	datetime dateTime	NULL	Lower bound (inclusive) for the creation date. Example: `2024-01-01T00:00:00`
`CreateDateTo`	datetime dateTime	NULL	Upper bound (inclusive) for the creation date. Example: `2024-01-31T23:59:59`
`UpdateDateFrom`	datetime dateTime	NULL	Lower bound (inclusive) for the last update date. Example: `2024-01-01T00:00:00`
`UpdateDateTo`	datetime dateTime	NULL	Upper bound (inclusive) for the last update date. Example: `2024-01-31T23:59:59`
`EventDateFrom`	datetime dateTime	NULL	Lower bound (inclusive) for the event date. Example: `2024-01-01T00:00:00`
`EventDateTo`	datetime dateTime	NULL	Upper bound (inclusive) for the event date. Example: `2024-01-31T23:59:59`
`MinAmount`	money decimal	NULL	Minimum value (inclusive) of `Amount`. Example: `10.00`
`MaxAmount`	money decimal	NULL	Maximum value (inclusive) of `Amount`. Example: `500.00`
`MinMatchedAmount`	money decimal	NULL	Minimum value (inclusive) of the matched amount `Matched`. Example: `0.00`
`MaxMatchedAmount`	money decimal	NULL	Maximum value (inclusive) of the matched amount `Matched`. Example: `500.00`
`InvoiceRef`	int	NULL	Invoice key (`InvoiceKey`) the event is linked to (exact match). Example: `123456`
`DoNotRefund`	bit boolean	NULL	Filter on the `DoNotRefund` flag. `NULL` = no filter. Allowed values `0` – Only events without the do-not-refund flag `1` – Only events flagged as do not refund Example: `0`
`PaymentCode`	char(1) string	NULL	Payment method code of the linked invoice (`Invoices.Payment`). Only events with a linked invoice of this payment method are returned. Example: `V`
`CustRef`	int	NULL	Customer key. Applied only if greater than `0`. Example: `4711`
`ShowDeleted`	bit boolean	0	Whether deleted events from `CustomerAccountLog` are included. Allowed values `0` – Only active events `1` – Active and deleted events Example: `0`

Result set 1 · Always

One row per customer account event matching the filters.

Column	Type	Description
`CustomerAccountKey`	int	Key of the customer account entry.
`CustRef` Nullable	int	Customer key.
`EventID` Nullable	varchar(3) string	Event type.
`EventDate` Nullable	datetime dateTime	Date of the event.
`Reference` Nullable	string	Reference text of the event.
`Amount` Nullable	money decimal	Amount of the event.
`Matched` Nullable	money decimal	Matched (allocated) amount.
`InvoiceRef` Nullable	int	Linked invoice key.
`ManualBookingRef` Nullable	int	Reference to a manual booking.
`DoNotRefund` Nullable	bit boolean	Flag: amount must not be refunded.
`CreateEmp` Nullable	string	Employee who created the entry.
`CreateDate` Nullable	datetime dateTime	Creation date.
`UpdateEmp` Nullable	string	Employee who last changed the entry.
`UpdateDate` Nullable	datetime dateTime	Date of the last change.
`Comment` Nullable	string	Comment.
`IsDeleted`	bit boolean	`0` = active entry from `CustomerAccount`, `1` = deleted entry from `CustomerAccountLog`.
`OrderCurrency` Nullable	string	Currency code (`CodeA` from `pixi.Currency`).

Response example

<SqlRowSet1>
  <row>
    <CustomerAccountKey>1025</CustomerAccountKey>
    <CustRef>4711</CustRef>
    <EventID>PAY</EventID>
    <EventDate>2024-01-15T10:32:00</EventDate>
    <Reference>Bank transfer 2024-0115</Reference>
    <Amount>149.9000</Amount>
    <Matched>149.9000</Matched>
    <InvoiceRef>123456</InvoiceRef>
    <ManualBookingRef>0</ManualBookingRef>
    <DoNotRefund>false</DoNotRefund>
    <CreateEmp>AD</CreateEmp>
    <CreateDate>2024-01-15T10:32:00</CreateDate>
    <UpdateEmp>AD</UpdateEmp>
    <UpdateDate>2024-01-15T10:35:00</UpdateDate>
    <Comment/>
    <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:pixiCAGetEvents>
      <!-- <EventID>PAY</EventID> -->
      <!-- <CreateDateFrom>2024-01-01T00:00:00</CreateDateFrom> -->
      <!-- <CreateDateTo>2024-01-31T23:59:59</CreateDateTo> -->
      <!-- <UpdateDateFrom>2024-01-01T00:00:00</UpdateDateFrom> -->
      <!-- <UpdateDateTo>2024-01-31T23:59:59</UpdateDateTo> -->
      <!-- <EventDateFrom>2024-01-01T00:00:00</EventDateFrom> -->
      <!-- <EventDateTo>2024-01-31T23:59:59</EventDateTo> -->
      <!-- <MinAmount>10.00</MinAmount> -->
      <!-- <MaxAmount>500.00</MaxAmount> -->
      <!-- <MinMatchedAmount>0.00</MinMatchedAmount> -->
      <!-- <MaxMatchedAmount>500.00</MaxMatchedAmount> -->
      <!-- <InvoiceRef>123456</InvoiceRef> -->
      <!-- <DoNotRefund>0</DoNotRefund> -->
      <!-- <PaymentCode>V</PaymentCode> -->
      <!-- <CustRef>4711</CustRef> -->
      <!-- <ShowDeleted>0</ShowDeleted> -->
    </ns1:pixiCAGetEvents>
  </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:pixiCAGetEvents>
      <!-- <EventID>PAY</EventID> -->
      <!-- <CreateDateFrom>2024-01-01T00:00:00</CreateDateFrom> -->
      <!-- <CreateDateTo>2024-01-31T23:59:59</CreateDateTo> -->
      <!-- <UpdateDateFrom>2024-01-01T00:00:00</UpdateDateFrom> -->
      <!-- <UpdateDateTo>2024-01-31T23:59:59</UpdateDateTo> -->
      <!-- <EventDateFrom>2024-01-01T00:00:00</EventDateFrom> -->
      <!-- <EventDateTo>2024-01-31T23:59:59</EventDateTo> -->
      <!-- <MinAmount>10.00</MinAmount> -->
      <!-- <MaxAmount>500.00</MaxAmount> -->
      <!-- <MinMatchedAmount>0.00</MinMatchedAmount> -->
      <!-- <MaxMatchedAmount>500.00</MaxMatchedAmount> -->
      <!-- <InvoiceRef>123456</InvoiceRef> -->
      <!-- <DoNotRefund>0</DoNotRefund> -->
      <!-- <PaymentCode>V</PaymentCode> -->
      <!-- <CustRef>4711</CustRef> -->
      <!-- <ShowDeleted>0</ShowDeleted> -->
    </ns1:pixiCAGetEvents>
  </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 = [
    // 'EventID' => 'PAY',
    // 'CreateDateFrom' => '2024-01-01T00:00:00',
    // 'CreateDateTo' => '2024-01-31T23:59:59',
    // 'UpdateDateFrom' => '2024-01-01T00:00:00',
    // 'UpdateDateTo' => '2024-01-31T23:59:59',
    // 'EventDateFrom' => '2024-01-01T00:00:00',
    // 'EventDateTo' => '2024-01-31T23:59:59',
    // 'MinAmount' => '10.00',
    // 'MaxAmount' => '500.00',
    // 'MinMatchedAmount' => '0.00',
    // 'MaxMatchedAmount' => '500.00',
    // 'InvoiceRef' => '123456',
    // 'DoNotRefund' => '0',
    // 'PaymentCode' => 'V',
    // 'CustRef' => '4711',
    // 'ShowDeleted' => '0',
];

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

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

SQL object:: dbo.pipiCAGetEvents