Aller directement à la fin des métadonnées
Aller au début des métadonnées

Financial items and the civicrm_financial_item table

A financial item is an item that is included in a Contribution that causes the end user to incur a financial charge of a certain amount.  Examples include donations, event fees, membership dues and other similar items. All are separate financial items and are accounted for separately.

Payments are linked in whole or in part to any number of different financial_items through the civicrm_entity_financial_trxn table, which records a link between the financial_trxn table that stores all payments (and transfers between accounts), and the relevant financial_item/s being paid.

Technical Specification

Schema

  1. Create a new table civicrm_financial_item with the following specifications:
    create table `civicrm_financial_items` (
    `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'unique id' ,
    `created_date` DATETIME NOT NULL COMMENT 'Date and time the item was created',
    `transaction_date` DATETIME NOT NULL COMMENT 'Date and time of the source transaction',
    `contact_id` INT(10) UNSIGNED NOT NULL COMMENT 'FK to Contact ID of contact the item is from' ,
    `description` VARCHAR(255) NULL COMMENT 'Human readable description of this item, to ease display without lookup of source item' ,
    `amount` DECIMAL(20,2) NOT NULL COMMENT 'Total amount of this item',
    `currency` VARCHAR(3) NULL COMMENT 'Currency for the amount',
    `financial_account_id` INT(10) UNSIGNED NULL COMMENT 'FK to civicrm_financial_account',
    `status_id` INT(10) UNSIGNED NULL COMMENT 'Payment status: test, paid, part_paid, unpaid (if empty assume unpaid)' ,
    `entity_table` VARCHAR(64) NULL COMMENT 'The table providing the source of this item such as civicrm_line_item' ,
    `entity_id` INT(10) UNSIGNED NULL COMMENT 'The specific source item that is responsible for the creation of this financial_item' ,
    PRIMARY KEY (`id`) ,
    INDEX `IX_created_date` (`created_date` DESC) ,
     INDEX `IX_transaction_date` (`transaction_date` DESC) ,
     INDEX `IX_source` (`source_table` ASC, `source_id` ASC) ,
    INDEX `FK_financial_item_contact_id` (`contact_id` ASC) ,
    INDEX `FK_financial_item_contact_id_to` (`contact_id_to` ASC) ,
    CONSTRAINT `FK_financial_item_contact_id`
    FOREIGN KEY (`contact_id` )
    REFERENCES `civicrm_contact` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
    CONSTRAINT `FK_financial_item_contact_id_to`
    FOREIGN KEY (`contact_id_to` )
    REFERENCES `civicrm_contact` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
    ENGINE = InnoDB
    COMMENT = 'Financial data for civicrm_line_item, etc.'
  2. Create a new record in civicrm_option_group for financial_item_status
  3. For the financial_item_status option group, create records in civicrm_option_value for 'Paid', 'Partially paid', and 'Unpaid'
  4. In the table civicrm_price_field_value, add column `financial_type_id` as a foreign key to civicrm_financial_type.id 
  5. In the table civicrm_price_field_value, add column `deductible_amount` DECIMAL(20,2) NOT NULL COMMENT 'Tax-deductible portion of the amount'

  6. In the table civicrm_line_item, add column `deductible_amount` DECIMAL(20,2) NOT NULL COMMENT 'Tax-deductible portion of the amount', 

  7. In civicrm_payment_processor, add column `financial_type_id` int(10) NOT NULL as a foreign key to civicrm_financial_type.id. NB: this field is under review as of dec 18, 2012.
  8. Upgrade script:
    1. When there is a reference in this section to a contribution page, it should be understood to include both event signup pages and contribution pages and contribution pages including memberships.
    2. For price fields that are in price sets that are used on a single page or multiple pages with the same contribution_type_id for all pages, use the contribution_type_id of the contribution_page to determine the appropriate financial_type_id for each civicrm_price_field_value record (according to the process that maps 4.2 contribution types to 4.3 financial_accounts and financial_types).
    3. For price fields that are in price sets that are used on multiple pages with different contribution_type_id's, replicate the price set for each contribution_type_id, and set up the financial_type_id for each one using the algorithm in b).
    4. For price fields that are in price sets that are not assigned to a contribution_page, use the kind of price set (contribution, event, membership) to derive a default financial_type_id from the reserved financial_type_id's for Donations, Events, and Membership Fees.
    5. Set the deductible_amount for civicrm_price_field_value to 0.
    6. This point is under review as of Dec 18, 2012. For each civicrm_payment_processor, create a financial_type and insert its id in civicrm_payment_processor.financial_type_id. For each of these financial_types, create a financial_account with financial_account_type of 'Asset'. Link the financial_account with the financial_type by inserting a civicrm_entity_financial_account record with account_relationship of 'asset account is'. Use the following values:
      1. civicrm_financial_type.name: civicrm_payment_processor.name
      2. civicrm_financial_type.description: civicrm_payment_processor.description
      3. civicrm_financial_type.is_deductible: '0'
      4. civicrm_financial_type.is_reserved: '0'
      5. civicrm_financial_type.is_active: civicrm_payment_processor.is_active
      6. civicrm_financial_type.version_timestamp: now()
      7. civicrm_financial_account.name: civicrm_payment_processor.name
      8. civicrm_financial_account.description: civicrm_payment_processor.description
      9. civicrm_financial_account.contact_id: of the domain organization
      10. civicrm_financial_account.financial_account_type_id: based on 'asset account is'
      11. civicrm_financial_account.account_code, parent_id and tax_rate are all null
      12. civicrm_financial_account.is_header_account, is_deductible, is_reserved, is_tax all '0'
      13. civicrm_financial_account.is_active: civicrm_payment_processor.is_active
      14. civicrm_financial_account.is_default: civicrm_payment_processor.is_default
      15. civicrm_entity_financial_account.entity_table: 'civicrm_financial_type'
      16. civicrm_entity_financial_account.entity_id: civicrm_financial_type.id of inserted record
      17. civicrm_entity_financial_account.account_relationship: 'asset account is' or equivalent
      18. civicrm_entity_financial_account.financial_account_id: civicrm_financial_account.id of just inserted record
  9. This point is under review as of Dec 18, 2012. In the UI for creating and editing a payment processor, provide the option of selecting an existing financial_type from existing one. The financial_type must have a 'is asset account of' relationship with a financial account record with a financial_account_type of 'asset' in order to be validated. Ensure that a financial_type associated with a payment processor never loses having an account with this relationship.
  10. Create a fifth default record in civicrm_financial_account for Banking Fees, with a financial_account_type of expense, is_default=true, and a null value for the accounting_code.

 

Class structure and functionality

Ensure the following functionality in BAO classes:

Hooks

hook_civicrm_pre($op, $objectName, $id, &$params)

Before operations 'view', 'create', 'edit', and 'delete', invoke this hook for each financial_item, with these values:

  • $op: 'view', 'create', 'edit', or 'delete'
  • $objectName: 'financialItem'
  • $id: NULL
  • $params: all values to be stored in civicrm_financial_item (including line_item_id, which should have been created at this point)

hook_civicrm_post($op, $objectName, $objectId, &$objectRef)

After operations 'view', 'create', 'edit', and 'delete', invoke this hook for each financial_item, with these values:

  • $op: 'view', 'create', 'edit', or 'delete'
  • $objectName: 'financialItem'
  • $id: the value of civicrm_financial_item.id
  • $objectRef: the FinancialItem DAO object

Code changes

Changes will be made to the code in relation to both the creation of the "obligation" (Financial Item) and "payment to satisfy the obligation" (Financial Transaction).  A Financial Transaction will also be recorded for the "Accounts Receivable", which are the asset created by unpaid obligations.

Recording the Obligation - Line Item / Financial Item (Income)

When a contribution is made, at the point where civicrm_line_items are being created, one row will be inserted into civicrm_financial_item for each line item as follows:

  1. created_date: (today's date)
  2. transaction_date: (date of contribution the civicrm_line_item is in)
  3. contact_id: civicrm_contribution.contact_id
  4. description: (civicrm_line_item.qty<>1 ? civicrm_line_item.qty . ' of ' : '') . content of this item's label as displayed in the contribution form)
  5. amount: civicrm_line_item.line_total
  6. currency: (the value of civicrm_contribution_page.currency for the page where the contribution came from)
  7. financial_account_id: search civicrm_entity_financial_account for entity_table='civicrm_financial_type' and entity_id=civicrm_line_item.financial_type_id and account_relationship='Income Account is', and return financial_account_id (I understand the financial_type_id has/is being added to the line_item table and is recorded there based on the price_field_value at time of creating the line_item, so any later change in the price_field_value table does not cause a loss of data integrity)
  8. status_id: (the value of civicrm_option_value.value where the option_group is 'financial_item_status' and the option value name is "Unpaid")
  9. source_table: 'civicrm_line_item'
  10. source_id: (the value of civicrm_line_item.id for the line_item record)
  11. Other fields are left at their default values.

Recording an initial transaction with or without a payment 

  1. Insert a record in civicrm_financial_trxn for each contribution, event, and membership transaction, setting civicrm_financial_trxn.to_financial_account_id as follows:
    1. If the transaction is just pay_later, then select the to_financial_account_id using the financial_type and civicrm_entity_financial_account.account_relationship of accounts receivable.
    2. If the transaction is immediate payment of the full amount, then select the to_financial_account_id using the civicrm_payment_processor.financial_type_id and civicrm_entity_financial_account.account_relationship of asset.
    3. If the payment is a manual payment for the full amount by Cash/Check, then
      1. if there is only one financial account of financial_type=asset, then use it
      2. if there is more than one financial_account of financial_type=asset, then an additional field will be displayed to the admin user just under Contribution Type / Financial Account allowing them to select "Payment Received Into" (ie to_financial_account_id) with a drop down list of all financial_accounts of financial_type=asset, with the default selection being the one with is_default=true.
    4. If the payment is for part of the transaction amount, then create one entry as above in 1a for the pay later amount, and another entry in civicrm_financial_trxn for the rest of the amount as per 1b or 1c, whichever is appropriate.
  2. Create a row in civicrm_entity_financial_trxn between the financial_trxn and each financial_item in the transaction:
    1. Set civicrm_entity_financial_trxn.amount to the amount specified in the UI for this item (see "Technical Specification: Calculating paid amount of items in initial payment" in CiviAccounts Specifications-Flexible User Payments).
      1. However, reject the transaction with an error if the total of all civicrm_entity_financial_trxn.amount values for this item is either 
        1. more than the amount of the item itself (i.e., don't allow the item to be over-paid), or 
        2. less than zero.
    2. Calculate ITEM_TOTAL as the total of all civicrm_entity_financial_trxn.amount values for this item, and set civicrm_financial_item.status_id as follows:
      1. If ITEM_TOTAL is 0, set to the value of civicrm_option_value.value where the option_group is 'financial_item_status' and the option value name is "Unpaid"
      2. If ITEM_TOTAL is equal civicrm_financial_item.amount, set to the value of civicrm_option_value.value where the option_group is 'financial_item_status' and the option value name is "Paid"
      3. If ITEM_TOTAL is less than civicrm_financial_item.amount, set to the value of civicrm_option_value.value where the option_group is 'financial_item_status' and the option value name is "Partially paid"
  3. Create upgrade code that translates existing civicrm_financial_trxn records linked to civicrm_contribution records so that they link to civicrm_line_items for those civicrm_contribution records.

Recording Fees

If the financial_item is being paid through a payment processor and a fee_amount is charged, or if the contribution is being manually recorded and a fee is recorded, then create an additional civicrm_financial_item for the fee as follows:

  1. Link this civicrm_financial_item to the civicrm_financial_trxn using both through the civicrm_financial_item.entity_table and civicrm_financial_item.entity_id (this indicates what the fee is about) and through civicrm_entity_financial_trxn (this shows the the civicrm_financial_item has been paid, and that the payment that caused fee actually paid for the fee):
    1. civicrm_financial_item.entity_table = 'civicrm_financial_trxn'
    2. civicrm_financial_item.entity_id = the new civicrm_financial_trxn.id
    3. civicrm_entity_financial_trxn.entity_table = 'civicrm_financial_item'
    4. civicrm_entity_financial_trxn.entity_id = the new civicrm_financial_item.id
  2. For both manual and payment processor cases, use the to_financial_account_id for the payment identified in "Recording an initial transaction with or without a payment", point 1 as the from_financial_account_id for the fee.
  3. Select the to_financial_account_id as follows:
    1. For a payment_processor, use the financial_type and civicrm_entity_financial_account.account_relationship of expense. If none exists, raise an error. (We don't want to force this at config time since some payment processors may not report fees back).
    2. For a manual payment,
      1. if there is only one financial account of financial_account_type=expense, then use it
      2. if there is more than one financial_account of financial_type=expense, then an additional field will be displayed to the admin user in the Additional Details fieldset just under Fee Amount allowing them to select "Fee Account" with a drop down list of all financial_accounts of financial_type=expense, with the default selection being the one with is_default=true.
  4. status_id: Option List id for Paid (on the basis it has already been taken out of the trxn and therefore the financial_trxn.net_amount is exclusive of the fee).
  5. set civicrm_financial_item.contact_id to the domain or system owner contact id (see CRM-9897).

Recording Pay Later Payments

For the payment of a previous unpaid/part paid financial_item (which will currently come from a contribution/line_item, but thanks to the description being stored in the financial_item, even obligations coming from third party forms/drupal modules/joomla extensions/web services can be displayed for payment through CiviCRM):

  1. check the total payment matches the total of the individual financial_item amounts 
  2. check that the financial_items the payment is for have status unpaid or part paid, and
  3. if the financial_item is part paid, use the entity_financial_trxn table to locate the transactions partially paying that financial_item and ensure that the current payment combined with the sum of the financial_trxn using the "Accounts Receivable Payment Processor" for that financial_item, will not exceed the value of the financial_item,
  4. record an equal negative financial_trxn against the account_id for the financial_account used by the "Accounts Receivable Payment Processor" for that financial_item previously, to pay out the account receivable, that is:
    1. from_financial_account_id is the accounts receivable account
    2. to_financial_account_id is the asset account
    3. the amount of the payment
  5. link the new financial_trxn to the financial_trxn for the payment via the entity_financial_trxn table.

This process will create records that can be synced with a financial accounts package whether they use a cash or an accruals system of accounting, with the financial_item table recording changes in the "Profit and Loss accounts" (Income and Expenses), while the financial_trxn table records changes in the "Balance Sheet" (Assets and Liabilities).

Changes/Reversals

There are two different categories of changes/reversals. Changes/Reversals to:

  1. Financial Items (ie edits to contributions, memberships and event purchases); and
  2. Financial Transactions (eg edits to payments)

The UI may allow the user to change an existing transaction and simultaneously make a payment or refund for some or all of the amount at the same time. We will spec out what happens for the line items and financial items first, then move on to the financial transactions second. However, every accounting transaction must be balanced, so there is some interaction between the two - the total difference in the financial items has to be reflected in a corresponding entry in the civicrm_financial_trxn table.

Changes/Reversals to Line Items / Financial Items

Line Item Changes

For each line item being saved after an edit where an attempt is being made to change/reverse a line-item:

  1. There may have been a change either to the financial_type for the line item that results in a new income account, or the income account within the existing financial type may have changed (though it is possible that a change in financial_type has not resulted in a change of income account). If there is a change to the line_item.financial_type_id, check via entity_financial_account.account_relationship for the "Income account is" to see if that account is different than the existing financial_item.financial_account_id. If changed, get confirmation from user, create a transaction that move the original amount from the original income account to the new income account, and proceed with rest of transaction.
    1. More specifically, if entity_financial_account.financial_account_id != financial_item.account_id, display a confirmation dialog that "The Income Account for the <line item label> has been changed from <original account name> to <new account name>. Clicking OK will move this income from the old to the new income account."
    2. If Cancel, return to edit form.
    3. If OK,
      1. create a new financial_item record the same as the old one with the new financial_account_id
      2. create a financial_trxn record with from_financial_account_id=old financial_item.account_id and to_financial_account_id=new financial_item.account_id, with amount=old financial_item.amount.
      3. create two entity_financial_trxn records connecting the new financial_trxn record to both the old and the new financial_item records
  2. If the change would result in the total of the financial_item for the line_item becoming less than zero, throw an exception and don't proceed
  3. Create a new financial_item with source_table and source_id set to the civicrm_line_item with the amount of the adjustment against the account
    1. E.g. if the original amount is 100 and the new amount is 50, then the adjustment is -50; if the original is 100 and the new amount is 125, then the adjustment is 25.
  4. Aggregate all financial_item amounts for the contribution to determine the contribution.total_amount. Aggregate all financial_trxn amounts to determine the amount paid on the contribution (take care to determine whether to add or subtract amounts to the total based on the account type of the to_account, as per item 2 in Financial Trxn Changes below). If the amount paid is less than or equal to 0, set all of the financial_item.status_id to Unpaid; if amount paid >= contribution.total_amount, set all financial_item.status_id=Paid; otherwise set all of the financial_item.status_id to Partly paid.
Financial Trxn Changes

Store any changes in the total amount owing for the whole price set/transaction as follows:

  1. Determine the total_difference_amount for the whole price set/transaction including any payment or refund, ie the change in the amount of the obligation: Sum of line items in current version of price set that is about to be stored minus the sum of the line items of the previous version of price set that is currently in the db.
  2. Determine the total_amount_previously_owing by adding or subtracting financial_trxn amounts for the transaction as follows:
    1. Asset: subtract (this means a deposit has been received)ll
    2. Liability: add (this means a payment such as a refund has been made)
    3. Accounts receivable: add (this records what is owed eg for a pay later)
    4. Accounts payable: subtract (this records what has to be paid, eg a refund that will be paid via cheque to be cut later)
    5. Revenue: add (this is the obligation of a donor, and appears in this field if an accounts receivable is being cancelled)
    6. Expense: subtract (this supports recording what is owed to a grant winner, and appears in this field if an accounts payable is being cancelled)
  3. Calculate total_amount_currently_owing = total_amount_previously_owing + total_difference_amount - payment.amount
  4. Record any payment being made at the time of the transaction as one, two or perhaps three financial_trxn records: against a/r amount, a regular payment, and potentially an overpayment (payment.amount > 0):
    1. if total_amount_previously_owing > 0
      1. if payment.amount <= total_amount_previously_owing, create one financial_trxn with:
        1. from_financial_account_id = a/r account
        2. to_financial_account_id = the asset account for the payment
        3. financial_trxn.amount=payment.amount.
      2. if payment.amount > total_amount_previously_owing,
        1. create one financial_trxn with
          1. from_financial_account_id = a/r account
          2. to_financial_account_id = the asset account for the payment
          3. financial_trxn.amount=total_amount_previously_owing
        2. create a second financial_trxn with from_financial_account_id = null, to_financial_account_id = the asset account for the payment and financial_trxn.amount=payment.amount - total_amount_previously_owing
      3. if total_amount_previously_owing >= 0, then record payment in financial_trxn with from_financial_account_id blank
  5. Record any refund being made at the time of the transaction as one, two or perhaps three financial_trxn records: against a/p amount, a regular refund, and an inappropriately large refund that creates an a/r to be paid later:
    1. if total_amount_previously_owing < 0, then
      1. if refund.amount <= - total_amount_previously_owing, create one financial_trxn with from_financial_account_id = a/p account and to_financial_account_id = the asset account for the refund and financial_trxn.amount=refund.amount
      2. if refund.amount > - total_amount_previously_owing, create one financial_trxn with from_financial_account_id = a/p account and to_financial_account_id = the asset account for the refund and financial_trxn.amount=refund.amount and a second financial_trxn with from_financial_account_id = null, to_financial_account_id = the asset account for the refund and financial_trxn.amount= total_amount_previously_owing - refund.amount
  6. Create entity_financial_item records linking financial_trxn to the financial_item records that have been created for the change transaction.
    1. In 4.3, the amount of each financial_trxn will be divided between the newly created entity_financial_trxn records.

 

  1. If this total_difference_amount is a positive number, then more money is owing from contributor. Create a new financial_trxn record for the adjustment amount. Depending on invoking context and adjustment, this may be:
    1. a reduction of a refund owing amount,
    2. the elimination of a refund owing amount,
    3. a pay later a/r transaction,
    4. a pay immediate asset transaction for an online payment or,
    5. a pay immediate manually recorded payment entered at same time as changes
    It is possible for b) to be combined with c, d, or e. Store a new transaction for the appropriate case in civicrm_financial_trxn as per 1 of 'Recording an initial transaction with or without a payment' above.
  2. If the result is 0, then continue / do nothing
  3. If the result is a negative number, then either less money is owing from the contributor, and / or a refund is owed to the contributor. Figure out current amount owing on whole transaction as follows:
    1. find all civicrm_financial_trxn records related to the contribution
      1. Select financial_trxn_id from civicrm_entity_financial_trxn for entity_table='civicrm_financial_item' and entity_id in (select id from civicrm_financial_item fi inner join civicrm_line_item li on fi.entity_id=li.id and fi.entity_table='civicrm_line_item' where li.contribution_id=this->contribution_id)
    2. if total_amount_owing < 0 then a refund is owed
      1. Mark the status of all financial_items and the contribution as paid
    3. If total_amount_owing >= 0, then
      1. civicrm_financial_trxn.total_amount = total_amount_owing + total_difference_amount (which is a negative amount) 
      2. if civicrm_financial_trxn.total_amount < 0 then
      3. // we need to offset the existing civicrm_financial_trxn for accounts receivables accounts for this transaction
      4. for each civicrm_financial_trxn for accounts receivables accounts for this transaction
      5. civicrm_financial_trxn.total_amount = - total_amount_owing, to_financial_account_id = the
      6. create another new civicrm_financial_trxn record for a refund with civicrm_financial_trxn.total_amount = total_amount_owing - total_difference_amount, setting the to_financial_account_id to the
      7. if (to_distribute_amount < 0) then
      8. // pay all the owing amounts
      9. for each line_item
        1. create a civicrm_entity_financial_trxn record linking the
        2. set status to paid
      10. if (to_dis

Use that amount to change the contribution.status if necessary (if old status paid and adjustment_total>0, then new status=partly paid; if old_status=partly paid and < some calcuation to be determined using old_total and adjustment_total>, set new status=paid)

Create a new financial_trxn record for the adjustment amount. Depending on invoking context and adjustment, this may be:

  • a pay later a/r transaction
  • a pay immediate asset transaction
  • a refund immediate asset transaction
  • a refund later a/p transaction

Changes/Reversals to Financial Transactions (Payments)

This section will deal with changes to payments.

TBD

Étiquette
  • Aucun

Creative Commons License
Except where otherwise noted, content on this site is licensed under a Creative Commons Attribution-Share Alike 3.0 United States Licence.