Phase 1 Implementation - Data Structure Changes

This page covers recent changes made in the current (3.2) version of CiviCRM. This phase of CiviAccounts focused on data structure changes in support of these concepts: The data model should allow folks to distinguish 'commitments to pay' (contribution, membership signup, event registration, etc.) from 'payments'. The financial transaction table is the place to record all payments when they are made. The schema needs to change to support 1 payment for multiple 'commitments to pay' AND / OR multiple payments for 1 'commitment to pay'. Issue Tracker link http://issues.civicrm.org/jira/browse/CRM-5970

Data model changes

Background

The changes to the data model outlined below allow:

1. The creating of multiple financial accounts, including accounts of type Asset, Liability, Income and Expense (new civicrm_financial_account table)
2. Record either a Credit (eg refund) or a Debit (eg payment) transaction to a specified financial account (civicrm_financial_trxn extended to include to_account_id and optional from_account_id)
3. A Debit to be recorded against an Accounts Receivable account when a "pay later" option is used
4. Link multiple entities (contribution, membership, participant etc) to a financial transaction (eg payment or account receivable) (new civicrm_entity_financial_trxn table)
5. Credit an Accounts Receivable account when a payment is made for a "pay later" item
6. Record a deposit of multiple financial transactions to a bank account (eg banking the proceeds of multiple payments at end of day/week) (also using new civicrm_entity_financial_trxn)

The changes do not provide:

1. A shopping cart (although a cart (stored in civicrm_cart) could use a table much the same as civicrm_entity_financial_trxn, called civicrm_entity_cart to store the entities in the cart, with each entity getting a new status called Cart so you can track what items a user has in their Cart but hasn't yet purchased)
2. An invoice (although an invoice (stored in civicrm_financial_invoice) could use a table much the same as civicrm_entity_financial_trxn, called civicrm_entity_financial_invoice to record the entities in that invoice, and the civicrm_financial_invoice could be another entity linked to a civicrm_financial_trxn through the civicrm_entity_financial_trxn table)
3. A receipt (although a receipt (stored in civicrm_financial_receipt) could be linked to a civicrm_financial_trxn through the civicrm_entity_financial_trxn table)

1. New tables added:

civicrm_entity_financial_trxn: This table is storing the relationship between contact payment activities and actual payment records.

id: Key
entity_table: external entity table (civicrm_contribution, civicrm_membership, civicrm_participant etc)
entity_id: external entity id (contribution_id, membership_id, etc)
financial_trxn_id: FK to civicrm_financial_trxn id
amount: allocated amount from transaction to this entity
currency: while currency is now in this table, it probably shouldn't be as the currency is stored in the transaction itself - so this table just needs to store the amount apportioned to this entity

civicrm_financial_account: This table contains the account information and should probably be extended to include an accounting_code that can be entered to reflect the corresponding account in the user's accounting system

id: Key
contact_id: FK to civicrm_contact id ondelete SET NULL constraint (contact can be an individual or organisation)
name: name of the account
account_type_id: FK to civicrm_option_value (account_type)

2. Tables modified:

civicrm_financial_trxn This table contains transactions information.

Remove:
contribution_id

Add:
from_account_id: Optional FK to civicrm_financial_account id
to_account_id: Optional FK to civicrm_financial_account id

civicrm_option_group civicrm_option_value
(no structure change but data update)

Add "Account Type" group and "Asset, Liability, Income, Expense" type values.

3. Tables that may need to be modified (later stage):

civicrm_pledge_payment: change contribution_id to financial_trxn_id - although the "Income" perhaps should be stored as a contribution, with the payment recorded in civicrm_financial_trxn and linked to that contribution and/or pledge through the civicrm_entity_financial_trxn table

4. Tables that may need to be removed (later stage):

civicrm_membership_payment civicrm_participant_payment - as the linkage between payments and memberships/events will be stored in civicrm_entity_financial_trxn, although again, the "Income" perhaps should be stored as a contribution, with the payment recorded in civicrm_financial_trxn and linked to that contribution and/or pledge through the civicrm_entity_financial_trxn table

Files to add:

1. CRM_Contribute_DAO_EntityFinancialTrxn
DB class to insert record to civicrm_entity_financial_trxn table.

Codebase files might need to change

Currently only online payment processing will use table civicrm_financial_trxn. Once civicrm_financial_trxn is updated, CiviCRM will need to insert records to civicrm_entity_financial_trxn.

1. CRM_Contact_Form_Search_Custom_EventAggregate

** This has been more or less superceded by CiviReport so ensuring the report works well is probably more important - issues are around whether multiple participants display correctly (although the event report DOES show GST which is nice - but I think enhancing the CiviReport is the way to go)

Need to join civicrm_entity_financial_trxn for contribution_id.

2. CRM_Contribute_BAO_FinancialTrxn

Move to CRM_Core_BAO_FinancialTrxn

Insert into civicrm_entity_financial_trxn when creating financial transactions.

Workflow:

1. Create an account for contact A who is going to receive money -- store information in civicrm_account table.
2. Create a transaction for contact B who made the payment to contact A. -- store information in civicrm_financial_trxn.
3. On the payment made above, create a new contribution (or for membership/event registration fee in later stage) or add to an existing contribution. -- store in civicrm_entity_financial_trxn table.