subject: Great Plains On Pervasive Sql Data Repair Restoring Gl Balance [print this page] We are just from the fieldsWe are just from the fields. We would like to share the story with you. Somewhere in the past year which was closed at this time Balance Sheet went out of balance and difference carried over into current year. Customer is on Great Plains Dynamics on Pervasive SQL 2000. Lets talk about steps to fix the problem:
1.In Pervasive SQL Server Control Center we created new database which is hosted in company folder in Dynamics directory on the server. DDF files were already available so we didnt have to launch GenDDF. DSN was created as server side and bound (no need to create file.ddf, field.ddf and index.ddf as we copied them into company root directory)
2.Table GL30000 was suspect as this is GL transaction history. The first idea was to check if some of the year 2012 GL entries are out of balance. Please see the following script
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)
and the result reproduced seven disbalanced entries. Next step was to give details on these entries from GL30000. Job was done by the following script:
SELECT * FROM "GL30000" where JRNENTRY=175335 or JRNENTRY=163598
or JRNENTRY=169154
or JRNENTRY=169648
or JRNENTRY=171247
or JRNENTRY=172093
or JRNENTRY=175051
After discussing the issue with controller decision was made to delete these corrupt rows from GL30000 and post correcting transaction to historical year. Please see script below:
delete from "GL30000"
where JRNENTRY=163598
or JRNENTRY=169154
or JRNENTRY=169648
or JRNENTRY=171247
or JRNENTRY=172093
or JRNENTRY=175051
Before posting correction we recommended reconcile historical year: Utilities -> Financial -> Reconcile. What it does is it recalculates monthly summary for accounts and let FRx reports run correct as FRx uses summaries in order to improve performance
At this point we are ready for your questions:
Q. Could the same data fix be applied for GP on Microsoft SQL Server?
A. Yes it should be similar scripts. Please note however that Pervasive SQL syntax is slightly different
Q. We were under impression that Pervasive SQL Query might be used for data selection or export only but not for deletion. Are you sure delete did work?
A. Yes, we also were not sure about delete statement and planned to deploy MS Access. But table contained about one hundred fifty thousand rows so we tried Delete in Pervasive Control Center and it did work
Q. How do you know that the table is GL30000?
A. If you are Great Plains technical consultant it is easy. If you are not familiar with table structure you can review it Tools -> Resource Description -> Tables
Q. Is it possible to update inventory item quantities allocated in Control Center?
A. We believe that it should work but we have not tried yet. In the past we used Microsoft Access Linked Tables to update rows and fields
Q. Is it possible to restore double entry directly in GP?
A. Answer is probably it depends. Consider remove data in Utilities. But as we could figure out you can remove minimum the whole day of GL transactions and not precisely single document
For additional information please call us: 1-866-528-0577, 1-630-961-5918 or email us: help@albaspectrum.com
