How to correct data errors that prevent Management Reporter integration

Step 1

• Do you have more than one currency ID set up?
   

  Select 'Step1a', CASE WHEN COUNT(*)>1 then 'YES' else 'NO' end, count(*) from currncy with (nolock)

  
  Notes
  • No = single currency
  • Yes = multiple currencies that are set up in Currency Maintenance and have the number listed
• Is multicurrency 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

  
  Note This script returns either "Enabled" or "Not Set Up."

Step 2

• This should be the base currency of the application database:
   

  Select 'Step2a',BaseCuryId from glsetup with (nolock)

• The Base currency of the Actual ledger (balance type = 'a') should be the base currency of the database.
   

  Select 'Step2b',BaseCuryID,LedgerID, BalanceType from ledger with (nolock)

• The BaseCuryID field of all companies in a given application database should all be the same.
   
• This script shows the BaseCuryID field of all application databases that are associated with the System database.
   

  Select 'Step2c',BaseCuryID, CpnyID, databasename from vs_company with (nolock)

• The BaseCuryIDs field should exist in the currency maintenance table. Any foreign currencies should also exist in this table.
   

  Select 'Step2d',curyid from currncy with (nolock)

Step 3

• Accthist that has a currency ID that is 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))

  
  Fix: You may need to update the CuryID field to match the BaseCuryID field. However, that record may already exist. In this case, you receive a 6909 message. If this occurs, determine whether the record is required.
   
• Accthist that has a currency ID that does not match the ledger record (or for a ledger that is not 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

  
  Fix: The update depends on each situation.
   
• CuryAcct that has a CuryID 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))

  
  Fix: The update will depend on each situation. You may have to delete the record later if there is no corresponding AcctHist record. Also, you may have to update the CuryID field. However, that record may already exist. In this case, you receive a 6909 message. If this occurs, determine whether the record is needed.
   
• CuryAcct with a BasecuryID that does not match the ledger record (or for a ledger that is not 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

  
  Fix: The update depends on each situation.
   
• GLTran with a CuryID 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)

  
  Fix: The update depends on each situation. You may need to update the CuryId field to a valid value.
   
• GLTran with a BaseCuryID field value that does not match the ledger record (or for a ledger that is not 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
   g.Posted='P' and g.LedgerID is null OR l.BaseCuryID<>g.BaseCuryId

  
  To fix: Update will depend on each situation. You may need to update the BaseCuryID field to a valid value.

Step 4

• Find CuryAcct records without AcctHist records.
   

  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

  
  Fix: Run the Fix4a.SQL script from MR2.zip on both single-currency and multi-currency databases if errors are reported.
   
• Find Accthist without CuryAcct records.
   

  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

  
  Fix: For multicurrency databases, 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 create or update a curyacct record in GL>Account History>Currency tab, you must enter the data for each period. When you create a batch to post a transaction to the account, this should create a CuryAcct record that contains values for each period. Make sure that you use the year in which the error is reported as the year for posting. Then, reverse the batch to remove the dollars out of the account.
  
  For a single-currency database, open the record in GL>Account History during initialization mode if there are only one or two records. Change the beginning balance by some amount, save the record, revert it, and then save again. This creates the curyacct record. Or, you can run the script from step 6a in this section.
   
• GLTrans without AcctHist
  
  This query will run slowly if there are many gltrans entries and if all the entries are valid. (The query actually runs 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 application 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

  
  Fix: Evaluate the GLtrans field entries. If there is no AcctHist record, the financials are not using these transactions for reports
• This is typically because the transactions are old. You may decide to delete them or take some other action. Evaluate these entries carefully.

Step 5

• Error text: Year, Month, and Day parameters describe an unrepresentable DateTime.
• Error text: Object reference is not set to an instance of an object.
• GLTrans records that have a blank FiscYr field.
   

  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 the FiscYr field based on the perpost value.
   
• GLTrans records that have a perpost value that is 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')

  
  Fix: These batches will all likely have a future period to post. You can open these batches in GL>Journal Transactions during Initialization mode.
  Either delete them, change the period to post, or process them, as appropriate.
   
• GLTrans records that have a transdate value that is far outside a normal range (frequently by having a 2079 year).
   

  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'

  
  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 a FiscYr field 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
  

  
  Fix: These are probably unwanted records. In manipulating data historically, you might have set the fiscal year to a future year to keep the records for later possible review.

Step 6

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)