1 (edited by mark0300 10/20/2020 02:55:33 pm)

Topic: REP709.php TAX REPORT BUG

I found a bug in rep709.php tax report.  I got it fixed and would like to share.    (see the ======> below in the sql query codes)

Problem:
When performing a Bank Payment and if the entries contain the same tax code which appear more than one time, and when the tax report rep709.php is run, it will generate multiple lines for the same tax codes and the number of times it appear will depend on the number of appearance of the same tax code.

For example, if my tax code is 8800 (tax name TX) and I have made 2 tax amounts of  is say $7 and $14.

My bank payment entries would be:
-   8800   $7
-    8800   $14
In this example, 8800 appear twice.   Now if I run this report, this tax will appear as follows (each entry appear twice):

Bank Payment BKPMT-00010    03-07-2020 -100.00 7.00 -7.00 TX
Bank Payment BKPMT-00010    03-07-2020 -100.00 7.00 -7.00 TX
Bank Payment BKPMT-00010    03-07-2020 -200.00 7.00 -14.00 TX
Bank Payment BKPMT-00010    03-07-2020 -200.00 7.00 -14.00 TX

If the same bank payment transaction has the 3rd tax entry say $21, total 3 rows now (each entry now appear 3 times):
Bank Payment BKPMT-00010    03-07-2020 -100.00 7.00 -7.00 TX
Bank Payment BKPMT-00010    03-07-2020 -100.00 7.00 -7.00 TX
Bank Payment BKPMT-00010    03-07-2020 -100.00 7.00 -7.00 TX
Bank Payment BKPMT-00010    03-07-2020 -200.00 7.00 -14.00 TX
Bank Payment BKPMT-00010    03-07-2020 -200.00 7.00 -14.00 TX
Bank Payment BKPMT-00010    03-07-2020 -200.00 7.00 -14.00 TX
Bank Payment BKPMT-00010    03-07-2020 -300.00 7.00 -21.00 TX
Bank Payment BKPMT-00010    03-07-2020 -300.00 7.00 -21.00 TX
Bank Payment BKPMT-00010    03-07-2020 -300.00 7.00 -21.00 TX

I looked into the sql query and I found a fix:

    $sql = "SELECT tt.name as taxname, taxrec.*, taxrec.amount*ex_rate AS amount,
                taxrec.net_amount*ex_rate AS net_amount,
                IF(taxrec.trans_type=".ST_BANKPAYMENT." OR taxrec.trans_type=".ST_BANKDEPOSIT.", 
                    IF(gl.person_type_id<>".PT_MISC.", gl.memo_, gl.person_id), 
                    IF(ISNULL(supp.supp_name), debt.name, supp.supp_name)) as name,
                branch.br_name
        FROM ".TB_PREF."trans_tax_details taxrec
        LEFT JOIN ".TB_PREF."tax_types tt
            ON taxrec.tax_type_id=tt.id
        LEFT JOIN ".TB_PREF."gl_trans gl 
            ON taxrec.trans_type=gl.type AND taxrec.trans_no=gl.type_no AND gl.amount<>0 AND

=======>  gl.amount=taxrec.amount AND <====== Add this line to the query and it fix the problem.  

            (tt.purchasing_gl_code=gl.account OR tt.sales_gl_code=gl.account)
        LEFT JOIN ".TB_PREF."supp_trans strans
            ON taxrec.trans_no=strans.trans_no AND taxrec.trans_type=strans.type
        LEFT JOIN ".TB_PREF."suppliers as supp ON strans.supplier_id=supp.supplier_id
        LEFT JOIN ".TB_PREF."debtor_trans dtrans
            ON taxrec.trans_no=dtrans.trans_no AND taxrec.trans_type=dtrans.type
        LEFT JOIN ".TB_PREF."debtors_master as debt ON dtrans.debtor_no=debt.debtor_no
        LEFT JOIN ".TB_PREF."cust_branch as branch ON dtrans.branch_code=branch.branch_code
        WHERE (taxrec.amount <> 0 OR taxrec.net_amount <> 0)
            AND !ISNULL(taxrec.reg_type)
            AND taxrec.tran_date >= '$fromdate'
            AND taxrec.tran_date <= '$todate'
        ORDER BY taxrec.trans_type, taxrec.tran_date, taxrec.trans_no, taxrec.ex_rate";

Any comments are welcome.

/Mark

Re: REP709.php TAX REPORT BUG

In your instance, the amounts were different across vouchers and appear multiple times, but such occurrence is due to the voucher number being same. There should be no linkage with amount as we can have the same amount legitimately in the same day for the same account too. Check on duplication in the reference: BKPMT-00010. Maybe if the taxes are different you might want to have different account numbers for each.

State which version / date of FA you are using. Please note that the file referred to was last changed on 2015-12-12 and no one has complained about it in the last few years.

Re: REP709.php TAX REPORT BUG

There is no duplication of reference BKPMT-00010. I think the issue is in the ON condition set in the LEFT JOIN.    If the same tax code appear in the gl_trans more than once with other condition same, and when LEFT JOIN with trans_tax_details take place, it will produce multiple rows.  The version of this rep709.rep is from 2.4.8.

Re: REP709.php TAX REPORT BUG

@mark0300

I have to agree with you that it does happen. The thing that confuses me is that you are saying it happens on Bank Payments. As far as I understand it Tax on Bank Payments are not supported on Vanilla 2.4.8 and you need a pull request to get it going. Is that what you have?

In my case I can reproduce it easily on supplier invoice and as described in the attached screen shots. Three tax entries are booked nine in the report.

Someone needs to take a look at it.

Post's attachments

rep709.zip 107.4 kb, 3 downloads since 2020-10-21 

You don't have the permssions to download the attachments of this post.

Re: REP709.php TAX REPORT BUG

@Rafat,

If you have another tax item with the same tax account code entered, you will notice that each will appear 4 times!

If you go to report709.php and add that line I mentioned in my earlier post and run the report again, then the problem might go away.  It is caused by the ON condition in the LEFT JOIN.

I am not sure my code will cause other issue.  But for now, it solves my problem.

It will be great if some experts could test it out.

Re: REP709.php TAX REPORT BUG

This has been fixed and committed to stable repo.

A fixed file can be downloaded on Mantis.

Thanks for  reporting this smile

Joe

Re: REP709.php TAX REPORT BUG

Thanks Joe,
Its working fine.

8 (edited by seahawk 03/25/2021 07:16:16 pm)

Re: REP709.php TAX REPORT BUG

It seems that this bug has now reared its head again with the last update for report 709. TAX Report. The new one is not incorporating the VAT on bank costs from the bank accounts payment. Reverted back to the previous version of 2.3.25 report.

Wynand

Re: REP709.php TAX REPORT BUG

@joe: Any clues?

Re: REP709.php TAX REPORT BUG

Oops, this was a side-effect. @mark0300, do you have a solution to this.
I am in a very business periode, so don't have time for fixing.
Maybe any of you Senior Members can help. Please.
I will then commit the fix asap. Thanks in advance.

/Joe

11 (edited by rafat 03/29/2021 10:10:55 am)

Re: REP709.php TAX REPORT BUG

@seahawk

Tax on Bank Payments and the corresponding report on 2.4.x were introduced by Braath as a pull request (https://github.com/FrontAccountingERP/FA/pull/39). I think you have this pull request installed on 2.4.8 if I am not mistaken. Now if you upgraded to to 2.4.9 you need to reinstall the pull request..or if you updated (copied) rep709 you also need to incorporate the changes done by Braath pull request (in the report section) for the tax report to show Bank Payments

One thing for sure..stock FA 2.4.x does not support Tax on Bank Payments, deposits..etc..

Re: REP709.php TAX REPORT BUG

rafat wrote:

@seahawk

One thing for sure..stock FA 2.4.x does not support Tax on Bank Payments, deposits..etc..

The question is why not, since in RSA the banks charge VAT on all their baning transaction fees and account fees. There is no other way that these VAT can be incorporated into the TAX Report since it is only displayed on your bank statement. No separate VAT invoice is issued by the bank.

I can understand that it is not all banking transactions, but when you create a quick entry with VAT inclusive for bank cost and account fees, all of that is automated. Just enter the amount.

Any other transaction that happens once-off, who is not a customer or supplier will be handled the same way, although you have an invoice as proof.

Wynand

Re: REP709.php TAX REPORT BUG

Exactly... I don't know why not. I am under the impression that this functionality will be included in FA 2.5.