Suppliers Read

pixiGetSOrderlines

Returns the lines of a supplier order with item, price, delivery and stock data plus customer demand and sales figures per item.

Returns all lines of the supplier order whose ID is passed in SOrderRef. Internally the call runs Pixi_GetSorderlines with all optional fields enabled, buffers the rows in a temporary table and returns them unchanged.

For each line the result contains:

Line data: item name, ordered quantity, status code of the supplier order, comment, estimated delivery date, creator and last editor with timestamps.
Item data: EAN (ArtNr), supplier item number and internal item number from the supplier-specific item data of the order's supplier, sales price (VKPrice), order unit, VAT percentage valid at the line's creation date.
Purchase prices: EKprice is the supplier price stored on the line. If the order status is flagged as new and the line price is 0 or empty, the supplier price from the supplier-specific item data is used instead. EKValue is quantity × EKprice. FinalPrice is supplier price × quantity minus the line discount.
Delivery data: delivered and undelivered quantity of the line and the total received quantity summed over all delivery details (TotalQtyReceived).
Stock and demand at the delivery location of the order: location stock, minimum order and minimum stock quantity, quantity in customer order lines with status HAL (Hal_Cust_O) or ANG, BES, BOR (Open_Cust_O), quantity in other open, ordered/confirmed supplier orders (Open_Suppl_O), and sold quantities of the last 30, 120 and 365 days (L30, L120, L365, all customer order lines except status STO).

If OpenOrderlines = 1, only lines are returned whose ordered quantity is greater than delivered plus undelivered quantity. Lines are produced in order of the line ID.

Only lines are returned for which the item, the item's location-specific record at the order's delivery location, the supplier and both the creating and modifying user exist (inner joins).

Notes

LocID is passed to the inner procedure but not used in the query. Location-related figures always refer to the delivery location of the supplier order, regardless of LocID.
If the setting Include_FUT_StatusInOpenOrdersForSupplierOrdering is 1, customer order lines with status FUT are also counted in Open_Cust_O.
All reads use NOLOCK, so figures may include uncommitted data.
The query is built as dynamic SQL; the many correlated subqueries (sales of 30/120/365 days, open orders) can make the call slow for large orders.
Fixed values: Detail, Detail2 and Price are always 0, ReceiveDate is always empty. StockQty equals SummaryQuantity.
SOrderlineNr actually contains the ID of the supplier order (same as SOrderRef), not a line number. Use SOrderlineKey to identify a line.
The ordering by line ID is applied in the inner query; the final select from the temporary table has no explicit ORDER BY.
Several columns of the internal temporary table are declared NOT NULL (e.g. VKPrice, FinalPrice). If the source data contains NULL there (e.g. a line without supplier price), the insert may fail with an SQL error.
If SOrderRef is left out (default 0), no rows are returned.

Tables used

SupplierOrder · Read SupplierOrderLine · Read SupplierOrderStatus · Read UCS_USERS · Read Items · Read ItemLocationSpecific · Read Suppliers · Read SDelDetail · Read AddItems · Read Orderlines · Read

Parameter Type Default Description

Parameter	Type	Default	Description
`SOrderRef`	int	0	ID of the supplier order whose lines are returned. NULL is treated as 0. Example: `4711`
`LocID`	char(3) string	001	Location ID. Passed on internally but has no effect on the result; location figures always refer to the order's delivery location. Example: `001`
`OpenOrderlines`	bit boolean	0	Restricts the result to lines that are not yet fully delivered. NULL is treated as 0. Allowed values `0` – Return all lines of the supplier order. `1` – Return only lines whose ordered quantity exceeds delivered plus undelivered quantity. Example: `1`

SOrderRef

int

ID of the supplier order whose lines are returned. NULL is treated as 0.

Example: 4711

LocID

char(3)
string

001

Location ID. Passed on internally but has no effect on the result; location figures always refer to the order's delivery location.

Example: 001

OpenOrderlines

bit
boolean

Restricts the result to lines that are not yet fully delivered. NULL is treated as 0.

Allowed values

0 – Return all lines of the supplier order.
1 – Return only lines whose ordered quantity exceeds delivered plus undelivered quantity.

Example: 1

Result set 1 · Always (empty if the supplier order has no matching lines).

One row per supplier order line.

Column	Type	Description
`Detail`	int	Always 0.
`Detail2`	int	Always 0.
`ArtNr` Nullable	varchar(13) string	EAN/UPC from the supplier-specific item data of the order's supplier.
`ArtName` Nullable	varchar(200) string	Item name stored on the order line.
`OrderQty`	int	Ordered quantity (0 if empty).
`ReceiveDate` Nullable	datetime dateTime	Always NULL.
`Status`	char(3) string	Status code of the supplier order.
`CreateDate` Nullable	datetime dateTime	Creation timestamp of the line.
`CreateEmp`	varchar(30) string	Name of the user who created the line.
`UpdateDate` Nullable	datetime dateTime	Last modification timestamp of the line.
`UpdateEmp`	varchar(30) string	Name of the user who last modified the line.
`SOrderlineKey`	int	ID of the supplier order line.
`SitemRef`	int	Item ID (ItemKey).
`SOrderRef`	int	ID of the supplier order.
`XMLconfirm` Nullable	bit boolean	Direct-fulfillment XML confirmation flag of the line.
`SOrderlineNr`	int	Contains the supplier order ID of the line (same value as SOrderRef).
`Note` Nullable	varchar(2000) string	Comment of the line.
`EstimatedDelivery` Nullable	datetime dateTime	Estimated delivery date of the line.
`VKPrice`	money decimal	Sales price of the item.
`SummaryQuantity`	int	Total stock quantity of the item.
`LocationQuantity`	int	Stock quantity of the item at the order's delivery location.
`QtyReceived`	int	Delivered quantity stored on the line.
`QtyNotDelivered`	int	Quantity marked as not deliverable.
`MinOrderQty`	int	Minimum order quantity at the delivery location.
`MinStockQty`	int	Minimum stock quantity at the delivery location.
`OrderUnit`	varchar(50) string	Order unit of the item ('0' if empty).
`ItemNrSuppl` Nullable	varchar(50) string	Supplier item number from the supplier-specific item data.
`ItemNrInt` Nullable	varchar(50) string	Internal item number from the supplier-specific item data.
`VATperc` Nullable	float decimal	VAT rate in percent valid at the line's creation date.
`EKprice` Nullable	money decimal	Purchase price; for new orders with empty line price the supplier price from the item data.
`EKValue`	money decimal	Quantity × purchase price (0 if empty).
`Hal_Cust_O` Nullable	int	Quantity of the item in customer order lines with status HAL at the delivery location.
`Open_Cust_O` Nullable	int	Quantity in open customer order lines (ANG, BES, BOR, optionally FUT) at the delivery location.
`Open_Suppl_O`	int	Quantity in ordered/confirmed, not closed supplier orders for the same delivery location (including this order if it qualifies).
`L30` Nullable	int	Quantity ordered by customers in the last 30 days at the delivery location (excluding status STO).
`L120` Nullable	int	Quantity ordered by customers in the last 120 days (excluding status STO).
`L365` Nullable	int	Quantity ordered by customers in the last 365 days (excluding status STO).
`StockQty`	int	Total stock quantity of the item (same as SummaryQuantity).
`Price`	money decimal	Always 0 (kept for backward compatibility).
`TotalQtyReceived`	int	Sum of delivered quantities of all delivery details for this line.
`FinalPrice`	money decimal	Supplier price × quantity minus line discount amount.

Response example

<SqlRowSet1>
  <row>
    <Detail>0</Detail>
    <Detail2>0</Detail2>
    <ArtNr>4006381333931</ArtNr>
    <ArtName>Kugelschreiber blau</ArtName>
    <OrderQty>100</OrderQty>
    <Status>BES</Status>
    <CreateDate>2024-03-04T09:12:00</CreateDate>
    <CreateEmp>mmueller</CreateEmp>
    <UpdateDate>2024-03-05T14:30:00</UpdateDate>
    <UpdateEmp>mmueller</UpdateEmp>
    <SOrderlineKey>18233</SOrderlineKey>
    <SitemRef>5521</SitemRef>
    <SOrderRef>4711</SOrderRef>
    <XMLconfirm>false</XMLconfirm>
    <SOrderlineNr>4711</SOrderlineNr>
    <EstimatedDelivery>2024-03-15T00:00:00</EstimatedDelivery>
    <VKPrice>2.9900</VKPrice>
    <SummaryQuantity>40</SummaryQuantity>
    <LocationQuantity>25</LocationQuantity>
    <QtyReceived>60</QtyReceived>
    <QtyNotDelivered>0</QtyNotDelivered>
    <MinOrderQty>50</MinOrderQty>
    <MinStockQty>20</MinStockQty>
    <OrderUnit>Stk</OrderUnit>
    <ItemNrSuppl>KS-100-B</ItemNrSuppl>
    <ItemNrInt>A-5521</ItemNrInt>
    <VATperc>19</VATperc>
    <EKprice>0.8500</EKprice>
    <EKValue>85.0000</EKValue>
    <Hal_Cust_O>3</Hal_Cust_O>
    <Open_Cust_O>12</Open_Cust_O>
    <Open_Suppl_O>100</Open_Suppl_O>
    <L30>45</L30>
    <L120>160</L120>
    <L365>520</L365>
    <StockQty>40</StockQty>
    <Price>0.0000</Price>
    <TotalQtyReceived>60</TotalQtyReceived>
    <FinalPrice>80.0000</FinalPrice>
  </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:pixiGetSOrderlines>
      <!-- <SOrderRef>4711</SOrderRef> -->
      <!-- <LocID>001</LocID> -->
      <!-- <OpenOrderlines>1</OpenOrderlines> -->
    </ns1:pixiGetSOrderlines>
  </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:pixiGetSOrderlines>
      <!-- <SOrderRef>4711</SOrderRef> -->
      <!-- <LocID>001</LocID> -->
      <!-- <OpenOrderlines>1</OpenOrderlines> -->
    </ns1:pixiGetSOrderlines>
  </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 = [
    // 'SOrderRef' => '4711',
    // 'LocID' => '001',
    // 'OpenOrderlines' => '1',
];

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

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

SQL object:: dbo.pipiGetSOrderlines