pixiReportGetUnpaidInvoices · Pixi API documentation

Builds an open-items list of invoices as of a reference date.

Date is moved to the end of that day (23:59:59.997) by fPixi_GetDateEndOfDay. If it is omitted, the current day is used. ShopID is trimmed, and an empty ShopID or PaymentCode counts as "no filter".
The call selects invoices that meet all of these conditions:
- The invoice date is before the reference date.
- The invoice is not a return or credit note, i.e. it has no reference to another invoice.
- The shop matches (InvShopID = ShopID), if ShopID is given.
- The payment code matches (Payment = PaymentCode), if PaymentCode is given.
Each invoice is joined to its customer account entries. Entries that were already fully matched (Amount = Matched) and last updated before the reference date are ignored.
The open amount is the customer account amount plus the sum of all matches (AbsoluteMatchAmount) created before the reference date. A match counts whether the entry is referenced as first or second match partner. Only invoices whose open amount is still below 0 are returned.
For each open invoice the call returns:
- invoice number, date and total
- open amount
- external customer number and DATEV account number
- last and first name from the invoice address
- shop
- country of the shipping address
- currency code

The result has no ORDER BY, so the row order is not defined.

Notes

All tables are read with NOLOCK. The figures can therefore include uncommitted data.
OffenerRechnungsbetrag uses the sign convention of the customer account: open receivables are negative values.
The final query uses inner joins to invoice address, shipping address, customer and currency. Invoices missing one of these references are not returned.
If an invoice has several customer account entries, MAX(Amount) of these entries is combined with the sum of all their matches. The open amount may then differ from the expected value.
Invoices dated exactly at the end-of-day timestamp (23:59:59.997) are excluded because the comparison is strict (<).
Column names of the result are German (Rechnungsnummer, Kundennummer, ...), except DatevUserAccount, ShopID, CountryCode and CurrencyCode.

Tables used

Invoices · Read CustomerAccount · Read CustomerAccountMatch · Read Addresses · Read Customers · Read Currency · Read

Parameter	Type	Default	Description
`Date`	datetime dateTime	NULL	Reference date. The end of this day is used as cutoff. Defaults to today. Example: `2024-03-31T00:00:00`
`ShopID`	varchar(3) string	NULL	Restricts the result to invoices of this shop. Empty or NULL means all shops. Example: `ABC`
`PaymentCode`	char(1) string	NULL	Restricts the result to invoices with this payment code (`Invoices.Payment`). Empty or NULL means all payment codes. Example: `R`

Result set 1 · Always (may be empty).

One row per invoice that is still open at the reference date.

Column	Type	Description
`Rechnungsnummer` Nullable	string	Invoice number (`Invoices.InvoiceNr`).
`Rechnungsdatum` Nullable	dateTime	Invoice date.
`Rechnungsbetrag` Nullable	decimal	Invoice total (`Invoices.total`).
`OffenerRechnungsbetrag`	decimal	Open amount at the reference date, negative for open receivables.
`Kundennummer` Nullable	string	External customer number from the invoice address.
`DatevUserAccount` Nullable	varchar(10) string	DATEV account number of the customer.
`Nachname` Nullable	string	Last name from the invoice address.
`Vorname` Nullable	string	First name from the invoice address.
`ShopID` Nullable	string	Shop of the invoice.
`CountryCode` Nullable	string	Country of the shipping address.
`CurrencyCode` Nullable	string	Currency code of the invoice (`Currency.CodeA`).

Response example

<SqlRowSet1>
  <row>
    <Rechnungsnummer>RE240312</Rechnungsnummer>
    <Rechnungsdatum>2024-03-15T10:24:00</Rechnungsdatum>
    <Rechnungsbetrag>119.90</Rechnungsbetrag>
    <OffenerRechnungsbetrag>-59.90</OffenerRechnungsbetrag>
    <Kundennummer>K100234</Kundennummer>
    <DatevUserAccount>10234</DatevUserAccount>
    <Nachname>Müller</Nachname>
    <Vorname>Anna</Vorname>
    <ShopID>ABC</ShopID>
    <CountryCode>DE</CountryCode>
    <CurrencyCode>EUR</CurrencyCode>
  </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:pixiReportGetUnpaidInvoices>
      <!-- <Date>2024-03-31T00:00:00</Date> -->
      <!-- <ShopID>ABC</ShopID> -->
      <!-- <PaymentCode>R</PaymentCode> -->
    </ns1:pixiReportGetUnpaidInvoices>
  </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:pixiReportGetUnpaidInvoices>
      <!-- <Date>2024-03-31T00:00:00</Date> -->
      <!-- <ShopID>ABC</ShopID> -->
      <!-- <PaymentCode>R</PaymentCode> -->
    </ns1:pixiReportGetUnpaidInvoices>
  </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 = [
    // 'Date' => '2024-03-31T00:00:00',
    // 'ShopID' => 'ABC',
    // 'PaymentCode' => 'R',
];

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

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