When the year-end close process is run for General Ledger in Microsoft Dynamics GP, it automatically moves the Analytical Accounting transactions from the AAG30000 historical tables to the AAG40000 series tables. (There is no separate closing procedure that must be run in Analytical Accounting.) You may select which dimensions that you want to be consolidated during the year-end process. In Analytical Accounting, Balance Brought Forward entries are created in the AAG30000 tables for those dimensions that were marked to be included in the year-end process, and then the detail is moved to the AAG40000 series tables. Note
If you are using FRx reporting, you should be on FRx 6.7 SP12 (version 6.7.12008) or higher. If your FRx version is lower than 6.7.12008, then you must first apply hotfix 962862 in order for Microsoft FRx to be able to read Analytical Accounting historical data in the AAG40000 series tables.
STEP 1: DETERMINE IF FINANCIAL REPORTS USE AA TABLES:
Before you close GL or do any steps in this article, run your financial reports that you normally run to balance to the GL Trial Balance. Based on what you found, follow the appropriate method:
-METHOD 1 - IF CORRECT
: If your Financial reports are correct and match GL, then you may proceed with steps 2-8 in this article
, which still must be completed so you do not get any AA errors during the GL year end close process.
Note: If you use a reporting system that only reads directly from GL tables (such as the Legacy provider in Management Reporter when reading from a GL company or other reporting tool)] for your Financial Reports, then you may continue on to Step 2 since AA data does not affect your Financial reporting.
-METHOD 2 - IF NOT CORRECT
: However, if your Financial reports are not correct, it is mostly likely due to the AA tables being used and the AA data does not match GL data. To verify the AA data, you must first run through the SQL scripts provided in KB 2910626 in addition to the rest of the steps in this article.
Note: Data Mart in Management Reporter reads from AA (and GL) tables, or the Legacy provider used with Management Reporter may read from an AA company.Steps for Method 2:
a.) First, run all the scripts in KB 2910626 to verify AA data with GL data.
Financial Reports from Management Reporter do not match the General Ledger Trial Balance Reports in Microsoft Dynamics GPhttp://support.microsoft.com/kb/2910626
b.) When you have completed the steps in KB 2910626, then return back to this KB and continue on with the rest of the steps listed below. (Note that step 2 and step 4 are also in KB 2910626, but we recommend to double-check this again, as they should not return any results if you already fixed this data.)
STEP 2: VERIFY AA DATA FOR OVERLAPPING YEARS
Run these scripts to make sure you do not have overlapping years in the AAG30000 Open table with the AAG40000 historical table. Each distinct year should only exist in one table or the other, but not both.
select distinct(YEAR1) from AAG30000select distinct(YEAR1) from AAG40000
-If you find overlapping years in both tables, then it is recommended to open a support incident for assistance. The support case is chargeable since this issue is usually created due to importing records. Please note that if data-fixing is needed, this may need to be referred to consulting, which would be a billable expense to you.
STEP 3: VERIFY THE YEARS BETWEEN OPEN/HISTORICAL TABLES MATCH BETWEEN AA/GL:
Next, make sure the years are in the AA tables are in the same open or closed years as your GL tables. The AAG30000 and GL20000 open tables should have the same years. And the AAG40000 and GL30000 historical tables should contain the same closed years.
select distinct(YEAR1) from AAG30000select distinct(OPENYEAR) from GL20000select distinct(YEAR1)from AAG40000 order by YEAR1select distinct(HSTYEAR) from GL30000 order by HSTYEAR
-If you find years in the AAG30000 open table PRIOR to the year you are closing, you must also do STEPS TO MOVE DATA TO HISTORY
below as well to move the historical year data to history. The AAG30000 table should only have data for the years that are currently open years in GL. If you attempt to close the year in GL, you will be prompted with this message:
You must run the consolidate transactions and transfer detail to history utility for closed years to recreate the Analytical Accounting Balances Brought Forward.
So use the scripts above to determine if you need to run the STEPS TO MOVE DATA TO HISTORY
, so you can prevent the message above from happening during the GL year-end close process.STEPS TO MOVE DATA TO HISTORY:
The first time you close GL on a version higher than GP 10.0 SP2 or higher (with AA activated), you will be prompted to move AA data to history before the system will allow you to close the GL year. The system will verify that the AA data is in the corresponding open/historical series of AA tables, as the GL data sits in the open/historical tables in GL. If this is not the case, you will receive a message to run the Move to History Utility for AA before you can proceed with the GL Year End close.
Remember, if you have not closed your GL year (with AA activated) after installing a service pack later than SP2 for version 10.0, or upgrading to GP 2010, you may receive a message stating "You must consolidate transactions and transfer detail to history utility to close the year". Code was added to the closing process that will compare the years in the AA open tables against the historical years in the Company Fiscal Period Setup. If there is AA data in the AAG3000X series of tables for a historical year, you will receive the error. Follow these steps to consolidate those years:
1.) On the Microsoft Dynamics GP
menu, point to Tools,
point to Utilities
, point to Financial
, point to Analytical Accounting
, and then click Move Data to History.
2.) The oldest year will default in that the system found in the open AAG3000x tables. You will only be able to move one year at a time.
3.) Select the appropriate option: Transfer transaction detail to history
– This option will move the AA detail records from open to history tables and no BBF entries will be created. You must ensure there are no BBF entries in the AA tables otherwise you will not be able to select this option. This option just moves the records from the AAG30000 tables to the AAG40000 tables. Consolidate transactions and transfer detail to history
– This option will move the AA detail records from open to history tables, and create BBF entries. However, you must have the options mentioned previously selected in order for BBF entries to be created. This option will consolidate the balances of all the transaction dimension codes in the closed year (that are marked to be consolidated) and transfer the AA information to the history tables.
Note The consolidated balances are brought forward to the new year. BBF entries are created from the closed years.Print transfer preview report only
– This will allow you to view the transactions that will be moved without actually moving the data. The preview report displays the consolidations that will be made.
Note This option does not change data.
4.) Click OK.
5.) Repeat this process for each 'historical' year. (Where the year is in the AAG30000 open table but in the GL30000 history table. The year needs to be moved to the AA history table to match.)
Note: If you rerun the distinct year scripts in 'STEP 3' above again, you should get the distinct years to match between open AA and GL tables, and historical AA and GL tables.
STEP 4: CHECK AA TABLES FOR OVERLAPPING HEADER ID'S
Run this script against the company database see if the same header ID's also exist between the tables:
select * from AAG30000 where aaGLHdrId in (select aagLHDrId from AAG40000)
- If you find duplicate header ID's both tables, then it is recommended to open a support incident for assistance. The support case is chargeable. Please note that if data-fixing is needed, this may need to be referred to consulting, which would be a billable expense to you.
This would happen if you restored an older Dynamics database over the top of the current Dynamics database, and so the next available numbers stored in the AAG00102 table in the Dynamics database are set back. GP continues to increment from these values, even though they may already have been used and would result in the same aaGLHdrID value getting used for different YEAR1 values.
STEP 5: UPDATE AACOPYSTATUS VALUES
Next check for an incorrect aacopystatus value in the AAG40001 table. Run this script:
select count(*) from AAG40001 where aaCopyStatus<>8
If the script above returns results you will want to update the aaCopyStatus to '8' before running the GL Year Close: (The value of '8' is a value that the year-end close process will accept.)
update AAG40001 set aaCopyStatus=8
STEP 6: REVIEW SETUP FOR DIMENSIONS TO BE INCLUDED IN YEAR-END
Use the two steps below to turn on the Company option to include AA dimensions in the year-end close, and then to mark the individual dimensions that you want to be included in the year-end close. This will result in entries in the AAG30003 table with the same aaGLHdrID as the BBF entries in the AAG30000/AAG30001/AAG30002 tables. This is a two step process as follows:
If you have not closed General Ledger yet, follow these steps to make sure the dimension is marked correctly to be included in the close process:
- Mark the setup option to include Analytical Accounting in the year-end close as follows:
- On the Microsoft Dynamics GP menu, point to Tools, point to Setup, point to Company, point to Analytical Accounting, and then click Options.
- Click to select the Include in Year End Close check box, and then click OK.
Note This option is just to enable the functionality to create Balance Brought Forward entries on the dimensions. The Analytical Accounting data will still move to the AAG40000 series tables when General Ledger is closed regardless of whether this option is marked.
- Individually mark the dimensions to be included in year-end as follows:
- On the Cards menu, point to Financial, point to Analytical Accounting, and then click Transaction Dimension.
- In the Trx Dimension list, click the dimension that you want to include in the year-end close process.
- In the Year End Close area, click to select the Include in Year End Close check box, and then click Save.
- Repeat steps b and c for each dimension that you want to include in the year-end close process.
STEP 7 - VERIFY AA ACCOUNT MASTER
It is always a good idea to verify that the AA Account Master table (AAG00200) matches the GL Account Master table (GL00100) before you process a year-end close. If accounts are missing, it would cause the BBF entries in AA to be incorrect. Run the scripts below against the company database to verify that the GL Account Master, GL Account Index Master, and AA Account Master tables all have the same number of records:
-------------------------------------------------select count(*) from GL00100select count(*) from GL00105select count(*) from AAG00200-------------------------------------------------
• If the AA Account Master table has LESS records than the GL00100 table, use the script below to insert the missing GL accounts:
-------------------------------------------------insert into aag00200 ACTINDX, aaAcctClassID,aaChangeDate,aaChangeTime)select ACTINDX, 0, convert(char(10),getdate(),111), convert(char(12),getdate(),114)from GL00100 where ACTINDX not in (select ACTINDX from aag00200)-------------------------------------------------
• If the AA Account Master table has MORE records than the GL00100 table, use the script below to remove the extra records:
-------------------------------------------------Delete AAG00200 where ACTINDX not in (Select ACTINDX from GL00100)-------------------------------------------------
• If the GL00105 table doesn't match, refer to KB 855963
for steps on How to Recreate the Account Master Index table (GL00105).
STEP 8 - RUN TEST CLOSE
Always make a current backup before starting the GL year-end close process. It is recommended to test running the GL year-end close in a test company first to ensure you do not get any errors. The GL Year-end close process is what actually creates the balance brought forward journal entries (BBF's) and moves the records for the year that you are closing in both General Ledger and Analytical Accounting tables. BBF entries are created in both GL and AA tables. Refer to the process outlined in KB 888003 for year-end closing procedures for General Ledger.
For more information, click the following article number to view the article in the Microsoft Knowledge Base:
Year-End Closing procedures for General Ledger in Microsoft Dynamics GP
STEP 9- VERIFY BALANCE SHEET REPORTS
It is recommended to compare the Balance Sheet report in Management Reporter to the General Ledger Trial Balance report from Microsoft Dynamics GP, to verify that the account balances brought forward to the new year are correct. If these balances do not match, restore to your backup and contact Microsoft Dynamics GP Support to open a support incident for additional assistance.