Invoices Read

pixiReadInvoices

Returns invoice headers from Pixi, filtered by invoice key or number, by creation and/or update date, or as a key-ordered page.

Reads invoice header records from Invoices. The currency code is taken from pixi.Currency, and the highest-level per-invoice reminder from Reminders is used for reminder data. All modes return the same columns, sorted by InvoiceKey ascending.

If RowCount is set to a value other than 0, the number of returned rows is limited to it. A missing InvoiceKey is treated as 0.

The first matching mode is used:

Create or update date: CreateAndUpdateDateFrom or CreateAndUpdateDateTo is set. Returns invoices with InvoiceKey > InvoiceKey whose CreateDate or UpdateDate lies between the two values. Both bounds are inclusive (BETWEEN).
Create date: CreateDateFrom or CreateDateTo is set. Returns invoices with InvoiceKey > InvoiceKey and CreateDate >= from and < to. Either bound may be left out.
Update date: UpdateDateFrom or UpdateDateTo is set. Same logic on UpdateDate.
Key range / paging: RowCount is passed. Returns invoices with InvoiceKey greater than the given InvoiceKey. For paging, pass the last key received as the next InvoiceKey.
Single invoice: otherwise returns the invoices with InvoiceKey = InvoiceKey or InvoiceNr = InvoiceNr.

In modes 1–3 the key condition also applies, so date filters can be combined with InvoiceKey and RowCount for keyset paging. InvoiceNr is only used in mode 5.

The column ReminderLevel returns the highest HighestReminderLevel from Reminders for the invoice, and ReminderCosts returns that reminder's costs. If no such reminder exists, both columns fall back to the values stored on the invoice. VoucherSum_OrderCurr is the gross voucher sum for OrderType B2C and the net voucher sum otherwise.

Notes

All reads use NOLOCK, so uncommitted data can be returned.
Invoices without a matching record in pixi.Currency (inner join on CurrencyId) are never returned.
Pitfall in mode 1: if only one of CreateAndUpdateDateFrom / CreateAndUpdateDateTo is given, the OR condition is always true. All invoices above InvoiceKey are then returned. Always pass both bounds.
Pitfall in mode 4: passing InvoiceKey together with RowCount returns the invoices after that key, not the invoice itself. InvoiceNr is ignored as soon as RowCount or a date filter is passed.
RowCount = 0 still selects mode 4, but without any row limit. This can return all invoices.
Without any parameters, mode 5 searches for InvoiceKey = 0 and normally returns an empty result. The final fallback branch (WHERE 1 = 0) is never reached.
The row limit uses SET ROWCOUNT.
The columns Total_OrderCurr, Total_ShipCost_OrderCurr, VATBaseHigh_OrderCurr, VATBaseLow_OrderCurr, VATHigh_OrderCurr, VATLow_OrderCurr, VATBaseHigh_ShipCost_OrderCurr and VATBaseLow_ShipCost_OrderCurr are only copies of other columns. Code comments mark them for removal, so they should not be relied on.

Tables used

Invoices · Read Currency · Read Reminders · Read

Parameter	Type	Default	Description
`InvoiceKey`	int	NULL	Invoice key. In mode 5 it selects this invoice. In all other modes only invoices with a greater key are returned (paging start). NULL is treated as 0. Example: `10500`
`InvoiceNr`	varchar(20) string	NULL	Invoice number to look up. Only used in mode 5, i.e. when no date filter and no `RowCount` is passed. Example: `RE2024-001234`
`CreateDateFrom`	datetime dateTime	NULL	Lower bound (inclusive) for `CreateDate`. Example: `2024-05-01T00:00:00`
`CreateDateTo`	datetime dateTime	NULL	Upper bound (exclusive) for `CreateDate`. Example: `2024-06-01T00:00:00`
`UpdateDateFrom`	datetime dateTime	NULL	Lower bound (inclusive) for `UpdateDate`. Only used if no create-date filter is given. Example: `2024-05-01T00:00:00`
`UpdateDateTo`	datetime dateTime	NULL	Upper bound (exclusive) for `UpdateDate`. Example: `2024-06-01T00:00:00`
`CreateAndUpdateDateFrom`	datetime dateTime	NULL	Lower bound (inclusive) for invoices created or updated in the period. Takes priority over all other filters. Pass it together with `CreateAndUpdateDateTo`. Example: `2024-05-01T00:00:00`
`CreateAndUpdateDateTo`	datetime dateTime	NULL	Upper bound (inclusive) for invoices created or updated in the period. Example: `2024-05-31T23:59:59`
`RowCount`	int	NULL	Maximum number of rows (0 or NULL = no limit). If passed, it also activates the key-range mode when no date filter is set. Example: `100`

Result set 1 · Always (may be empty)

Invoice headers matching the selected mode, ordered by InvoiceKey ascending.

Column	Type	Description
`InvoiceKey`	int	Invoice key.
`InvoiceNr` Nullable	string	Invoice number.
`InvDate` Nullable	dateTime	Invoice date.
`CustRef` Nullable	int	Customer reference.
`AddrRef` Nullable	int	Billing address reference.
`Summary` Nullable	decimal	Invoice summary value as stored.
`VATBaseHigh` Nullable	decimal	Taxable base at the high VAT rate.
`VATBaseLow` Nullable	decimal	Taxable base at the reduced VAT rate.
`VATType` Nullable	string	VAT type.
`VATNo` Nullable	string	VAT ID number.
`total` Nullable	decimal	Invoice total.
`Payment` Nullable	string	Payment type.
`Printed` Nullable	string	Printed flag.
`PrintDate` Nullable	dateTime	Print date.
`PayDay` Nullable	dateTime	Payment date.
`Payed` Nullable	string	Paid flag.
`PayedSum` Nullable	decimal	Amount paid.
`SUGKTO` Nullable	string	Account field as stored (accounting).
`BuchuText` Nullable	string	Booking text.
`AuszugNr` Nullable	string	Bank statement number.
`Account` Nullable	string	Bank account number.
`BLZ` Nullable	string	Bank code.
`AccName` Nullable	string	Account holder.
`LSDate` Nullable	dateTime	Direct debit date.
`LSNr` Nullable	string	Direct debit number.
`RLSDate` Nullable	dateTime	Returned direct debit date.
`RLSCost` Nullable	decimal	Returned direct debit costs.
`Reminder1Date` Nullable	dateTime	Date of first reminder.
`Reminder2Date` Nullable	dateTime	Date of second reminder.
`Reminder3Date` Nullable	dateTime	Date of third reminder.
`Note` Nullable	string	Note.
`ShipCode` Nullable	string	Shipping code.
`CreateDate` Nullable	dateTime	Creation timestamp.
`CreateEmp` Nullable	string	Employee who created the invoice.
`UpdateDate` Nullable	dateTime	Last update timestamp.
`UpdateEmp` Nullable	string	Employee who last updated the invoice.
`BoxNr` Nullable	string	Box number.
`Total_OrderCurr` Nullable	decimal	Copy of `Total` (marked for removal).
`Total_ShipCost` Nullable	decimal	Total shipping costs.
`Total_ShipCost_OrderCurr` Nullable	decimal	Copy of `Total_ShipCost` (marked for removal).
`ScanOutDate` Nullable	dateTime	Scan-out (shipping) date.
`OrderCurrency` Nullable	string	Currency code (`CodeA` from `pixi.Currency`).
`VATBaseHigh_OrderCurr` Nullable	decimal	Copy of `VATBaseHigh` (marked for removal).
`VATBaseLow_OrderCurr` Nullable	decimal	Copy of `VATBaseLow` (marked for removal).
`VATHighPerc` Nullable	decimal	High VAT rate in percent.
`VATLowPerc` Nullable	decimal	Reduced VAT rate in percent.
`VATHigh` Nullable	decimal	VAT amount at the high rate.
`VATLow` Nullable	decimal	VAT amount at the reduced rate.
`VATHigh_OrderCurr` Nullable	decimal	Copy of `VATHigh` (marked for removal).
`VATLow_OrderCurr` Nullable	decimal	Copy of `VATLow` (marked for removal).
`VATHText` Nullable	string	Text for the high VAT rate.
`VATLText` Nullable	string	Text for the reduced VAT rate.
`RefToInvoiceNr` Nullable	string	Number of the referenced invoice (e.g. for credit notes).
`RefToInvoiceKey` Nullable	int	Key of the referenced invoice.
`ChargeVAT` Nullable	string	Whether VAT is charged.
`ShipVendor` Nullable	string	Shipping carrier.
`CCPPaymentDone` Nullable	string	Credit card payment done flag.
`CCPAuthCode` Nullable	string	Credit card authorisation code.
`CCPNumOfTries` Nullable	int	Number of credit card charge attempts.
`CCPReCharge` Nullable	string	Credit card recharge flag.
`ShipAdrRef` Nullable	int	Shipping address reference.
`InvLocationID` Nullable	string	Location (warehouse) of the invoice.
`InvShopID` Nullable	string	Shop ID.
`PackageNetWeight` Nullable	decimal	Net package weight.
`PackageGrossWeight` Nullable	decimal	Gross package weight.
`IsPackageLost` Nullable	string	Package lost flag.
`InvShipLock` Nullable	string	Shipping lock flag.
`InvTrackNumber` Nullable	string	Tracking number.
`VoucherSum_OrderCurr` Nullable	decimal	Voucher sum: gross (`VoucherSum`) for B2C, net (`VoucherSum_Net`) otherwise.
`VoucherID` Nullable	string	Voucher ID.
`VATinShipCost` Nullable	decimal	VAT contained in shipping costs.
`ShippedByVendor` Nullable	string	Shipped by supplier (drop shipping) flag.
`VATBaseLow_ShipCost` Nullable	decimal	Shipping cost base at the reduced VAT rate.
`VATBaseHigh_ShipCost` Nullable	decimal	Shipping cost base at the high VAT rate.
`ccpPayID` Nullable	string	Payment provider payment ID.
`ccpTransID` Nullable	string	Payment provider transaction ID.
`ReminderLevel` Nullable	int	Highest reminder level from `Reminders`, or the invoice's own level if no reminder exists.
`ReminderProcessed` Nullable	string	Reminder processed flag.
`ReminderCosts` Nullable	decimal	Costs of the highest-level reminder, or the invoice's own reminder costs.
`ReminderReason` Nullable	string	Reminder reason.
`ShipmentTrackingID` Nullable	string	Shipment tracking ID.
`ReturnReason` Nullable	string	Return reason.
`StockNote` Nullable	string	Warehouse note.
`ReturnSupplier` Nullable	string	Return to supplier value as stored.
`ReturnClosed` Nullable	string	Return closed flag.
`Fulfillment` Nullable	string	Fulfillment value as stored.
`InvoiceMailSent` Nullable	string	Invoice mail sent flag.
`TrackingIDsent` Nullable	string	Tracking ID sent flag.
`PaidByCustomer` Nullable	decimal	Paid by customer value as stored.
`PLHref` Nullable	string	Packing list reference.
`Packages` Nullable	int	Number of packages.
`DiscountTotal` Nullable	decimal	Total discount.
`PaidAtCreation` Nullable	string	Paid at creation flag.
`TextIfNoInvlines` Nullable	string	Text used if the invoice has no lines.
`retTrackID` Nullable	string	Return tracking ID.
`retTrackIDhist` Nullable	string	History of return tracking IDs.
`1SS` Nullable	string	Value of column `1SS` as stored.
`DueDate` Nullable	dateTime	Due date.
`CreateDTAUS` Nullable	string	DTAUS creation flag.
`InvCreationMode` Nullable	string	Invoice creation mode.
`VATBaseHigh_ShipCost_OrderCurr` Nullable	decimal	Copy of `VATBaseHigh_ShipCost` (marked for removal).
`VATBaseLow_ShipCost_OrderCurr` Nullable	decimal	Copy of `VATBaseLow_ShipCost` (marked for removal).

Response example

<SqlRowSet1>
  <row>
    <InvoiceKey>10501</InvoiceKey>
    <InvoiceNr>RE2024-001235</InvoiceNr>
    <InvDate>2024-05-02T10:15:00</InvDate>
    <CustRef>4711</CustRef>
    <total>119.00</total>
    <VATBaseHigh>100.00</VATBaseHigh>
    <VATHigh>19.00</VATHigh>
    <VATHighPerc>19.00</VATHighPerc>
    <OrderCurrency>EUR</OrderCurrency>
    <CreateDate>2024-05-02T10:15:00</CreateDate>
    <UpdateDate>2024-05-03T08:00:00</UpdateDate>
    <ReminderLevel>0</ReminderLevel>
    <InvShopID>SHOP1</InvShopID>
  </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:pixiReadInvoices>
      <!-- <InvoiceKey>10500</InvoiceKey> -->
      <!-- <InvoiceNr>RE2024-001234</InvoiceNr> -->
      <!-- <CreateDateFrom>2024-05-01T00:00:00</CreateDateFrom> -->
      <!-- <CreateDateTo>2024-06-01T00:00:00</CreateDateTo> -->
      <!-- <UpdateDateFrom>2024-05-01T00:00:00</UpdateDateFrom> -->
      <!-- <UpdateDateTo>2024-06-01T00:00:00</UpdateDateTo> -->
      <!-- <CreateAndUpdateDateFrom>2024-05-01T00:00:00</CreateAndUpdateDateFrom> -->
      <!-- <CreateAndUpdateDateTo>2024-05-31T23:59:59</CreateAndUpdateDateTo> -->
      <!-- <RowCount>100</RowCount> -->
    </ns1:pixiReadInvoices>
  </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:pixiReadInvoices>
      <!-- <InvoiceKey>10500</InvoiceKey> -->
      <!-- <InvoiceNr>RE2024-001234</InvoiceNr> -->
      <!-- <CreateDateFrom>2024-05-01T00:00:00</CreateDateFrom> -->
      <!-- <CreateDateTo>2024-06-01T00:00:00</CreateDateTo> -->
      <!-- <UpdateDateFrom>2024-05-01T00:00:00</UpdateDateFrom> -->
      <!-- <UpdateDateTo>2024-06-01T00:00:00</UpdateDateTo> -->
      <!-- <CreateAndUpdateDateFrom>2024-05-01T00:00:00</CreateAndUpdateDateFrom> -->
      <!-- <CreateAndUpdateDateTo>2024-05-31T23:59:59</CreateAndUpdateDateTo> -->
      <!-- <RowCount>100</RowCount> -->
    </ns1:pixiReadInvoices>
  </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 = [
    // 'InvoiceKey' => '10500',
    // 'InvoiceNr' => 'RE2024-001234',
    // 'CreateDateFrom' => '2024-05-01T00:00:00',
    // 'CreateDateTo' => '2024-06-01T00:00:00',
    // 'UpdateDateFrom' => '2024-05-01T00:00:00',
    // 'UpdateDateTo' => '2024-06-01T00:00:00',
    // 'CreateAndUpdateDateFrom' => '2024-05-01T00:00:00',
    // 'CreateAndUpdateDateTo' => '2024-05-31T23:59:59',
    // 'RowCount' => '100',
];

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

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

SQL object:: dbo.pipiReadInvoices