pixiGetResolvedItemDependencies

Takes a list of items with quantities as XML and returns the items that the request actually depends on, for example to check or reserve stock for an order.

Steps:

The XML in ItemXML is parsed from /Items/Item. Each Item can identify the article by ItemKey, by ItemNrInt or by both, and carries a Qty.
Missing identifiers are completed from the Items table: if ItemKey is missing it is looked up by ItemNrInt; if ItemNrInt is missing it is looked up by ItemKey.
Entries whose ItemKey cannot be determined (unknown item number) are silently discarded.
Resolution:
- Items with IsBundle = 0 are returned with the requested Qty.
- Items with IsBundle = 1 are not returned themselves. Instead every component from BundleItems is returned with quantity Qty × component quantity.
The result is grouped by ItemKey: quantities of the same item (listed several times or contained in several bundles) are summed up.

If ItemXML is not passed, an empty result set is returned. No sort order is defined.

Notes

Read-only; all reads on Items and BundleItems use NOLOCK, so uncommitted data may be returned.
Bundles are resolved only one level deep. A component that is itself a bundle is returned as it is and is not expanded further.
If both ItemKey and ItemNrInt are given, ItemKey is used and ItemNrInt is not checked against it.
Unknown items are dropped without any error message; compare the result with the request if this matters.
If Qty is omitted for an entry, its quantity is NULL and does not contribute to the summed Qty (the sum becomes NULL if all contributions are NULL).
Invalid XML causes an SQL error from sp_xml_preparedocument, returned as SqlMessage.
The data type of the returned Qty depends on the type of BundleItems.Qty, which is not visible in the code.

Tables used

Items · Read BundleItems · Read

Parameter Type Default Description

Parameter	Type	Default	Description
`ItemXML`	varchar(max) xml	NULL	XML list of requested items. Root `Items`, one `Item` element per entry with the child elements `ItemKey` and/or `ItemNrInt` and `Qty`. If NULL, the result is empty. Example: `<Items> <Item> <ItemKey>1052</ItemKey> <ItemNrInt>ART-10052</ItemNrInt> <Qty>2</Qty> </Item> <Item> <ItemNrInt>BUNDLE-200</ItemNrInt> <Qty>1</Qty> </Item> </Items>`

ItemXML

varchar(max)
xml

NULL

XML list of requested items. Root Items, one Item element per entry with the child elements ItemKey and/or ItemNrInt and Qty. If NULL, the result is empty.

Example:

<Items>
  <Item>
    <ItemKey>1052</ItemKey>
    <ItemNrInt>ART-10052</ItemNrInt>
    <Qty>2</Qty>
  </Item>
  <Item>
    <ItemNrInt>BUNDLE-200</ItemNrInt>
    <Qty>1</Qty>
  </Item>
</Items>

Result set 1 · Always (empty if `ItemXML` is NULL or no item could be resolved)

One row per resolved stock item with the total required quantity.

Column	Type	Description
`ItemKey`	int	Key of the resolved item (a non-bundle item from the request or a bundle component).
`ItemNrInt` Nullable	varchar(50) string	Internal item number of the resolved item from `Items`.
`Qty` Nullable	decimal	Total required quantity: sum of the requested quantities, multiplied by the component quantity for bundle components.

Response example

<SqlRowSet1>
  <row>
    <ItemKey>1052</ItemKey>
    <ItemNrInt>ART-10052</ItemNrInt>
    <Qty>3</Qty>
  </row>
  <row>
    <ItemKey>1077</ItemKey>
    <ItemNrInt>ART-10077</ItemNrInt>
    <Qty>2</Qty>
  </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:pixiGetResolvedItemDependencies>
      <!-- <ItemXML><![CDATA[<Items><Item><ItemKey>1052</ItemKey><ItemNrInt>ART-10052</ItemNrInt><Qty>2</Qty></Item><Item><ItemNrInt>BUNDLE-200</ItemNrInt><Qty>1</Qty></Item></Items>]]></ItemXML> -->
    </ns1:pixiGetResolvedItemDependencies>
  </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:pixiGetResolvedItemDependencies>
      <!-- <ItemXML><![CDATA[<Items><Item><ItemKey>1052</ItemKey><ItemNrInt>ART-10052</ItemNrInt><Qty>2</Qty></Item><Item><ItemNrInt>BUNDLE-200</ItemNrInt><Qty>1</Qty></Item></Items>]]></ItemXML> -->
    </ns1:pixiGetResolvedItemDependencies>
  </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 = [
    // 'ItemXML' => '<Items><Item><ItemKey>1052</ItemKey><ItemNrInt>ART-10052</ItemNrInt><Qty>2</Qty></Item><Item><ItemNrInt>BUNDLE-200</ItemNrInt><Qty>1</Qty></Item></Items>',
];

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

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

Notes

Tables used

Result set 1 · Always (empty if ItemXML is NULL or no item could be resolved)

Response example

Result set 1 · Always (empty if `ItemXML` is NULL or no item could be resolved)