What is a line item? And what is a financial item and how are those two linked together?

Question

I have created an extension to generate invoices (contributions) based on time spend on activities on a case.

For each activity a line item was created and added to the contribution. Also the financial type had 21% percent of VAT configured.

In CiviCRM version 4.6 this extension works smoothly. However after upgrading to CiviCRM version 5.9.1 I cannnot edit contributions any more. It fails with the error Expected one FinancialItem but found 0.

After debugging I found out that the line items created with my extension does not have a related financial item (I did not even know they existed and/or where required).

So now I have changed my extension to create two financial items when one line item is added:

1. One financial item for the amount without VAT
2. And, one financial item for the VAT amount

Contributions created after this change could be edited. Hurray!

The questions

Now comes my questions:

1. What are financial items?
2. How do they differ from line items?
3. Where do I find documentation about line items and financial items?
4. What are financial transactions (trxn)? (See answer of Joe)?

Solution

To create all the underlying financial items etc.. One must use the Order API. The order API does create a contribution and line items.

Answer 1

The Line Item table is oriented towards the current state of an order's line item. The Financial Item table is oriented towards keeping track of the history of the financial aspects of line items. The Financial Transaction table is the base table for financial transactions created by CiviCRM every time there is any add, update, cancel, refund, pay grant or other action that has a financial impact.

At a high level, each time there is a change in a line item that produces a financial effect, like a change in the quantity, or the price, or the financial type associated with the line item, a reversal entry of the previous financial impact of the line item is made in the financial item table, and a new entry is inserted representing the current financial impact.

Another difference is that line items are more like business entities while financial items are aspects of financial accounting entries. To hide the complexity of double entry bookkeeping, CiviCRM uses Financial Types to create a bundle of Financial Accounts that will be used through the life cycle of a transaction for things like its Accounts Receivable account, its Revenue account, and potentially other accounts like the Accounts Payable or Contra Revenue account for refunds owing. These financial accounts are the ones that appear in the Chart of Accounts for an organization. Line items have financial types associated with them, while the specific detailed nature of financial items means they have financial accounts associated with them.

The civicrm_financial_trxn and civicrm_financial_item tables hold the main accounting or bookkeeping information in CiviCRM while civicrm_entity_financial_trxn is a related table that connects records in civicrm_financial_trxn to related records in various other tables. There are many types of financial transactions, and they all are stored in these tables, and are exportable via Financial Batch exports to Accounting Packages like QuickBooks, Sage, and Microsoft Dynamics.

Bookkeeping entries have two sides to them: a debit account and a credit account, as well as an amount and a date. Some bookkeeping entries are split, where a single amount on one side is split amongst several amounts and accounts on the other side. For example, a payment for an event registration and a donation would have the total going into the bank on one side, and revenue being recognized for two accounts on the other side.

In the case of contributions which handle the finances for donations, memberships and event registration as well as other sale items, each line item will create a civicrm_financial_item, each payment will create a civicrm_financial_trxn, and civicrm_entity_financial_item records will connect them. In the case of line items with sales tax, which in general goes to a different account in an organization's chart of accounts, there will be an additional entry in the civicrm_financial_item table representing the tax on a line item.

An important principle of accounting is that books should be auditable. One part of that is to retain records about all of the changes that have been made on items. So if a purchase is made with pay later selected, and two partial payments are made, the number of things purchased is changed, a refund is made, etc., then all of these are tracked in the financial tables in CiviCRM. Entries in civicrm_financial_item and civicrm_financial_trxn tables are intended to never be edited or deleted.

We highly discourage developers trying to write directly to the civicrm_financial_trxn, civicrm_financial_item and civicrm_entity_financial_trxn tables which store financial information in CiviCRM since version 4.3. We'd encourage use of the order api and/or payment api, and will enhance and/or fix issues with those APIs.

Documentation that is reasonably accurate on entries for these interlinked tables for various types of accounting transactions can be found at https://wiki.civicrm.org/confluence/display/CRM/CiviAccounts+Data+Flow#CiviAccounts4.3DataFlow-OnlineorofflinecontributionwithFEE.

The Caldera Forms CiviCRM plugin for WordPress was recently trying to integrate its support for various line item operations with Civi core, so their code may provide useful guidance. The Line Item Edit extension might also be a useful developer resource, though I seem to recall that it needs a bit of refactoring.

Answer 2

The solution to my question is to use the Order API. The order API does create a contribution, line items and the underlyning financial items etc...

Answer 3

Let me try to explain: Line item, Financial item

First of all both line item and financial item are created based on contribution

Line item are created only once and financial items are created based on the contribution status

---

Line item

Line item are used for invoice purpose which holds contribution break down (amount and tax amount)

They are static line which gets changed only when a contribution total changes

---

Financial Item:

Financial items are created for each contribution status - Purely used for accounting purpose (also for finding the outstanding contribution amount to pay)

when you create a contribution as pending you end up with financial item as pending

Then when the contribution is completed you will see one more record for completed

same for cancelled and refund

---

Other linked tables

civicrm_entity_financial_trxn
civicrm_financial_trxn

I guess this helps - I tried my best !!!

Thanks

Answer 4

If I may add another question: Why are we going through this whole rigmarole in the first place with "financial type" "financial item" and line item"s?!

In Quickbooks, things are simple: Every record has two objects, which they represent in rows on a spreadsheet (starting at different columns though): (1) transaction (2) line items. Every transaction must have at least one line item and, but can have more. The transaction is represented by one row in a spreadsheet, the first line item is a second row in a spreadsheet the the second line item is a third etc. The sum total of the "items" (line items) becomes the "amount" on the transaction record. The transaction record itself does not have any items. This way, a single transaction can have a single or multiple "items" and "classes", be recorded as one transaction (and gives you the benefit of tracking quantity & inventory when that's relevant).

In Civi, each transaction is only one line and that one line must have a "financial type" associated with it ("financial type" seems to match QB "items"-because they are both associated with accounts, but not actually accounts).

If you don't use the line item extension, you can't record split transactions, quantities, or inventory.

If you do use the line item extension, you have "line items" and "financial items" & "financial types" which are all essentially the same thing but overcomplicate things for no good reason (they are all a way of recording what was "sold"). All the line item fields are stuck in some weird place that you can only access with a special report. Because the transaction itself requires a "financial type", the "financial types"of the "line items" are overridden, rendered useless (the whole reason are putting different "financial types" on each line item is because this one transaction can't be characterized by one "financial type").

You can't export the line items as CSV, IIF or sync with Quickbooks and it doesn't reflect the reality of business. The QBO integration had to map civi "accounts" to QBO "items/products/services" which doesn't make any sense (shouldn't accounts map to accounts, not "items"?) The reason they had to do this, is because every QB sales receipt or invoice must have an "item", and there's no good item to map to.

Is there any reason civi can't mirror the same setup as QB with (1) "items" (2) "class" (3)"accounts" and require every transaction to use line items, even if they only have one? This doesn't hurt other accounting softwares which don't record like this because they can pull only the "transaction line" while QBO can sync seamlessly with all the relevant lines.

For the record, I would argue that "events" should also use line items to represent the "ticket/item" that was sold as a "contribution".

Does anyone have ideas on this? I do, and I'm willing to put money towards it, but it needs more support from the community first.