pixiGetPosJournal

Returns the cash journal (Kassenbuch) of the Pixi POS module. The work is done by the internal procedure CR_POS_Kassenbuch. Its rows are buffered, and a reduced set of columns is returned.

Steps:

The common API envelope checks the call frequency. If the minimum interval configured for this function has not passed, only a status row with ResultCode 502 is returned and no journal data is read.
Defaults are applied:
- FromDate and ToDate default to today.
- FromDate is used as given, including its time.
- ToDate is extended to the end of its day.
POS session events in the range are selected. Events of type I are excluded.
- LocID limits the result to one location. NULL or empty means all locations.
- RegisterID limits the result to one register. '' or NULL means all registers.
Each event is joined with the POS invoice header, if one exists, and with a description of the event type:
- S = POS Start
- E = POS Stop
- P = Invoice
- D = Drawer Open
RegisterValue is the running total of AmountIn - AmountOut over all selected events with an EventTime up to and including the current one.
EventComment combines the event comment and the invoice comment, separated by a line break.

The internal procedure sorts by EventTime.

Notes

Call statistics: the API envelope writes the time of the call into API_STATISTICS. This is the only write access.
Reads: all reads of journal data use NOLOCK, so uncommitted data may appear.
Sort order: the final SELECT reads from a table variable without its own ORDER BY. The order by EventTime is therefore not strictly guaranteed.
Running total: RegisterValue is calculated over all selected events, not per register or per location. When several registers or locations are queried at once, the value mixes them. It also starts at 0 at FromDate; there is no opening balance from before the range.
End of range: ToDate is converted with CAST(... AS INTEGER). For datetime values this rounds to the nearest day, so a ToDate with a time of 12:00 or later extends the range to the end of the following day. Pass ToDate without a time.
VAT rates: events are inner-joined with VAT rates from fPixi_GetVATRate (not attached). If that function returns no rate for a location, the events of that location are missing from the result.
Always empty: CreatedBy and UpdatedBy are always NULL.
Unknown types: EventDescription is NULL for event types other than S, E, P and D.

Tables used

POSSession_event · Read POSSession · Read POSheader2 · Read API_STATISTICS · Write API_CONTROL · Read

Parameter	Type	Default	Description
`FromDate`	datetime dateTime	NULL	Start of the period (inclusive, time is respected). `NULL` means today 00:00. Example: `2024-05-01T00:00:00`
`ToDate`	datetime dateTime	NULL	End of the period. It is extended to the end of its day; times of 12:00 or later round to the next day. `NULL` means today. Example: `2024-05-31T00:00:00`
`LocID`	varchar(3) string	NULL	Location (Lager) of the POS session. `NULL` or empty returns all locations. Example: `001`
`RegisterID`	varchar(30) string	–	POS register ID. Empty or `NULL` returns all registers. Example: `KASSE01`
`ResultCode` OUTPUT	char(3) string	NULL	Result code of the API envelope. Allowed values `200` – OK, journal data is returned. `502` – Call frequency limit exceeded, no data returned. Example: `200`
`ResultText` OUTPUT	varchar(1024) string	NULL	Result text: `OK` or the frequency limit message. Example: `OK`

Result set 1 · If the call frequency check passes (`ResultCode` = `200`)

One row per POS session event in the period, except events of type I.

Column	Type	Description
`EventTime` Nullable	datetime dateTime	Time of the event.
`RegisterID` Nullable	varchar(30) string	POS register of the session.
`CreatedBy` Nullable	varchar(50) string	Always `NULL`.
`UpdatedBy` Nullable	varchar(50) string	Always `NULL`.
`EventType` Nullable	char(1) string	Event type: `S` start, `E` stop, `P` invoice, `D` drawer open, or another code.
`EventDescription` Nullable	varchar(100) string	English name of the event type (`POS Start`, `POS Stop`, `Invoice`, `Drawer Open`); `NULL` for other types.
`InvoiceNr` Nullable	varchar(20) string	Document number of the linked POS invoice.
`InvoiceAmount` Nullable	money decimal	Total of the linked POS invoice.
`EventComment` Nullable	varchar(max) string	Event comment and invoice comment, separated by a line break if both exist.
`AmountOut` Nullable	money decimal	Amount taken out of the register.
`VatHigh` Nullable	money decimal	VAT amount at the standard rate of the invoice.
`VatLow` Nullable	money decimal	VAT amount at the reduced rate of the invoice.
`AmountIn` Nullable	money decimal	Amount paid into the register.
`RegisterValue` Nullable	money decimal	Running total of `AmountIn - AmountOut` over all selected events up to this event time.

Result set 2 · If the call frequency limit is exceeded (`ResultCode` = `502`)

Status row from the API envelope; no journal data follows.

Column	Type	Description
`ResultCode`	char(3) string	Always `502`.
`ResultText`	varchar(1024) string	Message with the remaining wait time in milliseconds.

Response example

<SqlRowSet1>
  <row>
    <EventTime>2024-05-01T08:00:12</EventTime>
    <RegisterID>KASSE01</RegisterID>
    <EventType>S</EventType>
    <EventDescription>POS Start</EventDescription>
    <AmountOut>0.0000</AmountOut>
    <AmountIn>150.0000</AmountIn>
    <RegisterValue>150.0000</RegisterValue>
  </row>
  <row>
    <EventTime>2024-05-01T09:15:40</EventTime>
    <RegisterID>KASSE01</RegisterID>
    <EventType>P</EventType>
    <EventDescription>Invoice</EventDescription>
    <InvoiceNr>K2024-000123</InvoiceNr>
    <InvoiceAmount>39.9000</InvoiceAmount>
    <AmountOut>0.0000</AmountOut>
    <VatHigh>6.3700</VatHigh>
    <VatLow>0.0000</VatLow>
    <AmountIn>39.9000</AmountIn>
    <RegisterValue>189.9000</RegisterValue>
  </row>
</SqlRowSet1>

Status	Message	Cause
502	({WaitMsec}) Frequency quote exceeded. Next call to this function can be made in ca. {WaitMsec} milliseconds	The call was made before the minimum interval configured in `API_CONTROL` for this function had passed. Wait the given time and call again.

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:pixiGetPosJournal>
      <!-- <FromDate>2024-05-01T00:00:00</FromDate> -->
      <!-- <ToDate>2024-05-31T00:00:00</ToDate> -->
      <!-- <LocID>001</LocID> -->
      <!-- <RegisterID>KASSE01</RegisterID> -->
      <!-- <ResultCode>200</ResultCode> -->
      <!-- <ResultText>OK</ResultText> -->
    </ns1:pixiGetPosJournal>
  </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:pixiGetPosJournal>
      <!-- <FromDate>2024-05-01T00:00:00</FromDate> -->
      <!-- <ToDate>2024-05-31T00:00:00</ToDate> -->
      <!-- <LocID>001</LocID> -->
      <!-- <RegisterID>KASSE01</RegisterID> -->
      <!-- <ResultCode>200</ResultCode> -->
      <!-- <ResultText>OK</ResultText> -->
    </ns1:pixiGetPosJournal>
  </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-05-01T00:00:00',
    // 'ToDate' => '2024-05-31T00:00:00',
    // 'LocID' => '001',
    // 'RegisterID' => 'KASSE01',
    // 'ResultCode' => '200',
    // 'ResultText' => 'OK',
];

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

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

Notes

Tables used

Result set 1 · If the call frequency check passes (ResultCode = 200)

Result set 2 · If the call frequency limit is exceeded (ResultCode = 502)

Response example

Result set 1 · If the call frequency check passes (`ResultCode` = `200`)

Result set 2 · If the call frequency limit is exceeded (`ResultCode` = `502`)