Stock Read

pixiGetItemStockOnForOrder2Store

Returns the physical and available stock of items per stock location plus an aggregated 'All' row per item, taking open orders or reservations into account.

Returns one row per item and stock location (ItemLocationSpecific joined with Location) and, combined via UNION, one additional row per item with Location = All.

Settings read at runtime

HalOrdersActiveDays: number of days that order lines in status HAL count as open. A value of 0 is replaced by 7.
ReservePrepaymentOrders: if 1, availability is calculated from reservations, otherwise from open order lines.

Per-location rows

PhysicalQTY is the LocationQuantity of the item on that location.
OpenOrders is the sum of Qty of order lines of the item on that location with status ANG, or status HAL and an OrderDate within the last HalOrdersActiveDays days.
ReservedOrders is the number of reservation records of the item whose order line is on that location and has a non-final order state other than EIN, FUT, FUS.
AvailQTY = PhysicalQTY minus ReservedOrders (reservations enabled) or minus OpenOrders (reservations disabled).
SpecialStock is the special-stock flag of the location.
Filtered by LocationId if given.

'All' rows

PhysicalQTY is the total item quantity (Items.Quantity).
OpenOrders and ReservedOrders are computed like above, but over all locations.
AvailQTY = total quantity minus the stock on special-stock locations (Location.SpecialStock = 1) minus ReservedOrders or OpenOrders (depending on the setting).
SpecialStock is always 0.
LocationId is not applied to these rows, they are always returned for the selected items.

Item selection The item parameters ItemKey, EAN, ItemNrSuppl and ItemNrInt are combined with OR, and each condition is true when its parameter is NULL. See the notes for the consequences. No sort order is defined.

Notes

Filter pitfall: because the four item conditions are OR-combined and each is true when its parameter is NULL, leaving out any of ItemKey, EAN, ItemNrSuppl, ItemNrInt returns all items. Passing only ItemKey therefore does not restrict the result. Only when all four parameters are filled are the items returned that match at least one of them.
Without any filter the call returns rows for every item and location, which can be very large and slow because of the correlated subqueries per row.
The output parameter Quantity is never assigned by the code; it is returned unchanged.
ReservedOrders counts reservation records, not quantities.
If the setting HalOrdersActiveDays does not exist, the cut-off date is NULL and HAL order lines are never counted as open.
A code comment mentions 14 days for HAL orders, but the actual period comes from HalOrdersActiveDays (default 7 if set to 0).
All tables are read with NOLOCK, so values may include uncommitted data.

Parameters

Parameter	Type	Default	Description
`ItemKey`	int	NULL	Internal item key. Only effective as a filter if all other item parameters are also set (see notes). Example: `12345`
`EAN`	varchar(13) string	NULL	EAN/UPC of the item (`Items.EANUPC`). OR-combined with the other item parameters. Example: `4006381333931`
`ItemNrSuppl`	varchar(50) string	NULL	Supplier item number (`Items.ItemNrSuppl`). OR-combined with the other item parameters. Example: `SUP-4711`
`ItemNrInt`	varchar(50) string	NULL	Internal item number (`Items.ItemNrInt`). OR-combined with the other item parameters. Example: `ART-10001`
`LocationId`	varchar(50) string	NULL	Restricts the per-location rows to this location ID. `NULL` returns all locations. Does not affect the 'All' rows. Example: `001`
`Quantity` OUTPUT	int	NULL	Output parameter that is never assigned by the code; it is returned unchanged.

Response

Result set 1 · Always

Stock rows per item and location plus one 'All' row per item; unsorted.

Column	Type	Description
`Location`	string	Location ID (`ItemLocationSpecific.LocID`) or the literal `All` for the aggregated row.
`ItemKey`	int	Internal item key.
`PhysicalQTY` Nullable	int	Physical stock: `LocationQuantity` on the location, or `Items.Quantity` for the 'All' row.
`OpenOrders`	int	Sum of quantities of open order lines (status `ANG`, or `HAL` within the configured days) on the location or across all locations.
`ReservedOrders`	int	Number of reservation records for order lines in non-final states other than `EIN`, `FUT`, `FUS`.
`AvailQTY` Nullable	int	Available stock: physical stock minus reservations or open orders (depending on `ReservePrepaymentOrders`); for 'All' additionally minus stock on special-stock locations.
`SpecialStock` Nullable	int	Special-stock flag of the location (`Location.SpecialStock`); always `0` for the 'All' row.

Response example

<SqlRowSet1>
  <row>
    <Location>001</Location>
    <ItemKey>12345</ItemKey>
    <PhysicalQTY>20</PhysicalQTY>
    <OpenOrders>3</OpenOrders>
    <ReservedOrders>0</ReservedOrders>
    <AvailQTY>17</AvailQTY>
    <SpecialStock>0</SpecialStock>
  </row>
  <row>
    <Location>All</Location>
    <ItemKey>12345</ItemKey>
    <PhysicalQTY>25</PhysicalQTY>
    <OpenOrders>4</OpenOrders>
    <ReservedOrders>0</ReservedOrders>
    <AvailQTY>16</AvailQTY>
    <SpecialStock>0</SpecialStock>
  </row>
</SqlRowSet1>

Examples

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:pixiGetItemStockOnForOrder2Store>
      <!-- <ItemKey>12345</ItemKey> -->
      <!-- <EAN>4006381333931</EAN> -->
      <!-- <ItemNrSuppl>SUP-4711</ItemNrSuppl> -->
      <!-- <ItemNrInt>ART-10001</ItemNrInt> -->
      <!-- <LocationId>001</LocationId> -->
      <!-- <Quantity>?</Quantity> -->
    </ns1:pixiGetItemStockOnForOrder2Store>
  </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:pixiGetItemStockOnForOrder2Store>
      <!-- <ItemKey>12345</ItemKey> -->
      <!-- <EAN>4006381333931</EAN> -->
      <!-- <ItemNrSuppl>SUP-4711</ItemNrSuppl> -->
      <!-- <ItemNrInt>ART-10001</ItemNrInt> -->
      <!-- <LocationId>001</LocationId> -->
      <!-- <Quantity>?</Quantity> -->
    </ns1:pixiGetItemStockOnForOrder2Store>
  </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 = [
    // 'ItemKey' => '12345',
    // 'EAN' => '4006381333931',
    // 'ItemNrSuppl' => 'SUP-4711',
    // 'ItemNrInt' => 'ART-10001',
    // 'LocationId' => '001',
    // 'Quantity' => '?',
];

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

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

Tables used

Settings · Read OrderStates · Read ItemLocationSpecific · Read Location · Read Orderlines · Read Reservation · Read Items · Read

SQL object:: dbo.pipiGetItemStockOnForOrder2Store