Invoices Read

pixiGetInvoiceLinesPrinted

Returns the lines of an invoice prepared for printing, grouped per order line, with optional bundle combining and a fallback line for invoices without lines.

The call passes InvoiceNr to the internal procedure GOS_PrintInvLine and returns its single result set.

Steps:

The invoice is looked up by InvoiceNr in Invoices (joined with pixi.Currency). Its key, pick list header (PLHref), credit-note reference (RefToInvoiceKey) and DiscountTotal are read.
All invoice lines (Invlines) of the invoice are joined with their order lines, items, country of origin and the item code of type 3 (InternalItemNumber). Lines are grouped by the parent order line (OriginalOrderlineRef, or the order line itself if it was not split) plus price, VAT, discount, serial number, batch, best-before date and further attributes. Quantities and amounts are summed per group.
For each line the first bin name and the number of pick list items are taken from the pick list of the invoice (BinName, QtyFromBin).
Bundles: if the setting CombineBundles = 1 and the invoice is not a credit note (RefToInvoiceKey is NULL), all component lines of a bundle are combined into one line with the data of the bundle order line (BundleOrderlines) and the bundle item. The number of bundles is derived from the component quantity divided by the quantity per bundle from BundleItemsHistDetail (or the bundle order line quantity if no history exists). Otherwise every component line is returned on its own.
If no line results (invoice without lines), one pseudo line is built from the invoice header: text from TextIfNoInvlines, OrderQty = 1, price = invoice total minus shipping cost, VAT code and tax rate derived from the high/low VAT bases. In this row OrderNr holds the invoice key and OrderNrExternal the invoice number.
The result is sorted by ArtName. Each row additionally carries the invoice-level discount as InvoiceTotalDiscount.

The setting UseInitialOrderDate controls OrderDate: 0 returns the latest order date of the grouped order lines, 1 the earliest.

Notes

The parameter is declared as varchar(50), but the internal procedure accepts only varchar(25); longer invoice numbers are silently truncated.
If the invoice number does not exist (or the invoice has no matching currency in pixi.Currency), the result set is empty; no error is returned.
All reads use NOLOCK, so uncommitted data may be returned.
The settings CombineBundles and UseInitialOrderDate are read at runtime via fpixi_GetSetting; ItemTagList is built by fpixi_GetItemTAGsNamesAsString. Neither function is attached.
Columns such as itemprice_ordercurr, ItemAmount_OrderCurr, USDamount and EURamount are legacy duplicates (comments mark them for removal); they contain the same values as the invoice-currency columns.
ItemAmount includes the line amount differences (InvLineAmountDiff), whereas ItemAmountNoVAT_OrderCurr is the plain sum of the net line amounts.

Tables used

Invoices · Read Currency · Read Invlines · Read Orderlines · Read items · Read Countries · Read ItemCode · Read InvBin · Read InvBinItem · Read PLitem · Read BundleOrderlines · Read BundleItemsHistDetail · Read

Parameter	Type	Default	Description
`InvoiceNr` Required	varchar(50) string	–	Invoice number (`Invoices.InvoiceNr`) of the invoice whose lines are returned. Only the first 25 characters are used. Example: `RE2024-001234`

Result set 1 · Always; empty if the invoice number is not found.

Printable invoice lines, sorted by ArtName. For invoices without lines one pseudo line built from the invoice header.

Column	Type	Description
`InvLineKey`	int	Key of the (highest) invoice line of the group; 0 for the fallback line.
`OrderlineKey`	int	Key of the original order line, or of the bundle order line for combined bundles; 0 if not available.
`ArtNr` Nullable	varchar(13) string	Item number from the order line.
`OrderDate` Nullable	dateTime	Order date (latest or earliest depending on `UseInitialOrderDate`); invoice date for the fallback line.
`ArtName` Nullable	varchar(300) string	Item name from the order line, followed by the special note in brackets; truncated to 300 characters.
`OrderCurrency` Nullable	string	Currency code (`CodeA`) of the invoice.
`ItemText` Nullable	string	Item text of the invoice line plus special note in brackets.
`ItemName` Nullable	varchar(300) string	Item name from the item master.
`OrderQty` Nullable	int	Ordered quantity (number of bundles for combined bundles).
`ItemQty` Nullable	int	Invoiced quantity (number of bundles for combined bundles).
`RemainQty` Nullable	int	Ordered minus invoiced quantity; 0 for combined bundles.
`BundleItemsQty` Nullable	int	Sum of component quantities for combined bundles; 0 otherwise.
`ItemPrice` Nullable	decimal	Gross unit price.
`ShipCost` Nullable	decimal	Shipping cost of the line.
`ItemVAT` Nullable	varchar(1) string	VAT code: `H` high, `L` low, `N` none.
`ItemNr` Nullable	string	Item number of the invoice line; EAN of the bundle item for combined bundles.
`itemprice_ordercurr` Nullable	decimal	Legacy duplicate of `ItemPrice`.
`OrderLineRef` Nullable	int	Order line referenced by the invoice line; NULL for combined bundles, 0 for the fallback line.
`ItemAmount` Nullable	decimal	Gross line amount including amount differences.
`ItemAmountNoVat` Nullable	decimal	Net line amount including net amount differences.
`ItemAmount_OrderCurr` Nullable	decimal	Same value as `ItemAmount`.
`USDamount` Nullable	money decimal	Legacy: sum of gross line amounts without differences.
`EURamount` Nullable	money decimal	Legacy: same as `USDamount`; NULL for the fallback line.
`OrderNr` Nullable	string	Order number; invoice key for the fallback line.
`OrderNrExternal` Nullable	string	External order number; invoice number for the fallback line.
`Status` Nullable	string	Status of the order line.
`ItemPriceNoVAT` Nullable	decimal	Net unit price.
`FullPrice_Net` Nullable	decimal	Net full price before discount.
`FullPrice` Nullable	decimal	Gross full price before discount.
`ItemPriceNoVAT_OrderCurr` Nullable	decimal	Same value as `ItemPriceNoVAT`.
`ItemAmountNoVAT_OrderCurr` Nullable	decimal	Sum of net line amounts (without differences for regular lines).
`ConfigType` Nullable	string	Characters 3–4 of the item configuration type.
`GIN` Nullable	string	Internal item number (`ItemNrInt`).
`ItemNrInt` Nullable	string	Internal item number.
`ItemNrSuppl` Nullable	string	Supplier item number.
`BinName` Nullable	varchar(50) string	First Lagerplatz from the pick list of the invoice for this order line; NULL for combined bundles.
`QtyFromBin` Nullable	int	Number of pick list items for this order line on the invoice's pick list; NULL for combined bundles.
`BundledItemsInOrder`	int	Number of combined component lines for bundles; 1 otherwise.
`BundleItemRef` Nullable	int	Item key of the bundle item for combined bundles; NULL otherwise.
`oItemSpecialNote`	string	Special note of the order line; empty string if none.
`EanUpc` Nullable	string	EAN/UPC of the item.
`SerialNr` Nullable	string	Serial number of the invoice line.
`DiscountValue` Nullable	decimal	Discount value of the line; invoice discount total for the fallback line.
`DiscountPerc` Nullable	decimal	Discount percentage of the line.
`MeasuredWeight` Nullable	decimal	Measured weight of the order line (summed for combined bundles).
`PriceWeightUnits_Price` Nullable	decimal	Price per weight unit (average for combined bundles).
`DF_Type`	string	Direct fulfillment type of the order line; `0` if not set.
`CustomsTariffNumber` Nullable	string	Customs tariff number of the item.
`CustomsCountryOfOrigin` Nullable	string	Country of origin of the item.
`CntCodeISO2` Nullable	string	ISO-2 code of the country of origin.
`CntCodeISO3` Nullable	string	ISO-3 code of the country of origin.
`CustomsTariffText` Nullable	varchar(2048) string	Customs tariff text of the item.
`BatchNumber` Nullable	string	Charge (batch number) of the order line.
`BestBeforeDate` Nullable	dateTime	Best-before date of the order line.
`lastSupplierPrice` Nullable	decimal	Supplier price of the order line or of the bundle order line.
`InvoiceTotalDiscount` Nullable	money decimal	Total discount of the invoice (`Invoices.DiscountTotal`), same on every row.
`InternalItemNumber` Nullable	string	Item code of code type 3 from `pixi.ItemCode`; empty string if none.
`TaxRate` Nullable	numeric(6,3) decimal	Tax rate in percent.
`TaxAmount` Nullable	decimal	Tax amount of the line.
`ItemWeight` Nullable	decimal	Weight from the item master.
`PicLinkLarge` Nullable	string	Link to the large item picture.
`PicLinkSmall` Nullable	string	Link to the small item picture.
`ItemDescription` Nullable	string	Item description.
`ItemTagList` Nullable	string	Comma-separated list of the item's tag names.

Response example

<SqlRowSet1>
  <row>
    <InvLineKey>558812</InvLineKey>
    <OrderlineKey>904417</OrderlineKey>
    <ArtNr>4711-BL</ArtNr>
    <OrderDate>2024-03-04T10:15:00</OrderDate>
    <ArtName>T-Shirt Basic blau L</ArtName>
    <OrderCurrency>EUR</OrderCurrency>
    <ItemText>T-Shirt Basic blau L</ItemText>
    <ItemName>T-Shirt Basic</ItemName>
    <OrderQty>2</OrderQty>
    <ItemQty>2</ItemQty>
    <RemainQty>0</RemainQty>
    <BundleItemsQty>0</BundleItemsQty>
    <ItemPrice>19.9900</ItemPrice>
    <ShipCost>0.0000</ShipCost>
    <ItemVAT>H</ItemVAT>
    <ItemNr>4711-BL</ItemNr>
    <ItemAmount>39.9800</ItemAmount>
    <ItemAmountNoVat>33.6000</ItemAmountNoVat>
    <OrderNr>310245</OrderNr>
    <OrderNrExternal>SHOP-100234</OrderNrExternal>
    <Status>AUS</Status>
    <BinName>A-01-03</BinName>
    <QtyFromBin>2</QtyFromBin>
    <BundledItemsInOrder>1</BundledItemsInOrder>
    <oItemSpecialNote/>
    <DF_Type>0</DF_Type>
    <InvoiceTotalDiscount>0.0000</InvoiceTotalDiscount>
    <TaxRate>19.000</TaxRate>
  </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:pixiGetInvoiceLinesPrinted>
      <InvoiceNr>RE2024-001234</InvoiceNr>
    </ns1:pixiGetInvoiceLinesPrinted>
  </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:pixiGetInvoiceLinesPrinted>
      <InvoiceNr>RE2024-001234</InvoiceNr>
    </ns1:pixiGetInvoiceLinesPrinted>
  </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' => 'RE2024-001234',
];

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

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

SQL object:: dbo.pipiGetInvoiceLinesPrinted