Payments Read

pixiGetPaymentDetailsForInvoice

Returns the customer account entry of an invoice together with all matched account entries and their bank line and bank import details.

Looks up the payment situation of a single invoice via the customer account (CustomerAccount).

The invoice is identified by InvoiceNr in Invoices. The customer account entry whose InvoiceRef points to this invoice is determined (the invoice entry).
All customer account entries that are matched with this invoice entry in CustomerAccountMatch are collected. The match is evaluated in both directions (CARef1 and CARef2), so payments, credit notes or other entries linked to the invoice are found regardless of how the match was stored.
For the invoice entry and all matched entries, one row each is returned with the account entry data. If an entry was created from a bank line (BanklineRef), the bank line data (BankLines) and the data of the bank import file (BankHeader) are added; otherwise these columns are empty.

Sort order: the invoice entry itself comes first, followed by the matched entries in ascending order of CustomerAccountKey.

If no invoice with the given number exists or it has no customer account entry, an empty result set is returned.

Notes

All tables are read with NOLOCK, so uncommitted data may be returned.
Only direct matches of the invoice entry are returned; entries that are matched only indirectly (via another matched entry) are not included.
If several invoices share the same InvoiceNr, the subquery returns more than one value and SQL Server raises error 512 (returned as SqlMessage).
If several customer account entries reference the invoice, only one of them (not deterministically chosen) is used as the invoice entry.
Column types are not visible in the code; types in this documentation are derived from column names and usage.

Tables used

CustomerAccount · Read Invoices · Read CustomerAccountMatch · Read BankLines · Read BankHeader · Read

Parameter	Type	Default	Description
`InvoiceNr` Required	varchar(20) string	–	Number of the invoice (`Invoices.InvoiceNr`) whose payment details are requested. Example: `RE2024-001234`

Result set 1 · Always; empty if the invoice or its customer account entry does not exist.

Invoice entry and all matched customer account entries with bank line and bank import data; invoice entry first, then by CustomerAccountKey ascending.

Column	Type	Description
`CustRef` Nullable	int	Reference to the customer of the account entry.
`CustomerAccountKey`	int	Key of the customer account entry.
`EventID` Nullable	string	Type of event of the account entry (e.g. invoice, payment).
`EventDate` Nullable	dateTime	Date of the account entry.
`Reference` Nullable	string	Reference text of the account entry.
`Amount` Nullable	decimal	Amount of the account entry.
`Matched` Nullable	boolean	Indicates whether the entry has been matched.
`InvoiceRef` Nullable	int	Reference to the invoice (`Invoices.InvoiceKey`); set for the invoice entry.
`BanklineRef` Nullable	int	Reference to the bank line the entry originates from.
`DoNotRefund` Nullable	boolean	Flag that the amount must not be refunded.
`BankDate` Nullable	dateTime	Value date of the bank line; empty without bank line.
`BookDate` Nullable	dateTime	Booking date of the bank line.
`BankAmount` Nullable	decimal	Amount of the bank line.
`BankText01` Nullable	string	Bank text line 1 (purpose of payment).
`BankText02` Nullable	string	Bank text line 2.
`BankText03` Nullable	string	Bank text line 3.
`CurrencyCode` Nullable	string	Currency of the bank line.
`BankCode` Nullable	string	Bank code of the payer's account.
`AccountNr` Nullable	string	Account number of the payer.
`CustomerName` Nullable	string	Name of the payer from the bank line.
`ParsedBIC` Nullable	string	BIC parsed from the bank line.
`ParsedIBAN` Nullable	string	IBAN parsed from the bank line.
`SourceText` Nullable	string	Original text of the bank line from the import.
`SourceType` Nullable	string	Type/format of the bank import source.
`BankFileName` Nullable	string	File name of the imported bank file.
`BankImportDate` Nullable	dateTime	Date of the bank import.

Response example

<SqlRowSet1>
  <row>
    <CustRef>10452</CustRef>
    <CustomerAccountKey>88120</CustomerAccountKey>
    <EventID>INV</EventID>
    <EventDate>2024-03-04T10:15:00</EventDate>
    <Reference>RE2024-001234</Reference>
    <Amount>-119.00</Amount>
    <Matched>1</Matched>
    <InvoiceRef>50311</InvoiceRef>
    <DoNotRefund>0</DoNotRefund>
  </row>
  <row>
    <CustRef>10452</CustRef>
    <CustomerAccountKey>88257</CustomerAccountKey>
    <EventID>PAY</EventID>
    <EventDate>2024-03-07T00:00:00</EventDate>
    <Reference>Zahlung RE2024-001234</Reference>
    <Amount>119.00</Amount>
    <Matched>1</Matched>
    <BanklineRef>7731</BanklineRef>
    <DoNotRefund>0</DoNotRefund>
    <BankDate>2024-03-07T00:00:00</BankDate>
    <BookDate>2024-03-07T00:00:00</BookDate>
    <BankAmount>119.00</BankAmount>
    <BankText01>RE2024-001234</BankText01>
    <CurrencyCode>EUR</CurrencyCode>
    <CustomerName>Max Mustermann</CustomerName>
    <ParsedBIC>COBADEFFXXX</ParsedBIC>
    <ParsedIBAN>DE89370400440532013000</ParsedIBAN>
    <SourceType>CAMT</SourceType>
    <BankFileName>camt053_20240307.xml</BankFileName>
    <BankImportDate>2024-03-08T06:00:00</BankImportDate>
  </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:pixiGetPaymentDetailsForInvoice>
      <InvoiceNr>RE2024-001234</InvoiceNr>
    </ns1:pixiGetPaymentDetailsForInvoice>
  </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:pixiGetPaymentDetailsForInvoice>
      <InvoiceNr>RE2024-001234</InvoiceNr>
    </ns1:pixiGetPaymentDetailsForInvoice>
  </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 = [
    'InvoiceNr' => 'RE2024-001234',
];

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

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

SQL object:: dbo.pipiGetPaymentDetailsForInvoice