Here is a message that I sent out to Canadian partners, and the responses that I received. I decided to move the conversation over here, so that anyone could participate:
Initial message:
I have a new client who needs to print invoices and send them to customers. The two sales taxes are shown summarized on the invoice. It is a requirement in Canada (and I imagine other countries) to show them separately. Federal and provincial taxes are tracked separately. I imagine some changes have to be made to the query in the invoicing program in order to do this, but I don’t have enough knowledge to do that yet. I need help to resolve.
Fred,
I took a quick look at the tables involved and at this point it looks like I will be introducing a database function similar to ‘CheckDetailFormatted’ that AP uses for a multi page check. That function returns a ‘set’ of data - in that case invoice information – per cheque and the query source in the report pulls it in one row at a time. I think that using that approach will be relatively straight forward to return a list of tax codes and the tax amount for a given document. Lots of details to work out yet, but it looks like a good place to start.
Marvin Bos
Fred:
Unless there has been a really dramatic change in the tax calculation mechanism between 3.2.1 and 3.5.1 the suggested solution seems to me to be overkill for most Canadian jurisdictions.
If the corporate entity doing the invoicing is a Canadian operation and solely responsible for collecting and remitting taxes for Canadian jurisdictions … and presuming that the two invoice total section tax breakout lines could be labelled “HST/GST” and “PST”, respectively (or other labels of your choosing), then I do not believe that a function such as that described is a requirement.
If it is your preference/need (or other partners’ preference/need) to label the tax lines specifically as to either HST or GST and, where required, with a label identifying the particular provincial tax that is applicable, then such a function might make sense.
Based on our past experience with entities charging various of HST, GST and provincial tax (including Quebec’s tax on tax) we would most likely be pursuing the first variant above. Note that this statement of intent does presume that the latest xTuple version has not changed too significantly.
As to a Canadian payroll system we have little to no interest. In our experience the furthest we have ever gone (with some other legacy ERP applications … not xTuple as yet) is identifying an application or service that would export useful accounting distribution data to reflect payroll outcomes in the G/L and providing an import utility for this data. Generally speaking we have found that getting involved in payroll system maintenance and support is a lot of work for very little return.
John Trainor
I have not been successful so far. I created an issue in mantis, with the suggestion of tech support. I guess it needs to be sponsored. I would do it myself and contribute it back, but don’t know how yet. I took the intro course, but we didn’t cover this. (Sorry but I am an accountant, and sql challenged) I sent a message to all the Canadian partners, but didn’t receive any responses. Maybe it ended up in the spam filter or something. Seems to me that someone in Canada must be printing invoices. Maybe they are manually calculating the taxes and entering into the notes. In the mean time, I hard coded it into the group extended sql query for Quebec, so I don’t have to manually enter it in the notes for every invoice.
I added the 2 statements:
formatMoney(tax * 5 / 12.875) AS f_gst,
formatMoney(tax * 7.875 / 12.875) AS f_qst,
Of course this will only works for Quebec, so I assigned the invoice to customer type “Quebec”. (You could create one for each province that you deal with). If you are using the customer type for something else its a problem. I agree its a bandaid, but it saves me from having to sit there with a calculator when I print invoices. (for my own company)
Fred Blauer