Double-Charge while there is only one order in Tessitura & Charge with no order in Tessitura

We recently discovered a new type of double-charge - we have been on Tessitura Merchant Services/Adyen since April, and usually the standard reconciliation process will catch discrepancies on either side. However, after a couple of patrons contacted us, we discovered that in rare cases on our website a card was getting charged twice, but both charges were associated with a single payment_no in T_PAYMENT. To complicate further, the order and constituent record would show us a single successful payment, but the Payment Gateway Summary/Detail report run in Tessitura would show both payments. That meant our daily reconciliation between the Tessitura payment gateway report and Adyen wasn't catching the double-charge, since both payments were being reflected in the totals.

To try to pin these anomalies down, I dug into the T_PAYMENT_GATEWAY_ACTIVITY table a bit, and tried to isolate examples of multiple payments authorized under a single payment_no. My query is below (pardon the amateur SQL, there's probably a more elegant way to do it) - basically, each payment_no should have a single authorization, so if any payment_nos rise to the top of the list with 2+ authorizations, there's a duplicate issue to resolve. Luckily we only found 5 of these since April, so it wasn't as widespread as feared.

Our web developers (non-TNEW) took a look and they believe in rare cases a user is refreshing or not being properly redirected after their first payment is authorized, causing them to input a second payment under the same payment_no. They are adding some code so that every time the payment component data is being requested and loaded onto the page, it will void any previous authorizations and request a fresh payment_no. Hopefully that will help us solve it.

SQL query for multiple authorizations under one payment_no:

select payment_no, count(payment_gateway_transaction_type_id) as num_auths from T_PAYMENT_GATEWAY_ACTIVITY
where payment_gateway_transaction_type_id = -1
and create_dt >= **SET DESIRED START DATE HERE**
and response_message = 'Authorised'
group by payment_no
order by num_auths desc

We recently discovered a new type of double-charge - we have been on Tessitura Merchant Services/Adyen since April, and usually the standard reconciliation process will catch discrepancies on either side. However, after a couple of patrons contacted us, we discovered that in rare cases on our website a card was getting charged twice, but both charges were associated with a single payment_no in T_PAYMENT. To complicate further, the order and constituent record would show us a single successful payment, but the Payment Gateway Summary/Detail report run in Tessitura would show both payments. That meant our daily reconciliation between the Tessitura payment gateway report and Adyen wasn't catching the double-charge, since both payments were being reflected in the totals.

To try to pin these anomalies down, I dug into the T_PAYMENT_GATEWAY_ACTIVITY table a bit, and tried to isolate examples of multiple payments authorized under a single payment_no. My query is below (pardon the amateur SQL, there's probably a more elegant way to do it) - basically, each payment_no should have a single authorization, so if any payment_nos rise to the top of the list with 2+ authorizations, there's a duplicate issue to resolve. Luckily we only found 5 of these since April, so it wasn't as widespread as feared.

Our web developers (non-TNEW) took a look and they believe in rare cases a user is refreshing or not being properly redirected after their first payment is authorized, causing them to input a second payment under the same payment_no. They are adding some code so that every time the payment component data is being requested and loaded onto the page, it will void any previous authorizations and request a fresh payment_no. Hopefully that will help us solve it.

SQL query for multiple authorizations under one payment_no:

select payment_no, count(payment_gateway_transaction_type_id) as num_auths from T_PAYMENT_GATEWAY_ACTIVITY
where payment_gateway_transaction_type_id = -1
and create_dt >= **SET DESIRED START DATE HERE**
and response_message = 'Authorised'
group by payment_no
order by num_auths desc

I totally understand and appreciate the frustration that this issue is causing and I want to let you all know that this is the primary focus in the Payments team right now.  There are three basic issues that we have identified and are working on that can cause payments to be recorded without a corresponding order, or in some cases duplicate payments for the same order.  The first is caused by the use of Guest Checkout in TNEW v16 when the customer completes a payment and then decides to continue shopping in the same session.  A patch for this issue was put on test TNEW sites last week and was deployed to production sites today.  This affects v16 only.  The second was caused by a load balancer routing issue and has been fixed in both TNEW v7 (for v15 sites) and TNEW v16.  The final known cause, the fix for which is currently in development, is not properly handling the situation when a customer uses the browser back button or refreshes the page in the middle of a payment process.