Store multiple tax amounts
Donate to this project
Development Project Status: Seeking Funding
NOTES:
- this project requires Tax Exclusive Product Prices to be implemented first, to limit migration issues.
- this project is related to the Invoice Level Taxation project, but can be implemented independently. If implemented after Invoice Level Taxation, there are rounding changes to the TOTAL rule. See below.
When calculating tax amounts, OpenVPMS sums the relevant tax rates before applying it to the line item total. Only the final tax amount is stored. The following information is discarded:
- tax type
- tax rate
- tax amount per tax type
This makes reporting in juridications that have multiple tax rates difficult. E.g. in Canada, depending on the province, a line item may attract GST, PST, GST and PST, or no taxes. The tax rate of any of these may change over time. PST may differ from province to province. In Saskatchewan for example, both the GST and PST are 5%, so the breakdown per tax type cannot be determined.
This project will:
1. Store the breakdown of taxes at the line-item level. E.g.:
- item1 - tax = $36
- GST - 5% - $15
- PST - 7% - $21
- item2 - tax = $12
- GST - 5% - $5
- PST - 7% - $7
2. Store the cumulative taxes at the charge level. E.g.
- invoice - tax = $48
- GST - 5% - $20
- PST - 7% - $28
3. Provide reporting functions to support Jasper Reports
Database Changes
This change will require the addition of a new table, tax_amounts, with the following columns:
| Column | Type | Description |
|---|---|---|
| id | bigint | row identifier |
| financial_act_id | bigint | identifies parent row in the financial_acts table |
| lookup_id | bigint | identifies lookup.taxType row in the lookups table |
| rate | decimal(18,3) | tax rate |
| amount | decimal(18,3) | tax amount |
Calculation Model Changes
Currently tax rates are summed, the tax amount calculated, and the result rounded.
This will need to change so that tax amounts are calculated per rate and then rounded.
This may result in tax amount differences between the old and new calculation models.
Migration
As the information used to generate tax amounts has been discarded, no one-size-fits-all script can be provided to migrate existing financial data.
An example migration script will be provided that implementers can customise to populate the tax amounts.
Sites that don't perform these migrations will be unable to report tax breakdowns on charges finalised prior to the tax changes being implemented.
Invoice Level Taxation
The Invoice Level Taxation project adds support for different tax rules to calculate tax amounts. If implemented, this project has implications for the TOTAL rule used to calculate tax amounts:
- tax amounts at line item level are informative only
- invoice line items are summed per tax type, multiplied, then rounded, to give the invoice tax per tax type
- invoice tax is the sum of tax per tax type
This may result in tax amount differences between the old and new calculations.
Reporting Functions
The following functions will be provided to query the tax amounts for the current line item or charge:
| Function | Description | Example |
|---|---|---|
| tax:exists(code) | Determines if a tax is present | tax:exists('GST') |
| tax:amount(code) | Returns the amount of tax for the given tax type code. Returns 0 if the tax isn't present | tax:amount('PST') |
| tax:rate(code) | Returns the tax rate for the given tax type code. Returns 0 if the tax isn't present | tax:rate('HST') |
| tax:lookup(code) | Returns the lookup.taxType for the given tax type code. Returns null if the tax isn't present | tax:lookup('VAT') |
Archetypes
Unless a compelling reason can be found, the object used to store tax in the tax_amounts table won't be exposed as an archetype.
Comments
Re: Store multiple tax amounts
Hi Tim,
This looks great from our prespective as these changes would give access to all the information we need for now and into the foreseeable future. It all seems very flexible. I'm particulary pleased to see that the individual line-item tax amounts would actually be stored as not only does that preserve all the historical data without assumptions, it completely separates the tax calculation function from the tax reporting function. As it is right now, my reports need to do those calculations... not a desireable situation.
But could you please clarify the dependencies between this project and the two related projects: Tax Exlusive Product Prices and Invoice Level Taxation. I understand that Tax Exclusive Product Prices is a prerequisite for this project but what about Invoice Level Taxation? The reason why I am asking is:
1) Doesn't this project and its prerequisite go a very long way to addressing the concerns of current and potential US users of OpenVPMS and,
2) I do not see here or in its prerequisite, any mention of changes to archetypes. While I still struggle conceptually with the role of archetypes, do we not need some changes in order to allow access to the component tax information in such internally generated reports such as
Thanks again for all your thoughts on this. Fabulous work.
Sam
Re: Store multiple tax amounts
I've added a section on Invoice Level Taxation and Archetypes.
At this stage, I can't see a reason to provide an archetype for the tax amount object. One reason you might want them would be in a Jasper Reports sub-report that listed the tax items. I don't know that this would be all that common a scenario. I think the reporting functions described above are a more useful way to access the information.
Also, can you think of a more concise project name?
Re: Store multiple tax amounts
Tim A - as per your email suggesting that for performance reasons it would be better to store the tax breakdown in the financial act - ie quote
"In hindsight, I'd probably denomalise the data for performance reasons and store the amount, rate and type, for up to 5 rates, in the financial_acts table.
E.g. tax_rate1, tax_amount1, tax_type1, tax_rate2, tax_amount2, tax_type2, etc....
The tax_type columns would contain the tax lookup code. This is not nice from a referential integrity perspective as there is no link to the lookups table, and incorrect from a data perspective as only a small subset of lookups can be be tax rates." end-quote.
Suggestions:
1) don't hold the rate - it is recoverable (if ever needed) from the ex-tax amount and the tax_amount
2) provide a "tax index" in lookup.taxes so which slot a tax goes into can be controlled. This would make it far easier for reporting - tax_amount1 is always GST, tax_amount2 is always PST, etc
3) using a 'tax index' means that you do not need a tax_type - because the tax name and current rate is recoverable from lookup.taxes - just look for the lookup_details record with name='Tax Index' and value = the slot number.
3) looking at https://en.wikipedia.org/wiki/List_of_countries_by_tax_rates 5 slots would appear to cover all countries . Note that although it is convenient to have a 'No Tax' entry in the tax lookup (with a rate of zero), we do not need a "tax index" for this and in this case its index should probably be set to 0.
4) Note that in some countries, there is 1 tax but with different rates for different classes of goods. Assume we have 3 different GST rates, 5%, 10% and 15%, as well as a PST tax. Then the way to handle this is to define 3 different GST taxes - but you could set the 'tax index' to the same number (say 1) for all. This makes reporting very easy - all the GST is in slot 1, all the PST is in slot2. If you really need the breakdown of what you collected at each GST rate, then you set a different tax index for each.
Regards, Tim G
Re: Store multiple tax amounts
1) The storage requirements are small. Also rounding error means that the calculated rate won't necessarily be the same as the actual rate.
2) No - this is brittle. Changes to an index would break historical data.
3) Only those taxes that were used to calculate tax would be included, so unused tax fields would be null.
4). See 2. Denormalising the data makes SQL reporting a PITA, so it may be worth keeping it normalised.