Orders Read

pixiGetExpiredReservations

Returns orders at a POS location whose order lines are older than the reservation expiry period and have no reservation.

Returns the distinct order numbers and customer references of orders at a POS location whose reservation period has expired.

An order line is included if all of the following apply:

LocationID of the order line equals @LocationId (the POS location).
The POS location has a reservation location assigned (Location.ResLocID), and that location exists in Location.
The customer referenced by CustRef exists in Customers.
No entry in Reservation exists for the order line (ResOrderlineRef).
The order line status is ANG or HAL.
More full days have passed since OrderDate than the expiry period. The period is ResExpDays of the reservation location. If that value is NULL, the global setting ReservationExpiry is used.
Every order line of the same order has an ItemLocationSpecific record for the reservation location. If a single line has no such record, the whole order is excluded.

Each order appears only once. The result is sorted by ordernr ascending.

Notes

If LocationId is omitted (NULL), the comparison LocationID = NULL is never true and the result is always empty. Always pass a location.
How the expiry period is resolved from the setting ReservationExpiry:
- The setting is read via fpixi_GetSetting. That function returns '0' when the setting does not exist.
- A missing setting therefore gives an expiry of 0 days, not 14. Any order line older than one day then qualifies.
- The fallback of 14 days applies only if the setting exists with an empty value.
- A non-numeric setting value causes a conversion error.
All tables are read with NOLOCK, so uncommitted data may be returned.
The header comment of the procedure ("orders ready to pick up") does not match its name or logic. The behaviour described here follows the code.
The procedure only reads data. It does not cancel orders or change any data.

Parameters

Parameter	Type	Default	Description
`LocationId`	varchar(50) string	NULL	ID of the POS location (`Location.LocID`) whose order lines are checked. Without a value, no rows are returned. Example: `P01`

Response

Result set 1 · Always; empty if no order matches or `LocationId` is NULL.

One row per order with expired reservation period, sorted by order number.

Column	Type	Description
`ordernr`	int	Order number (`Orderlines.OrderNr`).
`CustRef`	int	Customer key of the order (`Orderlines.CustRef`, matches `Customers.CustKey`).

Response example

<SqlRowSet1>
  <row>
    <ordernr>100245</ordernr>
    <CustRef>5821</CustRef>
  </row>
  <row>
    <ordernr>100312</ordernr>
    <CustRef>6110</CustRef>
  </row>
</SqlRowSet1>

Examples

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:pixiGetExpiredReservations>
      <!-- <LocationId>P01</LocationId> -->
    </ns1:pixiGetExpiredReservations>
  </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:pixiGetExpiredReservations>
      <!-- <LocationId>P01</LocationId> -->
    </ns1:pixiGetExpiredReservations>
  </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 = [
    // 'LocationId' => 'P01',
];

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

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

Tables used

Orderlines · Read Location · Read Customers · Read Reservation · Read ItemLocationSpecific · Read Settings · Read

SQL object:: dbo.pipiGetExpiredReservations