Getting access to line item tax type
Greetings all...
I'm starting to work through the templates for all the reports required for a Canadian deployment of OpenVPMS. I've started with the basic customer invoice. In the iReports template for invoiceitems.jrxml, I was able to extract the line-item tax amount and create/display a price-ex along with the associated item-tax. However, as BC has a dual-tax system, irregularly applied to products and services, it is important that I be able to extract and track from the line item-tax, the PST (7%) and GST (5%) components.
So my question is... while creating the invoice, is there a way (or at least a strategy) for me to gain access to the tax types assigned to each of the line items? I can have one of four tax types assigned : None, PST, GST, or Both. Having access to that tag (not only in the invoice but in many other reports) will more easily allow me to split out from the line item-tax the PST and GST components. Alternatively, I guess I am left with doing a bunch of bigDecimal compares to see whether the tax compnent is made up of only GST, only PST, or both.
Thanks,
Sam Longiaru
Re: Getting access to line item tax type
Product tax rates come from one of three sources:
Any tax exemptions the customer may have are then removed, and the line item tax amount is calculated using the sum of the rates. The tax amount is the only tax information stored on the invoice item.
In the short term, I think your only option is to calculate the PST and GST amounts and do comparisons. In the longer term, what reporting functions would make it easier?
Re: Getting access to line item tax type
This will definitely be the most challenging hurdle I think to using OpenVPMS for us. I am quite nervous about it, although I still have a few months before opening to work out some of the difficulties. I really do need to track (or rather extract) the PST and GST amounts separately. Every month, we will need to produce a report showing the amount of PST paid on resaleable items and the amount of PST collected through sales. The difference is remitted to the provincial government. The GST is treated somewhat similarly. Also, all of the company's year-end reports would include the non-refundable PST paid on NON-resaleable goods as that is considered part of the cost of acquisition and would be expensed out.
In the ideal world, having access to the bigDecimal taxAmount for each type of tax assigned to the item would be most excellent. Ideally I would see fields taxAmount.GST and taxAmount.PST when I look into the iReports Report Inspector. The fact that only a single-tax is stored with the item (while not ideal) is not a deal-breaker as everything ultimately comes down to reporting.
If I'm correct in understanding how things intertwine here, as complex as they are, I think I should be able to get what I need by tweaking the SQL queries for those reports that have those queries exposed. As for those that do not have visible queries (such as the basic invoice), getting access to even a target.taxrate field (either as a string or int for easier comparison: 1=PST 2=GST 3=Both), would be great. Then I could apportion the tax between PST and GST variables and deal with it from there.
In the mean time, I do have the line-item tax values, and so I guess I'll stick with my original plan of doing some kind of compare to determine whether it is a 5%, 7% or 12% tax that is being incorporated into the "price". Hopefully, it can be a write-once, use-many kind of thing.
Thanks,
Sam
Re: Getting access to line item tax type
If you are splitting tax amounts into PST and GST, is there a calculation order?
Re: Getting access to line item tax type
No, they are both based on the price-ex.
Given that GST is 5%, and PST 7%, I've taken the approach of testing whether for each line-item, the TaxAmount is equal to, less than, or more than the PST amount with the following:
$V{TaxAmount}.compareTo(($V{Price}.multiply($V{rate_PST})).setScale(2,BigDecimal.ROUND_HALF_UP))
If the expression returns -1, then the tax must be GST-only, if 0, then PST-only and if the expression returns 1, then it is PST+GST. With that, I should be able to apportion and display the tax as required. I'll be struggling with iReports for a while on this, but I'm sure it will come.
I took this approach because I thought that it would be the most flexible, particularly if the government(s) change the rates as they are known to do. For example if the GST rate changes to 6%, then this filter will still work un-changed. Only if the PST rate changes will I need to adjust the expression in the reports. If for example, it rises to 8%, then for historical reports, I would need to do an additional OR test... if the rate is either 7% or 8%, then it is PST.
My big concern now is looking down the road. I see a ticking time bomb. When the GST was introduced, for example, it was 7% and the PST and GST were the same. If they are ever the same again, then trying to decode the tax based on value alone falls apart. And with no other key information available other than maybe startTime(?), it becomes impossible to decode the tax value.
While I understand that full internationalization of the program is a major undertaking, I'm wondering if there might be a more limited project that could be scoped out to get to the international user the key information required to generate these reports. And while I'm stargazing, with that capability (and a small bundle of US/Canadian-styled report templates), wow!
Thanks,
Sam
Re: Getting access to line item tax type
How do you split tax amounts that comprise both GST and PST? Do you extract the PST first and the remainder is GST, or vice-versa? You will get slight differences through rounding error.
The calculation model is currently: sum rates and then derive tax total.
In your case, it sounds as though it needs to be: calculate tax per rate, and total. In which case, it is another requirement for the Invoice Level Taxation project, or a spin-off.
The main issue I think is being able to be able to determine the tax type and rate used at the time the invoice item was created.
There's a few approaches to this:
1. change the data model to include a new table that breaks down the tax amounts per financial_acts row. It would have the following columns
There would need to be extension functions for invoice reports etc to help determine how much GST or PST an invoice item had.
2. create entity.taxTypes that are linked to acts via participations
The entity.taxType would be an instance of a lookup.taxType at a particular time.
This is to get round the fact that tax types are currently modelled as lookup.taxType lookups, which aren't dated.
It would contain:
For each tax used in a charge item, there would be a corresponding entity.taxType linked using a participation. There would need to be extension functions to:
E.g:
Of these, 1. is the more complete solution, and is much better suited to reporting. 2. is at best a workaround.
Re: Getting access to line item tax type
Hi Tim,
Thanks for thinking again about this issue.
As I am just now at the stage of trying to accumulate the GST and PST components in the invoice, your question as to how to split the GST and PST are quite timely... I'll have to think about the best approach as I don't want the invoice to be internally inconsistent, or short-change the tax man.:( Maybe I should just round one value half-to-even, then subtract that from the TaxAmount to derive the other. As long as it is fair, consistent and justifiable, then that will have to suffice.
I tweaked my compare to:
$V{TaxAmount}.compareTo(($V{PriceEx}.multiply($V{rate_PST})).setScale(2,BigDecimal.ROUND_HALF_UP))
where I changed Price to PriceEx as (not only was that a mistake before) it is more consistent with how our taxes are applied. I have it now reliably tagging items as either GST, PST or BOTH, and now just need to do the apportioning and reporting.
I've studied your approaches above and agree that (1) is better as long as it works for multiple taxes on an item. It is more comforting to actually have the tax amounts stored somewhere because once that's done, I really don't care what the tax regime was at the time or what rate was used... I just read the amount for each tax without any recalculation.
Thanks again,
Sam
Re: Getting access to line item tax type
Is this the Canadian invoice calculation model?
From a reporting perspective, it would probably be worthwhile storing the cumulative tax totals per rate at the invoice level as well.
Re: Getting access to line item tax type
Everything is correct except for the third point... the taxes do not need to be shown on an invoice for each line item, only a summary needs to be shown at the bottom. And as for the fourth point, we don't really have line-item totals displayed. It's not always the most informative way, but it is the way it's done. So here is a real-life example.... my most recent cable bill:
Most invoices are the same... you don't really know what you're paying tax on. For example, we pay GST on most everything and so 5% GST is applied to the 143.95 sub-total above and that's correct.. $7.20. But the rules for PST are a mess and so the 7% PST is charged on some component of that 143.95 and frankly, I really couldn't tell you what. The cable company keeps track of what is taxable and what is not.
If this was a bill that had come into the clinic, however, I would expense out 143.95 + 5.89 as the PST is not claimable by the company and it becomes a cost of doing business. So I would need to pull that invoice-level PST value out in a report. The GST amount, however, would be subtracted from the GST that we take in through sales and the difference periodically remitted to the govenment. So I would also need to get to that value... but just at the invoice level. So you're quite correct that storing the cumulative tax totals at the invoice level would be helpful.
As I'm sure it is for the cable company, keeping track of what is taxable and what is not is a nightmare for all businesses in Canada. That's why line-item control of the chargeable taxes is so important. For example, I was just reading through the PST rules for BC veterinarians last night. Apparently a bone plate or pin is PST taxable since it becomes a part of the animal, but if it is included as part of a treatment package combined with other non-taxable items, then the PST does not apply. One can't even really do things like lump drugs under one umbrella tax-scheme. For a good chuckle, a direct quote from the PST guide:
"The following are exempt from PST:
drugs and other substances that alleviate pain in animals (including applied externally), such as pain relief medications, including anaesthetics, analgesics and pain tablets, as well as burn remedies, liniments, ointments and other medications that clearly have a primary purpose of relieving pain
Please note: Items whose primary purpose is something other than pain relief are not considered pain relievers and are taxable, unless sold on a prescription. For example, the primary purpose of an antacid is to neutralize stomach acids, while pain relief is a secondary purpose. "
Where's my taxable Zantax... I feel a pain coming on...
Sam
Re: Getting access to line item tax type
Do you have a reference to an official site detailing Canadian invoicing requirements? I've found a few unofficial ones that display invoices breaking down tax at the item level (e.g. http://sbinfocanada.about.com/od/management/a/invoice1.htm ), and those that display it at the invoice level.
There is this: http://www.cra-arc.gc.ca/tx/bsnss/tpcs/gst-tps/bspsbch/itc-cti/nvc-eng.html
which seems to indicate that for total sales over $30 you have to indicate which items include GST.
As a side note, early payment discounts are handled differently than volume discounts, according to http://www.gst-tax.ca/GST/Accounting_for_GST.htm - they apply to the total including GST. Not sure what is supposed to happen if PST is included. We don't support invoice level discounts at present, so its not an issue, but is applicable to the http://www.openvpms.org/project/add-support-invoice-allowances-and-charges project.
Re: Getting access to line item tax type
The CRA site that you found would be the most authoritative I would think without actually going to the tax code. I've looked at your links there and my read on it is that only when your customer is a GST-registrant, i.e, another business, are you actually required to provide line-item GST information. And that is just to allow them to support their own GST claims. If only one type of invoice is going to be produced, then it would be better to over-report and show the line-item taxes. There is probably a much greater need for that type of detailed invoice for large-animal over small-animal practices, however.
I think my cable bill from above meets all the criteria for an invoice to a non-commercial customer and in a clinic situation, to a non-commercial client:
I tried to get some clarification as to whether the term "supply" meant the sale of each line-item or a sale at invoice level. The govenment site was no help really with this definition:
Supply
A supply means the provision of property or a service in any way, including sale, transfer, barter, exchange, licence, rental, lease, gift, and disposition.
Yes, discounts get handled differently, depending on their application. I believe that the overriding concept, however, is that tax is fixed at the time of, and attached to, the price prevailing at the moment of sale. If you give incentives or penalties after that, that's your own business.
Sam
Re: Getting access to line item tax type
I now have gotten my basic invoice to work well, displaying PriceEx, PST, GST and Totals. I'm still testing, but everything looks good so far, yielding line item equivalencies to the PriceInc and TaxAmounts being stored in the DB. I believe that even the discount-at-sale is working as it should with no tweaking.
My goal here is to put a package of templates together that will allow Canadian-style business forms and reports. I think my approach is flexible enough to be used in all PST/GST provinces (as long as the PST and GST rates are not the same!) as well as in those provinces that have the Harmonized Sales TAX (HST). I'll post them to the community's shared resources when I get them done.
Thanks for all the discussion on this. It was really quite helpful.
Sam
Re: Getting access to line item tax type
Glad you've been able to get it working.
I've started documenting adding support for this at the database layer here: http://www.openvpms.org/project/store-multiple-tax-amounts