Payments Read

pixiPayGetOrdersToAuthorize

Returns orders with lines in status HAL that are due for payment authorization for a given payment method, including amounts, card and address data.

Selects orders that a payment connector (for example a credit card processor) should authorize, and returns one row per order.

Selection steps:

Collect all orders that have at least one order line in status HAL.
Keep only orders where:
- the order header was last updated more than 10 minutes ago (updatedate), so that the order is complete before it is processed,
- the payment code of the order address (Addresses.aPayment) equals PaymentCode,
- at least one of these holds: ccpNumOfTries <= 3, CCPReauthorizeYN = Y (re-authorization requested), ShowAll = 1, or ccpSetFailTries between 1 and 3 and ccpSetFailLastTime older than 10 minutes,
- the customer is not locked (Customers.SLocked is not Y).
Take the first RowCount orders, ordered by ccpNumOfTries ascending, then OrderHeaderKey ascending (orders with fewer attempts first).
For these orders, sum OrderLineAmount of the lines in status ANG, HAL and AUS separately (missing statuses count as 0).

The result contains order totals, the per-status line totals, currency, shop ID, card data and address data of the order address, plus the authorization tracking fields of the order header. The final result is sorted by OrderHeaderKey ascending.

Notes

Read-only; all permanent tables are read WITH (NOLOCK), so uncommitted data may be returned.
Pitfall: in the result the column Addresses.eMail is returned under the alias CCPPaymentInfoOK (there is no column eMail). The column therefore contains the e-mail address of the order address, not a payment status flag.
The *_OrderCurr columns contain exactly the same values as their counterparts without suffix (comments in the code mark them for removal); no currency conversion happens.
The result contains sensitive card data (CardNumber, CardCVC, CardExpiration); handle and transport it accordingly.
ShowAll = 1 only bypasses the retry-count conditions; the 10-minute waiting time, payment code, HAL line and customer lock filters still apply.
RowCount limits the number of orders, not lines.
The 10-minute threshold is based on the server time (GETDATE()).

Tables used

Orderlines · Read Orderheader · Read Addresses · Read Customers · Read Currency · Read

Parameter Type Default Description

Parameter	Type	Default	Description
`PaymentCode` Required	char(1) string	–	Payment method code; compared with `Addresses.aPayment` of the order address. Example: `K`
`ShowAll`	bit boolean	0	If 1, orders are returned regardless of the number of previous authorization attempts. Allowed values `0` – Only orders with at most 3 attempts, re-authorization requested, or 1–3 failed settlement attempts older than 10 minutes. `1` – Ignore the attempt-count conditions. Example: `0`
`RowCount`	int	30	Maximum number of orders returned. Example: `30`

PaymentCode

Required

char(1)
string

–

Payment method code; compared with Addresses.aPayment of the order address.

Example: K

ShowAll

bit
boolean

If 1, orders are returned regardless of the number of previous authorization attempts.

Allowed values

0 – Only orders with at most 3 attempts, re-authorization requested, or 1–3 failed settlement attempts older than 10 minutes.
1 – Ignore the attempt-count conditions.

Example: 0

RowCount

int

Maximum number of orders returned.

Example: 30

Result set 1 · Always (may be empty)

One row per order to authorize, sorted by OrderHeaderKey ascending.

Column	Type	Description
`OrderNr`	int	Pixi order number.
`OrderNrExternal` Nullable	string	External (shop) order number.
`OrderTotal` Nullable	decimal	Order total from the order header.
`OrderLinesTotal` Nullable	decimal	Total of the order lines from the order header.
`OrderLinesDiscount` Nullable	decimal	Discount on the order lines.
`ShipCost` Nullable	decimal	Shipping costs.
`OrderLinesTotalANG`	decimal(19,4) decimal	Sum of `OrderLineAmount` of lines in status `ANG` (0 if none).
`OrderLinesTotalHAL`	decimal(19,4) decimal	Sum of `OrderLineAmount` of lines in status `HAL`.
`OrderLinesTotalAUS`	decimal(19,4) decimal	Sum of `OrderLineAmount` of lines in status `AUS` (0 if none).
`OrderLinesTotal_OrderCurr` Nullable	decimal	Same value as `OrderLinesTotal`.
`OrderLinesTotalANG_OrderCurr`	decimal(19,4) decimal	Same value as `OrderLinesTotalANG`.
`OrderLinesTotalHAL_OrderCurr`	decimal(19,4) decimal	Same value as `OrderLinesTotalHAL`.
`OrderLinesTotalAUS_OrderCurr`	decimal(19,4) decimal	Same value as `OrderLinesTotalAUS`.
`ShipCost_OrderCurr` Nullable	decimal	Same value as `ShipCost`.
`OrderCurrency` Nullable	string	Currency code (`Currency.CodeA`) of the order.
`ShopID` Nullable	string	Shop of the customer (`Customers.CustomerOf`).
`CardOwner` Nullable	string	Card holder name from the order address.
`CardNumber` Nullable	string	Card number from the order address.
`CardExpiration` Nullable	string	Card expiration date.
`CardCVC` Nullable	string	Card verification code.
`Anrede` Nullable	string	Salutation.
`CareOf` Nullable	string	Care-of / company line.
`FirstName` Nullable	string	First name.
`LastName` Nullable	string	Last name.
`Address` Nullable	string	Street address.
`City` Nullable	string	City.
`ZIP` Nullable	string	Postal code.
`Country` Nullable	string	Country.
`Phone` Nullable	string	Phone number.
`CCPPaymentInfoOK` Nullable	string	Contains `Addresses.eMail` (e-mail address of the order address) due to a missing comma in the code.
`CCPLastAuthCode` Nullable	string	Last authorization code.
`CCPNumOfTries` Nullable	int	Number of authorization attempts so far.
`CCPReauthorizeYN` Nullable	string	`Y` if re-authorization was requested.
`ccpPayID` Nullable	string	Payment ID at the payment provider.
`ccpSetFailLastTime` Nullable	dateTime	Time of the last failed settlement attempt.
`ccpSetFailTries` Nullable	int	Number of failed settlement attempts.

Response example

<SqlRowSet1>
  <row>
    <OrderNr>104512</OrderNr>
    <OrderNrExternal>SHOP-2024-0815</OrderNrExternal>
    <OrderTotal>129.9000</OrderTotal>
    <OrderLinesTotal>125.0000</OrderLinesTotal>
    <OrderLinesDiscount>0.0000</OrderLinesDiscount>
    <ShipCost>4.9000</ShipCost>
    <OrderLinesTotalANG>0.0000</OrderLinesTotalANG>
    <OrderLinesTotalHAL>125.0000</OrderLinesTotalHAL>
    <OrderLinesTotalAUS>0.0000</OrderLinesTotalAUS>
    <OrderLinesTotal_OrderCurr>125.0000</OrderLinesTotal_OrderCurr>
    <OrderLinesTotalANG_OrderCurr>0.0000</OrderLinesTotalANG_OrderCurr>
    <OrderLinesTotalHAL_OrderCurr>125.0000</OrderLinesTotalHAL_OrderCurr>
    <OrderLinesTotalAUS_OrderCurr>0.0000</OrderLinesTotalAUS_OrderCurr>
    <ShipCost_OrderCurr>4.9000</ShipCost_OrderCurr>
    <OrderCurrency>EUR</OrderCurrency>
    <ShopID>SHOP1</ShopID>
    <CardOwner>Max Mustermann</CardOwner>
    <CardNumber>XXXXXXXXXXXX1111</CardNumber>
    <CardExpiration>12/27</CardExpiration>
    <CardCVC>***</CardCVC>
    <Anrede>Herr</Anrede>
    <CareOf/>
    <FirstName>Max</FirstName>
    <LastName>Mustermann</LastName>
    <Address>Musterstr. 1</Address>
    <City>Berlin</City>
    <ZIP>10115</ZIP>
    <Country>DE</Country>
    <Phone>030123456</Phone>
    <CCPPaymentInfoOK>max@example.com</CCPPaymentInfoOK>
    <CCPLastAuthCode/>
    <CCPNumOfTries>0</CCPNumOfTries>
    <CCPReauthorizeYN>N</CCPReauthorizeYN>
    <ccpPayID/>
    <ccpSetFailTries>0</ccpSetFailTries>
  </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:pixiPayGetOrdersToAuthorize>
      <PaymentCode>K</PaymentCode>
      <!-- <ShowAll>0</ShowAll> -->
      <!-- <RowCount>30</RowCount> -->
    </ns1:pixiPayGetOrdersToAuthorize>
  </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:pixiPayGetOrdersToAuthorize>
      <PaymentCode>K</PaymentCode>
      <!-- <ShowAll>0</ShowAll> -->
      <!-- <RowCount>30</RowCount> -->
    </ns1:pixiPayGetOrdersToAuthorize>
  </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 = [
    'PaymentCode' => 'K',
    // 'ShowAll' => '0',
    // 'RowCount' => '30',
];

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

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

SQL object:: dbo.pipiPayGetOrdersToAuthorize