Payments Read

pixiPayGetInvoicesToCapture

Returns unpaid, shipped invoices of a payment method that are due for capture, with order, amount, customer address and card data.

Selects the invoices that a payment integration (for example a credit card provider) should capture next and returns them with all data needed for the capture.

An invoice is selected when all of the following apply:

Payment equals @PaymentCode.
Nothing has been paid yet (PayedSum = 0) and Total > 0.
The capture is not marked as done (CCPPaymentDone is N or NULL).
The invoice has been scanned out (ScanOutDate is set).
InvoiceKey is greater than @FromInvoiceId.
One of the retry conditions holds:
- no capture attempt yet (CCPNumOfTries = 0), or
- a recharge is requested (CCPRecharge = 'Y'), or
- 1 to 3 failed attempts (ccpSetFailTries) and the last failure (ccpSetFailLastTime) is older than 10 minutes, or
- @ShowAll = 1, which skips the retry conditions entirely.

At most @RowCount invoices are selected, ordered by InvoiceKey ascending. For each one the call returns invoice number, the comma-separated list of external order numbers, total, paid amount, amount to capture (Total - PayedSum), the sum of linked order lines in status ANG, currency code, shop (CustomerOf of the customer), the card and address data of the invoice address, and the capture status fields.

Paging: pass the InvoiceId of the last returned row as @FromInvoiceId to get the next block.

Notes

All reads use NOLOCK, so uncommitted changes may be visible.
The call does not change any data; marking invoices as captured or failed must be done separately.
@ShowAll = 1 only removes the retry/back-off condition; all other filters (payment code, unpaid, scanned out, not done) still apply. It also returns invoices with more than 3 failed attempts.
The TOP (@RowCount) limit is applied before joining currency, customer and address. Invoices without a matching row there are dropped, so fewer than @RowCount rows may be returned although more invoices exist.
Because only invoices with PayedSum = 0 are selected, TotalToCapture always equals Total.
Total_OrderCurr is a duplicate of Total; a code comment marks it for removal, so clients should not rely on it.
The result contains sensitive card data (number, expiry, CVC); handle and log it accordingly.

Tables used

Invoices · Read Invlines · Read Orderlines · Read Currency · Read Customers · Read Addresses · Read

Parameter	Type	Default	Description
`PaymentCode` Required	char(1) string	–	Payment method code; only invoices whose `Payment` field equals this value are returned. Example: `C`
`ShowAll`	bit boolean	0	If 1, the retry conditions (no attempt yet, recharge requested, or 1–3 failures with the last one older than 10 minutes) are ignored. Allowed values `0` – Only invoices that are due according to the retry conditions. `1` – All unpaid, scanned-out invoices of the payment method, regardless of previous attempts. Example: `0`
`RowCount`	int	30	Maximum number of invoices to return. Example: `30`
`FromInvoiceId`	int	0	Only invoices with an `InvoiceKey` greater than this value are returned; use the last returned `InvoiceId` for paging. Example: `0`

Result set 1 · Always (may be empty).

One row per invoice to capture, ordered by InvoiceId ascending.

Column	Type	Description
`InvoiceNr` Nullable	string	Invoice number.
`OrderNrExternal` Nullable	varchar(8000) string	Comma-separated, sorted list of the distinct external order numbers of the invoice lines.
`Total` Nullable	decimal	Invoice total.
`OrderLinesTotalANG`	decimal	Sum of `OrderLineAmount` of the order lines linked to the invoice that are in status `ANG`; 0 if none.
`PayedSum` Nullable	decimal	Amount already paid (always 0 due to the filter).
`TotalToCapture` Nullable	decimal	Amount to capture, `Total - PayedSum`.
`Total_OrderCurr` Nullable	decimal	Same as `Total`; marked for removal in the code.
`OrderCurrency` Nullable	string	Currency code (`CodeA`) of the invoice currency.
`ShopID` Nullable	string	Shop the customer belongs to (`CustomerOf`).
`CardOwner` Nullable	string	Card holder name from the invoice address.
`CardNumber` Nullable	string	Card number from the invoice address.
`CardExpiration` Nullable	string	Card expiry from the invoice address.
`CardCVC` Nullable	string	Card verification code from the invoice address.
`Anrede` Nullable	string	Salutation of the invoice address.
`CareOf` Nullable	string	Care-of line of the invoice address.
`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.
`eMail` Nullable	string	E-mail address.
`CCPNumOfTries` Nullable	int	Number of capture attempts so far.
`CCPRecharge` Nullable	string	`Y` if a recharge is requested.
`ccpPayID` Nullable	string	Payment ID at the payment provider.
`ccpSetFailLastTime` Nullable	dateTime	Time of the last failed attempt.
`ccpSetFailTries` Nullable	int	Number of failed attempts.
`InvoiceId`	int	Internal invoice key; use as `@FromInvoiceId` for the next page.

Response example

<SqlRowSet1>
  <row>
    <InvoiceNr>RE2024001234</InvoiceNr>
    <OrderNrExternal>100045678,100045679</OrderNrExternal>
    <Total>149.90</Total>
    <OrderLinesTotalANG>0.00</OrderLinesTotalANG>
    <PayedSum>0.00</PayedSum>
    <TotalToCapture>149.90</TotalToCapture>
    <Total_OrderCurr>149.90</Total_OrderCurr>
    <OrderCurrency>EUR</OrderCurrency>
    <ShopID>SHOP1</ShopID>
    <CardOwner>Max Mustermann</CardOwner>
    <CardNumber>411111******1111</CardNumber>
    <CardExpiration>12/27</CardExpiration>
    <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>
    <eMail>max@example.com</eMail>
    <CCPNumOfTries>0</CCPNumOfTries>
    <CCPRecharge>N</CCPRecharge>
    <ccpPayID>PAY-88231</ccpPayID>
    <ccpSetFailTries>0</ccpSetFailTries>
    <InvoiceId>58231</InvoiceId>
  </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:pixiPayGetInvoicesToCapture>
      <PaymentCode>C</PaymentCode>
      <!-- <ShowAll>0</ShowAll> -->
      <!-- <RowCount>30</RowCount> -->
      <!-- <FromInvoiceId>0</FromInvoiceId> -->
    </ns1:pixiPayGetInvoicesToCapture>
  </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:pixiPayGetInvoicesToCapture>
      <PaymentCode>C</PaymentCode>
      <!-- <ShowAll>0</ShowAll> -->
      <!-- <RowCount>30</RowCount> -->
      <!-- <FromInvoiceId>0</FromInvoiceId> -->
    </ns1:pixiPayGetInvoicesToCapture>
  </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' => 'C',
    // 'ShowAll' => '0',
    // 'RowCount' => '30',
    // 'FromInvoiceId' => '0',
];

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

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

SQL object:: dbo.pipiPayGetInvoicesToCapture