We ran into a problem with upgrading to Microsoft Dynamics GP 2010 and did not find much documentation to solve it.
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], ...
After looking under the hood we ran the following 2 scripts to get over it -
Script 1
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
Script 2
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
This got us past this hurdle.
Hope this helps you get started with Microsoft Dyanamics GP 2010.
I am often asked as to what are the relevant reports or metrics we need to track while integrating between Order-to-Cash process. Here is a list of reports that you will find useful while thinking through your business integration needs – mainly between CRM or eCommerce applications like Salesforce.com and Accounting systems like QuickBooks, Intacct or others.
While Quickbooks is a boon to small and medium businesses, you hit a wall as you grow to what all you can do with it. This is a list of few points that companies face and require either integration, data extracts or even a reason to upgrade to a more sophisticated accounting package.
