Orderlines Read

pixiReadOrderlines

Returns order lines from `Orderlines`, selected by key, key range or creation/update date range, with currency, exchange rate, shipping cost and estimated delivery.

Reads order lines (Auftragspositionen) in one of several selection modes. A missing OrderlineKey is treated as 0. The first mode that applies is used:

Created or updated – if CreateAndUpdateDateFrom or CreateAndUpdateDateTo is set: lines with OrderlineKey > OrderlineKey whose CreateDate or UpdateDate lies in the range.
Created – else if CreateDateFrom or CreateDateTo is set: lines with key > OrderlineKey whose CreateDate lies in the range.
Updated – else if UpdateDateFrom or UpdateDateTo is set: lines with key > OrderlineKey whose UpdateDate lies in the range.
Key range – else if RowCount is passed (any value, including 0): all lines with key > OrderlineKey.
Single line – otherwise: the line with exactly OrderlineKey.

In the date modes a missing lower bound defaults to 10 years before the current server time, a missing upper bound to the current server time (GETDATE()). Bounds are inclusive (BETWEEN).

If RowCount > 0, the number of rows is limited to RowCount, taking the lowest keys first. The result is sorted by OrderlineKey ascending. This allows keyset paging: pass the last returned OrderlineKey as OrderlineKey in the next call.

Each line is joined with its order header (OrderHeader), the order's exchange rate and currency, and optionally with Orderline_EstimatedDelivery.

Notes

All tables are read WITH (NOLOCK); uncommitted or inconsistent data can be returned.
The limit is applied with SET ROWCOUNT, which affects both the candidate selection and the final query.
Lines are only returned if their order header, exchange rate (pixi.ExchRate) and currency (pixi.Currency) exist (inner joins). A page can therefore contain fewer than RowCount rows even though more data follows; continue paging from the last key, and stop only when no row is returned.
If Orderline_EstimatedDelivery holds more than one row per order line, the line appears several times.
Passing RowCount = 0 without date filters returns all lines above OrderlineKey without a limit.
Calling without any parameter looks up OrderlineKey = 0 and normally returns no rows.
Date filters have priority over each other in the order listed; only one date mode is used per call.
Price_OrderCurr equals Price, ShipCost_OrderCurr equals ShipCost, Price_OrderCurr_Set and ShipCost_OrderCurr_Set are always 0, SOrderlineRef is always NULL. Code comments mark the _OrderCurr columns for removal.
ShipCost is the shipping cost of the whole order header and is repeated on every line of the order.

Tables used

Orderlines · Read OrderHeader · Read ExchRate · Read Currency · Read Orderline_EstimatedDelivery · Read

Parameter	Type	Default	Description
`OrderlineKey`	int	NULL	In single-line mode the key of the line to return; in all other modes only lines with a greater key are returned (paging start). `NULL` is treated as `0`. Example: `0`
`CreateDateFrom`	datetime dateTime	NULL	Lower bound for `CreateDate` (inclusive). Defaults to 10 years ago if only the upper bound is set. Example: `2024-01-01T00:00:00`
`CreateDateTo`	datetime dateTime	NULL	Upper bound for `CreateDate` (inclusive). Defaults to the current server time if only the lower bound is set. Example: `2024-01-31T23:59:59`
`UpdateDateFrom`	datetime dateTime	NULL	Lower bound for `UpdateDate` (inclusive). Used only if no create or create-and-update filter is set. Example: `2024-05-01T00:00:00`
`UpdateDateTo`	datetime dateTime	NULL	Upper bound for `UpdateDate` (inclusive). Defaults to the current server time. Example: `2024-05-02T00:00:00`
`CreateAndUpdateDateFrom`	datetime dateTime	NULL	Lower bound; lines whose `CreateDate` or `UpdateDate` lies in the range are returned. Has priority over all other date filters. Example: `2024-05-01T00:00:00`
`CreateAndUpdateDateTo`	datetime dateTime	NULL	Upper bound for the combined create/update filter. Defaults to the current server time. Example: `2024-05-02T00:00:00`
`RowCount`	int	NULL	Maximum number of rows if > 0. If passed without date filters, switches to key-range mode (all lines with key > `OrderlineKey`). Example: `500`

Result set 1 · Always; may be empty.

Selected order lines with order currency, exchange rate, order shipping cost and estimated delivery, sorted by OrderlineKey ascending.

Column	Type	Description
`OrderlineKey`	int	Key of the order line.
`OrderNr`	int	Order number (link to `OrderHeader`).
`CustRef` Nullable	int	Customer reference.
`AddrRef` Nullable	int	Address reference.
`ShipAdrRef` Nullable	int	Shipping address reference.
`InvRef` Nullable	int	Invoice reference.
`NODRef` Nullable	int	Value of `Orderlines.NODRef`.
`SupplRef` Nullable	string	Supplier reference.
`SupplInfo` Nullable	string	Supplier information.
`ShipRef` Nullable	int	Shipment reference.
`ArtNr` Nullable	string	Item number.
`ArtName` Nullable	string	Item name.
`Qty` Nullable	int	Quantity.
`Backlist` Nullable	string	Value of `Orderlines.Backlist`.
`Price` Nullable	decimal	Sales price of the line.
`SupplPrice` Nullable	decimal	Supplier (purchase) price.
`ArtVAT` Nullable	string	VAT code of the item.
`Status` Nullable	string	Status of the order line.
`Note` Nullable	string	Note.
`OrderMS` Nullable	string	Value of `Orderlines.OrderMS`.
`ConfMS` Nullable	string	Value of `Orderlines.ConfMS`.
`ConfigType` Nullable	string	Value of `Orderlines.ConfigType`.
`MusicType` Nullable	string	Value of `Orderlines.MusicType`.
`Source` Nullable	string	Source of the order line.
`OrderDate` Nullable	dateTime	Order date.
`TimeZone` Nullable	string	Time zone.
`SOrderDate` Nullable	dateTime	Supplier order date.
`ConfDate` Nullable	dateTime	Confirmation date.
`ConfVia` Nullable	string	Confirmation channel.
`LConfDate` Nullable	dateTime	Last confirmation date.
`LConfVia` Nullable	string	Channel of the last confirmation.
`ShipDate` Nullable	dateTime	Shipping date.
`AktionsKennz` Nullable	string	Promotion code.
`Discount` Nullable	decimal	Discount.
`ShipId` Nullable	string	Shipment ID.
`BoxNr` Nullable	string	Box number.
`OrderLineType` Nullable	string	Type of the order line.
`BackorderRef` Nullable	int	Backorder reference.
`OrderCurrency` Nullable	string	Currency code of the order (`pixi.Currency.CodeA`).
`CreateDate` Nullable	dateTime	Creation date.
`CreateEmp` Nullable	string	Employee who created the line.
`UpdateDate` Nullable	dateTime	Date of the last update.
`UpdateEmp` Nullable	string	Employee of the last update.
`OrderExchRate` Nullable	decimal	Exchange rate of the order (`pixi.ExchRate.ExchRate`).
`ItemRef` Nullable	int	Item reference.
`WaitDays` Nullable	int	Waiting days.
`Price_OrderCurr` Nullable	decimal	Same value as `Price`; marked for removal.
`TaxAmount` Nullable	decimal	Tax amount.
`TaxRate` Nullable	decimal	Tax rate.
`ShipCost` Nullable	decimal	Shipping cost of the order header (`OrderHeader.ShipCost`).
`ShipCostPerItem` Nullable	decimal	Shipping cost per item.
`ShipCost_OrderCurr` Nullable	decimal	Same value as `ShipCost`; marked for removal.
`OrderNrExternal` Nullable	string	External order number (e.g. shop order number).
`ItemNrExternal` Nullable	string	External item number.
`LocationID` Nullable	string	Location (warehouse) ID.
`LastOrdLocationID` Nullable	string	Location of the last order.
`SupplGroup` Nullable	string	Supplier group.
`SOrderlineRef` Nullable	int	Always `NULL`.
`CustomerEMailed` Nullable	string	Value of `Orderlines.CustomerEMailed` (customer notification).
`DelayEMailed` Nullable	string	Value of `Orderlines.DelayEMailed` (delay notification).
`SupplGroupScanIn` Nullable	string	Value of `Orderlines.SupplGroupScanIn`.
`rowguid` Nullable	string	Row GUID.
`OrdShopID` Nullable	string	Shop ID of the order.
`OrGosF1` Nullable	string	Value of `Orderlines.OrGosF1`.
`OrGosF2` Nullable	string	Value of `Orderlines.OrGosF2`.
`SupplPrice_Source` Nullable	decimal	Supplier price in source currency.
`Curr_Source` Nullable	string	Source currency.
`LastDelayEmail` Nullable	dateTime	Last delay e-mail.
`ConfEmailed` Nullable	string	Value of `Orderlines.ConfEmailed` (confirmation e-mail).
`HALEmailed` Nullable	string	Value of `Orderlines.HALEmailed`.
`CCReqEmailed` Nullable	string	Value of `Orderlines.CCReqEmailed`.
`CCEXpEmailed` Nullable	string	Value of `Orderlines.CCEXpEmailed`.
`NotEmailed` Nullable	string	Value of `Orderlines.NotEmailed`.
`OrShippingNote` Nullable	string	Shipping note.
`ForceFirstSuppl` Nullable	string	Value of `Orderlines.ForceFirstSuppl`.
`ForceLocToSuppl` Nullable	string	Value of `Orderlines.ForceLocToSuppl`.
`ForceParallelShip` Nullable	string	Value of `Orderlines.ForceParallelShip`.
`VoucherID` Nullable	string	Voucher ID.
`OrReplFlag` Nullable	string	Value of `Orderlines.OrReplFlag`.
`OrCCPLock` Nullable	string	Value of `Orderlines.OrCCPLock`.
`ChangeDate` Nullable	dateTime	Change date.
`ChangeSequence` Nullable	long	Change sequence.
`InvSorderlineRef` Nullable	int	Value of `Orderlines.InvSorderlineRef`.
`BundleOrderlineRef` Nullable	int	Reference to the bundle order line.
`Refunded` Nullable	string	Value of `Orderlines.Refunded`.
`DIcheckbox` Nullable	string	Value of `Orderlines.DIcheckbox`.
`OriginalOrderlineRef` Nullable	int	Reference to the original order line.
`oItemSpecialNote` Nullable	string	Special note for the item.
`DiscountValue` Nullable	decimal	Discount value.
`DiscountPerc` Nullable	decimal	Discount percentage.
`BookAccountNr` Nullable	string	Booking account number.
`SerialNr` Nullable	string	Serial number.
`UpdateBackToHal` Nullable	string	Value of `Orderlines.UpdateBackToHal`.
`AlreadyOrdered` Nullable	string	Value of `Orderlines.AlreadyOrdered`.
`FullPrice` Nullable	decimal	Full price.
`DF_type` Nullable	string	Direct fulfillment type.
`DFShipDate` Nullable	dateTime	Direct fulfillment shipping date.
`DFShipmentTrackingID` Nullable	string	Direct fulfillment tracking ID.
`NLBCustomerEmailed` Nullable	string	Value of `Orderlines.NLBCustomerEmailed`.
`PriceWeightUnits_Price` Nullable	decimal	Value of `Orderlines.PriceWeightUnits_Price`.
`MeasuredWeight` Nullable	decimal	Measured weight.
`OrderDateStartOfDay` Nullable	dateTime	Order date truncated to start of day.
`PickListNrUsed` Nullable	string	Pick list number used.
`InvoiceNrExternal` Nullable	string	External invoice number.
`Order_Id` Nullable	int	Value of `Orderlines.Order_Id`.
`ItemOriginalFullPrice` Nullable	decimal	Original full price of the item.
`Price_Net` Nullable	decimal	Net price.
`Price_OrderCurr_Set`	int	Always `0`; marked for removal.
`ShipCost_OrderCurr_Set`	int	Always `0`; marked for removal.
`VATPercent` Nullable	decimal	VAT percentage.
`VATsRef` Nullable	int	VAT reference.
`BatchNumber` Nullable	string	Batch number (Charge).
`BestBeforeDate` Nullable	dateTime	Best-before date.
`RealSorderlineRef` Nullable	int	From `Orderline_EstimatedDelivery`; `NULL` if no entry exists.
`EstimatedDelivery` Nullable	datetime dateTime	Estimated delivery date from `Orderline_EstimatedDelivery`; `NULL` if no entry exists.

Response example

<SqlRowSet1>
  <row>
    <OrderlineKey>184523</OrderlineKey>
    <OrderNr>90211</OrderNr>
    <CustRef>4711</CustRef>
    <ArtNr>AB-1001</ArtNr>
    <ArtName>T-Shirt blau M</ArtName>
    <Qty>2</Qty>
    <Price>19.9900</Price>
    <Status>AU</Status>
    <OrderDate>2024-05-01T10:15:00</OrderDate>
    <OrderCurrency>EUR</OrderCurrency>
    <OrderExchRate>1.0000</OrderExchRate>
    <ShipCost>4.9000</ShipCost>
    <OrderNrExternal>SHOP-100234</OrderNrExternal>
    <LocationID>001</LocationID>
    <CreateDate>2024-05-01T10:15:03</CreateDate>
    <UpdateDate>2024-05-01T11:02:40</UpdateDate>
    <Price_OrderCurr_Set>0</Price_OrderCurr_Set>
    <ShipCost_OrderCurr_Set>0</ShipCost_OrderCurr_Set>
    <EstimatedDelivery>2024-05-03T00:00:00</EstimatedDelivery>
  </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:pixiReadOrderlines>
      <!-- <OrderlineKey>0</OrderlineKey> -->
      <!-- <CreateDateFrom>2024-01-01T00:00:00</CreateDateFrom> -->
      <!-- <CreateDateTo>2024-01-31T23:59:59</CreateDateTo> -->
      <!-- <UpdateDateFrom>2024-05-01T00:00:00</UpdateDateFrom> -->
      <!-- <UpdateDateTo>2024-05-02T00:00:00</UpdateDateTo> -->
      <!-- <CreateAndUpdateDateFrom>2024-05-01T00:00:00</CreateAndUpdateDateFrom> -->
      <!-- <CreateAndUpdateDateTo>2024-05-02T00:00:00</CreateAndUpdateDateTo> -->
      <!-- <RowCount>500</RowCount> -->
    </ns1:pixiReadOrderlines>
  </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:pixiReadOrderlines>
      <!-- <OrderlineKey>0</OrderlineKey> -->
      <!-- <CreateDateFrom>2024-01-01T00:00:00</CreateDateFrom> -->
      <!-- <CreateDateTo>2024-01-31T23:59:59</CreateDateTo> -->
      <!-- <UpdateDateFrom>2024-05-01T00:00:00</UpdateDateFrom> -->
      <!-- <UpdateDateTo>2024-05-02T00:00:00</UpdateDateTo> -->
      <!-- <CreateAndUpdateDateFrom>2024-05-01T00:00:00</CreateAndUpdateDateFrom> -->
      <!-- <CreateAndUpdateDateTo>2024-05-02T00:00:00</CreateAndUpdateDateTo> -->
      <!-- <RowCount>500</RowCount> -->
    </ns1:pixiReadOrderlines>
  </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' => '0',
    // 'CreateDateFrom' => '2024-01-01T00:00:00',
    // 'CreateDateTo' => '2024-01-31T23:59:59',
    // 'UpdateDateFrom' => '2024-05-01T00:00:00',
    // 'UpdateDateTo' => '2024-05-02T00:00:00',
    // 'CreateAndUpdateDateFrom' => '2024-05-01T00:00:00',
    // 'CreateAndUpdateDateTo' => '2024-05-02T00:00:00',
    // 'RowCount' => '500',
];

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

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

SQL object:: dbo.pipiReadOrderlines