pixiGetUpdatedInvoices · Pixi API documentation

Use this call for delta synchronisation of invoices.

If Since is not passed, the start of the current day (00:00) is used. An empty PaymentType is treated like NULL.
Candidates are collected from three sources, each restricted to an InvoiceKey greater than OffSet:
- invoices whose CreateDate is later than Since,
- invoices whose UpdateDate is later than Since,
- invoice lines (Invlines) whose UpdateDate is later than Since; the invoice they belong to is the candidate.
The candidates are grouped per invoice. UpdateDate in the result is the latest of the matching timestamps.
If PaymentType is set, only invoices with this payment code are kept.
The list is sorted by InvoiceKey ascending and limited to Rows invoices (0 = no limit).
For every remaining invoice the call returns key, invoice number, change date, shop (CustomerOf of the customer) and payment code with payment text, sorted by InvoiceKey.

Paging: pass the highest InvoiceKey of the previous page as OffSet with the same Since to fetch the next page.

Notes

All tables are read WITH (NOLOCK); uncommitted changes may be visible.
The comparison with Since is strict (>); records changed exactly at Since are not returned.
UpdateDate only reflects timestamps later than Since, so it is the latest change within the requested window.
The Rows limit (via SET ROWCOUNT) is applied before the joins to Customers and Payments. Invoices without a matching customer or payment code are dropped afterwards, so a page can contain fewer than Rows rows even if more data exists. Always continue paging with the highest InvoiceKey returned until no rows come back.
Only the final select is wrapped in error handling; on an unexpected error a status row is returned and the procedure returns 3, otherwise 0.
Temporary tables are cleaned up with util.TempTableDrop (definition not attached).

Tables used

Invoices · Read Invlines · Read Customers · Read Payments · Read

Parameter	Type	Default	Description
`Since`	datetime dateTime	NULL	Only invoices created or changed (including their lines) after this point in time are returned. `NULL` means start of the current day. Example: `2024-05-01T00:00:00`
`Rows`	int	0	Maximum number of invoices per call (page size). `0` returns all matching invoices. Example: `500`
`PaymentType`	varchar(1) string	NULL	Optional payment code (`Invoices.Payment`). Only invoices with this payment type are returned. `NULL` or empty means all payment types. Example: `V`
`OffSet`	int	-1	Paging offset: only invoices with an `InvoiceKey` greater than this value are considered. Pass the highest `InvoiceKey` of the previous page. Example: `-1`

Result set 1 · On success

One row per updated invoice, sorted by InvoiceKey ascending.

Column	Type	Description
`InvoiceKey`	int	Internal key of the invoice.
`InvoiceNr` Nullable	string	Invoice number.
`UpdateDate` Nullable	datetime dateTime	Latest creation or change timestamp of the invoice or one of its lines after `Since`.
`ShopID` Nullable	string	Shop of the customer (`Customers.CustomerOf`).
`PaymentCode` Nullable	string	Payment code of the invoice.
`PaymentText` Nullable	string	Description of the payment type from `Payments`.

Result set 2 · On an unexpected error while reading the result

Status row describing the error; the procedure returns 3.

Column	Type	Description
`Status`	varchar(5) string	Always `Error`.
`Message` Nullable	nvarchar(4000) string	Error text including the SQL Server error message.

Response example

<SqlRowSet1>
  <row>
    <InvoiceKey>10452</InvoiceKey>
    <InvoiceNr>RE2024-010452</InvoiceNr>
    <UpdateDate>2024-05-02T09:14:33</UpdateDate>
    <ShopID>SHOP1</ShopID>
    <PaymentCode>V</PaymentCode>
    <PaymentText>Vorkasse</PaymentText>
  </row>
  <row>
    <InvoiceKey>10457</InvoiceKey>
    <InvoiceNr>RE2024-010457</InvoiceNr>
    <UpdateDate>2024-05-02T11:02:10</UpdateDate>
    <ShopID>SHOP1</ShopID>
    <PaymentCode>R</PaymentCode>
    <PaymentText>Rechnung</PaymentText>
  </row>
</SqlRowSet1>

Status	Message	Cause
Error	Unexpected error while getting the values. ({ERROR_MESSAGE})	An unexpected SQL error occurred while selecting the result rows or dropping the temporary tables. The original SQL Server message is included; check the data and retry.

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:pixiGetUpdatedInvoices>
      <!-- <Since>2024-05-01T00:00:00</Since> -->
      <!-- <Rows>500</Rows> -->
      <!-- <PaymentType>V</PaymentType> -->
      <!-- <OffSet>-1</OffSet> -->
    </ns1:pixiGetUpdatedInvoices>
  </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:pixiGetUpdatedInvoices>
      <!-- <Since>2024-05-01T00:00:00</Since> -->
      <!-- <Rows>500</Rows> -->
      <!-- <PaymentType>V</PaymentType> -->
      <!-- <OffSet>-1</OffSet> -->
    </ns1:pixiGetUpdatedInvoices>
  </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 = [
    // 'Since' => '2024-05-01T00:00:00',
    // 'Rows' => '500',
    // 'PaymentType' => 'V',
    // 'OffSet' => '-1',
];

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

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