Pixi API documentation

Unofficial · by koempf24.de

Postman collection
Stock Read

pixiGetItemStock

Returns available and physical stock, supplier price and open order line quantities for one or more items, optionally broken down by location.

Looks up items and returns their stock figures.

Item identification

  • Empty strings are treated as NULL; ItemKey = 0 is treated as NULL.
  • At least one of ItemKey, EAN, ItemNrSuppl, ItemNrInt or ItemXML is required, otherwise an error row is returned.
  • The single parameters are combined with OR against pixi.ItemCode: EAN matches code type 1 (barcode), ItemNrInt code type 2 (shop item number), ItemNrSuppl code type 4 (supplier item number), ItemKey matches the item id of any code row.
  • ItemXML adds further items: each /ITEMS/ITEM element can contain ITEMKEY (taken directly), ITEMNRINT, ITEMNRSUPPL or EAN (matched against items.ItemNrInt, items.ItemNrSuppl and items.EANUPC).
  • If no candidate is found, an error row is returned.

Stock calculation (per item from items)

  • AvailableStock = ItemQtyIBIExportStock_locations minus, for non-bundle items and when setting StockExportUsePhysicalStock is 0:
    • ItemQtyOpenOrderLines if reservations (ReservePrepaymentOrders) are disabled or ExportAvailableStock_WhenReservationsAreEnabled = 1,
    • otherwise ItemQtyOpenReservations if reservations are enabled,
    • otherwise nothing is subtracted.
  • PhysicalStock = stock outside special stock locations, PhysicalStockAllLocations = including special stock.
  • OnlyEnabledItems = 1 restricts the output to enabled items.

Excluding bin groups If ExcludeBinGroups (comma-separated list) is given, the quantities in bins (InvBin/InvBinItemLog) of these bin groups are summed per item and subtracted from AvailableStock, PhysicalStock and PhysicalStockAllLocations. For bundles containing such components, a derived quantity (minimum over components of the excluded quantity divided by the component quantity, rounded up) is subtracted as well.

Location breakdown With LocationStock = 1 two additional text columns are returned:

  • Locations: All:<qty>; <LocID>:<qty>; … with the total quantity and the quantity per location.
  • OpenOrderlineSumLocations: <LocID>:<qty>; …; All:<qty>; with open customer order quantities (ANG/FUT and recent HAL orders) per location and in total, computed by pixi.ItemInfoStockPerLocation.

One row is returned per found item; no explicit sort order is applied.

Notes

  • Read-only; all tables are read WITH (NOLOCK), so values may reflect uncommitted data.
  • On errors the procedure returns code 1 in addition to the status row.
  • ItemKey as a single parameter only matches if the item has at least one row in pixi.ItemCode. ITEMKEY values from ItemXML are not validated; unknown keys count as found but produce no output row, so the result can be empty without an error row.
  • An item found both via single parameters and via ItemXML can appear twice in the result.
  • EAN in ItemXML is read as varchar(13); longer values are truncated.
  • Passing OnlyEnabledItems explicitly as NULL yields no rows.
  • The bin group exclusion only affects items that have a shop item number (code type 2) and whose ItemNrInt equals that code.
  • The order of entries inside Locations and OpenOrderlineSumLocations is not guaranteed.
  • pixi.ItemInfoStockPerLocation is executed even when LocationStock is not 1; for many items this aggregates order lines and supplier orders and can be costly. Its result depends on the settings HalOrdersActiveDays and Include_FUT_StatusInOpenOrdersForSupplierOrdering.
  • Invalid XML in ItemXML causes an SQL error from sp_xml_preparedocument.

Tables used

ItemCode · Read items · Read ItemLocationSpecific · Read Location · Read InvBin · Read InvBinItemLog · Read BundleItems · Read Settings · Read
SQL object:
dbo.pipiGetItemStock
Updated at 2026-10-30