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 GP
http://support.microsoft.com/help/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 AAG30000

select 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 AAG30000

select distinct(OPENYEAR) from GL20000

select distinct(YEAR1)from AAG40000 order by YEAR1

select 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:  

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.  (This routine should only need to be run ONCE after you upgrade past GP 10.0, and then you should never have to run it again.  It was meant to be a one-time process. This utility will not fix duplicated years between AA tables or damaged data at a later time.)

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 is in the GL30000 history table. The AA open record with the old year needs to be moved to the AA history table to match the corresponding record in the GL history table.)

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:

1. Mark the setup option to include Analytical Accounting in the year-end close as follows:

   1. On the Microsoft Dynamics GP menu, point to Tools, point to Setup, point to Company, point to Analytical Accounting, and then click Options.

   2. 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.

2. Individually mark the dimensions to be included in year-end as follows:

   1. On the Cards menu, point to Financial, point to Analytical Accounting, and then click Transaction Dimension.

   2. In the Trx Dimension list, click the dimension that you want to include in the year-end close process.

   3. In the Year End Close area, click to select the Consolidate Balances during Year End close checkbox and then click Save.

   4. Repeat steps b and c for each dimension that you want to include in the year-end close process.

Note: If you are using MR, and the above checkboxes are not marked, then the beginning balance amounts may be missed if AA BBF dimension data was not created during the year-end close and you are reporting on AA data. 



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 GL00100

select count(*) from GL00105

select 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 - CHECK REVERSING GL/AA ENTRIES (GP2015/GP2016 ONLY)

There have been issues with GL Reversing entries posting to a historical year in each version, as noted below.  Run the script below against the company database.  Review any results as noted for each version.  If any assistance is needed, please open a support case and reference the quality issues. 

Run this script to review all Reversing entries that posted to a historical year. 

--------------------------------

Select distinct(a.JRNENTRY) from GL20000 a
join GL30000 b
on a.JRNENTRY = b.JRNENTRY
where a.SOURCDOC = 'GJ'
and a.TRXSORCE like 'GLREV%'
and b.TRXSORCE like 'GLTHS%'

----------------------------------

Review the results as outlined below for the version you are using:

Microsoft Dynamics GP 2016 (Quality issue #91834)

 Compare the records between the GL tables and AA tables for each JE# returned above, as results may vary depending on if P&L accounts were used, and if it was transaction-level or batch-level posted.  Manual updates needed may be: 

• Update SEQNUMBR in GL20000 table for the 'GLREV' entry to match SEQNUMBR in AAG30001 table. (If using MR, need to use the SEQNUMBR from the AA table in order for MR to read it.)

• Update ACTINDX in AAG30001 table for the "GLREV' entry to match ACTINDX in GL20000 table. (AA table incorrectly has the Retained Earnings account index on the reversing entry.)

• Verify sum of records in AAG30002 equal the sum of records in AAG30001 for the aaGLHdrID records for the JE.

Open a support case and reference quality issue #91834 if assistance is needed. 

UPDATE:  This issue was fixed in the January Hot Fix for GP 2016 (16.00.0675) and GP 2018 (18.00.0438). 

Microsoft Dynamics GP 2015 (Quality issue #88914)

Review the AA tables for each JE# returned above .  Manual updates needed may be:

• Verify sum of records in AAG30002 equal the sum of records in AAG30001 for the aaGLHdrID records for the JE.

• Review the AAG30000 and AAG40000 tables for each JE# returned.  Look for the records for the 'GLREV' entry to be in both the AAG30000 series tables. The AA records for the 'GLREV' entry should be in the AAG30000 tables only since the reversing entry is in the new year, and should not be in the AAG40000 series tables.  These duplicated records would cause MR reports to be overstated if reporting on AA.

Open a support case and reference quality issue #88914 if assistance is needed. 

UPDATE:  This issue was fixed in GP 2016 RTM.   

STEP 9 -  INCORRECT AA BBF's (****Known issue for GP 2016 only***)

**IMPORTANT NOTE FOR DYNAMICS GP 2016 USERS***

**You must be on GP dictionary 16.00.0675 or higher, (or AA dictionary 16.00.0645 or higher) before you close your Dynamics GP 2016 in order to have correct beginning balances brought forward**

There is a known quality issue #91502 when you close your GL year with AA.  If you have any GL accounts with a $0 balance and are rolling AA codes forward, the AA BBF's will be incorrect.  This is not an issue in GP 2015 or GP 2013.  

A fix has been included for this issue in the December patch update (KB 4056559) for Microsoft Dynamics GP 2016.  Even though the December release is referred to as the2017 Canadian Payroll Year-end Update, it should be installed by all U.S. customers that need this AA BBF fix that is included.  It is highly recommended that you install this December patch update before you close your GL if you are using AA and have GL accounts with a zero balance, for all installations (U.S., Canada, etc) that are using AA. 

Note that the Dynamics GP version of 16.00.0641 does not change between the 2017 US Year-End Update (November release/KB 4046341) and the 2017 Canadian Payroll Year-End Update (December release/KB 4056559). However the AA dictionary will update from 16.00.0552 to 16.00.0645. (Check in the Help | About Microsoft Dynamics GP | Additional | About Analytical Accounting.) You will need the AA code in the December release to resolve this AA/BBF issue. 

STEP 10 - 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:

888003 Year-End Closing procedures for General Ledger in Microsoft Dynamics GP

-----------------------------------------------------------------------------------------------
NOTE:  Year end close with AA on SQL 2019 fails 
If you are using GP 18.2 with SQL 2019 and AA loaded, the GL year-end close will fail with the message below: (Prior SQL versions work fine.  It only fails on SQL 2019.  This is fixed in the February 2020 Hot fix. Refer to the AA YE BLOG for more details.)

"Internal error: An expression services limit has been reached. Please look for potentially complex expressions in your query, and try to simplify them."  

------------------------------------------------------------------------------------------------

STEP 11- CHECK IF 'UNIT ACCOUNTS' CLEARED (GP 2013/GP 2015 only - #86400)

If you are using Microsoft Dynamics GP 2015 or Microsoft Dynamics GP 2013 and marked the checkbox on Unit Accounts to 'Clear Balance During Year-End Close', the records in the AAG30002 table may still incorrectly have values, and should be 0.00 to match the AAG30001 table.  (This issue was fixed in Microsoft Dynamics GP 2016.)

To make sure the Unit Account balances are correct in the AA tables, run the first script below against the company database after the year-end close process is run to make sure the BBF for the unit account is set to zero if it was marked to be cleared.  Use the second script to update any results.

select b.ACTINDX, c.aaGLHdrID, c.aaGLDistID, c.DEBITAMT, c.CRDTAMNT, c.ORDBTAMT, c.ORCRDAMT
from AAG30002 c inner join AAG30001 b
on b.aaGLHdrID = c.aaGLHdrID and
b.aaGLDistID = c.aaGLDistID
inner join GL00100 d on
b.ACTINDX = d.ACTINDX
where d.Clear_Balance = 1 and b.ACCTTYPE = 2 and b.SOURCDOC = 'BBF' 
and (c.DEBITAMT <> 0 or c.CRDTAMNT <> 0 or c.ORDBTAMT <> 0 or c.ORCRDAMT <> 0)

update c set c.DEBITAMT = 0, c.CRDTAMNT = 0, c.ORDBTAMT = 0, c.ORCRDAMT = 0 
from AAG30002 c inner join AAG30001 b
on b.aaGLHdrID = c.aaGLHdrID and
b.aaGLDistID = c.aaGLDistID
inner join GL00100 d on
b.ACTINDX = d.ACTINDX
where d.Clear_Balance = 1 and b.ACCTTYPE = 2 and b.SOURCDOC = 'BBF'
and (c.DEBITAMT <> 0 or c.CRDTAMNT <> 0 or c.ORDBTAMT <> 0 or c.ORCRDAMT <> 0)

STEP 12- 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.

MICROSOFT INTERNAL INFORMATION

Last updated date: 12/3/2021 - cw
Author: dspecht; rewritten 12/2/2012 by cwaswick, 9/19/2013 - added step 3 by kenhub/cwaswick.
Writer: lmuelle
Tech Reviewer: kriszree
Editor: v-andmck