Reports Read

pixiReport_StockValue

Returns the stock value report: stock per article and location at a given date, with gross/net sales price, purchase price, gross profit and totals.

Wrapper around the internal report procedure Report_StockValue. All parameters are passed through unchanged.

Steps

The key date is set to the end of the day of ParamDate. If ParamDate is omitted, the end of the current day is used.
The stock is rebuilt from the stock movement log InvBinItemLog. All movements with UpdateDate up to the key date are summed per location (LocID) and article. Only combinations with a non-zero sum are kept, so negative stock is included.
If LocID is given and not empty, only movements of that location are considered. Otherwise all locations are included.
Each stock row is joined to the article (Items). Rows whose article does not exist are dropped.
Per article, the latest supplier information valid at the key date is determined. It provides the sales price Price, the purchase price SupplPrice and the supplier number. The supplier name comes from Suppliers, and the category text comes from TontrArten via Items.ConfigType.
The VAT rate is determined for the default country and the key date, based on the article's MwStSatz:
- 1 = high rate
- 2 = low rate
- any other value = no VAT; the price is treated as net.
Filters:
- If SupplNr is given and not empty, only articles whose latest supplier is that supplier are returned.
- If Category is given and not empty, only articles with ConfigType = Category are returned.
One row per article and location is returned, sorted by the internal article key.

Calculated values

VK_Netto = gross price divided by (1 + VAT rate)
Rohertrag = VK_Netto − EK per unit
Sum_* = stock quantity × unit value

The columns par_* echo the effective filter values. Filters that are not set appear as -alle-.

Notes

Read-only. Most tables are read WITH (NOLOCK), so the result can contain uncommitted data. Items is read without a hint.
The stock is recalculated from the complete movement log up to the key date. This can be slow for large histories; use filters where possible.
Only the date part of ParamDate matters, because the time is always moved to the end of the day.
The location column of the internal stage table is varchar(3), although LocID accepts up to 10 characters.
When SupplNr is set, articles without supplier information are excluded. Without the filter they are returned with NULL prices.
Rohertrah (sic, spelled differently from Rohertrag) is the total gross profit. It is 0 for articles whose MwStSatz is neither 1 nor 2, whereas Rohertrag is still calculated for them.
The output column MwStSatz contains the VAT rate in percent, not the article's VAT code.
The helper functions fPixi_GetLatestSupplierInfo, fPixi_GetVATRate, fPixi_GetDefaultCountryCode, fPixi_GetDateEndOfDay and pixi.GetDatabaseCurrency are not attached. Their exact logic, for example how the "latest" supplier information is chosen, and the data types of the price columns are therefore not documented here.

Tables used

InvBinItemLog · Read Items · Read TontrArten · Read Suppliers · Read

Parameter	Type	Default	Description
`ParamDate`	datetime dateTime	NULL	Key date of the report. The stock and prices are evaluated as of the end of this day. NULL = today. Example: `2024-03-31T00:00:00`
`LocID`	varchar(10) string	NULL	Location (warehouse) filter. NULL or empty = all locations. Example: `001`
`Category`	varchar(10) string	NULL	Article category filter, compared with `Items.ConfigType`. NULL or empty = all categories. Example: `1`
`SupplNr`	varchar(10) string	NULL	Supplier filter, compared with the supplier of the latest supplier information at the key date. NULL or empty = all suppliers. Example: `L100`

Result set 1 · Always (may be empty)

One row per article and location with non-zero stock at the key date, sorted by internal article key.

Column	Type	Description
`LieferantenArtikelnummer` Nullable	string	Supplier article number (`Items.ItemNrSuppl`).
`Artikelnummer` Nullable	string	Internal article number (`Items.ItemNrInt`).
`Barcode` Nullable	string	EAN/UPC barcode of the article.
`Artikelname` Nullable	string	Article name.
`Kategorie` Nullable	string	Category text from `TontrArten` for the article's `ConfigType`.
`SupplName` Nullable	string	Name of the supplier from the latest supplier information.
`LocID`	varchar(3) string	Location (warehouse) ID.
`Bestand`	int	Stock quantity at the key date (sum of movements, can be negative).
`VK_Brutto` Nullable	decimal	Gross sales price per unit.
`VK_Netto` Nullable	decimal	Net sales price per unit (gross price without VAT).
`EK` Nullable	decimal	Purchase price per unit.
`Rohertrag` Nullable	decimal	Gross profit per unit (`VK_Netto` − `EK`).
`Sum_VK_Netto` Nullable	decimal	Stock × net sales price.
`Sum_VK_Brutto` Nullable	decimal	Stock × gross sales price.
`Sum_EK` Nullable	decimal	Stock × purchase price (stock value at purchase price).
`MwStSatz` Nullable	decimal	Applied VAT rate in percent (0 if the article has neither the high nor the low rate).
`Rohertrah` Nullable	decimal	Total gross profit (stock × (`VK_Netto` − `EK`)); 0 for articles without the high or low VAT rate.
`CurrencyCode` Nullable	varchar(3) string	Database currency code.
`par_paramdate`	datetime dateTime	Effective key date (end of day).
`par_LocID`	varchar(10) string	Location filter as passed, or `-alle-`.
`par_Category`	varchar(10) string	Category filter as passed, or `-alle-`.
`par_SupplNR`	varchar(10) string	Supplier filter as passed, or `-alle-`.

Response example

<SqlRowSet1>
  <row>
    <LieferantenArtikelnummer>SUP-4711</LieferantenArtikelnummer>
    <Artikelnummer>100234</Artikelnummer>
    <Barcode>4006381333931</Barcode>
    <Artikelname>Notizbuch A5 kariert</Artikelname>
    <Kategorie>Standard</Kategorie>
    <SupplName>Papierwaren GmbH</SupplName>
    <LocID>001</LocID>
    <Bestand>25</Bestand>
    <VK_Brutto>11.9000</VK_Brutto>
    <VK_Netto>10.0000</VK_Netto>
    <EK>4.5000</EK>
    <Rohertrag>5.5000</Rohertrag>
    <Sum_VK_Netto>250.0000</Sum_VK_Netto>
    <Sum_VK_Brutto>297.5000</Sum_VK_Brutto>
    <Sum_EK>112.5000</Sum_EK>
    <MwStSatz>19.00</MwStSatz>
    <Rohertrah>137.5000</Rohertrah>
    <CurrencyCode>EUR</CurrencyCode>
    <par_paramdate>2024-03-31T23:59:59.997</par_paramdate>
    <par_LocID>001</par_LocID>
    <par_Category>-alle-</par_Category>
    <par_SupplNR>-alle-</par_SupplNR>
  </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>
      <!-- <ParamDate>2024-03-31T00:00:00</ParamDate> -->
      <!-- <LocID>001</LocID> -->
      <!-- <Category>1</Category> -->
      <!-- <SupplNr>L100</SupplNr> -->
    </ns1:pixiReport_StockValue>
  </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>
      <!-- <ParamDate>2024-03-31T00:00:00</ParamDate> -->
      <!-- <LocID>001</LocID> -->
      <!-- <Category>1</Category> -->
      <!-- <SupplNr>L100</SupplNr> -->
    </ns1:pixiReport_StockValue>
  </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-03-31T00:00:00',
    // 'LocID' => '001',
    // 'Category' => '1',
    // 'SupplNr' => 'L100',
];

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

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

SQL object:: dbo.pipiReport_StockValue