pixiReport_StockMovementsForItemsWithBnBbd

Wrapper around the internal report procedure Report_StockMovementsForItemsWithBnBbd. It lists entries of the stock movement log (InvBinItemLog) for items that carry the item tag -6 or -7 (batch number / best-before date).

Parameter handling

Empty values (and 0 for the integer parameters) are treated as NULL, which means "no filter".
If DateFrom is missing, the start of the current day is used. If DateTo is missing, the end of the current day is used. The time part of both dates is ignored: DateFrom is moved to 00:00:00 and DateTo to 23:59:59.997 of the given day.

Parameter checks (each failure returns one Status/Message row and stops the call)

LocID must exist in Location.
ItemTag, if given, must be -6 or -7.
UserName must occur as a substring of UpdateEmp in at least one log entry.
SMTKey must exist in StockMovementTypes.
Category must exist in TontrArten.

SupplNr is not checked.

Filters

Movement date (UpdateDate) between DateFrom and DateTo. This filter is always active.
SMTKey: the stock movement type of the log entry.
UserName: substring match (LIKE '%…%') on the user who made the entry.
LocID: the location of the current bin (InvBin.BinLocID).
ItemTag: only items that carry this tag (-6 or -7).
Category: the item's ConfigType.
SupplNr: the supplier taken from the latest supplier information valid at the movement date.

Output One row per movement with the item, bin, moved quantity, batch number, best-before date, movement type with its translated name, user, historical supplier price and database currency. The report sorts the rows by movement date in ascending order.

Notes

All tables are read with NOLOCK, so uncommitted data can appear in the result.
The report itself sorts by movement date. The final SELECT from the temporary table has no ORDER BY, so the row order is not strictly guaranteed.
Because empty values are mapped to NULL, a SMTKey or ItemTag of 0 and a date of 1900-01-01 count as "not set".
Stock is SUM(Items.Quantity). It is the item's total stock, not the stock in the bin. If several joined rows collapse into one group, the value can be a multiple of that stock.
BinName holds the current bin name. For deleted bins it holds the historical name followed by Deleted. Deleted bins drop out of the result when LocID is set, because the location filter uses the current bin only.
If a movement has neither a current nor a deleted bin record, BinName is empty and the insert into the result table fails. This returns the "Unexpected error" row.
SMTName is the translation with LanguageID = 2.
UserName is the part of UpdateEmp before the first /.
The precheck for UserName (and for LocID) also fails when InvBinItemLog (or Location) is empty, even if no value was passed.
The supplier data come from the function fPixi_GetLatestSupplierInfo. Its definition is not attached, so the exact selection logic is not documented here.
The report runs as dynamic SQL via sp_executesql.
The report also computes the columns ParamDateFrom … ParamSupplier, but they are not returned to the caller.
A code comment names the tags -6 and -7 together as "BN & BBD".

Tables used

Location · Read InvBinItemLog · Read StockMovementTypes · Read TontrArten · Read Translation · Read TagMatch · Read ItemCode · Read Items · Read InvBin · Read InvBinHist · Read Tag · Read Suppliers · Read

Parameter	Type	Default	Description
`DateFrom`	datetime dateTime	NULL	Start of the period (movement date). The time part is ignored and the day starts at 00:00:00. Default: start of the current day. Example: `2024-03-01T00:00:00`
`DateTo`	datetime dateTime	NULL	End of the period (movement date). The time part is ignored and the whole day up to 23:59:59.997 is included. Default: end of the current day. Example: `2024-03-31T00:00:00`
`SMTKey`	int	NULL	Key of the stock movement type (`StockMovementTypes.SMTKey`). It must exist. `NULL` or `0` means all types. Example: `5`
`UserName`	varchar(100) string	NULL	Substring of the user name stored in the movement log (`UpdateEmp`). At least one log entry must match. Example: `mmueller`
`LocID`	varchar(3) string	NULL	Location ID (`Location.LocID`). It must exist. Filters on the location of the current bin. Example: `001`
`ItemTag`	int	NULL	Restricts the result to items with this tag. Only `-6` and `-7` are accepted. `NULL` or `0` means both. Allowed values `-6` – System item tag -6 (one of the BN/BBD tags) `-7` – System item tag -7 (one of the BN/BBD tags) Example: `-6`
`Category`	varchar(4) string	NULL	Item category (`TontrArten.TontrArt`). It must exist and is compared with `Items.ConfigType`. Example: `STD`
`SupplNr`	varchar(4) string	NULL	Supplier number. It is compared with the supplier of the latest supplier information valid at the movement date. It is not validated. Example: `L001`

Result set 1 · On success (all parameter checks passed)

Stock movements of BN/BBD items, one row per movement.

Column	Type	Description
`EANUPC` Nullable	varchar(50) string	EAN/UPC of the item
`ItemName` Nullable	varchar(120) string	Item name
`BinName`	varchar(50) string	Bin name; for deleted bins the historical name followed by ' Deleted'
`Stock` Nullable	int	Sum of `Items.Quantity` (total stock of the item)
`MovedQty`	int	Quantity moved in this movement
`HistTotalSupplPrice` Nullable	money decimal	Supplier price valid at the movement date multiplied by the moved quantity
`Date` Nullable	datetime dateTime	Date and time of the movement
`SMTType`	varchar(4) string	Type code of the stock movement type
`SMTName` Nullable	varchar(max) string	Translated name of the stock movement type (language ID 2)
`BatchNumber` Nullable	varchar(50) string	Batch number of the movement
`BestBeforeDate` Nullable	datetime dateTime	Best-before date of the movement
`UserName` Nullable	varchar(100) string	User who made the movement (part of `UpdateEmp` before the first '/')
`DatabaseCurrencyCode`	varchar(3) string	Currency code of the database
`SupplNr` Nullable	varchar(4) string	Supplier number from the latest supplier information at the movement date
`ItemKey` Nullable	int	Internal item key
`ItemNrInt` Nullable	varchar(50) string	Internal item number
`InternalItemNumber` Nullable	varchar(50) string	Item code of code type 3 from `pixi.ItemCode`

Result set 2 · If a parameter check fails or an unexpected error occurs

Single error row. The procedure returns code 2 for check errors and 3 for unexpected errors.

Column	Type	Description
`Status`	varchar(5) string	Always 'Error'
`Message`	varchar(max) string	Error text

Response example

<SqlRowSet1>
  <row>
    <EANUPC>4012345678901</EANUPC>
    <ItemName>Bio Honig 500g</ItemName>
    <BinName>A-01-03</BinName>
    <Stock>120</Stock>
    <MovedQty>-5</MovedQty>
    <HistTotalSupplPrice>-17.5000</HistTotalSupplPrice>
    <Date>2024-03-12T10:15:22</Date>
    <SMTType>OUT</SMTType>
    <SMTName>Ausbuchung MHD abgelaufen</SMTName>
    <BatchNumber>CH2023-117</BatchNumber>
    <BestBeforeDate>2024-03-10T00:00:00</BestBeforeDate>
    <UserName>mmueller</UserName>
    <DatabaseCurrencyCode>EUR</DatabaseCurrencyCode>
    <SupplNr>L001</SupplNr>
    <ItemKey>4711</ItemKey>
    <ItemNrInt>HON-500</ItemNrInt>
    <InternalItemNumber>100234</InternalItemNumber>
  </row>
</SqlRowSet1>

Status	Message	Cause
Error	Location "{LocID}" does not exist.	`LocID` does not exist in `Location`. This error also occurs if the `Location` table is empty. Pass an existing location ID.
Error	Only item tag with ID -6 and -7 are allowed.	`ItemTag` is set to a value other than -6 or -7.
Error	User with user name: "{UserName}" does not exist.	No movement log entry has an `UpdateEmp` that contains `UserName`. This error also occurs if the log is empty.
Error	Stock movement type with SMTKey: "{SMTKey}" does not exist.	`SMTKey` does not exist in `StockMovementTypes`.
Error	Category with ID: "{Category}" does not exist.	`Category` does not exist in `TontrArten`.
Error	Unexpected error while getting the report values. ({ERROR_MESSAGE})	The internal report failed, for example when a movement has no bin name and the insert into the result table fails.

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_StockMovementsForItemsWithBnBbd>
      <!-- <DateFrom>2024-03-01T00:00:00</DateFrom> -->
      <!-- <DateTo>2024-03-31T00:00:00</DateTo> -->
      <!-- <SMTKey>5</SMTKey> -->
      <!-- <UserName>mmueller</UserName> -->
      <!-- <LocID>001</LocID> -->
      <!-- <ItemTag>-6</ItemTag> -->
      <!-- <Category>STD</Category> -->
      <!-- <SupplNr>L001</SupplNr> -->
    </ns1:pixiReport_StockMovementsForItemsWithBnBbd>
  </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_StockMovementsForItemsWithBnBbd>
      <!-- <DateFrom>2024-03-01T00:00:00</DateFrom> -->
      <!-- <DateTo>2024-03-31T00:00:00</DateTo> -->
      <!-- <SMTKey>5</SMTKey> -->
      <!-- <UserName>mmueller</UserName> -->
      <!-- <LocID>001</LocID> -->
      <!-- <ItemTag>-6</ItemTag> -->
      <!-- <Category>STD</Category> -->
      <!-- <SupplNr>L001</SupplNr> -->
    </ns1:pixiReport_StockMovementsForItemsWithBnBbd>
  </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 = [
    // 'DateFrom' => '2024-03-01T00:00:00',
    // 'DateTo' => '2024-03-31T00:00:00',
    // 'SMTKey' => '5',
    // 'UserName' => 'mmueller',
    // 'LocID' => '001',
    // 'ItemTag' => '-6',
    // 'Category' => 'STD',
    // 'SupplNr' => 'L001',
];

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

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