2022
This page deals with resolving the common problem encountered by Great Plains users while upgrading to the latest version of Great Plains, Microsoft Dynamics Great Plains 2010.
We were consistently getting error at
The following SQL statement produced an error:
CREATE VIEW GL10000CurrencyTranslationView AS select \[GL10000Final\].\[OPENYEAR\], \[GL10000Final\].\[ACTINDX\], \[GL10000Final\].\[CRDTAMNT\], \[GL10000Final\].\[DEBITAMT\], \[GL10000Final\].\[ORCRDAMT\], \[GL10000Final\].\[ORDBTAMT\], \[GL10000Final\].\[TRXDATE\], \[GL10000Final\].\[DSCRIPTN\], ...
The following scripts will resolve the above issue.
GO SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING OFF GO CREATE TABLE \[dbo\].\[MC40600\]( \[CURNCYID\] \[char\](15) NOT NULL, \[CurrentExchangeTableID\] \[char\](15) NOT NULL, \[HistoricalExchgTableID\] \[char\](15) NOT NULL, \[AverageExchangeTableID\] \[char\](15) NOT NULL, \[BudgetExchangeTableID\] \[char\](15) NOT NULL, \[DEX_ROW_ID\] \[int\] IDENTITY(1,1) NOT NULL, CONSTRAINT \[PKMC40600\] PRIMARY KEY NONCLUSTERED ( \[CURNCYID\] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON \[PRIMARY\] ) ON \[PRIMARY\] GO SET ANSI_PADDING OFF GO
GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO CREATE VIEW \[dbo\].\[GL10000CurrencyTranslationView\] AS select \[GL10000Final\].\[OPENYEAR\], \[GL10000Final\].\[ACTINDX\], \[GL10000Final\].\[CRDTAMNT\], \[GL10000Final\].\[DEBITAMT\], \[GL10000Final\].\[ORCRDAMT\], \[GL10000Final\].\[ORDBTAMT\], \[GL10000Final\].\[TRXDATE\], \[GL10000Final\].\[DSCRIPTN\], \[GL10000Final\].\[REFRENCE\], \[GL10000Final\].\[CURNCYID\], \[GL10000Final\].\[Original_Exchange_Rate\], \[GL10000Final\].\[JRNENTRY\], \[GL10000Final\].\[TRXSORCE\], \[GL10000Final\].\[SOURCDOC\], \[GL10000Final\].\[ORDOCNUM\], \[GL10000Final\].\[ORTRXSRC\], \[GL10000Final\].\[ORMSTRID\],\[GL10000Final\].\[ORMSTRNM\], \[GL10000Final\].\[ORTRXTYP\], \[GL10000Final\].\[SERIES\], \[GL10000Final\].\[VOIDED\], \[GL10000Final \].\[Ledger_ID\], \[GL10000Final\].\[TranslationCurrencyID\], \[GL10000Final\].\[CurrencyTranslationType\], \[GL10000Final \].\[PERDENDT\], \[GL10000Final\].\[TranslationExchangeRate\], case GL10000Final.CRDTAMNT when 0.0 then 0.0 else dbo.mcFuncCalculateAmountExtended(\[GL10000Final\].\[RTCLCMTD\], 3, \[GL10000Final\].\[TranslationExchangeRate\], \[GL10000Final\].\[DENXRATE\], \[GL10000Final\].\[MCTRXSTT\], \[GL10000Final\].\[DECPLCUR\], \[GL10000Final\].\[CRDTAMNT\]) end as TranslationCreditAmount, case GL10000Final.DEBITAMT when 0.0 then 0.0 else dbo.mcFuncCalculateAmountExtended(\[GL10000Final \].\[RTCLCMTD\], 3, \[GL10000Final\].\[TranslationExchangeRate\], \[GL10000Final\].\[DENXRATE\], \[GL10000Final\].\[MCTRXSTT\], \[GL10000Final\].\[DECPLCUR\], \[GL10000Final\].\[DEBITAMT\]) end as TranslationDebitAmount, \[GL10000Final\].\[SequenceNumber\], \[GL10000Final\].\[PERIODID\], \[GL10000Final\].\[CURRNIDX\], \[GL10000Final\].\[DECPLCUR\], \[GL10000Final\].\[RATETPID\], \[GL10000Final\].\[EXGTBLID\], \[GL10000Final\].\[EXCHDATE\], \[GL10000Final\].\[TIME1\], \[GL10000Final\].\[RTCLCMTD\], \[GL10000Final\].\[DENXRATE\], \[GL10000Final\].\[MCTRXSTT\], \[GL10000Final\].\[Adjustment_Transaction\] from (select distinct \[GL_TRX_WORK\].\[OPENYEAR\], \[GL_TRX_WORK\].\[ACTINDX\], \[GL_TRX_WORK\].\[CRDTAMNT\], \[GL_TRX_WORK\].\[DEBITAMT\],\[GL_TRX_WORK \].\[ORCRDAMT\], \[GL_TRX_WORK\].\[ORDBTAMT\], \[GL_TRX_WORK\].\[TRXDATE\], \[GL_TRX_WORK\].\[DSCRIPTN\], \[GL_TRX_WORK \].\[REFRENCE\], \[GL_TRX_WORK\].\[CURNCYID\], \[GL_TRX_WORK\].\[XCHGRATE\] as Original_Exchange_Rate, \[GL_TRX_WORK \].\[JRNENTRY\], \[GL_TRX_WORK\].\[TRXSORCE\], \[GL_TRX_WORK\].\[SOURCDOC\], \[GL_TRX_WORK\].\[ORDOCNUM\], \[GL_TRX_WORK \].\[ORTRXSRC\], \[GL_TRX_WORK\].\[ORMSTRID\], \[GL_TRX_WORK\].\[ORMSTRNM\], \[GL_TRX_WORK\].\[ORTRXTYP\], \[GL_TRX_WORK \].\[SERIES\], \[GL_TRX_WORK\].\[VOIDED\], \[GL_TRX_WORK\].\[Ledger_ID\], \[GL_TRX_WORK\].\[TranslationCurrencyID\], \[GL_TRX_WORK \].\[CurrencyTranslationType\], \[GL_TRX_WORK\].\[PERDENDT\], F.XCHGRATE as TranslationExchangeRate, \[GL_TRX_WORK\].\[SQNCLINE\] as SequenceNumber, \[GL_TRX_WORK\].\[PERIODID\], E.\[CURRNIDX\], (E.\[DECPLCUR\]-1) as DECPLCUR, \[GL_TRX_WORK\].\[RATETPID\], \[GL_TRX_WORK\].\[EXGTBLID\], F.\[EXCHDATE\], F.\[TIME1\], D.\[RTCLCMTD\], dbo.mcFuncGetDenExchRate(GL_TRX_WORK.TranslationCurrencyID,D.RTCLCMTD) as DENXRATE, \[GL_TRX_WORK\].\[MCTRXSTT\], \[GL_TRX_WORK \].\[Adjustment_Transaction\] from DYNAMICS..MC40200 E, DYNAMICS..MC40300 D cross apply (select e.YEAR1 as OPENYEAR,a.JRNENTRY,a.SOURCDOC,a.REFRENCE,f.DSCRIPTN,a.TRXDATE, a.TRXSORCE,f.ACTINDX,a.SERIES,f.ORTRXTYP,f.ORMSTRID,f.ORMSTRNM, f.ORDOCNUM,a.ORTRXSRC,a.SQNCLINE,a.CURNCYID,b.CURNCYID as TranslationCurrencyID, a.CURRNIDX,a.RATETPID,b.ExchangeTableID as EXGTBLID,a.XCHGRATE, a.EXCHDATE,a.TIME1,a.RTCLCMTD,dbo.glFuncGetPeriodID(a.TRXDATE,a.OPENYEAR,2) as PERIODID,f.CRDTAMNT,f.DEBITAMT,f.ORCRDAMT,f.ORDBTAMT, e.PERDENDT, dbo.mcFuncGetMCTrxState(b.CURNCYID) as MCTRXSTT,b.CurrencyTranslationType, a.VOIDED,a.Ledger_ID, a.Adjustment_Transaction, case b.CurrencyTranslationType when 1 then e.PERDENDT when 3 then a.TRXDATE end as ExchangeRateDate from GL10000 a, GL10001 f, (select c.ACTINDX,b.CURNCYID, ExchangeTableID= case CurrencyTranslationType when 1 then b.AverageExchangeTableID when 3 then b.HistoricalExchgTableID end, c.CurrencyTranslationType from MC00200 c,MC40600 b where c.CURNCYID='' and c.CurrencyTranslationType<>2) b, (select distinct b.PERIODID, a.YEAR1,a.FSTFSCDY,a.LSTFSCDY, b.PERIODDT,b.PERDENDT from SY40101 a, SY40100 b where a.YEAR1=b.YEAR1 and b.SERIES=2) e where a.JRNENTRY=f.JRNENTRY and f.ACTINDX=b.ACTINDX and a.PERIODID=e.PERIODID and e.YEAR1=e.YEAR1 and a.TRXDATE >=e.FSTFSCDY and a.TRXDATE <=e.LSTFSCDY) GL_TRX_WORK cross apply dbo.mcFuncGetExchangeRateTable(GL_TRX_WORK.ExchangeRateDate, GL_TRX_WORK.EXGTBLID, D.TRXDTDEF, D.DATELMTS,D.PRVDSLMT,D.Base_Exchange_Rate_On,GL_TRX_WORK.MCTRXSTT) F where GL_TRX_WORK.EXGTBLID = D.EXGTBLID and D.CURNCYID=E.CURNCYID) GL10000Final GO