Reports Read

pixiReport_StockValue_Hist

Returns the historical stock value per item on a given date, valued at historical supplier prices and net sales prices.

Wrapper around the internal report procedure Report_StockValue_Hist. It always runs with ShowDetails = 0. Supplier matching always uses the main supplier of the item (AllItemsForSupplier = 0).

Steps:

Reference date: ParamDate is moved to the end of that day. If it is omitted, the end of the current day is used.
Item filter: Empty strings for LocID, Category and SupplNr count as "no filter".
- SupplNr filters on the item's main supplier (Items.SupplNr).
- Category filters on Items.ConfigType.
Stock at the reference date: For each item, all quantity movements in InvBinItemLog up to the reference date are summed.
- With LocID, only bins of that location (InvBin.BinLocID) are counted.
- Movements on bins that were deleted after the reference date are also counted. These bins are found in InvBinHist with remark Deleted.
- Items whose resulting quantity is 0 are dropped.
Historical supplier prices: The stock quantities are passed to internal helper procedures. These determine historical purchase prices from supplier deliveries, so one item can appear in several rows, one per supplier delivery that makes up the stock.
Sales price:
- VK_Netto is the gross price Items.Price divided by (1 + VAT rate), rounded to 2 decimals.
- The VAT rate comes from the item's MwStSatz: 1 = high rate, 2 = reduced rate, any other value = 0. Rates are those of the default country.

The result is sorted by Artikelname, then SupplDelDate, then VAT rate.

Notes

All permanent tables are read WITH (NOLOCK), so the result can contain uncommitted data.
The historical supplier prices come from Report_SaveSupplierPrices and pixi_HistorySupplierPriceForItems. Their definitions are not attached. Whether they write to permanent tables, and exactly how SupplPriceHist, SupplPriceType and SupplPriceMessage are determined, cannot be seen here.
VAT rates are taken for the current date, not for ParamDate. Historical rate changes are therefore not reflected in VK_Netto.
Bestand is the total stock of the item on the reference date and is repeated on every delivery row of that item. Use SupplDelQty for per-row sums. Sum_VK_Netto and Sum_SupplPriceHist are already based on SupplDelQty.
par_Category and par_SupplNR contain -alle- when no filter is set. Otherwise they contain the category text and the supplier name of the item, not the number.
The report evaluates the whole bin movement log and can be slow on large databases.

Tables used

Items · Read AddItems · Read InvBinItemLog · Read InvBin · Read InvBinHist · Read TontrArten · Read Suppliers · Read

Parameter	Type	Default	Description
`ParamDate`	datetime dateTime	NULL	Reference date for the stock value. The end of that day is used. If omitted, the end of the current day is used. Example: `2024-12-31T00:00:00`
`LocID`	varchar(10) string	NULL	Location ID. Only stock on bins of this location is counted. Empty or omitted means all locations. Example: `001`
`Category`	varchar(10) string	NULL	Item category, compared with `Items.ConfigType`. Empty or omitted means all categories. Example: `1`
`SupplNr`	varchar(10) string	NULL	Supplier number. Only items whose main supplier (`Items.SupplNr`) matches are included. Empty or omitted means all suppliers. Example: `L0042`

Result set 1 · Always; one row per item with stock ≠ 0 on the reference date, possibly several rows per item (one per supplier delivery).

Historical stock value per item, valued with historical supplier prices and net sales prices.

Column	Type	Description
`ItemKey`	int	Internal item key.
`LieferantenArtikelnummer` Nullable	string	Supplier's item number (`Items.ItemNrSuppl`).
`Artikelnummer` Nullable	string	Internal item number (`Items.ItemNrInt`).
`Barcode` Nullable	string	EAN/UPC barcode.
`Artikelname` Nullable	string	Item name.
`Kategorie` Nullable	string	Category text from `TontrArten`.
`SupplNr` Nullable	string	Main supplier number of the item.
`SupplName` Nullable	string	Name of the main supplier.
`Bestand` Nullable	int	Total stock quantity of the item on the reference date.
`VK_Brutto` Nullable	decimal	Current gross sales price (`Items.Price`).
`VK_Netto` Nullable	decimal	Net sales price, gross price divided by (1 + VAT rate), rounded to 2 decimals.
`MwStSatz` Nullable	numeric decimal	VAT rate in percent.
`Sum_VK_Netto` Nullable	decimal	`SupplDelQty` × `VK_Netto`.
`SupplPrice` Nullable	decimal	Current supplier price of the item.
`SupplPriceHist` Nullable	money decimal	Historical supplier price determined for this row.
`Sum_SupplPriceHist` Nullable	money decimal	`SupplDelQty` × `SupplPriceHist`.
`SupplPriceType` Nullable	int	Type/source of the historical supplier price, set by the internal price procedure.
`SupplPriceMessage` Nullable	varchar(200) string	Message explaining how the historical price was determined.
`SupplDelQty` Nullable	int	Quantity covered by this supplier delivery; falls back to `Bestand` if no delivery was found.
`SupplDelNr` Nullable	int	Number of the supplier delivery.
`SupplDelDate` Nullable	datetime dateTime	Date of the supplier delivery.
`par_paramdate`	datetime dateTime	Reference date used (end of day).
`par_LocID`	varchar(10) string	Location filter used, or `-alle-`.
`par_Category` Nullable	string	`-alle-` without category filter, otherwise the item's category text.
`par_SupplNR` Nullable	string	`-alle-` without supplier filter, otherwise the supplier name.
`DatabaseCurrencyCode` Nullable	varchar(3) string	Currency code of the database.

Response example

<SqlRowSet1>
  <row>
    <ItemKey>1234</ItemKey>
    <LieferantenArtikelnummer>SP-100</LieferantenArtikelnummer>
    <Artikelnummer>A-1000</Artikelnummer>
    <Barcode>4012345678901</Barcode>
    <Artikelname>Beispielartikel</Artikelname>
    <Kategorie>Standard</Kategorie>
    <SupplNr>L0042</SupplNr>
    <SupplName>Muster GmbH</SupplName>
    <Bestand>15</Bestand>
    <VK_Brutto>23.8000</VK_Brutto>
    <VK_Netto>20.00</VK_Netto>
    <MwStSatz>19.000000000</MwStSatz>
    <Sum_VK_Netto>200.00</Sum_VK_Netto>
    <SupplPrice>9.5000</SupplPrice>
    <SupplPriceHist>9.2000</SupplPriceHist>
    <Sum_SupplPriceHist>92.0000</Sum_SupplPriceHist>
    <SupplPriceType>1</SupplPriceType>
    <SupplPriceMessage>Supplier delivery</SupplPriceMessage>
    <SupplDelQty>10</SupplDelQty>
    <SupplDelNr>5678</SupplDelNr>
    <SupplDelDate>2024-11-05T10:12:00</SupplDelDate>
    <par_paramdate>2024-12-31T23:59:59.997</par_paramdate>
    <par_LocID>001</par_LocID>
    <par_Category>-alle-</par_Category>
    <par_SupplNR>Muster GmbH</par_SupplNR>
    <DatabaseCurrencyCode>EUR</DatabaseCurrencyCode>
  </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:pixiReport_StockValue_Hist>
      <!-- <ParamDate>2024-12-31T00:00:00</ParamDate> -->
      <!-- <LocID>001</LocID> -->
      <!-- <Category>1</Category> -->
      <!-- <SupplNr>L0042</SupplNr> -->
    </ns1:pixiReport_StockValue_Hist>
  </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:pixiReport_StockValue_Hist>
      <!-- <ParamDate>2024-12-31T00:00:00</ParamDate> -->
      <!-- <LocID>001</LocID> -->
      <!-- <Category>1</Category> -->
      <!-- <SupplNr>L0042</SupplNr> -->
    </ns1:pixiReport_StockValue_Hist>
  </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 = [
    // 'ParamDate' => '2024-12-31T00:00:00',
    // 'LocID' => '001',
    // 'Category' => '1',
    // 'SupplNr' => 'L0042',
];

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

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

SQL object:: dbo.pipiReport_StockValue_Hist