One-Time ACH Contributions Aren't Being Verified

[tamarmeir]

Hi guys,

System stats:

CMS: Drupal 7.52 Civi: 4.7.12 iATS: 1.5.4-alpha

Recurring contributions are being verified as expected, one-time contributions, however, are not - they remain in pending status even after it has been confirmed that the contributions have been settled successfully.

I see you have released a few more updates and are now on 1.5.4-rc-2, would it be worth Mathieu installing to see if the issue persists or do the updates have nothing to do with what might be happening here?

Thanks as always for your time! Tamar

[adixon]

It doesn't sound related, it sounds more like a case we haven't encountered much before (i.e. one-time ACH). For a recurring series, the first contribution is a bit of a weird one (thanks, Civi ...), so perhaps the handling of that (or mis-handling in this case) is the issue. Is this new - i.e. have you had one-time ACH/EFT confirmed on previous versions of Civi/iATS?

[tamarmeir]

Hi Alan,

Sorry it took me so long to get back - I am finding that I don't have much data for you on this since as you point out, it is rare to make one-time payments via ACH - it was definitely working before we updated clients to 4.7 - what I'm not sure of if in 4.7 it stopped working altogether or if it was after a point release (we updated to 4.79 and then to 4.7.12) - that's the information I'm lacking - I will attempt to test through a dev environment running 4.7.16 to see if I can reproduce consistently and let you know the results.

Thanks as always for your time, Tamar

[tamarmeir]

Hi guys,

Looks like the verification issue for one time installments has been resolved - I think I might be seeing a different issue with the way in which information is being logged in civicrm_financial_trxn (it looks like payment_instrument and payment_processor_id are NULL for ACH payments), but I see a bunch of open issues on here that may be the same so I need to do some more research to confirm if this is what I am seeing. This issue, in my opinion, can be closed for the time being.

Thanks!

[tamarmeir]

I spoke too soon - looks like the issue is back, but I wonder if it is specific to contribution pages? It appears that one-time ACH contributions being submitted through event pages are completing as expected. Happy to test this out - Karen, do I still have an account on semper IT?

[KarinG]

Hey Tamar - yes you do still have an account; I'm not sure why the origin of the contribution would matter though - but happy for you to show us if it does.

[KarinG]

I suspect ACHEFT verification depends on the time it takes to verify - if it takes too long - then we're relying on the Box Journal - and there were issues with pulling that Box journal from iATS - at some point.

[tamarmeir]

Hey Karin,

OK - weird, but just tested on a client site using iATS test credentials - one-time ACH contributions made via a contribution page are listed with a pending status (presumably until verified), but one-time ACH event payments are created with a status of completed (which is probably what led me to believe that this issue was resolved) and listed with a payment instrument of credit card!

Not sure how to speed up the verification process to see what happens to the contribution page contribution - any hocus pocus you can do from the public test environment for invoice ID: 9a696a73700bd98d49171c5f9599e2ef ? If you can push it through, let me know and I will re-run the verification and let you know what happens to that contribution.

Not sure what the box journal is....

[lcdservices]

I'm seeing a similar issue, running Civi 4.7.19 + iATS 1.6.0. One-time ACH/EFT contributions are left in pending status. What's interesting is that the record in civicrm_iats_response_log reports a status of OK:555555. So it is verified and is reporting back to the raw response_log table, but isn't updating the contribution status.

[adixon]

Ah, thanks for your report. You know that ACH/EFT flow is different, right? It should be initially created and left as pending, and only gets converted to complete after a 'verification' via the corresponding iATS ACH/EFT verification job. That will get a separate entry in both the response_log table and the verification table. The initial transaction will also get an entry in that response_log table, but it's just telling you that the request has been queued up for submission to the bank, there's been no real attempt at verifying that the transaction will go through. The OK:555555 is what iATS sends back on the initial submission, not the verification.

[KarinG]

Hi - I'm running some more tests right now - trying to reproduce this on my 4.7.x - Transaction #: A9C1684B:1494549939; iATS ACH/EFT - TE4188; 3703 ToBeSend:ToBeSent:

[KarinG]

I got a REJ 111 "Setup Issue" for this ACHEFT TEST. Will contact iATS to check the account setup on their end.

[lcdservices]

@KarinG any more feedback re: your tests?

[KarinG]

Still trying to reproduce it - found an iATS TEST account [other than TEST88] that I can use for ACHEFT testing.

3fa858982a03e686c8eb923a2ac47a08 5/17/2017 8:48:50 AM XXXXX Checking KarinG Gerritsen 1***3456 1.00 ToBeSend:ToBeSent

Still waiting for approval on the iATS end of things. Will check in this afternoon.

You're not doing Events are you? Their workflow is yet different. I'm first looking at one time ACHEFT - Contribution $1

[lcdservices]

no, it's not an event. it's a straight forward one-time donation via EFT recurring seem to work fine -- the problem we're seeing is with one-time contributions.

[KarinG]

Ok - I've confirmed this is a bug. @adixon - contribution ID 3704 is not verifying.

[KarinG]

Note that the original Contribution even though it was an ACHEFT one has Payment Method: Credit Card.

[adixon]

That's the key there - only contributions of payment instrument type 2 get verified. Try changing the payment method of the contribution (don't worry about the financial record), that should fix it.

I'll take a longer look to see why the contribution was created as a credit card payment method.

[KarinG]

Cool - changing Payment Method from Credit Card in the GUI to Debit Card -> we now have a fees section:

Trying to verify: Completed now. And the Fees section has disappeared again.

[KarinG]

Another issue: Payment Method is empty on the financial records level:

[KarinG]

I did get a Fatal Error running the verify job "Failure, Error message: Failed to complete transaction: Contribution already completed" - but that may or may not be related to this specific one.

[KarinG]

Ah - this may be why -> new field on the Payment Processor Config screen - it defaults to Credit Card - setting that to Debit Card - and running more tests.

[adixon]

The upgrade sql upgrade_1_5_003.sql was supposed to set all those values, but I can see that it only ran for 4.7+, so if you upgraded to 4.7 after running that upgrade, it's going to fail.

Okay, so we'll need to rerun that upgrade sql, pretty much any time caches get cleared ...

[adixon]

If you want to try this branch: https://github.com/iATSPayments/com.iatspayments.civicrm/tree/iATS-managed-recurring

... it should fix everything. I'll fix the conflict and merge it in for a pre-release next week.

[KarinG]

Just a note to confirm that manually setting that Payment Method to Debit Card does fix the issue. One time ACHEFT is now getting properly completed. Did you make a 1_6_1.sql upgrade to make this edit for existing payment processors? I'll test/check the new work next week!

[adixon]

https://github.com/iATSPayments/com.iatspayments.civicrm/releases/tag/1.6.1-beta is ready for testing. Re: sql upgrade - well, it's a more complicated answer than that.

1. The new verify process will verify independent of the payment method.
2. The sql is already there as upgrade_1_5_003.sql, but yes, that is now being re-run to fix/force the payment method for consistency.
3. There are three other sql scripts that run as well, relating to the refactoring of the verification process. I've also got a renaming of the reserved payment method id 2 from Debit Card to ACH/EFT.

[lcdservices]

Just to clarify -- with this update, the iatsacheftverify job is replaced with iatsverify, correct?

[adixon]

Exactly correct.

[natewilldo]

:) Fixed my issue. Seems to be working well.