pixiDispoGetItemBinLog

Exports stock movements from InvBinItemLog for an external disposition tool (LogControl).

Selection

Only log entries with IBILKEY > @ItemBinLogKeyStart, Qty <> 0 and UpdateDate between @DateStart and @DateEnd (both inclusive). If @DateStart is NULL, 1900-01-01 is used; if @DateEnd is NULL, the current date and time is used.
Entries are grouped by update timestamp, ForOrderline, location, item, reason, update employee and stock movement type. Groups whose quantity sum is 0 are dropped.
The first @RowCount groups (default 10000), ordered by their lowest log key, are taken. The lowest log key of a group is returned as ItemBinLogKey and PItemBinLogKey.

Movement type code A hard-coded type is derived per group:

0300: outflow (negative sum) not related to an order line
2000: outflow related to an order line (ForOrderline set and not 0)
0200: inflow booked by an employee whose name starts with the value of setting DispoInventoryUser
1000: all other movements

If setting DispoExportAllImportTypes is 1, mode 0 returns the Type of the joined StockMovementTypes record instead.

Supplier order data If the reason contains Picco Del.Nr. followed by a numeric delivery number and the movement has no order line, the procedure looks up the supplier delivery (SDelHeader/SDelDetail), its buying match and the related supplier order line. Order date, internal supplier order number and estimated delivery date are returned; if nothing is found, these columns contain '0'.

Output modes

@InventoryMode = 0: one row per group, ordered by ItemBinLogKey, with the absolute quantity and the location.
@InventoryMode = 1: movements without order line are summed up per item (type 0300 for a negative total, otherwise 0200); movements with order line are returned individually (type 2000 for outflow, 1000 for inflow). Both parts are combined with UNION and ordered by Type. This mode returns no EstimatedDeliveryDate and no LocationID.

For incremental export, pass the highest ItemBinLogKey of the previous call as @ItemBinLogKeyStart.

Notes

All reads use NOLOCK, so uncommitted data may be included.
SET NOCOUNT is intentionally not set; the intermediate SELECT ... INTO statements produce row-count messages.
fpixi_GetSetting returns '0' when a setting does not exist. If DispoInventoryUser is missing, the inventory user prefix becomes '0', not NULL, so inflows by employees whose name starts with 0 are typed 0200.
@DateEnd is compared with the full timestamp: a date like 20110820 means midnight, so movements later on that day are excluded.
UpdateDate is returned as text in format dd.mm.yyyy. In inventory mode, MAX(UpdateDate) compares these strings alphabetically, so the result is not necessarily the latest date.
In inventory mode, sums run across all locations of an item, @RowCount limits the underlying groups (not the output rows), and UNION removes identical rows. The column order differs from mode 0 (SupplOrderNr before SupplOrderDate) and the item column is named ItemKey instead of ITemKey.
The delivery number is taken from position 14 of Reason, which assumes the reason starts with Picco Del.Nr. .
An unused LEFT JOIN on SDelHeader via the part of UpdateEmp after / could multiply rows and inflate sums if several deliveries share that number.
Temporary tables are dropped via util.TempTableDrop (definition not attached).

Tables used

InvBinItemLog · Read Items · Read StockMovementTypes · Read SDelHeader · Read SDelDetail · Read BuyingMatch · Read SupplierOrderLine · Read SupplierOrder · Read Settings · Read

Parameter	Type	Default	Description
`ItemBinLogKeyStart` Required	int	–	Only log entries with a key greater than this value are exported. Pass 0 for a full export or the last exported `ItemBinLogKey` for an incremental export. Example: `0`
`DateStart`	datetime dateTime	NULL	Earliest update timestamp (inclusive). NULL means 1900-01-01. Example: `2011-08-01T00:00:00`
`DateEnd`	datetime dateTime	NULL	Latest update timestamp (inclusive). NULL means the current date and time. Example: `2011-08-20T23:59:59`
`InventoryMode`	bit boolean	0	Selects the output format: single movements or movements summed up per item. Allowed values `0` – One row per grouped movement, including location and estimated delivery date. `1` – Movements without order line summed per item (types 0300/0200), movements with order line individually (types 2000/1000). Example: `0`
`RowCount`	int	10000	Maximum number of grouped movements taken from the log. NULL means 10000. Example: `10000`

Result set 1 · If `@InventoryMode` = 0

One row per grouped stock movement, ordered by ItemBinLogKey.

Column	Type	Description
`PItemBinLogKey`	int	Lowest log key of the group (same as `ItemBinLogKey`).
`ItemBinLogKey`	int	Lowest log key of the group; use the highest value as next `@ItemBinLogKeyStart`.
`ITemKey`	int	Item key.
`UpdateDate`	varchar(10) string	Date of the movement as text `dd.mm.yyyy`.
`Qty`	decimal	Absolute quantity of the movement.
`Type` Nullable	string	Movement type code `0300`, `2000`, `0200` or `1000`; the `StockMovementTypes` type if setting `DispoExportAllImportTypes` = 1.
`SupplNR` Nullable	string	Supplier number of the item.
`ItemNrInt` Nullable	string	Internal item number.
`ItemNrSuppl` Nullable	string	Supplier item number, or the internal item number if empty.
`SupplOrderDate`	varchar(10) string	Order date of the related supplier order as `dd.mm.yyyy`, or `0`.
`SupplOrderNr`	string	Internal number of the related supplier order, or `0`.
`EstimatedDeliveryDate`	varchar(10) string	Estimated delivery date of the supplier order line as `dd.mm.yyyy`, or `0`.
`LocationID` Nullable	string	Location (warehouse) ID of the movement.

Result set 2 · If `@InventoryMode` = 1

Movements without order line summed per item plus individual movements with order line, ordered by Type.

Column	Type	Description
`PItemBinLogKey`	int	Highest log key of the summed item (or the log key of the single movement).
`ItemBinLogKey`	int	Same as `PItemBinLogKey`.
`ItemKey`	int	Item key.
`UpdateDate`	varchar(10) string	Date as `dd.mm.yyyy`; for summed rows the alphabetical maximum.
`Qty`	decimal	Absolute (summed) quantity.
`Type`	varchar(4) string	`0300`/`0200` for summed movements without order line, `2000`/`1000` for movements with order line.
`SupplNr` Nullable	string	Supplier number of the item.
`ItemNrInt` Nullable	string	Internal item number.
`ItemNrSuppl` Nullable	string	Supplier item number or internal item number.
`SupplOrderNr`	string	Internal supplier order number, or `0`.
`SupplOrderDate`	varchar(10) string	Supplier order date as `dd.mm.yyyy`, or `0`.

Response example

<SqlRowSet1>
  <row>
    <PItemBinLogKey>154873</PItemBinLogKey>
    <ItemBinLogKey>154873</ItemBinLogKey>
    <ITemKey>4711</ITemKey>
    <UpdateDate>02.08.2011</UpdateDate>
    <Qty>5</Qty>
    <Type>1000</Type>
    <SupplNR>L100</SupplNR>
    <ItemNrInt>ART-4711</ItemNrInt>
    <ItemNrSuppl>SUP-998</ItemNrSuppl>
    <SupplOrderDate>25.07.2011</SupplOrderDate>
    <SupplOrderNr>BE-2011-0815</SupplOrderNr>
    <EstimatedDeliveryDate>01.08.2011</EstimatedDeliveryDate>
    <LocationID>001</LocationID>
  </row>
  <row>
    <PItemBinLogKey>154880</PItemBinLogKey>
    <ItemBinLogKey>154880</ItemBinLogKey>
    <ITemKey>4711</ITemKey>
    <UpdateDate>03.08.2011</UpdateDate>
    <Qty>1</Qty>
    <Type>2000</Type>
    <SupplNR>L100</SupplNR>
    <ItemNrInt>ART-4711</ItemNrInt>
    <ItemNrSuppl>SUP-998</ItemNrSuppl>
    <SupplOrderDate>0</SupplOrderDate>
    <SupplOrderNr>0</SupplOrderNr>
    <EstimatedDeliveryDate>0</EstimatedDeliveryDate>
    <LocationID>001</LocationID>
  </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:pixiDispoGetItemBinLog>
      <ItemBinLogKeyStart>0</ItemBinLogKeyStart>
      <!-- <DateStart>2011-08-01T00:00:00</DateStart> -->
      <!-- <DateEnd>2011-08-20T23:59:59</DateEnd> -->
      <!-- <InventoryMode>0</InventoryMode> -->
      <!-- <RowCount>10000</RowCount> -->
    </ns1:pixiDispoGetItemBinLog>
  </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:pixiDispoGetItemBinLog>
      <ItemBinLogKeyStart>0</ItemBinLogKeyStart>
      <!-- <DateStart>2011-08-01T00:00:00</DateStart> -->
      <!-- <DateEnd>2011-08-20T23:59:59</DateEnd> -->
      <!-- <InventoryMode>0</InventoryMode> -->
      <!-- <RowCount>10000</RowCount> -->
    </ns1:pixiDispoGetItemBinLog>
  </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 = [
    'ItemBinLogKeyStart' => '0',
    // 'DateStart' => '2011-08-01T00:00:00',
    // 'DateEnd' => '2011-08-20T23:59:59',
    // 'InventoryMode' => '0',
    // 'RowCount' => '10000',
];

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

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

Notes

Tables used

Result set 1 · If @InventoryMode = 0

Result set 2 · If @InventoryMode = 1

Response example

Result set 1 · If `@InventoryMode` = 0

Result set 2 · If `@InventoryMode` = 1