Direct-Fulfillment Read

pixiDFGetChanges

Returns the latest not yet notified fulfilment status changes of `FUT` order lines for a Direct Fulfillment supplier, with address, item and order data.

API call for Direct Fulfillment (DF) suppliers that fetches the order line changes they still have to process.

Authentication

The supplier is looked up in Suppliers by SupplNr. Secret must match the supplier's CustNr. The supplier must not be disabled and must have CanDoDirectFulfillment = 1.
If no supplier matches and the setting DFAPIAllowFullAccessWithoutSecret is 0 (or missing), a single row with Error = 403 is returned.
If no supplier matches but the setting is not 0, the call continues without a supplier filter and returns changes of all suppliers.

Selection

For every order line, only the newest OrderHistory entry (highest OrderHistKey) is considered, and only if all of these conditions apply:

the order line has status FUT;
the history status is an OrderStates state with FulfilmentStatus = 'Y';
the order line's SupplInfo equals the authenticated supplier (if one was found);
the shipping address (ASlocked) and the customer (SLocked) are not locked, unless LockedCustomers = 1;
OrderNrExternal matches OrderNRExternal, if that parameter is given;
the order header was created more than 5 minutes ago.

In addition, the history entry must have ClientNotified = 'N'. Order lines whose status is contained in ExcludeStatuses are skipped.

Output

If any order line appears more than once in the result, a single message row is returned instead of the data and the call stops.
Otherwise all rows are returned, sorted by StatusChangeDate. In databases whose name starts with pixi_PSP, they are sorted by ordernr, then StatusChangeDate.

Notes

The call is read-only. It does not set ClientNotified, so the same changes are returned again until they are marked as notified by another process. That process is not part of this code.
All tables are read WITH (NOLOCK), so uncommitted data may appear.
ExcludeStatuses is checked with CHARINDEX (substring match), so any separator works, e.g. STO,AUS. The inner selection already only returns FUT lines, so the parameter only matters if it contains FUT, which would suppress all rows.
If ExcludeStatuses is empty and the database name starts with pixi_PSP, it defaults to STO.
If SupplNr/Secret are wrong but DFAPIAllowFullAccessWithoutSecret is not 0, the data of all suppliers is returned.
Orders created less than 5 minutes ago are held back.
The temporary table is handled by util.TempTableDrop. That object is not attached.

Tables used

Suppliers · Read Settings · Read OrderHistory · Read Orderlines · Read Addresses · Read OrderHeader · Read Items · Read Customers · Read Currency · Read OrderStates · Read

Parameter	Type	Default	Description
`SupplNr`	varchar(4) string	NULL	Supplier number of the Direct Fulfillment supplier. Used for authentication and to filter order lines by `SupplInfo`. Example: `L001`
`Secret`	varchar(20) string	NULL	Secret of the supplier. Must match the supplier's `CustNr` in `Suppliers`. Example: `K10023`
`OrderNRExternal`	varchar(50) string	NULL	Optional external order number. Restricts the result to changes of this order; empty or NULL means all orders. Example: `SHOP-2024-004711`
`ExcludeStatuses`	varchar(200) string	NULL	Order line statuses to exclude, matched as substrings (e.g. `STO,AUS`). Defaults to `STO` in `pixi_PSP` databases when empty. Example: `STO`
`LockedCustomers`	bit boolean	0	Whether order lines with locked shipping addresses or locked customers are included. Allowed values `0` – Exclude lines whose shipping address (`ASlocked`) or customer (`SLocked`) is locked. `1` – Include lines regardless of address or customer locks. Example: `0`

Result set 1 · If no matching DF supplier is found and `DFAPIAllowFullAccessWithoutSecret` is `0`.

Authorization error row.

Column	Type	Description
`Error`	varchar(3) string	Error code, always `403`.
`ErrorMsg`	varchar(47) string	Error text.

Result set 2 · If an order line appears more than once in the collected changes.

Single message row with one unnamed column. No data is returned in this case.

Column	Type	Description
`(no column name)`	varchar(79) string	Message text. The column has no alias in the code.

Result set 3 · On successful authorization (or full access allowed) and no duplicate order lines.

One row per changed order line with shipping address, order, item and status data.

Column	Type	Description
`Anrede` Nullable	string	Salutation of the shipping address.
`CareOf` Nullable	string	Care-of line of the shipping address.
`FirstName` Nullable	string	First name of the shipping address.
`LastName` Nullable	string	Last name of the shipping address.
`Street` Nullable	string	Street of the shipping address.
`ZIP` Nullable	string	Postal code of the shipping address.
`HouseNr` Nullable	string	House number of the shipping address.
`City` Nullable	string	City of the shipping address.
`Country` Nullable	string	Country of the shipping address.
`Email` Nullable	string	E-mail address of the shipping address (`eMail`).
`Phone` Nullable	string	Phone number of the shipping address.
`Address_remarks` Nullable	string	Address remarks from the order header.
`AddrNoChargeVAT` Nullable	string	VAT-exemption flag of the shipping address.
`ShipInstruct` Nullable	string	Shipping instructions from the order header.
`Shipdate` Nullable	dateTime	Shipping date from the order header.
`ShipVendorCode` Nullable	string	Shipping vendor code from the order header.
`ArtName` Nullable	string	Item name on the order line.
`EANUPC` Nullable	string	EAN/UPC of the item.
`ItemNrSuppl` Nullable	string	Supplier item number.
`itemNrExternal` Nullable	string	External item number of the order line.
`Qty` Nullable	int	Ordered quantity.
`Price` Nullable	decimal	Price of the order line.
`Status` Nullable	string	Current order line status (always `FUT` given the filters).
`OrderHistKey`	int	Key of the latest order history entry.
`OrderLineRef`	int	Key of the order line.
`StatusChangeDate` Nullable	dateTime	Date of the status change in the order history.
`OrderDate` Nullable	dateTime	Order date of the order line.
`OrderNrExternal` Nullable	string	External order number.
`AddrShopID` Nullable	string	Shop ID of the billing address.
`oItemSpecialNote` Nullable	string	Special note on the order line.
`SupplInfo` Nullable	string	Supplier number assigned to the order line.
`APayment` Nullable	string	Payment type of the billing address.
`discount` Nullable	decimal	Discount from the order header.
`ShipCost` Nullable	decimal	Shipping costs from the order header.
`InvoiceNrExternal` Nullable	string	External invoice number of the order line.
`DF_Type` Nullable	string	Direct Fulfillment type of the order line.
`ordernr`	int	Pixi order number.
`CustomerLock` Nullable	string	Ship lock flag of the customer (`SLocked`).
`AddressLock` Nullable	string	Ship lock flag of the shipping address (`ASlocked`).
`OrderCurrencyCode` Nullable	string	Currency code of the order (`CodeA` from `pixi.Currency`).

Response example

<SqlRowSet1>
  <row>
    <Anrede>Frau</Anrede>
    <CareOf/>
    <FirstName>Anna</FirstName>
    <LastName>Muster</LastName>
    <Street>Hauptstraße</Street>
    <ZIP>80331</ZIP>
    <HouseNr>12</HouseNr>
    <City>München</City>
    <Country>DE</Country>
    <Email>anna.muster@example.com</Email>
    <Phone>089123456</Phone>
    <Address_remarks/>
    <AddrNoChargeVAT>N</AddrNoChargeVAT>
    <ShipInstruct/>
    <Shipdate>2024-05-14T00:00:00</Shipdate>
    <ShipVendorCode>DHL</ShipVendorCode>
    <ArtName>Kaffeemaschine X200</ArtName>
    <EANUPC>4012345678901</EANUPC>
    <ItemNrSuppl>X200-BLK</ItemNrSuppl>
    <itemNrExternal>SKU-4711</itemNrExternal>
    <Qty>1</Qty>
    <Price>129.90</Price>
    <Status>FUT</Status>
    <OrderHistKey>884512</OrderHistKey>
    <OrderLineRef>553210</OrderLineRef>
    <StatusChangeDate>2024-05-13T10:15:22</StatusChangeDate>
    <OrderDate>2024-05-13T09:58:01</OrderDate>
    <OrderNrExternal>SHOP-2024-004711</OrderNrExternal>
    <AddrShopID>WEB</AddrShopID>
    <oItemSpecialNote/>
    <SupplInfo>L001</SupplInfo>
    <APayment>PP</APayment>
    <discount>0.00</discount>
    <ShipCost>4.90</ShipCost>
    <InvoiceNrExternal/>
    <DF_Type>DF</DF_Type>
    <ordernr>120345</ordernr>
    <CustomerLock>N</CustomerLock>
    <AddressLock>N</AddressLock>
    <OrderCurrencyCode>EUR</OrderCurrencyCode>
  </row>
</SqlRowSet1>

Status	Message	Cause
403	Not Authorized. Secret or SupplNr is incorrect.	No enabled DF supplier matches `SupplNr` and `Secret` (`CustNr`), and `DFAPIAllowFullAccessWithoutSecret` is `0`. Pass a valid supplier number and secret.
ERROR	Duplicate Orderhistory Entries - export impossible, please contact pixi Support	An order line appears more than once in the collected changes. The data must be corrected by pixi Support.

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:pixiDFGetChanges>
      <!-- <SupplNr>L001</SupplNr> -->
      <!-- <Secret>K10023</Secret> -->
      <!-- <OrderNRExternal>SHOP-2024-004711</OrderNRExternal> -->
      <!-- <ExcludeStatuses>STO</ExcludeStatuses> -->
      <!-- <LockedCustomers>0</LockedCustomers> -->
    </ns1:pixiDFGetChanges>
  </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:pixiDFGetChanges>
      <!-- <SupplNr>L001</SupplNr> -->
      <!-- <Secret>K10023</Secret> -->
      <!-- <OrderNRExternal>SHOP-2024-004711</OrderNRExternal> -->
      <!-- <ExcludeStatuses>STO</ExcludeStatuses> -->
      <!-- <LockedCustomers>0</LockedCustomers> -->
    </ns1:pixiDFGetChanges>
  </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 = [
    // 'SupplNr' => 'L001',
    // 'Secret' => 'K10023',
    // 'OrderNRExternal' => 'SHOP-2024-004711',
    // 'ExcludeStatuses' => 'STO',
    // 'LockedCustomers' => '0',
];

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

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

SQL object:: dbo.pipiDFGetChanges