Bug Tracker Incident #7729

 


Summary
Project
INCDT - xTuple ERP
Incident Category
Bugs
Incident Number
7729
Visibility
public


Product Version
2.3.2
Fixed In Version
None


Summary
Patch for out of Balance - Balance Sheet


Description

This issue originally popped up do to our Balance sheet being off by a few hundred dollars. I researched the cause and narrowed it down to a few G/L transactions that we had posted into very old periods to clear up a few credit memos we had issued in the past.

Background:
We originally setup out system with periods starting 1-1-2007 and going month by month there after. We needed to post a few G/L transactions prior to this to clean up a few credit memos. I created a new period 1-1-2000 through 12-31-2006 as one big period. However, I could never get it to close, so I forced it to be closed in the Database with an UPDATE statement (I have since discovered the "period_initial" column).

I thought the cause might be related to creating one large period, so I went back and made month by month periods starting in 1-1-2003. We had no activity on the G/L in 2003, so I wanted an empty year to calculate Retained Earnings set to Zero for 1-1-2004. To do that I needed to close the year period 2003, however, since I forced 1-1-2003 to be closed, the "trialbal" table had null values 1-1-2003 through 1-31-2003. This prevented me from closing 2003 year period.

After I discovered the "period_initial" column, I was able to close the first period and year, 1-1-2003. However, my balance sheet was still off. The Retained Earnings account was building, but there was no Asset to balance it. I found that the Trial Balance was not showing any of the G/L transactions from 2004 and 2005 that were used to calculate the Retained Earnings. This is because they had already been posted in the past. The posting process updates the Trial Balance table. However, when they were posted months ago they went into the 1-1-2000 through 12-31-2006 period. This is when the Trial Balance update would have been done. I effectively needed to recalculate the Trial Balance table for all of the new periods I just created.

The G/L transactions were debits to our "Prepaid Receivables Account" that is a Revenue account and credits to "Accounts Receivable" that is an Asset account. When you close a year, it totals G/L transactions for Revenue and Expense accounts and pushes that over to Retained Earnings. This has nothing to do with Trial Balances.
closeaccountingyearperiod()
[code]
SELECT SUM(gltrans_amount) INTO _totalProfitLoss
FROM gltrans, accnt
WHERE ( (gltrans_accnt_id = accnt_id)
AND (accnt_type IN ( 'R', E'E' ) )
AND (gltrans_date between _r.yearperiod_start and _r.yearperiod_end ) );
IF(_totalProfitLoss IS NULL) THEN
_totalProfitLoss := 0;
END IF;
[/code]

This was causing Retained Earnings to show the G/L transactions in question. However, the Asset side of the Balance sheet pulls from the "trialbal" table. Since that table does not show the G/L transactions in question, they were not updating year to year or even period to period. This was happening because I was never able to properly close the period that they G/L transactions happened in.

Therefore the Balance Sheet was out of balance.

I tried to update the "trial balance" table via the suggested method of clearing the rows from the table and the closing each period up to the current period.
[code]
DELETE FROM trialbal;
[/code]
When you close the periods, there is no action in the functions that totals G/L transactions for that period. Since all of the G/L transactions are already posted, the Trial Balance shows no activity.

I looked into the forwardupdatetrialbalance() function and created the following solution.


Details
Reporter
bendiy
CRM Account
Xikar, Inc
Status
Closed
Assigned
cryan


Priority
Normal
Severity
Patch
Updated
09-06-12 15:39
Resolution
No Change Required



Comments

DateUsernameComment
03/04/11 08:25mfgadmin

Steps to Reproduce:

Step 1 - Change the forwardupdatetrialbalance() function to the following:
[code]
-- Function: forwardupdatetrialbalance(integer)

-- DROP FUNCTION forwardupdatetrialbalance(integer);

CREATE OR REPLACE FUNCTION forwardupdatetrialbalance(integer)
RETURNS integer AS
$BODY$
DECLARE
pTrialbalid ALIAS FOR $1;
_p RECORD;
_r RECORD;
_ending NUMERIC;
_prevYear INTEGER;
_currYear INTEGER;
-------------------------------------------------
-- Add the following line
-------------------------------------------------
_glAmount NUMERIC;
-------------------------------------------------

BEGIN

SELECT trialbal_accnt_id, trialbal_ending,
period_end, accnt_type IN ('E', E'R') AS revexp INTO _p
FROM trialbal, period, accnt
WHERE ( (trialbal_period_id=period_id)
AND (trialbal_accnt_id=accnt_id)
AND (trialbal_id=pTrialbalid) );

_ending = _p.trialbal_ending;

SELECT yearperiod_id INTO _prevYear
FROM yearperiod
WHERE (_p.period_end BETWEEN yearperiod_start AND yearperiod_end);
IF (NOT FOUND) THEN
_prevYear := -1;
END IF;

-- Find all of the subsequent, but not future, periods and their trialbal, if they exist
-------------------------------------------------
-- Add period_start to SELECT statement
-------------------------------------------------
FOR _r IN SELECT period_id, period_start, period_end,
trialbal_id, trialbal_debits, trialbal_credits,
trialbal_yearend
FROM period LEFT OUTER JOIN trialbal
ON ( (trialbal_period_id=period_id) AND (trialbal_accnt_id=_p.trialbal_accnt_id) )
WHERE ( (period_start > _p.period_end)
AND (period_start <= CURRENT_DATE) )
ORDER BY period_start LOOP
-------------------------------------------------

SELECT yearperiod_id INTO _currYear
FROM yearperiod
WHERE (_r.period_end BETWEEN yearperiod_start AND yearperiod_end);
IF (NOT FOUND) THEN
_currYear := -1;
END IF;

IF (_p.revexp AND _currYear != _prevYear) THEN
_ending := 0;
END IF;

_prevYear := _currYear;

IF (_r.trialbal_id IS NULL) THEN
-- Comment out the following up to INSERT INTO trialbal
-------------------------------------------------
-- Uncomment the following 4 lines
-------------------------------------------------
SELECT SUM(gltrans_amount) INTO _glAmount FROM gltrans
WHERE ( (gltrans_date BETWEEN _r.period_start and _r.period_end )
AND (gltrans_accnt_id=_p.trialbal_accnt_id)
AND (gltrans_posted) );
-------------------------------------------------
-- and change 2nd and 3rd VALUES line of INSERT to read
-------------------------------------------------
-- These values do not work right if there is a NULL value
-------------------------------------------------
-- _ending, _ending + _glAmount,
-- noneg(0 - _glAmount), noneg(_glAmount), FALSE );
-------------------------------------------------

INSERT INTO trialbal
( trialbal_period_id, trialbal_accnt_id,
trialbal_beginning, trialbal_ending,
trialbal_debits, trialbal_credits, trialbal_dirty )
VALUES
( _r.period_id, _p.trialbal_accnt_id,
-------------------------------------------------
-- Put the VALUES here and add COALESCE to use 0 if NULL
-------------------------------------------------
COALESCE(_ending,0), COALESCE(_ending + _glAmount,0),
COALESCE(noneg(0 - _glAmount),0), COALESCE(noneg(_glAmount),0), FALSE );
-------------------------------------------------
-------------------------------------------------
-- Comment out the following 2 lines
-------------------------------------------------
-- _ending, _ending,
-- 0, 0, FALSE );
-------------------------------------------------
ELSE
UPDATE trialbal
SET trialbal_beginning = (_ending + trialbal_yearend),
trialbal_ending = (_ending + trialbal_yearend - _r.trialbal_debits + _r.trialbal_credits),
trialbal_dirty = FALSE
WHERE (trialbal_id=_r.trialbal_id);

_ending = (_ending + _r.trialbal_yearend - _r.trialbal_debits + _r.trialbal_credits);
END IF;
END LOOP;

UPDATE trialbal
SET trialbal_dirty = FALSE
WHERE (trialbal_id=pTrialbalid);

RETURN pTrialbalid;

END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION forwardupdatetrialbalance(integer) OWNER TO mfgadmin;
[/code]

Step 2 - Clear trialbal table
[code]
DELETE FROM trialbal;
[/code]

Step 3 - Open all Periods and Years

Step 4 - Make sure your first Period is set to the Initial Period in the dateabase. It would be useful to have this option in System > Configure Modules > Accounting or somewhere on the Accounting Periods window.
Remove the old one:
[code]
UPDATE period SET period_initial=FALSE WHERE period_id='old initial period_id here';
[/code]
Set the new one:
[code]
UPDATE period SET period_initial=TRUE WHERE period_id='new initial period_id here';
[/code]

Step 5 - Close First Period
I found that the first time you close a period, it may not total the G/L transactions. Instead, I ensured that the first two periods had Zero activity and Zero Balances. This may not be the case, but when it is closed, it creates Zeros for all accounts in that period and Zeros for all accounts in the next period with the "trialbal_dirty" set to TRUE.

closeaccountingperiod()
[code]
FOR _r IN SELECT accnt_id, accnt_type IN ('E', E'R') AS revexp,
trialbal_id, trialbal_beginning,
trialbal_credits, trialbal_debits
FROM accnt LEFT OUTER JOIN trialbal ON ( (trialbal_accnt_id=accnt_id) AND (trialbal_period_id=pPeriodid) )
ORDER BY accnt_id LOOP
IF (_r.trialbal_id IS NULL) THEN
_ending = 0;

INSERT INTO trialbal
( trialbal_period_id, trialbal_accnt_id,
trialbal_beginning, trialbal_ending, trialbal_dirty,
trialbal_credits, trialbal_debits )
VALUES
( pPeriodid, _r.accnt_id,
0, 0, FALSE,
0, 0 );
ELSE
_ending = (_r.trialbal_beginning - _r.trialbal_debits + _r.trialbal_credits);

UPDATE trialbal
SET trialbal_ending=_ending,
trialbal_dirty = FALSE
WHERE (trialbal_id=_r.trialbal_id);

PERFORM forwardUpdateTrialBalance(_r.trialbal_id);
END IF;
[/code]

If trialbal_id is null, which it will be the first time, it just inserts Zeros. The Else is what calls the edited forwardupdatetrialbalace() function that would total all of the G/L transactions. This is why I think you need the first two periods to be Zero balance.

Step 6 - Run "Forward Update Accounts.." from Accounting > Utilities
Running it the first time will take a minute as it calls the edited forwardupdatetrialbalances() function and the all G/L entries are totaled. It will then update the ending balance in the "trialbal" table.

Step 7 - Run "Forward Update Accounts.." again
I found that the first time calculates the ending balance, but does not move the ending balance to the beginning balance of the next period. Running it a second time will move the ends balances from Step 6 to the beginning balances of the next period.

Step 8 - Close all periods up to current or desired period

03/04/11 08:25mfgadmin

This issue originally popped up do to our Balance sheet being off by a few hundred dollars. I researched the cause and narrowed it down to a few G/L transactions that we had posted into very old periods to clear up a few credit memos we had issued in the past.

Background:
We originally setup out system with periods starting 1-1-2007 and going month by month there after. We needed to post a few G/L transactions prior to this to clean up a few credit memos. I created a new period 1-1-2000 through 12-31-2006 as one big period. However, I could never get it to close, so I forced it to be closed in the Database with an UPDATE statement (I have since discovered the "period_initial" column).

I thought the cause might be related to creating one large period, so I went back and made month by month periods starting in 1-1-2003. We had no activity on the G/L in 2003, so I wanted an empty year to calculate Retained Earnings set to Zero for 1-1-2004. To do that I needed to close the year period 2003, however, since I forced 1-1-2003 to be closed, the "trialbal" table had null values 1-1-2003 through 1-31-2003. This prevented me from closing 2003 year period.

After I discovered the "period_initial" column, I was able to close the first period and year, 1-1-2003. However, my balance sheet was still off. The Retained Earnings account was building, but there was no Asset to balance it. I found that the Trial Balance was not showing any of the G/L transactions from 2004 and 2005 that were used to calculate the Retained Earnings. This is because they had already been posted in the past. The posting process updates the Trial Balance table. However, when they were posted months ago they went into the 1-1-2000 through 12-31-2006 period. This is when the Trial Balance update would have been done. I effectively needed to recalculate the Trial Balance table for all of the new periods I just created.

The G/L transactions were debits to our "Prepaid Receivables Account" that is a Revenue account and credits to "Accounts Receivable" that is an Asset account. When you close a year, it totals G/L transactions for Revenue and Expense accounts and pushes that over to Retained Earnings. This has nothing to do with Trial Balances.
closeaccountingyearperiod()
[code]
SELECT SUM(gltrans_amount) INTO _totalProfitLoss
FROM gltrans, accnt
WHERE ( (gltrans_accnt_id = accnt_id)
AND (accnt_type IN ( 'R', E'E' ) )
AND (gltrans_date between _r.yearperiod_start and _r.yearperiod_end ) );
IF(_totalProfitLoss IS NULL) THEN
_totalProfitLoss := 0;
END IF;
[/code]

This was causing Retained Earnings to show the G/L transactions in question. However, the Asset side of the Balance sheet pulls from the "trialbal" table. Since that table does not show the G/L transactions in question, they were not updating year to year or even period to period. This was happening because I was never able to properly close the period that they G/L transactions happened in.

Therefore the Balance Sheet was out of balance.

I tried to update the "trial balance" table via the suggested method of clearing the rows from the table and the closing each period up to the current period.
[code]
DELETE FROM trialbal;
[/code]
When you close the periods, there is no action in the functions that totals G/L transactions for that period. Since all of the G/L transactions are already posted, the Trial Balance shows no activity.

I looked into the forwardupdatetrialbalance() function and created the following solution.

03/04/11 08:25mfgadmin

Additional Info:

Following these steps, I was able to get my test database to match my production database balance sheet. However, the Trial Balances and Balance Sheet actually show the activity for the G/L transactions in 2004 and 2005 that were causing the problems and not in 12-31-2006. The balance sheet also balances now.

Alternatively, if I leave the forwardupdatetrialbalance() function as it is and clear the "trialbal" table and the close all the periods, the only thing that shows on the Balance Sheet is the Retained Earnings calculation. Everything else is Zero.

WARNING
I found that after adding all of the month periods for 1-1-2003 through 12-31-2006 and force closing the first period before I learned how to reset the "period_initial". Using "Forward Update Accounts.." I was able to get my balance sheet to balance. This worked because the period that the original G/L transactions were recorded in was now closed properly. I had created a prior period, so the error checking worked. However, the G/L transactions in question only showed in the original period I had setup 1-1-2000 through 12-31-2006. I have changed that period to be December 2006, 12-1-2006 through 12-31-2006. Even thought the G/L transactions are in 2004 and 2005, they show in December 2006. This is because when they were posted, I only had one period. If I run a Balance Sheet for 11-1-2006 through 11-30-2006 I have Zero balances.

I think the cause for this is an error in "Forward Update Accounts..". You have to run it TWICE for the balances to truly forward update. I ran it the first time after creating all the new periods and closing December 2006 and was still out of balance. The first time all it did was total the ending balances. That is when I started researching the issue. I have since ran it a second time. That is when it moved the ending balance to the beginning of the next period. At that point, my Balance Sheet balanced. Although the G/L transaction still showed in the wrong period (12-1-2006) and not in 2004 and 2005 and my Retained Earnings only show for 1-1-2007 forward.

I believe there my be several users with a Balance Sheet that balances, however, transactions are in the wrong period. "Forward Update Accounts.." never recalculates Posted G/L transactions.

11/24/08 09:35ptyler

Thanks for all your feedback on this issue. As we discovered, the root of the problem occurred when the new initial period was subdivided into several periods. After testing on a pre-subdivision copy of the database, we discovered that re-setting the initial period solved the problem. However, your feedback helped us realize we need to more tightly restrict the creation of new initial periods so users don''t end up in the situation you ran into.

11/21/08 19:11xikar

I think my patch is easier and only involves one "end user" edit to the database to clear the trialbal table. I have made a few minor changes to the forwardUpdateTrialbalance() function since I first posted. I have since edited the closeAccountingPeriod() function to fix the issues related to the first two periods needing to have Zero balances and transactions. You also no longer need to run the forward update function at all during the process. See the attached two patched functions.

1. Install attached patches
2. DELETE FROM trialbal < database edit
3. Open all periods and years
4. Close the first period

The UPDATE gltrans_posted=FALSE method requires three "end user" edits to the database. One of them, close future periods, is forcing the database to do something the client does not allow.

1. Force close all periods < database edit
2. UPDATE gltrans_posted=FALSE < database edit
3. DELETE FROM tiralbal < database edit
4. Thaw and open all periods
5. Close all periods

11/18/08 16:14svn

Revision: 4190
http://postbooks.svn.sourceforge.net/postbooks/?rev=4190&view=rev
Author: cryan
Date: 2008-11-18 21:14:41 +0000 (Tue, 18 Nov 2008)

Log Message:
-----------
Added a trigger to make sure the period_initial flag is always set correctly and script to trigger it during the update. Issue #3316, Issue #6345, Issue #7729.

Added Paths:
-----------
xtupleserver/trunk/dbscripts/triggers/period.sql
xtupleserver/trunk/updatescripts/from_320alpha/fixPeriod.sql

11/18/08 13:51xikar

I know this whole issue would not have popped up at all if I know about the "period_initial" flag. I could have closed my period and forward updated and had no issues. There is no viability in the system for that flag. If someone create new prior periods after the initial setup, they will have the same problem.

The error that you get when you do this "This period cannot be closed because there is a gap between the prior period and this period" makes you think that you need to setup another period before your first period. I had no idea that I needed to reset the initial period in the database.

This caused me to create month by month prior periods, so the Trial Balances needed to be recalculated for those periods. That is when I ran into the issues above.

11/18/08 13:29xikar

The patch is in Advanced View: "Steps to Reproduce"



Characteristics

Operating System
ALL
Doc Flag
False
Copyright Assigned
Yes

Files

Filename
closeaccountingperiod
forwardupdatetrialbalance


Related Documents

TypeNumberDescriptionRelationshipRemove
Incident3857NEW button disabled on S/O DispRelated tox
Incident3858Update Actual Costs Session only works if the user has "Create User" rightsRelated tox
ProjectXTUPLEAPPSPorted From Mantisx


Subscribers

You do not have permission to view subscribers.


Incident History

DateUsernameFieldChange
12/16/10 09:12acdrupalNewIncident Added