Invoices Read

pixiGetInvoicesForCustomer

Returns the invoices of one customer, identified by customer key or external customer number, optionally filtered to a single invoice number.

Lists invoices together with payment, bank or card, shipping and order information for one customer.

Identifying the customer

NULL values are first replaced by 0 (for CustKey) or an empty string. Then exactly one identifier must be set:

CustKey <> 0 and CustomerNrExternal empty: the invoices of this customer key are returned.
CustKey = 0 and CustomerNrExternal set: all customers in Customers with this CustomerNrExternal are used. If several customers share the number, the invoices of all of them are returned.
Both set or both empty: no customer is selected and the result set is empty.

Invoice filter

If InvoiceNr is empty or NULL, all invoices of the customer are returned.
Otherwise only the invoice with exactly this InvoiceNr is returned.

Returned data per invoice

Invoice data from Invoices: number, date, payment and shipment address IDs, total, payment code, payment date, paid amount, tracking ID, ship date and reminder level.
PaymentText comes from Payments. If the code is not found there, the payment code itself is returned.
Paid is 1 if the paid amount (a missing amount counts as 0) equals the total, otherwise 0.
Card and bank data (Cc…, Account, Blz, BankName, AccountName) come from the payment address in Addresses.
OrderNr and OrderNrExternal are the highest values found among the order lines linked to the invoice lines. They are NULL if the invoice has no linked order lines.
OrderCurrencyCode is the currency code from pixi.Currency.

The code does not define a sort order.

Notes

All tables are read WITH (NOLOCK), so uncommitted data can appear in the result.
pixi.Currency is joined with an inner join. Invoices whose CurrencyId has no matching currency are not returned.
If CustKey and CustomerNrExternal are both sent, the call returns no rows and no error. Send only one of them.
If the invoice total is NULL, the comparison for Paid is not true and the column returns 1.
The result contains sensitive payment data: card number, card holder, card expiry and bank account.

Tables used

Customers · Read Invoices · Read Invlines · Read Orderlines · Read Payments · Read Addresses · Read Currency · Read

Parameter	Type	Default	Description
`CustKey`	int	0	Internal customer key. Use either this or `CustomerNrExternal`. 0 or `NULL` means not set. Example: `4711`
`CustomerNrExternal`	varchar(60) string	–	External customer number, for example the shop customer number. It is used only if `CustKey` is 0. Empty or `NULL` means not set. Example: `SHOP-100234`
`InvoiceNr`	varchar(20) string	NULL	Optional invoice number. If it is set, only this invoice is returned. Empty or `NULL` returns all invoices of the customer. Example: `RE2024001234`

Result set 1 · Always; empty if no customer could be identified or no invoice matches.

One row per invoice of the selected customer(s).

Column	Type	Description
`InvoiceNr` Nullable	string	Invoice number.
`InvoiceDate` Nullable	dateTime	Invoice date (`Invoices.InvDate`).
`PaymentAddressId` Nullable	int	Reference to the payment (billing) address (`Invoices.AddrRef`).
`ShipmentAddressId` Nullable	int	Reference to the shipment address (`Invoices.ShipAdrRef`).
`total` Nullable	decimal	Invoice total.
`PaymentCode` Nullable	string	Payment method code (`Invoices.Payment`).
`PaymentText` Nullable	string	Payment method text from `Payments`. If the code is not found there, the payment code is returned.
`PaymentDate` Nullable	dateTime	Payment date (`Invoices.Payed`).
`Paid`	bit boolean	1 if the paid amount (`NULL` counts as 0) equals the total, otherwise 0.
`TotalToPay` Nullable	decimal	Amount to pay; same value as `total`.
`PaidSum` Nullable	decimal	Amount paid so far (`Invoices.PayedSum`).
`CcCardType` Nullable	string	Credit card type from the payment address.
`CcCardNr` Nullable	string	Credit card number from the payment address.
`CcCardName` Nullable	string	Name of the card holder.
`CcCardExp` Nullable	string	Card expiry.
`Account` Nullable	string	Bank account number from the payment address.
`Blz` Nullable	string	Bank code (BLZ).
`BankName` Nullable	string	Name of the bank.
`AccountName` Nullable	string	Name of the account holder.
`TrackingID` Nullable	string	Shipment tracking ID.
`ShipDate` Nullable	dateTime	Scan-out (shipping) date.
`ReminderLevel` Nullable	int	Reminder (dunning) level of the invoice.
`OrderNr` Nullable	int	Highest order number among the order lines linked to the invoice lines. `NULL` if there are none.
`OrderNrExternal` Nullable	string	Highest external order number among the linked order lines. `NULL` if there are none.
`OrderCurrencyCode` Nullable	string	Currency code (`pixi.Currency.CodeA`) of the invoice.

Response example

<SqlRowSet1>
  <row>
    <InvoiceNr>RE2024001234</InvoiceNr>
    <InvoiceDate>2024-03-12T10:15:00</InvoiceDate>
    <PaymentAddressId>88231</PaymentAddressId>
    <ShipmentAddressId>88232</ShipmentAddressId>
    <total>129.90</total>
    <PaymentCode>VK</PaymentCode>
    <PaymentText>Vorkasse</PaymentText>
    <PaymentDate>2024-03-14T00:00:00</PaymentDate>
    <Paid>true</Paid>
    <TotalToPay>129.90</TotalToPay>
    <PaidSum>129.90</PaidSum>
    <TrackingID>00340434161234567890</TrackingID>
    <ShipDate>2024-03-15T08:42:00</ShipDate>
    <ReminderLevel>0</ReminderLevel>
    <OrderNr>502311</OrderNr>
    <OrderNrExternal>SHOP-77812</OrderNrExternal>
    <OrderCurrencyCode>EUR</OrderCurrencyCode>
  </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:pixiGetInvoicesForCustomer>
      <!-- <CustKey>4711</CustKey> -->
      <!-- <CustomerNrExternal>SHOP-100234</CustomerNrExternal> -->
      <!-- <InvoiceNr>RE2024001234</InvoiceNr> -->
    </ns1:pixiGetInvoicesForCustomer>
  </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:pixiGetInvoicesForCustomer>
      <!-- <CustKey>4711</CustKey> -->
      <!-- <CustomerNrExternal>SHOP-100234</CustomerNrExternal> -->
      <!-- <InvoiceNr>RE2024001234</InvoiceNr> -->
    </ns1:pixiGetInvoicesForCustomer>
  </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 = [
    // 'CustKey' => '4711',
    // 'CustomerNrExternal' => 'SHOP-100234',
    // 'InvoiceNr' => 'RE2024001234',
];

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

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

SQL object:: dbo.pipiGetInvoicesForCustomer