Stock Write

pixiAssignBoxesForPicklist

Assigns a set of boxes to a picklist, replacing the previous box assignment, after checking that all boxes exist and that there are enough for the orders.

Replaces the box assignment of a picklist with the list of box numbers passed as XML.

Steps:

Reads the location (PLHlocID) of the picklist @PicklistNr from PLHeader. If @PicklistNr is NULL, <= 0 or the picklist does not exist, an error row is returned.
Checks that @BoxNumbers is not NULL or empty.
Parses @BoxNumbers as XML and reads all distinct /BoxNumbers/BoxNr values as integers. If no value other than 0 is found, an error row is returned.
Looks up each box number in boxes at the location of the picklist (LocationID = picklist location). If any box number is not found there, an error row listing the missing numbers is returned and nothing is changed.
Determines the number of distinct orders on the picklist (including picklists split from it via PLHOriginalKey). If the number of boxes sent is lower than the number of orders, an error row is returned.
In a transaction:
- deletes existing PicklistBox assignments of the picklist whose box is not in the new list,
- inserts assignments for boxes in the list that are not yet assigned (created/modified by the user resolved from @UserName),
- resets PLHfromBox and PLHtoBox of the picklist to 0.
Returns a row with Status = 1 and Message = Success.

The result is always a single Status/Message row; Status = 0 means validation failed and no data was changed.

Notes

The call is a full replacement: boxes currently assigned to the picklist but missing from @BoxNumbers are removed.
@BoxNumbers is converted to XML before the transaction and outside the error handling. Malformed XML or non-integer BoxNr values cause a SQL conversion error returned as SqlMessage.
A BoxNr of 0 is not ignored when other numbers are present; it is looked up like any other number and usually triggers the "do not exist" error.
The list of missing boxes in the error message is limited to 100 characters (@ErrorDetails is varchar(100)) and may be cut off.
In the "not enough boxes" message the needed count is cast to a VARCHAR without length (length 1), so only the first digit of the number of orders appears (e.g. needed 1 for 12 orders).
If @UserName is not found in UCS_USERS, the user pixiAdministration is used.
If the call runs inside an existing transaction, a savepoint is used and no commit is done.
On a runtime error inside the transaction, the changes are rolled back and the error is logged via log.InsertError; in that case no Status row is returned.
Lookups on PLHeader, PicklistBox and boxes use NOLOCK.

Parameters

Parameter Type Default Description

Parameter	Type	Default	Description
`PicklistNr` Required	int	–	Key of the picklist (`PLHkey`) to assign the boxes to. Must be > 0 and refer to an existing picklist. Example: `45872`
`BoxNumbers` Required	nvarchar(max) xml	–	XML document with the box numbers (`BoxNr` in `boxes`) to assign. Root element `BoxNumbers` with one `BoxNr` element per box. Duplicates are ignored. All boxes must exist at the location of the picklist, and their count must be at least the number of orders on the picklist. Example: `<BoxNumbers> <BoxNr>1001</BoxNr> <BoxNr>1002</BoxNr> <BoxNr>1003</BoxNr> </BoxNumbers>`
`UserName`	varchar(50) string	API	Pixi user name stored as creator/modifier of new box assignments. Falls back to `pixiAdministration` if the user does not exist. Example: `API`

PicklistNr

Required

int

–

Key of the picklist (PLHkey) to assign the boxes to. Must be > 0 and refer to an existing picklist.

Example: 45872

BoxNumbers

Required

nvarchar(max)
xml

–

XML document with the box numbers (BoxNr in boxes) to assign. Root element BoxNumbers with one BoxNr element per box. Duplicates are ignored. All boxes must exist at the location of the picklist, and their count must be at least the number of orders on the picklist.

Example:

<BoxNumbers>
  <BoxNr>1001</BoxNr>
  <BoxNr>1002</BoxNr>
  <BoxNr>1003</BoxNr>
</BoxNumbers>

UserName

varchar(50)
string

API

Pixi user name stored as creator/modifier of new box assignments. Falls back to pixiAdministration if the user does not exist.

Example: API

Response

Result set 1 · Always (except when a runtime error occurs inside the transaction or the XML cannot be converted)

Status row with the outcome of the call.

Column	Type	Description
`Status`	int	`1` = boxes assigned successfully, `0` = validation failed, nothing changed.
`Message`	varchar(1000) string	`Success` or the validation error message.

Response example

<SqlRowSet1>
  <row>
    <Status>1</Status>
    <Message>Success</Message>
  </row>
</SqlRowSet1>

Error messages

Status	Message	Cause
0	Invalid PicklistNr provided.	`@PicklistNr` is NULL, <= 0 or no picklist with this key exists in `PLHeader`. Pass the key of an existing picklist.
0	BoxNumbers parameter is required.	`@BoxNumbers` is NULL or an empty string. Pass an XML document with at least one `BoxNr`.
0	No boxes provided in XML array.	The XML contains no `/BoxNumbers/BoxNr` element with a value other than 0. Check the element names and values.
0	Boxes {BoxNr}, {BoxNr}, do not exist and could not be added.	One or more box numbers do not exist in `boxes` at the location of the picklist. Only send boxes defined for that location.
0	There are not enough boxes (sent {NumberOfBoxes}) assigned to the picklist (needed {OrdersOnPicklist}).	Fewer distinct boxes were sent than there are orders on the picklist. Send at least one box per order. Note that `{OrdersOnPicklist}` shows only the first digit of the count.

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:pixiAssignBoxesForPicklist>
      <PicklistNr>45872</PicklistNr>
      <BoxNumbers><![CDATA[<BoxNumbers><BoxNr>1001</BoxNr><BoxNr>1002</BoxNr><BoxNr>1003</BoxNr></BoxNumbers>]]></BoxNumbers>
      <!-- <UserName>API</UserName> -->
    </ns1:pixiAssignBoxesForPicklist>
  </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:pixiAssignBoxesForPicklist>
      <PicklistNr>45872</PicklistNr>
      <BoxNumbers><![CDATA[<BoxNumbers><BoxNr>1001</BoxNr><BoxNr>1002</BoxNr><BoxNr>1003</BoxNr></BoxNumbers>]]></BoxNumbers>
      <!-- <UserName>API</UserName> -->
    </ns1:pixiAssignBoxesForPicklist>
  </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 = [
    'PicklistNr' => '45872',
    'BoxNumbers' => '<BoxNumbers><BoxNr>1001</BoxNr><BoxNr>1002</BoxNr><BoxNr>1003</BoxNr></BoxNumbers>',
    // 'UserName' => 'API',
];

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

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

Tables used

PLHeader · Write PicklistBox · Write boxes · Read PLitem · Read Orderlines · Read UCS_USERS · Read

SQL object:: dbo.pipiAssignBoxesForPicklist