pixiReportGetShippableOrdersV2

Determines which open orders can be shipped with the current stock and returns them as line details or as aggregated totals.

1. Candidate orders

Only order lines in status ANG or EIN (as far as these exist in OrderStates) with an ItemRef and Qty > 0 are considered. Replacement lines without an item are skipped.
ShopId: only orders whose customer has CustomerOf = @ShopId. An empty string (the default) means all shops.
Orders whose shipping or billing address is locked (ASlocked = 'Y') are excluded.
Orders whose header is locked (OhSLocked = 'Y') are excluded.
Ship date: by default only orders without a ship date or with a ship date before tomorrow are included. With IncludeOrdersWithAnyShipDate = 1 the ship date is ignored.

2. Stock check For every candidate line, the internal procedure pixi_CalculateStockForOrderLines decides whether the line is shippable. Stock of locations with SpecialStock = 0 is used. Earlier reservations and older unreserved order lines (FIFO by reservation date / order line key) are subtracted from the stock. Lines in status EIN always count as shippable. UseFIFObyLocationV2 selects whether stock is checked per location of the line (1) or summed over all locations (0). NULL means the value of the setting UseFIFObyLocation is used.

3. Filter by part delivery

OnlyPartDelivery = 0, IncludePartDelivery = 0: all lines of orders in which every candidate line is shippable (complete orders only).
OnlyPartDelivery = 0, IncludePartDelivery = 1: all shippable lines, regardless of whether the order is complete.
OnlyPartDelivery = 1: only shippable lines of orders that also contain at least one line that is not shippable. IncludePartDelivery is ignored.

4. Currency If the setting MultiCurrencyEnabled is 1, Price and ShipCost are divided by the exchange rate of the database currency source for the order's currency and exchange date. The result is in the database currency, and dbCurrency holds its code. If no matching rate is found, the amounts are left unchanged. Without multi-currency, amounts are returned as stored and dbCurrency is NULL.

5. Output (exactly one result set, chosen by ViewDetails and PerLocation)

ViewDetails = 1, PerLocation = 0: one row per line, sorted by OrderNr, OrderlineRef.
ViewDetails = 0, PerLocation = 0: one total row with the number of orders, order value (Price * Qty), shipping cost and picks.
ViewDetails = 1, PerLocation = 1: like the details, with an additional LocationId column.
ViewDetails = 0, PerLocation = 1: one total row per location.

Notes

All reads use NOLOCK. The result can contain uncommitted data and is a snapshot only; nothing is reserved or changed.
NULL values for ViewDetails, IncludePartDelivery, OnlyPartDelivery and PerLocation are treated as 0. An empty ShopId is treated as "all shops". A NULL value for IncludeOrdersWithAnyShipDate acts like 0.
Settings that change the behaviour:
- MultiCurrencyEnabled: currency conversion and the dbCurrency column.
- HomeCurrency and DatabaseCurrencyExchRateSourceId: which database currency and rate source are used.
- UseFIFObyLocation: used when UseFIFObyLocationV2 is NULL.
Whether an order is "complete" is judged only by its ANG/EIN lines with an item and positive quantity. Lines in other statuses are ignored.
ShipCost is the shipping cost of the order header, repeated on every line. The totals add it once per order. In the per-location totals it is added once per order and location, so an order spread over several locations counts its shipping cost in each of them.
In the overall totals (ViewDetails = 0, PerLocation = 0) one row is always returned. If nothing is shippable, Orders = 0, ShipCost = 0, and OrderTotal/Picks are NULL. The per-location totals return no rows in that case.
The comment in the code marks PerLocation as the "old way".
The internal stock procedure calls log.EventDurationInsert (definition not attached), which presumably writes a duration log entry.
The call scans all open ANG/EIN order lines and their reservations and can be expensive on large databases.

Tables used

OrderStates · Read Orderlines · Read OrderHeader · Read Customers · Read Addresses · Read ExchRate · Read Currency · Read Settings · Read Location · Read ItemLocationSpecific · Read Reservation · Read

Parameter	Type	Default	Description
`ViewDetails`	bit boolean	NULL	`1` returns one row per order line, `0` (or `NULL`) returns totals. Allowed values `0` – Totals (default when NULL) `1` – Line details Example: `1`
`IncludePartDelivery`	bit boolean	NULL	With `OnlyPartDelivery = 0`: `1` returns all shippable lines, including lines of orders that can only be partly shipped. `0` returns complete orders only. Ignored if `OnlyPartDelivery = 1`. Allowed values `0` – Only complete orders (default when NULL) `1` – All shippable lines, including part deliveries Example: `0`
`OnlyPartDelivery`	bit boolean	NULL	`1` returns only the shippable lines of orders that also contain unshippable lines (part deliveries). Allowed values `0` – Filter by IncludePartDelivery (default when NULL) `1` – Only part deliveries Example: `0`
`ShopId`	varchar(3) string	–	Shop code. Only orders of customers with `CustomerOf` equal to this value are returned. Empty means all shops. Example: `HOH`
`IncludeOrdersWithAnyShipDate`	bit boolean	0	`0`: only orders without a ship date or with a ship date up to today. `1`: the ship date is ignored. Allowed values `0` – Ship date up to today or empty `1` – Any ship date Example: `0`
`PerLocation`	bit boolean	0	`1` adds `LocationId` to the details or groups the totals by location. Marked as the "old way" in the code. Allowed values `0` – No location breakdown `1` – Breakdown by location Example: `0`
`UseFIFObyLocationV2`	bit boolean	NULL	Controls the stock check: `1` checks the stock of the line's location, `0` checks the total stock of all non-special-stock locations. `NULL` uses the setting `UseFIFObyLocation`. Allowed values `0` – Stock summed over all locations `1` – Stock per location of the line Example: `1`

Result set 1 · If `ViewDetails` = 1 and `PerLocation` = 0

One row per shippable order line, sorted by OrderNr, OrderlineRef.

Column	Type	Description
`OrderlineRef`	int	Order line key
`OrderNr` Nullable	int	Order number
`Qty` Nullable	int	Ordered quantity of the line
`ItemRef` Nullable	int	Item reference
`Status` Nullable	varchar(3) string	Line status (`ANG` or `EIN`)
`ShipCost` Nullable	money decimal	Shipping cost of the order header, converted to the database currency when multi-currency is enabled
`Price` Nullable	money decimal	Unit price of the line, converted to the database currency when multi-currency is enabled
`Shippable`	int	Always 1
`dbCurrency` Nullable	varchar(3) string	Database currency code if multi-currency is enabled, otherwise NULL

Result set 2 · If `ViewDetails` = 0 and `PerLocation` = 0 (default)

A single row with totals over all returned lines.

Column	Type	Description
`Orders`	int	Number of distinct orders
`OrderTotal` Nullable	money decimal	Sum of `Price * Qty`
`ShipCost`	money decimal	Sum of the shipping costs, counted once per order
`Picks` Nullable	int	Sum of the quantities
`dbCurrency` Nullable	varchar(3) string	Database currency code if multi-currency is enabled, otherwise NULL

Result set 3 · If `ViewDetails` = 1 and `PerLocation` = 1

One row per shippable order line including the location, sorted by OrderNr, OrderlineRef.

Column	Type	Description
`OrderlineRef`	int	Order line key
`OrderNr` Nullable	int	Order number
`LocationId` Nullable	varchar(3) string	Location of the order line
`Qty` Nullable	int	Ordered quantity of the line
`ItemRef` Nullable	int	Item reference
`Status` Nullable	varchar(3) string	Line status (`ANG` or `EIN`)
`ShipCost` Nullable	money decimal	Shipping cost of the order header, converted to the database currency when multi-currency is enabled
`Price` Nullable	money decimal	Unit price of the line, converted to the database currency when multi-currency is enabled
`Shippable`	int	Always 1
`dbCurrency` Nullable	varchar(3) string	Database currency code if multi-currency is enabled, otherwise NULL

Result set 4 · If `ViewDetails` = 0 and `PerLocation` = 1

One row with totals per location; no rows if nothing is shippable.

Column	Type	Description
`Orders`	int	Number of distinct orders with lines in this location
`LocationId` Nullable	varchar(3) string	Location
`OrderTotal` Nullable	money decimal	Sum of `Price * Qty` in this location
`ShipCost` Nullable	money decimal	Sum of the shipping costs, counted once per order in this location
`Picks` Nullable	int	Sum of the quantities in this location
`dbCurrency` Nullable	varchar(3) string	Database currency code if multi-currency is enabled, otherwise NULL

Response example

<SqlRowSet1>
  <row>
    <Orders>42</Orders>
    <OrderTotal>3157.8000</OrderTotal>
    <ShipCost>209.5000</ShipCost>
    <Picks>118</Picks>
    <dbCurrency>EUR</dbCurrency>
  </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:pixiReportGetShippableOrdersV2>
      <!-- <ViewDetails>1</ViewDetails> -->
      <!-- <IncludePartDelivery>0</IncludePartDelivery> -->
      <!-- <OnlyPartDelivery>0</OnlyPartDelivery> -->
      <!-- <ShopId>HOH</ShopId> -->
      <!-- <IncludeOrdersWithAnyShipDate>0</IncludeOrdersWithAnyShipDate> -->
      <!-- <PerLocation>0</PerLocation> -->
      <!-- <UseFIFObyLocationV2>1</UseFIFObyLocationV2> -->
    </ns1:pixiReportGetShippableOrdersV2>
  </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:pixiReportGetShippableOrdersV2>
      <!-- <ViewDetails>1</ViewDetails> -->
      <!-- <IncludePartDelivery>0</IncludePartDelivery> -->
      <!-- <OnlyPartDelivery>0</OnlyPartDelivery> -->
      <!-- <ShopId>HOH</ShopId> -->
      <!-- <IncludeOrdersWithAnyShipDate>0</IncludeOrdersWithAnyShipDate> -->
      <!-- <PerLocation>0</PerLocation> -->
      <!-- <UseFIFObyLocationV2>1</UseFIFObyLocationV2> -->
    </ns1:pixiReportGetShippableOrdersV2>
  </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 = [
    // 'ViewDetails' => '1',
    // 'IncludePartDelivery' => '0',
    // 'OnlyPartDelivery' => '0',
    // 'ShopId' => 'HOH',
    // 'IncludeOrdersWithAnyShipDate' => '0',
    // 'PerLocation' => '0',
    // 'UseFIFObyLocationV2' => '1',
];

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

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

Notes

Tables used

Result set 1 · If ViewDetails = 1 and PerLocation = 0

Result set 2 · If ViewDetails = 0 and PerLocation = 0 (default)

Result set 3 · If ViewDetails = 1 and PerLocation = 1

Result set 4 · If ViewDetails = 0 and PerLocation = 1

Response example

Result set 1 · If `ViewDetails` = 1 and `PerLocation` = 0

Result set 2 · If `ViewDetails` = 0 and `PerLocation` = 0 (default)

Result set 3 · If `ViewDetails` = 1 and `PerLocation` = 1

Result set 4 · If `ViewDetails` = 0 and `PerLocation` = 1