- Management Reporter with Data Mart will read from AA tables if AA has been activated. This is by design in versions up to CU9.
- Management Reporter with Legacy provider allows you to choose if you want to pull from an AA company or a GL company.
There are several reasons that the AA data may not match the GL data or have issues, including:
- The Analytical Accounting module has not been installed on all workstations, where users are keying/posting transactions. Therefore, the record exists in GL, but not AA.
- Users are disabling the Analytical Accounting module if they received any error referencing 'aa', so AA tables are not updated.
- Records are being imported directly into GL tables, and not AA tables. Or users are disabling AA if they receive any 'aa' errors during the import process.
- Users are closing the GL year on a workstation where AA has not been installed. As a result the AA records are not moved to history and no Balance Brought Forward journal entry is created in the AA tables.
- Management Reporter may not read the AA record if the currency ID differs between the AA record and the corresponding record in GL, which could be caused by importing records, activating Multicurrency, or any quality issues with currency ID's that have since been fixed.
- The Dynamics database was restored over itself, causing the 'Next Available Number' (in the AAG00102 table) to be set back to a value that has already been used. This system will increment off of this value for the next header ID value as it inserts new records into the table, and so it can duplicates if it is inserting in a heard ID value that has already been used.
- If you upgrade to Microsoft Dynamics GP 2013 and set a functional currency ID, but are not registered for Multicurrency, or you do not run check links as instructed after setting up the functional currency.
- Posting interruptions
If you need assistance to correct any of the results returned from the scripts below, please open a support incident using the categories below to reach the correct Support Engineer Team. Please include the information as noted within each step.
- To open a support incident online, use this link: https://mbs.microsoft.com/support/newstart.aspx
- Use the Support Topic: Financial - Analytical Accounting, and the Sub-Topic: Data Consistency Issues
- To reach the Message Center to open a support incident, call 1-888-477-7877. Please provide as many details as you can.
- Please note that the support incident may be time consuming so please allow ample time to work with the Engineer.
- The support incident will be chargeable unless the only cause is determined to be a known quality issue in Microsoft Dynamics GP. If multiple issues are addressed, the case will remain chargeable.
- The scope of the support incident will include fixing all the data returned by the SQL scripts below, within the guidelines of what we can do in a regular support case. If additional assistance is needed to find the 'root-cause', this will require a new support incident to be opened. Some of the causes are common as explained in the CAUSE section above. However, for example, if you determine a currency ID difference exists on records that were imported into Microsoft Dynamics GP, this would require a new support incident to be opened to work with the eConnect/Integration Manager Support Team. The engineer maintains the discretion to advise when a new support incident is needed or when it may become a consulting engagement.
- ONE support case can be opened initially, with the results of all of the scripts from steps 1-7 below. The Engineer will work with you in ONE company, and you will be expected to correct the other companies on your own, using the same steps as the Engineer provided you for the first company. Additional assistance with multiple companies may require a new case and is up to the Engineer's discretion.
STEPS:Please copy/download the SQL scripts below, and execute against the company database in SQL Server Management Studio. Data returned by any of these SQL scripts below will cause Financial Reports in MR-Data Mart not to balance with GL Trial Balance reports.
***Please make a current backup of the company database before running any scripts that will update data.***
See KB #871973 -- Set up a test company that has a copy of live company data by using SQL Server 7.0, SQL Server 2000, SQL Server 2005, SQL Server 2008, or SQL Server 2012
STEP 1 - DISTINCT YEARS
Please run the scripts below to verify that the years are distinct in each table and do not overlap with each other, and also are consistent with the years in the GL tables. If you find the same year listed in both open and history tables or scrambled between the tables or not consistent with the years in the GL tables, please open a support case for further assistance. Please include the results of all scripts.
select distinct(YEAR1) from AAG30000 order by YEAR1
select distinct(YEAR1) from AAG40000 order by YEAR1
select distinct(OPENYEAR) from GL20000 order by OPENYEAR
select distinct(HSTYEAR) from GL30000 order by HSTYEAR
STEP 2 - FIND CURRENCY ID DISCREPANCIESPlease download the AA_FindCurrencyDiscrepancy.sql script to identify transactions where the Currency ID does not match between GL and AA tables in both open and history:
*If you do not use Multicurrency, or just use only one currency ID, you can simply update the CURNCYID and CURRNIDX fields directly in the AA (AAG30001/AAG40002) or GL (GL20000/GL30000) tables. Test to make sure the currency ID is matching on recently keyed transactions to ensure there is not an ongoing issue. Please open a support incident if you need assistance to update the CURNCYID directly in the SQL table, or if you are able to recreate this on a current transaction.
*If Multicurrency is being used, please open a support incident and include the following:
- Provide the results of the script in text format. We need to know which table had the currency ID and which table did not. And are these older or newer transactions? If older transactions and you just installed Multicurrency Management, did you run check links on Multicurrency Setup? Check links should go back and update the currency ID on all existing transactions.
- What version of Microsoft Dynamics GP are you using?
- Where did the transactions come from? Test to see if this is an ongoing issue. Verify if Multicurrency Management is marked in the Registration window (under Microsoft Dynamics GP, point to Tools, point to Setup, point to System, and click on Registration.)
- What is your functional currency? How many currency ID's do you use?
STEP 3 - FIND UNMATCHED DISTRIBUTIONS
Please download the AA_FindUnmatchedDists.sql script to identify if any detailed data does not match between the AA and GL tables. If any results are returned, please open a support case for further assistance. Please attach the file of results in text format. These problem records are usually due to importing issues, or users working on a workstation where AA was disabled or not installed.
NOTE: This script should not be used for versions lower than Microsoft Dynamics GP 10.0 SP2, or where you have not yet completed a GL year-end close on a version higher than that (ie. where pre-SP2 AA data has not yet been moved to history at all).
NOTE: Please be aware that 'result totals' may show in-between the sections and are misleading. These result totals are a factor of how the script was written and can be ignored. You only need to be concerned with 'result totals' that are immediately following a 'section' and returned actual data.
STEP 4 - FIND DUPLICATE AA CODE ID'SPlease download the AA_FindDupaaTrxDimCodeID_AAG00401.sql script to see if any AA code ID's are duplicated (due to being imported). If any results are returned, please open a support case for further assistance. Please attach the results of the script in text format, and also the contents of the AAG00401 table. We can provide you the steps to resolve this, but having Microsoft do this for you may be a consulting expense. This issue is usually caused by importing, or by restoring an older copy of the Dynamics database over itself.
STEP 5 - FIND DUPLICATE AA HEADER ID'S
Please execute the below script to determine if you have header ID's duplicated between the AA open and historical tables. We can provide the steps to resolve this, but having Microsoft do this for you may be a consulting expense. This issue is usually caused by importing, or by restoring an older copy of the Dynamics database over itself, or a posting interruption.
select aaGLHdrID from AAG30000 where aaGLHdrId in (select aagLHDrId from AAG40000)
select a.aaGLHdrID, a.JRNENTRY as OPEN_JE#,a.YEAR1 as 'OPEN_YEAR1',
b.JRNENTRY as 'HIST_JE#', b.YEAR1 as 'HIST_YEAR1' From AAG30000 a (nolock)
join AAG40000 b (nolock) on a.aaGLHdrID = b.aaGLHdrID
where a.JRNENTRY <> b.JRNENTRY
- Results of the script in text format.
- Research the records and advise if they are the exact same record, or if they are different records, but just have the same Header ID only.
STEP 6 - FIND MISMATCHED SEQUENCE NUMBERS (For P&L Entries)Download and run the AA_FindYEC_RetainedEarnings.sql script to look for records where the Sequence Numbers do not match between AA and GL between open and history tables for the Retained Earnings journal entry (SOURCDOC ='P&L'). If any results are returned, please open a support case and include the results of the script in text format. (Note, if you had any results in Step 3 above for the 'GLSEQNMBR' sections, we will want to have you fix those first and then run this script again for step 6, so we don't need your results now for step 6 if you had results in Step 3.)
NOTE: It is possible to get results with this script above if you currently have P&L entries with multiple distribution lines with the same ACTINDX, and the data is actually valid. You should compare the SEQNUMBR between the AAG30001 and GL20000 (or AAG40001 and GL30000) for your results returned, and if the SEQNUMBR matches, then you may ignore the MR validation errors for the SEQNUMBR and proceed.
STEP 7 - FIND MISSING BBF ENTRIESIf you find that your Financial reports are off by the amount of the beginning balances, the Balance Brought Forward (BBF) entries may be missing from the AA tables. Please run the scripts below to determine if the BBF entries in GL are missing from AA tables. This is caused by the user closing the GL year on a workstation where AA was disabled or not installed. For Microsoft Dynamics GP 2013 SP2 and prior versions, you will need to have a consulting service opened to reopen the GL year. If you are Microsoft Dynamics GP 2013 R2 or later versions, you can use the "Reverse Historical Year' checkbox in the GL year-end closing routine to reopen the year, and then reclose it again on a machine where the AA code is installed, so the BBF's are created in both the AA and GL tables.
PRINT 'OPEN year missing BBF'Select distinct (JRNENTRY) from GL20000 where JRNENTRY not in (select JRNENTRY from AAG30000)and SOURCDOC in ('BBF','P/L')
PRINT 'HISTORY year missing BBF'Select distinct (JRNENTRY) from GL30000 where JRNENTRY not in (select JRNENTRY from AAG40000)and SOURCDOC in ('BBF','P/L')
STEP 8 - KNOWN ISSUE FOR AA BUDGETS (in GP 2010/GP2013 only)
Select * from AAG00902 where aaLvlCodeString <> '' and aaLevel = 0
If the above script returns any results, you can resolve the issue by running this script against the company database:
update AAG00902 set aaLvlCodeString = '' where aaLevel = 0
STEP 9 - AAG30002/AAG40002 sum totals do not match AAG30001/AAG40001
The sum of the allocated records in the AAG30002/AAG40002 tables should match the sum of the amounts in the AAG30001/AAG40001 distribution tables per transaction. If the amounts are allocated to several AA codes, you can have a different number of records between these tables, but the important part is that they match in 'sum' total per transaction. MR reports will not read the records if the sum totals do not match between these tables for each transaction. Download the 'find and compare' scripts for the open and historical years below and execute against the company database.
(Note, if you had any results in Step 3 above for the 'AAG30002/AAG40002' sections, we will want to have you fix those first and then run this script again for step 9, so we don't need your results now for step 9 if you had results in Step 3.)
Open year AA tables:
Historical year AA tables:
If any results are returned, save them to an .rpt report and open a support case for further assistance. Attach the results for review.
STEP 10 - DEBITS/CREDITS ON SAME DISTRIBUTION LINE
MR is not able to read records where both the debit and credit fields are populated on the same distribution line. Known causes may be 'importing' in the records in that condition, or having used the Standard Cost Utility in Inventory with an override. You can run the SQL query scripts below against the company database to help identify these records. To fix the records, you will need to update the record directly in the GL/AA SQL tables to 'net' the amount on either the debit or credit field, as only one of these fields should be populated. It is suggested to fix each record manually one by one using the Dex_Row_Id. (If you need scripts to do this in mass, please consult your Microsoft Partner for assistance.) Use the scripts below to find the problem records:
select JRNENTRY,CRDTAMNT,DEBITAMT,ORCRDAMT,ORDBTAMT,* from GL10001 where (CRDTAMNT<>0 and DEBITAMT<>0) or (ORCRDAMT<>0 and ORDBTAMT<>0) select JRNENTRY,CRDTAMNT,DEBITAMT,ORCRDAMT,ORDBTAMT,* from GL20000 where (CRDTAMNT<>0 and DEBITAMT<>0) or (ORCRDAMT<>0 and ORDBTAMT<>0) select JRNENTRY,CRDTAMNT,DEBITAMT,ORCRDAMT,ORDBTAMT,* from GL30000 where (CRDTAMNT<>0 and DEBITAMT<>0) or (ORCRDAMT<>0 and ORDBTAMT<>0)
--if using AA, also compare the AA tables:select AAG0.JRNENTRY from AAG30001 AAG1join AAG30000 AAG0 on AAG0.aaGLHdrID = AAG1.aaGLHdrIDwhere ((AAG1.DEBITAMT <> 0 and AAG1.CRDTAMNT <> 0) or (AAG1.ORDBTAMT <> 0 and AAG1.ORCRDAMT <> 0)) and AAG1.SOURCDOC not in ('BBF','P/L')
select AAG0.JRNENTRY from AAG40001 AAG1join AAG40000 AAG0 on AAG0.aaGLHdrID = AAG1.aaGLHdrID where ((AAG1.DEBITAMT <> 0 and AAG1.CRDTAMNT <> 0) or (AAG1.ORDBTAMT <> 0 and AAG1.ORCRDAMT <> 0)) and AAG1.SOURCDOC not in ('BBF','P/L')
STEP 11 - REBUILD THE DATA MART
After all of the results have been fixed above, rerun each script to make sure no results are no longer returned.
Then rebuild the Data Mart in Management Reporter and generate your Financial reports again. (With SQL Change Tracking, some changes may update automatically, but not all, and therefore we consider it best practice to rebuild the Data Mart if any changes were made directly in SQL to ensure all changes are recognized.) Here are the steps to do this (as a precaution, you can backup the DDM database, as this will be deleted and re-created):
1. Open the Configuration Console on the server from the Start menu.
2. In the left hand navigation pane, click ERP Integrations.
3. Click the integration being used (note the server name and the database names), and disable the integration with the “Disable Integration” button.
4. Click “Remove” to remove the ERP Integration.
5. Click the Management Reporter Services in the left hand navigation and stop both of the Management Reporter Services.
6. Delete the DDM database in SQL (you can make a backup first, if needed).
7. Start the Management Reporter Services.
8. Configure the ERP Integration again by going to “File > Configure”.
9. Once the setup is completed, enable the ERP integrations.
With the AA data corrected, and the DDM updated MR should pull in the correct values.
STEP 12 - VERIFY REPORT BALANCES
Detemine if the report balances to the GL Trial Balance report or not. If not, see if you can determine what the difference is. Is the difference still related to the Balance Brought Forward Entries not being correct? If so, please open a support incident for further assistance.