Topic: Editing a Journal Entry results in a duplicate entry on Tax Report

We noticed that editing a journal entry that affects VAT control accounts results in a duplicate entry on the Tax report - but not in the GL VAT control acc.

For example if I edit a journal entry 3 times and there is VAT in the entry, the entry will appear 3 times in the Tax Report instead of once.   The GL VAT control will be ok.

Re: Editing a Journal Entry results in a duplicate entry on Tax Report

@joe: possible updation should be tried first before insertion or there is a primary key issue that is not satisfied in the query....

Re: Editing a Journal Entry results in a duplicate entry on Tax Report

I will have a look into this later.

Joe

Re: Editing a Journal Entry results in a duplicate entry on Tax Report

The sql default for listing the Journal Vouchers is:

SELECT COUNT(DISTINCT gl.tran_date, a.gl_seq, gl.type, gl.type_no) FROM 1_gl_trans as gl
         LEFT JOIN 1_audit_trail as a ON
            (gl.type=a.type AND gl.type_no=a.trans_no)
         LEFT JOIN 1_comments as com ON
            (gl.type=com.type AND gl.type_no=com.id)
         LEFT JOIN 1_refs as refs ON
            (gl.type=refs.type AND gl.type_no=refs.id)
         LEFT JOIN 1_users as u ON
            a.user=u.id
      WHERE (gl.tran_date >= '2015-03-08'
        AND gl.tran_date <= '2015-04-08'
        AND gl.amount!=0 AND gl.type='0');

In such display, when any element in the GROUP part of the SQL is null, the COUNT evaluates to NULL. The function sql_gen() in includes/db_pager.inc has:

            return "SELECT COUNT($group) FROM $from $where";

The comments table does not have any primary key and is the current order of fields correct?

CREATE TABLE `1_comments` (
  `type` int(11) NOT NULL DEFAULT '0',
  `id` int(11) NOT NULL DEFAULT '0',
  `date_` date DEFAULT '0000-00-00',
  `memo_` tinytext,
  KEY `type_and_id` (`type`,`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Last edited by apmuthu (04/08/2015 03:15:34 pm)

Re: Editing a Journal Entry results in a duplicate entry on Tax Report

so what does that mean in english?

Re: Editing a Journal Entry results in a duplicate entry on Tax Report

When the Journal Inquiry is done, first the total number of records is (COUNT) queried to determine the page links (Next/Prev/Total...). If it is a GROUP BY query, and one of the GROUP BY fields is NULL, then the total number of records count returned is 0 (Operation on NULL).

The full fix is given in this Forum Post but the real fix would be when the input data is consistent with all NULLs removed by defining appropriate defaults (blank string, 0, etc) in the table structure itself.

Last edited by apmuthu (04/09/2015 01:06:48 pm)

Re: Editing a Journal Entry results in a duplicate entry on Tax Report

thanks, your reply is really appreciated but still doesn't mean much to me - sorry.
main point is that there's a bug that makes the VAT report wrong if a transaction has been edited - if a transaction has been edited then it's included twice on the VAT report instead of once.
the VAT report is used to make the VAT return and claiming the same input VAT twice is a problem that can have serious implications with the tax authorities.
any change of this real fix being included in a release so that Front's VAT report is reliable?
it's a pretty bad bug.

Last edited by MarkAndrew (04/09/2015 07:16:53 pm)

Re: Editing a Journal Entry results in a duplicate entry on Tax Report

I followed the link to the fix but I have no idea what to do as I don't understand code.
is there any way this can be included in next update?

Re: Editing a Journal Entry results in a duplicate entry on Tax Report

The issue has been fixed in the core itself and the changes have been bundled and attached to the release announcement post. Just overwrite your FA v2.3.24 files with them.

Last edited by apmuthu (04/13/2015 07:20:58 am)

Re: Editing a Journal Entry results in a duplicate entry on Tax Report

Thx

Re: Editing a Journal Entry results in a duplicate entry on Tax Report

The tax report has the same problem if you do an inquiry on sales/Customer Transaction Inquiry and you open an invoice or you open a credit note, it will duplicate that as a transaction if you update anything, even if nothing has changed.

That results in the the credit note number and invoice number displaying twice doubling the amount of tax payable for the period.

I have installed the update as recommended above, but the problem still persists. It seems it was fixed only for the purchases not sales. If I did an inquiry on an invoice, it will duplicate the invoice when updated.

I agree that this have serious implications for someone who will claim double deduction or has to pay double the amount especially if they do not print the detail in the report and verify the number of invoices on Output Vat and Input vat.

Any help or fix in this regard will really be appreciated.

Last edited by seahawk (07/05/2015 07:20:48 am)

Wynand

Re: Editing a Journal Entry results in a duplicate entry on Tax Report

Subscribed.

MarkAndrew and seahawk, thank you for discovery and reporting this bug.

Is there a plan to fix this and what is the time frame?

Re: Editing a Journal Entry results in a duplicate entry on Tax Report

This bug has also been reported on Mantis.

I have asked Janusz to take care of this. Hopefully he will do that asap.

Joe

Re: Editing a Journal Entry results in a duplicate entry on Tax Report

Hi Joe,

Has this bug been corrected already ?
We experienced the same issue as Seahawk.

The only work around we found was to void and recreate the invoices impacted.

Thanks,
René

Re: Editing a Journal Entry results in a duplicate entry on Tax Report

I have contacted Janusz again for a reminder.

Joe

Re: Editing a Journal Entry results in a duplicate entry on Tax Report

Kindly state the Mantis Bug Number and full link for ease of access.

Re: Editing a Journal Entry results in a duplicate entry on Tax Report

We cannot reproduce the problem, I guess it is somewhat specific to tax setup on your system.
Please send minimal database content on which you observe the issue to our contributions mailbox, together with the problem description. This will allow us fix the issue.

Janusz

Re: Editing a Journal Entry results in a duplicate entry on Tax Report

I found this bug 2 years ago but I cannot find the cause. Just today that I tried to fix it again and found that the SQL in report709.php seems to be unreasonable as the following.

    $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 
            (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 taxrec.trans_type <> ".ST_CUSTDELIVERY."
            AND taxrec.tran_date >= '$fromdate'
            AND taxrec.tran_date <= '$todate'
        ORDER BY taxrec.trans_type, taxrec.tran_date, taxrec.trans_no, taxrec.ex_rate";

When there are 2 records in tax_trans (The amount value in one record is not 0 and the other one is 0) which linked to the same record in debtor_trans, this SQL will generate 2 records after joining.

In tax_trans, the record of amount value 0 is filtered out by WHERE parameters, and only 1 tax_tran will be activated to join. But in joining gl_trans, the record of amount 0 is not filtered out so they all will be joined with this 1 tax_tran and get 2 transactions in the report.

Solution:
Just add " AND (gl.amount <> 0)" into the LEFT JOIN of gl_trans. This is what I have done so far. However, I do not really know the side effect as I have never thoroughly get into the codes of FrontAccounting.

Re: Editing a Journal Entry results in a duplicate entry on Tax Report

In the demo data, there is no element with gl.amount = 0 and hence I have not tested it out. Generally this occurs when a transaction is edited, the old one's amount becomes 0  and the current (edited) value becomes the gl.amount in the new record.

@joe: does this merit inclusion in the base code?

Post's attachments

Txn.sql.png 14.54 kb, file has never been downloaded. 

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

Re: Editing a Journal Entry results in a duplicate entry on Tax Report

I have talked with Janusz and he claims that this bug has already been fixed.
And this is correct. Look into the sql in the committed rep709 and you will see the correction.

Joe

Re: Editing a Journal Entry results in a duplicate entry on Tax Report

Thanks @joe.

@itronics committed it on 2015-07-10.

This now stands cross referenced. The name field / column can be backquoted in the sql.