Direct-Fulfillment Read

pixiDFGetOrderlines

Returns the open direct fulfillment order lines (status FUT) of one order for a direct fulfillment supplier, authenticated by supplier number and secret.

Used by direct fulfillment (drop shipping) suppliers to fetch the order lines of one order they have to ship.

Authentication

Empty strings in SupplNr and Secret are treated as NULL.
The call looks for a supplier in Suppliers whose SupplNr equals SupplNr and whose CustNr equals Secret. The supplier must not be disabled and must have CanDoDirectFulfillment = 1.
If no such supplier exists and the setting DFAPIAllowFullAccessWithoutSecret is 0 (or missing), a single row with Error = '403' is returned and nothing else.
If the setting is not 0, the check is skipped and the call works without a valid secret.

Order lines returned

Only lines that meet all of these conditions:

The line belongs to the order OrderNR.
The line has status FUT, and its latest OrderHistory entry with status FUT is used (OrderHistKey, StatusChangeDate, ClientNotified).
The status FUT is marked as a fulfilment status in OrderStates (FulfilmentStatus = 'Y').
Unless AllOrderlines = 1, only lines that have not yet been confirmed (ClientNotified = 'N') are returned.
If SupplNr is given, only lines with SupplInfo = SupplNr are returned, and that supplier must be able to do direct fulfillment. If SupplNr is empty (only possible when the secret check is disabled), lines of all suppliers are returned.
The shipping address must not be locked (ASlocked) and the customer must not be locked (SLocked).
Order header, invoice address, customer and currency must exist (inner joins).

Item data

EANUPC, ItemNrSuppl and ItemNrInt are taken from the supplier-specific AddItems record (supplier = SupplInfo of the line, the most recently updated one); if no such value exists, the values from Items are used.

Duplicate rows are removed (DISTINCT). No sort order is defined.

Notes

The error is returned as a normal result row (Error, ErrorMsg), not as a SQL error. Check for the column Error in the response.
Secret is compared with the field CustNr of the supplier (the customer number under which the shop is known at the supplier).
If DFAPIAllowFullAccessWithoutSecret is enabled and SupplNr is left empty, the order lines of all suppliers of the order are returned. Keep this setting at 0 in production.
The call is read-only; it does not mark lines as confirmed. OrderlineConfirmed reflects ClientNotified of the latest FUT history entry.
All tables are read with NOLOCK, so uncommitted data can be returned.

Tables used

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

Parameter	Type	Default	Description
`OrderNR` Required	int	–	Number of the order whose direct fulfillment lines are returned. Example: `104523`
`SupplNr`	varchar(4) string	NULL	Supplier number of the direct fulfillment supplier. Used for authentication and to restrict the lines to this supplier. Empty string is treated as NULL. Example: `DF01`
`Secret`	varchar(20) string	NULL	Secret of the supplier; must match the field `CustNr` of the supplier. Empty string is treated as NULL. Example: `K-778812`
`AllOrderlines`	bit boolean	0	Controls whether already confirmed lines are included. Allowed values `0` – Only lines not yet confirmed (`ClientNotified = 'N'`). `1` – All `FUT` lines of the order, confirmed or not. Example: `0`

Result set 1 · If the supplier is authenticated or the setting `DFAPIAllowFullAccessWithoutSecret` is not `0`

One row per matching direct fulfillment order line; may be empty.

Column	Type	Description
`OrderLineRef`	int	Key of the order line (`OrderlineKey`).
`OrderNr`	int	Order number.
`ArtName` Nullable	string	Item name as stored on the order line.
`EANUPC` Nullable	string	EAN/UPC, supplier-specific from `AddItems` if available, otherwise from `Items`.
`ItemNrSuppl` Nullable	string	Supplier item number, supplier-specific from `AddItems` if available, otherwise from `Items`.
`itemNrExternal` Nullable	string	External item number of the order line.
`Qty` Nullable	int	Ordered quantity.
`Price` Nullable	decimal	Price of the order line.
`Status`	string	Status of the order line, always `FUT`.
`OrderHistKey`	int	Key of the latest `FUT` entry in the order history of the line.
`StatusChangeDate` Nullable	dateTime	Date of the status change of that history entry.
`OrderDate` Nullable	dateTime	Order date of the line.
`OrderNrExternal` Nullable	string	External (shop) order number.
`oItemSpecialNote` Nullable	string	Special note of the order line.
`SupplInfo` Nullable	string	Supplier number assigned to the order line.
`InvoiceNrExternal` Nullable	string	External invoice number of the order line.
`DF_Type` Nullable	string	Direct fulfillment type of the order line (`DF_type`).
`ItemRef`	int	Key of the item (`ItemKey`).
`OrderlineConfirmed` Nullable	string	`ClientNotified` flag of the latest `FUT` history entry (`N` = not yet confirmed).
`OrderCurrencyCode` Nullable	string	Currency code of the order (`CodeA` from `Currency`).
`BundleOrderlineRef` Nullable	int	Reference to the bundle order line, if the line is part of a bundle.
`ItemNrInt` Nullable	string	Internal item number, supplier-specific from `AddItems` if available, otherwise from `Items`.

Result set 2 · If no matching DF supplier is found for `SupplNr`/`Secret` and `DFAPIAllowFullAccessWithoutSecret` is `0`

Single error row instead of the order lines.

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

Response example

<SqlRowSet1>
  <row>
    <OrderLineRef>558812</OrderLineRef>
    <OrderNr>104523</OrderNr>
    <ArtName>Gartenstuhl Teak natur</ArtName>
    <EANUPC>4006381333931</EANUPC>
    <ItemNrSuppl>GS-TEAK-01</ItemNrSuppl>
    <itemNrExternal>SHOP-77812</itemNrExternal>
    <Qty>2</Qty>
    <Price>89.90</Price>
    <Status>FUT</Status>
    <OrderHistKey>9912034</OrderHistKey>
    <StatusChangeDate>2024-05-14T09:32:11</StatusChangeDate>
    <OrderDate>2024-05-14T08:15:00</OrderDate>
    <OrderNrExternal>WEB-2024-55123</OrderNrExternal>
    <oItemSpecialNote/>
    <SupplInfo>DF01</SupplInfo>
    <InvoiceNrExternal/>
    <DF_Type>DF</DF_Type>
    <ItemRef>33417</ItemRef>
    <OrderlineConfirmed>N</OrderlineConfirmed>
    <OrderCurrencyCode>EUR</OrderCurrencyCode>
    <BundleOrderlineRef>0</BundleOrderlineRef>
    <ItemNrInt>100245</ItemNrInt>
  </row>
</SqlRowSet1>

Status	Message	Cause
403	Not Authorized. Secret or SupplNr is incorrect.	No active supplier with `CanDoDirectFulfillment = 1` matches `SupplNr` and `Secret` (compared with `CustNr`), and the secret check is enabled. Pass the correct supplier number and secret.

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:pixiDFGetOrderlines>
      <OrderNR>104523</OrderNR>
      <!-- <SupplNr>DF01</SupplNr> -->
      <!-- <Secret>K-778812</Secret> -->
      <!-- <AllOrderlines>0</AllOrderlines> -->
    </ns1:pixiDFGetOrderlines>
  </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:pixiDFGetOrderlines>
      <OrderNR>104523</OrderNR>
      <!-- <SupplNr>DF01</SupplNr> -->
      <!-- <Secret>K-778812</Secret> -->
      <!-- <AllOrderlines>0</AllOrderlines> -->
    </ns1:pixiDFGetOrderlines>
  </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 = [
    'OrderNR' => '104523',
    // 'SupplNr' => 'DF01',
    // 'Secret' => 'K-778812',
    // 'AllOrderlines' => '0',
];

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

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

SQL object:: dbo.pipiDFGetOrderlines