CiviCRM Community Forums (archive)

See the wiki page at: http://wiki.civicrm.org/confluence/pages/viewpage.action?pageId=83493410 for the specification so far.

Previous work on Drupal specific extensions was discussed here: https://groups.drupal.org/node/182444

Some previous work on specifying tax features that core could provide was done here: http://wiki.civicrm.org/confluence/display/CRMDOC43/Taxes+and+Fees+for+CiviCRM

I think this thread will be a place to discuss what needs to go in core and what each extension should provide.

It might be a good idea to start separate threads for the specifics of each extension.

Copying just some discussion from our email thread:

One part of the proposal now on the wiki reads:

Allow multiple sales tax accounts to be linked to a single financial type
This will allow multiple tax rates to be applied to a single entity for example a state tax and a city tax.

Parvez  also wrote:

"My feeling was that the tax_amount held against the line item would be the total. Unless hooked to be different, this should equate to the summation of the one or many tax account percentages. I know its not ideal but I think to get things going is probably the easiest way? I guess the only other way we could do this is to separate out the tax's into a line_item_tax table or something to indicate the amount of tax and break down."

I am interpreting this to mean that the current proposal is to link all possibly applicable taxes to the Financial Type - hence the need for a one to many relationship - or a lot of relationships. Then the hook can remove some if necessary, finally sum the remaining ones to get the total tax.

Joe had proposed keeping the one-one relationship or creating a list of relationships like "Sales Tax 1 Account is", "Sales Tax 2 Account is" etc.

So I proposed another option:

What if there was simply a taxable checkbox on the financial type and it is up to the hook to determine the accounts that are used rather than relying on the civicrm_entity_financial_account table at all.

Issue: Would it work with existing validation code? Batch exports?

In our case where the taxes apply only to the province then it doesn't matter if we make these links or not - the hook will determine the tax value anyway and can just scan all accounts marked "is_tax" for the ones to apply.

Use cases where a single taxable checkbox might not give the hook enough information, CiviCRM as a general shopping cart - not a common case.

    Selling books in Ontario Pre-HST, books were PST exempt
        In this case the client could make a Financial Type called "Product Sales (PST exempt)" and link it to the product on the contribution page
        In this case maybe we Canadians are slightly better off if we can make links between this Financial Type and just the GST account suggesting to the hook to not even try to apply the PST
        Still if the extension developer of this "CiviShopping" extension wants - they could use hook_civicrm_buildForm to add a multiselect to the Account page then choose there on which Premiums to apply this tax account rate. No links needed to any Financial Types.
    Selling food at events in Ontario, if food is under 4$ it is tax exempt
        In this case it is still up to the extension to determine at sale time which taxes to apply depending on the total sale and linking accounts in the back end didn't really help

I am even wondering if this is a more general case - right now the one to one link between Financial Types and Accounts of a certain type lets the core code make the contribution split automatically into the right accounts. For taxes we want to dynamically determine the split into various accounts. There may be a case in the future that some other Financial Types cannot be split automatically by core into various account types and we need a generic hook hook_civicrm_splitContribution or something? But leaving that for now...

The main question is do we gain anything by making the entries in the civicrm_entity_financial_account table? If we don't do we lose compatibility with the current core code?

Parvez you mentioned that this would cause a problem for adjustments? Can you provide an example of how this is automatically handled now - because this was my question - I am pretty new to CiviAccounts and I'm just hoping to avoid making the dozens of links to possibly used accounts but not if it causes extra work in the current code.

Another thought on adjustment:
If the amount of the event fee etc. changes then the tax still has to be recalculated and the tax extension still has to figure out which taxes/how much to apply - because for example in the US with 50 state taxes - a reason for adjustment might be that the user picked the wrong state from the dropdown.

I am just reading through the emails leading up to this post, but thought I would jot down my initial thoughts before I got caught up in them.

People may recall that the precursor to a financial_type, a contribution_type, used to have a single field that you could put an accounting code into.  The intention for changing this to the new financial_type was that it would broaden what might be the subject of a "financial transaction" (ie not just contributions) and also enable those financial_types to be associated with a number of different accounts.  For example, not just an Income Account but also a "Payment Processor Asset Account" and/or "Accounts Receivable Asset Account", depending whether the person paid online or will "pay later".  This helps make it easier to integrate with accounting systems because there will be a "double entry" for the transaction, both the Income and the Asset that is created from the transaction.  Both these items can then be imported into Quickbooks for example.

With sales taxes, you generally wouldn't want the tax amount to be treated as Income, so you don't want the tax amount going to the Income Account but rather a "Sales Tax Liability" Account.

If on the financial_type you can set the "tax_rule", when there is a transaction on an item with that financial_type the total a person is asked to pay would be increased by the % or $ amount calculated by the "Tax Rule".  In a simple case, the "Tax Rule" may just be a label for displaying in the screen/on documents, an alias for showing in drop down boxes (so admins can distinguish between different "Sales Tax" rules) and a rate (% or $).

I see no reason why you couldn't have multiple lines of tax on a financial_type, provided there is a clear way to determine whether a % amount is calculated on the base price of the item or on the price after a previous tax line is added.  Whether this needs to be considered now depends on the use cases likely to be encountered.

I just started reading through Joe's email that kindly brought me in on this discussion, in order to look at the use cases, and realised that it is raising another use case, where the location of the purchaser is the determining factor.  Rather than try and cover the other use cases that may be discussed in the email corro in this post, I will end this one here with just an explanation of how I think the above method could apply to the use case Joe's referring to and then post separately if I can contribute on other use cases.

With the above method, a tax rule could have multiple lines in it, for example:

label="GST", alias="Australian GST", rule="IF purchaser_country='Australia'", rate=10%, tax_liability_account="FK to liability account for 'Australian GST Payable';

With this scenario, purchasing a $10 ticket with financial_type = "Event Fees", by a person from Australia would create the following financial transactions:

Event Income: $10 (CR)
Australian GST Payable: $1 (CR)
Asset Account: $11 (DR)

In future, whether this transaction is recorded when the item is purchased, or paid for, would depend on whether a "cash accounting" or "accruals accounting" flag is ticked.  I believe that currently CiviAccounts is based only on accruals, which may be an issue if a lot of organisations are paying their sales/value added taxes on a cash basis and therefore don't want the liability for the tax created until payment is received for "Pay Later" transactions - but that's an issue for another thread.

If there were national, state, and city taxes, then you could have three rules for your "Event Fees" financial type that you could write loosely as:

label="GST", alias="Australian GST", rule="IF purchaser_country='Australia'", rate=10%, tax_liability_account="FK to liability account for 'Australian GST Payable'"

label="Fire Levy", alias="NSW Fire Levy", rule="IF event_host_country='Australia' AND event_host_state='New South Wales'", rate=2.5%, tax_liability_account="FK to liability account for 'NSW Fire Levy Payable'"

label="Hotel Tax", alias="Sydney Hotel Tax", rule="IF event_location_country='Australia' AND event_location_state='New South Wales' AND event_location_city='Sydney'", rate=$1, per='participant', tax_liability_account="FK to liability account for 'Sydney Hotel Tax Payable'

So the purchase of 2 tickets for $20 by a person located in Australia, from an event host located in NSW, for an event in Sydney, would look like:

Event Income: $20 (CR)
Australian GST Payable: $2 (CR)
NSW Fire Levy Payable: $0.50 (CR)
Sydney Hotel Tax Payable: $2 (CR)
Asset Account: $24.50 (DR)

The tax rules therefore hook off the Financial Type in a way that the tax rule framework can be built into CiviAccounts, and the rules displayed in the Financial Type UI, while the nitty gritty of those rules are setup elsewhere (whether in an extension/XML and/or via a GUI).

I'll try to get through the material that I've missed out on, but hopefully the above clarifies how I'd envisaged a method of "hooking on tax rules" within the native CiviAccounts module UI and financial transaction architecture, in a way that tax rules would be visible enough for the average user to use them and use a common tax rule processing method for ease of extension development.

<philosophical-rant>Ultimately we want to avoid people writing unmaintainable custom code that goes off and writes to SQL tables directly based on a hard coded "financial_type" id etc, that the user won't have visibility of, or on the converse having to develop/MAINTAIN a separate UI for the basic elements of a tax rule, in every tax rule extension/module out there.  Not that I'm suggesting anyone is planning to do that, but I've seen it happen too many times with other use cases where well meaning developers are just trying to get things done for their poor client's immediate needs, so they'll write some code (perhaps not even in an extension/module of some form - because they have no budget and just need to get it working for the event that launches next week and are new to Civi/Drupal), but then everything blows up due to the rapid CiviCRM development cycle and the next developer coming along and trying to do an upgrade on an undocumented system, or do another hook in a purchase workflow.  Clients and developers all get annoyed, budgets get blown and all of a sudden clients look to "cloud based solutions" because they are easier - not realising that part of the reason is that they don't let people do
custom stuff within the platform itself - only via the APIs.  We are dealing with financial transactions and fundraising workflows here, so it's vital to the health of client organisations that CiviAccounts provide and developers use native APIs to do this stuff.</philosophical-rant>

So let's make sure we focus on getting those "tax rule APIs" right in CiviAccounts so that people in each country can develop and then contribute some local tax rules that can be downloaded and installed "out of the box", and be maintainable and well documented as a basis for people in other countries to create more.

Andrew,

Thank you for that amazing post and for providing some much appreciated CiviAcounts background. I agree that what you described is what we want in a perfect solution. At the code sprint I was in a discussion with Dave Greenberg and Parvez Saleh and we did come to the point of realizing that in order to accommodate all the myriad tax rules in every country in some way in the core we needed a full rules engine. We didn't spec this out as well as you have here though.

In that discussion we thought we might want to take an alternate method that could be implemented in half a year to year and without requiring any major sponsorships. The lightweight alternative is to use the already existing "is_tax" and "tax_rate" fields that are already in place on Financial Accounts as a storage place for the rates and as a place to hold the tax liability for export. Then we thought that implementing the rules on a per country basis can be handled by an extension for each country. These extensions can just hard code the rules for that country and update them as the regulations change. In this plan core will provide some lightweight services and some places for the extensions to hook into.

Then we thought that even though we postponed the idea of building a place for the full rules engine into the core we thought we could get some nice benefits out of agreeing that each country's tax extension will store the data in some common way. For example, if an organization spans several countries then they can import CiviCRM data from local chapters in different countries and do a report including taxes across all that data. It will also generally put taxes on the core radar, plus it provides a starting point for the extension developers to find the parts that they are all re-inventing and move them to core feature requests.

That is kind of the scope that I think most of us have assumed in the discussion so far.  In my case, I embraced this approach because it seemed like an incremental improvement that could get us a tax solution for at least a couple of countries within the year. As we develop these first extensions we can publish guidelines for other extensions to follow for storage and use of core services. We assume the other countries will then get solutions via the extension community.

If we can integrate some sort of rules engine, perhaps through Apache Solr/Lucene, or a PHP rules engine and some kind of GUI for it into CiviCRM I am all for it but not sure where we will get the sponsors. Between JMA and Freeform Solutions I think we have sponsorship for the lightweight scope of approach here in Canada (is that right Joe?) and Parvez has a plan (and I am assuming customer sponsorship) to deliver something this year in the UK.

Your other comment about tax rates not equaling accounts has me uneasy that we are going down the wrong path. I can imagine one use case that might not fit neatly into the "tax rate = account" framework as follows: In a particular country the Federal government collects all taxes including provincial/city then pays out a portion to the provinces/cities in some way. Therefore the organization has several rates to charge but only one real liability account to pay - the Federal government tax account. Still it seems OK to me because in CiviAccounts right now we can create two accounts with different tax rates but with the same accounting code so that the money flows into the same place once it gets to the accounting package.

Do you see other major drawbacks/invalid use cases based on starting with this limited scope approach and the existing tax fields that we were thinking of using?

(Edited to change rules Engine suggestion - I was just joking about the crazy huge ones anyway)

I think it is a great idea if we go this way - it is ultimately the best. A rules engine in core could also be used to build a workflow engine for other things like reminder messages, contribution followups, and perhaps be re-used in CiviCase.

I also liked your UI suggestion - just show "(inclusive)" and provide a pop-up with the taxes breakdown.

Maybe this is realistic library to consider as the rules engine base? It uses the MIT License: http://jwage.com/post/31292541379/ruler-a-simple-stateless-production-rules-engine-for

I am stuck because I have a customer who needs a tax solution in the next month or two so I am going to have to proceed with some version of the basic extension alternative. I was hoping to keep it compatible with the future core direction because I don't want the customer stuck with something one off.

Hi Guys,

So moving forward, what's the plan?

I understand Lola has a fairly urgent need to a tax extension for an upcoming project. I have a couple of projects that could certainly use it so I too am motivated to donate more time to the effort. I'm just not quite certain how we go about implementing it within the framework we have discussed up to now as I believe there are some pieces to the puzzle missing.

Any thoughts?

Andrew

Yes, without all of the bits it will be tricky to figure out how to make this work and provide an upgrade path for when all of the bits are in place.

With regard to tracking tax information per transaction and reporting, when we modified (hacked) Civi core to add a tax feature to a CiviCRM 3.x installation we added a table to store tax related information (generalized column names):

• invoice_id
• tax_percent
• pre_tax
• tax_charged
• post_tax

When we pull up a report we query the custom tax table where invoice_id = the actual invoice id and then added a few extra columns to the report to show per-tax, tax amount and then the total. This was used for HST when we had it and now covers GST so it only deals with a single tax in the system we have running live currently. Moving forward we need to account for multiple taxes. 

Prior to the addition of CiviAccounts, I was intending to follow this route for the tax extension I was working on and use a couple of other tables to manage the tax rules as well. The Tax rules were going to be handled by two tables; one to define the tax name, type, rate etc... and the other to relate the tax types to the transaction types.

I'm not sure how much of this will be helpful moving forward today but this is how I was approaching it.

Andrew

Right, we declare and manage the the tax in the civicrm_financial_account table and in theory, when we have taxes working a record of taxes resulting from taxable transactions will be stored in the civicrm_financial_item table. Is that correct?

Regarding the question of the proposed changes to the core contribution and line item tables:

One thing we discussed in the earlier emails was an item from the original proposal: Add a tax_amount column to the civicrm_contribution table to store a taxes roll up for easy reporting. The wiki proposal suggests we store a tax_amount column in civicrm_line_item as well. Some had disagreed with adding that much redundancy. I was wondering if anybody has any more input that might lead to a consensus on this?

As I understand it we are looking to add the tax paid information to contribution reports which are the domain of the contribution table. I expect that if we add columns that account for taxes this would simplify the reporting part greatly. I'm not sure what the value is of also recording the information within the line item table.

* I suppose if there is a need to pull up detailed line item reports then it would be necessary to have the data in the line item table as well.

I'm still not certain of how we are going to handle reporting when we have multiple taxes for various financial types.

EDIT: See next post (page 2) for more thoughts about this...