Stock Read

pixiInventoryItemsToRefill

Returns the Artikel that must be moved from a source location to a target location to cover minimum stock and open customer orders, with the source Lagerplätze to pick from.

Builds a replenishment (refill) list between two warehouse locations. The call passes all parameters to the internal procedure CR_InventoryItemsToRefill, which does the work.

Demand in the target location (ToLocID)

Open customer orders: sum of Orderlines.Qty per Artikel in ToLocID with status ANG, BES, BOR and HAL. HAL is left out when the setting InventoryItemsToRefill_NoHAL is 1.
Orders on hold: sum of Qty of order lines with status HAL in ToLocID. This value is returned for information only.
Stock per location comes from ItemLocationSpecific (LocationQuantity, MinStockQty, TargetStock).

Which Artikel are selected

The Artikel has an ItemLocationSpecific record for ToLocID.
FromLocID differs from ToLocID.
Positive minimum stock plus open customer order quantity is greater than the stock in ToLocID.
The Artikel is stored on at least one Lagerplatz (InvBin) of FromLocID.

Missing quantity

It is calculated as positive MinStockQty + open customer order quantity − stock in ToLocID − stock in AdditionalLocID (the last term only if AdditionalLocID is set).

Allocation to source bins

All bin entries (InvBinItem) of the Artikel in FromLocID are used. Entries that are referenced by a non-expired picklist item (PLitem) are skipped.
The entries are numbered oldest first (CreateDate). Only the first MissingQty entries are kept.
The result has one row per Artikel and source Lagerplatz. MissingQty is the number of entries taken from that bin. FromBinqty is the total quantity of the Artikel on that bin.
ToLocIDbinName/ToLocIDbinSortNum show one Lagerplatz of the Artikel in ToLocID, if there is one.

Sort order

With SortBy = '1' the rows are sorted by ToLocIDbinSortNum, otherwise by FromLocIDbinSortNum. Within that, rows are sorted by ItemName.

Notes

Read-only. All tables are read WITH (NOLOCK), so the result can contain uncommitted data.
The allocation counts bin entries (InvBinItem rows), not quantities. The MissingQty returned per bin is therefore the number of entries taken from that bin. Its sum over all bins of an Artikel can be lower than the calculated shortage if there are fewer entries.
The selection filter does not subtract the stock of AdditionalLocID, but the missing quantity does. If the additional location covers the shortage, the missing quantity is 0 or less and the Artikel does not appear.
ToLocIDbinName is taken with TOP 1 without an ORDER BY. If the Artikel has several Lagerplätze in ToLocID, which one is shown is not defined.
The setting is read through fpixi_GetSetting, whose definition is not attached. If it returns NULL instead of an empty value, HAL is also excluded.
FromLocQty and MinStockQty can be empty if no matching ItemLocationSpecific value exists.
If FromLocID equals ToLocID, the result is always empty.

Tables used

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

Parameter	Type	Default	Description
`FromLocID` Required	varchar(3) string	–	Source location (Lager) from whose Lagerplätze the goods are taken. Example: `001`
`ToLocID` Required	varchar(3) string	–	Target location to be refilled. Stock, minimum stock and open customer orders are evaluated for this location. Example: `002`
`AdditionalLocID`	varchar(3) string	NULL	Optional additional location whose stock is subtracted from the missing quantity. If NULL or empty, no additional location is considered. Example: `003`
`SortBy`	varchar(1) string	0	Sort order of the result. Allowed values `1` – Sort by the sort number of the target Lagerplatz, then by Artikel name. `0` – (Default, also any other value) Sort by the sort number of the source Lagerplatz, then by Artikel name. Example: `0`

Result set 1 · Always (can be empty).

One row per Artikel and source Lagerplatz that should be used to refill the target location.

Column	Type	Description
`FromLocIDbinName` Nullable	varchar(50) string	Name of the source Lagerplatz in `FromLocID`.
`FromLocIDbinSortNum` Nullable	int	Sort number of the source Lagerplatz.
`ToLocIDbinName` Nullable	varchar(50) string	Name of a Lagerplatz of the Artikel in `ToLocID`. Empty if the Artikel has none there.
`ToLocIDbinSortNum` Nullable	int	Sort number of that target Lagerplatz.
`EanUpc` Nullable	varchar(13) string	EAN/UPC of the Artikel.
`ItemName` Nullable	varchar(120) string	Artikel name.
`ToLocQty`	int	Stock of the Artikel in the target location (0 if none).
`FromLocQty` Nullable	int	Stock of the Artikel in the source location according to `ItemLocationSpecific`.
`FromBinqty` Nullable	int	Quantity of the Artikel on this source Lagerplatz (sum of `BinItemQTY`, only entries not reserved by an active picklist).
`MissingQty` Nullable	int	Number of bin entries to take from this source Lagerplatz to cover the shortage.
`MinStockQty` Nullable	int	Minimum stock of the Artikel in the target location.
`SupplPrice` Nullable	money decimal	Supplier price (purchase price) of the Artikel.
`OpenCustOrdersQty`	int	Quantity in open customer order lines in the target location (status ANG, BES, BOR, and HAL unless excluded by setting).
`OrdersOnHoldQty`	int	Quantity in order lines with status HAL in the target location.
`ParFromLocID` Nullable	varchar(3) string	Echo of parameter `FromLocID`.
`ParToLocID` Nullable	varchar(3) string	Echo of parameter `ToLocID`.
`SortBy` Nullable	varchar(1) string	Echo of parameter `SortBy`.
`PicLinkSmall` Nullable	varchar(512) string	Link to the small Artikel picture.
`PicLinkLarge` Nullable	varchar(512) string	Link to the large Artikel picture.
`ItemNrInt` Nullable	varchar(50) string	Internal Artikel number.
`TargetStock` Nullable	int	Target stock of the Artikel in the target location.

Response example

<SqlRowSet1>
  <row>
    <FromLocIDbinName>A-01-03</FromLocIDbinName>
    <FromLocIDbinSortNum>103</FromLocIDbinSortNum>
    <ToLocIDbinName>S-02-01</ToLocIDbinName>
    <ToLocIDbinSortNum>201</ToLocIDbinSortNum>
    <EanUpc>4012345678901</EanUpc>
    <ItemName>Trinkflasche 0,75 l blau</ItemName>
    <ToLocQty>2</ToLocQty>
    <FromLocQty>40</FromLocQty>
    <FromBinqty>12</FromBinqty>
    <MissingQty>3</MissingQty>
    <MinStockQty>5</MinStockQty>
    <SupplPrice>3.4500</SupplPrice>
    <OpenCustOrdersQty>0</OpenCustOrdersQty>
    <OrdersOnHoldQty>0</OrdersOnHoldQty>
    <ParFromLocID>001</ParFromLocID>
    <ParToLocID>002</ParToLocID>
    <SortBy>0</SortBy>
    <PicLinkSmall>https://shop.example.com/img/s/4711.jpg</PicLinkSmall>
    <PicLinkLarge>https://shop.example.com/img/l/4711.jpg</PicLinkLarge>
    <ItemNrInt>4711</ItemNrInt>
    <TargetStock>10</TargetStock>
  </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:pixiInventoryItemsToRefill>
      <FromLocID>001</FromLocID>
      <ToLocID>002</ToLocID>
      <!-- <AdditionalLocID>003</AdditionalLocID> -->
      <!-- <SortBy>0</SortBy> -->
    </ns1:pixiInventoryItemsToRefill>
  </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:pixiInventoryItemsToRefill>
      <FromLocID>001</FromLocID>
      <ToLocID>002</ToLocID>
      <!-- <AdditionalLocID>003</AdditionalLocID> -->
      <!-- <SortBy>0</SortBy> -->
    </ns1:pixiInventoryItemsToRefill>
  </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' => '001',
    'ToLocID' => '002',
    // 'AdditionalLocID' => '003',
    // 'SortBy' => '0',
];

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

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

SQL object:: dbo.pipiInventoryItemsToRefill