Orderlines Read

pixiReadBundleOrderlines

Returns bundle order lines from `BundleOrderlines`, filtered by create and/or update date and paged by key.

Reads bundle order lines (the bundle header lines of orders) from the table BundleOrderlines, using keyset paging.

Selection of candidates. Only the first matching filter group is applied, in this priority:

CreateAndUpdateDateFrom / CreateAndUpdateDateTo: lines whose CreateDate or UpdateDate lies in the range.
CreateDateFrom / CreateDateTo: lines whose CreateDate lies in the range.
UpdateDateFrom / UpdateDateTo: lines whose UpdateDate lies in the range.
No date filter: all lines.

Within a date group, a missing From value defaults to two years before the current date, and a missing To value defaults to the current date and time. The ranges are inclusive (BETWEEN).

Paging. In every case only lines with BundleOrderlineKey > OrderlineKey are returned. OrderlineKey defaults to 0. At most RowCount lines are returned (default 1000), ordered by BundleOrderlineKey ascending. To read the next page, pass the highest OrderlineKey of the previous page.

The result is one result set with selected columns of BundleOrderlines, sorted by OrderlineKey ascending.

Notes

OrderlineKey is not an exact-match filter. Because a NULL value is replaced by 0 and RowCount by 1000 at the start, the code branch for reading a single key is never reached. To read exactly one line with key n, pass OrderlineKey = n-1 and RowCount = 1.
Passing RowCount as NULL is treated as 1000.
All reads use NOLOCK, so uncommitted data can be returned and rows may be missed or duplicated while data changes concurrently.
Date defaults use the server time (GETDATE()); without any From value, lines older than two years are not returned by the date filters.
Column data types are not visible in the code; they come from the table BundleOrderlines.

Tables used

BundleOrderlines · Read

Parameter	Type	Default	Description
`OrderlineKey`	int	NULL	Paging cursor: only lines with a key greater than this value are returned. NULL is treated as `0`. Example: `0`
`CreateDateFrom`	datetime dateTime	NULL	Start of the create date range. Used only if no create-and-update range is given. If only the end is set, defaults to two years ago. Example: `2024-01-01T00:00:00`
`CreateDateTo`	datetime dateTime	NULL	End of the create date range (inclusive). If only the start is set, defaults to the current date and time. Example: `2024-12-31T23:59:59`
`UpdateDateFrom`	datetime dateTime	NULL	Start of the update date range. Used only if neither a create-and-update nor a create date range is given. If only the end is set, defaults to two years ago. Example: `2024-06-01T00:00:00`
`UpdateDateTo`	datetime dateTime	NULL	End of the update date range (inclusive). If only the start is set, defaults to the current date and time. Example: `2024-06-30T23:59:59`
`CreateAndUpdateDateFrom`	datetime dateTime	NULL	Start of a range matched against `CreateDate` or `UpdateDate`. Takes priority over all other date filters. If only the end is set, defaults to two years ago. Example: `2024-06-01T00:00:00`
`CreateAndUpdateDateTo`	datetime dateTime	NULL	End of the combined create/update range (inclusive). If only the start is set, defaults to the current date and time. Example: `2024-06-30T23:59:59`
`RowCount`	int	1000	Maximum number of lines returned per call. NULL is treated as `1000`. Example: `500`

Result set 1 · Always (may be empty)

Bundle order lines matching the filters, sorted by OrderlineKey ascending.

Column	Type	Description
`OrderlineKey`	int	Key of the bundle order line (`BundleOrderlineKey`); use as paging cursor.
`OrderNr` Nullable	int	Order number.
`CustRef` Nullable	int	Reference to the customer.
`AddrRef` Nullable	int	Reference to the address.
`ShipAdrRef` Nullable	int	Reference to the shipping address.
`SupplInfo` Nullable	string	Supplier information.
`ArtNr` Nullable	string	Item number.
`ArtName` Nullable	string	Item name.
`Qty` Nullable	int	Quantity.
`Price` Nullable	decimal	Sales price.
`SupplPrice` Nullable	decimal	Supplier (purchase) price.
`Status` Nullable	string	Status of the line.
`Note` Nullable	string	Note.
`OrderMS` Nullable	string	Value of column `OrderMS`.
`ConfigType` Nullable	string	Configuration type.
`Source` Nullable	string	Source of the order line.
`OrderDate` Nullable	dateTime	Order date.
`TimeZone` Nullable	string	Time zone of the order date.
`OrderLineType` Nullable	string	Type of the order line.
`CreateDate` Nullable	dateTime	Creation date.
`CreateEmp` Nullable	string	Employee who created the line.
`UpdateDate` Nullable	dateTime	Date of the last change.
`UpdateEmp` Nullable	string	Employee who last changed the line.
`ItemRef` Nullable	int	Reference to the item.
`WaitDays` Nullable	int	Waiting days.
`TaxAmount` Nullable	decimal	Tax amount.
`TaxRate` Nullable	decimal	Tax rate.
`ShipCostPerItem` Nullable	decimal	Shipping cost per item.
`OrderNrExternal` Nullable	string	External order number (e.g. from the shop).
`ItemNrExternal` Nullable	string	External item number.
`LocationID` Nullable	string	Location (warehouse) ID.
`LastOrdLocationID` Nullable	string	Location ID of the last order.
`SupplGroup` Nullable	string	Supplier group.
`rowguid` Nullable	string	Row GUID.
`OrdShopID` Nullable	string	Shop ID of the order.
`VoucherID` Nullable	string	Voucher ID.
`oItemSpecialNote` Nullable	string	Special note for the item.
`DiscountValue` Nullable	decimal	Discount amount.
`DiscountPerc` Nullable	decimal	Discount percentage.
`FullPrice` Nullable	decimal	Full price before discount.
`DF_type` Nullable	string	Direct fulfillment type.
`Price_Net` Nullable	decimal	Net price.
`VATPercent` Nullable	decimal	VAT percentage.
`VATsRef` Nullable	int	Reference to the VAT record.

Response example

<SqlRowSet1>
  <row>
    <OrderlineKey>10452</OrderlineKey>
    <OrderNr>200315</OrderNr>
    <CustRef>8812</CustRef>
    <AddrRef>9120</AddrRef>
    <ShipAdrRef>9121</ShipAdrRef>
    <ArtNr>BUNDLE-001</ArtNr>
    <ArtName>Starter Set</ArtName>
    <Qty>1</Qty>
    <Price>49.90</Price>
    <Status>AUF</Status>
    <OrderDate>2024-06-12T10:15:00</OrderDate>
    <CreateDate>2024-06-12T10:15:03</CreateDate>
    <CreateEmp>API</CreateEmp>
    <UpdateDate>2024-06-12T11:02:44</UpdateDate>
    <UpdateEmp>API</UpdateEmp>
    <ItemRef>3301</ItemRef>
    <OrderNrExternal>SHOP-100045</OrderNrExternal>
    <LocationID>001</LocationID>
    <OrdShopID>SHOP1</OrdShopID>
    <Price_Net>41.93</Price_Net>
    <VATPercent>19.00</VATPercent>
  </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:pixiReadBundleOrderlines>
      <!-- <OrderlineKey>0</OrderlineKey> -->
      <!-- <CreateDateFrom>2024-01-01T00:00:00</CreateDateFrom> -->
      <!-- <CreateDateTo>2024-12-31T23:59:59</CreateDateTo> -->
      <!-- <UpdateDateFrom>2024-06-01T00:00:00</UpdateDateFrom> -->
      <!-- <UpdateDateTo>2024-06-30T23:59:59</UpdateDateTo> -->
      <!-- <CreateAndUpdateDateFrom>2024-06-01T00:00:00</CreateAndUpdateDateFrom> -->
      <!-- <CreateAndUpdateDateTo>2024-06-30T23:59:59</CreateAndUpdateDateTo> -->
      <!-- <RowCount>500</RowCount> -->
    </ns1:pixiReadBundleOrderlines>
  </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:pixiReadBundleOrderlines>
      <!-- <OrderlineKey>0</OrderlineKey> -->
      <!-- <CreateDateFrom>2024-01-01T00:00:00</CreateDateFrom> -->
      <!-- <CreateDateTo>2024-12-31T23:59:59</CreateDateTo> -->
      <!-- <UpdateDateFrom>2024-06-01T00:00:00</UpdateDateFrom> -->
      <!-- <UpdateDateTo>2024-06-30T23:59:59</UpdateDateTo> -->
      <!-- <CreateAndUpdateDateFrom>2024-06-01T00:00:00</CreateAndUpdateDateFrom> -->
      <!-- <CreateAndUpdateDateTo>2024-06-30T23:59:59</CreateAndUpdateDateTo> -->
      <!-- <RowCount>500</RowCount> -->
    </ns1:pixiReadBundleOrderlines>
  </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-12-31T23:59:59',
    // 'UpdateDateFrom' => '2024-06-01T00:00:00',
    // 'UpdateDateTo' => '2024-06-30T23:59:59',
    // 'CreateAndUpdateDateFrom' => '2024-06-01T00:00:00',
    // 'CreateAndUpdateDateTo' => '2024-06-30T23:59:59',
    // 'RowCount' => '500',
];

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

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

SQL object:: dbo.pipiReadBundleOrderlines