Reports Read

pixiReportGetStockItems

Returns a stock evaluation per item with stock quantity, average purchase price, stock value, stock age, last order date and sales quantities for three periods.

Builds a stock evaluation report for all items that have at least one bin entry in InvBinItem. According to the header comment it was made for stock evaluation and price recalculation for a specific customer (GCH).

Filter

If SupplNr is set, only items whose Items.SupplNr equals this value are included. If it is NULL (default), all items with bin entries are returned.
Items without any InvBinItem row are not returned. Items whose bin rows add up to 0 are still returned.

Calculated values per item

EK: average supplier price from fPixi_GetAverageSupplPrice. It averages AddItems.SupplPrice of all supplier records linked through the item's IntLinkNr1, ignoring NULL and 0 prices and the supplier MGS. If no such price exists, 0 is returned.
AnzahlArtikelAufLager: sum of BinItemQty over all bin locations of the item.
EKGesamt: stock quantity multiplied by EK.
AufLagerSeit / AufLagerSeitTage: earliest UpdateDate in InvBinItemLog with a positive quantity, and the number of days since then.
LetzteBestellung / LetzteBestellungTage: latest OrderDate of any order line for the item, and the number of days since then.
soldRange1, soldRange2, soldRange3: sum of Orderlines.Qty for order lines with an OrderDate within the last SoldRange1, SoldRange2 or SoldRange3 days, counted back from the current server time.

The result has no defined sort order and no paging.

Notes

All tables are read with NOLOCK, so the figures can include uncommitted data.
Sales quantities and the last order date count all order lines of the item, regardless of order or line status (for example cancelled lines are not excluded).
soldRange*, LetzteBestellung, AufLagerSeit and the day columns are NULL if there are no matching order lines or log entries.
The average purchase price always excludes the supplier MGS; this is hard-coded in fPixi_GetAverageSupplPrice. The function's fallback query is identical to the first query, so it effectively returns 0 if no price is found.
Each item triggers several correlated subqueries and a scalar function call. Without a SupplNr filter the call can run for a long time on large catalogues.

Tables used

Items · Read InvBinItem · Read InvBinItemLog · Read Orderlines · Read AddItems · Read

Parameter	Type	Default	Description
`SupplNr`	varchar(4) string	NULL	Supplier number (`Items.SupplNr`) to restrict the report to. `NULL` returns all items with bin entries. Example: `MGS`
`SoldRange1`	int	30	Number of days back from now for the sales sum in column `soldRange1`. Example: `30`
`SoldRange2`	int	90	Number of days back from now for the sales sum in column `soldRange2`. Example: `90`
`SoldRange3`	int	120	Number of days back from now for the sales sum in column `soldRange3`. Example: `180`

Result set 1 · Always; one row per item with at least one `InvBinItem` entry matching the supplier filter.

Stock evaluation per item.

Column	Type	Description
`ItemKey`	int	Internal item key.
`Artikelnummer` Nullable	string	Supplier item number (`Items.ItemNrSuppl`).
`EANUPC` Nullable	string	EAN/UPC of the item.
`Lieferant` Nullable	string	Supplier number of the item (`Items.SupplNr`).
`Artikelname` Nullable	string	Item name.
`VK` Nullable	decimal	Selling price (`Items.Price`).
`EK`	money decimal	Average supplier price over linked supplier records, excluding supplier `MGS` and zero prices; `0` if none.
`AnzahlArtikelAufLager` Nullable	int	Total stock quantity over all bin locations.
`EKGesamt` Nullable	money decimal	Stock value: stock quantity × `EK`.
`AufLagerSeit` Nullable	datetime dateTime	Earliest date the item was put to stock (first `InvBinItemLog` entry with positive quantity).
`AufLagerSeitTage` Nullable	int	Days since `AufLagerSeit`.
`LetzteBestellung` Nullable	datetime dateTime	Latest order date of any order line for the item.
`LetzteBestellungTage` Nullable	int	Days since `LetzteBestellung`.
`soldRange1` Nullable	int	Quantity ordered within the last `SoldRange1` days.
`soldRange2` Nullable	int	Quantity ordered within the last `SoldRange2` days.
`soldRange3` Nullable	int	Quantity ordered within the last `SoldRange3` days.

Response example

<SqlRowSet1>
  <row>
    <ItemKey>10452</ItemKey>
    <Artikelnummer>MG-4711</Artikelnummer>
    <EANUPC>4006381333931</EANUPC>
    <Lieferant>MGS</Lieferant>
    <Artikelname>Steckdosenleiste 6-fach</Artikelname>
    <VK>24.9900</VK>
    <EK>11.2500</EK>
    <AnzahlArtikelAufLager>38</AnzahlArtikelAufLager>
    <EKGesamt>427.5000</EKGesamt>
    <AufLagerSeit>2023-02-14T09:31:00</AufLagerSeit>
    <AufLagerSeitTage>412</AufLagerSeitTage>
    <LetzteBestellung>2024-03-28T16:05:12</LetzteBestellung>
    <LetzteBestellungTage>4</LetzteBestellungTage>
    <soldRange1>12</soldRange1>
    <soldRange2>31</soldRange2>
    <soldRange3>40</soldRange3>
  </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:pixiReportGetStockItems>
      <!-- <SupplNr>MGS</SupplNr> -->
      <!-- <SoldRange1>30</SoldRange1> -->
      <!-- <SoldRange2>90</SoldRange2> -->
      <!-- <SoldRange3>180</SoldRange3> -->
    </ns1:pixiReportGetStockItems>
  </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:pixiReportGetStockItems>
      <!-- <SupplNr>MGS</SupplNr> -->
      <!-- <SoldRange1>30</SoldRange1> -->
      <!-- <SoldRange2>90</SoldRange2> -->
      <!-- <SoldRange3>180</SoldRange3> -->
    </ns1:pixiReportGetStockItems>
  </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 = [
    // 'SupplNr' => 'MGS',
    // 'SoldRange1' => '30',
    // 'SoldRange2' => '90',
    // 'SoldRange3' => '180',
];

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

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

SQL object:: dbo.pipiReportGetStockItems

pixiReportGetStockItems

Notes

Tables used

Result set 1 · Always; one row per item with at least one InvBinItem entry matching the supplier filter.

Response example

Result set 1 · Always; one row per item with at least one `InvBinItem` entry matching the supplier filter.