Reports Read

pixiReportStockSalesOverview

Returns a per-item overview of current stock, stock movements, invoiced sales and returns since a given date, with paging by item key.

Builds a stock and sales report for all items in Items.

For each item the call calculates:

Average supplier price (SupplPrice): the average SupplPrice from AddItems for the item's IntLinkNr1. Supplier MGS and empty or zero prices are ignored. If no price is found, the value is 0.
Current stock (StockQty): the sum of BinItemQty over all bin locations (Lagerplätze) in InvBinItem, 0 if the item has no bin entry.
Stock increases / decreases (StockInc, StockDec): the sum of positive or negative quantities in the bin item log InvBinItemLog with UpdateDate >= @StatDateFrom.
First put to stock (FirstPutToStock): the earliest log entry with a positive quantity. This value does not use @StatDateFrom.
Sold / returned quantity (ItemQtySold, ItemQtyReturned): the sum of positive or negative ItemQty in invoice lines (InvLines) with CreateDate >= @StatDateFrom.
Last sale date (LastSaleDate): the latest CreateDate of these invoice lines.

The category text comes from TontrArten and is matched by the item's ConfigType.

Filters:

@StatDateFrom: start of the statistics period. If it is NULL, 1970-01-01 is used, so all history is included.
@ItemKeyStart: only items with ItemKey >= @ItemKeyStart are returned. NULL means no lower limit.
@OnlyItemsOnStock = 1: only items with a current stock greater than 0 are returned.

The result is sorted by ItemKey ascending. SET ROWCOUNT @RowCount limits the number of rows. For paging, pass the last returned ItemKey + 1 as @ItemKeyStart in the next call.

Notes

All tables are read with NOLOCK. Values can therefore include uncommitted data.
The aggregations in the CTEs run over all items. The filters @ItemKeyStart and @OnlyItemsOnStock are applied only in the final SELECT. Depending on the optimizer, the call can be slow on large databases. In addition, the scalar function fPixi_GetAverageSupplPrice is evaluated per item.
StockDec and ItemQtyReturned are negative values or 0.
LastSaleDate is NULL if there are no invoice lines in the period. FirstPutToStock is NULL if the item was never put to stock.
@RowCount = 0 removes the row limit, because this is the behaviour of SET ROWCOUNT 0.

Tables used

Items · Read InvBinItem · Read InvBinItemLog · Read InvLines · Read TontrArten · Read AddItems · Read

Parameter	Type	Default	Description
`StatDateFrom`	datetime dateTime	NULL	Start of the statistics period for stock movements and invoice lines. If it is `NULL`, `1970-01-01` is used. Example: `2024-01-01T00:00:00`
`ItemKeyStart`	int	NULL	Returns only items with `ItemKey` greater than or equal to this value. Used for paging. `NULL` means no lower limit. Example: `1001`
`OnlyItemsOnStock`	bit boolean	0	Restricts the result to items with a current stock greater than 0. Allowed values `0` – All items `1` – Only items with stock > 0 Example: `1`
`RowCount`	int	1000	Maximum number of rows returned, applied via `SET ROWCOUNT`. `0` means no limit. Example: `500`

Result set 1 · Always

One row per item, sorted by ItemKey.

Column	Type	Description
`ItemKey`	int	Internal item key
`ItemNrInt` Nullable	string	Internal item number
`ItemNrSuppl` Nullable	string	Supplier item number
`SupplNr` Nullable	string	Supplier number of the item
`ItemName` Nullable	string	Item name
`Category` Nullable	string	Text from `TontrArten` for the item's `ConfigType`
`Price` Nullable	decimal	Item price from `Items`
`SupplPrice`	money decimal	Average supplier price from `AddItems` (supplier `MGS` excluded), `0` if no price is found
`StockQty`	int	Current stock summed over all bin locations
`StockInc`	int	Sum of stock increases since `@StatDateFrom`
`StockDec`	int	Sum of stock decreases since `@StatDateFrom` (negative or 0)
`ItemQtySold` Nullable	int	Invoiced quantity (positive invoice lines) since `@StatDateFrom`
`ItemQtyReturned` Nullable	int	Returned quantity (negative invoice lines) since `@StatDateFrom`, negative or 0
`FirstPutToStock` Nullable	datetime dateTime	Date of the first stock increase in the log, independent of `@StatDateFrom`
`LastSaleDate` Nullable	datetime dateTime	Creation date of the latest invoice line since `@StatDateFrom`

Response example

<SqlRowSet1>
  <row>
    <ItemKey>1001</ItemKey>
    <ItemNrInt>A-10045</ItemNrInt>
    <ItemNrSuppl>SUP-778</ItemNrSuppl>
    <SupplNr>LIEF01</SupplNr>
    <ItemName>Trekking Rucksack 40L</ItemName>
    <Category>Standardartikel</Category>
    <Price>89.9000</Price>
    <SupplPrice>41.5000</SupplPrice>
    <StockQty>23</StockQty>
    <StockInc>60</StockInc>
    <StockDec>-37</StockDec>
    <ItemQtySold>39</ItemQtySold>
    <ItemQtyReturned>-2</ItemQtyReturned>
    <FirstPutToStock>2021-03-15T10:22:00</FirstPutToStock>
    <LastSaleDate>2024-05-28T14:03:11</LastSaleDate>
  </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:pixiReportStockSalesOverview>
      <!-- <StatDateFrom>2024-01-01T00:00:00</StatDateFrom> -->
      <!-- <ItemKeyStart>1001</ItemKeyStart> -->
      <!-- <OnlyItemsOnStock>1</OnlyItemsOnStock> -->
      <!-- <RowCount>500</RowCount> -->
    </ns1:pixiReportStockSalesOverview>
  </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:pixiReportStockSalesOverview>
      <!-- <StatDateFrom>2024-01-01T00:00:00</StatDateFrom> -->
      <!-- <ItemKeyStart>1001</ItemKeyStart> -->
      <!-- <OnlyItemsOnStock>1</OnlyItemsOnStock> -->
      <!-- <RowCount>500</RowCount> -->
    </ns1:pixiReportStockSalesOverview>
  </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 = [
    // 'StatDateFrom' => '2024-01-01T00:00:00',
    // 'ItemKeyStart' => '1001',
    // 'OnlyItemsOnStock' => '1',
    // 'RowCount' => '500',
];

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

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

SQL object:: dbo.pipiReportStockSalesOverview