1 Topic by mmunoz (edited by mmunoz 08/15/2013 06:26:24 pm)

  • Registered: 08/15/2013
  • Posts: 3

Topic: inventory gl inquiry does not match balance

I get different total amounts when displaying  the gl ledger inquiry and balance for the inventory account in the same date range.
As additional information,  i have an opening balance of zero. Had to make adjustements due to negative inventory at the beginning of period. Thank you very much in advance for every contribution to this topic . I am using 2.1.17 version, had the same issue with 2.3.16.

*****
i think i found a bug here:
in the gl inquiry i get this query:

SELECT 0_gl_trans.*, 0_chart_master.account_name FROM 0_gl_trans
            LEFT JOIN 0_voided v ON 0_gl_trans.type_no=v.id AND v.type=0_gl_trans.type,0_chart_master WHERE 0_chart_master.account_code=0_gl_trans.account
        AND ISNULLAND ISNULL(v.date_)
        AND tran_date >= '2013-01-01'
        AND tran_date <= '2013-08-15' AND 0_gl_trans.amount <> 0 AND 0_gl_trans.account = '12000' ORDER BY tran_date, counter.


if i remove " AND ISNULL(v.date_) " part of the query i get the same summed amount as in balance sheet.

i solved this looking at gl_trans table, and setting the transaction amounts to 0 where   (v.date_)<> NULL in the resultset above. Had to be careful tough to set both entries of the same transaction (debit and credit).

i think the problem happens when voiding transactions of type 22.

  • From: Singapore, India, Chennai
  • Registered: 05/02/2009
  • Posts: 6,150

Re: inventory gl inquiry does not match balance

Line 118 of gl/includes/db/gl_db_trans.inc
and
line 93 of gl/includes/db/gl_db_bank_trans.inc
have the said phrase: AND ISNULL(v.date_)

Any corrections needed?

apmuthu's Website

  • Registered: 12/25/2007
  • Posts: 2,448

Re: inventory gl inquiry does not match balance

No. This excludes voided transactions from the query results. Voided transactions should never be taken into account.
This is weird, transaction type 22 is Supplier Payment, so the transactions of this type should never appear in inventory account.

mmunoz wrote:

I get different total amounts when displaying  the gl ledger inquiry and balance for the inventory account in the same date range.

Which menu options are you using to retrieve those data?
Janusz