Invoices Read

pixiGetInvoice

Returns invoice headers with totals, VAT, payment status, bank data and linked order numbers, filtered by invoice number, shop, dates, order or payment state, with key-based paging.

Selects invoices from Invoices (joined with Customers) and returns one row per invoice with amounts, payment and shipping data.

Parameter normalisation

Empty strings in InvoiceNr, ShopID, PaymentType, OrderNRExternal and OrderNR = 0 are treated as no filter.
RowCount NULL or 0 becomes 1000.
Date parameters before 1990-01-01 are ignored.

Filters (all combined with AND, only applied when set)

StartKey > 0: only invoices with InvoiceKey greater than StartKey.
InvoiceNr: exact invoice number.
InvDateFrom/InvDateTo: invoice date, ShipDateFrom/ShipDateTo: scan-out (ship) date, UpdateDateFrom/UpdateDateTo: last update. All date comparisons are exclusive (> and <).
ShopID: shop of the customer (Customers.CustomerOF).
PaymentType: payment code of the invoice.
OrderNR / OrderNRExternal: invoice must have at least one invoice line belonging to an order line with that order number / external order number.
OnlyUnpaidInvoices = 1: paid sum differs from the rounded total, and there is no open customer account entry (without bank line, DTAUS or manual booking) flagged DoNotRefund.

Paging The first RowCount matching invoices ordered by InvoiceKey are selected. To read the next page, pass the highest InvoiceKey of the previous result as StartKey.

Result

OrderNr/OrderNrExternal are the highest order numbers of the orders linked through the invoice lines (restricted to matching order lines if an order filter is set).
CcpPayID is filled only with AddCCPPayID = 1 and contains a comma-separated list of payment IDs of the linked orders.
PaymentDate is the latest matching date from customer account matching.
Paid is 1 if the paid sum equals the total rounded to 2 decimals.
Discount_Invoice is the gross voucher sum for B2C invoices, otherwise the net voucher sum.
VATHigh/VATLow/VATSum include VAT on shipping and return costs.

Rows are sorted by InvoiceDate.

Notes

All reads use NOLOCK; uncommitted data may appear.
The main selection is built as dynamic SQL (sp_executesql) with parameters.
TotalRows is the number of rows of the selection step, which is limited by RowCount; it is not the total number of matching invoices.
A negative StartKey returns no rows.
Date filters are exclusive: InvDateTo = 2024-03-31 excludes invoices dated exactly at that moment and later on that day.
The final query uses inner joins to CustomerAccount, Payments, Addresses, pixi.Currency and pixi.ExchRate. Invoices without a matching record there are not returned (but are counted in TotalRows). An invoice with several customer account entries, or linked orders with different ccpPayID values, can appear in several rows.
Temporary tables use unique indexes on the invoice key; if an invoice has several customer account entries or ambiguous order data, the call may fail with a duplicate key error from SQL Server.

Tables used

Invoices · Read Customers · Read InvLines · Read Orderlines · Read OrderHeader · Read CustomerAccount · Read CustomerAccountMatch · Read Currency · Read Payments · Read Addresses · Read ExchRate · Read

Parameter	Type	Default	Description
`InvoiceNr`	varchar(20) string	NULL	Exact invoice number. Empty = no filter. Example: `RE2024001234`
`ShopID`	varchar(3) string	NULL	Shop ID of the customer (`Customers.CustomerOF`). Empty = all shops. Example: `SHP`
`PaymentType`	char(1) string	NULL	Payment code of the invoice (`Invoices.Payment`). Empty = all. Example: `V`
`OrderNR`	int	NULL	Only invoices containing lines of this order number. 0 = no filter. Example: `100523`
`OrderNRExternal`	varchar(50) string	NULL	Only invoices containing lines of this external (shop) order number. Empty = no filter. Example: `WEB-2024-55871`
`InvDateFrom`	datetime dateTime	NULL	Invoice date strictly after this value. Values before 1990-01-01 are ignored. Example: `2024-03-01T00:00:00`
`InvDateTo`	datetime dateTime	NULL	Invoice date strictly before this value. Values before 1990-01-01 are ignored. Example: `2024-04-01T00:00:00`
`UpdateDateFrom`	datetime dateTime	NULL	Last update of the invoice strictly after this value. Values before 1990-01-01 are ignored. Example: `2024-03-15T08:00:00`
`UpdateDateTo`	datetime dateTime	NULL	Last update of the invoice strictly before this value. Values before 1990-01-01 are ignored. Example: `2024-03-16T08:00:00`
`RowCount`	int	1000	Maximum number of invoices to select (page size). NULL or 0 = 1000. Example: `500`
`AddCCPPayID`	bit boolean	0	If 1, column `CcpPayID` is filled with the comma-separated payment IDs of the linked orders. Allowed values `0` – `CcpPayID` stays NULL `1` – Fill `CcpPayID` Example: `1`
`OnlyUnpaidInvoices`	bit boolean	0	If 1, only invoices whose paid sum differs from the rounded total and that have no open `DoNotRefund` customer account entry. Allowed values `0` – All invoices `1` – Only unpaid invoices Example: `0`
`StartKey`	int	0	Paging key: only invoices with `InvoiceKey` greater than this value. 0 = start from the beginning; negative values return nothing. Example: `0`
`ShipDateFrom`	datetime dateTime	NULL	Scan-out (ship) date strictly after this value. Values before 1990-01-01 are ignored. Example: `2024-03-01T00:00:00`
`ShipDateTo`	datetime dateTime	NULL	Scan-out (ship) date strictly before this value. Values before 1990-01-01 are ignored. Example: `2024-04-01T00:00:00`

Result set 1 · Always (may be empty)

One row per selected invoice (possibly several, see notes), sorted by InvoiceDate.

Column	Type	Description
`TotalRows` Nullable	int	Number of invoices selected in this page (limited by `RowCount`).
`InvoiceNr` Nullable	varchar(20) string	Invoice number.
`InvoiceDate` Nullable	datetime dateTime	Invoice date.
`ShopID` Nullable	varchar(50) string	Shop of the customer.
`PaymentAddressId` Nullable	int	Billing address key (`Invoices.AddrRef`).
`ShipmentAddressId` Nullable	int	Shipping address key.
`CustKey` Nullable	int	Customer key.
`Total` Nullable	decimal	Invoice total (gross).
`VATSum` Nullable	decimal	Total VAT including shipping and return costs.
`VATHigh` Nullable	decimal	VAT at the high rate including shipping and return costs.
`VATLow` Nullable	decimal	VAT at the reduced rate including shipping and return costs.
`TotalNoVAT` Nullable	decimal	Net total.
`ShipCost` Nullable	decimal	Shipping cost (gross).
`ShipCostNoVAT` Nullable	numeric(17,2) decimal	Shipping cost (net).
`Discount_Invoice` Nullable	decimal	Voucher sum: gross for `B2C`, net otherwise.
`Discount_InvLines` Nullable	decimal	Total discount of the invoice lines.
`PaymentCode` Nullable	char(1) string	Payment code.
`PaymentText` Nullable	string	Payment type text, or the code if no text exists.
`PaymentDate` Nullable	smalldatetime dateTime	Latest customer account matching date.
`Paid`	bit boolean	1 if paid sum equals the total rounded to 2 decimals.
`TotalToPay` Nullable	decimal	Amount to pay (same as `Total`).
`PaidSum` Nullable	decimal	Amount paid so far.
`CcCardType` Nullable	string	Credit card type from the billing address.
`CcCardNr` Nullable	string	Credit card number.
`CcCardName` Nullable	string	Card holder name.
`CcCardExp` Nullable	string	Card expiry.
`Account` Nullable	string	Bank account number.
`Blz` Nullable	string	Bank code.
`BankName` Nullable	string	Bank name.
`AccountName` Nullable	string	Account holder.
`TrackingID` Nullable	string	Shipment tracking ID.
`ShipDate` Nullable	dateTime	Scan-out (ship) date.
`UpdateDate` Nullable	dateTime	Last update of the invoice.
`ReminderLevel` Nullable	int	Dunning level.
`OrderNr` Nullable	int	Highest linked order number.
`OrderNrExternal` Nullable	varchar(50) string	Highest linked external order number.
`Note` Nullable	string	Invoice note.
`ShipVendor` Nullable	string	Shipping carrier.
`CcpPayID` Nullable	varchar(1024) string	Comma-separated payment IDs of linked orders; only with `AddCCPPayID` = 1.
`Currency` Nullable	string	Currency code.
`OrderCurrency` Nullable	string	Currency code (same as `Currency`).
`TotalToPay_OrderCurr` Nullable	decimal	Invoice total (same as `Total`).
`ReturnReason` Nullable	string	Return reason.
`InvoiceKey`	int	Internal invoice key; use the highest value as next `StartKey`.
`RefToInvoiceNr` Nullable	string	Referenced invoice number (e.g. for credit notes).
`RefToInvoiceKey` Nullable	int	Referenced invoice key.
`ReturnCostsAmount` Nullable	decimal	Return costs amount.
`CreateEmp` Nullable	string	Employee who created the invoice.
`ExchangeRate` Nullable	decimal	Exchange rate of the invoice.
`ReturnTrackingId` Nullable	string	Return tracking ID.
`ReturnTrackingIdHistory` Nullable	string	History of return tracking IDs.

Response example

<SqlRowSet1>
  <row>
    <TotalRows>2</TotalRows>
    <InvoiceNr>RE2024001234</InvoiceNr>
    <InvoiceDate>2024-03-05T10:15:00</InvoiceDate>
    <ShopID>SHP</ShopID>
    <PaymentAddressId>55120</PaymentAddressId>
    <ShipmentAddressId>55121</ShipmentAddressId>
    <CustKey>30211</CustKey>
    <Total>119.00</Total>
    <VATSum>19.00</VATSum>
    <VATHigh>19.00</VATHigh>
    <VATLow>0.00</VATLow>
    <TotalNoVAT>100.00</TotalNoVAT>
    <ShipCost>4.95</ShipCost>
    <ShipCostNoVAT>4.16</ShipCostNoVAT>
    <Discount_Invoice>0.00</Discount_Invoice>
    <Discount_InvLines>0.00</Discount_InvLines>
    <PaymentCode>V</PaymentCode>
    <PaymentText>Vorkasse</PaymentText>
    <PaymentDate>2024-03-06T09:00:00</PaymentDate>
    <Paid>true</Paid>
    <TotalToPay>119.00</TotalToPay>
    <PaidSum>119.00</PaidSum>
    <TrackingID>00340434161234567890</TrackingID>
    <ShipDate>2024-03-05T14:30:00</ShipDate>
    <ReminderLevel>0</ReminderLevel>
    <OrderNr>100523</OrderNr>
    <OrderNrExternal>WEB-2024-55871</OrderNrExternal>
    <ShipVendor>DHL</ShipVendor>
    <Currency>EUR</Currency>
    <OrderCurrency>EUR</OrderCurrency>
    <TotalToPay_OrderCurr>119.00</TotalToPay_OrderCurr>
    <InvoiceKey>884512</InvoiceKey>
    <ReturnCostsAmount>0.00</ReturnCostsAmount>
    <CreateEmp>PIXI</CreateEmp>
    <ExchangeRate>1.000000</ExchangeRate>
  </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:pixiGetInvoice>
      <!-- <InvoiceNr>RE2024001234</InvoiceNr> -->
      <!-- <ShopID>SHP</ShopID> -->
      <!-- <PaymentType>V</PaymentType> -->
      <!-- <OrderNR>100523</OrderNR> -->
      <!-- <OrderNRExternal>WEB-2024-55871</OrderNRExternal> -->
      <!-- <InvDateFrom>2024-03-01T00:00:00</InvDateFrom> -->
      <!-- <InvDateTo>2024-04-01T00:00:00</InvDateTo> -->
      <!-- <UpdateDateFrom>2024-03-15T08:00:00</UpdateDateFrom> -->
      <!-- <UpdateDateTo>2024-03-16T08:00:00</UpdateDateTo> -->
      <!-- <RowCount>500</RowCount> -->
      <!-- <AddCCPPayID>1</AddCCPPayID> -->
      <!-- <OnlyUnpaidInvoices>0</OnlyUnpaidInvoices> -->
      <!-- <StartKey>0</StartKey> -->
      <!-- <ShipDateFrom>2024-03-01T00:00:00</ShipDateFrom> -->
      <!-- <ShipDateTo>2024-04-01T00:00:00</ShipDateTo> -->
    </ns1:pixiGetInvoice>
  </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:pixiGetInvoice>
      <!-- <InvoiceNr>RE2024001234</InvoiceNr> -->
      <!-- <ShopID>SHP</ShopID> -->
      <!-- <PaymentType>V</PaymentType> -->
      <!-- <OrderNR>100523</OrderNR> -->
      <!-- <OrderNRExternal>WEB-2024-55871</OrderNRExternal> -->
      <!-- <InvDateFrom>2024-03-01T00:00:00</InvDateFrom> -->
      <!-- <InvDateTo>2024-04-01T00:00:00</InvDateTo> -->
      <!-- <UpdateDateFrom>2024-03-15T08:00:00</UpdateDateFrom> -->
      <!-- <UpdateDateTo>2024-03-16T08:00:00</UpdateDateTo> -->
      <!-- <RowCount>500</RowCount> -->
      <!-- <AddCCPPayID>1</AddCCPPayID> -->
      <!-- <OnlyUnpaidInvoices>0</OnlyUnpaidInvoices> -->
      <!-- <StartKey>0</StartKey> -->
      <!-- <ShipDateFrom>2024-03-01T00:00:00</ShipDateFrom> -->
      <!-- <ShipDateTo>2024-04-01T00:00:00</ShipDateTo> -->
    </ns1:pixiGetInvoice>
  </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 = [
    // 'InvoiceNr' => 'RE2024001234',
    // 'ShopID' => 'SHP',
    // 'PaymentType' => 'V',
    // 'OrderNR' => '100523',
    // 'OrderNRExternal' => 'WEB-2024-55871',
    // 'InvDateFrom' => '2024-03-01T00:00:00',
    // 'InvDateTo' => '2024-04-01T00:00:00',
    // 'UpdateDateFrom' => '2024-03-15T08:00:00',
    // 'UpdateDateTo' => '2024-03-16T08:00:00',
    // 'RowCount' => '500',
    // 'AddCCPPayID' => '1',
    // 'OnlyUnpaidInvoices' => '0',
    // 'StartKey' => '0',
    // 'ShipDateFrom' => '2024-03-01T00:00:00',
    // 'ShipDateTo' => '2024-04-01T00:00:00',
];

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

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

SQL object:: dbo.pipiGetInvoice