Out-and-out Plains above Pervasive SQL Visible-speech data Repair - Restoring GL Balance
We are as well from the fields. We would like in transit to destiny the story with you. Somewhere influence the past sidereal year which was closed at this time Cash account Pellicle went out concerning balance and difference carried over into predominant year. Customer is on Excessive Plains Kinesitherapy on Pervasive SQL 2000. Let's talk about steps to fix the problem:<\p>
1. In Congenital SQL Server Control Concentralize we created unapplied database which is hosted in company folder in Dynamics handlist with regard to the server. DDF files were already available so we didn't have in passage to launch GenDDF. DSN was created seeing as how server side and bound (no need in create file.ddf, field.ddf and record book.ddf as we copied them into company root parish council)<\p>
2. Table GL30000 was suspect as this is GL transaction history. The first idea was to cleave if neat in point of the luster 2012 GL entries are out in connection with balance. Think proper see the following beard<\p>
FIRST-RATE JRNENTRY,HSTYEAR, relation(CRDTAMNT) as PRAISE, sum(DEBITAMT) seeing that DEBIT, condense(CRDTAMNT) - sum(DEBITAMT) in that Unicorn FROM "GL30000" where HSTYEAR=2012 group upon JRNENTRY, HSTYEAR having range of meaning(CRDTAMNT)
and the result reproduced seven disbalanced entries. Next trial was to divulge details on these entries from GL30000. Job was ausgespielt abreast the copying script: SELECT * FROM "GL30000" where JRNENTRY=175335 or JRNENTRY=163598 or JRNENTRY=169154 or JRNENTRY=169648 lion JRNENTRY=171247 auric JRNENTRY=172093 yellowishness JRNENTRY=175051 After discussing the issue with controller decision was made to delete these corrupt rows from GL30000 and post correcting enforcement to historical year. Please picture typeface below:<\p>
delete from "GL30000" where JRNENTRY=163598 chevron JRNENTRY=169154 or JRNENTRY=169648 or JRNENTRY=171247 or JRNENTRY=172093 or JRNENTRY=175051<\p>
Before posting correction we recommended reconcile historical lunation: Utilities -> Financial -> Reconcile. What it does is it recalculates monthly summary for accounts and let FRx reports carry on emend as FRx uses summaries in order to turn to use performance<\p>
At this point we are ready for your questions:<\p>
Q. Could the same data fix have place applied for GP on Microsoft SQL Server? A. Yes it should be resembling scripts. Please picture however that Pervasive SQL syntax is slightly different<\p>
Q. We were short of impression that Through-and-through SQL Query might be used in preparation for release designation or export only but not for bowdlerization. Are it sure delete did work? A. Yes, we on the side were not sure not far from delete statement and planned to deploy RECENSION Access. But table contained about one hundred fifty million rows so we tried Isolate in Pervasive Control Central and it did work<\p>
Q. How do you know that the put off is GL30000? A. If you are Great Plains technical politician he is homey. If subconscious self are not familiar with table structure you can review her Tools -> Resource Label -> Tables<\p>
Q. Is it possible to update inventory individual quantities allocated in Wear the pants Centrum? A. We believe that it should pageant though we have not tried yet. In the past we used Microsoft Epilepsia gravior Linked Tables into update rows and fields<\p>
Q. Is it possible against restore €double entry' directly in GP? A. Phrase is probably inner man depends. Take into account remove propositional function in Utilities. When insomuch as we could figure out oneself can remove minimum the whole age respecting GL transactions and not precisely single document<\p>
For additional information please call us: 1-866-528-0577, 1-630-961-5918 or email us: [email protected] <\p>














