pixiReport_ItemsOnStockWithBatchManagement

Exposes the pixi* report Items on stock with BN/BBD (batch number / best-before date) as an API call.

Normalisation: empty strings are treated as NULL for all text filters. ItemTag = 0 is treated as NULL. If BestBeforeDateFrom or BestBeforeDateTo is missing (or 1900-01-01), it defaults to the start or end of the current day.
Prechecks: each given filter is validated. The call stops with an Error row if:
- LocId is not in Location,
- ItemTag is not -6 or -7,
- Ean is not a barcode (code type 1) in pixi.ItemCode,
- ItemNrInt is not a shop item number (code type 2) in pixi.ItemCode,
- BinGroup is not in InvBin,
- SupplierNr is not in Suppliers,
- Category matches neither TontrArt nor TontrText in TontrArten,
- BatchNumber is not in InvBinItem.
Report: the internal report procedure Report_ItemsOnStockWithBatchManagement is executed:
- Only items tagged with tag -6 (batch number) or -7 (best-before date) are considered.
- Bin stock from InvBinItem is summed per item, bin, batch number and best-before date (QtyInBin).
- The best-before date filter is always applied. Rows whose BestBeforeDate lies between the two dates are included, and so are rows without a best-before date.
- Optional filters:
  - BatchNumber: exact batch number.
  - LocId: location of the bin.
  - Ean: barcode of the item.
  - ItemNrInt: shop item number.
  - ItemTag: the item must carry this tag.
  - SupplierNr: the item is assigned to this supplier in AddItems.
  - BinGroup: bin group.
  - Category: the item's ConfigType, matched by code or text.
The rows are sorted by ItemName, then BinName, and returned.

Notes

Default date range is today only. Without BestBeforeDateFrom/BestBeforeDateTo, only stock expiring today or without a best-before date is returned. Pass a wide range to get all batches.
The dates are passed to the inner report as DATE, so the time part is dropped. A BestBeforeDate on the end day with a time after midnight may be excluded.
The prechecks only test whether a matching record exists anywhere. For example, the Ean check accepts any existing barcode, not only barcodes of batch-managed items. Without a filter value, they still require at least one row in the respective table. An empty database or empty table therefore produces the corresponding error with an empty value.
The inner report builds its WHERE clause as dynamic SQL. Any SQL error raised there is caught and returned as an Error row with the original error text.
All reads use NOLOCK, so uncommitted data may appear.
The report internally also produces parameter echo columns (ParamBatchNumber etc.). These are not returned to the caller.

Tables used

Location · Read ItemCode · Read InvBin · Read Suppliers · Read TontrArten · Read InvBinItem · Read Items · Read TagMatch · Read Tag · Read AddItems · Read

Parameter	Type	Default	Description
`BatchNumber`	varchar(50) string	NULL	Filter by exact batch number. Must exist in `InvBinItem`. Empty means no filter. Example: `L2024-0815`
`BestBeforeDateFrom`	datetime dateTime	NULL	Start of the best-before date range. Defaults to the start of the current day; only the date part is used. Example: `2025-01-01T00:00:00`
`BestBeforeDateTo`	datetime dateTime	NULL	End of the best-before date range. Defaults to the end of the current day; only the date part is used. Example: `2025-12-31T23:59:59`
`LocId`	varchar(3) string	NULL	Location ID. Only bins of this location are included. Must exist in `Location`. Example: `001`
`Ean`	varchar(13) string	NULL	Barcode (item code type 1) of the item. Must exist in `pixi.ItemCode`. Example: `4006381333931`
`ItemTag`	int	NULL	Restricts to items carrying this tag. `0` or empty means no filter. Allowed values `-6` – Items with batch number management (BN) `-7` – Items with best-before date management (BBD) Example: `-6`
`SupplierNr`	varchar(4) string	NULL	Supplier number. Only items assigned to this supplier (`AddItems`) are included. Must exist in `Suppliers`. Example: `0012`
`BinGroup`	varchar(20) string	NULL	Bin group. Only bins of this group are included. Must exist in `InvBin`. Example: `FOOD`
`Category`	varchar(50) string	NULL	Item category, given as code (`TontrArt`) or text (`TontrText`) from `TontrArten`. Matches the item's `ConfigType`. Example: `Lebensmittel`
`ItemNrInt`	varchar(50) string	NULL	Shop item number (item code type 2). Must exist in `pixi.ItemCode`. Example: `100234`

Result set 1 · On success (all prechecks passed, no error in the report)

Stock per item, bin, batch number and best-before date, sorted by item name and bin name.

Column	Type	Description
`ItemKey`	int	Internal item key
`ItemNrSuppl` Nullable	varchar(50) string	Supplier item number
`ItemNrInt` Nullable	varchar(50) string	Shop item number
`EANUPC` Nullable	varchar(13) string	EAN/UPC from the item master
`ItemName` Nullable	varchar(120) string	Item name
`BatchNumber` Nullable	varchar(50) string	Batch number
`BestBeforeDate` Nullable	datetime dateTime	Best-before date
`BinName`	varchar(50) string	Bin name
`BinGroup`	varchar(20) string	Bin group
`QtyInBin` Nullable	int	Summed quantity in the bin for this batch/best-before date

Result set 2 · If a precheck fails or the report raises an error

Single status row describing the error.

Column	Type	Description
`Status`	varchar(5) string	Always `Error`
`Message`	varchar string	Error message

Response example

<SqlRowSet1>
  <row>
    <ItemKey>4711</ItemKey>
    <ItemNrSuppl>SUP-998</ItemNrSuppl>
    <ItemNrInt>100234</ItemNrInt>
    <EANUPC>4006381333931</EANUPC>
    <ItemName>Bio Honig 500g</ItemName>
    <BatchNumber>L2024-0815</BatchNumber>
    <BestBeforeDate>2025-06-30T00:00:00</BestBeforeDate>
    <BinName>A-01-03</BinName>
    <BinGroup>FOOD</BinGroup>
    <QtyInBin>24</QtyInBin>
  </row>
  <row>
    <ItemKey>4711</ItemKey>
    <ItemNrSuppl>SUP-998</ItemNrSuppl>
    <ItemNrInt>100234</ItemNrInt>
    <EANUPC>4006381333931</EANUPC>
    <ItemName>Bio Honig 500g</ItemName>
    <BatchNumber>L2024-0901</BatchNumber>
    <BestBeforeDate>2025-09-15T00:00:00</BestBeforeDate>
    <BinName>A-01-04</BinName>
    <BinGroup>FOOD</BinGroup>
    <QtyInBin>12</QtyInBin>
  </row>
</SqlRowSet1>

Status	Message	Cause
Error	Location "{LocId}" does not exist.	`LocId` is not found in `Location`. Pass an existing location ID.
Error	Only item tag with ID -6 and -7 are allowed.	`ItemTag` has a value other than `-6`, `-7`, `0` or empty.
Error	Item with EAN: "{Ean}" does not exist.	No barcode (code type 1) matching `Ean` exists in `pixi.ItemCode`.
Error	Item with Shop item number: "{ItemNrInt}" does not exist.	No shop item number (code type 2) matching `ItemNrInt` exists in `pixi.ItemCode`.
Error	Bin group with number: "{BinGroup}" does not exist.	No bin with this `BinGroup` exists in `InvBin`.
Error	Supplier with supplier number: "{SupplierNr}" does not exist.	`SupplierNr` is not found in `Suppliers`.
Error	Category with ID: "{Category}" does not exist.	`Category` matches neither `TontrArt` nor `TontrText` in `TontrArten`.
Error	Batch number: "{BatchNumber}" does not exist.	No bin stock row with this batch number exists in `InvBinItem`.
Error	Unexpected error while getting the report values. ({ERROR_MESSAGE})	A SQL error occurred while running the internal report (e.g. in its dynamic SQL). The original error text is included.

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:pixiReport_ItemsOnStockWithBatchManagement>
      <!-- <BatchNumber>L2024-0815</BatchNumber> -->
      <!-- <BestBeforeDateFrom>2025-01-01T00:00:00</BestBeforeDateFrom> -->
      <!-- <BestBeforeDateTo>2025-12-31T23:59:59</BestBeforeDateTo> -->
      <!-- <LocId>001</LocId> -->
      <!-- <Ean>4006381333931</Ean> -->
      <!-- <ItemTag>-6</ItemTag> -->
      <!-- <SupplierNr>0012</SupplierNr> -->
      <!-- <BinGroup>FOOD</BinGroup> -->
      <!-- <Category>Lebensmittel</Category> -->
      <!-- <ItemNrInt>100234</ItemNrInt> -->
    </ns1:pixiReport_ItemsOnStockWithBatchManagement>
  </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:pixiReport_ItemsOnStockWithBatchManagement>
      <!-- <BatchNumber>L2024-0815</BatchNumber> -->
      <!-- <BestBeforeDateFrom>2025-01-01T00:00:00</BestBeforeDateFrom> -->
      <!-- <BestBeforeDateTo>2025-12-31T23:59:59</BestBeforeDateTo> -->
      <!-- <LocId>001</LocId> -->
      <!-- <Ean>4006381333931</Ean> -->
      <!-- <ItemTag>-6</ItemTag> -->
      <!-- <SupplierNr>0012</SupplierNr> -->
      <!-- <BinGroup>FOOD</BinGroup> -->
      <!-- <Category>Lebensmittel</Category> -->
      <!-- <ItemNrInt>100234</ItemNrInt> -->
    </ns1:pixiReport_ItemsOnStockWithBatchManagement>
  </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 = [
    // 'BatchNumber' => 'L2024-0815',
    // 'BestBeforeDateFrom' => '2025-01-01T00:00:00',
    // 'BestBeforeDateTo' => '2025-12-31T23:59:59',
    // 'LocId' => '001',
    // 'Ean' => '4006381333931',
    // 'ItemTag' => '-6',
    // 'SupplierNr' => '0012',
    // 'BinGroup' => 'FOOD',
    // 'Category' => 'Lebensmittel',
    // 'ItemNrInt' => '100234',
];

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

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