Management Reporter ERP Data Validation Error
Error: CuryAcct with no AcctHist. http://go.microsoft.com/fwlink/?LinkID=397215
Validation Task Error Message, complete with dead Microsoft URL
When running Management Reporter with Microsoft Dynamics SL 2011 or 2015, you may run into data validation errors from time to time. They can be a quick fix, but unfortunately, the included link in the error message is broken. Microsoft created a new page, but in case that one also disappears – here’s the fix for the specific error “CuryAcct with no AcctHist” in Dynamics SL and Management Reporter.
The following SQL script removes all records from CuryAcct (c) that don’t have a matching fiscal year, account, sub account, and company in the AcctHist (a) table. In the MR4.zip package ( official Microsoft download ) (spare copy on this site just in case), this script is the same as Step4aFix.sql. Eith er in Azure Data Studio or SQL Server Management Studio, connect to your database server and run this script against your application database.
--delete curyacct with no matching accthist records. delete c from curyacct c left join AcctHist a on c.acct = a.acct and c.sub = a.sub and c.fiscyr = a.fiscyr and c.ledgerid = a.ledgerid and c.cpnyid = a.cpnyid where a.CpnyID is null
The above script and information in this post are freely available from Microsoft as of January 7th, 2020.
The whole diagnostic process:
Here’s the complete diagnosis script for a variety of Management Reporter data validation errors, also known as Diagnostics4.sql from MR4.zip.
---------------------------------------------------------
-- Accthist, CuryAcct, GLtran Diagnostic Scripts
--
-- Use to find missing or out of sync AcctHist or CuryAcct records
-- Use to find invalid currency IDs
-- Run all scripts against the application database
-- Some statements only grab the top 100 records. To get all records remove 'top 100' from the select.
-- Have a current backup or test updates in a test copy of the database first
-- updated 2/27/2015
---------------------------------------------------------
------------------------------------
-- Step 1 - Are they multi currency?
------------------------------------
-- Do you have more then 1 currency ID setup?
select 'Step1a', CASE WHEN COUNT(*)>1 then 'YES' else 'NO' end, count(*) from currncy with (nolock)
-- Is multi currency enabled?
select top 1 'Step1b',case isnull((select MAX(mcactivated) from cmsetup),-99) WHEN 1 then 'ENABLED' when -99 then 'NOT SETUP' else 'NOT ENABLED' end
-- Check for duplicate fiscal period end dates in GL Setup
SELECT 'Step1c', pernbr, Init ,RetEarnAcct ,FiscalPerEnd00 ,FiscalPerEnd01 ,FiscalPerEnd02 ,
FiscalPerEnd03 ,FiscalPerEnd04 ,FiscalPerEnd05 ,FiscalPerEnd06 ,FiscalPerEnd07 ,
FiscalPerEnd08 ,FiscalPerEnd09 ,FiscalPerEnd10 ,FiscalPerEnd11 ,FiscalPerEnd12 ,
BaseCuryId ,NbrPer ,PerNbr ,BegFiscalYr ,tstamp
FROM [GLSetup] with ( NOLOCK )where Init > 0
and (FiscalPerEnd00=FiscalPerEnd01 or FiscalPerEnd01=FiscalPerEnd02 or FiscalPerEnd02=FiscalPerEnd03
or FiscalPerEnd03=FiscalPerEnd04 or FiscalPerEnd04=FiscalPerEnd05 or FiscalPerEnd05=FiscalPerEnd06
or FiscalPerEnd06=FiscalPerEnd07 or FiscalPerEnd07=FiscalPerEnd08 or FiscalPerEnd08=FiscalPerEnd09
or FiscalPerEnd09=FiscalPerEnd10 or FiscalPerEnd10=FiscalPerEnd11 or FiscalPerEnd11=FiscalPerEnd12)
-----------------------------------------------
-- Step 2 - Look for bad currency setup records
--
-- Suggested Fix: Update the records to be correct or contact support.
-----------------------------------------------
-- Should be the base currency of the app DB:
select 'Step2a',BaseCuryId from glsetup with (nolock)
-- Base currency of their actual ledger (balance type = 'a') should be the base currency of the DB:
select 'Step2b',BaseCuryID,LedgerID, BalanceType from ledger with (nolock)
-- BaseCuryID of all companies in the given application database should all be the same (and same as above):
Select 'Step2c',BaseCuryID, CpnyID, databasename from vs_company with (nolock)
-- Base currency ID should exist in the currency maintenance table. Any forign currencies should also exist in this table:
select 'Step2d',curyid from currncy with (nolock)
-------------------------------------------------------------------
-- Step 3 - Look for bad currencyID or ledgerID on transaction and summary records
-------------------------------------------------------------------
-- AcctHist records with a currency ID not in the currency maintenance table
select top 100 'Step3a',CpnyID, LedgerID, FiscYr, Acct, SUB, CuryId, * from accthist with (nolock) where CuryId not in (select CuryId from Currncy with (nolock))
--To fix: May update CuryID to match the BaseCuryID. However, that record may already exist causing an 6909 msg. In that case, determine if the record is needed
--Accthist with a currency ID that doesn't match the ledger record (or for a ledger that isn't in the ledger table)
select top 100 'Step3b',a.CpnyID, a.LedgerID, a.FiscYr, a.Acct, a.SUB, a.CuryId, l.BaseCuryID,* from accthist a with (nolock) left join Ledger l with (nolock) on a.LedgerID=l.LedgerID where l.LedgerID is null OR l.BaseCuryID<>a.CuryId
--To fix: Update will depend upon each situation.
-- CuryAcct records with a currency ID not in the currency maintenance table
select top 100 'Step3c',CpnyID, LedgerID, FiscYr, Acct, SUB,BaseCuryID, CuryId, * from curyacct with (nolock) where CuryId not in (select CuryId from Currncy with (nolock))
--To fix: Update will depend upon each situation. May delete the record later if there is no corresponding AcctHist record. May update CuryID. However, that record may already exist causing an 6909 msg. In that case, determine if the record is needed.
--CuryAcct with a base currency ID that doesn't match the ledger record (or for a ledger that isn't in the ledger table)
select top 100 'Step3d',c.CpnyID, c.LedgerID, c.FiscYr, c.Acct, c.SUB,c.BaseCuryID, c.CuryId,l.BaseCuryID, * from curyacct c with (nolock) left join Ledger l with (nolock) on c.LedgerID=l.LedgerID where l.LedgerID is null OR c.BaseCuryID<>l.BaseCuryId
--To fix: Update will depend upon each situation.
-- GLTran records with a currency ID not in the currency maintenance table
select top 100 'Step3e',CpnyID, LedgerID, FiscYr, Acct, SUB,BaseCuryID, CuryId,* from gltran with (nolock) where Posted='P' and CuryId not in (select CuryId from Currncy)
--To fix: Update will depend upon each situation. May just update to a valid CuryId.
--GLTran with a base currency ID that doesn't match the ledger record (or for a ledger that isn't in the ledger table)
select top 100 'Step3f',g.CpnyID, g.LedgerID, g.FiscYr, g.Acct, g.SUB, g.BaseCuryID, g.CuryId, * from gltran g with (nolock) left join Ledger l with (nolock) on g.LedgerID=l.LedgerID where l.LedgerID is null OR l.BaseCuryID<>g.BaseCuryId
--To fix: Update will depend upon each situation. May just update to a valid value.
-------------------------------------------------------------------
-- Step 4 - Look for mismatched summary records
-------------------------------------------------------------------
-- CuryAcct with no AcctHist
select top 100 'Step4a',c.CpnyID, c.LedgerID, c.FiscYr, c.Acct, c.Sub, c.BaseCuryID, c.CuryId,*
from CuryAcct c with (nolock)
left join AcctHist a with (nolock) on c.CpnyID=a.CpnyID and c.Acct=a.Acct and c.Sub=a.Sub and c.LedgerID=a.LedgerID and c.FiscYr=a.FiscYr
where a.CpnyID is null order by c.CpnyID, c.Acct, c.SUB, c.ledgerid, c.FiscYr
--To Fix: Run the Fix4a.SQL script from MR3.zip on both single and multi-currency databases.
-- Accthist with no CuryAcct
select top 100 'Step4b',a.CpnyID, a.LedgerID, a.FiscYr, a.Acct, a.Sub, a.CuryId, *
from AcctHist a with (nolock)
left join Curyacct c with (nolock) on c.CpnyID=a.CpnyID and c.Acct=a.Acct and c.Sub=a.Sub and c.LedgerID=a.LedgerID and c.FiscYr=a.FiscYr
where a.BalanceType='A' and c.CpnyID is null order by a.CpnyID, a.Acct, a.SUB, a.ledgerid, a.FiscYr
--To fix: For multi-currency database, either initialize a curyacct record in Initialization mode in GL>Account History or post a transaction to the acct/sub/ledger/fiscyr/cpnyid/curyid.
--To Initialize in GL>Account History, you must enter the data for each period.
--Posting to the acct should create a CuryAcct record with values for each period. Be sure to use the same year for posting in which the error is reported.
--Then reverse the batch to take the dollars out of the account again.
--For a Single currency database, if there is only one or two records, in GL>Account History while in initialization mode, bring up the record. Change the beginning balance by some amount
--and Save. Then change it back and save again. This creates the curyacct record. Or you can run the Script from step 6a below.
-- GLTrans with no AcctHist
--This query will be slow when there are a lot of gltrans, and all of them are OK (actually faster if there is bad data)
select top 100 'Step4c',g.CpnyID, g.LedgerID, g.FiscYr, g.Acct, g.Sub, g.BaseCuryID, g.CuryId,*
from GLTran g with (nolock)
left join AcctHist a with (nolock) on g.CpnyID=a.CpnyID and g.Acct=a.Acct and g.Sub=a.Sub and g.LedgerID=a.LedgerID and g.FiscYr=a.FiscYr
inner join vs_company c with (nolock) on g.CpnyID=c.CpnyID and c.CpnyCOA in (select CpnyID from glsetup with (nolock)) -- only companies in this app DB
where g.posted='P' and a.CpnyID is null
order by g.CpnyID, g.LedgerID, g.FiscYr, g.Acct, g.Sub
option (force order) -- added for performance in larger databases
--To fix: Evaluate the GLtrans. If there is no AcctHist record, the financials have not been using these transactions for report.
--They are typically old.
--You may choose to delete them or some other action. Evaluate carefully.
-------------------------------------------------------------------
-- Step 5 - Look for GLTrans for invalid date/periods
-- Records like this can cause the following errors in MR:
-- Error text: Year, Month, and Day parameters describe an un-representable DateTime.
-- Error text: Object reference not set to an instance of an object.
-------------------------------------------------------------------
-- GLTrans with blank FiscYr
select top 100 'Step5a',CpnyID, LedgerID, FiscYr, Acct, Sub, perpost, perent, trandate,Posted, Rlsed, BatNbr, Module, *
from GLTran with (nolock) where FiscYr='' and (Rlsed=1 or Posted='P')
--To fix: Consider updating fiscyr based upon the perpost value
-- GLTrans with perpost value far outside a normal range
select top 100 'Step5b',CpnyID, LedgerID, FiscYr, Acct, Sub, perpost, perent, trandate,Posted, Rlsed, BatNbr, Module, *
from GLTran with (nolock) where (PerPost>='204812' or PerPost<='195001') and (Rlsed=1 or Posted='P')
--To fix: These will all likely have a future period to post. You can bring these batches up in GL>Journal Transactions while in Initialization mode.
--Delete them or change the period to post or process as appropriate.
-- GLTrans with transdate value far outside a normal range
select top 100 'Step5c',CpnyID, LedgerID, FiscYr, Acct, Sub, perpost, perent, trandate,Posted, Rlsed, BatNbr, Module, *
from GLTran with (nolock) where (trandate>='01/01/2048' or trandate<='01/01/1950') and (Rlsed=1 or Posted='P') and TranDate<>'1/1/1900'
--To fix: Update trandate to crtd_datetime. This script shows how.
--update gltran set trandate=crtd_datetime where (trandate>='01/01/2048' or trandate<='01/01/1950') and (Rlsed=1 or Posted='P') and TranDate<>'1/1/1900'
-- AcctHist records with FiscYr far outside a normal range
select top 100 'Step5d',a.CpnyID, a.LedgerID, a.FiscYr, a.Acct, a.Sub, a.CuryId, *
from AcctHist a with (nolock) where a.FiscYr>'2048' or a.FiscYr<'1950' or len(a.FiscYr) < 4
--To fix: These are probably unwanted records. In manipulating data historically, we might have set the fiscal year to a future year
-- just to keep the records for later potential review.
-------------------------------------------------------------------
-- Step 6 - Look where amounts in AcctHist don't match amounts in CuryAcct
--
-- Run this statement after issues from step 1-5 have been addressed
-------------------------------------------------------------------
select top 1000 'Step6a', a.CpnyID, a.Acct, a.Sub, a.LedgerID, a.FiscYr, c.numCuryAcct, a.BegBal, c.begbal, a.PtdBal00, c.ptdbal00, a.PtdBal01, c.ptdbal01, a.PtdBal02, c.ptdbal02,
a.PtdBal03, c.ptdbal03, a.PtdBal04, c.ptdbal04, a.PtdBal05, c.ptdbal05, a.PtdBal06, c.ptdbal06, a.PtdBal07, c.ptdbal07, a.PtdBal08, c.ptdbal08,
a.PtdBal09, c.ptdbal09, a.PtdBal10, c.ptdbal10, a.PtdBal11, c.ptdbal11, a.PtdBal12, c.ptdbal12, a.ytdBal00, c.ytdbal00, a.ytdBal01, c.ytdbal01,
a.ytdBal02, c.ytdbal02, a.ytdBal03, c.ytdbal03, a.ytdBal04, c.ytdbal04, a.ytdBal05, c.ytdbal05, a.ytdBal06, c.ytdbal06, a.ytdBal07, c.ytdbal07,
a.ytdBal08, c.ytdbal08, a.ytdBal09, c.ytdbal09, a.ytdBal10, c.ytdbal10, a.ytdBal11, c.ytdbal11, a.ytdBal12, c.ytdbal12
from AcctHist a inner join
(select count(*) as numCuryAcct, CpnyID, Acct, Sub, LedgerID, FiscYr, SUM(begbal) as begbal, SUM(ptdbal00) as ptdbal00, SUM(ptdbal01) as ptdbal01,
SUM(ptdbal02) as ptdbal02, SUM(ptdbal03) as ptdbal03, SUM(ptdbal04) as ptdbal04, SUM(ptdbal05) as ptdbal05, SUM(ptdbal06) as ptdbal06,
SUM(ptdbal07) as ptdbal07, SUM(ptdbal08) as ptdbal08, SUM(ptdbal09) as ptdbal09, SUM(ptdbal10) as ptdbal10, SUM(ptdbal11) as ptdbal11,
SUM(ptdbal12) as ptdbal12, SUM(ytdbal00) as ytdbal00, SUM(ytdbal01) as ytdbal01, SUM(ytdbal02) as ytdbal02, SUM(ytdbal03) as ytdbal03,
SUM(ytdbal04) as ytdbal04, SUM(ytdbal05) as ytdbal05, SUM(ytdbal06) as ytdbal06, SUM(ytdbal07) as ytdbal07, SUM(ytdbal08) as ytdbal08,
SUM(ytdbal09) as ytdbal09, SUM(ytdbal10) as ytdbal10, SUM(ytdbal11) as ytdbal11, SUM(ytdbal12) as ytdbal12
from curyacct group by CpnyID, Acct, Sub, LedgerID, FiscYr) c
on a.CpnyID=c.CpnyID and a.Acct=c.Acct and a.Sub=c.Sub and a.LedgerID=c.LedgerID and a.FiscYr=c.FiscYr
where round(a.BegBal,2)<>round(c.begbal,2) or round(a.PtdBal00,2)<>round(c.PtdBal00,2) or round(a.PtdBal01,2)<>round(c.PtdBal01,2) or
round(a.PtdBal02,2)<>round(c.PtdBal02,2) or round(a.PtdBal03,2)<>round(c.PtdBal03,2) or round(a.PtdBal04,2)<>round(c.ptdbal04,2) or
round(a.PtdBal05,2)<>round(c.PtdBal05,2) or round(a.PtdBal06,2)<>round(c.PtdBal06,2) or round(a.PtdBal07,2)<>round(c.PtdBal07,2) or
round(a.PtdBal08,2)<>round(c.PtdBal08,2) or round(a.PtdBal09,2)<>round(c.PtdBal09,2) or round(a.PtdBal10,2)<>round(c.PtdBal10,2) or
round(a.PtdBal11,2)<>round(c.PtdBal11,2) or round(a.PtdBal12,2)<>round(c.PtdBal12,2) or round(a.ytdBal00,2)<>round(c.ytdBal00,2) or
round(a.ytdBal01,2)<>round(c.ytdBal01,2) or round(a.ytdBal02,2)<>round(c.ytdBal02,2) or round(a.ytdBal03,2)<>round(c.ytdBal03,2) or
round(a.ytdBal04,2)<>round(c.ytdbal04,2) or round(a.ytdBal05,2)<>round(c.ytdBal05,2) or round(a.ytdBal06,2)<>round(c.ytdBal06,2) or
round(a.ytdBal07,2)<>round(c.ytdBal07,2) or round(a.ytdBal08,2)<>round(c.ytdBal08,2) or round(a.ytdBal09,2)<>round(c.ytdBal09,2) or
round(a.ytdBal10,2)<>round(c.ytdBal10,2) or round(a.ytdBal11,2)<>round(c.ytdBal11,2) or round(a.ytdBal12,2)<>round(c.ytdBal12,2)
--Make sure all other errors in this script are resolved first
--To fix: For a Single currency database, if there is only a few records, in GL>Account History while in initialization mode, bring up the record.
--Change the beginning balance by some amount and Save. Then change it back and save again. This updates the curyacct record. to match Accthist.
--If there are many records or if you prefer, just run the script ResetCuryAcctfromAcctHist.sql from MR3.zip.
--In a multi-currency database, if there are only a few records, in GL>Account History while in initialization mode, bring up the record and select the currency tab.
--Correct each period individually as needed. If there are many records or if you prefer, just run the script MultiCurrencyStep6aFix.sql from MR3.zip.
--Any change is made in the base CuryID record.
--
-- If the above doesn't correct, may need to Rebuild_CuryAcctFromGLTrans.sql. Contact support for guidance before choosing this option.
------------------------------------------------------------------------------------------
--When done with Step 6, rerun the Diagnostics script to verify that it is no longer reporting any errors.
------------------------------------------------------------------------------------------
The script has a lot of comments, read through them – you’ll notice that some of them call for reaching out to support. Others call for running scripts from MR3 or MR4 – that’s a versioning error. All scripts are available in both MR3 and MR4.
Free Advice
Setup email notifications for data validation – this can be done from the Management Reporter console under each specific ERP integration. With email notifications on, you can stay ahead of data validation issues.
References
https://support.microsoft.com/en-us/help/2931999/how-to-correct-data-errors-that-prevent-management-reporter-integratio
http://download.microsoft.com/download/4/A/7/4A7894FD-A30F-405F-877A-13053DF92422/MR4.zip