Re:Tax (GST) payments or invoice basis (Alastair Robertson) 04/22/2013

Firstly, thank you Alastair for the module. This is not a new post but for some reason I was unable to reply to the post above. I may have downloaded the module from the wrong place, but on a payments-basis GST return, the input tax on a paid supplier invoice was added to the output tax instead of being deducted from it.

For example, output GST of 4,434.79 and input GST of 375.52 was reported as a net payable of 4,810.31. The correct net payable is 4,059.27.

FrontAccounting stores a supplier payment with a negative ov_amount. The input-tax part of the report joined supp_trans on the payment (trans_type_from) and divided by that payment total. Dividing by a negative amount flipped the sign of the input GST, so collectible + payable added the input tax instead of subtracting it.

The fix is to divide by the supplier invoice total (trans_type_to). The invoice amount is positive, the existing -1 then makes the input GST a deduction, and the net payable is correct.

File: includes/tax_cash_db.inc

In tax_cash_sql(), change the supplier-allocation block from:

ttd.net_amount*sa.amt/round(ov_amount+ov_gst+ov_discount,6)*ex_rate net_input,
-1*ttd.amount*sa.amt/round(ov_amount+ov_gst+ov_discount,6)*ex_rate collectible,
...
INNER JOIN ".TB_PREF."supp_trans st
    ON st.type = sa.trans_type_from
    AND st.trans_no = sa.trans_no_from

to:

ttd.net_amount*sa.amt/round(inv.ov_amount+inv.ov_gst+inv.ov_discount,6)*ex_rate net_input,
-1*ttd.amount*sa.amt/round(inv.ov_amount+inv.ov_gst+inv.ov_discount,6)*ex_rate collectible,
...
INNER JOIN ".TB_PREF."supp_trans inv
    ON inv.type = sa.trans_type_to
    AND inv.trans_no = sa.trans_no_to

After the change, the input line shows as a negative amount and the total payable is the output GST less the input GST. The same figure is used by the Tax Details (Cash Basis) report.