Invoices Read

pixiGetInvoicesUnpaid

Returns all unpaid invoices, or with `PaidInvoices` = 1 all paid invoices, with payment, bank, address and currency details, sorted by invoice date.

Reads invoices from Invoices together with the customer, the payment method, the invoice (billing) address, its country and the invoice currency.

The parameter PaidInvoices selects one of two modes:

0 (default): unpaid invoices. An invoice is returned if the paid sum (PayedSum, NULL counts as 0) is not exactly equal to the invoice total rounded to 2 decimals, and there is no entry in CustomerAccount for this customer and invoice that has DoNotRefund = 1 and no bank line, DTAUS or manual booking reference.
1: paid invoices. An invoice is returned if the paid sum differs from the rounded total by less than 0.01, or such a DoNotRefund entry in CustomerAccount exists.

Only invoices for which a currency, a customer, a payment method (Payments.Code = invoice payment code) and an invoice address exist are returned (inner joins). The country is optional.

The result is sorted by invoice date ascending. There is no paging; all matching invoices are returned.

Column logic:

Paid is 1 only if the paid sum equals the rounded total exactly; otherwise 0.
PaymentDate is the invoice pay day if Paid = 1, otherwise NULL.
PaymentText is the text of the payment method, falling back to the payment code.
Currency and OrderCurrency both contain the currency code of the invoice; total, TotalToPay and TotalToPay_OrderCurr all contain the invoice total.

Notes

All tables are read WITH (NOLOCK); results can contain uncommitted data.
In mode 1 the filter uses a tolerance of 0.01, but Paid and PaymentDate use an exact comparison. Invoices with a tiny difference or invoices included only because of a DoNotRefund entry are therefore returned with Paid = 0 and PaymentDate = NULL.
Passing NULL for PaidInvoices returns no rows.
Invoices whose payment code has no entry in Payments, or whose customer, address or currency is missing, are silently excluded.
The result contains sensitive payment data (credit card number, name and expiry, bank account, BLZ).
Without paging the result can be large on databases with many invoices.
A code comment states that the currency handling (Currency = invoice currency) is provisional until multi-currency is fully supported.

Tables used

Invoices · Read Currency · Read Customers · Read Payments · Read Addresses · Read Countries · Read CustomerAccount · Read

Parameter Type Default Description

Parameter	Type	Default	Description
`PaidInvoices`	bit boolean	0	Selects the mode: 0 returns unpaid invoices, 1 returns paid invoices. Allowed values `0` – Unpaid invoices: paid sum differs from the rounded total and no open DoNotRefund entry in the customer account. `1` – Paid invoices: paid sum within 0.01 of the rounded total, or an open DoNotRefund entry exists in the customer account. Example: `0`

PaidInvoices

bit
boolean

Selects the mode: 0 returns unpaid invoices, 1 returns paid invoices.

Allowed values

0 – Unpaid invoices: paid sum differs from the rounded total and no open DoNotRefund entry in the customer account.
1 – Paid invoices: paid sum within 0.01 of the rounded total, or an open DoNotRefund entry exists in the customer account.

Example: 0

Result set 1 · Always (may be empty)

One row per matching invoice, sorted by invoice date ascending.

Column	Type	Description
`InvoiceNr` Nullable	string	Invoice number.
`InvoiceDate` Nullable	dateTime	Invoice date (`InvDate`).
`ShopID` Nullable	string	Shop the invoice belongs to (`CustomerOf`).
`PaymentAddressId`	int	Key of the invoice address (`AddrRef`).
`ShipmentAddressId` Nullable	int	Key of the shipping address (`ShipAdrRef`).
`CustKey`	int	Customer key (`CustRef` of the invoice).
`total` Nullable	decimal	Invoice total.
`PaymentCode`	string	Payment method code of the invoice.
`PaymentText` Nullable	string	Text of the payment method, or the payment code if no text is set.
`PaymentDate` Nullable	dateTime	Pay day of the invoice if it is fully paid (exact match), otherwise NULL.
`Paid`	bit boolean	1 if the paid sum equals the total rounded to 2 decimals, otherwise 0.
`TotalToPay` Nullable	decimal	Invoice total (same as `total`).
`PaidSum` Nullable	decimal	Amount paid so far (`PayedSum`).
`CcCardType` Nullable	string	Credit card type.
`CcCardNr` Nullable	string	Credit card number.
`CcCardName` Nullable	string	Credit card holder name.
`CcCardExp` Nullable	string	Credit card expiry.
`Account` Nullable	string	Bank account number.
`Blz` Nullable	string	Bank code (BLZ).
`BankName` Nullable	string	Name of the bank.
`AccountName` Nullable	string	Account holder name.
`TrackingID` Nullable	string	Shipment tracking ID.
`ShipDate` Nullable	dateTime	Scan-out (shipping) date.
`UpdateDate` Nullable	dateTime	Last update of the invoice.
`ReminderLevel` Nullable	int	Current dunning level.
`Note` Nullable	string	Invoice note.
`ShipVendor` Nullable	string	Shipping carrier.
`Currency` Nullable	string	Currency code of the invoice (`Currency.CodeA`).
`OrderCurrency` Nullable	string	Currency code of the invoice (same as `Currency`).
`TotalToPay_OrderCurr` Nullable	decimal	Invoice total (same as `total`).
`ReturnReason` Nullable	string	Return reason of the invoice.
`InvoiceKey`	int	Internal key of the invoice.
`RefToInvoiceNr` Nullable	string	Number of the referenced invoice (e.g. for credit notes).
`RefToInvoiceKey` Nullable	int	Key of the referenced invoice.
`CustomerNrExternal` Nullable	string	External customer number.
`InvZIP` Nullable	string	ZIP code of the invoice address.
`InvCity` Nullable	string	City of the invoice address.
`InvCountryCodeISO3` Nullable	string	ISO3 country code of the invoice address; NULL if the country is not found.
`InvName` Nullable	string	Name of the invoice address.
`InvName2` Nullable	string	Second name line of the invoice address.
`InvFirstName` Nullable	string	First name of the invoice address.
`InvLastName` Nullable	string	Last name of the invoice address.
`InvCompany` Nullable	string	Company / care-of of the invoice address (`CareOf`).
`InvStreet` Nullable	string	Street of the invoice address.
`InvHouseNr` Nullable	string	House number of the invoice address.
`DueDate` Nullable	dateTime	Due date of the invoice.
`DatevUserAccount` Nullable	varchar(10) string	DATEV account number of the customer.

Response example

<SqlRowSet1>
  <row>
    <InvoiceNr>RE100234</InvoiceNr>
    <InvoiceDate>2024-03-04T10:15:00</InvoiceDate>
    <ShopID>SHOP1</ShopID>
    <PaymentAddressId>55012</PaymentAddressId>
    <ShipmentAddressId>55013</ShipmentAddressId>
    <CustKey>20871</CustKey>
    <total>89.90</total>
    <PaymentCode>RE</PaymentCode>
    <PaymentText>Rechnung</PaymentText>
    <Paid>false</Paid>
    <TotalToPay>89.90</TotalToPay>
    <PaidSum>0.00</PaidSum>
    <TrackingID>00340434161234567890</TrackingID>
    <ShipDate>2024-03-04T15:42:00</ShipDate>
    <UpdateDate>2024-03-04T15:42:00</UpdateDate>
    <ReminderLevel>0</ReminderLevel>
    <ShipVendor>DHL</ShipVendor>
    <Currency>EUR</Currency>
    <OrderCurrency>EUR</OrderCurrency>
    <TotalToPay_OrderCurr>89.90</TotalToPay_OrderCurr>
    <InvoiceKey>100234</InvoiceKey>
    <CustomerNrExternal>K-4711</CustomerNrExternal>
    <InvZIP>80331</InvZIP>
    <InvCity>München</InvCity>
    <InvCountryCodeISO3>DEU</InvCountryCodeISO3>
    <InvName>Max Mustermann</InvName>
    <InvFirstName>Max</InvFirstName>
    <InvLastName>Mustermann</InvLastName>
    <InvStreet>Marienplatz</InvStreet>
    <InvHouseNr>1</InvHouseNr>
    <DueDate>2024-03-18T00:00:00</DueDate>
    <DatevUserAccount>10871</DatevUserAccount>
  </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:pixiGetInvoicesUnpaid>
      <!-- <PaidInvoices>0</PaidInvoices> -->
    </ns1:pixiGetInvoicesUnpaid>
  </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:pixiGetInvoicesUnpaid>
      <!-- <PaidInvoices>0</PaidInvoices> -->
    </ns1:pixiGetInvoicesUnpaid>
  </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 = [
    // 'PaidInvoices' => '0',
];

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

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

SQL object:: dbo.pipiGetInvoicesUnpaid