Stock Read

pixiGetMinStockChanges

Returns items whose recommended minimum stock or minimum order quantity, based on recent sales, differs from the current setting by at least a given ratio.

Calculates disposition recommendations per item for one stock location and returns only items where the change is significant. The result is sorted and paged.

Input normalisation

0 for HistoryDays, SecurityDays, ShowChangesAbovePercentage and Rows is replaced by the default value. ConfigtypeKey = 0 and empty strings for the text and XML parameters are treated as NULL.
If Location is empty, the first location with ShowLocation = 1 is used.
SortColumn and SortDirection are converted to lowercase.

Sales source (Pos / Shops)

Only Shops set: sales from order lines only.
Only Pos set: sales from closed POS receipts only.
Both or none set: order lines plus POS sales. The content of Pos is never read. Shops is parsed, but the shop list is not used to filter the data.

Item filters The filters are applied in this order and build a list of item keys:

Categories (/CATS/CAT/CatId, mapped via TontrArten to items.ConfigType).
Suppliers (/SUPPLIERS/SUP/SupplNr), intersected with the previous list.
Tags (/TAGS/TAG with TAGKEY and TAGTYPE): a = item must have all these tags, o = item must have at least one, n = item must not have the tag. Matching items are added to the list.
ConfigtypeKey (enabled items with this ConfigType), intersected with the list if it is not empty.
SupplNR, intersected with the list if it is not empty.

Disabled items are then removed. If no filter is given, all enabled items are evaluated.

Calculation per item

MinStockQty / MinOrderQty: current values from ItemLocationSpecific for Location.
Quantity: sum of LocationQuantity over the locations in Locations (default: all locations with ShowLocation = 1).
DailyQty: quantity sold in the last HistoryDays days at Location (order lines with status AUS, POS receipts with status CLO) divided by HistoryDays. DailyQtyBefore: the same for the previous period of equal length. Trend is +, - or a blank.
RecommendedMinStockQty = FLOOR(DailyQty × (delivery days + SecurityDays)); delivery days default to 7 if the supplier has none.
RecommendedMinOrderQty = FLOOR(DailyQty × DesiredStockTurnOver); a result of 1 is set to 0. Negative recommendations are returned as 0.
ChangeMinStockQty / ChangeMinOrderQty: relative change (recommended − current) / current; if the current value is 0 and a recommendation exists, 1.
StockTurnover = FLOOR(Quantity / DailyQty), 0 without sales.
NumOfOrders: number of distinct orders / POS receipts in the period over the Locations list.

Selection, sorting and paging Only items with ABS(ChangeMinStockQty) or ABS(ChangeMinOrderQty) ≥ ShowChangesAbovePercentage are kept. They are numbered (RowNr) by SortColumn / SortDirection, then by ItemName ascending. Rows with RowNr > Start, RowNr ≤ Start + Rows and ItemKey > ItemKeyStart are returned, together with TotalRows (all matching items) and NrOfRecordsLeft (rows after Start with ItemKey > ItemKeyStart).

Notes

Read-only; all permanent tables are read WITH (NOLOCK), so uncommitted data can appear. The call computes several aggregates per item over Orderlines and POSitem2/POSheader2 and can be slow on large databases; use filters.
Bug: in the default mode (order lines plus POS) ChangeMinOrderQty is always NULL because the code checks the POS-only case twice. Then only ChangeMinStockQty decides whether an item is returned.
Items without an ItemLocationSpecific row for Location, or without any row in the Locations list, are not returned.
The previous period excludes the day exactly HistoryDays days ago.
Tags adds items to the list instead of intersecting it with Categories/Suppliers. A filter with only n tags returns no items as soon as any item carries one of these tags.
If Categories or Suppliers match no item and no Tags, ConfigtypeKey or SupplNR is given, the filter is ignored and all enabled items are evaluated.
SortColumn is lowercased and compared with mixed-case names; sorting works only with a case-insensitive database collation. Unknown values sort by ItemName.
DesiredStockTurnOver = 0 is not replaced by the default and leads to recommended order quantities of 0.
Invalid XML in the XML parameters causes a SQL error from sp_xml_preparedocument.
To apply a recommendation, use pixiSetItemBuyingInfo.

pixiSetItemBuyingInfo

Tables used

Location · Read Shops · Read TontrArten · Read items · Read TagMatch · Read Suppliers · Read ItemLocationSpecific · Read Orderlines · Read POSitem2 · Read POSheader2 · Read

Parameter	Type	Default	Description
`HistoryDays`	int	180	Length of the sales period in days used to calculate the daily quantity. The previous period of the same length is used for `DailyQtyBefore`. `0` means default. Example: `90`
`SecurityDays`	int	7	Safety days added to the supplier delivery days when calculating the recommended minimum stock. `0` means default. Example: `7`
`DesiredStockTurnOver`	int	30	Number of days of sales one order should cover; used for the recommended minimum order quantity. Example: `30`
`ShowChangesAbovePercentage`	float decimal	0.1	Minimum relative change (e.g. `0.1` = 10 %) of minimum stock or minimum order quantity for an item to be returned. `0` means default. Example: `0.2`
`SupplNR`	varchar(4) string	NULL	Restricts the result to items of this supplier number. Example: `LIE1`
`Rows`	int	100	Page size: maximum number of rows returned. `0` means default. Example: `50`
`ItemKeyStart`	int	0	Only rows with `ItemKey` greater than this value are returned. Example: `0`
`ConfigtypeKey`	int	NULL	Restricts the result to items with this `ConfigType`. `0` is treated as `NULL`. Example: `2`
`Tags`	varchar(max) xml	NULL	XML list of tag filters `/TAGS/TAG` with `TAGKEY` and `TAGTYPE` (`a` = must have all, `o` = must have one, `n` = must not have). Example: `<TAGS> <TAG> <TAGKEY>12</TAGKEY> <TAGTYPE>a</TAGTYPE> </TAG> <TAG> <TAGKEY>15</TAGKEY> <TAGTYPE>o</TAGTYPE> </TAG> <TAG> <TAGKEY>20</TAGKEY> <TAGTYPE>n</TAGTYPE> </TAG> </TAGS>`
`SortColumn`	varchar(50) string	itemname	Column used for sorting and `RowNr`. Case is ignored. Unknown values sort by `ItemName`. Allowed values `itemname` – Item name `eanupc` – EAN/UPC `dailyqty` – Daily quantity `quantity` – Stock quantity `minstockqty` – Current minimum stock `recommendedminstockqty` – Recommended minimum stock `changeminstockqty` – Relative change of minimum stock `minorderqty` – Current minimum order quantity `recommendedminorderqty` – Recommended minimum order quantity `changeminorderqty` – Relative change of minimum order quantity `defaultdeliverydays` – Delivery days `supplname` – Supplier name `supplnr` – Supplier number `itemkey` – Item key `trend` – Sales trend `numoforders` – Number of orders Example: `changeminstockqty`
`SortDirection`	varchar(1) string	a	Sort direction. Case is ignored. Allowed values `a` – Ascending `d` – Descending Example: `d`
`Start`	int	0	Paging offset: rows with `RowNr` greater than this value are returned. Example: `0`
`Suppliers`	varchar(max) xml	NULL	XML list of supplier numbers `/SUPPLIERS/SUP/SupplNr`; only items of these suppliers are evaluated. Example: `<SUPPLIERS> <SUP> <SupplNr>LIE1</SupplNr> </SUP> <SUP> <SupplNr>LIE2</SupplNr> </SUP> </SUPPLIERS>`
`Categories`	varchar(max) xml	NULL	XML list of category keys `/CATS/CAT/CatId` (`TontrArtKey`); only items whose `ConfigType` matches are evaluated. Example: `<CATS> <CAT> <CatId>3</CatId> </CAT> <CAT> <CatId>5</CatId> </CAT> </CATS>`
`Locations`	varchar(max) xml	NULL	XML list of location IDs `/LOCATIONS/LOC/LocationID` used for `Quantity` and `NumOfOrders`. Default: all locations with `ShowLocation = 1`. Example: `<LOCATIONS> <LOC> <LocationID>001</LocationID> </LOC> <LOC> <LocationID>002</LocationID> </LOC> </LOCATIONS>`
`Shops`	varchar(max) xml	NULL	XML list `/SHOPS/SHOP/ShopID`. Only its presence matters: together with `Pos` it selects the sales source (see description). The shop IDs do not filter the data. Example: `<SHOPS> <SHOP> <ShopID>WEB</ShopID> </SHOP> </SHOPS>`
`Pos`	varchar(max) string	NULL	Flag for POS sales: any non-empty value counts as set; the content is not read. Together with `Shops` it selects the sales source. Example: `1`
`Location`	varchar(max) string	NULL	Location ID for the current minimum values and the sales figures. Default: first location with `ShowLocation = 1`. Example: `001`

Result set 1 · Always (may be empty)

One page of items with significant changes to minimum stock or minimum order quantity, including paging totals.

Column	Type	Description
`ItemKey`	int	Item key
`EANUPC` Nullable	string	EAN/UPC of the item
`ItemName` Nullable	string	Item name
`DailyQty` Nullable	numeric decimal	Average quantity sold per day in the current period
`DailyQtyBefore` Nullable	numeric decimal	Average quantity sold per day in the previous period
`Trend`	varchar(1) string	`+` rising, `-` falling, blank unchanged
`MinStockQty` Nullable	int	Current minimum stock at `Location`
`RecommendedMinStockQty` Nullable	numeric decimal	Recommended minimum stock (not negative)
`MinOrderQty` Nullable	int	Current minimum order quantity at `Location`
`RecommendedMinOrderQty` Nullable	numeric decimal	Recommended minimum order quantity (not negative; 1 becomes 0)
`ChangeMinStockQty` Nullable	numeric decimal	Relative change between recommended and current minimum stock
`ChangeMinOrderQty` Nullable	numeric decimal	Relative change between recommended and current minimum order quantity; `NULL` in combined mode (see notes)
`Quantity` Nullable	decimal	Stock summed over the `Locations` list
`StockTurnover` Nullable	numeric decimal	Days of stock coverage (`Quantity` / `DailyQty`), 0 without sales
`SupplNr` Nullable	varchar(4) string	Supplier number of the item
`SupplName` Nullable	string	Supplier name
`DefaultDeliveryDays`	int	Supplier delivery days, 7 if not set
`NumOfOrders` Nullable	int	Number of distinct orders / POS receipts in the current period
`RowNr`	bigint long	Position in the sorted result
`TotalRows`	int	Total number of matching items
`NrOfRecordsLeft`	int	Number of matching rows after `Start` with `ItemKey` > `ItemKeyStart`

Response example

<SqlRowSet1>
  <row>
    <ItemKey>1052</ItemKey>
    <EANUPC>4006381333931</EANUPC>
    <ItemName>Kugelschreiber blau</ItemName>
    <DailyQty>2.50000000000000</DailyQty>
    <DailyQtyBefore>1.80000000000000</DailyQtyBefore>
    <Trend>+</Trend>
    <MinStockQty>20</MinStockQty>
    <RecommendedMinStockQty>35</RecommendedMinStockQty>
    <MinOrderQty>50</MinOrderQty>
    <RecommendedMinOrderQty>75</RecommendedMinOrderQty>
    <ChangeMinStockQty>0.750000000000</ChangeMinStockQty>
    <Quantity>48</Quantity>
    <StockTurnover>19</StockTurnover>
    <SupplNr>LIE1</SupplNr>
    <SupplName>Bürobedarf GmbH</SupplName>
    <DefaultDeliveryDays>7</DefaultDeliveryDays>
    <NumOfOrders>312</NumOfOrders>
    <RowNr>1</RowNr>
    <TotalRows>27</TotalRows>
    <NrOfRecordsLeft>27</NrOfRecordsLeft>
  </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:pixiGetMinStockChanges>
      <!-- <HistoryDays>90</HistoryDays> -->
      <!-- <SecurityDays>7</SecurityDays> -->
      <!-- <DesiredStockTurnOver>30</DesiredStockTurnOver> -->
      <!-- <ShowChangesAbovePercentage>0.2</ShowChangesAbovePercentage> -->
      <!-- <SupplNR>LIE1</SupplNR> -->
      <!-- <Rows>50</Rows> -->
      <!-- <ItemKeyStart>0</ItemKeyStart> -->
      <!-- <ConfigtypeKey>2</ConfigtypeKey> -->
      <!-- <Tags><![CDATA[<TAGS><TAG><TAGKEY>12</TAGKEY><TAGTYPE>a</TAGTYPE></TAG><TAG><TAGKEY>15</TAGKEY><TAGTYPE>o</TAGTYPE></TAG><TAG><TAGKEY>20</TAGKEY><TAGTYPE>n</TAGTYPE></TAG></TAGS>]]></Tags> -->
      <!-- <SortColumn>changeminstockqty</SortColumn> -->
      <!-- <SortDirection>d</SortDirection> -->
      <!-- <Start>0</Start> -->
      <!-- <Suppliers><![CDATA[<SUPPLIERS><SUP><SupplNr>LIE1</SupplNr></SUP><SUP><SupplNr>LIE2</SupplNr></SUP></SUPPLIERS>]]></Suppliers> -->
      <!-- <Categories><![CDATA[<CATS><CAT><CatId>3</CatId></CAT><CAT><CatId>5</CatId></CAT></CATS>]]></Categories> -->
      <!-- <Locations><![CDATA[<LOCATIONS><LOC><LocationID>001</LocationID></LOC><LOC><LocationID>002</LocationID></LOC></LOCATIONS>]]></Locations> -->
      <!-- <Shops><![CDATA[<SHOPS><SHOP><ShopID>WEB</ShopID></SHOP></SHOPS>]]></Shops> -->
      <!-- <Pos>1</Pos> -->
      <!-- <Location>001</Location> -->
    </ns1:pixiGetMinStockChanges>
  </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:pixiGetMinStockChanges>
      <!-- <HistoryDays>90</HistoryDays> -->
      <!-- <SecurityDays>7</SecurityDays> -->
      <!-- <DesiredStockTurnOver>30</DesiredStockTurnOver> -->
      <!-- <ShowChangesAbovePercentage>0.2</ShowChangesAbovePercentage> -->
      <!-- <SupplNR>LIE1</SupplNR> -->
      <!-- <Rows>50</Rows> -->
      <!-- <ItemKeyStart>0</ItemKeyStart> -->
      <!-- <ConfigtypeKey>2</ConfigtypeKey> -->
      <!-- <Tags><![CDATA[<TAGS><TAG><TAGKEY>12</TAGKEY><TAGTYPE>a</TAGTYPE></TAG><TAG><TAGKEY>15</TAGKEY><TAGTYPE>o</TAGTYPE></TAG><TAG><TAGKEY>20</TAGKEY><TAGTYPE>n</TAGTYPE></TAG></TAGS>]]></Tags> -->
      <!-- <SortColumn>changeminstockqty</SortColumn> -->
      <!-- <SortDirection>d</SortDirection> -->
      <!-- <Start>0</Start> -->
      <!-- <Suppliers><![CDATA[<SUPPLIERS><SUP><SupplNr>LIE1</SupplNr></SUP><SUP><SupplNr>LIE2</SupplNr></SUP></SUPPLIERS>]]></Suppliers> -->
      <!-- <Categories><![CDATA[<CATS><CAT><CatId>3</CatId></CAT><CAT><CatId>5</CatId></CAT></CATS>]]></Categories> -->
      <!-- <Locations><![CDATA[<LOCATIONS><LOC><LocationID>001</LocationID></LOC><LOC><LocationID>002</LocationID></LOC></LOCATIONS>]]></Locations> -->
      <!-- <Shops><![CDATA[<SHOPS><SHOP><ShopID>WEB</ShopID></SHOP></SHOPS>]]></Shops> -->
      <!-- <Pos>1</Pos> -->
      <!-- <Location>001</Location> -->
    </ns1:pixiGetMinStockChanges>
  </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 = [
    // 'HistoryDays' => '90',
    // 'SecurityDays' => '7',
    // 'DesiredStockTurnOver' => '30',
    // 'ShowChangesAbovePercentage' => '0.2',
    // 'SupplNR' => 'LIE1',
    // 'Rows' => '50',
    // 'ItemKeyStart' => '0',
    // 'ConfigtypeKey' => '2',
    // 'Tags' => '<TAGS><TAG><TAGKEY>12</TAGKEY><TAGTYPE>a</TAGTYPE></TAG><TAG><TAGKEY>15</TAGKEY><TAGTYPE>o</TAGTYPE></TAG><TAG><TAGKEY>20</TAGKEY><TAGTYPE>n</TAGTYPE></TAG></TAGS>',
    // 'SortColumn' => 'changeminstockqty',
    // 'SortDirection' => 'd',
    // 'Start' => '0',
    // 'Suppliers' => '<SUPPLIERS><SUP><SupplNr>LIE1</SupplNr></SUP><SUP><SupplNr>LIE2</SupplNr></SUP></SUPPLIERS>',
    // 'Categories' => '<CATS><CAT><CatId>3</CatId></CAT><CAT><CatId>5</CatId></CAT></CATS>',
    // 'Locations' => '<LOCATIONS><LOC><LocationID>001</LocationID></LOC><LOC><LocationID>002</LocationID></LOC></LOCATIONS>',
    // 'Shops' => '<SHOPS><SHOP><ShopID>WEB</ShopID></SHOP></SHOPS>',
    // 'Pos' => '1',
    // 'Location' => '001',
];

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

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

SQL object:: dbo.pipiGetMinStockChanges

pixiGetMinStockChanges

Notes

Related calls

Tables used

Result set 1 · Always (may be empty)

Response example