1. Home
  2. Blogs
  3. Blogs

Invoices - quick review of what exists and thoughts on what's missing

Published
2009-08-14 11:03
Written by
Dave Greenberg
I've been offline for a week, so a bit of delay in continuing this discussion on Invoicing and Accounting Integration. I thought it would be helpful to jump in with a quick review / reminder of existing functionality and data structures and some thoughts about what's missing based on my understanding "so far".
  • All financial transactions in CiviCRM are represented by a Contribution record. This includes "regular" contributions, membership payments, event registration payments and pledge payments. For "multiple participant registrations", a single contribution record is created for the grand total.
  • The Contribution record includes in Invoice ID column. This is populated automatically with a unique value for "online" transactions, and is can be populated manually (via form field) for offline transactions. The auto-populated values are alphanumeric (and not "user friendly") - example: 56416ae3d6cec1d5348580aec90e1f64
  • Invoice ID's are included in contribution exports but are not included in receipts. Invoice iD's are also not searchable via the search interface.
  • An additional Finanacial Transaction record is created for each online transaction. This record stores payment-processor specific details, notably the processor-generated transaction ID and result code.
Based on the recent discussions, some missing pieces / issues I see:
  • Ability to generate an invoice for payment(s) due and "send" that to constituents. (The closest we have to this currently is the concept of a contribution in Pending status.)
  • Method for handling multiple payments against a single invoice-able "purchase" - i.e. participant pays for an event in multiple payments?
  • Method for handling a single payment that covers more than one thing - i.e. a credit card transaction which covers a membership fee AND an additional contribution?
  • Consistent (and possibly configurable) method for generating invoice ID's, making them searchable, and including them in various outputs (e.g. receipts and reports).
  • Integration / reconciliation with accounting systems. This issue seems the most complex given the variations in business processes. I'm hoping some folks can "scratch their own itch" and come up with a game plan that works for their environment. Once we have a few good examples, we can figure out if there is generally useful schema changes and / or functionality that could be included in the core.
Based on this list, the addition of a separate Invoice record to the schema might be one direction worth exploring. This could facilitate solutions for the first 4 items (and "might" help with integration). This idea is similar to the "receipt" record proposed by Andrew here - but perhaps a bit more general?? Would love to hear folks thoughts on this.
Filed under
Finance and Accounting

Comments

+1 on this - so glad this conversation is taking place. I will discuss with the Kabissa team about our requirements for invoicing.

Generally we'd like to be able to reconcile with QuickBooks, and also to have a frontend for users of our site to be able to see their own billing history and upcoming payments due.

Thanks!

Tobias

Hi Dave,

My initial response to the feedback I got with regards to the accounting system was that it showed such a huge diversity of approaches but the more the discussion went on and the more I thought about it I realised that the underlying needs were not that different.

I felt that a clear consensus came through on the need for a separation of commitments to pay (invoices) and payments (financial transactions).

I originally started out thinking that the contributions table represented the payments and that what was missing was a table holding the invoices. However, after a lot of thought I have started to think that the contributions table actually represents the commitments to pay (invoices) - fulfilled or otherwise. Which kind of brings me pretty close to gelhuifes position I think - that the payments information is the areas that needs to be improved.

I was wondering whether it would be possible to record all payments of any sort in the 'financial transactions' table - with appropriate details. There would need to be a table matching invoices against payments (which I believe there is). This would fit the multiple payments against one invoice fine but in order for a payment to match against more than one invoice there would need to be an amount field.

If the contribution table is seen to be the invoice table then the contribution ID is a valid invoice number. However, any tables holding data about invoices, payments or credit matches need to hold fields for a bank reference, a payment reference and an accounting system reference. There should probably also be a invoice type like 'accpay' or 'accrec' (these are the terms our accounting system uses to diffentiate between incoming & outgoing contributions).

This is probably a pretty difficult idea to implement as it might involve data migration but I think any solution to making CiviCRM better situated for accounting integration needs to be based on an understanding of the key record types in accounts systems and to define which table (or query) represents invoices and which represents payments. People may not choose to issue invoices for all the various commitments people make to pay -e.g. a donation, or they might want to customise them but the underlying database should still recognise them as invoices.

It's also important to recognise that in accounting terms invoices and payments are not deleted. Invoices can be cancelled (credited) and payments can be reversed but reversed payments result in a second transaction - not in the first being cancelled.

It's also worth noting that accounting systems separate payments & invoices because they have figured out over time how to deal with the challenges of payments and invoices not matching on a one-to-one basis

Not sure the status difference is that important (commitment vs. real payment). On accounting, the invoice comes before the payment in general for instance. Moreover, at least in France in B2B, you get "pro format" invoices, that are basically quotes, before the client orders.

I would think that with the cleanups Dave suggest, some kind of basic receipt/invoice and a search on the id, you cover well lots of "basic" needs.

I'd imagine an external ref, like what exists on the contacts might be needed, and a pre (to generate a ref for instance)/post payment (to write it into the accounting system) hook would open the Civi enough for the most complex needs.

As for "you can't delete an invoice", this legal requirement is rather a pain and is usually worked around legally in France with the notion of "temporary" invoices, that are sent to the client and can still be modified, and are "validated" (and not modifiable anymore) only upon the payment by the client.

I would strongly suggest to don't go further the most basic "receipt/invoice" in civi and work on interfacing tools rather than trying to become a bloatware ERP.

My comments about the separation of payment & invoice are oriented towards what would be needed for accounts system integration. Since accounts systems work on the basis of invoices & payments if you wish to synchronise them you need to be able to identify these types of transactions. Also, Dave alludes to the need to be able to have multiple payments for one invoice & multiple invoices for one payment.

Breaking the one-to-one relationship between commitments to pay and payments really does require a clear consensus on how CiviCRM represents each type of transaction. It could be argued that invoices at the moment are represented by a variety of tables (participation records, membership records, pledges) and that contributions are payments (this was my original perspective) or that contributions are invoices and it is the payments that need to be separated out in order to break the one-to-one.

My comments are more about the underlying data and concepts that I believe would facilitate any sort of synchronisation with an accounts system than the front end interface. I would be inclined to say that completed contributions should not be able to be deleted but they could be cancelled. A 'pending' (ie. unpaid) contribution/invoice status would still be editable. If it is cancelled after payment there are two possibilities - full refund or partial refund. I find it hard to see how you can keep visibility of the partial refund without using a second contribution/invoice record - a credit note.

I'm using accounting terms not because I want to turn CiviCRM into an ERP but because I believe that thinking about how data about financial transactions fits into an accounting model is the way in which CiviCRM can facilitate others to do the integration. Also, because I believe some work needs to go into breaking the one-to-one relationship between invoices & payments currently in CiviCRM and I think that having clarity about how CiviCRM represents these types of financial transactions sheds light on the best way to do that.

Hi Father Shaun,

This is a good point. There are two ways to think about pledges in the context of invoices:

1) a recurring invoice
2) a single invoice with multiple payments.

I suspect that pledges *often* (or at least sometimes) don't make it to completion so the recurring invoice idea is a better fit. The pledge record it thus the instructions for generating a series of invoices (contribution records) against which payments will be receipted.

I note you refer to a pledge payment object in your spec. This object seems to cover fields to do with actual payment (which I imagine going into whatever the payments table is), details about the pledge (pledge table), and details about follows up made. I wonder if the follow ups could use activities as a basis rather than needing their own table?

I don't think it matters what we call it internally, nor does it logically need a different data structure in my mind. An interface that uses pledge language to display data from a pending contribution structure would be fine. We need three basic functions:

  • Track individual and total pledges/pending contributions per donor and fund.
  • Record contributions of any amount against the pledge/pending contribution.
  • Report on total contribution and contribution remaining per donor and fund.

I agree that while there may be good reasons to distinguish a Pay Later Contribution from a pledge to make a contribution on the fundraising side, from an accounting perspective they need to be treated pretty much the same.

I'm also a bit confused by terminology around what is sent to someone when they have made a payment. From my perspective the Receipt and Thank You email are pretty similar. I can imagine a thank you be issued immediately for a pay later contribution, a receipt and thank you issued at the time of the payment being received, and a follow up thank you letter or email being sent later for relationship building purposes. I think there may be some terminological confusions and process differences confusing things on this front.

Permalink
Anonymous (not verified)
2009-08-19 - 19:07

Here's what we've been trying to do.

I'm working with a school. They need to be able to assign courses to a student along with the associated costs. Those costs should be added up to show total for semester. Then payments made should be shown and then a total still due needs to show.

Then there needs to be the ability to print an invoice that can be given to a student to show them how much they owe. They'd also be able to see this information when they are logged into the site.

Dave, as per my correspondence some time ago, thanks for this extra detail - it is really helpful.

We are going to be focused pretty heavily on all of these issues over the next 3-6 months, so will be throwing ideas around quite a bit for input. We'll do most of that on the Finance and Accounting WIKI page and forums, with blogs from time to time.

I think that an "invoice" record and a "receipt" record are somewhat different in that a receipt record would record the receipt of a payment, while an invoice record would record an obligation to make a payment.

At the moment, the Contribution and Pledge records respectively record obligations and intentions to pay like invoices would, however a separate invoice record would enable the collection together of multiple Contributions and Pledges into a single "demand" for payment. It might make sense to create this object at the time of reworking all of these things, so that receipts of money would be applied to an invoice instead of to a Contribution/Pledge.

Sorry for jumping in so late. The single demand/request for payment of multiple outstanding Contributions (I take these are pay later contributions) and Pledges sounds like what accounting systems call a Statement of Account. Normally such a statement would also include payments that have been made, and late fees and interest charges as well.

Hi Joe

The way we are thinking of it is more like an invoice, with each contribution being a line item on that invoice - like carrots and potatoes on your grocery bill.

At this stage, it is anticipated that this information is imported into an accounts package that would handle Statements.

Dave

Thanks again for this explanation. It seems that the best option to do a gradual introduction of the ability to make a payment for multiple contributions in one transaction, or for multiple payments (transactions) to pay one contribution, is to deprecate (or remove) the contribution_id from the civicrm_financial_trxn table and put it in a new table called civicrm_contribution_financial_trxn that just contains an id, contribution_id, financial_trxn_id and allocated_amount

As the civicrm_financial_trxn table is currently only used for online contributions, making this change now should not require too much change to the core code. Essentially when an online payment is made, two tables will be updated instead of 1, and initially the civicrm_contribution_financial_trxn table would just have a single contribution_id for each financial_trxn_id and the allocated_amount would be the full amount of the transaction/contribution (ie no part payments).

In time, the use of the civicrm_financial_trxn table could become part of the standard contribution payment workflow and a payment_to_account field could be added to track which account of a Contact the transaction is being paid into.

If this model can be tentatively agreed, it will allow us to start developing our accounts code with some certainty as to how multiple payments per contribution or multiple contributions per payment will be handled and hopefully we can submit a patch for online payments so that the new table structure can be incorporated into the core.

I think it is a bad time to obsolete this table as I think we are on the cusp of it becoming useful!!

At the moment it probably isn't very useful because of the one to one relationship between financial transactions and contributions, but a lot of the discussion over the last six months with respect to accounts has been the need to introduce a one to many relationship between financial transactions (payments) and contributions (or invoices).

It is logical to me that this be in a financial transactions table, because we are looking at setting up very simple "accounts" that a Contact can have funds go into and out of when they receive money or pay grants... but that is a step for another day that we'll talk about more!

For now, it would be good just to lock in the existing financial transaction table as a table where all payments could in future be recorded, whether against multiple contributions or invoices.
Perhaps we should write all of this up in the wiki?

I agree with this direction, and all of Dave's points.

I also think that the scope of it keeps the main focus on the CRM side of things where it should be rather than straying too much into the accounting side.

In terms of a schema design, if we use the normal pattern for a many-to-many join table then it's worth noting it will be easy to go from 'payments' back to 'commitments' via the "join" table as well as from 'commitments' to 'payments'. If the three tables are P, C, and J, each with a unique id, then J will have two additional FK fields, J.p_id and J.c_id.