Orderlines Read

pixiGetOrderlineExtended

Returns order lines and bundle order lines with prices, VAT, stock availability, reservation, shipping and picklist data, filtered by order, item, status, shop, payment or date.

Returns extended data for order lines. All filters are optional and combined with AND. Empty strings and 0 for OrderlineKey/OrderNR are treated as "no filter".

Item filter (ItemXML): items can be given by ITEMKEY, ITEMNRINT, ITEMNRSUPPL or EAN. All matching item keys are collected; only order lines of these items are returned.

Bundle handling (ShowOrderlines):

1: bundles are not treated as normal items, so the component order lines of bundles are included.
0: bundles are treated as normal items, so order lines that belong to a bundle (BundleOrderlineRef set) are excluded.
not sent / NULL: taken from the setting ProcessBundleItemsAsNormalItems.

Selection steps:

Up to RowCount (default 1000) rows from Orderlines matching the filters, ordered by OrderlineKey.
The remaining row budget is filled with rows from BundleOrderlines (grouped per bundle line). Filters on OrderNR and OrderNRExternal only match bundle lines if bundles are processed as normal items; date and invoice filters are applied to the component order lines.

Key filter: with IsStartKey = 0, OrderlineKey returns exactly this line; with IsStartKey = 1, all lines with OrderlineKey >= the given value, which allows paging together with RowCount.

Other filters: Status (order line status), ShopID (customer's shop), PaymentType (payment type of the address), OrderDateFrom/OrderDateTo and UpdateDateFrom/UpdateDateTo (exclusive bounds), InvoiceNr (line is contained in this invoice).

Replacements (IsReplacementOnly = 1): only order lines with status MIN or with a MIN entry in the order history are returned.

Calculated values:

AvailStockOrderLine: stock quantity of the item at the line's location minus the number of earlier reservations for the same item among the selected lines (only lines in non-final states other than EIN, FUT, FUS are ranked).
ReservedCount: the smaller of Qty and AvailStockOrderLine, at least 0.
VATRate, PriceNoVAT, ShipDate come from the first invoice line of the order line if there is one, otherwise from the order line and item.

The result is sorted by OrderlineKey.

Notes

All reads use NOLOCK; results may contain uncommitted data.
The selection is built as dynamic SQL. The row limit comes from RowCount (NULL becomes 1000); the RowCount parameter itself is not used otherwise.
If ItemXML is sent but none of the given items can be resolved, no item filter is applied and lines of all items are returned.
Invalid XML in ItemXML causes an SQL error from sp_xml_preparedocument.
For bundle lines the key filter is always an exact match (IsStartKey is ignored there).
IsReplacementOnly only restricts normal order lines; selected bundle lines are still returned.
Normal order lines are only returned if an ItemLocationSpecific record exists for the item and the line's LocationID, and if the order header and its currency exist.
An order line on several picklists appears once per picklist (PicklistNr).
For bundle lines ShipDate, DFShipDate, DFShipmentTrackingID, AvailStockOrderLine, EstimatedDelivery and therefore ReservedCount are NULL; OrderDate/UpdateDate come from one of the component lines.

Tables used

OrderStates · Read Orderlines · Read BundleOrderlines · Read Customers · Read Addresses · Read items · Read Invlines · Read Invoices · Read OrderHistory · Read Reservation · Read PLitem · Read OrderHeader · Read Currency · Read ItemLocationSpecific · Read Orderline_EstimatedDelivery · Read Settings · Read

Parameter	Type	Default	Description
`OrderlineKey`	int	NULL	Order line key. Exact match or start key depending on `IsStartKey`. `0` means no filter. Example: `123456`
`OrderNR`	int	NULL	Pixi order number. `0` means no filter. Example: `500123`
`OrderNRExternal`	varchar(50) string	NULL	External (shop) order number. Example: `WEB-100045`
`Status`	varchar(3) string	NULL	Order line status code. Example: `AUF`
`ShopID`	varchar(3) string	NULL	Shop ID; filters on the shop of the customer (`CustomerOF`). Example: `SHP`
`PaymentType`	char(1) string	NULL	Payment type of the order address (`APayment`). Example: `V`
`OrderDateFrom`	datetime dateTime	NULL	Only lines with order date after this value (exclusive). Example: `2024-01-01T00:00:00`
`OrderDateTo`	datetime dateTime	NULL	Only lines with order date before this value (exclusive). Example: `2024-02-01T00:00:00`
`UpdateDateFrom`	datetime dateTime	NULL	Only lines updated after this value (exclusive). Example: `2024-01-15T08:00:00`
`UpdateDateTo`	datetime dateTime	NULL	Only lines updated before this value (exclusive). Example: `2024-01-16T08:00:00`
`RowCount`	int	1000	Maximum number of selected lines (order lines first, then bundle lines). NULL means 1000. Example: `500`
`InvoiceNr`	varchar(50) string	NULL	Only lines contained in the invoice with this number. Example: `RE-2024-000123`
`ShowOrderlines`	bit boolean	NULL	Controls how bundle component lines are handled. Allowed values `1` – Bundles are not processed as normal items; component order lines are included. `0` – Bundles are processed as normal items; order lines belonging to a bundle are excluded. `NULL` – Use the setting `ProcessBundleItemsAsNormalItems`. Example: `1`
`IsStartKey`	bit boolean	0	Defines how `OrderlineKey` is compared. Allowed values `0` – Exact match on `OrderlineKey`. `1` – All lines with `OrderlineKey` greater than or equal to the value (paging). Example: `1`
`ItemXML`	varchar(max) xml	NULL	Optional list of items to filter by; each `ITEM` may contain `ITEMKEY`, `ITEMNRINT`, `ITEMNRSUPPL` or `EAN`. Example: `<ITEMS> <ITEM> <ITEMKEY>4711</ITEMKEY> <ITEMNRINT>ART-1001</ITEMNRINT> <ITEMNRSUPPL>SUP-55</ITEMNRSUPPL> <EAN>4006381333931</EAN> </ITEM> </ITEMS>`
`IsReplacementOnly`	bit boolean	0	Restricts normal order lines to replacement lines. Allowed values `0` – All matching lines. `1` – Only lines with status `MIN` or a `MIN` entry in the order history. Example: `0`

Result set 1 · Always (may be empty)

Order lines and bundle lines, sorted by OrderlineKey.

Column	Type	Description
`OrderlineKey` Nullable	int	Order line key.
`ArtName` Nullable	varchar(120) string	Item name on the line.
`Qty` Nullable	int	Quantity.
`Price` Nullable	money decimal	Unit price.
`Status` Nullable	varchar(3) string	Line status code.
`StatusText` Nullable	varchar(60) string	Text of the status.
`ItemNrExternal` Nullable	varchar(50) string	External item number.
`OrderNR` Nullable	int	Pixi order number.
`OrderNrExternal` Nullable	varchar(50) string	External order number.
`OrderCurrency` Nullable	varchar(3) string	Currency code of the order.
`OrderDate` Nullable	datetime dateTime	Order date.
`UpdateDate` Nullable	datetime dateTime	Last change of the line.
`ItemRef` Nullable	int	Item key.
`oItemSpecialNote` Nullable	varchar(8000) string	Special note on the line.
`DiscountValue` Nullable	money decimal	Discount amount.
`DiscountPerc` Nullable	numeric(6,2) decimal	Discount percentage.
`FullPrice` Nullable	money decimal	`Qty` × `Price`.
`FullPriceForItem` Nullable	money decimal	Full price stored on the line.
`VATRate` Nullable	varchar(1) string	VAT class: `H` (high), `L` (low), `N` (none); from the invoice line or the item.
`PriceNoVAT` Nullable	money decimal	Net unit price, from the invoice line or the order line.
`ItemNrInt` Nullable	varchar(50) string	Internal item number.
`ShipDate` Nullable	datetime dateTime	Scan-out date of the invoice (or referenced invoice); NULL for bundle lines.
`DFShipDate` Nullable	datetime dateTime	Direct fulfillment ship date.
`DFShipmentTrackingID` Nullable	varchar(100) string	Direct fulfillment tracking ID.
`AvailStockOrderLine` Nullable	int	Stock at the line's location minus earlier reservations for the item; NULL for bundle lines.
`EstimatedDelivery` Nullable	datetime dateTime	Estimated delivery date.
`EANUPC` Nullable	varchar(13) string	EAN/UPC of the item.
`ReservedCount` Nullable	numeric decimal	Minimum of `Qty` and `AvailStockOrderLine`, not below 0.
`PicLinkLarge` Nullable	varchar(512) string	Link to the large item picture.
`LocationID` Nullable	varchar(3) string	Location (warehouse) of the line.
`TaxAmount` Nullable	money decimal	Tax amount of the line.
`VATPercentage` Nullable	numeric(6,3) decimal	Tax rate in percent.
`OrderStatus` Nullable	varchar(10) string	Status of the order header.
`PicklistNr` Nullable	int	Picklist number, if the line is on a picklist.

Response example

<SqlRowSet1>
  <row>
    <OrderlineKey>123456</OrderlineKey>
    <ArtName>T-Shirt Blue M</ArtName>
    <Qty>2</Qty>
    <Price>19.9900</Price>
    <Status>AUF</Status>
    <StatusText>Offen</StatusText>
    <ItemNrExternal>TS-BL-M</ItemNrExternal>
    <OrderNR>500123</OrderNR>
    <OrderNrExternal>WEB-100045</OrderNrExternal>
    <OrderCurrency>EUR</OrderCurrency>
    <OrderDate>2024-01-15T10:22:00</OrderDate>
    <UpdateDate>2024-01-15T11:05:00</UpdateDate>
    <ItemRef>4711</ItemRef>
    <DiscountValue>0.0000</DiscountValue>
    <DiscountPerc>0.00</DiscountPerc>
    <FullPrice>39.9800</FullPrice>
    <FullPriceForItem>39.9800</FullPriceForItem>
    <VATRate>H</VATRate>
    <PriceNoVAT>16.8000</PriceNoVAT>
    <ItemNrInt>ART-1001</ItemNrInt>
    <AvailStockOrderLine>14</AvailStockOrderLine>
    <EANUPC>4006381333931</EANUPC>
    <ReservedCount>2.0</ReservedCount>
    <LocationID>001</LocationID>
    <TaxAmount>6.3800</TaxAmount>
    <VATPercentage>19.000</VATPercentage>
    <OrderStatus>OPEN</OrderStatus>
    <PicklistNr>8812</PicklistNr>
  </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:pixiGetOrderlineExtended>
      <!-- <OrderlineKey>123456</OrderlineKey> -->
      <!-- <OrderNR>500123</OrderNR> -->
      <!-- <OrderNRExternal>WEB-100045</OrderNRExternal> -->
      <!-- <Status>AUF</Status> -->
      <!-- <ShopID>SHP</ShopID> -->
      <!-- <PaymentType>V</PaymentType> -->
      <!-- <OrderDateFrom>2024-01-01T00:00:00</OrderDateFrom> -->
      <!-- <OrderDateTo>2024-02-01T00:00:00</OrderDateTo> -->
      <!-- <UpdateDateFrom>2024-01-15T08:00:00</UpdateDateFrom> -->
      <!-- <UpdateDateTo>2024-01-16T08:00:00</UpdateDateTo> -->
      <!-- <RowCount>500</RowCount> -->
      <!-- <InvoiceNr>RE-2024-000123</InvoiceNr> -->
      <!-- <ShowOrderlines>1</ShowOrderlines> -->
      <!-- <IsStartKey>1</IsStartKey> -->
      <!-- <ItemXML><![CDATA[<ITEMS><ITEM><ITEMKEY>4711</ITEMKEY><ITEMNRINT>ART-1001</ITEMNRINT><ITEMNRSUPPL>SUP-55</ITEMNRSUPPL><EAN>4006381333931</EAN></ITEM></ITEMS>]]></ItemXML> -->
      <!-- <IsReplacementOnly>0</IsReplacementOnly> -->
    </ns1:pixiGetOrderlineExtended>
  </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:pixiGetOrderlineExtended>
      <!-- <OrderlineKey>123456</OrderlineKey> -->
      <!-- <OrderNR>500123</OrderNR> -->
      <!-- <OrderNRExternal>WEB-100045</OrderNRExternal> -->
      <!-- <Status>AUF</Status> -->
      <!-- <ShopID>SHP</ShopID> -->
      <!-- <PaymentType>V</PaymentType> -->
      <!-- <OrderDateFrom>2024-01-01T00:00:00</OrderDateFrom> -->
      <!-- <OrderDateTo>2024-02-01T00:00:00</OrderDateTo> -->
      <!-- <UpdateDateFrom>2024-01-15T08:00:00</UpdateDateFrom> -->
      <!-- <UpdateDateTo>2024-01-16T08:00:00</UpdateDateTo> -->
      <!-- <RowCount>500</RowCount> -->
      <!-- <InvoiceNr>RE-2024-000123</InvoiceNr> -->
      <!-- <ShowOrderlines>1</ShowOrderlines> -->
      <!-- <IsStartKey>1</IsStartKey> -->
      <!-- <ItemXML><![CDATA[<ITEMS><ITEM><ITEMKEY>4711</ITEMKEY><ITEMNRINT>ART-1001</ITEMNRINT><ITEMNRSUPPL>SUP-55</ITEMNRSUPPL><EAN>4006381333931</EAN></ITEM></ITEMS>]]></ItemXML> -->
      <!-- <IsReplacementOnly>0</IsReplacementOnly> -->
    </ns1:pixiGetOrderlineExtended>
  </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 = [
    // 'OrderlineKey' => '123456',
    // 'OrderNR' => '500123',
    // 'OrderNRExternal' => 'WEB-100045',
    // 'Status' => 'AUF',
    // 'ShopID' => 'SHP',
    // 'PaymentType' => 'V',
    // 'OrderDateFrom' => '2024-01-01T00:00:00',
    // 'OrderDateTo' => '2024-02-01T00:00:00',
    // 'UpdateDateFrom' => '2024-01-15T08:00:00',
    // 'UpdateDateTo' => '2024-01-16T08:00:00',
    // 'RowCount' => '500',
    // 'InvoiceNr' => 'RE-2024-000123',
    // 'ShowOrderlines' => '1',
    // 'IsStartKey' => '1',
    // 'ItemXML' => '<ITEMS><ITEM><ITEMKEY>4711</ITEMKEY><ITEMNRINT>ART-1001</ITEMNRINT><ITEMNRSUPPL>SUP-55</ITEMNRSUPPL><EAN>4006381333931</EAN></ITEM></ITEMS>',
    // 'IsReplacementOnly' => '0',
];

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

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

SQL object:: dbo.pipiGetOrderlineExtended