Answers.Body: <p>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.</p>
  
  <p>Not sure why.</p>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-04-26T18:44:12.500Z
Answers.Text: Correct, but that's a bug, see https://issues.civicrm.org/jira/browse/CRM-20387 for the status of my fix.
Answers.UserDisplayName: Alan Dixon
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2016-03-22T20:18:32.257Z
Answers.LastActivityDate: 2016-03-22T20:18:32.257Z
Answers.UserDisplayName: Alex Katayama
Answers.Body: <p>This answer is significantly revised based on feedback from  colleagues here and in Mattermost, and a bit of subsequent research.</p>
  
  <p>The contribution ID column is a good unique reference field. </p>
  
  <p>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. </p>
  
  <p>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.</p>
  
  <p>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). </p>
  
  <p>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.</p>
  
  <p>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. </p>
  
  <p>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.</p>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-03-31T19:19:04.833Z
Answers.Text: 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.
Answers.UserDisplayName: Alan Dixon
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-03-31T22:13:31.300Z
Answers.Text: 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.
Answers.UserDisplayName: Joe Murray
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-04-03T15:38:06.430Z
Answers.Text: So long as this issue is in discussion, let's direct interested parties here: https://wiki.civicrm.org/confluence/display/CRM/2017+issues+with+Sales+Tax+%28like+VAT%29+and+PDF+Sales+Tax+Invoices
Answers.UserDisplayName: Alan Dixon
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-04-03T15:41:15.330Z
Answers.Text: 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.
Answers.UserDisplayName: Alan Dixon
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-03-31T18:50:46.047Z
Answers.LastActivityDate: 2017-04-01T01:17:27.230Z
Answers.LastEditDate: 2017-04-01T01:17:27.230Z
Answers.UserDisplayName: Joe Murray
Body: <p>CiviCRM 4.6 - Moneris payment processor - </p>
  
  <p>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.</p>
  
  <p>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. </p>
  
  <p>I tried searching the issues tracker, but couldn't find anything.</p>
  
  <p>Before and after images included -</p>
  
  <p><a href="https://i.sstatic.net/apmWo.png" rel="noreferrer"><img src="https://i.sstatic.net/apmWo.png" alt="enter image description here"></a></p>
  
  <p><a href="https://i.sstatic.net/DII9J.png" rel="noreferrer"><img src="https://i.sstatic.net/DII9J.png" alt="enter image description here"></a></p>
  
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2017-03-31T18:47:52.603Z
Comments.Text: 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.
Comments.UserDisplayName: Joe Murray
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2017-04-26T18:45:26.343Z
Comments.Text: Here's my rebuttal to the trending trashing of the use of the hash in the invoice_id field: http://homeofficekernel.blogspot.ca/2017/04/civicrms-invoiceid-field-and-why-you.html
Comments.UserDisplayName: Alan Dixon
Tags: <civicontribute><invoice>
Title: Confusion between Contribution ID vs Invoice_ID in the database
ThreadText: # Confusion between Contribution ID vs Invoice_ID in the database
  
  (Asked by: username_0 on 2016-01-13)
  
  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](https://i.sstatic.net/apmWo.png)](https://i.sstatic.net/apmWo.png)
  
  [![enter image description here](https://i.sstatic.net/DII9J.png)](https://i.sstatic.net/DII9J.png)
  
  **Tags:** civicontribute, invoice
  
  > **Comments:**
  > **username_1**: 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.  
  > **username_2**: Here's my rebuttal to the trending trashing of the use of the hash in the invoice_id field: http://homeofficekernel.blogspot.ca/2017/04/civicrms-invoiceid-field-and-why-you.html  
  
  ---
  
  ## 2 answers
  
  ### Answer by username_0 on 2016-03-22. Score: 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.
  
  > **Comments:**
  > **username_2**: Correct, but that's a bug, see https://issues.civicrm.org/jira/browse/CRM-20387 for the status of my fix.  
  
  ---
  
  ### Answer by username_1 on 2017-03-31. Score: 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.
  
  > **Comments:**
  > **username_2**: 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.  
  > **username_1**: 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.  
  > **username_2**: So long as this issue is in discussion, let's direct interested parties here: https://wiki.civicrm.org/confluence/display/CRM/2017+issues+with+Sales+Tax+%28like+VAT%29+and+PDF+Sales+Tax+Invoices  
  > **username_2**: 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.  
  
  ---
  
  