Invoices Read

pixiReportInvlines

Returns invoice lines within a date range, optionally filtered by shop, payment type, supplier, category, EAN and internal item number.

Reads invoice lines from the view PV_Invlines and adds the currency code of the order from the exchange rate (pixi.ExchRate) and currency (pixi.Currency) tables.

Steps:

The number of returned rows is limited to Rowcount (default 1000).
DateFrom is moved to the start of its day (00:00:00).
If DateTo is empty or NULL, the current date and time is used. Otherwise it is moved to the end of its day (23:59:59.997).
Empty strings in the optional filter parameters are treated as NULL, so the filter is not applied.
Only invoice lines whose invoice date lies between DateFrom and DateTo are returned. These optional filters are combined with AND:
- ShopID: ShopID LIKE @ShopID
- PaymentType: exact match on PaymentType
- SupplNr: the item must have an entry for this supplier in AddItems. Any supplier assigned to the item counts, not only the supplier stored on the invoice line.
- Category: the category text is looked up in TontrArten.TontrText. The matching TontrArt is compared with LIKE against the configtype of the line.
- EAN: EANUPC LIKE @EAN
- ItemNrInt: ItemNrInt LIKE @ItemNrInt

The code has no ORDER BY, so the order of the rows is not defined.

Notes

All tables are read WITH (NOLOCK). Uncommitted data can appear in the result.
The row limit uses SET ROWCOUNT. Rowcount = 0 removes the limit.
ShopID, EAN and ItemNrInt are compared with LIKE, so wildcards such as % and _ can be used.
If the Category text is not found in TontrArten, no rows are returned. If the text exists more than once, the subquery returns several values and SQL Server raises an error.
If DateTo is omitted, the range ends at the current time, not at the end of the current day.
Invoice lines without a matching exchange rate or currency are excluded by the inner joins.
InvDate is returned as a string in the format yyyy-mm-dd hh:mi:ss.
The header comment of the procedure is a generic template text and does not describe this call.
PV_Invlines is a view whose definition is not attached. The data types of most columns therefore cannot be derived.

Tables used

PV_Invlines · Read ExchRate · Read Currency · Read TontrArten · Read AddItems · Read

Parameter	Type	Default	Description
`Rowcount`	int	1000	Maximum number of rows returned; 0 means no limit. Example: `500`
`DateFrom` Required	datetime dateTime	–	Start of the invoice date range. The time is set to the start of the day. Example: `2024-03-01T00:00:00`
`DateTo`	datetime dateTime	NULL	End of the invoice date range, moved to the end of the day. If empty, the current date and time is used. Example: `2024-03-31T00:00:00`
`ShopID`	varchar(3) string	NULL	Filter on the shop ID, compared with LIKE. Empty means all shops. Example: `ABC`
`PaymentType`	char(1) string	NULL	Filter on the payment type of the invoice (exact match). Empty means all payment types. Example: `R`
`SupplNr`	varchar(4) string	NULL	Supplier number. Returns only lines of items that have this supplier assigned in `AddItems`. Example: `L001`
`Category`	varchar(50) string	NULL	Category text as stored in `TontrArten.TontrText`. It is translated to the category code and matched against the line's `configtype`. Example: `CD`
`EAN`	varchar(13) string	NULL	Filter on the EAN/UPC of the item, compared with LIKE. Example: `4006381333931`
`ItemNrInt`	varchar(50) string	NULL	Filter on the internal item number, compared with LIKE. Example: `ART-10045`

Result set 1 · Always

Invoice lines matching the filters, limited to Rowcount rows, in no defined order.

Column	Type	Description
`Itemkey` Nullable	int	Key of the item.
`InvoiceKey` Nullable	int	Key of the invoice.
`InvoiceNR` Nullable	string	Invoice number.
`InvDate` Nullable	varchar(30) string	Invoice date as text in the format `yyyy-mm-dd hh:mi:ss`.
`PaymentType` Nullable	string	Payment type of the invoice.
`orderlinekey` Nullable	int	Key of the related order line.
`ShopID` Nullable	string	Shop ID.
`SupplNR` Nullable	string	Supplier number stored on the invoice line.
`Supplier` Nullable	string	Supplier name.
`ItemName` Nullable	string	Item name.
`EANUPC` Nullable	string	EAN/UPC of the item.
`ItemNrInt` Nullable	string	Internal item number.
`ItemNrSuppl` Nullable	string	Supplier item number.
`Category` Nullable	string	Item category.
`ItemQty` Nullable	int	Invoiced quantity.
`SupplPrice` Nullable	decimal	Supplier purchase price.
`ItemPrice_Brutto` Nullable	decimal	Gross item price.
`ItemPrice_netto` Nullable	decimal	Net item price.
`ItemPrice_VAT` Nullable	decimal	VAT amount or VAT rate of the item price; the code does not show which.
`OrderCurrencyCode` Nullable	string	Currency code (`Currency.CodeA`) of the exchange rate linked to the line.

Response example

<SqlRowSet1>
  <row>
    <Itemkey>10045</Itemkey>
    <InvoiceKey>88231</InvoiceKey>
    <InvoiceNR>R2024-001234</InvoiceNR>
    <InvDate>2024-03-05 14:22:10</InvDate>
    <PaymentType>R</PaymentType>
    <orderlinekey>554120</orderlinekey>
    <ShopID>ABC</ShopID>
    <SupplNR>L001</SupplNR>
    <Supplier>Muster GmbH</Supplier>
    <ItemName>USB-Kabel 2m</ItemName>
    <EANUPC>4006381333931</EANUPC>
    <ItemNrInt>ART-10045</ItemNrInt>
    <ItemNrSuppl>MU-778</ItemNrSuppl>
    <Category>Zubehör</Category>
    <ItemQty>2</ItemQty>
    <SupplPrice>2.50</SupplPrice>
    <ItemPrice_Brutto>9.99</ItemPrice_Brutto>
    <ItemPrice_netto>8.39</ItemPrice_netto>
    <ItemPrice_VAT>19.00</ItemPrice_VAT>
    <OrderCurrencyCode>EUR</OrderCurrencyCode>
  </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:pixiReportInvlines>
      <!-- <Rowcount>500</Rowcount> -->
      <DateFrom>2024-03-01T00:00:00</DateFrom>
      <!-- <DateTo>2024-03-31T00:00:00</DateTo> -->
      <!-- <ShopID>ABC</ShopID> -->
      <!-- <PaymentType>R</PaymentType> -->
      <!-- <SupplNr>L001</SupplNr> -->
      <!-- <Category>CD</Category> -->
      <!-- <EAN>4006381333931</EAN> -->
      <!-- <ItemNrInt>ART-10045</ItemNrInt> -->
    </ns1:pixiReportInvlines>
  </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:pixiReportInvlines>
      <!-- <Rowcount>500</Rowcount> -->
      <DateFrom>2024-03-01T00:00:00</DateFrom>
      <!-- <DateTo>2024-03-31T00:00:00</DateTo> -->
      <!-- <ShopID>ABC</ShopID> -->
      <!-- <PaymentType>R</PaymentType> -->
      <!-- <SupplNr>L001</SupplNr> -->
      <!-- <Category>CD</Category> -->
      <!-- <EAN>4006381333931</EAN> -->
      <!-- <ItemNrInt>ART-10045</ItemNrInt> -->
    </ns1:pixiReportInvlines>
  </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 = [
    // 'Rowcount' => '500',
    'DateFrom' => '2024-03-01T00:00:00',
    // 'DateTo' => '2024-03-31T00:00:00',
    // 'ShopID' => 'ABC',
    // 'PaymentType' => 'R',
    // 'SupplNr' => 'L001',
    // 'Category' => 'CD',
    // 'EAN' => '4006381333931',
    // 'ItemNrInt' => 'ART-10045',
];

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

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

SQL object:: dbo.pipiReportInvlines