Invoices Read Deprecated

pixiGetInkassoBookings

Returns invoices handed over to debt collection (Inkasso) in a date range, with customer address and amounts; only supported on the pixi_GCH databases.

This call is customer-specific and only works when the Pixi database is named pixi_GCH or pixi_GCH_test. On all other databases it returns one status row with Status = Info and the message that the call is no longer supported.

On the supported databases the call runs the internal procedure Pixi_CA_GetInkassoBookings and returns its rows:

The booking event ID for manual Inkasso bookings is read from the setting CA_Inkasso_ManualBookingID (default INU).
All customer account bookings (CustomerAccount) with this event ID and an EventDate between FromDate and ToDate (both inclusive) are selected.
Through CustomerAccountMatch the matched bookings are found, in both directions, and the invoices those bookings refer to are determined.
One row per invoice is returned with the customer (Customers) and the customer address (Addresses), sorted by invoice date (InvDate) ascending.

The row layout is a fixed export format for a debt collection agency:

Fixed values: KundenNr = 975503, RecordCode = 0, CompanyType = 99, Land = 001, Adresszusatz and Rechnungsgrund empty.
Sprachcode is D if the address belongs to shop GCH, otherwise F.
Geschlecht is m for salutation Herr, w for Frau, otherwise f.
Dates (Geburtsdatum, Rechnungsdatum) are formatted as dd.mm.yyyy.
Amounts (BetragCHF, Mahnkosten, BetragCHFtotal) are returned as text with a dot as decimal separator. BetragCHF is the amount of the matched Inkasso booking, BetragCHFtotal the invoice total, Mahnkosten the reminder costs (0 if none).

Notes

Outside the databases pixi_GCH / pixi_GCH_test the call is effectively retired: it always returns the Info row and never queries data.
Before the export, the helper util.TableDrop is called for the legacy table tmp.pipiGetInkassoBookings_Output; the data itself is collected in a temporary table that is dropped at the end. The helper procedures are not attached.
The event ID depends on the setting CA_Inkasso_ManualBookingID, read at runtime via dbo.SettingGet.
The customer account tables are read WITH (NOLOCK), so uncommitted bookings may appear.
EventDate BETWEEN FromDate AND ToDate is compared on datetime: a ToDate without time (midnight) excludes bookings later on that day.
Geburtsjahr is 0 if no birth date is stored; Geburtsdatum is then empty.
BetragCHF can be empty (NULL) if no matching Inkasso booking amount is found. The amount lookups use scalar subqueries; if an invoice has several customer account entries, SQL Server may raise a 'subquery returned more than 1 value' error.

Tables used

Invoices · Read Customers · Read Addresses · Read CustomerAccount · Read CustomerAccountMatch · Read

Parameter	Type	Default	Description
`FromDate` Required	datetime dateTime	–	Start of the period (inclusive) for the `EventDate` of the Inkasso bookings. Example: `2024-01-01T00:00:00`
`ToDate` Required	datetime dateTime	–	End of the period (inclusive) for the `EventDate` of the Inkasso bookings. Include the time, e.g. 23:59:59, to cover the whole last day. Example: `2024-01-31T23:59:59`

Result set 1 · If the database is `pixi_GCH` or `pixi_GCH_test`

One row per invoice handed over to debt collection in the period, ordered by invoice date.

Column	Type	Description
`KundenNr`	varchar(6) string	Fixed client number at the collection agency, always `975503`.
`RecordCode`	varchar(1) string	Fixed record code, always `0`.
`KundenRefNr` Nullable	varchar(60) string	External customer number (`CustomerNrExternal`).
`Name1` Nullable	varchar(60) string	Name of the customer address.
`Name2`	varchar(60) string	Second name line, empty if not set.
`CompanyType`	varchar(2) string	Fixed value `99`.
`Adresszusatz`	varchar(1) string	Always empty.
`Strasse` Nullable	varchar(100) string	Street.
`Hausnummer` Nullable	varchar(12) string	House number.
`Postleitzahl` Nullable	varchar(10) string	ZIP code.
`Ort` Nullable	varchar(50) string	City.
`Land`	varchar(3) string	Fixed country code `001`.
`Sprachcode`	varchar(1) string	`D` if the address belongs to shop `GCH`, otherwise `F`.
`Geschlecht`	varchar(1) string	`m` for salutation `Herr`, `w` for `Frau`, otherwise `f`.
`Geburtsdatum`	varchar(30) string	Birth date as `dd.mm.yyyy`, empty if unknown.
`Geburtsjahr`	int	Birth year, `0` if no birth date is stored.
`Telefon` Nullable	varchar(60) string	Phone number.
`Telefax`	varchar(60) string	Fax number, empty if not set.
`E_Mail` Nullable	varchar(60) string	E-mail address.
`Mobile`	varchar(60) string	Second phone number (`Phone2`), empty if not set.
`Rechnungsdatum` Nullable	varchar(30) string	Invoice date as `dd.mm.yyyy`.
`Rechnungsnummer`	varchar(20) string	Invoice number.
`BetragCHF` Nullable	varchar(8000) string	Amount of the matched Inkasso booking as text with dot as decimal separator; empty if none is found.
`Rechnungsgrund`	varchar(1) string	Always empty.
`Mahnkosten` Nullable	varchar(8000) string	Reminder costs of the invoice as text with dot as decimal separator, `0` if none.
`BetragCHFtotal` Nullable	varchar(8000) string	Invoice total as text with dot as decimal separator.

Result set 2 · If the database is not `pixi_GCH` or `pixi_GCH_test`

Single status row stating that the call is no longer supported.

Column	Type	Description
`Status`	varchar(4) string	Always `Info`.
`Message`	varchar(56) string	Notice that the call is no longer supported.

Response example

<SqlRowSet1>
  <row>
    <KundenNr>975503</KundenNr>
    <RecordCode>0</RecordCode>
    <KundenRefNr>K-104522</KundenRefNr>
    <Name1>Muster</Name1>
    <Name2>Hans</Name2>
    <CompanyType>99</CompanyType>
    <Adresszusatz/>
    <Strasse>Bahnhofstrasse</Strasse>
    <Hausnummer>12</Hausnummer>
    <Postleitzahl>8001</Postleitzahl>
    <Ort>Zürich</Ort>
    <Land>001</Land>
    <Sprachcode>D</Sprachcode>
    <Geschlecht>m</Geschlecht>
    <Geburtsdatum>14.03.1978</Geburtsdatum>
    <Geburtsjahr>1978</Geburtsjahr>
    <Telefon>044 123 45 67</Telefon>
    <Telefax/>
    <E_Mail>hans.muster@example.ch</E_Mail>
    <Mobile>079 123 45 67</Mobile>
    <Rechnungsdatum>05.10.2023</Rechnungsdatum>
    <Rechnungsnummer>RE2023104411</Rechnungsnummer>
    <BetragCHF>149.90</BetragCHF>
    <Rechnungsgrund/>
    <Mahnkosten>20.00</Mahnkosten>
    <BetragCHFtotal>149.90</BetragCHFtotal>
  </row>
</SqlRowSet1>

Status	Message	Cause
Info	API Call: pixiGetInkassoBookings is no longer supported.	Returned on every database other than `pixi_GCH` and `pixi_GCH_test`. The call is retired there and cannot be enabled by parameters.

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:pixiGetInkassoBookings>
      <FromDate>2024-01-01T00:00:00</FromDate>
      <ToDate>2024-01-31T23:59:59</ToDate>
    </ns1:pixiGetInkassoBookings>
  </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:pixiGetInkassoBookings>
      <FromDate>2024-01-01T00:00:00</FromDate>
      <ToDate>2024-01-31T23:59:59</ToDate>
    </ns1:pixiGetInkassoBookings>
  </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 = [
    'FromDate' => '2024-01-01T00:00:00',
    'ToDate' => '2024-01-31T23:59:59',
];

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

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

SQL object:: dbo.pipiGetInkassoBookings