Bug Tracker Incident #7729
Steps to Reproduce:
Step 1 - Change the forwardupdatetrialbalance() function to the following:
-- DROP FUNCTION forwardupdatetrialbalance(integer);
CREATE OR REPLACE FUNCTION forwardupdatetrialbalance(integer)
SELECT trialbal_accnt_id, trialbal_ending,
_ending = _p.trialbal_ending;
SELECT yearperiod_id INTO _prevYear
-- Find all of the subsequent, but not future, periods and their trialbal, if they exist
SELECT yearperiod_id INTO _currYear
IF (_p.revexp AND _currYear != _prevYear) THEN
_prevYear := _currYear;
IF (_r.trialbal_id IS NULL) THEN
INSERT INTO trialbal
_ending = (_ending + _r.trialbal_yearend - _r.trialbal_debits + _r.trialbal_credits);
Step 2 - Clear trialbal table
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.
Step 5 - Close First Period
INSERT INTO trialbal
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
Step 7 - Run "Forward Update Accounts.." again
Step 8 - Close all periods up to current or desired period
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.
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.
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.
I looked into the forwardupdatetrialbalance() function and created the following solution.
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.
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.
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.
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
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
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.
The patch is in Advanced View: "Steps to Reproduce"
|Incident||3857||NEW button disabled on S/O Disp||Related to||x|
|Incident||3858||Update Actual Costs Session only works if the user has "Create User" rights||Related to||x|
|Project||XTUPLEAPPS||Ported From Mantis||x|
SubscribersYou do not have permission to view subscribers.
|12/16/10 09:12||acdrupal||New||Incident Added|