Stock Read

pixiReportInventoryItemsToRefillMinStock

Returns a pick list of items to move from a source location to a target location, with the quantity per source bin, computed by one of three refill modes.

The call passes all parameters unchanged to the internal report procedure Report_InventoryItemsToRefillMinStock and returns its result.

Step 1 – candidate items

An item is a candidate only if:

FromLocID and ToLocID are different,
the item has an ItemLocationSpecific record for both FromLocID and ToLocID,
the item is assigned to at least one bin (InvBinItem/InvBin) at FromLocID,
the calculated missing quantity is greater than 0.

Open order quantities are summed from Orderlines of the item at an order location. The order location is ToLocID for Type 1 and 3 and FromLocID otherwise. Statuses ANG, BES and BOR count as open customer orders, HAL counts as orders on hold. If the setting InventoryItemsToRefill_NoHAL is 1, on-hold quantities are ignored in the calculations.

The available stock at the source is the location quantity at FromLocID, minus the minimum stock of FromLocID (only if MinStock = 1 and Type = 2), minus the open customer order quantity (only if the order location is FromLocID), minus the on-hold quantity (unless the setting excludes it).

The missing quantity at the additional location is minimum stock minus location quantity at AdditionalLocID, or 0 if it is not below minimum.

The missing quantity depends on Type:

1: demand = open customer orders at ToLocID + on-hold quantity + minimum stock of ToLocID (only if MinStock = 1). If the demand fits into the available stock, the target is missing quantity at the additional location + demand, otherwise the available stock. The result is the target minus the current stock at ToLocID, never below 0.
2: the missing quantity at the additional location, limited to the available stock at the source.
3: only if the minimum stock at ToLocID is 0, the stock at AdditionalLocID is 0, the stock at FromLocID is greater than 0 and there are open customer orders at ToLocID. The result is the smaller value of open customer orders and stock at FromLocID.

Step 2 – distribution over bins

For each candidate, the item's InvBinItem records in bins at FromLocID are numbered by InvBinItemKey. Only the first n records are used, where n is the missing quantity. The rows are grouped per source bin. In the output, MissingQty is the number of these records in that bin. This equals a piece count if each bin record stands for one piece. One bin at ToLocID is added as the target bin, if there is one.

Sort order

If SortBy = 1, rows are sorted by the sort number of the target bin, otherwise by the sort number of the source bin. Within that, rows are sorted by ItemName.

Notes

The header comment of the wrapper describes it as calling the 1SS Report for billing purposes.
Type defaults to NULL in the wrapper. With NULL or any value other than 1, 2 or 3, the missing quantity is NULL and the call returns no rows. Always pass Type.
The missing quantity at the additional location comes from an outer join. If AdditionalLocID is NULL or the item has no ItemLocationSpecific record there, the value is NULL, not 0:
- Type 2 and Type 3 then return no row for the item.
- Type 1 only returns a row if the demand exceeds the available stock.
The target bin is taken with TOP 1 without an ORDER BY. For items with several bins at ToLocID, the bin shown is not deterministic.
The value of the setting InventoryItemsToRefill_NoHAL is read at runtime via fPIXI_GetSetting; that function is not attached.
All reads use NOLOCK, so results can include uncommitted data.
The call only reads data. It uses a temporary table internally.

Tables used

Items · Read Orderlines · Read ItemLocationSpecific · Read InvBinItem · Read InvBin · Read

Parameter	Type	Default	Description
`FromLocID` Required	varchar(3) string	–	Source location from which stock is taken. The item must have bins and an `ItemLocationSpecific` record here. Example: `002`
`MinStock`	bit boolean	1	Controls whether minimum stock is considered. For `Type` 1, the minimum stock of `ToLocID` is added to the demand. For `Type` 2, the minimum stock of `FromLocID` is kept back from the available stock. No effect for `Type` 3. Allowed values `1` – Consider minimum stock `0` – Ignore minimum stock Example: `1`
`ToLocID` Required	varchar(3) string	–	Target location to be refilled. Must differ from `FromLocID`, otherwise no rows are returned. The item must have an `ItemLocationSpecific` record here. Example: `001`
`AdditionalLocID`	varchar(3) string	NULL	Additional location whose shortfall below minimum stock is included (`Type` 1 and 2). For `Type` 3, its stock must be 0. Example: `003`
`SortBy`	varchar(1) string	NULL	Sort order of the result. The value is also returned in the `SortBy` column. Allowed values `1` – Sort by sort number of the target bin at `ToLocID`, then item name `(other or NULL)` – Sort by sort number of the source bin at `FromLocID`, then item name Example: `1`
`Type`	int	NULL	Calculation mode for the missing quantity. Without a valid value, no rows are returned. Allowed values `1` – Open customer orders (and on-hold orders) at `ToLocID`, plus optional minimum stock of `ToLocID`, plus shortfall at `AdditionalLocID`; limited to available source stock, minus current stock at `ToLocID` `2` – Fill the shortfall below minimum stock at `AdditionalLocID`, limited to available source stock `3` – Pick for open customer orders at `ToLocID` when `ToLocID` has no minimum stock and `AdditionalLocID` has no stock; limited to stock at `FromLocID` Example: `1`

Result set 1 · Always; empty if no item qualifies, if `FromLocID` = `ToLocID` or if `Type` is not 1, 2 or 3

One row per item and source bin with the quantity to move from that bin.

Column	Type	Description
`FromLocIDbinName`	string	Name of the source bin at `FromLocID` (`InvBin.BinName`)
`FromLocIDbinSortNum` Nullable	int	Sort number of the source bin
`ToLocIDbinName` Nullable	string	Name of a bin of the item at `ToLocID`; NULL if the item has no bin there
`ToLocIDbinSortNum` Nullable	int	Sort number of the target bin
`EanUpc` Nullable	string	EAN/UPC of the item
`ItemName` Nullable	string	Item name
`ToLocQty`	int	Current location quantity at `ToLocID` (0 if NULL)
`FromLocQty` Nullable	int	Current location quantity at `FromLocID`
`MinStockQty` Nullable	int	Minimum stock of `ToLocID` for `Type` 1, otherwise of `FromLocID`
`SupplPrice` Nullable	decimal	Supplier price of the item (`Items.SupplPrice`)
`OpenCustOrdersQty`	int	Quantity in order lines with status `ANG`, `BES` or `BOR` at the order location
`OrdersOnHoldQty`	int	Quantity in order lines with status `HAL` at the order location, regardless of the setting
`ParFromLocID`	varchar(3) string	Echo of parameter `FromLocID`
`ParToLocID`	varchar(3) string	Echo of parameter `ToLocID`
`SortBy` Nullable	varchar(1) string	Echo of parameter `SortBy`
`MissingQty`	int	Number of bin records of the item in this source bin that are used to cover the missing quantity
`FromBinqty` Nullable	int	Total quantity of the item in this source bin (sum of `BinItemQTY`)
`PicLinkSmall` Nullable	string	Link to the small item picture
`PicLinkLarge` Nullable	string	Link to the large item picture

Response example

<SqlRowSet1>
  <row>
    <FromLocIDbinName>A-01-03</FromLocIDbinName>
    <FromLocIDbinSortNum>103</FromLocIDbinSortNum>
    <ToLocIDbinName>S-12-01</ToLocIDbinName>
    <ToLocIDbinSortNum>1201</ToLocIDbinSortNum>
    <EanUpc>4012345678901</EanUpc>
    <ItemName>Trinkflasche 750 ml</ItemName>
    <ToLocQty>2</ToLocQty>
    <FromLocQty>40</FromLocQty>
    <MinStockQty>5</MinStockQty>
    <SupplPrice>3.20</SupplPrice>
    <OpenCustOrdersQty>4</OpenCustOrdersQty>
    <OrdersOnHoldQty>0</OrdersOnHoldQty>
    <ParFromLocID>002</ParFromLocID>
    <ParToLocID>001</ParToLocID>
    <SortBy>1</SortBy>
    <MissingQty>7</MissingQty>
    <FromBinqty>12</FromBinqty>
    <PicLinkSmall>https://shop.example.com/img/s/4711.jpg</PicLinkSmall>
    <PicLinkLarge>https://shop.example.com/img/l/4711.jpg</PicLinkLarge>
  </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:pixiReportInventoryItemsToRefillMinStock>
      <FromLocID>002</FromLocID>
      <!-- <MinStock>1</MinStock> -->
      <ToLocID>001</ToLocID>
      <!-- <AdditionalLocID>003</AdditionalLocID> -->
      <!-- <SortBy>1</SortBy> -->
      <!-- <Type>1</Type> -->
    </ns1:pixiReportInventoryItemsToRefillMinStock>
  </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:pixiReportInventoryItemsToRefillMinStock>
      <FromLocID>002</FromLocID>
      <!-- <MinStock>1</MinStock> -->
      <ToLocID>001</ToLocID>
      <!-- <AdditionalLocID>003</AdditionalLocID> -->
      <!-- <SortBy>1</SortBy> -->
      <!-- <Type>1</Type> -->
    </ns1:pixiReportInventoryItemsToRefillMinStock>
  </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 = [
    'FromLocID' => '002',
    // 'MinStock' => '1',
    'ToLocID' => '001',
    // 'AdditionalLocID' => '003',
    // 'SortBy' => '1',
    // 'Type' => '1',
];

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

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

SQL object:: dbo.pipiReportInventoryItemsToRefillMinStock

pixiReportInventoryItemsToRefillMinStock

Notes

Tables used

Result set 1 · Always; empty if no item qualifies, if FromLocID = ToLocID or if Type is not 1, 2 or 3

Response example

Result set 1 · Always; empty if no item qualifies, if `FromLocID` = `ToLocID` or if `Type` is not 1, 2 or 3