Payments Read

pixiReadCustomerAccount

Returns entries of the customer account (CustomerAccount) by key, by key range with paging, or filtered by creation and/or update date.

Reads rows from the CustomerAccount table, the customer account ledger. Entries reference invoices, bank lines, DTAUS records and manual bookings. The parameters select exactly one of the following modes, checked in this order:

Create or update date: CreateAndUpdateDateFrom and/or CreateAndUpdateDateTo is set. Returns entries with CustomerAccountKey > CustomerAccountKey.
- From-condition: CreateDate >= from or UpdateDate >= from.
- To-condition: CreateDate < to or UpdateDate < to.
Create date: CreateDateFrom and/or CreateDateTo is set. Returns entries with CustomerAccountKey > CustomerAccountKey and CreateDate in the half-open range [from, to).
Update date: UpdateDateFrom and/or UpdateDateTo is set. Same as mode 2, but uses UpdateDate.
Key range: RowCount is passed and no date is set. Returns all entries with CustomerAccountKey > CustomerAccountKey.
Single entry: otherwise. Returns the entry with exactly CustomerAccountKey.

Modes 1 to 4 are sorted by CustomerAccountKey ascending. If RowCount is non-zero, the number of returned rows is limited to that value (SET ROWCOUNT).

For keyset paging, pass the last CustomerAccountKey you received as CustomerAccountKey in the next call.

ExchRateId is only filled when the setting MultiCurrencyEnabled is 1; otherwise it is always NULL.

Notes

Reads with NOLOCK, so uncommitted data can be returned.
If CustomerAccountKey is omitted, it is treated as 0. Called without any parameter, the call therefore runs the single-entry mode for key 0 and normally returns no row. The final "no data" branch in the code can never be reached.
RowCount = 0 still counts as "passed" for mode 4. The call then returns all entries above the key without a row limit.
In the combined date mode the OR logic is loose. An entry matches if any one of its two dates fulfils the from-condition and any one fulfils the to-condition. This is not necessarily the same date.
The date parameter groups do not combine. Only the first group that is set (in the order of the modes) is used; the other groups are ignored.
The column types of CustomerAccount are not part of the supplied code; the column types shown here are partly assumed.

Tables used

CustomerAccount · Read Settings · Read

Parameter	Type	Default	Description
`CustomerAccountKey`	int	NULL	In single-entry mode, the key of the entry to return. In all other modes, entries with a key greater than this value are returned (paging start). NULL is treated as 0. Example: `0`
`CreateDateFrom`	datetime dateTime	NULL	Lower bound (inclusive) for `CreateDate`. Example: `2024-01-01T00:00:00`
`CreateDateTo`	datetime dateTime	NULL	Upper bound (exclusive) for `CreateDate`. Example: `2024-02-01T00:00:00`
`UpdateDateFrom`	datetime dateTime	NULL	Lower bound (inclusive) for `UpdateDate`. Only used if no create date and no combined date parameter is set. Example: `2024-01-01T00:00:00`
`UpdateDateTo`	datetime dateTime	NULL	Upper bound (exclusive) for `UpdateDate`. Example: `2024-02-01T00:00:00`
`CreateAndUpdateDateFrom`	datetime dateTime	NULL	Lower bound (inclusive); matches if `CreateDate` or `UpdateDate` is on or after this value. Takes precedence over all other date parameters. Example: `2024-01-15T00:00:00`
`CreateAndUpdateDateTo`	datetime dateTime	NULL	Upper bound (exclusive); matches if `CreateDate` or `UpdateDate` is before this value. Example: `2024-01-16T00:00:00`
`RowCount`	int	NULL	Maximum number of rows to return (0 or NULL = unlimited). If passed (even 0) without date parameters, the call returns the key range above `CustomerAccountKey` instead of a single entry. Example: `500`

Result set 1 · Always (may be empty)

Customer account entries matching the selected mode.

Column	Type	Description
`CustomerAccountKey`	int	Primary key of the customer account entry.
`CustRef` Nullable	int	Reference to the customer.
`EventID` Nullable	string	Type of the booking event.
`EventDate` Nullable	dateTime	Date of the booking event.
`Reference` Nullable	string	Reference text of the booking.
`Amount` Nullable	decimal	Booking amount.
`Matched` Nullable	string	Matching status of the entry.
`InvoiceRef` Nullable	int	Reference to the invoice (Rechnung).
`BanklineRef` Nullable	int	Reference to the bank statement line.
`DTAUSref` Nullable	int	Reference to the DTAUS (direct debit) record.
`ManualBookingRef` Nullable	int	Reference to a manual booking.
`DoNotRefund` Nullable	string	Flag that the amount must not be refunded.
`CreateEmp` Nullable	string	Employee who created the entry.
`CreateDate` Nullable	datetime dateTime	Creation timestamp.
`UpdateEmp` Nullable	string	Employee who last changed the entry.
`UpdateDate` Nullable	datetime dateTime	Timestamp of the last change.
`Comment` Nullable	string	Comment.
`CATKey` Nullable	int	Key of the customer account type/category.
`ExchRateId` Nullable	int	Exchange rate ID; only filled if setting `MultiCurrencyEnabled` = 1, otherwise NULL.
`CurrencyId` Nullable	string	Currency of the entry.

Response example

<SqlRowSet1>
  <row>
    <CustomerAccountKey>1201</CustomerAccountKey>
    <CustRef>4711</CustRef>
    <EventID>INV</EventID>
    <EventDate>2024-01-15T10:22:00</EventDate>
    <Reference>RE-2024-000123</Reference>
    <Amount>-59.90</Amount>
    <Matched>0</Matched>
    <InvoiceRef>88123</InvoiceRef>
    <DoNotRefund>0</DoNotRefund>
    <CreateEmp>API</CreateEmp>
    <CreateDate>2024-01-15T10:22:00</CreateDate>
    <UpdateEmp>API</UpdateEmp>
    <UpdateDate>2024-01-15T10:22:00</UpdateDate>
    <CATKey>1</CATKey>
    <CurrencyId>EUR</CurrencyId>
  </row>
  <row>
    <CustomerAccountKey>1202</CustomerAccountKey>
    <CustRef>4711</CustRef>
    <EventID>PAY</EventID>
    <EventDate>2024-01-16T08:05:00</EventDate>
    <Reference>Bank transfer</Reference>
    <Amount>59.90</Amount>
    <Matched>1</Matched>
    <BanklineRef>30551</BanklineRef>
    <DoNotRefund>0</DoNotRefund>
    <CreateEmp>SYS</CreateEmp>
    <CreateDate>2024-01-16T08:05:00</CreateDate>
    <UpdateEmp>SYS</UpdateEmp>
    <UpdateDate>2024-01-16T08:05:00</UpdateDate>
    <CATKey>2</CATKey>
    <CurrencyId>EUR</CurrencyId>
  </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:pixiReadCustomerAccount>
      <!-- <CustomerAccountKey>0</CustomerAccountKey> -->
      <!-- <CreateDateFrom>2024-01-01T00:00:00</CreateDateFrom> -->
      <!-- <CreateDateTo>2024-02-01T00:00:00</CreateDateTo> -->
      <!-- <UpdateDateFrom>2024-01-01T00:00:00</UpdateDateFrom> -->
      <!-- <UpdateDateTo>2024-02-01T00:00:00</UpdateDateTo> -->
      <!-- <CreateAndUpdateDateFrom>2024-01-15T00:00:00</CreateAndUpdateDateFrom> -->
      <!-- <CreateAndUpdateDateTo>2024-01-16T00:00:00</CreateAndUpdateDateTo> -->
      <!-- <RowCount>500</RowCount> -->
    </ns1:pixiReadCustomerAccount>
  </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:pixiReadCustomerAccount>
      <!-- <CustomerAccountKey>0</CustomerAccountKey> -->
      <!-- <CreateDateFrom>2024-01-01T00:00:00</CreateDateFrom> -->
      <!-- <CreateDateTo>2024-02-01T00:00:00</CreateDateTo> -->
      <!-- <UpdateDateFrom>2024-01-01T00:00:00</UpdateDateFrom> -->
      <!-- <UpdateDateTo>2024-02-01T00:00:00</UpdateDateTo> -->
      <!-- <CreateAndUpdateDateFrom>2024-01-15T00:00:00</CreateAndUpdateDateFrom> -->
      <!-- <CreateAndUpdateDateTo>2024-01-16T00:00:00</CreateAndUpdateDateTo> -->
      <!-- <RowCount>500</RowCount> -->
    </ns1:pixiReadCustomerAccount>
  </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 = [
    // 'CustomerAccountKey' => '0',
    // 'CreateDateFrom' => '2024-01-01T00:00:00',
    // 'CreateDateTo' => '2024-02-01T00:00:00',
    // 'UpdateDateFrom' => '2024-01-01T00:00:00',
    // 'UpdateDateTo' => '2024-02-01T00:00:00',
    // 'CreateAndUpdateDateFrom' => '2024-01-15T00:00:00',
    // 'CreateAndUpdateDateTo' => '2024-01-16T00:00:00',
    // 'RowCount' => '500',
];

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

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

SQL object:: dbo.pipiReadCustomerAccount