Suppliers Read

pixiGetSupplierOrder

Returns supplier orders line by line, optionally filtered by supplier, order number, date range and status, with simple row-based paging.

Returns one row per supplier order line, together with header data of the supplier order, the supplier, the item, the delivery location and the users who created and last modified the records.

Filters (all combined with AND):

SupplNr: only orders of this supplier. Empty string counts as not set.
SupplOrderNr: only the order with this internal number (InternalNumber). 0 counts as not set.
Status: only orders whose status code matches, e.g. NEW, CLS, BES, CON. Empty string counts as not set.
DateFrom / DateTo: the order date is compared; if an order has no order date, its creation date is used instead.
- DateFrom is rounded down to the start of the day. If it is missing or before 1970-01-01, 1970-01-01 00:00 is used.
- DateTo is extended to the end of the day (23:59:59.997). If it is missing or before 1970-01-01, the current date and time is used.

Paging: the rows are numbered by RowNr in ascending order of the internal supplier order ID. Only rows with RowNr greater than Start are returned, at most RowCount rows. To fetch the next page, increase Start by RowCount.

Calculated columns:

OrderQty is the still open quantity: ordered quantity minus undelivered quantity minus delivered quantity.
SupplPrice is the price of the order line; if it is 0 or empty, the supplier price from the additional supplier data of the item for this supplier is used, otherwise the supplier price of the item master record.

Notes

All tables are read with NOLOCK, so uncommitted data may be returned.
Paging counts order lines, not orders: an order with several lines can be split across two pages.
RowNr is ordered only by the supplier order ID; lines of the same order get numbers in no defined order, and the final result has no ORDER BY. Paging across lines of one order is therefore not strictly deterministic.
All joins are inner joins: orders without lines, and orders or lines whose type, status, delivery location, supplier, item or creating/modifying user cannot be resolved, are not returned.
If DateTo is omitted, the current time (not the end of the day) is the upper bound.
The column name SupplierOrderLineUpateUser contains a typo (Upate) and must be read exactly like that.
SupplierOrderlineKey and SupplierOrderlineId contain the same value, as do SupplierOrderNRExternal and ExtOrderNr.
The fallback price from the additional supplier data is taken with TOP 1 without sort order; if several entries exist for item and supplier, the chosen one is not defined.
The status values NEW, CLS, BES, CON are only listed in a code comment; the actual codes come from the supplier order status table.

Tables used

SupplierOrder · Read SupplierOrderType · Read Location · Read SupplierOrderLine · Read items · Read SupplierOrderStatus · Read Suppliers · Read UCS_USERS · Read AddItems · Read

Parameter	Type	Default	Description
`SupplNr`	varchar(4) string	NULL	Supplier number. Only orders of this supplier are returned. NULL or empty string: all suppliers. Example: `L001`
`SupplOrderNr`	int	NULL	Internal supplier order number. NULL or 0: all orders. Example: `10452`
`DateFrom`	datetime dateTime	NULL	Start of the date range (order date, or creation date if no order date). Rounded down to the start of the day; NULL or before 1970-01-01 means 1970-01-01. Example: `2024-01-01T00:00:00`
`DateTo`	datetime dateTime	NULL	End of the date range. Extended to the end of the given day; NULL or before 1970-01-01 means the current date and time. Example: `2024-03-31T00:00:00`
`Status`	varchar(3) string	NULL	Status code of the supplier order. NULL or empty string: all statuses. The code comment lists NEW, CLS, BES and CON. Allowed values `NEW` – New supplier order (as listed in the code comment). `CLS` – Closed supplier order (as listed in the code comment). `BES` – Ordered supplier order (as listed in the code comment). `CON` – Confirmed supplier order (as listed in the code comment). Example: `BES`
`RowCount`	int	1000	Maximum number of rows (order lines) to return. Example: `500`
`Start`	int	0	Paging offset: only rows with `RowNr` greater than this value are returned. Example: `0`

Result set 1 · Always (may be empty)

One row per supplier order line matching the filters, limited by RowCount and Start.

Column	Type	Description
`RowNr`	bigint long	Running row number for paging, by supplier order ID.
`SupplierOrderlineKey`	int	ID of the supplier order line.
`SupplierOrderNR` Nullable	int	Internal supplier order number.
`SupplierOrderNRExternal` Nullable	string	External code of the supplier order.
`OrderDate` Nullable	datetime dateTime	Order date.
`SupplNr` Nullable	string	Supplier number.
`Status` Nullable	varchar(3) string	Status code of the supplier order.
`EstimatedDelivery` Nullable	datetime dateTime	Estimated delivery date of the line.
`Note` Nullable	varchar(1024) string	Note for the supplier from the order header.
`EANUPC` Nullable	string	EAN/UPC of the item.
`ItemNrSuppl` Nullable	string	Supplier item number of the line.
`OrderQty`	decimal	Open quantity: ordered minus undelivered minus delivered quantity.
`VPE` Nullable	decimal	Packaging unit of the line.
`ItemName` Nullable	string	Item name.
`SupplierOrderUpdateDate` Nullable	datetime dateTime	Last modification date of the order header.
`SupplierOrderUpdateUser` Nullable	string	User who last modified the order header.
`SupplierOrderlineUpdateDate` Nullable	datetime dateTime	Last modification date of the order line.
`SupplierOrderLineUpateUser` Nullable	string	User who last modified the order line (column name contains a typo).
`ItemKey`	int	Internal item key.
`SupplPrice` Nullable	decimal	Supplier price: line price, otherwise price from additional supplier data, otherwise item supplier price.
`OrderedQty` Nullable	decimal	Ordered quantity.
`UndeliveredQty` Nullable	decimal	Quantity marked as not delivered.
`DeliveredQty` Nullable	decimal	Delivered quantity.
`SupplierOrderType` Nullable	string	Name of the supplier order type.
`ItemNrInt` Nullable	string	Internal item number.
`ItemCodeInternal` Nullable	string	Internal item code stored on the order line.
`DeliveryLocation` Nullable	string	Location ID of the delivery location.
`ExtOrderNr` Nullable	string	External code of the supplier order (same as `SupplierOrderNRExternal`).
`SupplierOrderId`	int	Internal ID of the supplier order.
`SupplierOrderlineId`	int	ID of the order line (same as `SupplierOrderlineKey`).
`SupplierOrderCreateDate` Nullable	dateTime	Creation date of the supplier order.
`SupplierOrderCreateUser` Nullable	string	User who created the supplier order.
`Comment` Nullable	nvarchar(2000) string	Comment of the order line.

Response example

<SqlRowSet1>
  <row>
    <RowNr>1</RowNr>
    <SupplierOrderlineKey>88231</SupplierOrderlineKey>
    <SupplierOrderNR>10452</SupplierOrderNR>
    <SupplierOrderNRExternal>PO-2024-0117</SupplierOrderNRExternal>
    <OrderDate>2024-02-12T09:15:00</OrderDate>
    <SupplNr>L001</SupplNr>
    <Status>BES</Status>
    <EstimatedDelivery>2024-02-26T00:00:00</EstimatedDelivery>
    <Note>Please deliver on pallets</Note>
    <EANUPC>4006381333931</EANUPC>
    <ItemNrSuppl>SUP-7781</ItemNrSuppl>
    <OrderQty>40</OrderQty>
    <VPE>10</VPE>
    <ItemName>Ballpoint pen blue</ItemName>
    <SupplierOrderUpdateDate>2024-02-12T09:20:00</SupplierOrderUpdateDate>
    <SupplierOrderUpdateUser>mmueller</SupplierOrderUpdateUser>
    <SupplierOrderlineUpdateDate>2024-02-12T09:18:00</SupplierOrderlineUpdateDate>
    <SupplierOrderLineUpateUser>mmueller</SupplierOrderLineUpateUser>
    <ItemKey>5123</ItemKey>
    <SupplPrice>0.45</SupplPrice>
    <OrderedQty>50</OrderedQty>
    <UndeliveredQty>0</UndeliveredQty>
    <DeliveredQty>10</DeliveredQty>
    <SupplierOrderType>Standard</SupplierOrderType>
    <ItemNrInt>100245</ItemNrInt>
    <ItemCodeInternal>100245</ItemCodeInternal>
    <DeliveryLocation>001</DeliveryLocation>
    <ExtOrderNr>PO-2024-0117</ExtOrderNr>
    <SupplierOrderId>3310</SupplierOrderId>
    <SupplierOrderlineId>88231</SupplierOrderlineId>
    <SupplierOrderCreateDate>2024-02-12T09:10:00</SupplierOrderCreateDate>
    <SupplierOrderCreateUser>mmueller</SupplierOrderCreateUser>
    <Comment/>
  </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:pixiGetSupplierOrder>
      <!-- <SupplNr>L001</SupplNr> -->
      <!-- <SupplOrderNr>10452</SupplOrderNr> -->
      <!-- <DateFrom>2024-01-01T00:00:00</DateFrom> -->
      <!-- <DateTo>2024-03-31T00:00:00</DateTo> -->
      <!-- <Status>BES</Status> -->
      <!-- <RowCount>500</RowCount> -->
      <!-- <Start>0</Start> -->
    </ns1:pixiGetSupplierOrder>
  </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:pixiGetSupplierOrder>
      <!-- <SupplNr>L001</SupplNr> -->
      <!-- <SupplOrderNr>10452</SupplOrderNr> -->
      <!-- <DateFrom>2024-01-01T00:00:00</DateFrom> -->
      <!-- <DateTo>2024-03-31T00:00:00</DateTo> -->
      <!-- <Status>BES</Status> -->
      <!-- <RowCount>500</RowCount> -->
      <!-- <Start>0</Start> -->
    </ns1:pixiGetSupplierOrder>
  </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',
    // 'SupplOrderNr' => '10452',
    // 'DateFrom' => '2024-01-01T00:00:00',
    // 'DateTo' => '2024-03-31T00:00:00',
    // 'Status' => 'BES',
    // 'RowCount' => '500',
    // 'Start' => '0',
];

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

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

SQL object:: dbo.pipiGetSupplierOrder