xTuple.com xTupleU Blog & News Customer Support

GST & PST Taxes

I am new to Postbooks and OpenRPT. I am in Toronto, Ontario where we have 2 taxes. How do I add and print 2 taxes in the invoice report?

I think the above mentioned implementation is for previous versions of tax structures, since 3.3.0 no longer supports rate(abc). Is there any GST & PST implementation done for 3.3.0 tax scheme?

Hi, toolsnottoys:

If you haven’t seen our wiki page describing Tax support in xTuple ERP, you might find it helpful:

http://wiki.xtuple.org/TaxBasics

Regards,
Pierce

Thanks Pierce, but I have read this wiki page. I am fine with setting up the second tax but how do I print it on the invoice?

toolsnottoys,

You’ll have to change the query (probably the GroupExtended query) to draw the tax breakdown data from various fields in the invchead record rather than the single invchead_tax summary field. The fields of interest are invchead_tax_rate[abc], invchead_freighttax_rate[abc], and invchead_adjtax_rate[abc].

Gil

This has been done. The modified groupextended query for a standard invoice. See f_tax_a and f_tax_b below GST and PST respectively.

SELECT formatMoney( noNeg(invchead_freight + invchead_misc_amount + invchead_tax +
( SELECT COALESCE(SUM(round(((invcitem_billed * invcitem_qty_invuomratio) * (invcitem_price / COALESCE(invcitem_price_invuomratio,1))),2)), 0)
FROM invcitem LEFT OUTER JOIN item ON (invcitem_item_id=item_id)
WHERE (invcitem_invchead_id=) )
- total_allocated) ) AS f_totaldue,
formatMoney(invchead_misc_amount) AS f_misc,
formatMoney(invchead_tax) AS f_tax,
formatMoney(invchead_tax_ratea) AS f_gst,
formatMoney(invchead_tax_rateb) AS f_pst,

   formatMoney(invchead_freight) AS f_freight,
   formatMoney(invchead_payment + (SELECT SUM(arapply_applied) AS applied
                                   FROM arapply
                                   LEFT OUTER JOIN invchead ON (arapply_target_docnumber = invchead_invcnumber)
                                   WHERE (invchead_id = )
                                   AND (arapply_source_doctype = 'K'))) AS f_payment,
   formatMoney(total_allocated) AS f_allocated,
   invchead_notes,
   invchead_misc_descrip

FROM invchead, arapply,
(SELECT COALESCE(SUM(CASE WHEN((aropen_amount - aropen_paid) >=
currToCurr(aropenco_curr_id, aropen_curr_id,
aropenco_amount, aropen_docdate))
THEN currToCurr(aropenco_curr_id, invchead_curr_id,
aropenco_amount, aropen_docdate)
ELSE currToCurr(aropen_curr_id, invchead_curr_id,
aropen_amount - aropen_paid, aropen_docdate)
END),0) AS total_allocated
FROM aropenco, aropen, cohead, invchead
WHERE ( (aropenco_aropen_id=aropen_id)
AND (aropenco_cohead_id=cohead_id)
AND ((aropen_amount - aropen_paid) > 0)
AND (cohead_number=invchead_ordernumber)
AND (NOT invchead_posted)
AND (invchead_id=) )
UNION
SELECT COALESCE(SUM(currToCurr(arapply_curr_id, t.aropen_curr_id,
arapply_applied, t.aropen_docdate)),0) AS total_allocated
FROM arapply, aropen s, aropen t, invchead
WHERE ( (s.aropen_id=arapply_source_aropen_id)
AND (arapply_target_aropen_id=t.aropen_id)
AND (arapply_target_doctype=‘I’)
AND (arapply_target_docnumber=invchead_invcnumber)
AND (arapply_source_aropen_id=s.aropen_id)
AND (invchead_posted)
AND (invchead_id=) )
– there will be two rows, one each for posted and not. get the greater of the two
– as at least one is guaranteed to be 0
ORDER BY total_allocated DESC ) AS totalalloc
WHERE (invchead_id=)
;

Thanks yellowdog, your post is very helpful. I am new to Postbooks, where is this code or where do I add it?

You need to open the report definition under System>Design>Reports. There you look for the query sources under Document>Query Sources. You will see the GroupExtended query source - that is the one that needs to changed. Then you need to add the new fields to the report replacing the single tax line.

Regards,
Norm

Thanks Norm, my invoice is now printing GST & PST.

Very much appreciate it?
Paul

Hi Everyone, (Yellow Dog)

I am pretty new to this as well and I had a question. I have looked at your code and understand it for the invoice, what I am wondering is if there is a way to do the same thing but use the COHEAD and COITEM tables. I am trying to generate a shipping notice that has taxes for just the shipped items. IE if there was 10 ordered but only 3 shipped I want to show 10 ordered 3 shipped, the amount of the 3 shipped, the subtotal for the 3 shipped and then generate the tax portion for the 3 shipped.

Any help would be greatly appreciated.

Ian

Hi,

How to display tax break down (GST, PST) on Purchase Order, Sales Quote, Sales Order, Credit Memo, Invoice? Do I need to add new tax specific metaSQL on each form? I can see taxBreakdown metaSQL in the list found in System/Design/MetaSQL Statements. Would adding the same code give the tax breakdown on each form?

thank you.

Hi giacomo -

Refer to yellowdog’s response to this initial post dated 11.13.08, I believe he answers your question:

"You need to open the report definition under System>Design>Reports. There you look for the query sources under Document>Query Sources. You will see the GroupExtended query source - that is the one that needs to changed. Then you need to add the new fields to the report replacing the single tax line.

Regards,
Norm"

I would encourage you to consider attending xTuple training. There’s an Introduction to xTuple Applications (http://www.xtuple.com/training101) class scheduled for the week of August 16 followed by a Power User Training class (http://www.xtuple.com/training202) the week of August 24. The Intro class in particular focuses on set-up and provides a lot of insight into getting started with xTuple.

Regards,
Danielle

Hi Danielle,

I tried that but without success. First I just inserted the following 3 lines in the GroupExtended.

formatMoney(invchead_tax) AS f_tax,
formatMoney(invchead_tax_ratea) AS f_gst,
formatMoney(invchead_tax_rateb) AS f_pst,

everything after subtotal was empty.

Then I just copied all yellowdog’s query and replaced the original GroupExtended with it. But still empty after subtotal.

The versions I am using may not be good? I tried 3.5 and 3.5.1 beta.

thank you.

My understanding is that the suggested query mod will not work on the new version of the tax implementation. Any hint as to how we would do this now, since it is required in Canada (and probably other VAT jurisdictions)? Thanks.

(Sorry, posted here by mistake, started a new thread)

Hi.
Developing a custom report showing all open orders and the details of each.
I have my query successfully showing everything I need - group header for header items, detail band for items. I would like to use the group footer to show the items subtotal, then the freight and misc from the cohead record, and add all together to get an order total.

I would use the standard sales order template, except that the footer query uses the parameter of the single sales order to print. Since I am printing details on all open orders, I can’t use that footer query.
Ideally, I would have the cohead_freight and cohead_misc fields on the detail query, then simply add them to the subtotal amount in the footer. There’s the problem, it doesn’t look like variables are supported in OpenRPT.

I can post the report template if necessary,

Thanks.

I am on 3.5 as well and trying to get the tax details on the invoice and other reports.
Setting up the tax to be calculated correctly is done and the total tax on the invoice is correct.
The question for me as well is the breakdown in GST/PST and HST.

Is there anyone who did succeed in the breakdown for Canada in GST/PST and HST.

Thanks,
Patrick

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)

Conversation continued here: