Stock Read

pixiMobixGetBinItemInfo

Returns the items with positive stock on a bin (or on all bins), including tags, supplier numbers, open picklists and packed box quantities.

Lists every item that currently has stock on a bin, intended for the Mobix mobile warehouse client.

Bin stock

Bins (InvBin) are joined with the bin item log (InvBinItemLog, matched on bin and location) and the item master (items).
If BinName is given, only bins with exactly that name are considered (bins with this name in any location). If it is omitted or NULL, all bins are evaluated.
Quantities are summed per bin and item; only combinations with a sum greater than 0 are returned.

Additional item information

Tags: names of the item tags assigned to the item, sorted alphabetically, separated by commas.
ItemNrSuppls: distinct supplier item numbers from additems.
SupplNrs: supplier numbers from additems.

Picklist information

Order lines of the item that are on a non-expired picklist item (PLitem.Expired = 0) and whose status is not AUS, NLB, RET, STO or EIN are counted per picklist.
PicklistsUsed lists the picklist header references, QtyOnPiclists the number of picklist items.

Box information

Order lines with status EIN that are packed into boxes (boxitems, boxes) are summed per box.
BoxesUsed lists the box numbers, QtyInBoxes the packed quantity.

LocId only restricts the picklist and box information to order lines of that location (Orderlines.LocationID). An empty value or NULL means all locations. The bin stock itself is not filtered by LocId.

No explicit sort order is applied.

Notes

All tables are read with NOLOCK; results can include uncommitted data.
QtyOnPiclists and QtyInBoxes are calculated with scalar subqueries. If an item is on more than one picklist or in more than one box, SQL Server raises error 512 (subquery returned more than one value) and the call fails.
If the same item lies on several bins in the result, the picklist count is multiplied by the number of these bins because of the join with the bin list.
The lists in Tags, ItemNrSuppls, SupplNrs, PicklistsUsed and BoxesUsed are built by string concatenation and usually end with a trailing comma; separators may contain extra spaces.
The status filter for picklists uses an unqualified Status column; it is most likely the order line status.
Without BinName all bins with stock are evaluated, which can be slow and return many rows.

Parameters

Parameter	Type	Default	Description
`BinName`	varchar(50) string	NULL	Name of the bin to evaluate. `NULL` or omitted returns all bins with positive stock. Example: `A-01-03`
`LocId`	varchar(3) string	NULL	Location ID used to restrict the picklist and box information to order lines of this location. `NULL` or empty means all locations. Does not filter the bin stock. Example: `001`

Response

Result set 1 · Always (may be empty if no bin item has positive stock).

One row per bin and item with positive stock.

Column	Type	Description
`BinKey`	int	Key of the bin.
`BinName`	varchar(50) string	Name of the bin.
`BinLocID`	varchar(3) string	Location of the bin.
`ItemName` Nullable	string	Item name.
`EANUPC` Nullable	string	EAN/UPC of the item.
`WebPic` Nullable	string	Picture reference of the item.
`BinQty`	decimal	Stock of the item on the bin (sum of the bin item log quantities, always > 0).
`Tags`	nvarchar(max) string	Comma-separated item tag names, sorted alphabetically; empty string if none.
`ItemNrSuppls`	nvarchar(max) string	Comma-separated distinct supplier item numbers; empty string if none.
`SupplNrs`	nvarchar(max) string	Comma-separated supplier numbers of the item; empty string if none.
`PicklistsUsed`	nvarchar(max) string	Comma-separated picklist header references with open picklist items for the item; empty string if none.
`QtyOnPiclists`	int	Number of open picklist items for the item; 0 if none.
`BoxesUsed`	nvarchar(max) string	Comma-separated numbers of boxes containing the item from order lines with status `EIN`; empty string if none.
`QtyInBoxes`	decimal	Quantity of the item packed in boxes; 0 if none.

Response example

<SqlRowSet1>
  <row>
    <BinKey>1523</BinKey>
    <BinName>A-01-03</BinName>
    <BinLocID>001</BinLocID>
    <ItemName>T-Shirt Basic Black M</ItemName>
    <EANUPC>4012345678901</EANUPC>
    <WebPic>tshirt_black.jpg</WebPic>
    <BinQty>12</BinQty>
    <Tags>Cotton, Sale,</Tags>
    <ItemNrSuppls>TS-BLK-M, </ItemNrSuppls>
    <SupplNrs>70001, </SupplNrs>
    <PicklistsUsed>4711, </PicklistsUsed>
    <QtyOnPiclists>2</QtyOnPiclists>
    <BoxesUsed/>
    <QtyInBoxes>0</QtyInBoxes>
  </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:pixiMobixGetBinItemInfo>
      <!-- <BinName>A-01-03</BinName> -->
      <!-- <LocId>001</LocId> -->
    </ns1:pixiMobixGetBinItemInfo>
  </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:pixiMobixGetBinItemInfo>
      <!-- <BinName>A-01-03</BinName> -->
      <!-- <LocId>001</LocId> -->
    </ns1:pixiMobixGetBinItemInfo>
  </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 = [
    // 'BinName' => 'A-01-03',
    // 'LocId' => '001',
];

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

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

Tables used

InvBin · Read InvBinItemLog · Read items · Read ItemTag · Read ItemTagMatching · Read additems · Read Orderlines · Read PLitem · Read boxitems · Read boxes · Read

SQL object:: dbo.pipiMobixGetBinItemInfo