Invoices Read

pixiGetInvoiceLines

Returns all lines of one invoice with item, order line, price, VAT, batch and drop-shipping data, optionally including shipping cost, voucher and manual invoice lines.

Returns the invoice lines of the invoice whose number equals InvoiceNr (exact match). The data comes from the reporting view PV_Invlines, which combines real invoice lines with synthetic lines for invoice-level amounts. Each line is enriched with the order line (BatchNumber, BestBeforeDate, TaxRate as VATPercent) and the item (PicLinkLarge).

Special lines (controlled by IncludeSpecialInvLines):

-2: shipping costs of the invoice (ArtName = ZZZ Versandkosten), only if the invoice has shipping costs other than 0.
-1: vouchers (ArtName = ZZZ Gutscheine), price shown as negative voucher sum.
-3: manual invoice without any invoice lines (ArtName = ZZZ Manuelle Rechnung ohne Artikel), amount = invoice total minus shipping costs.

With IncludeSpecialInvLines = 0 (default) these lines are excluded; with 1 they are returned as well. For special lines, OrderLineKey holds the special value (-1, -2, -3), ItemQty is 1, and most item/order fields (InvoiceLineKey, ArtNr, OrderNr, BoxNr, …) are NULL.

ItemPriceNoVAT is the net price rounded to the number of decimals defined in the setting PrintInvLineMoneyPrecision (2 if the setting is missing or 0). No sort order is applied.

Notes

All reads use NOLOCK, so uncommitted data may be returned.
The filter on special lines compares the item key with NOT IN (-1, -2, -3). Real invoice lines without an order line or whose order line has no item have an item key of NULL and are therefore also excluded when IncludeSpecialInvLines = 0. Pass 1 to be sure to get every line of the invoice.
SerialNr falls back to the serial number of another invoice line referencing the same order line if the line itself has none.
The setting PrintInvLineMoneyPrecision is read at runtime from Settings; shop or location specific values are not considered.
The result order is undefined; sort on the client side if needed.
An unknown invoice number returns an empty result set, no error.

Tables used

Invoices · Read Invlines · Read Orderlines · Read Items · Read Customers · Read Addresses · Read Payments · Read Suppliers · Read TontrArten · Read Settings · Read

Parameter Type Default Description

Parameter	Type	Default	Description
`InvoiceNr` Required	varchar(50) string	–	Number of the invoice whose lines are returned (exact match on the invoice number). Example: `100123`
`IncludeSpecialInvLines`	bit boolean	0	Whether synthetic lines for shipping costs, vouchers and manual invoices are included. Allowed values `0` – Only lines whose item key is not NULL and not -1, -2 or -3 (regular item lines). `1` – All lines of the invoice, including shipping cost (-2), voucher (-1) and manual invoice (-3) lines. Example: `1`

InvoiceNr

Required

varchar(50)
string

–

Number of the invoice whose lines are returned (exact match on the invoice number).

Example: 100123

IncludeSpecialInvLines

bit
boolean

Whether synthetic lines for shipping costs, vouchers and manual invoices are included.

Allowed values

0 – Only lines whose item key is not NULL and not -1, -2 or -3 (regular item lines).
1 – All lines of the invoice, including shipping cost (-2), voucher (-1) and manual invoice (-3) lines.

Example: 1

Result set 1 · Always (may be empty if the invoice number does not exist).

One row per invoice line of the requested invoice.

Column	Type	Description
`InvoiceLineKey` Nullable	int	Key of the invoice line; NULL for special lines.
`InvoiceNr`	string	Invoice number.
`OrderLineKey` Nullable	int	Key of the order line; -1 (voucher), -2 (shipping costs) or -3 (manual invoice) for special lines.
`ArtNr` Nullable	string	Article number from the order line.
`ArtName` Nullable	string	Item name; fixed texts such as `ZZZ Versandkosten` for special lines.
`SpecialNote` Nullable	string	Special note of the order line item.
`ItemText` Nullable	string	Text of the invoice line.
`OrderDate` Nullable	dateTime	Order date of the order line.
`OrderQty` Nullable	int	Ordered quantity.
`ItemQty` Nullable	int	Invoiced quantity (negative for credits); 1 for special lines.
`ItemPrice` Nullable	decimal	Gross price of the line (0 if not set); shipping total, negative voucher sum or manual invoice amount for special lines.
`ShipCost` Nullable	decimal	Shipping cost stored on the invoice line.
`ItemVAT` Nullable	string	VAT indicator of the invoice line.
`ItemNr` Nullable	string	Item number on the invoice line.
`ItemNrSuppl` Nullable	string	Supplier item number.
`ItemNrInt` Nullable	string	Internal item number.
`EanUpc` Nullable	string	EAN/UPC of the item.
`SerialNr` Nullable	string	Serial number of the line, or of another invoice line of the same order line if empty.
`OrderNr` Nullable	string	Order number.
`OrderNrExternal` Nullable	string	External order number (e.g. shop order number).
`Status` Nullable	string	Status of the order line.
`ItemPriceNoVAT` Nullable	money decimal	Net price rounded to the precision from setting `PrintInvLineMoneyPrecision` (default 2).
`FullPrice` Nullable	decimal	Full price before discount.
`DiscountValue` Nullable	decimal	Discount amount.
`DiscountPerc` Nullable	decimal	Discount percentage.
`ItemNrExternal` Nullable	string	External item number from the order line.
`DF_Type` Nullable	string	Direct-fulfillment (drop-shipping) type of the order line.
`DFShipDate` Nullable	dateTime	Ship date of the drop-shipment.
`DFShipmentTrackingID` Nullable	string	Tracking ID of the drop-shipment.
`BoxNr` Nullable	string	Box number of the invoice; NULL for special lines.
`BatchNumber` Nullable	string	Batch number (Charge) of the order line.
`BestBeforeDate` Nullable	dateTime	Best-before date of the order line.
`VATPercent` Nullable	decimal	VAT rate of the order line.
`VATAmount` Nullable	numeric(17,4) decimal	VAT amount of the line.
`PicLinkLarge` Nullable	string	Link to the large item picture.

Response example

<SqlRowSet1>
  <row>
    <InvoiceLineKey>554321</InvoiceLineKey>
    <InvoiceNr>100123</InvoiceNr>
    <OrderLineKey>887766</OrderLineKey>
    <ArtNr>A-1001</ArtNr>
    <ArtName>T-Shirt blau M</ArtName>
    <ItemQty>2</ItemQty>
    <ItemPrice>19.9900</ItemPrice>
    <ItemNrInt>A-1001</ItemNrInt>
    <EanUpc>4012345678901</EanUpc>
    <OrderNr>50077</OrderNr>
    <OrderNrExternal>SHOP-98765</OrderNrExternal>
    <ItemPriceNoVAT>16.8000</ItemPriceNoVAT>
    <VATPercent>19.00</VATPercent>
    <VATAmount>3.1900</VATAmount>
  </row>
  <row>
    <InvoiceNr>100123</InvoiceNr>
    <OrderLineKey>-2</OrderLineKey>
    <ArtName>ZZZ Versandkosten</ArtName>
    <ItemNrInt/>
    <EanUpc/>
    <ItemQty>1</ItemQty>
    <ItemPrice>4.9500</ItemPrice>
    <ItemPriceNoVAT>4.1600</ItemPriceNoVAT>
    <VATAmount>0.7900</VATAmount>
  </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:pixiGetInvoiceLines>
      <InvoiceNr>100123</InvoiceNr>
      <!-- <IncludeSpecialInvLines>1</IncludeSpecialInvLines> -->
    </ns1:pixiGetInvoiceLines>
  </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:pixiGetInvoiceLines>
      <InvoiceNr>100123</InvoiceNr>
      <!-- <IncludeSpecialInvLines>1</IncludeSpecialInvLines> -->
    </ns1:pixiGetInvoiceLines>
  </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' => '100123',
    // 'IncludeSpecialInvLines' => '1',
];

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

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

SQL object:: dbo.pipiGetInvoiceLines