Invoices Read

pixiGetInvoicesByOrderNr

Returns all invoices that contain lines of the given order, with payment, bank/credit card, shipping and currency data.

Looks up every invoice that is linked to the order OrderNr through its invoice lines: the order lines of the order are joined to the invoice lines (OrderLineRef), and each referenced invoice is returned once.

For each invoice the call returns:

Invoice data: number, date, payment and shipment address IDs, total.
Payment data:
- the payment code and its text from the payment types. If no text exists, the code itself is returned as text.
- payment date and paid sum.
- a computed Paid flag.
Bank and credit card data of the payment address (AddrRef).
Shipping data: tracking ID, scan-out date as ship date, and the reminder level.
Order data: OrderNr, plus OrderNrExternal with surrounding spaces removed.
Currency: the ISO code (CodeA) of the invoice currency.

Paid is 1 when PayedSum (NULL treated as 0) equals total, otherwise 0.

The result has no defined sort order.

Notes

All tables are read with NOLOCK, so uncommitted data may appear.
Invoices are only returned if the payment address exists in Addresses and the currency exists in Currency. Both are inner joins.
Paid is 0 for overpaid invoices as well, because the code checks for equality, not for 'paid sum ≥ total'.
If total is NULL, Paid is 1.
If the order lines carry different OrderNrExternal values, the maximum value is returned for all invoices.
The result contains sensitive payment data (account number, credit card number), so the output must be handled accordingly.

Tables used

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

Parameter	Type	Default	Description
`OrderNr` Required	int	–	Pixi order number whose invoices are returned. Example: `123456`

Result set 1 · Always; empty if no invoice lines reference order lines of the order.

One row per invoice linked to the order.

Column	Type	Description
`InvoiceNr` Nullable	string	Invoice number.
`InvoiceDate` Nullable	dateTime	Invoice date (`InvDate`).
`PaymentAddressId`	int	Key of the payment (billing) address (`AddrRef`).
`ShipmentAddressId` Nullable	int	Key of the shipment address (`ShipAdrRef`).
`total` Nullable	decimal	Invoice total.
`PaymentCode` Nullable	string	Payment type code of the invoice.
`PaymentText` Nullable	string	Text of the payment type. If no text exists, the payment code is returned.
`PaymentDate` Nullable	dateTime	Date of payment (`Payed`).
`Paid`	bit boolean	1 if the paid sum equals the total, otherwise 0.
`TotalToPay` Nullable	decimal	Amount to pay. Same value as `total`.
`PaidSum` Nullable	decimal	Sum paid so far (`PayedSum`).
`CcCardType` Nullable	string	Credit card type of the payment address.
`CcCardNr` Nullable	string	Credit card number of the payment address.
`CcCardName` Nullable	string	Name of the credit card holder.
`CcCardExp` Nullable	string	Credit card expiry.
`Account` Nullable	string	Bank account number of 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 of the invoice.
`ShipDate` Nullable	dateTime	Scan-out date of the shipment (`ScanOutDate`).
`ReminderLevel` Nullable	int	Dunning (reminder) level of the invoice.
`OrderNr` Nullable	int	Pixi order number (same as the input).
`OrderNrExternal` Nullable	string	External order number (e.g. shop order number), trimmed.
`OrderCurrencyCode` Nullable	string	Currency code of the invoice (`CodeA`, e.g. EUR).

Response example

<SqlRowSet1>
  <row>
    <InvoiceNr>R2024001234</InvoiceNr>
    <InvoiceDate>2024-03-15T10:22:00</InvoiceDate>
    <PaymentAddressId>45821</PaymentAddressId>
    <ShipmentAddressId>45822</ShipmentAddressId>
    <total>129.90</total>
    <PaymentCode>PP</PaymentCode>
    <PaymentText>PayPal</PaymentText>
    <PaymentDate>2024-03-15T10:25:00</PaymentDate>
    <Paid>true</Paid>
    <TotalToPay>129.90</TotalToPay>
    <PaidSum>129.90</PaidSum>
    <CcCardType/>
    <CcCardNr/>
    <CcCardName/>
    <CcCardExp/>
    <Account/>
    <Blz/>
    <BankName/>
    <AccountName/>
    <TrackingID>00340434161094042557</TrackingID>
    <ShipDate>2024-03-15T16:40:00</ShipDate>
    <ReminderLevel>0</ReminderLevel>
    <OrderNr>123456</OrderNr>
    <OrderNrExternal>SHOP-100045</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:pixiGetInvoicesByOrderNr>
      <OrderNr>123456</OrderNr>
    </ns1:pixiGetInvoicesByOrderNr>
  </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:pixiGetInvoicesByOrderNr>
      <OrderNr>123456</OrderNr>
    </ns1:pixiGetInvoicesByOrderNr>
  </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 = [
    'OrderNr' => '123456',
];

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

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

SQL object:: dbo.pipiGetInvoicesByOrderNr