pixiGetPOSSalesReport · Pixi API documentation

Builds a report of point-of-sale sales. According to the code comment, it was created for a specific customer (ERK).

Each row is one POS line from POSitem2. It is joined to its POS header (POSheader2) and to the item (items). The supplier (Suppliers) is joined optionally through the item's SupplNr, so SupplName can be empty.
LocID filters on the location of the POS header (PHlocID). An empty string or NULL means all locations.
DateFrom and DateTo filter on the POS header date PHdate with an inclusive BETWEEN. A value that is NULL or earlier than 1990-01-01 is replaced by the current date and time.
The report shows the list sales price of the item (VK_List) next to the price actually charged on the POS line (VK_Real). It also returns the purchase price of the supplier and the quantity.
Rows are sorted by POS header key in ascending order, so they appear in the order the POS receipts were created.

Notes

If DateFrom and DateTo are both omitted, both are set to the current time, which is only milliseconds apart. The result is then practically always empty, so always pass both dates.
BETWEEN includes both ends. If DateTo is passed as a date without a time (midnight), sales later on that day are not included. To cover the whole day, pass the next day or a time of 23:59:59.
All tables are read with NOLOCK, so uncommitted data can appear in the result.
There is no paging. Large date ranges can return many rows.

Tables used

POSitem2 · Read POSheader2 · Read items · Read Suppliers · Read

Parameter	Type	Default	Description
`LocID`	char(3) string	NULL	Location ID of the POS sales. An empty string or `NULL` returns sales of all locations. Example: `001`
`DateFrom`	datetime dateTime	NULL	Start of the period (inclusive), compared with the POS header date. `NULL` or a value before 1990-01-01 is replaced by the current date and time. Example: `2024-03-01T00:00:00`
`DateTo`	datetime dateTime	NULL	End of the period (inclusive), compared with the POS header date. `NULL` or a value before 1990-01-01 is replaced by the current date and time. Example: `2024-03-31T23:59:59`

Result set 1 · Always (may be empty)

One row per POS sales line in the period, sorted by POS header key in ascending order.

Column	Type	Description
`ItemName` Nullable	string	Item name.
`VK_List` Nullable	decimal	List sales price of the item (`items.Price`).
`VK_Real` Nullable	decimal	Price actually charged on the POS line (`PIendPrice`).
`SupplPrice` Nullable	decimal	Purchase price from the supplier, taken from the item.
`SupplName` Nullable	string	Supplier name. Empty if no supplier with the item's `SupplNr` exists.
`SupplNr` Nullable	string	Supplier number of the item.
`ItemNrInt` Nullable	string	Internal item number.
`ItemNrSuppl` Nullable	string	Supplier's item number.
`EANUPC` Nullable	string	EAN/UPC of the item.
`InvoiceDate` Nullable	dateTime	Date of the POS receipt (`PHdate`).
`Qty` Nullable	int	Quantity sold on the POS line (`PIqty`).

Response example

<SqlRowSet1>
  <row>
    <ItemName>Trekking Shoe Alpine 42</ItemName>
    <VK_List>129.90</VK_List>
    <VK_Real>119.90</VK_Real>
    <SupplPrice>62.50</SupplPrice>
    <SupplName>Outdoor Supply GmbH</SupplName>
    <SupplNr>OSG</SupplNr>
    <ItemNrInt>100245</ItemNrInt>
    <ItemNrSuppl>TS-ALP-42</ItemNrSuppl>
    <EANUPC>4012345678901</EANUPC>
    <InvoiceDate>2024-03-05T14:22:10</InvoiceDate>
    <Qty>1</Qty>
  </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:pixiGetPOSSalesReport>
      <!-- <LocID>001</LocID> -->
      <!-- <DateFrom>2024-03-01T00:00:00</DateFrom> -->
      <!-- <DateTo>2024-03-31T23:59:59</DateTo> -->
    </ns1:pixiGetPOSSalesReport>
  </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:pixiGetPOSSalesReport>
      <!-- <LocID>001</LocID> -->
      <!-- <DateFrom>2024-03-01T00:00:00</DateFrom> -->
      <!-- <DateTo>2024-03-31T23:59:59</DateTo> -->
    </ns1:pixiGetPOSSalesReport>
  </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 = [
    // 'LocID' => '001',
    // 'DateFrom' => '2024-03-01T00:00:00',
    // 'DateTo' => '2024-03-31T23:59:59',
];

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

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