pixiCreatePicklist

Starts picklist creation using the settings of a stored picklist profile. Some profile values can be overridden by parameters.

Premium API check. The setting Premium_API must be enabled. Otherwise an ERROR row is returned.
Profile. If ProfileId is given (0 counts as not given), it must exist in PL_Profile. If it is not given, the most recently updated default profile (PLP_Default = 1) that is not a 1SS profile (PLP_1SS = 0) is used.
Location. An empty LocationId becomes 001.
Order restriction (optional).
- Only OrderNr: the order must exist in OrderHeader.
- Only OrderNrExternal: an order with this external number must exist.
- Both: an order matching both values must exist. Afterwards only OrderNr is used and OrderNrExternal is cleared.
- OrderNr = 0 and an empty OrderNrExternal count as not given.
Bin range (optional). BinFrom and BinTo must be bin names (InvBin.BinName) in the chosen location. Their BinSortNum replaces the bin sort range of the profile.
User and comment. UserName is trimmed; if it is empty it becomes API. Comment is trimmed and stored as the picklist comment.
Profile parameters. All parameters of the profile are loaded. Location, profile ID, order restriction, bin range, user name and comment are then overwritten with the values from steps 2–6.
Exclusive lock. The call requests the application lock ExclusivePickListCreation without waiting. If another session holds the lock, an ERROR row is returned.
Execution. An execution number is built from the current time in the format yyMMddHHmmss.
- ExecuteAsynchronous = 1 (default): an EXEC dbo.Pixi_CreatePickList … statement containing all parameters is written to ExecQueries. An INFO row is returned.
- ExecuteAsynchronous = 0: Pixi_CreatePickList runs immediately. It reads its parameters from the temporary parameter table.

The lock is released afterwards.

Notes

Synchronous mode (ExecuteAsynchronous = 0)

No INFO row is returned.
The caller receives whatever Pixi_CreatePickList returns. That procedure is not attached, so its result sets and further side effects are not documented here.
The lock is session-scoped. If Pixi_CreatePickList fails with an error, the release may be skipped. The lock then stays held until the database session ends.

Asynchronous mode (default)

The lock is held only while the queue entry is written. It does not prevent several queued creations from running at the same time.
The queued statement is built as a text string. Quotes in values such as Comment or UserName are not escaped, so an apostrophe can make the queued statement invalid.
The queued statement does not pass the profile values OlderThanHour, OlderThanMinute, NewerThanHour, NewerThanMinute and DisablePicklistSplitOnBatchManagement.

Pitfalls

The location check never fails. If LocationId does not exist, the given value is kept and the location error message is never returned.
Comment always overwrites the profile comment. Omitting it clears the comment from the profile.
An empty string for BinFrom or BinTo is not treated as missing. It is looked up as a bin name and leads to an error.
The profile ID error message says "could be found", but it means that the profile was not found.

Other

All lookups use NOLOCK.

Tables used

Settings · Read PL_Profile · Read Location · Read OrderHeader · Read InvBin · Read ExecQueries · Write

Parameter	Type	Default	Description
`ProfileId`	smallint int	NULL	Key of the picklist profile (`PL_Profile.PLP_Key`). NULL or 0 uses the most recently updated default profile that is not a 1SS profile. Example: `3`
`LocationId`	varchar(3) string	NULL	Location for the picklist. NULL or empty becomes `001`. Example: `001`
`OrderNr`	int	NULL	Restricts the picklist to this order number. NULL or 0 means no restriction. The order must exist. Example: `1045873`
`BinFrom`	varchar(50) string	NULL	Name of the first bin of the range in the chosen location. Its `BinSortNum` replaces the lower bin sort limit of the profile. Example: `A-01-01`
`BinTo`	varchar(50) string	NULL	Name of the last bin of the range in the chosen location. Its `BinSortNum` replaces the upper bin sort limit of the profile. Example: `A-10-05`
`UserName`	varchar(50) string	NULL	User recorded for the picklist creation. NULL or empty becomes `API`. Example: `shopsync`
`Comment`	varchar(8000) string	NULL	Picklist comment (trimmed). Always replaces the profile comment, even when it is empty. Example: `Express wave 14:00`
`ExecuteAsynchronous`	bit boolean	1	Controls whether the picklist is created immediately or queued. Allowed values `1` – Queue the creation in `ExecQueries` and return an `INFO` row. `0` – Run `Pixi_CreatePickList` immediately within the call. Example: `1`
`OrderNrExternal`	varchar(50) string	NULL	Restricts the picklist to the order with this external order number. Empty means no restriction. If `OrderNr` is also given, both must match the same order, and then only `OrderNr` is used. Example: `SHOP-100234`

Result set 1 · On a validation error, if the lock is not available, or after queueing (asynchronous mode).

Status row of the call.

Column	Type	Description
`Status`	varchar(5) string	`ERROR` or `Error` on failure, `INFO` after successful queueing.
`Message` Nullable	varchar(max) string	Text of the message.

Result set 2 · Synchronous mode (`ExecuteAsynchronous = 0`) after all checks have passed.

Result sets produced by Pixi_CreatePickList. That procedure is not attached, so its structure is unknown.

Response example

<SqlRowSet1>
  <row>
    <Status>INFO</Status>
    <Message>Picklist creation added to the queue.</Message>
  </row>
</SqlRowSet1>

Status	Message	Cause
ERROR	Setting "Premium_API" is not enabled.	The setting `Premium_API` is not enabled. It must be enabled in Pixi before this call can be used.
Error	Profile ID "{ProfileId}" could be found. Please provide a profile ID that you want tu use for creating the picklist.	The given `ProfileId` does not exist in `PL_Profile`. Pass an existing profile key.
Error	No default Profile ID could be found. Please provide a profile ID that you want tu use for creating the picklist.	No `ProfileId` was passed and no default profile that is not a 1SS profile exists. Pass a `ProfileId` explicitly.
Error	Location "{LocationId}" could be found. Please provide a different Location ID that you want tu use for creating the picklist.	Intended for an unknown location. Because of the way the check is written, this message is never returned in practice.
Error	Order "{OrderNr}" could not be found. Please provide a different OrderNr that you want tu use for creating the picklist.	Only `OrderNr` was passed and no order with this number exists.
Error	OrderNrExternal "{OrderNrExternal}" could not be found. Please provide a different OrderNrExternal that you want tu use for creating the picklist.	Only `OrderNrExternal` was passed and no order with this external number exists.
Error	OrderNr "{OrderNr}" and OrderNrExternal "{OrderNrExternal}" could not be found. Please provide a different parameters that you want tu use for creating the picklist.	Both order numbers were passed, but no order matches both values.
Error	Bin place "{BinFrom}" for paramter @BinFrom could not be found. Please provide a different BinName that you want tu use for creating the picklist.	No bin with the name `BinFrom` exists in the chosen location. An empty string also triggers this error.
Error	Bin place "{BinTo}" for paramter @BinTo could not be found. Please provide a different BinName that you want tu use for creating the picklist.	No bin with the name `BinTo` exists in the chosen location. An empty string also triggers this error.
ERROR	Another instance of picklist creation already running. Try again later.	Another session holds the application lock `ExclusivePickListCreation`. Retry later.

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:pixiCreatePicklist>
      <!-- <ProfileId>3</ProfileId> -->
      <!-- <LocationId>001</LocationId> -->
      <!-- <OrderNr>1045873</OrderNr> -->
      <!-- <BinFrom>A-01-01</BinFrom> -->
      <!-- <BinTo>A-10-05</BinTo> -->
      <!-- <UserName>shopsync</UserName> -->
      <!-- <Comment>Express wave 14:00</Comment> -->
      <!-- <ExecuteAsynchronous>1</ExecuteAsynchronous> -->
      <!-- <OrderNrExternal>SHOP-100234</OrderNrExternal> -->
    </ns1:pixiCreatePicklist>
  </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:pixiCreatePicklist>
      <!-- <ProfileId>3</ProfileId> -->
      <!-- <LocationId>001</LocationId> -->
      <!-- <OrderNr>1045873</OrderNr> -->
      <!-- <BinFrom>A-01-01</BinFrom> -->
      <!-- <BinTo>A-10-05</BinTo> -->
      <!-- <UserName>shopsync</UserName> -->
      <!-- <Comment>Express wave 14:00</Comment> -->
      <!-- <ExecuteAsynchronous>1</ExecuteAsynchronous> -->
      <!-- <OrderNrExternal>SHOP-100234</OrderNrExternal> -->
    </ns1:pixiCreatePicklist>
  </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 = [
    // 'ProfileId' => '3',
    // 'LocationId' => '001',
    // 'OrderNr' => '1045873',
    // 'BinFrom' => 'A-01-01',
    // 'BinTo' => 'A-10-05',
    // 'UserName' => 'shopsync',
    // 'Comment' => 'Express wave 14:00',
    // 'ExecuteAsynchronous' => '1',
    // 'OrderNrExternal' => 'SHOP-100234',
];

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

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

Notes

Tables used

Result set 1 · On a validation error, if the lock is not available, or after queueing (asynchronous mode).

Result set 2 · Synchronous mode (ExecuteAsynchronous = 0) after all checks have passed.

Response example

Result set 2 · Synchronous mode (`ExecuteAsynchronous = 0`) after all checks have passed.