pixiGetItemStockOnLocations · Pixi API documentation

Returns stock figures for one item, either on all locations or on a single location.

Item lookup: if ItemKey is given, it is used directly. Otherwise the item is searched in pixi.ItemCode by EAN, ItemNrSuppl or ItemNrInt. A code matches if it equals any of the three values and has code type 1, 2 or 4. The first match is taken (TOP 1).
Total row: from items one row with Location = All is built:
- PhysicalQTY = item quantity
- AvailQTY = quantity minus special stock (ItemQtyIBISpecialStock_1) minus open reservations
- RealQtyAvail = quantity minus special stock minus open order lines
- SpecialStock = 0
Location rows: for every entry in ItemLocationSpecific of the item, one row with the location ID, the location quantity as PhysicalQTY and the SpecialStock flag of the location is returned. If LocationId is set (not empty), only the location with this LocID is returned.
Location rows and the total row are combined with UNION, so identical rows are removed.

The All row is always part of the result as long as the item exists, even if LocationId is set. If no item is found, the result set is empty.

Notes

AvailQTY and RealQtyAvail are item-level totals; they are repeated unchanged on every location row and are not calculated per location.
The output parameter Quantity is never assigned by the procedure; it returns only the value passed in (default NULL).
The item lookup by code uses TOP 1 without ORDER BY. If several items share a code, which item is used is not deterministic. The meaning of code types 1, 2 and 4 is not defined in the code.
No ORDER BY on the result: the order of rows (including the position of the All row) is not guaranteed.
All tables are read with NOLOCK, so uncommitted data may be returned.

Tables used

ItemCode · Read items · Read ItemLocationSpecific · Read Location · Read

Parameter	Type	Default	Description
`ItemKey`	int	NULL	Internal key of the item. If set, the code parameters are ignored. Example: `4711`
`EAN`	varchar(13) string	NULL	EAN used to find the item when `ItemKey` is not set. Example: `4006381333931`
`ItemNrSuppl`	varchar(50) string	NULL	Supplier item number used to find the item when `ItemKey` is not set. Example: `SUP-100245`
`ItemNrInt`	varchar(50) string	NULL	Internal item number used to find the item when `ItemKey` is not set. Example: `ART-10023`
`LocationId`	varchar(50) string	NULL	Location ID (`LocID`) to restrict the location rows to. Empty or NULL returns all locations of the item. The `All` row is returned in any case. Example: `A-01-02`
`Quantity` OUTPUT	int	NULL	Output parameter that is never assigned by the procedure; it returns the value passed in.

Result set 1 · Always; empty if no item is found.

One row per location of the item (optionally filtered) plus one total row with Location = All.

Column	Type	Description
`Location` Nullable	varchar(50) string	Location ID, or `All` for the total row.
`ItemRef`	int	Item key.
`PhysicalQTY` Nullable	int	Physical quantity on the location; for the `All` row the total item quantity.
`AvailQTY` Nullable	int	Item total quantity minus special stock minus open reservations (same value in all rows).
`RealQtyAvail` Nullable	int	Item total quantity minus special stock minus open order lines (same value in all rows).
`SpecialStock` Nullable	bit boolean	Special stock flag of the location; always 0 for the `All` row.

Response example

<SqlRowSet1>
  <row>
    <Location>A-01-02</Location>
    <ItemRef>4711</ItemRef>
    <PhysicalQTY>12</PhysicalQTY>
    <AvailQTY>15</AvailQTY>
    <RealQtyAvail>13</RealQtyAvail>
    <SpecialStock>false</SpecialStock>
  </row>
  <row>
    <Location>All</Location>
    <ItemRef>4711</ItemRef>
    <PhysicalQTY>20</PhysicalQTY>
    <AvailQTY>15</AvailQTY>
    <RealQtyAvail>13</RealQtyAvail>
    <SpecialStock>false</SpecialStock>
  </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:pixiGetItemStockOnLocations>
      <!-- <ItemKey>4711</ItemKey> -->
      <!-- <EAN>4006381333931</EAN> -->
      <!-- <ItemNrSuppl>SUP-100245</ItemNrSuppl> -->
      <!-- <ItemNrInt>ART-10023</ItemNrInt> -->
      <!-- <LocationId>A-01-02</LocationId> -->
      <!-- <Quantity>?</Quantity> -->
    </ns1:pixiGetItemStockOnLocations>
  </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:pixiGetItemStockOnLocations>
      <!-- <ItemKey>4711</ItemKey> -->
      <!-- <EAN>4006381333931</EAN> -->
      <!-- <ItemNrSuppl>SUP-100245</ItemNrSuppl> -->
      <!-- <ItemNrInt>ART-10023</ItemNrInt> -->
      <!-- <LocationId>A-01-02</LocationId> -->
      <!-- <Quantity>?</Quantity> -->
    </ns1:pixiGetItemStockOnLocations>
  </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' => '4711',
    // 'EAN' => '4006381333931',
    // 'ItemNrSuppl' => 'SUP-100245',
    // 'ItemNrInt' => 'ART-10023',
    // 'LocationId' => 'A-01-02',
    // 'Quantity' => '?',
];

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

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