Payments Write

pixiAutoMatchPayments

Automatically matches the bank lines of a bank header to open orders or invoices, confirms the match and releases the order if it is fully paid.

Runs the automatic payment matching for one bank header (BankHeaderKey) and, if a match was found, confirms it and tries to release the matched order.

If BankHeaderKey is NULL, a single row with NULL values and the message Parameter BankHeaderKey is mandatory! is returned and nothing else happens.
The internal automatic matching (Pixi_BankAutomaticMatching) is executed for all bank lines of this header that do not yet have an entry in BankMatch. It looks for 100% hits:
- Orders whose payment method (Payments.MatchToOrders = 1) allows matching and that have at least one order line in status HAL. The bank amount must equal the open amount (OrderTotal - PaidByCustomer, rounded to 2 decimals) in the same currency, and the bank text (BankText01–BankText03) must contain the external order number (optionally without prefix), the ccpPayID, a unique external customer number or a unique e-mail address.
- Invoices that are completely unpaid and whose payment method has MatchToInvoices = 1. The bank amount must equal the invoice total; the bank text must contain the invoice number, the external order number, the customer number or the e-mail.
- Ambiguous candidates are discarded. Found matches are written to BankMatch as prematches (IsPrematch = 1).
If no match was found, the returned message is 0 matched orders/invoices found. followed by a line break and the statistics text of the matching (number of bank lines, open and matched orders/invoices).
Otherwise the BankMatch entry of the bank line of this header is read, and all prematches of this bank line are confirmed (IsPrematch set to 0).
For the matched order, OrderNr and the difference ABS(OrderTotal - PaidByCustomer) are read from OrderHeader:
- Difference 0: all order lines of the order in status HAL (on hold) are set to ANG with update user IPN_API (payments); message Delta: 0; releasing order: {OrderNr}.
- Otherwise: message Delta: {Delta}; cannot release order:{OrderNr}; the order stays on hold.
Unless SuppressOutput = 1, one row with OrderHeaderKey, OrderNr, AmountMatched, BankLineKey and message is returned.

Notes

One bank line per header expected: the bank line is determined by a subquery on BankLines filtered by BankHeaderRef. If the bank header has more than one bank line and a match was found, SQL Server raises error 512 (subquery returned more than one value), returned as SqlMessage. At that point the prematches are already written to BankMatch. The call is designed for headers with a single line (e.g. one payment notification per header, update user IPN_API).
No transaction: matching, confirmation and order release run as separate steps. An error in a later step does not undo earlier steps.
The BankMatch entry is read without filtering on IsPrematch; if several entries exist for the bank line, an arbitrary one is returned.
If the match refers to an invoice instead of an order, OrderHeaderKey, OrderNr are NULL, no order is released and message is NULL (string concatenation with NULL).
If no BankMatch row is found despite a match (so BankLineKey is NULL), the confirmation step falls into its mass branch and confirms up to 100 arbitrary prematches of all bank lines.
Whether PaidByCustomer is already updated when the difference is calculated depends on logic not visible in the code (e.g. triggers on BankMatch).
Matching behaviour is influenced by the settings AutomatchBanklines_Enforce_CustomerNrExternal (order/invoice number must be accompanied by the customer number unless set to 0) and AutomatchBanklinesStrictOrderNrExternalMatchOnly (1 disables matching by external order number without prefix). Unprefixed order numbers are only used with at least 5 characters.
Reads use NOLOCK, so uncommitted data may be considered.
If releasing the order fails, the error is written to LogTable and re-raised.

Parameters

Parameter Type Default Description

Parameter	Type	Default	Description
`BankHeaderKey`	int	NULL	Key of the bank header whose bank lines are matched. Effectively mandatory; if omitted, an error row is returned. Example: `40098`
`SuppressOutput`	bit boolean	0	Suppresses the result row after processing. NULL is treated as 0. The error row for a missing `BankHeaderKey` is always returned. Allowed values `0` – Return the result row. `1` – Return no result row. Example: `0`

BankHeaderKey

int

NULL

Key of the bank header whose bank lines are matched. Effectively mandatory; if omitted, an error row is returned.

Example: 40098

SuppressOutput

bit
boolean

Suppresses the result row after processing. NULL is treated as 0. The error row for a missing BankHeaderKey is always returned.

Allowed values

0 – Return the result row.
1 – Return no result row.

Example: 0

Response

Result set 1 · Always if `BankHeaderKey` is NULL (error row); otherwise only if `SuppressOutput` = 0.

One row with the matched order, the matched amount, the bank line and a status message.

Column	Type	Description
`OrderHeaderKey` Nullable	int	Key of the matched order; NULL if nothing was matched or the match refers to an invoice.
`OrderNr` Nullable	int	Order number of the matched order.
`AmountMatched` Nullable	money decimal	Amount stored in the `BankMatch` entry.
`BankLineKey` Nullable	int	Key of the matched bank line.
`message` Nullable	varchar(1000) string	Status text: missing parameter, no match found (with matching statistics), order released, or order not released with the remaining difference.

Response example

<SqlRowSet1>
  <row>
    <OrderHeaderKey>215873</OrderHeaderKey>
    <OrderNr>100482</OrderNr>
    <AmountMatched>59.9000</AmountMatched>
    <BankLineKey>88231</BankLineKey>
    <message>Delta: 0; releasing order: 100482</message>
  </row>
</SqlRowSet1>

Error messages

Status	Message	Cause
message	Parameter BankHeaderKey is mandatory!	`BankHeaderKey` was not passed or is NULL. Pass the key of the bank header.
message	0 matched orders/invoices found. Message: {MatchInfo}	The automatic matching found no unique order or invoice for the unmatched bank lines of the header (or all lines are already matched). `{MatchInfo}` contains the matching statistics.
message	Delta: {Delta}; cannot release order:{OrderNr}	The match was confirmed, but the order total and the amount paid by the customer differ, so the order stays on hold.
RAISERROR	{ERROR_MESSAGE}	Releasing the order lines from status `HAL` failed (e.g. locking or constraint error). The original error is logged in `LogTable` and re-raised.

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:pixiAutoMatchPayments>
      <!-- <BankHeaderKey>40098</BankHeaderKey> -->
      <!-- <SuppressOutput>0</SuppressOutput> -->
    </ns1:pixiAutoMatchPayments>
  </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:pixiAutoMatchPayments>
      <!-- <BankHeaderKey>40098</BankHeaderKey> -->
      <!-- <SuppressOutput>0</SuppressOutput> -->
    </ns1:pixiAutoMatchPayments>
  </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 = [
    // 'BankHeaderKey' => '40098',
    // 'SuppressOutput' => '0',
];

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

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

Tables used

BankMatch · Write BankLines · Read OrderHeader · Read Orderlines · Write Addresses · Read Payments · Read Customers · Read Invoices · Read Invlines · Read LogTable · Write

SQL object:: dbo.pipiAutoMatchPayments