4

CiviCRM 4.6 - Moneris payment processor -

CiviCRM's invoice uses the ID column to generate the Invoice number (INV_XXXXX) but creates its own hash and adds it to the invoice_id column - is there a way to reconcile the two? It seems CiviCRM uses the ID column to reference a contribution everywhere except in the invoice_id column after a successful transaction as been made.

We notice that when "Send Invoice Email" is used in the Contribution view, it changes the invoice_id column to the INV_xxxxx naming which has given our client major reconciliation headaches. Based on the database logging, the "Send Invoice Email" function sends an Insert request and updates the invoice_id field.

I tried searching the issues tracker, but couldn't find anything.

Before and after images included -

enter image description here

enter image description here

  • The contribution ID column is a good unique reference field. I'm unfamiliar with the hash value being placed in the invoice_id field. That looks like a bad, undocumented hack to me, likely by a payment processor, from a time when core made this field available for manually entering an invoice number that the contribution paid. So that would be code that should be fixed. – Joe Murray Mar 31 '17 at 18:47
  • Here's my rebuttal to the trending trashing of the use of the hash in the invoice_id field: homeofficekernel.blogspot.ca/2017/04/… – Alan Dixon Apr 26 '17 at 18:45
1

It looks like in CiviCRM 4.6, the invoice_id hash gets replaced by the INV_xxxxx number the moment there's an "action" taken - the invoice is downloaded or edited, for example.

Not sure why.

0

This answer is significantly revised based on feedback from colleagues here and in Mattermost, and a bit of subsequent research.

The contribution ID column is a good unique reference field.

I had forgotten about the hash value being placed in the invoice_id field. It seems that very long ago Lobo (2007) started to put a unique hash value in this field to facilitate reconciliation with online payment processors, which later migrated into the code for various payment processor integrations. I don't think this is a good name for a field storing data for that purpose, nor is a hash a good invoice number. I suppose there is a field called invoice id on some payment processors that can accept an ID from CiviCRM which is ideally unique. The problem is that a hash value is not a human friendly value for an invoice, and best practices for auditing would want to see that no invoices have been issued that are not still in the system, which is best facilitated and made auditable by a consecutive numbering scheme or similar. I do think that the way the field was used for manual entry of invoice numbers makes sense.

There was a different field, trxn_id, also added in v. 1.3, that is now in the civicrm_financial_trxn table, that was intended to be used to store unique numbers received from payment processors. Perhaps it would have been good, or still could be good, to use this field instead for a unique value intended to match civi to payments in these external systems.

A further and more important problem with the way this invoice_id is stored and used currently by several payment processors is that CiviCRM has supported partial payments on events for a few years. The schema was changed to support this i 4.3 or 4.4 by me and dgg. I think it was spec'd and funded for 4.5 by CompuCorp and implemented by WebAccess and overseen by dgg (I wasn't involved).

Partial payments has since been extended to regular contributions and memberships. During this partial payments work, the payment specific information has been stored in the civicrm_financial_trxn table since that allows one record per payment. There is no way to track multiple payments on a contribution using a field on the contribution record like invoice_id.

The INV_12228 invoice id was added in 4.6 to provide a valid invoice id that complies with UK law for invoice numbers. The bad part was that when it was implemented it would have been nice to notify the maintainers or fix the code of the relevant payment processor plugins that were (still) using this field to correlate payments with the external systems.

At this point my suggestion is that interested parties should scope work involved in fixing this. One option would be to migrate data from civicrm_contribution.invoice_id to civicrm_financial_trxn.trxn_id and update code to store this way. If some processors use both trxn_id and invoice_id then we should create an invoice_id on civicrm_financial_trxn table, migrate data as appropriate, and also update the code to store info in this new location.

  • Hmm, I think that I wrote this Moneris code for you about 10 years ago, Joe? The hash in the invoice id has always been there as far as I recall, I'm not sure why you would consider that a hack (I think I probably cribbed it from another payment processor ...) - the point is to make it unique. What doesn't make sense to me to be changing the invoice id after the payment processor sets it, since that id is one way of matching things up with the payment processor reports. – Alan Dixon Mar 31 '17 at 19:19
  • Payment processor unique id is not an invoice id. It would (have been) be better to a field appropriately named and purposed for payment processor unique id. – Joe Murray Mar 31 '17 at 22:13
  • So long as this issue is in discussion, let's direct interested parties here: wiki.civicrm.org/confluence/display/CRM/… – Alan Dixon Apr 3 '17 at 15:38
  • re: lobo's use of invoice id as a unique id to send to payment processors. Yes, not ideal and subject to confusion, but more forward looking than I think you give him credit for. The tricky bit is to find something relatively standard on the payment processor side to hold a unique id, and the invoice number was and probably still is really the best option. – Alan Dixon Apr 3 '17 at 15:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.