Dynamics GP Reshaping Chart of Accounts

If you are working in accounting department in large company with several business entities installed and implemented in this Corporate ERP application, chart of accounts transformation might be a necessity.  It is often expected when your company adopts new standards, and that should be reflected in General Ledger module.  We will try to give you some highlights, which we are basing on our real consulting experience.  We do not recommend this change to be done in-house, rather we are encouraging you to share this information with your current consulting team to see if they are comfortable with the idea.  Let’s begin with Account concept overview in GP:

1. There are known facts about Account ID length – it can have up to ten segments and total length may not exceed sixty six characters (that could be uppercase letters or digits).  We are not sure about initial few versions of Great Plains as our consulting practice was launched in 1995.  But even those days, when GP was in ERP market active penetration some of the documents and their GL distribution tables were based on physical account ID.  However the trend in Great Plains reshaping with each new version (pretty challenging Dexterity programming project) was switch from Account ID to so-called Account Index.  This project was finished around 1998, and since then all the distribution tables reference account by Account Index (unique integer), while end user still enjoys the account segmentation in the application user interface

2. Accounts Segment Table introduction.  Around the year 2000 GP got new feature – separate table for each possible account Segment.  This is GL40200 table and you can look at its row in SQL Server Management Studio Query.  Table is very simple and it just has Segment Number, ID, and Name.  The idea behind is also simple, but powerful – when you are creating new account – the account description is created automatically for you with each segments listed with dash separator, for example: Corporate-Accounts Receivable-Wholesale.  Next table to review is Account Master, famous GL00100, where you can see all the account segment numbers listed, account index, main segment, account description and other fields.  And the third important table is Account Index Master (GL00105).  This table has Index itself, Account Number (in SQL this is combination of columns representing each segment, such as ACTNUMBR_1, etc.), plus Account Number String (this is the concatenated string, containing each account segment in sequence, separated by dash symbol).  Account Number String is unique index and it should be updated, when you are updating each account Segment in GL40200 and respectively in GL00100 tables

3. Account Change suggested procedure.  Of course you cannot do it in user interface, however it is legitimate routine for SQL programmer.  What is recommended – first install test server with Dynamics and each company database loaded from production backup (do not do this surgery on production without several test circles).  The procedure itself has three steps.  First – be sure that the new segments are created or one-to-one renamed in GL40200.  Second Step – be sure that you are legitimately renaming required segment number in GL00100 (for example if you are renaming the second segment, the field name is ACTNUMBR_2).  The third step is to rebuild GL00105 Account Index table (this routine we recommend to entrust to Account Number table cluster Check Links maintenance procedure, simply run this SQL statement against company DB: Delete GL00105 and then run Check Links on Account Master – it will recreate GL00105 table)

4. Additional Elegant Step.  You may also rename all the account descriptions in GL00100 account master table by reading segment names, separating them by dashes from GL40200 table.  We don’t have space in this publication to give you exact SQL Stored Procedure or Cursor, but it should be a nice piece of cake for experienced SQL DBA or Programmer in your IT department

5. Some comments on old version of Great Plains Dynamics Select.  If you are on the version where your database platform is not MS SQL Server, but Pervasive SQL 2000/Btrieve or Ctree (7.5, 7.0, 6.0, 5.5, 5.0, 4.0 or earlier), then theoretically you can do the same routine via ODBC compliance database access tool, such as Microsoft Access (with linked ODBC tables to Great Plains via Pervasive SQL ODBC driver), and we do not recommend you to launch this (pretty expensive) project on the historical version.  The cost of upgrade to the current version 2010/11.0 is comparable, where account number reshuffling project is much easier (MS SQL Server power, plus consulting resource availability, plus technical support directly from Microsoft Business Solutions in case if something happens)

6. FRx Financial Statements or Microsoft Management Reporter update.  Please, be sure that you have adequate account change project coordination with your FRx consultant, as all the reports (required change in Row Format, potential change in Reporting Tree and Column Layout, as there you can base your selection on Account Natural Segment or Full Account Length) should be reviewed in FRx (or Microsoft Management Reporter is you already deployed this new Financial Reporting tool for version 2010 or 10.0)

7. Dexterity Customization Code Review.  Some older Dex custom modules were coded with Account Segments logic (and without newly recommended Account Index logic).  If you have unique in-house programmed (or specially developed for your organization by independent Dexterity software developer) Dexterity module, please consider hiring technical consultant, who is experienced in Dexterity to review its code to be compliant to your new account structure plan

Leave a Reply