Deleting transactions from database

(0) Share

Report

Posted on by Community Member

Hi, I found some bad transactions in a database from a couple of years ago and I want to delete them. I know partner advisory services can help with this and 2013 R2 will have functionally to reopen closed years but I am pretty sure I figured the tables out. Are their any I'm missing? I deleted the journal entry from the GL3000 table, then ran the reconcile utility on all closed fiscal years. This seemed to update the summary activity for each account too. The trial balance is right, and the financial statements are also correct. Retained earnings also looks correct. This journal entry was from a data conversion so it originated in the GL and doesn't come from a sub module. Are there any triggers, stored procedures or other tables I should be concerned about before I make these changes in the production environment?

Thanks,

Mike

*This post is locked for comments

I have the same question (0)

All responses (6)

Answers (1)

Mahmoud Saadi 32,738 on at

Like (0)

Report
Copy link

Link copied!

Deleting from GL30000 is not a recommendation I would give. Although, several experts have done it before

On the following article, Mr. Andrew Karasev talked about such a thing in Dynamics GP Fixing Double Entry if GL30000 table doesn’t balance technology

In a support article by Microsoft (which is related to unit accounts issue) deleting transactions from GL30000 was one of the resolution steps and the required quality check was to run financial reconcile afterwards, Article ID: 857582

Although, the thing I would be concerned about the most is the retained earning account, further checks should be applied to ensure that everything is correct. Additionally, if the journal entry is originating from a specific module, you need to look back for the module since such delete might have caused a variance between the module and general ledger.

Was this reply helpful? Yes No
mpolino on at

Like (0)

Report
Copy link

Link copied!

I would be worried that your ending balance for the affected year won't match the beginning balance for the next year (this is either in RE or another BS account). Beginning balance accounts are populated in the new year when the old year is closed.

Mark

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report
Copy link

Link copied!

Thanks guys. The retained earnings and balance sheet accounts all seem fine. The really strange thing is: the current fiscal year balances are correct. I ran the financial reconcile tool for the two historical years, and that seemed to change the beginning balances for the balance sheet accounts and retained earnings account.

Was this reply helpful? Yes No
Verified answer

Community Member on at

Like (0)

Report
Copy link

Link copied!

Thanks for your suggestions everyone, I ended up removing the transaction from the GL30000 table, and running the financial reconcile tool. Thousands of financial statements and trial balances later I'm confident we match what came out of their old system. We also ran the year end close process for the current year in their test environmen to check for any strange behavior and things seemed to work out well.

Was this reply helpful? Yes No
Abdo Khoury 14,855 on at

Like (0)

Report
Copy link

Link copied!

Dear

I hope you are well, I am faced with the same situation where i need to delete the transaction of Year 2013 (while keeping 2014 records which are correct) but i do have sub ledger transactions from AP , AR and SOP. If i follow your above suggestion by removing GL30000 then running financial reconcile as well as reconcile for AP , AR i should reach a clean conclusion , Right ?

I am still planning the best approach to do this , if i am missing anything please let me know,

your feedback is greatly appreciated.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report
Copy link

Link copied!

What version of GP are you using? If you are on the later (2013 R2) there's a utility available to do a fiscal year re-open.

My situation was a little different than yours... i made an error during the data conversion, so I was only dealing with GL history. If you have transactions in any of the subledgers I am not sure what will happen. If you're not on 2013 R2 I would encourage you to test this again and again.

Or upgrade to R2 :)

Was this reply helpful? Yes No