Inactivating General Ledger Accounts In Great Plains From The SQL Backend

In most cases, any changes that need to be made to the corporate chart of accounts can be handled by the Accounting or Finance department within your organization.  Dynamics GP offers a tool for handling this, the Mass Modify Chart of Accounts window, which can be accessed via the main menu in GP by navigating to Cards>>Financial>>Mass Modify.

As shown below, the Mass Modify Chart of Accounts window allows the user to specify a particular range of accounts and apply a specific action to those accounts. These actions include options such as Copy, Move, Inactivate, Delete and Update.

gp_mass_modify_window

However, the Mass Modify Chart of Accounts tool tends to be a bit finicky.  Depending on the number of GL accounts that are being affected, the process for updating them can be slow and, in some cases, cause GP to lock up or crash altogether.  Though certain actions must be performed using this window, such as copying or moving a GL account, some actions can be more easily handled by scripting out the necessary changes and running them against the SQL back-end.

The most common action for mass modification, from my experience, tends to be inactivating general ledger accounts.  With a basic understanding of how your organization’s GL account segments are structured, this can be easily accomplished using the following scripts.

In the example above I am referencing ACTNUMBR_4, which in my case, corresponds to the cost center/department segment of the GL.  The first script can be used to view the records that will be updated. The second script will inactivate all GL accounts that contain the cost centers specified in the WHERE clause.

The additional WHERE clauses, which are commented out, can be used depending on how your company’s account segments are configured.

In addition to setting ACTIVE = 0, I am also setting ACCTENTR = 0.  This will prevent any entries into the GL account once it has been inactivated.

It’s worth noting that there are 6 account segments available within Dynamics GP.  The function of each segment can differ from company to company based on how their initial account formats were configured.  To verify how your accounts are configured navigate to Dynamics GP >>Tools>>Setup >>Company>> Account Format and view the Account Format Setup window as shown below.

gp_account_formats_window_view

As you can see ID 4 corresponds to the CostCenter as referenced in the scripts above and ID 6 is not even being used.  In my case, we tend to mass modify based on cost center as when a cost center becomes closed, all corresponding GL accounts should also be inactivated.  Handling this from the SQL back-end is much easier and far less time consuming than managing it using the Mass Modify Chart of Accounts tool within Dynamics GP.  It is also a relatively low risk update to the database. If any accounts were to be incorrectly inactivated, they can just as easily be reactivated without the need to restore the database.