Orderlines Read

pixiGetOrderlinesForOrder

Returns the orderlines of one or more orders, optionally with bundle orderlines collapsed into a single line, including stock, invoice and shipping data.

Collects the orders to read and returns their orderlines in a single result set.

Order selection

The order with OrderNr = @OrderNr is always included (if it exists).
If @OrderXML is given, every /ORDERS/ORDER element is read (element-centric) with the child elements ORDERNR and ORDERNREXTERNAL. Each order whose OrderNr matches ORDERNR or whose OrderNrExternal matches ORDERNREXTERNAL is added.

Bundle mode

@ReturnBundle decides the mode. If it is NULL, the setting APIBundleOrderlines is used (missing setting = 0).
0: every orderline is returned with its own values (ArtName, Qty, Price, Status, ...).
1: orderlines that belong to a bundle (BundleOrderlineRef set) are collapsed to one row per bundle (the line with the lowest OrderlineKey). Name, quantity, price, external numbers, dates, item, note, discounts, amount, tax rate and net price are taken from the bundle orderline (BundleOrderlines) where available. The status of the bundle is computed from all its orderlines depending on the setting APIBundleOneAllAUS: with 1 the common status is returned if all lines have the same status, otherwise the status with the lowest OrderStateKey; with 0 the status with the highest OrderStateKey is returned.

Calculated columns

StatusText: text of the status from OrderStates.
OrderCurrency: currency code (CodeA) of the order.
VATRate: H if the tax rate of the invoice line (or bundle) equals the order's TaxRateHigh, L if it equals TaxRateLow, otherwise N (also when no invoice line exists).
PriceNoVAT: net price of the invoice line (or bundle), rounded to 2 decimals.
AvailStockOrderLine: available quantity of the item at the orderline's location (location quantity minus open reservations of the item).
ReservedCount: the smaller of Qty and AvailStockOrderLine, but not below 0.
ShipDate: ScanOutDate of the related invoice.

The result has no explicit sort order.

Notes

All tables are read WITH (NOLOCK); uncommitted data may be returned.
@OrderNr has no default and must always be sent. When only @OrderXML is used, a non-existing number (e.g. 0) can be passed.
If the same order is selected both by @OrderNr and by @OrderXML, its key is added twice and its orderlines appear twice in the result.
Orderlines with several invoice lines with ItemQty >= 0 can appear more than once (one row per invoice line). Invoice lines with negative quantity (returns) are ignored.
In bundle mode only lines with BundleOrderlineRef IS NULL or the first line of a partition are returned. If non-bundle orderlines store 0 instead of NULL in BundleOrderlineRef, they would be grouped together and only one of them returned; this depends on the data, which is not visible in the code.
AvailStockOrderLine subtracts the number of reservation rows of the item across all orders (open states except EIN, FUT, FUS), not per location. It is NULL if the item has no ItemLocationSpecific row for the location; then ReservedCount is also NULL.
PriceNoVAT is NULL for orderlines that are not yet invoiced (unless taken from a bundle orderline).
Invalid XML in @OrderXML causes an SQL error from sp_xml_preparedocument.

Tables used

OrderHeader · Read Currency · Read Orderlines · Read BundleOrderlines · Read Items · Read InvLines · Read Invoices · Read OrderStates · Read Settings · Read ItemLocationSpecific · Read Reservation · Read

Parameter Type Default Description

Parameter	Type	Default	Description
`OrderNr` Required	int	–	Pixi order number (`OrderHeader.OrderNr`) whose orderlines are returned. Example: `100234`
`OrderXML`	varchar(max) xml	NULL	Optional XML with further orders, matched by `ORDERNR` or `ORDERNREXTERNAL`. Example: `<ORDERS> <ORDER> <ORDERNR>100235</ORDERNR> <ORDERNREXTERNAL/> </ORDER> <ORDER> <ORDERNR/> <ORDERNREXTERNAL>SHOP-4711</ORDERNREXTERNAL> </ORDER> </ORDERS>`
`ReturnBundle`	bit boolean	NULL	Controls whether bundle orderlines are collapsed. `NULL` uses the setting `APIBundleOrderlines`. Allowed values `0` – Return every orderline individually. `1` – Collapse orderlines of a bundle into one row with bundle values. Example: `1`

OrderNr

Required

int

–

Pixi order number (OrderHeader.OrderNr) whose orderlines are returned.

Example: 100234

OrderXML

varchar(max)
xml

NULL

Optional XML with further orders, matched by ORDERNR or ORDERNREXTERNAL.

Example:

<ORDERS>
  <ORDER>
    <ORDERNR>100235</ORDERNR>
    <ORDERNREXTERNAL/>
  </ORDER>
  <ORDER>
    <ORDERNR/>
    <ORDERNREXTERNAL>SHOP-4711</ORDERNREXTERNAL>
  </ORDER>
</ORDERS>

ReturnBundle

bit
boolean

NULL

Controls whether bundle orderlines are collapsed. NULL uses the setting APIBundleOrderlines.

Allowed values

0 – Return every orderline individually.
1 – Collapse orderlines of a bundle into one row with bundle values.

Example: 1

Result set 1 · Always (may be empty if no order matches).

Orderlines of the selected orders; in bundle mode one row per bundle plus all non-bundle lines.

Column	Type	Description
`OrderlineKey`	int	Key of the orderline (in bundle mode the first orderline of the bundle).
`ArtName` Nullable	string	Item name of the orderline or bundle.
`Qty` Nullable	int	Ordered quantity.
`Price` Nullable	decimal	Unit price.
`Status` Nullable	char(3) string	Status code of the orderline or the computed bundle status.
`StatusText` Nullable	string	Text of the status from `OrderStates`.
`ItemNrExternal` Nullable	string	External item number.
`OrderNr` Nullable	int	Pixi order number.
`OrderNrExternal` Nullable	string	External (shop) order number.
`OrderCurrency` Nullable	string	Currency code of the order (`Currency.CodeA`).
`OrderDate` Nullable	dateTime	Order date.
`ItemRef` Nullable	int	Item key (`Items.ItemKey`).
`oItemSpecialNote` Nullable	string	Special note for the orderline.
`DiscountValue` Nullable	decimal	Discount amount.
`DiscountPerc` Nullable	decimal	Discount in percent.
`FullPrice` Nullable	decimal	Line amount (`OrderLineAmount`).
`VATRate`	varchar(1) string	`H` = high tax rate, `L` = low tax rate, `N` = other or unknown.
`PriceNoVAT` Nullable	decimal	Net unit price from the invoice line or bundle, rounded to 2 decimals.
`AvailStockOrderLine` Nullable	int	Available quantity of the item at the orderline's location minus open reservations.
`ItemNrInt` Nullable	string	Internal item number.
`ShipDate` Nullable	dateTime	Scan-out date of the related invoice.
`DFShipDate` Nullable	dateTime	Ship date for direct fulfillment.
`DFShipmentTrackingID` Nullable	string	Tracking ID for direct fulfillment.
`ReservedCount` Nullable	int	Smaller value of `Qty` and `AvailStockOrderLine`, at least 0.
`ItemNrSuppl` Nullable	string	Supplier item number.

Response example

<SqlRowSet1>
  <row>
    <OrderlineKey>553201</OrderlineKey>
    <ArtName>Laufschuh Pro 42</ArtName>
    <Qty>1</Qty>
    <Price>89.9000</Price>
    <Status>AUS</Status>
    <StatusText>Ausgeliefert</StatusText>
    <ItemNrExternal>LS-PRO-42</ItemNrExternal>
    <OrderNr>100234</OrderNr>
    <OrderNrExternal>SHOP-4711</OrderNrExternal>
    <OrderCurrency>EUR</OrderCurrency>
    <OrderDate>2024-03-12T10:15:00</OrderDate>
    <ItemRef>8812</ItemRef>
    <oItemSpecialNote/>
    <DiscountValue>0.0000</DiscountValue>
    <DiscountPerc>0</DiscountPerc>
    <FullPrice>89.9000</FullPrice>
    <VATRate>H</VATRate>
    <PriceNoVAT>75.55</PriceNoVAT>
    <AvailStockOrderLine>14</AvailStockOrderLine>
    <ItemNrInt>100-8812</ItemNrInt>
    <ShipDate>2024-03-13T14:02:00</ShipDate>
    <ReservedCount>1</ReservedCount>
    <ItemNrSuppl>SUP-77812</ItemNrSuppl>
  </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:pixiGetOrderlinesForOrder>
      <OrderNr>100234</OrderNr>
      <!-- <OrderXML><![CDATA[<ORDERS><ORDER><ORDERNR>100235</ORDERNR><ORDERNREXTERNAL></ORDERNREXTERNAL></ORDER><ORDER><ORDERNR></ORDERNR><ORDERNREXTERNAL>SHOP-4711</ORDERNREXTERNAL></ORDER></ORDERS>]]></OrderXML> -->
      <!-- <ReturnBundle>1</ReturnBundle> -->
    </ns1:pixiGetOrderlinesForOrder>
  </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:pixiGetOrderlinesForOrder>
      <OrderNr>100234</OrderNr>
      <!-- <OrderXML><![CDATA[<ORDERS><ORDER><ORDERNR>100235</ORDERNR><ORDERNREXTERNAL></ORDERNREXTERNAL></ORDER><ORDER><ORDERNR></ORDERNR><ORDERNREXTERNAL>SHOP-4711</ORDERNREXTERNAL></ORDER></ORDERS>]]></OrderXML> -->
      <!-- <ReturnBundle>1</ReturnBundle> -->
    </ns1:pixiGetOrderlinesForOrder>
  </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 = [
    'OrderNr' => '100234',
    // 'OrderXML' => '<ORDERS><ORDER><ORDERNR>100235</ORDERNR><ORDERNREXTERNAL></ORDERNREXTERNAL></ORDER><ORDER><ORDERNR></ORDERNR><ORDERNREXTERNAL>SHOP-4711</ORDERNREXTERNAL></ORDER></ORDERS>',
    // 'ReturnBundle' => '1',
];

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

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

SQL object:: dbo.pipiGetOrderlinesForOrder