Pixi API documentation

Unofficial · by koempf24.de

Postman collection
Reports Read

pixiReportStockValueHistEKPrice

Returns all items with a given item tag together with stock quantity at a cut-off date, last known purchase price, invoice discounts and resulting stock value.

Builds a stock valuation report based on historical purchase (EK) prices for all items that carry a specific item tag.

Steps:

  1. Looks up the item tag @ItemTag (tag type 1) and checks that the shop @ShopID exists. If either is missing, the error Tag or Shop does not exist is raised (see notes, execution is not stopped).
  2. Selects all items linked to the tag. For each item it determines:
    • Anschaffungsdatum: the date of the first bin log entry with a positive quantity (first put to stock, not limited by the date range).
    • EK-Preis netto: the final price of the most recent supplier delivery line with a delivered quantity > 0 and a price > 0; if there is none, the item's supplier price is used.
  3. Calculates the stock quantity as the sum of all bin log movements of the item with an update date before @DateTo (across all locations and bins).
  4. Sums the discounts from invoice lines of invoices of shop @ShopID with an invoice date in the range @DateFrom (inclusive) to @DateTo (exclusive). The discount per line is quantity × discount value, converted to a net amount by dividing by the high or low VAT rate of the invoice depending on the line's VAT code (H/L).
    • If the setting MultiCurrencyEnabled is 1, the discount is additionally converted into the database currency using the exchange rate of the database currency's exchange rate source for the invoice's exchange date and currency.
  5. Returns one row per tagged item with price, quantity, total purchase value, discounts and the resulting total (EK Gesamt netto minus Bewertungsabschläge).

The result has no explicit sort order.

Notes

  • Error does not stop execution: RAISERROR with severity 16 is not followed by a RETURN. The caller receives the error message, but the procedure continues. With an unknown tag the result set is empty; with an unknown shop but a valid tag the items are still returned, only without discounts.
  • @ShopID is effectively required: NULL never matches an existing shop and triggers the error.
  • @DateTo and @DateFrom are effectively required: with @DateTo = NULL no stock is calculated and the price always falls back to the item's supplier price; with @DateFrom = NULL no discounts are found.
  • Purchase price pitfall: the date condition for the last delivery price compares the item's creation date with @DateTo, not the delivery date. The price is therefore the most recent delivery price overall (including deliveries after @DateTo), as long as the item was created before @DateTo.
  • Stock is not restricted to a shop or location; all bin log movements are summed.
  • dbCurrency contains the database currency code only if MultiCurrencyEnabled = 1; otherwise it is NULL and amounts are in the original invoice currency.
  • All reads use NOLOCK, so results may include uncommitted data.
  • Column names contain spaces; in the SOAP/DataSet XML they appear encoded, e.g. EK-Preis_x0020_netto.

Tables used

Settings · Read Currency · Read Tag · Read Shops · Read items · Read TagMatch · Read InvBinItemLog · Read SDelDetail · Read Invoices · Read Invlines · Read ExchRate · Read
SQL object:
dbo.pipiReportStockValueHistEKPrice
Updated at 2026-10-30