Invoices Read

pixiReadInvlines

Returns invoice lines, either one line by key or a list filtered by creation and/or update date, with key-based paging.

Reads invoice lines from Invlines. Each line is joined to its invoice (Invoices) and to the invoice currency (pixi.Currency). Only one filter mode is used. The procedure checks the modes in this order:

Create or update date: CreateAndUpdateDateFrom or CreateAndUpdateDateTo is set. A line matches if CreateDate or UpdateDate is >= the from value, and CreateDate or UpdateDate is < the to value.
Create date: CreateDateFrom or CreateDateTo is set. Filters on CreateDate (from inclusive, to exclusive).
Update date: UpdateDateFrom or UpdateDateTo is set. Filters on UpdateDate (from inclusive, to exclusive).
Key range: RowCount is passed (any non-NULL value). Returns all lines with InvLineKey greater than InvlineKey.
Single key: none of the above applies. Returns the line whose InvLineKey equals InvlineKey.

In modes 1–4 only lines with InvLineKey > InvlineKey are returned, sorted by InvLineKey ascending. InvlineKey defaults to 0, so all keys qualify by default.

A non-zero RowCount limits the number of rows (SET ROWCOUNT). This enables paging: pass the last InvLineKey received as InvlineKey in the next call.

Notes

All reads use NOLOCK. Uncommitted data can be returned.
Lines are returned only if their invoice exists and the invoice's CurrencyId resolves to a currency (inner joins).
Calling without any parameters returns no rows: InvlineKey becomes 0 and the single-key mode looks for key 0.
Passing RowCount = 0 switches to key-range mode without a row limit. This returns all invoice lines above InvlineKey.
Date filter modes exclude each other. For example, when CreateAndUpdateDate* is set, the CreateDate* and UpdateDate* parameters are ignored.
In the combined date mode, from and to are checked independently against either date. A line can therefore match even if neither date lies within the range.
ItemPrice_OrderCurr and ShipCost_OrderCurr are copies of ItemPrice and ShipCost. Code comments mark them for removal, so new integrations should not rely on them.
The final fallback branch of the code (empty result) can never be reached, because InvlineKey is never NULL at that point.

Tables used

Invoices · Read Currency · Read Invlines · Read

Parameter	Type	Default	Description
`InvlineKey`	int	NULL	Invoice line key. NULL is treated as `0`. In single-key mode the line with exactly this key is returned. In all other modes only lines with a greater key are returned (paging cursor). Example: `0`
`CreateDateFrom`	datetime dateTime	NULL	Lower bound (inclusive) for `CreateDate`. Ignored if a `CreateAndUpdateDate*` parameter is set. Example: `2024-01-01T00:00:00`
`CreateDateTo`	datetime dateTime	NULL	Upper bound (exclusive) for `CreateDate`. Ignored if a `CreateAndUpdateDate*` parameter is set. Example: `2024-02-01T00:00:00`
`UpdateDateFrom`	datetime dateTime	NULL	Lower bound (inclusive) for `UpdateDate`. Used only if no `CreateAndUpdateDate` or `CreateDate` parameter is set. Example: `2024-01-01T00:00:00`
`UpdateDateTo`	datetime dateTime	NULL	Upper bound (exclusive) for `UpdateDate`. Used only if no `CreateAndUpdateDate` or `CreateDate` parameter is set. Example: `2024-02-01T00:00:00`
`CreateAndUpdateDateFrom`	datetime dateTime	NULL	Lower bound (inclusive): matches if `CreateDate` or `UpdateDate` is on or after this value. Has the highest priority among the date filters. Example: `2024-01-15T00:00:00`
`CreateAndUpdateDateTo`	datetime dateTime	NULL	Upper bound (exclusive): matches if `CreateDate` or `UpdateDate` is before this value. Example: `2024-01-16T00:00:00`
`RowCount`	int	NULL	Maximum number of rows to return. `0` or NULL means no limit. Any non-NULL value (including `0`) without a date filter switches to key-range mode. Example: `500`

Result set 1 · Always (may be empty)

Invoice lines matching the selected filter mode. Sorted by InvLineKey ascending, except in single-key mode.

Column	Type	Description
`InvLineKey`	int	Primary key of the invoice line.
`InvoiceRef`	int	Key of the invoice (`Invoices.InvoiceKey`).
`OrderLineRef` Nullable	int	Reference to the order line.
`CustRef` Nullable	int	Reference to the customer.
`AddrRef` Nullable	int	Reference to the address.
`ItemNr` Nullable	string	Item number.
`ItemQty` Nullable	int	Invoiced quantity.
`OrderQty` Nullable	int	Ordered quantity.
`ItemText` Nullable	string	Item text of the line.
`ItemPrice` Nullable	decimal	Item price.
`ItemDiscount` Nullable	decimal	Item discount.
`ItemVAT` Nullable	decimal	VAT rate of the item.
`CreateDate` Nullable	dateTime	Creation date of the line.
`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.
`ItemPrice_OrderCurr` Nullable	decimal	Copy of `ItemPrice`. Marked for removal in the code.
`ShipCost` Nullable	decimal	Shipping cost of the line.
`ShipCostPerItem` Nullable	decimal	Shipping cost per item.
`OrderCurrency` Nullable	string	Currency code (`pixi.Currency.CodeA`) of the invoice.
`ShipCost_OrderCurr` Nullable	decimal	Copy of `ShipCost`. Marked for removal in the code.
`ItemRef` Nullable	int	Reference to the item.
`CCPChargedYN` Nullable	string	Flag from column `CCPChargedYN` (charge status of the line).
`SerialNr` Nullable	string	Serial number.
`FullPrice` Nullable	decimal	Full price before discount.
`DiscountValue` Nullable	decimal	Discount amount.
`DiscountPerc` Nullable	decimal	Discount in percent.

Response example

<SqlRowSet1>
  <row>
    <InvLineKey>10234</InvLineKey>
    <InvoiceRef>5120</InvoiceRef>
    <OrderLineRef>88412</OrderLineRef>
    <CustRef>3307</CustRef>
    <AddrRef>4410</AddrRef>
    <ItemNr>ART-1001</ItemNr>
    <ItemQty>2</ItemQty>
    <OrderQty>2</OrderQty>
    <ItemText>T-Shirt blau, Gr. M</ItemText>
    <ItemPrice>19.99</ItemPrice>
    <ItemDiscount>0</ItemDiscount>
    <ItemVAT>19</ItemVAT>
    <CreateDate>2024-01-15T10:22:05</CreateDate>
    <CreateEmp>API</CreateEmp>
    <UpdateDate>2024-01-15T10:22:05</UpdateDate>
    <UpdateEmp>API</UpdateEmp>
    <ItemPrice_OrderCurr>19.99</ItemPrice_OrderCurr>
    <ShipCost>4.90</ShipCost>
    <ShipCostPerItem>0</ShipCostPerItem>
    <OrderCurrency>EUR</OrderCurrency>
    <ShipCost_OrderCurr>4.90</ShipCost_OrderCurr>
    <ItemRef>1201</ItemRef>
    <CCPChargedYN>N</CCPChargedYN>
    <SerialNr/>
    <FullPrice>19.99</FullPrice>
    <DiscountValue>0</DiscountValue>
    <DiscountPerc>0</DiscountPerc>
  </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:pixiReadInvlines>
      <!-- <InvlineKey>0</InvlineKey> -->
      <!-- <CreateDateFrom>2024-01-01T00:00:00</CreateDateFrom> -->
      <!-- <CreateDateTo>2024-02-01T00:00:00</CreateDateTo> -->
      <!-- <UpdateDateFrom>2024-01-01T00:00:00</UpdateDateFrom> -->
      <!-- <UpdateDateTo>2024-02-01T00:00:00</UpdateDateTo> -->
      <!-- <CreateAndUpdateDateFrom>2024-01-15T00:00:00</CreateAndUpdateDateFrom> -->
      <!-- <CreateAndUpdateDateTo>2024-01-16T00:00:00</CreateAndUpdateDateTo> -->
      <!-- <RowCount>500</RowCount> -->
    </ns1:pixiReadInvlines>
  </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:pixiReadInvlines>
      <!-- <InvlineKey>0</InvlineKey> -->
      <!-- <CreateDateFrom>2024-01-01T00:00:00</CreateDateFrom> -->
      <!-- <CreateDateTo>2024-02-01T00:00:00</CreateDateTo> -->
      <!-- <UpdateDateFrom>2024-01-01T00:00:00</UpdateDateFrom> -->
      <!-- <UpdateDateTo>2024-02-01T00:00:00</UpdateDateTo> -->
      <!-- <CreateAndUpdateDateFrom>2024-01-15T00:00:00</CreateAndUpdateDateFrom> -->
      <!-- <CreateAndUpdateDateTo>2024-01-16T00:00:00</CreateAndUpdateDateTo> -->
      <!-- <RowCount>500</RowCount> -->
    </ns1:pixiReadInvlines>
  </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 = [
    // 'InvlineKey' => '0',
    // 'CreateDateFrom' => '2024-01-01T00:00:00',
    // 'CreateDateTo' => '2024-02-01T00:00:00',
    // 'UpdateDateFrom' => '2024-01-01T00:00:00',
    // 'UpdateDateTo' => '2024-02-01T00:00:00',
    // 'CreateAndUpdateDateFrom' => '2024-01-15T00:00:00',
    // 'CreateAndUpdateDateTo' => '2024-01-16T00:00:00',
    // 'RowCount' => '500',
];

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

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

SQL object:: dbo.pipiReadInvlines