Solving the Historical Journal Entry Problem in Dynamics GP
Microsoft Dynamics GP General Ledger allows transactions to be posted into the more recent closed Historical years but not to earlier ones. This restriction is acceptable for the many companies that only make Adjusting Entries into the prior year GL. However companies that are audited are sometimes required to make Adjusting Entries into multiple previous years.
What needs to be done: Posting transactions into a historical year requires multiple records to be updated. After an Adjusting Entry in a specific month/year. Balance Sheet account balances need to be adjusted by the same amount for all future periods in all future years. Income Statement Year To Date balances need to be adjusted through the end of the year in which the Adjusting Entry was made. The final step is to adjust the Year End Retained Earnings account (either single or Divisional) for the Adjustment year and all future periods/years. The standard GP user interface does not allow this, but there are three methods by which this can be done.
Method 1: Engage Microsoft Professional Services.
Microsoft provides a billable service to open multiple years and companies (no tool is available). Your database is sent to MBS Professional Services then reopen as many years as are necessary and then send the modified database back to you. This may be the solution in some situations, but is does the GL offline for 24 hours or more and will require you to make separate arrangements to ensure that ongoing batches from subsidiary modules are not lost.
Once the Historic year is open, make the adjusting entry, close the year, make any entry necessary in the following year, then repeat this process in each year and/or company until the current year is reopened. This is cumbersome, especially if multiple companies are involved. (One Alba client had 12 companies which required adjusting entries to the seventh prior year. This would have required 84 Year Reopen processes followed by 84 Year End Close processes!).
Method 2: Run SQL scripts directly on the General Ledger database.
The second alternative is to run SQL scripts on the General Ledger database to open each year and then post adjusting Journal Entries using the standard windowy. We recommend that this method should only be used someone with strong GP development experience. In illustration of this: the scripts will need to move all historical year related transactions from GL30000 (General Ledger transaction history) into GL20000 (General Ledger open year transaction). Then remove closing year related transaction(s) from GL20000 (these could be filtered by the Journal Type code). After this update Historical Year summaries, change year status (now it is back as Open), etc. This method is technically possible but requires an advanced understanding of the Dynamics GL Table structure and what happens during the GL closing process.
Method 3: Alba Spectrum's GP Historical Journal Entry Module.
Alba Spectrum has created a solution to deal with this situation simply and effectively with no special skill requires. The Dynamics GP Historical Journal Entry (HJE) Module is programmed directly in Great Plains Dexterity and appears seamlessly within the GP user interface. Once installed and configured, the process is transparent to the end user: you open the HJE window, select a Historical Year and then create ‘normal' Journal Entries. The Posting process processes all the table updates needed to create correct balances in subsequent years. This includes Divisional Close data. Special reports have been created to document this process for each Historical Journal Entry made.
Installation is documented in the user manual. It consists of installing the Alba Dexterity ‘cnk' file and updating the workstation Dynamics.SET file settings. Tables are created using the standard GP Table utility.
Security and Access: The module requires access to the Historical Journal Entry window and to the associated reports. Instructions are included for defining Roles and Tasks with GP 10.0 and GP 2010 and for the legacy security system in versions of Dynamics GP prior to 10.0.
Related products by Alba Spectrum:
Balance Brought Forward - this is a Dynamics GP utility which displays opening balances in the current year before the prior year is closed. Please visit http://www.albaspectrum.com/V2.0/Productsfinancials.aspx for more information
About Alba Spectrum:
Alba Spectrum Corporation (http://www.albaspectrum.com |1-866-528-0577 | sales@albaspectrum.com) is a Gold-Certified Microsoft Dynamics Partner specializing in Microsoft Dynamics GP Customization and Integration, E-Commerce and Warehouse Management Systems (WMS). Alba Spectrum is based in Chicago, Illinois, with additional offices in Irvine, California and Richmond, Texas.
What needs to be done: Posting transactions into a historical year requires multiple records to be updated. After an Adjusting Entry in a specific month/year. Balance Sheet account balances need to be adjusted by the same amount for all future periods in all future years. Income Statement Year To Date balances need to be adjusted through the end of the year in which the Adjusting Entry was made. The final step is to adjust the Year End Retained Earnings account (either single or Divisional) for the Adjustment year and all future periods/years. The standard GP user interface does not allow this, but there are three methods by which this can be done.
Method 1: Engage Microsoft Professional Services.
Microsoft provides a billable service to open multiple years and companies (no tool is available). Your database is sent to MBS Professional Services then reopen as many years as are necessary and then send the modified database back to you. This may be the solution in some situations, but is does the GL offline for 24 hours or more and will require you to make separate arrangements to ensure that ongoing batches from subsidiary modules are not lost.
Once the Historic year is open, make the adjusting entry, close the year, make any entry necessary in the following year, then repeat this process in each year and/or company until the current year is reopened. This is cumbersome, especially if multiple companies are involved. (One Alba client had 12 companies which required adjusting entries to the seventh prior year. This would have required 84 Year Reopen processes followed by 84 Year End Close processes!).
Method 2: Run SQL scripts directly on the General Ledger database.
The second alternative is to run SQL scripts on the General Ledger database to open each year and then post adjusting Journal Entries using the standard windowy. We recommend that this method should only be used someone with strong GP development experience. In illustration of this: the scripts will need to move all historical year related transactions from GL30000 (General Ledger transaction history) into GL20000 (General Ledger open year transaction). Then remove closing year related transaction(s) from GL20000 (these could be filtered by the Journal Type code). After this update Historical Year summaries, change year status (now it is back as Open), etc. This method is technically possible but requires an advanced understanding of the Dynamics GL Table structure and what happens during the GL closing process.
Method 3: Alba Spectrum's GP Historical Journal Entry Module.
Alba Spectrum has created a solution to deal with this situation simply and effectively with no special skill requires. The Dynamics GP Historical Journal Entry (HJE) Module is programmed directly in Great Plains Dexterity and appears seamlessly within the GP user interface. Once installed and configured, the process is transparent to the end user: you open the HJE window, select a Historical Year and then create ‘normal' Journal Entries. The Posting process processes all the table updates needed to create correct balances in subsequent years. This includes Divisional Close data. Special reports have been created to document this process for each Historical Journal Entry made.
Installation is documented in the user manual. It consists of installing the Alba Dexterity ‘cnk' file and updating the workstation Dynamics.SET file settings. Tables are created using the standard GP Table utility.
Security and Access: The module requires access to the Historical Journal Entry window and to the associated reports. Instructions are included for defining Roles and Tasks with GP 10.0 and GP 2010 and for the legacy security system in versions of Dynamics GP prior to 10.0.
Related products by Alba Spectrum:
Balance Brought Forward - this is a Dynamics GP utility which displays opening balances in the current year before the prior year is closed. Please visit http://www.albaspectrum.com/V2.0/Productsfinancials.aspx for more information
About Alba Spectrum:
Alba Spectrum Corporation (http://www.albaspectrum.com |1-866-528-0577 | sales@albaspectrum.com) is a Gold-Certified Microsoft Dynamics Partner specializing in Microsoft Dynamics GP Customization and Integration, E-Commerce and Warehouse Management Systems (WMS). Alba Spectrum is based in Chicago, Illinois, with additional offices in Irvine, California and Richmond, Texas.
