Topic: Closing a fiscal year needs to update the query a bit.

The fiscal year close needs to change the query to get the amount.

It was now like this.

// retrieve total balances from balance sheet accounts
    $sql = "SELECT SUM(amount) FROM ".TB_PREF."gl_trans INNER JOIN ".TB_PREF."chart_master ON account=account_code
        INNER JOIN ".TB_PREF."chart_types ON account_type=id INNER JOIN ".TB_PREF."chart_class ON class_id=cid 
        WHERE ctype>=".CL_ASSETS." AND ctype <=".CL_EQUITY." AND tran_date <= '$to'";

And it should be like this

// retrieve total balances from balance sheet accounts        
        $sql = "SELECT SUM(amount) FROM ".TB_PREF."gl_trans trans LEFT JOIN ".TB_PREF."voided v ON trans.type_no=v.id AND v.type=trans.type INNER JOIN ".TB_PREF."chart_master ON account=account_code
        INNER JOIN ".TB_PREF."chart_types types ON account_type=types.id INNER JOIN ".TB_PREF."chart_class ON class_id=cid WHERE ctype>=".CL_ASSETS." AND ctype <=".CL_EQUITY." AND tran_date <= '$to' AND ISNULL(v.date_)";

I think we missed to check voided transactions before getting results.

Re: Closing a fiscal year needs to update the query a bit.

Which file is this in?

Re: Closing a fiscal year needs to update the query a bit.

@rafat, numerous text editors have search in file functionality(Notepad++ for one), and it takes seconds to carry out

Re: Closing a fiscal year needs to update the query a bit.

rafat wrote:

Which file is this in?

Fiscal years db file. The fiscal year close function

Re: Closing a fiscal year needs to update the query a bit.

@kvvaradha: Since you are making a LEFT JOIN for the voided entries, there could be many null voided entries in the result set that would have the v.date_ field as NULL anyway!

@joe: can polish it up and check and include it if needed.

Re: Closing a fiscal year needs to update the query a bit.

But isn't the voided entries all set to 0 amounts? So in my eyes this is a redundant change, right?

Joe

7 (edited by kvvaradha 08/18/2018 12:11:29 pm)

Re: Closing a fiscal year needs to update the query a bit.

joe wrote:

But isn't the voided entries all set to 0 amounts? So in my eyes this is a redundant change, right?

Joe

I guess you are right. But one of a FA user asked to check it,when he closes the fiscal year. he had some voided balance included in the balance. His version is 2.4.3.

Re: Closing a fiscal year needs to update the query a bit.

Ok, let me investigate this a little more.

Joe

Re: Closing a fiscal year needs to update the query a bit.

@joe: any developments?

Re: Closing a fiscal year needs to update the query a bit.

No, not really. However in normal conditions the old code should work.

Joe