pixiGetPaidInvoices · Pixi API documentation

Finds invoices that have been settled in the customer account (CustomerAccount) and returns one row per invoice entry.

Steps:

Selects all customer account entries with EventDate between @From and @To (inclusive, BETWEEN) and Amount > 0. These are typically the payment entries.
Uses CustomerAccountMatch to find all customer account entries that are matched against those entries, in both directions (CARef1 ↔ CARef2).
Keeps only matched entries that reference an invoice (InvoiceRef IS NOT NULL).
Keeps only entries that are fully settled: the sum of AbsoluteMatchAmount over all matches of the entry plus the entry's own Amount must be exactly 0.

For each remaining entry the call returns:

the invoice key and invoice number from Invoices,
DatePaid: the latest EventDate of all customer account entries matched against the invoice entry (not limited to the date range),
ShopID: the shop of the customer (Customers.CustomerOf),
OrderNRExternal: the highest external order number of the order lines linked to the invoice through Invlines,
PaymentType: the payment method of the invoice.

No sort order is defined.

Notes

All tables are read WITH (NOLOCK); results can contain uncommitted data.
BETWEEN includes both limits. If @To is passed as a date without time (midnight), payments later on that day are not included.
The full-settlement check is an exact comparison with 0; rounding differences in the matches exclude an invoice.
If an invoice has several customer account entries that each qualify, the invoice can appear more than once.
DatePaid is the latest matched entry date overall and can lie outside @From/@To.
In the settlement check the columns Amount and CustomerAccountKey are not qualified; the code relies on them resolving to the outer CustomerAccount entry.
The header comment of the procedure ("Checks pixi* user credentials") does not match its function and is apparently a copy leftover.

Tables used

CustomerAccount · Read CustomerAccountMatch · Read Invoices · Read Customers · Read Orderlines · Read Invlines · Read

Parameter	Type	Default	Description
`From` Required	datetime dateTime	–	Start of the period (inclusive) for the posting date (`EventDate`) of the payment entries. Example: `2024-05-01T00:00:00`
`To` Required	datetime dateTime	–	End of the period (inclusive) for the posting date (`EventDate`) of the payment entries. Example: `2024-05-31T23:59:59`

Result set 1 · Always (may be empty).

One row per fully settled invoice entry in the customer account whose matched payment lies in the period.

Column	Type	Description
`InvoiceKey`	int	Key of the invoice (`CustomerAccount.InvoiceRef`).
`InvoiceNr` Nullable	string	Invoice number from `Invoices.InvoiceNr`.
`DatePaid` Nullable	datetime dateTime	Latest `EventDate` of all customer account entries matched against the invoice entry.
`ShopID` Nullable	string	Shop of the customer (`Customers.CustomerOf`).
`OrderNRExternal` Nullable	string	Highest external order number (`Orderlines.OrderNRExternal`) of the order lines invoiced on this invoice.
`PaymentType` Nullable	string	Payment method of the invoice (`Invoices.Payment`).

Response example

<SqlRowSet1>
  <row>
    <InvoiceKey>48213</InvoiceKey>
    <InvoiceNr>2024100512</InvoiceNr>
    <DatePaid>2024-05-14T10:32:00</DatePaid>
    <ShopID>ABC</ShopID>
    <OrderNRExternal>100045678</OrderNRExternal>
    <PaymentType>VORKASSE</PaymentType>
  </row>
  <row>
    <InvoiceKey>48250</InvoiceKey>
    <InvoiceNr>2024100549</InvoiceNr>
    <DatePaid>2024-05-20T08:15:00</DatePaid>
    <ShopID>ABC</ShopID>
    <OrderNRExternal>100045702</OrderNRExternal>
    <PaymentType>RECHNUNG</PaymentType>
  </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:pixiGetPaidInvoices>
      <From>2024-05-01T00:00:00</From>
      <To>2024-05-31T23:59:59</To>
    </ns1:pixiGetPaidInvoices>
  </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:pixiGetPaidInvoices>
      <From>2024-05-01T00:00:00</From>
      <To>2024-05-31T23:59:59</To>
    </ns1:pixiGetPaidInvoices>
  </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 = [
    'From' => '2024-05-01T00:00:00',
    'To' => '2024-05-31T23:59:59',
];

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

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