xTuple.com xTupleU Blog & News Customer Support

Requirement to show 2 sales taxes separately on invoice

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

Here is the latest message from John Trainor:
Fred:
The following and attached describes a fairly simple means of using xTuple 3.5.x to cause Canadian federal and, as required, provincial sales taxes to be presented properly on an invoice generated using the generic report writer component of the software, subject to one assumption. That assumption is that the first three characters of all federal tax codes (as established using the “Accounting>Tax>Tax Codes” menu selection) are either “GST” or “HST”.

There are two exported .XML invoice report formats attached. These present two variants of the sample invoice format provided by xTuple that will satisfy the requirement to print a typical Canadian invoice with federal and, where required, provincial tax invoice footer amounts broken out. The only difference between the two formats is the data used as the tax ‘label’ (i.e. the descriptive field to the left of the tax dollar amount in the invoice footer). One presents the actual tax code as the label. The other presents the tax code description.

To see the key changes in either one, look at the new queries within the report (“Document>Query Sources”) and examine the “tax_brkdown_fed” and “tax_brkdown_prov” queries. Additionally, examine the footer area of the invoice and specifically the descriptive and dollar fields for tax as shown.

These two formats were just developed and have not yet been deployed in a working client install of the software. With the disclaimer that Touchstone Technologies Inc. provides no warranty of any kind regarding the correct functioning of these prototype report formats, the attached formats are free to all to use in any manner they see fit. The decision to and responsibility for use, modification or deployment of these formats for any purpose rests with the party who takes that action.

By implication you must do your own testing to satisfy yourself as to the correct functioning of these report formats before deploying them. I am certainly willing to entertain a few questions or consider incremental improvements. Unless this strategy and/or the functioning of these reports proves to be deficient in some significant fashion I am disinclined to pursue a more elaborate, function driven strategy such as that put forward in earlier correspondence. Such a strategy, while presumably encompassing a more complete or sophisticated tax presentation methodology, implies ongoing custom support requirements that I would prefer to avoid.

Regards,

John

John Trainor
Touchstone Technologies Inc.
416-606-8366
jtrainor@touchstonetech.ca