Mass Modify Account Deletion

(0) Share

Report

Posted on by Community Member

Hi All,

A mass modify account deletion was run, and for some reason, accounts that exist in the purchasing distribution history tables were removed, leaving those purchasing distribution lines with a NULL account index. Anyone know how this might have happened, or how we might be able to correct? Thanks in advance.

*This post is locked for comments

I have the same question (0)

All responses (20)

Answers (4)

Suggested answer

Bill Campbell 12 on at

Like (1)

Report

RE: Mass Modify Account Deletion

Trusting that prior to running the process you completed a backup of the data, the first thing I would suggest is restoring the data to the state that it was in proir to running the account deletion.

Before proceeding to run the process a second time, I would create a TEST database and then and only then would I consider running or testing the process a second time.

Was this reply helpful? Yes No
Verified answer

Redbeard 12,931 on at

Like (0)

Report

RE: Mass Modify Account Deletion

There is a Knowledge Base Article, which explains the requirements for GL Accounts to be deleted from Dynamics GP:

support.microsoft.com/.../933642

And in a prior post, Richard Wheeler mentioned it was a good idea to cross reference Modular Posting setups to make sure you didn't remove accounts set as transaction defaults, but seldom used.

In the requirements for Deleting an Account, there is no reference to sub-ledger (purchasing, inventory and sales) history / distributions. So, while Dynamics GP checks for Account History prior to deletion, it does not check the sub-ledgers. So, Remove history was likely run against your GL History tables, or Dynamics GP was never configured to post detail to history.

I would recommend restoring an older copy of your company database as a test company, and see if you can identify the accounts removed, and recreate them. The Account indexes in the purchasing tables should map to GL Accounts in the GL00105 table of the historical company database. You will need to make sure the Account Index of the recreated accounts match the desired account numbers, so re-entering these accounts by hand is likely not an option.

Was this reply helpful? Yes No
Bill Campbell 12 on at

Like (0)

Report

RE: Mass Modify Account Deletion

Harry, just a quick question - you are saying that to 'delete an account' the requirement for no history only applies to the GL and not the Puchasing History? Is that correct?

Was this reply helpful? Yes No
Suggested answer

Redbeard 12,931 on at

Like (0)

Report

RE: Mass Modify Account Deletion

Bill,

I guess I am saying it, but don't shoot the messenger. Here is an overview of KB 933642:

The account cannot have a balance.

Fix: Transactions > Financial Clearing

The account cannot have any current activity or any year-to-date transactions.

Fix: Routines > Financial > Year End Close

The account cannot have any historical data, such as a transaction history or any transactions that occur in years that are closed.

Fix: Utilities > Financial > Remove History; this will create an out-of-balance situation

The account cannot have any multicurrency data in the MC00201 table or in the MC30001 table.

Fix: Routines > Financial > Year End Close & Utilities > Financial > Remove History; Delete MC00201 where ACTINDX = 'Index_of_Acct_to_Delete' & Delete MC0030001 where ACTINDX = 'Index_of_Acct_to_Delete'

The account cannot be contained in a quick journal or be in a saved batch.

Fix: Tools > Setup > Financial > Quick Journal & Tools > Routines > Master Posting > Post Batches

The account may not be included in an allocation account either as a breakdown account or as a distribution account.

Fix: Cards > Financial > Fixed Allocation and Variable Allocation

Note: You can delete variable allocation accounts and fixed allocation accounts at any time because these accounts do not hold a balance. However, if any transactions contain a balance, those transactions will have a blank distribution account. If you add the allocation accounts again, the accounts do not reestablish the link between the transactions that contain a balance. The link is not reestablished because the accounts are given a new account index value.

The note above is very instructive... The implication, backed up by the post above is any accounts with references/balances in areas not validated prior to the accounts' removal will result in blank distribution accounts, which the links cannot be easily reestablished for, because re-created accounts will not have the same index numbers.

Was this reply helpful? Yes No
Suggested answer

Bill Campbell 12 on at

Like (0)

Report

RE: Mass Modify Account Deletion

So the NOTE says it all - if the account in the SUB has a balance, it will be left blank. And as you noted, it is hard if not impossible to place all the blanks.

So would you recommend a restore of the production database at this time - or would you still go about the process of restoring to a test and then running comparision scripts to repopulate the data?

I am not sure which of the two I like the least.

Was this reply helpful? Yes No
Verified answer

Redbeard 12,931 on at

Like (1)

Report

RE: Mass Modify Account Deletion

Bill -

I would recommend a restore of the production database, if this were possible. My assumption was this was not, based on the post. Should this prove to be the case, it is more support for modeling any significant change to data in a test company, prior to implementing it in the live company.

If I were in this situation, without a back up, I would create a test company, reference the Knowledge Base article at this link support.microsoft.com/.../866332 and insert the records for the now missing accounts from a copy of the database prior to the execution of the Mass Modify. Once I was sure my queries produced the desired outcome, I would then implement the fix in my live database.

Was this reply helpful? Yes No
Bill Campbell 12 on at

Like (0)

Report

RE: Mass Modify Account Deletion

Harry, I did not get the feeling that it was not recoverable, but we have not heard from the original poster -

As to your recovery - that will require two (2) test databases - one with the history in place and one with the accounts missing. But if the process is possible, that would at least allow them to correct the problem.

Hope we learn the outcome of this case.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: Mass Modify Account Deletion

Hi All,

Thanks so much for the responses. This mass modify was done without my knowledge. I would have of course created a backup and verified the results in a test company, but that ship has sailed....

I will follow the suggestions on this post and let everyone know what the outcome is. Thanks again!!

Was this reply helpful? Yes No
Bill Campbell 12 on at

Like (0)

Report

RE: Mass Modify Account Deletion

Sorry to hear that - this is going to work out for you - keep the community posted.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: Mass Modify Account Deletion

Hi Harry,

I've got some test companies setup, and restored using the appropriate backups, and am starting to follow the above knowledgebase article, however the transfer fails during validation, saying that I can't insert data into the read only field DEX_ROW_ID. I have 'Enable identity insert' unchecked. It doesn't say anything like this in the KB article, but should I be removing the mapping for DEX_ROW_ID for all of the tables I'm transferring data for?

Was this reply helpful? Yes No