Scoping out basic CiviAccounts/CiviBanking

This post started out as a reply to Eileen's "Banking Screen?" comment on my previous post on this topic, but by the time I was done, I thought that this warranted its own post. I think that the core "Accounts" or "Banking" functionality that could be helpful to Civi users without getting too out of control, is:

1. generate a listing of the individual cheque and cash "receipts" processed since the last "deposit" was generated, add selected receipts to a deposit and print it as a deposit listing, using a template that would enable you to submit the listing to your bank with a deposit slip;
2. enable a bank reconciliation to confirm that each deposit was successful and highlight any discrepancies such as bounced cheques or typo's when entering cash or cheque amounts. Ideally you would be able to tick off each receipt within a deposit as reconciled (or just tick the deposit as a whole and have the system mark each receipt within that deposit as reconciled). If a particular receipt bounced, then you could tick off the others as reconciled and click on the bounced one to set the relevant CiviContribution/s status to pending/failed rather than paid
3. being able to record expense amounts against specific CiviEvents or campaigns or perhaps user defined expense categories so you can display the "profit" of a campaign/CiviEvent (Phase 2 - this where it gets more like CiviAccounts than CiviBanking)
4. thereby being able to maintain an electronic copy of your bank statement online (or in Phase 1, the bank deposits), with transactions linked through deposit records to receipts, and then to CiviContribute records

The first step towards enabling the above would seem to be:

1. create a new table called civicrm_bank_accounts - with an id (K), bank_name, account_name, branch_id, account_no and currency (?)
2. create a new table called civicrm_deposits - with an id (K), bank_account_id (FK), date, cash_total(?), cheque_total(?), no_of_cheques(?), currency(?)
3. create a new table called civicrm_receipt - with an id (K), payment_instrument_id (FK) (moved from civicrm_contribution and used to determine if it is a payment by cheque/credit card etc, if I understand correctly), amount, date, currency (?), deposit_id (FK) and reconciled_date
4. create a new table called civicrm_contribution_receipt - with an id (K), receipt_id (FK) and contribution_id (FK). This would enable 1 payment to be received for multiple contributions, or multiple payments to be received for one contribution, and the issuing of a single receipt number for the payment.
5. have a new value for "extends" in the civicrm_custom_group table of "Payment Instrument" (and would you then set "extends_entity_column_id" to the id of the Payment Instrument/Type you want to extend? - I don't think it works that way) so that you can create custom fields relevant to a particular Payment Type in your country* (such as, in the case of cheques in Oz: account_name (of drawer), bank_identifier, branch_identifier and cheque_number), and store them for a particular receipt. The relevant custom data table would then have an entity_id that links to a receipt_id.

So from a data storage perspective, at a quick glance it would seem that the data storage requirements to be able to track and reconcile deposits to bank accounts could be achieved with:

1. four new tables and
2. adding a new "extends" option to civicrm_custom_group

Of course then there is the significant job of:

1. refactoring all contribution code so that it takes into account the new civicrm_receipt table (which could use the current interface at first by assuming that every contribution is a single payment (and therefore receipt), but now store the payment_instrument_id in the new table, together with duplicates of the amount and date stored for the contribution - to reflect that the contribution was made and payment received simultaneously and for the same amount - even if often that may not be strictly true, and pull the receipt number from the new civicrm_receipt for all receipts);
2. modifying the current interface for dealing with contributions/payments so that a single payment can be applied to multiple contributions or multiple payments can be made towards a single contribution, with one receipt per payment; and
3. adding a new "CiviAccounts" or "CiviBanking" component interface to deal with bank account listings, deposits, receipts, reconciliations etc.

I am sure there are things I have missed in my quick scoping exercise, but I thought a bit more detail was required to explain my thinking around this. * Requirements may differ by region. In Australia, deposit slips need to list the details of each cheque in the deposit including the Drawer (account name on the cheque), Bank (that issued the cheque), Branch (eg Sydney) and Amount. The civicrm_contribution table currently has a check_number field (which is not required on a deposit slip in Oz), but doesn't have the other information that is required. The check_number field (or perhaps a new check_id) could be used as a foreign key to a new civicrm_check_details table containing those fields, or else those fields could be added to the civicrm_contribution table (although that doesn't handle one cheque being used for multiple contributions - eg Membership Dues + Donation - and therefore probably shouldn't be considered).

Comments

It seems to me your CiviReceipt table would in fact be what Gelhuife is referring to as the standardised way of dealing with payments

I'm assuming that outgoing transactions for your expenses would also go there? In our accounts package at work some types of payment are automatically reconciled - ie. EFT payments (AKA internet banking payments). So perhaps a more generic name would be better.

How would the existing civicrm_financial_txn table fit - could it be usefully extended for this purpose?

Hi Eileen

I had overlooked civicrm_financial_txn in my analysis.

Dave has also suggested some ideas which I am looking into and will post some more then.

Andrew

Cool,

I just realised that any work on this area should consider holding 3 potential external reference fields:

1) Cheque no / Credit card transaction ref - ie. ref specific to the payment
2) Bank reference number (ie. an identifier from the bank)
3) Accounts reference number (i.e an identifier from the accounting system)

All three should probably be available in the tables for payments/ financial transactions, banking, and anything that could be considered to be an invoice. They may not be used but having them there leaves space for integration to be done without further changes.

My thinking with 1) is that you have custom data fields that could apply to a payment based on the Payment Type, as in different countries you may need different information for different payment types such as Direct Debit or Cheque. Credit Card does seem reasonably standardised globally (not surprisingly).

Agree that 2) and 3) would be useful external reference fields.

Civicrm is used in many countries and it would be nice to have a module that would work with different banking systems all over the world. However it may be a problem updating a system that works with all the different banking systems in the world.

Civicrm is not the only php software that would need this kind of module. Would it be possible to make a generic module that Civicrm communicated with, and that other php software also could use? Has anyone else made a module like this? This could give a bigger pool of developers to keep it up to date.

I didn't catch if this module would contain invoicing, I would like an option of invoicing membership and pledges. And then couple the payments with the invoices.

Fred

Hi - this is a generic comment that could have been made on any of the recent blogs on this issue. I have pointed several clients who are asking questions about this to the blogs in question. It would be much easier if there was a 'civiaccount' or civifinancials category that all these blogs would use. Thanks

created a category called: "Finance and Accounting"

http://civicrm.org/taxonomy/term/25

did not want to name it CiviAccount or Civi* as yet, since there are no firm specs/deliverables etc and hence wanted to avoid confusing folks

lobo