FrontAccounting forum — Banking and GL - Tax Inquiry summation problem

Re: Banking and GL - Tax Inquiry summation problem

2017-10-31T14:43:58Z

Use the GL Inquiry if you want Bank Payment and Deposit entries.
Use Tax Inquiry only for the Taxes alone.

Re: Banking and GL - Tax Inquiry summation problem

2017-10-31T14:10:55Z

The Tax Inquiry and General Ledger Inquiry produce inconsistent results for taxes. General Ledger Inquiry results are correct while Tax Inquiry is omitting Bank Payment and Deposit type of transactions.

Re: Banking and GL - Tax Inquiry summation problem

2017-10-31T09:00:35Z

This is now fixed and Repo is updated.

/Joe

Re: Banking and GL - Tax Inquiry summation problem

2017-10-31T08:02:20Z

The existing SQL to debug is:

SELECT 
    SUM(IF(trans_type=21,-1,1) *
    IF((reg_type=0) || ((trans_type IN (20,21) OR (trans_type=0 AND reg_type=1))
        ), net_amount*ex_rate,0)
    ) net_output

    , SUM(IF(trans_type=21,-1,1) *
    IF((reg_type=0) || ((trans_type IN (20,21) OR (trans_type=0 AND reg_type=1))
        ), amount*ex_rate,0)) payable

    , SUM(IF(trans_type IN (21),-1,1) *
    IF(reg_type=1 AND tax_type_id AND taxrec.rate, net_amount*ex_rate, 0)) net_input


    , SUM(IF(trans_type IN (21),-1,1) *
    IF(reg_type=1 AND tax_type_id AND taxrec.rate, amount*ex_rate, 0)) collectible,
    taxrec.rate,
    ttype.id,
    ttype.name

FROM 0_trans_tax_details taxrec LEFT JOIN 0_tax_types ttype ON taxrec.tax_type_id=ttype.id
WHERE taxrec.trans_type IN (10, 11, 20, 21, 0)
-- AND taxrec.tran_date >= '$fromdate'
-- AND taxrec.tran_date <= '$todate'
GROUP BY ttype.id;

Re: Banking and GL - Tax Inquiry summation problem

2017-10-31T07:41:05Z

I am working on this.

The reason for rewrite is the new option for adding AR/AP transactions direct into Journal Entry.

Joe

Re: Banking and GL - Tax Inquiry summation problem

2017-10-31T06:46:19Z

This issue does not prevail in FA 2.3 and hence the above fix only addresses the output and not the underlying db fetch function in FA 2.4. Attached is the FA 2.3 output without the above fix and it works perfectly.

Therefore the function get_tax_summary() in gl/includes/db/gl_db_trans.inc needs to be investigated and it is different from it's FA 2.3 counterpart.

The new avatar has an extra parameter $also_zero_purchases set to false by default.

Re: Banking and GL - Tax Inquiry summation problem

2017-10-31T05:00:40Z

Good catch @andijani.

The Tax Inquiry page is at gl/inquiry/tax_inquiry.php. and it's line 91:

        $collectible = $tx['collectible'];

should be:

        $collectible = -$tx['collectible'];

If the Outputs/Inputs column too should get the appropriate signs, then make line 104 show the negative value.

~~This issue prevails in FA 2.3.x (line 95) as well and wonder how it went unchecked for so many years.~~

@joe: can commit it.

Banking and GL - Tax Inquiry summation problem

2017-10-31T01:50:52Z

FA 2.4.2 Tax summing is not correct in Tax Inquiry page. Supplier invoice taxes also show up as sales invoice taxes and they are added instead of being subtracted.

Example.

Type Description Amount Outputs/Inputs

GST / TPS 5% Charged on sales (Output Tax): 82.80 1,656.00
GST / TPS 5% Paid on purchases (Input Tax): 82.80 1,656.00
GST / TPS 5% Net payable or collectible: 165.60