Stock Read

pixiGetPickListDetailForPico

Returns the detail lines of a finished pick list, including its split pick lists, with bin, item, batch and order data for the Pico picking app.

Returns one row per picking position of the pick list PLHkey. The call passes the key to an internal procedure, Pixi_GetPickListDetailForPico, and does not change its default filters.

Which pick lists are included

The pick list header with PLHkey = @PLHkey.
All split pick lists whose PLHOriginalKey = @PLHkey.
Only headers with ExecutionFinished = 1.
Headers of every picking status.

How rows are built

Pick list items (PLitem) are grouped into one row per combination of:
- pick list header, order line and bin (InvBinItem.BinRef);
- batch number and best-before date;
- item (BinItemRef) and pliBinRef.
Qty is the number of pick list item records in the group; each record stands for one piece.
MinPlItemKey is the smallest PLIkey in the group.
Each row is enriched with data from:
- the bin: name, sort number, bin group;
- the pick list header: expiry date, creation date and employee, comment, box range, picking status code, original key;
- the order line: order number and external order number;
- the item: names, numbers, picture links, manufacturer (items.Artist), tags and packing unit.

Computed columns

ItemName is the item name prefixed by an item number in upper case plus a space. The setting PickListNumberColumn selects the number: EANUPC, ItemNrInt or ItemNrSuppl. Any other value or no setting gives no prefix.
OtherItemsCount counts the InvBinItem records in the same bin that belong to a different item.
DistinctBins is the number of distinct bins over all rows of the result. It is the same value in every row.
ItemTags lists the item's tag names. Each name ends with a comma, and the names are separated by spaces, e.g. Fragile, Heavy,.
BestBeforeDate_Text is the best-before date formatted as dd.mm.yyyy.
OrderUnit is items.OrderUnit converted to an integer. It is NULL if the value is empty or not numeric.

Swapped item number columns

ItemNrInt contains the supplier item number (ItemNrSuppl). This keeps backward compatibility.
ItemNrInt1 contains the internal item number.

If the pick list does not exist or is not finished, the result set is empty.

Notes

Order not guaranteed: the internal procedure sorts by BinGroup, BinSortNum, BinItemRef descending and then header creation date. Its rows are buffered in a temporary table, and the final SELECT has no ORDER BY, so the delivered row order is not guaranteed. Sort on the client if needed.
Read-only: all tables are read with NOLOCK, so uncommitted data can appear.
Runtime setting: the prefix in ItemName depends on the setting PickListNumberColumn. It is read through fpixi_GetSetting, whose definition is not attached.
Column order: OrderUnit is appended as the last column so that consumers reading by position are not affected.
Truncation risk: the intermediate temporary table limits some columns, e.g. EanUpc to 13 and ItemNrSuppl to 50 characters. Longer source values may lead to a truncation error.
Other filters not exposed: the internal picking-status filter and the option to exclude split pick lists are not available through this call.

Tables used

PLHeader · Read PLitem · Read InvBinItem · Read InvBin · Read Orderlines · Read items · Read Tag · Read TagMatch · Read PL_PickingStatus · Read

Parameter	Type	Default	Description
`PLHkey` Required	int	–	Key of the pick list header (`PLHeader.PLHkey`). Split pick lists created from it are included. Example: `48213`

Result set 1 · Always; empty if the pick list does not exist or is not finished (`ExecutionFinished` <> 1).

One row per pick list header, order line, bin, batch, best-before date and item, with the quantity to pick.

Column	Type	Description
`BinItemRef` Nullable	int	Item key of the stock record (`InvBinItem.BinItemRef`).
`PLIheaderRef` Nullable	int	Key of the pick list header the position belongs to; may be a split pick list.
`BinSortNum` Nullable	int	Sort number of the bin (walking order).
`ItemNameWithoutNumber` Nullable	varchar(255) string	Item name without number prefix.
`BinName` Nullable	varchar(255) string	Name of the bin.
`BinKey` Nullable	int	Key of the bin.
`PLHexpiryDate` Nullable	datetime dateTime	Expiry date of the pick list header.
`HeaderCreateDate` Nullable	datetime dateTime	Creation date of the pick list header.
`createEmp` Nullable	varchar(255) string	Employee who created the pick list.
`Qty` Nullable	int	Quantity to pick (number of pick list item records in the group).
`PLcomment` Nullable	varchar(8000) string	Comment of the pick list header.
`ItemName` Nullable	varchar(510) string	Item name, prefixed with the upper-case item number selected by setting `PickListNumberColumn` plus a space.
`ItemNrInt` Nullable	varchar(50) string	Supplier item number in upper case. The name is misleading and kept for backward compatibility.
`EanUpc` Nullable	varchar(13) string	EAN/UPC of the item in upper case.
`ItemNrInt1` Nullable	varchar(255) string	Internal item number in upper case.
`pliBinRef` Nullable	int	Bin reference stored on the pick list item (`PLitem.pliBinRef`).
`BinGroup` Nullable	varchar(20) string	Bin group of the bin.
`PLIorderlineRef` Nullable	int	Key of the order line.
`PLHfromBox` Nullable	int	First box number of the pick list.
`PLHtoBox` Nullable	int	Last box number of the pick list.
`PicLinkLarge` Nullable	varchar(512) string	Link to the large item picture.
`PicLinkSmall` Nullable	varchar(512) string	Link to the small item picture.
`BatchNumber` Nullable	varchar(50) string	Batch number (Charge) of the stock record.
`BestBeforeDate_Text` Nullable	varchar(11) string	Best-before date as text in format `dd.mm.yyyy`.
`BestBeforeDate` Nullable	datetime dateTime	Best-before date of the stock record.
`OrderNr` Nullable	int	Pixi order number of the order line.
`OrderNrExternal` Nullable	varchar(50) string	External (shop) order number.
`OtherItemsCount` Nullable	int	Number of `InvBinItem` records in the same bin that belong to other items.
`DistinctBins` Nullable	int	Number of distinct bins across the whole result; the same value in every row.
`ItemTags` Nullable	varchar(max) string	Tag names of the item, each followed by a comma and separated by spaces; `NULL` if the item has no tags.
`PickingStatusCode` Nullable	varchar(10) string	Status code of the picking status of the header (`PL_PickingStatus.StatusCode`).
`Manufacturer` Nullable	varchar(100) string	Manufacturer of the item (`items.Artist`).
`MinPlItemKey` Nullable	int	Smallest `PLIkey` of the grouped pick list items.
`PLHOriginalKey` Nullable	int	Key of the original pick list for split pick lists.
`OrderUnit` Nullable	int	Packing unit from `items.OrderUnit` as integer; `NULL` if empty or not numeric.

Response example

<SqlRowSet1>
  <row>
    <BinItemRef>10234</BinItemRef>
    <PLIheaderRef>48213</PLIheaderRef>
    <BinSortNum>120</BinSortNum>
    <ItemNameWithoutNumber>Coffee Mug Blue</ItemNameWithoutNumber>
    <BinName>A-01-03</BinName>
    <BinKey>5521</BinKey>
    <PLHexpiryDate>2024-05-15T18:00:00</PLHexpiryDate>
    <HeaderCreateDate>2024-05-14T08:12:00</HeaderCreateDate>
    <createEmp>JD</createEmp>
    <Qty>2</Qty>
    <PLcomment>Express</PLcomment>
    <ItemName>4006381333931 Coffee Mug Blue</ItemName>
    <ItemNrInt>SUP-7781</ItemNrInt>
    <EanUpc>4006381333931</EanUpc>
    <ItemNrInt1>MUG-BL-01</ItemNrInt1>
    <pliBinRef>5521</pliBinRef>
    <BinGroup>A</BinGroup>
    <PLIorderlineRef>998877</PLIorderlineRef>
    <PLHfromBox>1</PLHfromBox>
    <PLHtoBox>20</PLHtoBox>
    <PicLinkLarge>https://shop.example.com/img/mug_l.jpg</PicLinkLarge>
    <PicLinkSmall>https://shop.example.com/img/mug_s.jpg</PicLinkSmall>
    <BatchNumber>B2024-03</BatchNumber>
    <BestBeforeDate_Text>31.12.2025</BestBeforeDate_Text>
    <BestBeforeDate>2025-12-31T00:00:00</BestBeforeDate>
    <OrderNr>345678</OrderNr>
    <OrderNrExternal>SHOP-100234</OrderNrExternal>
    <OtherItemsCount>3</OtherItemsCount>
    <DistinctBins>7</DistinctBins>
    <ItemTags>Fragile,</ItemTags>
    <PickingStatusCode>OPEN</PickingStatusCode>
    <Manufacturer>Acme</Manufacturer>
    <MinPlItemKey>7712345</MinPlItemKey>
    <PLHOriginalKey>48213</PLHOriginalKey>
    <OrderUnit>6</OrderUnit>
  </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:pixiGetPickListDetailForPico>
      <PLHkey>48213</PLHkey>
    </ns1:pixiGetPickListDetailForPico>
  </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:pixiGetPickListDetailForPico>
      <PLHkey>48213</PLHkey>
    </ns1:pixiGetPickListDetailForPico>
  </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 = [
    'PLHkey' => '48213',
];

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

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

SQL object:: dbo.pipiGetPickListDetailForPico

pixiGetPickListDetailForPico

Notes

Tables used

Result set 1 · Always; empty if the pick list does not exist or is not finished (ExecutionFinished <> 1).

Response example

Result set 1 · Always; empty if the pick list does not exist or is not finished (`ExecutionFinished` <> 1).