Stock Read

pixiExportItemStock

Returns all items with a positive exportable stock for one location or all locations, together with item numbers, EAN, name, manufacturer, prices and category.

Exports a stock list of all items whose calculated stock is greater than 0.

Location selection

The Pixi setting ExportStock_locations is read first. An empty value means ALL and 0 means 001.
If Location is not empty, it overrides the setting. Because the default of Location is ALL, the setting only applies if the caller sends an empty value or NULL.
ALL covers all locations. Any other value is treated as a single location ID (LocID).

Stock calculation

The stock is calculated per item by the function fPixi_GetStockForExport:

Normal items: the sum of BinItemQty over all bins (InvBin) in locations that are not flagged as special stock (SpecialStock <> 1), restricted to the selected location unless ALL is used.
Bundle items (items referenced in BundleItems): Items.Quantity for ALL, otherwise LocationQuantity from ItemLocationSpecific for the selected non-special location.

Output

The call reads all items from Items and left-joins TontrArten for the category text. Only rows with Stock > 0 are returned. No item status filter and no sort order are applied.

Notes

The stock function is called with the fixed value 'N' for its physical-stock flag. The function only subtracts open order quantities (status ANG, or HAL within the last HalOrdersActiveDays days) or reservations when this flag equals '0'. As a result, no order or reservation quantities are deducted, and the exported value is the physical bin stock.
The settings StockExportUsePhysicalStock and OldStockExport are read but not used. ReservePrepaymentOrders, ShowLocationStockInItemSearch and HalOrdersActiveDays are passed to the stock function but have no effect on the result, for the reason above. HalOrdersActiveDays = 0 is replaced by 7.
All tables are read with NOLOCK, except InvBinItem inside the stock function, which is read without the hint.
The stock is calculated by a scalar function for every item in Items. On large item bases this can be slow.
If a location ID that does not exist is passed, the result is empty.
The join condition TontrArt = ConfigType does not name the tables. Which column belongs to Items and which to TontrArten cannot be determined from the code.
The header comment mentions XML input, but the procedure has no XML parameter.

Parameters

Parameter Type Default Description

Parameter	Type	Default	Description
`Location`	varchar(3) string	ALL	Location ID whose stock is exported, or `ALL` for all locations. An empty value or `NULL` falls back to the setting `ExportStock_locations`. Allowed values `ALL` – Stock over all locations except special stock locations. `{LocID}` – Stock of this single location only. – Use the setting `ExportStock_locations` (empty setting = `ALL`, `0` = `001`). Example: `001`

Location

varchar(3)
string

ALL

Location ID whose stock is exported, or ALL for all locations. An empty value or NULL falls back to the setting ExportStock_locations.

Allowed values

ALL – Stock over all locations except special stock locations.
{LocID} – Stock of this single location only.
– Use the setting `ExportStock_locations` (empty setting = `ALL`, `0` = `001`).

Example: 001

Response

Result set 1 · Always; contains only items with a stock greater than 0 (may be empty).

One row per item with positive exportable stock.

Column	Type	Description
`ItemNrSuppl` Nullable	string	Supplier item number (`Items.ItemNrSuppl`).
`EANUPC` Nullable	string	EAN/UPC barcode of the item.
`ItemName` Nullable	string	Item name.
`ItemNrInt` Nullable	string	Internal Pixi item number.
`Stock`	int	Exportable stock calculated by `fPixi_GetStockForExport` for the selected location(s); always > 0.
`Manufacturer` Nullable	string	Manufacturer (`Items.Artist`).
`Price` Nullable	decimal	Sales price (`Items.Price`).
`SupplPrice` Nullable	decimal	Purchase price (`Items.SupplPrice`).
`Category` Nullable	string	Category text from `TontrArten.TontrText`; `NULL` if no matching entry exists.

Response example

<SqlRowSet1>
  <row>
    <ItemNrSuppl>SUP-4711</ItemNrSuppl>
    <EANUPC>4006381333931</EANUPC>
    <ItemName>Kugelschreiber blau</ItemName>
    <ItemNrInt>100245</ItemNrInt>
    <Stock>37</Stock>
    <Manufacturer>Stabilo</Manufacturer>
    <Price>2.9900</Price>
    <SupplPrice>1.1000</SupplPrice>
    <Category>Schreibwaren</Category>
  </row>
  <row>
    <ItemNrSuppl>SUP-4712</ItemNrSuppl>
    <EANUPC>4006381333948</EANUPC>
    <ItemName>Kugelschreiber rot</ItemName>
    <ItemNrInt>100246</ItemNrInt>
    <Stock>12</Stock>
    <Manufacturer>Stabilo</Manufacturer>
    <Price>2.9900</Price>
    <SupplPrice>1.1000</SupplPrice>
    <Category>Schreibwaren</Category>
  </row>
</SqlRowSet1>

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:pixiExportItemStock>
      <!-- <Location>001</Location> -->
    </ns1:pixiExportItemStock>
  </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:pixiExportItemStock>
      <!-- <Location>001</Location> -->
    </ns1:pixiExportItemStock>
  </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 = [
    // 'Location' => '001',
];

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

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

Tables used

Settings · Read Items · Read TontrArten · Read BundleItems · Read InvBinItem · Read InvBin · Read Location · Read Orderlines · Read Reservation · Read OrderStates · Read ItemLocationSpecific · Read

SQL object:: dbo.pipiExportItemStock