Dynamics Gp Data Repair Balance Sheet Report Doesnt Balance
If you are controller and responsible for P&L
, Balance Sheet and Retained Earnings then situation when double entry is out of balance might be annoying. We just got one of these issues sorted out and fixed and we would like to share the experience. Lets review the reasons for double entry to be compromised and possible fixes.
1.What is the likely reason for balance compromise? When you post documents in modules for example Sales Order Processing they have preset distribution account. This allows posting all the way through General Ledger. If one of the accounts is missing GL posting will be blocked. If entry originates in GL directly then until difference is zero you cannot post it. When General Ledger posting happens distributions are recorded in GL20000 table (open year). If somebody who is good in SQL scripting but not so familiar with accounting double entry principle deletes one of the rows then posted transaction becomes not balanced. If you are on old Great Plains Dynamics on Pervasive SQL 2000 or Btrieve engine may fail to record in GL20000.btr file. On Microsoft SQL Server platform the problem usually doesnt occur and if you see it then likely it was mistake by SQL programmers
2.What is recommended as fix? Microsoft Business Solutions recommends adding account and creating correction in GL with offset to this account. Post it and then delete the account via SQL delete statement. Second solution is to remove not balanced records through SQL and then create correct ones and post
Lets try to answer several questions associated with this problem:
Q. The issue described above happened in last year which we already closed. However when we reviewed Balanced Brought Forward record which is data December 31s of the last year we found that it has difference meaning that debit summary is not equal to credit summary. How could something like that happened and be posted?
A. This seems to be related to closing year procedure. When you close the year records are moved from GL20000 to GL30000 (history). You do not really need to post something manually as system does the job. We are not positive but it might be that BBF transaction doesnt come through automatic balancing control. We have seen this problem with one of the customers on Pervasive SQL 2000 and Great Plains version 6.0. Our recommendation is this. If you found dis balance in your GL then do not close the year until issue is resolved
Q. I am IT support in the company and we have the problem discussed in this blog. However we have huge volume of transactions and we cannot find the problem by reading all these documents. Is it possible to catch the problem by SQL select query?
A. Yes. Try the following script (of course make necessary changes)
SELECT OPENYEAR, JRNENTRY, sum(DEBITAMT)-sum(CRDTAMNT) as [Difference]
FROM GL20000 where OPENYEAR=2013
group by OPENYEAR, JRNENTRY
having sum(CRDTAMNT)sum(DEBITAMT)
Q. How do we catch the same culprit entries in historical year?
A. You should use similar query against GL30000. Here is the sample one
SELECT JRNENTRY,HSTYEAR, sum(CRDTAMNT) as CREDIT,
sum(DEBITAMT) as DEBIT,
sum(CRDTAMNT) - sum(DEBITAMT) as [Difference]
FROM GL30000
where HSTYEAR=2012
group by JRNENTRY, HSTYEAR
having sum(CRDTAMNT)sum(DEBITAMT)
Q. These samples above are in Transact SQL and could be used on MS SQL Server. We are on pervasive. Is it possible to do similar ones in our case?
A. Yes. We recommend you to do it in Pervasive Control Center. Syntax will be slightly different. Try something like the following
SELECT JRNENTRY,HSTYEAR, sum(CRDTAMNT) as CREDIT,
sum(DEBITAMT) as DEBIT,
sum(CRDTAMNT) - sum(DEBITAMT) as Difference
FROM "GL30000"
where HSTYEAR=2012
group by JRNENTRY, HSTYEAR
having sum(CRDTAMNT)sum(DEBITAMT)
Table name should be in double quotes as you can see
OK. At this point of time we have to close this session. If your question is not answered please feel free to email it to us
For additional information, please feel free to call us 1-866-528-0577 or 1-630-961-5918 (this number works for international customers) or email us help@albaspectrum.com We serve you USA/Canada nationwide via remote support
by: Andrew Karasev
Perform Mmc Card Data Recovery With Ease Todays Demanding Data Center Need To Tighten Their Security With Cloud Technology Gaining Benefits From Big Data Ezsupport-ny Offers An Easy Way To Import Data From Divorce Clients Reasons To Choose Data Entry Company In India Ipod Touch Data Recovery-recover Ipod Touch Data With Ease How To Select The Right Grants Database Hard Drive Recovery-choose Best Method Can Containers Techniques Actually Prevent Unauthorized Data Access? What Questions To Ask About Breast Augmentation Recovery Data Scraping Techniques Are Important Tools Web Scraping Techniques Are Important Tools That Provide Relevant Data Great Plains Dos Data Repair