Accpac ERP 500 V5.4, SQLServer

Situation: Client migrated from Plus to Accpac 2 years ago with other firm, then they became our client and one of the first things we did was a data integrity check  It returned over a hundred pages of errors:

"Period X net amount does not equal sum of all posted transactions for fiscal set <Account>"

We sent out data for costly and ugly repair (Data was wiped out and replaced with single entries).  In investigating the errors, it seemed that the accounts in error had been changed using the Account Code change at some point, so we put new processes in place to catch the errors earlier.

Since then data has been fine for 5 months, verified by weekly data integrity checks, until now. 

3 Weeks ago, Client did an integrity check on day 1 which was clean, then did a G/L Account Code Change (Combine) on day 2, deviated from process and did no check right after.  Now, in getting ready for migration to 5.5, they performed a Data Integrity check which now has the same error from the past but ONLY on the accounts that were combined.

Data will not be going out this time for repair, it's less painful to restore from year end and re-import batches.

Questions:  Anyone experiencing this problem?  Anyone heard of such a thing?  Any ideas why?  Can it be prevented? 

Thanks for reading and any contributions!

Laura

Since that module came out I've used it probably 50 times, and the only problem I've had with it was it used to be slow.  But the numbers always balanced when it was done.

Thanks Jay, 

Just throwing this out...

What would happen if, after doing the account combine, say for example Account 1is combined to account 3, that an Imported batch was created using account 1?

Again, just trying to figure this out, between the software and the user process.

Doesn't matter.  The batches that were originally used have no bearing on the posted transactions or fiscal sets.  If the original batch was:

1 - 100.00

1 - 200.00

3 - 300.00

3 - 400.00

after the ACC it becomes

3 - 100.00

3 - 200.00

3 - 300.00

3 - 400.00

Is it possible you have a strange rounding problem?  How much off are the balances?

 3 Things: 

Just to be clear, a G/L batch was imported and posted after the ACC using Account 1 instead of Account 3.  I would assume that after the ACC we wouldn't see anymore transactions for 1 and only for 3.  Am I wrong?

2nd: Is there a better/quicker way than what I did?  I exported the Journal details table(GLPJD) and the Account Summary (GLAFS) and compared the two.  Even though this year matched perfect, I couldn't get anything to make sense.

3rd: We just did a test where we took the clean copy of year end.  Made the Account Code changes and ran a data integrity - 7 pages of errors.  Now what???

That doesn't make sense.  After account 1 is merged with account 3, you shouldn't be able to even import a batch with account 1 in it, much less post it.

What service packs of GL and ACC are you using?

All 5.4A

G/L SP 1 (should be 2)

Account Code Change - Doesn't Show any service Packs just 5.4a

I read up on the GL Service Pack and there is no documentation to address this issue because I thought maybe that's what it would be.  I could test here to see if it rememdies the problem.  After ACC what shoudl I see as far as the account setup, becasue right now it doesn't show that there was any effect.

You don't see any effect?  Then it didn't run to completion, the last step of the ACC is updating GLAMF.  Something is smelly fishy with this situation, I suspect there have been other unreported error messages.  Run a Database Setup/Verify to make sure your data dictionary is OK.

 0 Errors.  Looks fine.

Nevertheless, there's something still not right here.  ACC is a rock solid module.  You must be getting other errors you're not reporting (or not hearing about).

Ibelanger, are you able to create and post GL entries to account 1 after it was supposed to be combined into account 3? This def. sounds off.

 It didn't make sense why the account was still there, so I looked into it further, and found that they are re-creating the account after it is combined so they can start with a new account, so my theory that the process wasn't finishing is invalid.  I can perform the processing here and it is goes through fine.

Back to the drawing board - still don't know why I get all the balance mismatches.

That DIC issue is obviously a data problem, but I won't be able to tell you if it was caused by Account Code Change or not. I believe the only way to tell would to be try the ACC on the data (without DIC error), and see if it's really happening after.

 We now have narrowed the issue down to this:

The corruption does not happen if a single account code change is done, however, happens each and everytime an account code change is posted which has more than 1 change in the list.

I tried to duplicate the error here with a stand-alone Pervasive Environment and I cannot - it works fine.  They have a network with SQL Server 2005.

 More information:

This issue occurs in this circumstance: only  When mutliple changes are made where both changes and combines are in the list.  The corruption only occurs on those accounts touched by the combine, the changes seem fine.  It gives errors in the data integrity of these:

In the Combine from Account: Orphan Posted Transaction record...

In the Combine To Account: Net amount does not equal to zero when there are no posted transaction records.

If I do multiple changes only or multiple combines only I do not get these errors.  I also cannot reproduce these errors in our environment. 

Short of restoring data and telling them not to do this again, any idea of what is going on?

Thanks for everyone's help!