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).