Dynamics GP - Payables Transaction Inquiry error "The stored procedure createSQLTmpTable returned the following results: DBMS: 2627, Microsoft Dynamics GP: 0"

Problem:
When opening a transaction inquiry window to view transactions for a vendor, you get the following error for some vendors.
"The stored procedure createSQLTmpTable returned the following results: DBMS: 2627, Microsoft Dynamics GP: 0"

Cause:
This is caused by a record existing in both the Work AND History Tables.
This generally happens when a batch gets stuck, or half-posts and has to be recovered using the script to force-recover it. Some transactions may post to history, but not get cleaned out of the Work Table.

Solution:
Run this


select *
--delete
from pm20000
where
VENDORID in ('MyAffectedVendorID') and
DOCNUMBR in (
select DOCNUMBR  from pm30200)

Dynamics GP - Reduce Currency Decimal Places

UPDATE MC40200 SET DECPLCUR=3 where CURNCYID='Z-US$'

Original Article
http://support.microsoft.com/kb/865702

Dynamics GP - Simplified SP to Load seermHATBwrapper into a temp table

--Create Temp Table
/****** Object:  Table [dbo].[BI_HATB_Temp]    Script Date: 12/10/2014 2:47:18 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[BI_HATB_Temp](
[APPLY_AMOUNT] [numeric](19, 5) NOT NULL,
[AGING_AMOUNT] [numeric](19, 5) NOT NULL,
[CUSTNMBR] [char](15) NOT NULL
) ON [PRIMARY]
SET ANSI_PADDING OFF
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [CUSTNAME] [char](65) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [BALNCTYP] [smallint] NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [USERDEF1] [char](21) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [CNTCPRSN] [char](61) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [PHONE1] [char](21) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [SLPRSNID] [char](15) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [SALSTERR] [char](15) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [PYMTRMID] [char](21) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [CRLMTAMT] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [CRLMTPER] [smallint] NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [CRLMTPAM] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [CRLMTTYP] [smallint] NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [CUSTCLAS] [char](15) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [SHRTNAME] [char](15) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [ZIP] [char](11) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [STATE] [char](29) NOT NULL
SET ANSI_PADDING ON
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [CUDSCRIPTN] [char](31) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [AGNGDATE] [datetime] NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [CHCUMNUM] [char](15) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [DOCNUMBR] [char](21) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [RMDTYPAL] [smallint] NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [DSCRIPTN] [char](31) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [DCURNCYID] [char](15) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [ORTRXAMT] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [CURTRXAM] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [AGNGBUKT] [smallint] NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [CASHAMNT] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [COMDLRAM] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [SLSAMNT] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [COSTAMNT] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [FRTAMNT] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [MISCAMNT] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [TAXAMNT] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [DISAVAMT] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [DDISTKNAM] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [DWROFAMNT] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [TRXDSCRN] [char](31) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [DOCABREV] [char](3) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [CHEKNMBR] [char](21) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [DOCDATE] [datetime] NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [DUEDATE] [datetime] NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [GLPOSTDT] [datetime] NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [DISCDATE] [datetime] NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [POSTDATE] [datetime] NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [DINVPDOF] [datetime] NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [DCURRNIDX] [smallint] NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [DXCHGRATE] [numeric](19, 7) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [ORCASAMT] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [ORSLSAMT] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [ORCSTAMT] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [ORDAVAMT] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [ORFRTAMT] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [ORMISCAMT] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [ORTAXAMT] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [ORCTRXAM] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [ORORGTRX] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [DORDISTKN] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [DORWROFAM] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [DDENXRATE] [numeric](19, 7) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [DMCTRXSTT] [smallint] NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [Aging_Period_Amount] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [APFRDCNM] [char](21) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [APFRDCTY] [smallint] NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [FROMCURR] [char](15) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [APTODCNM] [char](21) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [APTODCTY] [smallint] NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [APPTOAMT] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [ACURNCYID] [char](15) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [DATE1] [datetime] NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [POSTED] [tinyint] NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [ADISTKNAM] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [AWROFAMNT] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [PPSAMDED] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [GSTDSAMT] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [ACURRNIDX] [smallint] NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [AXCHGRATE] [numeric](19, 7) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [RLGANLOS] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [ORAPTOAM] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [AORDISTKN] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [AORWROFAM] [numeric](19, 5) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [ADENXRATE] [numeric](19, 7) NOT NULL
ALTER TABLE [dbo].[BI_HATB_Temp] ADD [AMCTRXSTT] [smallint] NOT NULL

GO

SET ANSI_PADDING OFF
GO

--Create Custom Stored Procedure to clear table and load new data into it
/****** Object:  StoredProcedure [dbo].[BI_Hatb_load]    Script Date: 12/10/2014 3:00:10 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[BI_Hatb_load]
@I_dAgingDate1 datetime     = NULL
AS
BEGIN
set nocount on
truncate table  bi_hatb_temp
insert into bi_hatb_temp
([APPLY_AMOUNT]
      ,[AGING_AMOUNT]
      ,[CUSTNMBR]
      ,[CUSTNAME]
      ,[BALNCTYP]
      ,[USERDEF1]
      ,[CNTCPRSN]
      ,[PHONE1]
      ,[SLPRSNID]
      ,[SALSTERR]
      ,[PYMTRMID]
      ,[CRLMTAMT]
      ,[CRLMTPER]
      ,[CRLMTPAM]
      ,[CRLMTTYP]
      ,[CUSTCLAS]
      ,[SHRTNAME]
      ,[ZIP]
      ,[STATE]
      ,[CUDSCRIPTN]
      ,[AGNGDATE]
      ,[CHCUMNUM]
      ,[DOCNUMBR]
      ,[RMDTYPAL]
      ,[DSCRIPTN]
      ,[DCURNCYID]
      ,[ORTRXAMT]
      ,[CURTRXAM]
      ,[AGNGBUKT]
      ,[CASHAMNT]
      ,[COMDLRAM]
      ,[SLSAMNT]
      ,[COSTAMNT]
      ,[FRTAMNT]
      ,[MISCAMNT]
      ,[TAXAMNT]
      ,[DISAVAMT]
      ,[DDISTKNAM]
      ,[DWROFAMNT]
      ,[TRXDSCRN]
      ,[DOCABREV]
      ,[CHEKNMBR]
      ,[DOCDATE]
      ,[DUEDATE]
      ,[GLPOSTDT]
      ,[DISCDATE]
      ,[POSTDATE]
      ,[DINVPDOF]
      ,[DCURRNIDX]
      ,[DXCHGRATE]
      ,[ORCASAMT]
      ,[ORSLSAMT]
      ,[ORCSTAMT]
      ,[ORDAVAMT]
      ,[ORFRTAMT]
      ,[ORMISCAMT]
      ,[ORTAXAMT]
      ,[ORCTRXAM]
      ,[ORORGTRX]
      ,[DORDISTKN]
      ,[DORWROFAM]
      ,[DDENXRATE]
      ,[DMCTRXSTT]
      ,[Aging_Period_Amount]
      ,[APFRDCNM]
      ,[APFRDCTY]
      ,[FROMCURR]
      ,[APTODCNM]
      ,[APTODCTY]
      ,[APPTOAMT]
      ,[ACURNCYID]
      ,[DATE1]
      ,[POSTED]
      ,[ADISTKNAM]
      ,[AWROFAMNT]
      ,[PPSAMDED]
      ,[GSTDSAMT]
      ,[ACURRNIDX]
      ,[AXCHGRATE]
      ,[RLGANLOS]
      ,[ORAPTOAM]
      ,[AORDISTKN]
      ,[AORWROFAM]
      ,[ADENXRATE]
      ,[AMCTRXSTT]
 )


exec seermHATBSRSWrapper @I_dAgingDate=@I_dAgingDate1,@I_cStartCustomerNumber=N'',@I_cEndCustomerNumber=N'',@I_cStartCustomerName=N'',@I_cEndCustomerName=N'',@I_cStartClassID=N'',@I_cEndClassID=N'',@I_cStartSalesPersonID=N'',@I_cEndSalesPersonID=N'',@I_cStartSalesTerritory=N'',@I_cEndSalesTerritory=N'',@I_cStartShortName=N'',@I_cEndShortName=N'',@I_cStartState=N'',@I_cEndState=N'',@I_cStartZipCode=N'',@I_cEndZipCode=N'',@I_cStartPhoneNumber=N'',@I_cEndPhoneNumber=N'',@I_cStartUserDefined=N'',@I_cEndUserDefined=N'',@I_tUsingDocumentDate=0,@I_dStartDate='1900-01-01 00:00:00',@I_dEndDate='2014-12-09 00:00:00',@I_sIncludeBalanceTypes=0,@I_tExcludeNoActivity=1,@I_tExcludeMultiCurrency=1,@I_tExcludeZeroBalanceCustomer=1,@I_tExcludeFullyPaidTrxs=1,@I_tExcludeCreditBalance=0,@I_tExcludeUnpostedAppldCrDocs=1,@I_tConsolidateNAActivity=0

select * from BI_HATB_TEMP
END


GO



--Execute BI_HATB_TEMP with one parameter, aging date

exec bi_hatb_load @I_dAgingDate1='2014-12-31 00:00:00'

Dynamics GP - SQL command to run the Historical Aged Trial Balance - seermhatbsrswrapper

exec seermHATBSRSWrapper @I_dAgingDate='2014-12-31 00:00:00',@I_cStartCustomerNumber=N'',@I_cEndCustomerNumber=N'',@I_cStartCustomerName=N'',@I_cEndCustomerName=N'',@I_cStartClassID=N'',@I_cEndClassID=N'',@I_cStartSalesPersonID=N'',@I_cEndSalesPersonID=N'',@I_cStartSalesTerritory=N'',@I_cEndSalesTerritory=N'',@I_cStartShortName=N'',@I_cEndShortName=N'',@I_cStartState=N'',@I_cEndState=N'',@I_cStartZipCode=N'',@I_cEndZipCode=N'',@I_cStartPhoneNumber=N'',@I_cEndPhoneNumber=N'',@I_cStartUserDefined=N'',@I_cEndUserDefined=N'',@I_tUsingDocumentDate=0,@I_dStartDate='1900-01-01 00:00:00',@I_dEndDate='2014-12-09 00:00:00',@I_sIncludeBalanceTypes=0,@I_tExcludeNoActivity=1,@I_tExcludeMultiCurrency=1,@I_tExcludeZeroBalanceCustomer=1,@I_tExcludeFullyPaidTrxs=1,@I_tExcludeCreditBalance=0,@I_tExcludeUnpostedAppldCrDocs=1,@I_tConsolidateNAActivity=0

Dynamics GP - Reset System Password

Original post here
http://gp-dynamics.com/dynamics-gp-command-details.asp?id=96


UPDATE DYNAMICS..SY02400 SET DMYPWDID=1,PASSWORD = 0x00202020202020202020202020202020

Windows 2008 R2 - windows management framework 3.0 install error "the update is not applicable to your computer"

Read this
http://blogs.msdn.com/b/powershell/archive/2012/03/23/troubleshooting-windows-management-framework-3-0-beta-installation.aspx

and this
http://technet.microsoft.com/en-us/library/hh847837.aspx#BKMK_InstallingOnWindows7andWindowsServer2008R2

Solution:

• Install Windows 2008R2 Latest Service Pack (do this manually, windows update does not automatically install SP1)
• Install dot net 4.5 
• http://go.microsoft.com/fwlink/?LinkID=242919
• Run install
• http://go.microsoft.com/fwlink/?LinkID=240290

Dynamics GP - SQL View - All Posted and Unposted GL Transactions Detail and Summary by period and account

See Original view here
http://victoriayudin.com/2011/04/27/sql-view-for-all-gl-transactions-in-dynamics-gp/


--All GL Details
----------------------------------------------------------------------------------------------------------
SELECT     GL.Trx_Status, GL.TRXDATE AS Trx_Date, GL.JRNENTRY AS Journal_Entry, GM.ACTNUMST AS Account_Number,
                      GA.ACTDESCR AS Account_Description, GL.DEBITAMT AS Debit_Amount, GL.CRDTAMNT AS Credit_Amount, GL.REFRENCE AS Reference,
                      GL.SOURCDOC AS Source_Document, GL.ORTRXSRC AS Originating_TRX_Source, GL.ORMSTRID AS Originating_Master_ID,
                      GL.ORMSTRNM AS Originating_Master_Name, GL.ORDOCNUM AS Originating_Doc_Number, GL.CURNCYID AS Currency_ID,
                      GL.LASTUSER AS Last_User, GL.USWHPSTD AS User_Who_Posted, GL.OPENYEAR AS GLYear, GL.PERIODID AS GLPeriod
FROM         (SELECT     ACTINDX, TRXDATE, SOURCDOC, JRNENTRY, ORTRXSRC, REFRENCE, ORDOCNUM, ORMSTRID, ORMSTRNM, DEBITAMT, CRDTAMNT,
                                              CURNCYID, 'Open' AS Trx_Status, LASTUSER, USWHPSTD, OPENYEAR, PERIODID
                       FROM          dbo.GL20000
                       WHERE      (SOURCDOC NOT IN ('BBF', 'P/L')) AND (VOIDED = 0)
                       UNION ALL
                       SELECT     ACTINDX, TRXDATE, SOURCDOC, JRNENTRY, ORTRXSRC, REFRENCE, ORDOCNUM, ORMSTRID, ORMSTRNM, DEBITAMT, CRDTAMNT,
                                             CURNCYID, 'History' AS Trx_Status, LASTUSER, USWHPSTD, HSTYEAR, PERIODID
                       FROM         dbo.GL30000
                       WHERE     (SOURCDOC NOT IN ('BBF', 'P/L')) AND (VOIDED = 0)
                       UNION ALL
                       SELECT     GD.ACTINDX, GH.TRXDATE, GH.SOURCDOC, GH.JRNENTRY, GH.ORTRXSRC, GH.REFRENCE, GD.ORDOCNUM, GD.ORMSTRID,
                                             GD.ORMSTRNM, GD.DEBITAMT, GD.CRDTAMNT, GH.CURNCYID, 'Work' AS Trx_Status, GH.LASTUSER, GH.USWHPSTD, GH.OPENYEAR,
                                             GH.PERIODID
                       FROM         dbo.GL10000 AS GH INNER JOIN
                                             dbo.GL10001 AS GD ON GH.JRNENTRY = GD.JRNENTRY
                       WHERE     (GH.VOIDED = 0)) AS GL INNER JOIN
                      dbo.GL00105 AS GM ON GL.ACTINDX = GM.ACTINDX INNER JOIN
                      dbo.GL00100 AS GA ON GL.ACTINDX = GA.ACTINDX

--All GL Summary
---------------------------------------------------------------------------------------------------------------------
SELECT     GLYear, GLPeriod, Account_Number, Account_Description, SUM(Debit_Amount) AS Debit, SUM(Credit_Amount) AS Credit,
                      SUM(Debit_Amount - Credit_Amount) AS Total
FROM         dbo.BI_GL_Trx
GROUP BY Account_Number, Account_Description, GLYear, GLPeriod

Dynamics GP - SQL VIEW - PO, Received status, Invoiced status

SELECT     POP10100.PONUMBER, a.POPRCTNM, a.VNDDOCNM, a.APStatus, a.BCHSOURC, a.VCHRNMBR, PM00200.VENDORID, PM00200.VENDNAME,
                      CASE WHEN poprctnm IS NOT NULL THEN 'Received' ELSE 'Not Received' END AS RctStatus, CASE WHEN vchrnmbr IS NOT NULL
                      THEN 'Invoiced' ELSE 'Not Invoiced' END AS InvStatus
FROM         POP10100 LEFT OUTER JOIN
                      PM00200 ON POP10100.VENDORID = PM00200.VENDORID LEFT OUTER JOIN
                          (SELECT     'Work' AS APStatus, POP10300.POPRCTNM, POP10300.VNDDOCNM, POP10300.BCHSOURC, POP10310.RCPTLNNM,
                                                   POP10310.PONUMBER, POP10300.VENDORID, POP10300.VCHRNMBR, POP10310.ITEMDESC, POP10310.ITEMNMBR
                            FROM          POP10300 INNER JOIN
                                                   POP10310 ON POP10300.POPRCTNM = POP10310.POPRCTNM
                            UNION
                            SELECT     'Posted' AS APstatus, POP30300.POPRCTNM, POP30300.VNDDOCNM, POP30300.BCHSOURC, POP30310.RCPTLNNM,
                                                  POP30310.PONUMBER, POP30300.VENDORID, POP30300.VCHRNMBR, POP30310.ITEMDESC, POP30310.ITEMNMBR
                            FROM         POP30300 INNER JOIN
                                                  POP30310 ON POP30300.POPRCTNM = POP30310.POPRCTNM) AS a ON POP10100.PONUMBER = a.PONUMBER
GROUP BY a.APStatus, a.POPRCTNM, a.VNDDOCNM, a.BCHSOURC, a.VCHRNMBR, POP10100.PONUMBER, PM00200.VENDORID, PM00200.VENDNAME

Dynamics GP - Report Writer - Display all Comment Lines, even if the comment is larger than 4 lines with Autogrow

Normally this cannot be done.
However, if you switch the report to Text, and use the Comment field (not the array) it will display the full value of the field, and grow and shrink automatically.

Dynamics GP - How to copy / migrate an AFA Financial Report from one company to another

1. Find the next available AFA Report ID in the new company
1. select * from AF40100
2. The next id is the next available number
2. Copy all data from the following tables
1. AF40101, AF40102, AF40103, AF40104, AF40105, AF40106, AF40107, AF40108, AF40200, and AF40201
3. Replace the report id with the new report ID in the new company
1. update af40101 set reportid = [newid] where reportid = [oldid]

You can export the data from pervasive by browsing to each table and exporting to a data files.
However, pervasive will export an additional column of 0's before each actual column of data.
After cleaning, the first column from pervasive is not needed.
you must clean the data and remove these columns to import the data into SQL.
Once cleaned, you can copy and paste directly into the AFA tables.


See original solution here
http://support.microsoft.com/kb/863224

Dynamics NAV - How to import New company Opening Balances

On each account card that you want to update directly, enable the "Direct Posting" tickbox on the account card. This allows direct posting to the account without a subledger transaction.

For AR and AP balances, setup the initial posting groups to debit and credit the same accounts so there is a net 0 movement on the account. The actual balances can then be entered as journals if you tick the "Direct Posting" for those accounts

• Create a General Journal Batch for OPENAR
• Set the Bal type to "account", and the Bal Account No. to your Ar account number
• This will cause the ar entries to cancel themselves out in the gl, and then you can enter your gl balances separately.
• Configure the General Journal Screen to the format of your data
• Copy and paste your entries as rows

For Bank and Inventory Balances
http://www.dynamicsnavconsultant.com/tag/beginning-balance/

Solution here
http://support.microsoft.com/kb/944227

Dynamics NAV 2013 R2 - How to Create a new Company

• Open NAV RTC
• Search for Companies
• Administration>IT Administration>Companies
• Add new company
• Switch companies to login to the new company
• Company will initialize

Populate with default data

• Export these packages from an existing company, then import them to your new company
• NOSERIES
• LS DISTRIBUTION (Only required if using LS Retail in new company)
• BASICCONFIG
• NAVBASIC
• LS BASE (Only required if using LS Retail in new company)

Set Permissions if required

• If creating the company in the same database, the same permissions apply by default across all companies
• To change this, in the User Setup>Permission Set list, there is a company column
• If you select a company for a permission set, then those permissions will apply to that company for that user only.

Related Issues

• "Customer has one of more open ledger entries" when trying to set the IC Partner code
• Review the Customer Ledger Entries screen
• Add a filter where open = yes
• Enter a journal to close all of these transactions
• "Vendor has one of more open ledger entries" when trying to set the IC Partner code
• Review the Vendor Ledger Entries screen
• Add a filter where open = yes
• Enter a journal to close all of these transactions

Dynamics NAV - Rapidstart

• How to Create a Template, insert Data and import to NAV
• Create Package
• Create Worksheet
• Assign Worksheet to Package
• Export Template to Excel from Worksheet or Package Table
• Enter data to import into template
• Import Data from Template
• Apply Data

Dynamics GP - Check to Words shows extra 0's for cents. Check to Words is too long and is truncated at 80 characters.

Issue:
Payables check prints incorrect cents
If your currency has more than 2 decimal places, and you print checks, the check to Words function that prints the check amount in words on the check prints the cents with all of the zeroes for the number of decimal places the currency is set to.

Example. - 5 DP currency prints as

100.20 shows as One Hundred Dollars And 20000 Cents

Solution:

• Open PSTL
• Look under Misc Tools
• Look for Decimal Place Tool
• Tick the Box next to the Blue Tick (It will be a Red Tick after you tick it)

This will force the Check to Words to only display 2 decimal places in the description.

Original Solution
http://support.microsoft.com/kb/961423

However, this field is not on the cheque by default, you must add the "Amount in words" field from PM Payment work.
However, the "Amount in Words" field will throw an error if you use it directly.
Ignore it completely and instead, use the STRING A 255 Field. It will show the correct information once the PSTL tool has been installed.

If you want to wrap the field, you must use two (or more) calculated fields using the rw_parsestring function to split the string.
Then rw_parsestring against that calculated string.

• CalcLine1 = RW_ParseString(Pmt_Work.STRING A 255 50  1)
• CalcLine2 = RW_ParseString(Pmt_Work.STRING A 255 50  2)

OR you can use substring

• CalcLine1 = RW_SubString(Pmt_Work.STRING A 255 1  60)
• CalcLine2 = RW_SubString(Pmt_Work.STRING A 255 61  60)

Dynamics NAV - Install NAV 2013 R2-2018 and LS Retail 7.10-11.02

Dynamics NAV Install guide
https://docs.microsoft.com/en-us/dynamics-nav/installation-options

LS NAV Install guide
http://help.lsnav.lsretail.com/Content/Resources/Documents%20for%20download/Installation%20Guide%20LS%20Nav%202018%20(11.02).pdf

• Install NAV on the SQL server
• Disable UAC
• Add the current account to the local administrator group
• If you are creating a specific service account, and using separate database and app servers, follow this guide
• https://msdn.microsoft.com/en-us/library/hh166150(v=nav.70).aspx
• You will need Excel, install a copy first
• Add Search Service Role
• Add Role>File Services>Windows Search
• or Add role>Features>Windows Search Service
• Install the latest service pack on your OS
• Install Dot Net 3.5 from Roles>Features>Dot net 3.5
• Download and install Dot net 4.0 (required if on windows server 2008 R2)
• http://go.microsoft.com/fwlink/?LinkID=212547
• or dot Net 4.5
• http://go.microsoft.com/fwlink/?LinkID=242919
• You need Powershell 3 and .net 4.5.2 (includes powershell 3.0) (http://go.microsoft.com/fwlink/?LinkId=317819)
• Dot Net 4.5.2 (requires restart)
• http://www.microsoft.com/en-us/download/details.aspx?id=42643
• Windows Management Framework 3.0
• http://www.microsoft.com/en-us/download/details.aspx?id=34595

• Download the W1 install and W1 demo database media from 
• https://mbs.microsoft.com/partnersource/global/deployment/downloads/product-releases/MSDNAV2013R2DownloadPage#MicrosoftDynamicsNAV2013R2Documentation
• Download LS Retail install 
• http://partners.lsretail.com/Partners/tabid/235/language/en-US/Default.aspx
• Download Latest version of LS Retail Data Director
• http://partners.lsretail.com/LSDataDirector/LatestVersion/tabid/872/language/en-US/Default.aspx
• 
  
• Extract DVD, Run setup
• Choose an installation
• If you choose install demo, it will install an instance of SQL 2012 Express with the demo company
• Run the LS install, it will extract all the files you need
• Restore the bak file over your demo company
• If you cannot install the bak file, you can do a database upgrade to install the objects into the existing demo company
• Open the demo database in the NAV dev environment
• Upload your partner keys
• Import the fob from the ls retail>objects directory
• Set SQL user as db_owner in SQL Management Studio
• Add current user to database in NAV Client
• Compile all objects
• Build server application objects
• If you choose custom
• Install All Modules (Deselect what you don't need)
• Install the LS Toolboxes
• Navigate to LS Retail\LS7.10.01\Setup\LS Nav 2013 (7.10.01) Toolbox
• Install the Client Components
• Install the Server Components
• Install Data Director
• Install Data Director>Tick 2013 RTC and 2013 SRV
• Tick first 2 and last 2
• Run the Data Director Configuration Tool>Click Get Config
• Launch the Development Environment
• Open Database
• Click All
• Click Tools>Compile
• Click Tools>Build Server Application Objects
• Launch Dynamics NAV Client
• Search for Users
• Add current user and any other users
• Restart NAV service
• Launch NAV, and use LS Retail
• Run the LS Help installer from Installs>LS>LS>Online Help
• Use Import/Export tool to import the xmls from rapidstart
• Retail Setup>Insert Default Data
• Setup default store in retail setup as HO
• The settings on HO will be used for all Terminals

How to Install the POS

• Launch NAV
• Search for the "Import Export Worksheet"
• For Each Profile (Functionality, Hardware, Menu, Interface, Style)
• Select the profile
• On the top bar, Click Import
• Select the LS Rapidstart Profile xml file
• LS7.10\LS Retail\LS7.10.01\Setup\LS Nav 2013 (7.1) Rapid Start
• For Each Terminal, ensure that the Functionality Profile and Hardware profile are set to ##DEFAULT
• Configure Data Director to push all data to POS location
• Go to physical POS device
• Install NAV server and client
• Install LS retail and toolboxes
• Install Data director
• Create retail user on that machine
• Give access to only that store and terminal
• Log in to main server
• Find job in LS scheduler
• Run job to push data
• Create shortcut that will run the "Run Client" which will load the POS screen

How to import base NAV and LS configuration data

• Search for Import Export Worksheet
• For each line on the worksheet, Import the profile.xml in the LS Retail setup
• Import and apply all Rapidstart Packages
• Update Packages as necessary
• 
  

Issues:

• Nav service starts, but then stops after a while
• In the NAV Admin 
• Enter Company Name
• Enter Time Zone
• Add the service account to the local admin group
• Install the NAV database components on the SQL server
• Install NAV Demo on the SQL Server
• Install NAV Demo on App server
• Connect to the instance at least once on the database server
• You did not install NAV as an administrator
• Ensure you are a local administrator, delete the demo database, run NAV setup and repair NAV.
• Disable the firewall on the database server and the application server or open the relevant ports
• Run the NAV Install repair
• You do not have permission to create a database
• Do not select any database components, and install all other components with no sql connection data
• Create the instance manually in the admin console after the install completes

Dynamics GP - Voided Sales transactions appear in Historical Stock Status Report

The Historical Stock Status Report and the Item Stock Inquiry read their data from the IV30300 table.

Whenever a transaction is voided, the corresponding record is supposed to be removed from the IV30300 table.

If records are showing on your Historical Stock Status that should not be there, the offending records need to be removed.

Look for any doc number and item number in SOP30300 that is not in IV30300
Also look for any Doc numbers in IV30300 with multiple Trxsorce values. Chances are, the lowest trxsorce values are supposed to be removed.

--delete voided SOP30300 stuck in IV30300
--------------------------------------------------------------
delete from iv30300 where dex_row_id in (
select distinct dex_row_id from(
SELECT     IV30300.TRXSORCE, IV30300.DOCTYPE, IV30300.DOCNUMBR, IV30300.DOCDATE, IV30300.HSTMODUL, IV30300.CUSTNMBR, IV30300.ITEMNMBR, IV30300.LNSEQNBR, IV30300.UOFM,
                      IV30300.TRXQTY, IV30300.UNITCOST, IV30300.EXTDCOST, IV30300.TRXLOCTN, IV30300.TRNSTLOC, IV30300.TRFQTYTY, IV30300.TRTQTYTY, IV30300.IVIVINDX, IV30300.IVIVOFIX,
                      IV30300.DECPLCUR, IV30300.DECPLQTY, IV30300.QTYBSUOM, IV30300.Reason_Code, IV30300.DEX_ROW_ID, SOP30200.VOIDSTTS
FROM         SOP30200 INNER JOIN
                      SOP30300 ON SOP30200.SOPTYPE = SOP30300.SOPTYPE AND SOP30200.SOPNUMBE = SOP30300.SOPNUMBE INNER JOIN
                      IV30300 ON SOP30300.SOPNUMBE = IV30300.DOCNUMBR AND SOP30300.ITEMNMBR = IV30300.ITEMNMBR
WHERE     (SOP30200.VOIDSTTS = 1)
) as a)


--Delete IV30300 that have no corresponding SOP30300 lines
-------------------------------------------------------------------

delete from iv30300 where dex_row_id in (
SELECT    IV30300.DEX_ROW_ID
FROM         (SELECT DISTINCT TOP (100) PERCENT DOCNUMBR, COUNT(ITEMNMBR) AS Expr1, DOCTYPE, ITEMNMBR, MIN(TRXSORCE) AS Expr2
                       FROM          (SELECT DISTINCT TOP (100) PERCENT DOCNUMBR, DOCTYPE, TRXSORCE, ITEMNMBR
                                               FROM          IV30300 AS IV30300_1
                                               GROUP BY DOCDATE, ITEMNMBR, DOCNUMBR, DOCTYPE, TRXSORCE
                                               ORDER BY DOCNUMBR) AS derivedtbl_1
                       GROUP BY DOCNUMBR, DOCTYPE, ITEMNMBR
                       HAVING      (DOCTYPE = 6) AND (COUNT(ITEMNMBR) > 1)
                       ORDER BY DOCNUMBR, ITEMNMBR) AS b INNER JOIN
                      IV30300 ON b.DOCTYPE = IV30300.DOCTYPE AND b.DOCNUMBR = IV30300.DOCNUMBR AND b.ITEMNMBR = IV30300.ITEMNMBR AND b.Expr2 = IV30300.TRXSORCE
                      )

Dynamics GP - View - EFT Vendors

SELECT     SY06000.VENDORID, PM00200.VENDNAME, PM00200.ADDRESS1, PM00200.ADDRESS2, PM00200.ADDRESS3, PM00200.CITY, PM00200.STATE, PM00200.VNDCNTCT, PM00200.ZIPCODE, PM00200.COUNTRY, PM00200.PHNUMBR1, PM00200.PHNUMBR2,
                  PM00200.PHONE3, PM00200.FAXNUMBR, PM00200.VNDCLSID, SY06000.EFTBankAcct, SY06000.EFTBankBranch, SY06000.EFTBankCode, SY06000.EFTBankBranchCode, SY06000.EFTBankType, SY06000.FRGNBANK, SY06000.BANKNAME,
                  SY06000.EFTBankCheckDigit, SY06000.IntlBankAcctNum, SY06000.CustVendCountryCode, SY06000.DeliveryCountryCode, SY06000.BNKCTRCD, SY06000.CBANKCD, SY06000.ADDRESS1 AS Expr1, SY06000.ADDRESS2 AS Expr2, SY06000.ADDRESS3 AS Expr3,
                  SY06000.ADDRESS4, SY06000.RegCode1, SY06000.RegCode2, SY06000.BankInfo7, SY06000.EFTTransitRoutingNo, SY06000.CURNCYID, SY06000.EFTTransferMethod, SY06000.EFTAccountType, SY06000.EFTTerminationDate, SY06000.ADRSCODE
FROM        SY06000 INNER JOIN
                  PM00200 ON SY06000.VENDORID = PM00200.VENDORID

Dynamics GP - View to reconcile Bank Transactions to GL

--------------------------------------------------------------------------------
CREATE VIEW [dbo].[BI_BankTrx_All]
AS

SELECT     a.CMRECNUM, a.sRecNum, a.RCRDSTTS, a.CHEKBKID, a.CMTrxNum, a.SRCDOCNUM, a.SRCDOCTYP, a.TRXDATE, a.GLPOSTDT, a.TRXAMNT, a.ORIGAMT, a.Checkbook_Amount, a.CURNCYID, a.CMLinkID, a.paidtorcvdfrom, a.DSCRIPTN, a.Recond, a.reconnumb,
                  a.VOIDED, a.VOIDDATE, dbo.GL00105.ACTNUMST, dbo.CM00100.DSCRIPTN AS ChkBkDesc
FROM        dbo.GL00105 INNER JOIN
                  dbo.CM00100 ON dbo.GL00105.ACTINDX = dbo.CM00100.ACTINDX RIGHT OUTER JOIN
                      (SELECT     CMRECNUM, sRecNum, RCRDSTTS, CHEKBKID, CMTrxNum, SRCDOCNUM, SRCDOCTYP, TRXDATE, GLPOSTDT, TRXAMNT, ORIGAMT, Checkbook_Amount, CURNCYID, CMLinkID, paidtorcvdfrom, DSCRIPTN, Recond, CAST(RECONUM AS varchar(50))
                                         AS reconnumb, VOIDED, VOIDDATE
                       FROM        dbo.CM20200
                       UNION
                       SELECT     CMRECNUM, sRecNum, RCRDSTTS, CHEKBKID, RCPTNMBR, SRCDOCNUM, SRCDOCTYP, receiptdate, GLPOSTDT, RCPTAMT, ORIGAMT, Checkbook_Amount, CURNCYID, CMLinkID, RcvdFrom, DSCRIPTN, DEPOSITED,
                                         CAST(depositnumber AS varchar(50)) AS depnumb, VOIDED, VOIDDATE
                       FROM        dbo.CM20300) AS a ON dbo.CM00100.CHEKBKID = a.CHEKBKID

GO
----------------------------------------------------------------------------

create view [dbo].[BI_Posted_GL_Trx]
as

select
YEAR1 Trx_Year,
TRXDATE Trx_Date,
JRNENTRY Journal_Entry,
ORTRXSRC Originating_TRX_Source,
REFRENCE Reference,
ORMSTRID Originating_Master_ID,
ORMSTRNM Originating_Master_Name,
ORDOCNUM Originating_Doc_Number,
DEBITAMT Debit_Amount,
CRDTAMNT Credit_Amount,
ACTNUMST Account_Number,
ACTDESCR Account_Description,
ACCATDSC Account_Category,
CURNCYID Currency_ID,
USWHPSTD User_Who_Posted

from
(select ACTINDX, OPENYEAR YEAR1, TRXDATE, JRNENTRY, ORTRXSRC,
        REFRENCE, ORDOCNUM, ORMSTRID, ORMSTRNM, DEBITAMT,
        CRDTAMNT, CURNCYID, USWHPSTD
 from GL20000
 where SOURCDOC not in ('BBF','P/L') and VOIDED = 0

 union all

 select ACTINDX, HSTYEAR YEAR1, TRXDATE, JRNENTRY, ORTRXSRC,
        REFRENCE, ORDOCNUM, ORMSTRID, ORMSTRNM, DEBITAMT,
        CRDTAMNT, CURNCYID, USWHPSTD
 from GL30000
 where SOURCDOC not in ('BBF','P/L') and VOIDED = 0) GL

inner join GL00105 GM
     on GL.ACTINDX = GM.ACTINDX
inner join GL00100 GA
     on GL.ACTINDX = GA.ACTINDX
inner join GL00102 C
     on GA.ACCATNUM= C.ACCATNUM

GO
----------------------------------------------------------------------------
CREATE VIEW [dbo].[BI_Posted_GL_Trx_smry]
AS
SELECT     Trx_Year, Trx_Date, Journal_Entry, Originating_TRX_Source, Reference, Originating_Master_ID, Originating_Master_Name, Originating_Doc_Number, SUM(Debit_Amount) AS DebitAmt,
                      SUM(Credit_Amount) AS CreditAmt, Account_Number, Account_Description, Account_Category, Currency_ID, User_Who_Posted, CASE WHEN LEFT(originating_doc_number, 3)
                      = 'TRF' THEN 0 WHEN SUM(debit_amount) > 0 AND SUM(credit_amount) > 0 THEN SUM(debit_amount) ELSE SUM(Debit_Amount - Credit_Amount) END AS AbsAmt
FROM         dbo.BI_Posted_GL_Trx
GROUP BY Trx_Year, Trx_Date, Journal_Entry, Originating_TRX_Source, Reference, Originating_Master_ID, Originating_Master_Name, Originating_Doc_Number, Account_Number, Account_Description,
                      Account_Category, Currency_ID, User_Who_Posted


GO

--------------------------------------------------------------------------------
CREATE VIEW [dbo].[BI_Rec_GL_vs_Bank]
AS
SELECT     dbo.BI_Posted_GL_Trx_smry.Trx_Year, dbo.BI_Posted_GL_Trx_smry.Trx_Date, dbo.BI_Posted_GL_Trx_smry.Journal_Entry, dbo.BI_Posted_GL_Trx_smry.Originating_TRX_Source, dbo.BI_Posted_GL_Trx_smry.Reference,
                  dbo.BI_Posted_GL_Trx_smry.Originating_Master_ID, dbo.BI_Posted_GL_Trx_smry.Originating_Master_Name, dbo.BI_Posted_GL_Trx_smry.Originating_Doc_Number, dbo.BI_Posted_GL_Trx_smry.DebitAmt, dbo.BI_Posted_GL_Trx_smry.CreditAmt,
                  dbo.BI_Posted_GL_Trx_smry.Account_Number, dbo.BI_Posted_GL_Trx_smry.Account_Description, dbo.BI_Posted_GL_Trx_smry.Account_Category, dbo.BI_Posted_GL_Trx_smry.Currency_ID, dbo.BI_Posted_GL_Trx_smry.User_Who_Posted,
                  dbo.BI_Posted_GL_Trx_smry.AbsAmt, dbo.BI_BankTrx_All.*
FROM        dbo.BI_Posted_GL_Trx_smry INNER JOIN
                      (SELECT DISTINCT ACTNUMST
                       FROM        dbo.BI_BankTrx_All AS BI_BankTrx_All_1) AS bankacts ON dbo.BI_Posted_GL_Trx_smry.Account_Number = bankacts.ACTNUMST LEFT OUTER JOIN
                  dbo.BI_BankTrx_All ON dbo.BI_Posted_GL_Trx_smry.Originating_Doc_Number = dbo.BI_BankTrx_All.CMTrxNum

GO

--------------------------------------------------------------------------------------

CREATE VIEW [dbo].[BI_Rec_Bank_vs_GL]
AS
SELECT     dbo.BI_Posted_GL_Trx_smry.Trx_Year, dbo.BI_Posted_GL_Trx_smry.Trx_Date, dbo.BI_Posted_GL_Trx_smry.Journal_Entry, dbo.BI_Posted_GL_Trx_smry.Originating_TRX_Source, dbo.BI_Posted_GL_Trx_smry.Reference,
                  dbo.BI_Posted_GL_Trx_smry.Originating_Master_ID, dbo.BI_Posted_GL_Trx_smry.Originating_Master_Name, dbo.BI_Posted_GL_Trx_smry.Originating_Doc_Number, dbo.BI_Posted_GL_Trx_smry.DebitAmt, dbo.BI_Posted_GL_Trx_smry.CreditAmt,
                  dbo.BI_Posted_GL_Trx_smry.Account_Number, dbo.BI_Posted_GL_Trx_smry.Account_Description, dbo.BI_Posted_GL_Trx_smry.Account_Category, dbo.BI_Posted_GL_Trx_smry.Currency_ID, dbo.BI_Posted_GL_Trx_smry.User_Who_Posted,
                  dbo.BI_Posted_GL_Trx_smry.AbsAmt, dbo.BI_BankTrx_All.CMRECNUM, dbo.BI_BankTrx_All.sRecNum, dbo.BI_BankTrx_All.RCRDSTTS, dbo.BI_BankTrx_All.CHEKBKID, dbo.BI_BankTrx_All.CMTrxNum, dbo.BI_BankTrx_All.SRCDOCNUM,
                  dbo.BI_BankTrx_All.SRCDOCTYP, dbo.BI_BankTrx_All.TRXDATE, dbo.BI_BankTrx_All.GLPOSTDT, dbo.BI_BankTrx_All.TRXAMNT, dbo.BI_BankTrx_All.ORIGAMT, dbo.BI_BankTrx_All.Checkbook_Amount, dbo.BI_BankTrx_All.CURNCYID, dbo.BI_BankTrx_All.CMLinkID,
                  dbo.BI_BankTrx_All.paidtorcvdfrom, dbo.BI_BankTrx_All.DSCRIPTN, dbo.BI_BankTrx_All.Recond, dbo.BI_BankTrx_All.reconnumb, dbo.BI_BankTrx_All.VOIDED, dbo.BI_BankTrx_All.VOIDDATE, dbo.BI_BankTrx_All.ACTNUMST, dbo.BI_BankTrx_All.ChkBkDesc
FROM        dbo.BI_Posted_GL_Trx_smry INNER JOIN
                      (SELECT DISTINCT ACTNUMST
                       FROM        dbo.BI_BankTrx_All AS BI_BankTrx_All_1) AS bankacts ON dbo.BI_Posted_GL_Trx_smry.Account_Number = bankacts.ACTNUMST RIGHT OUTER JOIN
                  dbo.BI_BankTrx_All ON dbo.BI_Posted_GL_Trx_smry.Originating_Doc_Number = dbo.BI_BankTrx_All.CMTrxNum

GO
------------------------------------------------------------------------------------------------

eOne Flexicoder - Flexicoder does not recode distributions automatically

Issue:
After a user enters a transaction, the flexicoder triggers seem to not fire, and the distributions are not updated

Solution:
Most likely, the client that is entering the transaction has a bad Flexicoder install.
Reinstall flexicoder on the client using the admin account, and grant full access to the Dynamics GP program files folder.


Related Issues:
Flexicoder will recode distributions correctly under the following conditions:

• Flexicoder is correctly installed on the server
• Flexicoder is correctly installed on the client
• Flexicoder has been correctly configured to recode segments based on some criteria
• Flexicoder has been correctly configured to recode specific document types
• The document is saved
• The document is posted

Flexicoder will not recode distributions correctly if:

• The user is not an administrative user, and does not have access to the gp program files folder
• The installation may fail silently during install if you do not have full access
• The installation may fail if another instance of GP is running in the bckground
• An older version of the dynamics or company database is restored after installing flexicoder
• The user posting transactions does not have flexicoder correctly installed and configured
• The final account combination does not exist in the chart of accounts,and you have not configured automatic account creation

Dynamics GP - Workflow - Cannot add active directory user as manager to workflow

Dynamics GP workflow uses the Active Directory Manager field to determine the user's manager.

The user account must have a First name, and a manager defined in Active Directory to allow the user to be selected as a manager on a workflow.

Additional Info
https://community.dynamics.com/gp/b/dynamicsgp/archive/2014/10/09/workflow-2-0-39-s-workflow-user-setup-window-options-part-3-workflow-role.aspx

Dynamics GP - Encumbrance Management - Ticking enable encumbrance hangs, freezes, not responding

Issue:

After installing the Encumbrance management module, you have to enable encumbrance from tools>setup>purchasing.

When you tick enable encumbrance management, and fill in the budget information, the blue bar does not move, and the system appear to freeze or hang

Cause:

It will attempt to create encumbrances for all open GL journals.

Then it will attempt to create encumbrances for all open PO's and receipts.

If you have a large number of unposted journals or open po's, this process may take an extremely long time.

Resolution:

1. Post all journals
2. Close as many PO's as possible
3. OR you can just leave it running, and eventually it will finish creating all the encumbrances required. (during our testing, it created ~5 encumbrances / second)

Dynamics GP - Integration Manager - ADO Field is Nothing

Cause:
You have duplicate column header names.


Solution:
Rename the column headers in your data file to have unique names.

SQL 2012 - MSDN, Partner, Azure Default License Expires after 120 days

Issue:
By default, when you download the SQL2012 installation files, the correct key is included in the setup files.

However, during the installation, by default, it uses the evaluation key.
After 120 days, the SQL 2012 instance throws a license expiration error, and refuses connections.


Solution:
Enter the correct key and update the installation using the correct key.
By default, the SQL2012 install ALWAYS installs the evaluation keys. You cannot change them.
You must register SQL using your correct keys after it has completed installing.

Dynamics GP - SQL View - Sales Header and Detail, Work and History

--------------------------------------
--Sales Open
-------------------------------------

SELECT     TOP (100) PERCENT dbo.SOP10100.SOPNUMBE AS [SOP Number], dbo.SOP10100.DOCDATE AS [Document Date], dbo.SOP10100.GLPOSTDT AS [GL Post Date],
                  CASE WHEN SOP10100.soptype = 4 THEN SOP10200.XTNDPRCE * - 1 ELSE SOP10200.xtndprce END AS Sales, CASE WHEN SOP10100.soptype = 4 THEN SOP10200.EXTDCOST * - 1 ELSE SOP10200.extdcost END AS Costs,
                  CASE WHEN SOP10100.soptype = 4 THEN SOP10200.quantity * - 1 ELSE SOP10200.quantity END AS Qty, dbo.SOP10100.CUSTNMBR AS [Customer Number],
                  CASE WHEN SOP10100.soptype = 1 THEN 'Quote' WHEN SOP10100.soptype = 2 THEN 'Order' WHEN SOP10100.soptype = 3 THEN 'Invoice' WHEN SOP10100.soptype = 4 THEN 'Return' WHEN SOP10100.soptype = 5 THEN 'BackOrder' WHEN SOP10100.soptype = 6
                   THEN 'FulfilmentOrder' END AS [SOP Type], dbo.IV40400.ITMCLSDC AS [Item Class Description], dbo.IV40400.ITMCLSCD AS [Item Class Code], dbo.RM00101.CUSTNAME AS [Customer Name], dbo.RM00101.CUSTCLAS AS [Customer Class],
                  dbo.SOP10200.QTYBSUOM AS [Base Unit Of Measure], (CASE WHEN SOP10100.soptype = 4 THEN SOP10200.quantity * - 1 ELSE SOP10200.quantity END) * dbo.SOP10200.QTYBSUOM AS [Units Sold], dbo.SOP10200.STATE, dbo.SOP10200.PRSTADCD,
                  dbo.SOP10100.SLPRSNID AS [Salesperson ID], dbo.IV00101.ITEMNMBR AS [Item Number], dbo.IV00101.ITEMDESC AS [Item Description], dbo.SOP10100.ShipToName, dbo.RM00101.COMMENT1, dbo.RM00101.SLPRSNID AS Customer_Slprsnid,
                  CASE WHEN sop10100.soptype = 4 THEN sop10200.trdisamt * - 1 ELSE sop10200.trdisamt END AS TrDisamt, CASE WHEN sop10100.soptype = 4 THEN sop10200.mrkdnamt * - 1 ELSE sop10200.mrkdnamt END AS Mrkdnamt,
                  CASE WHEN sop10100.soptype = 4 THEN sop10100.trdisamt * - 1 ELSE sop10100.trdisamt END AS DocDiscount
FROM        dbo.SOP10100 INNER JOIN
                  dbo.SOP10200 ON dbo.SOP10100.SOPTYPE = dbo.SOP10200.SOPTYPE AND dbo.SOP10100.SOPNUMBE = dbo.SOP10200.SOPNUMBE INNER JOIN
                  dbo.IV00101 ON dbo.SOP10200.ITEMNMBR = dbo.IV00101.ITEMNMBR INNER JOIN
                  dbo.IV40400 ON dbo.IV00101.ITMCLSCD = dbo.IV40400.ITMCLSCD INNER JOIN
                  dbo.RM00101 ON dbo.SOP10100.CUSTNMBR = dbo.RM00101.CUSTNMBR
WHERE     (dbo.SOP10100.VOIDSTTS = 0) AND (dbo.SOP10200.CMPNTSEQ = 0) AND (dbo.SOP10100.SOPTYPE IN (3, 4))

--------------------------------------
--Sales History
-------------------------------------
SELECT     TOP (100) PERCENT dbo.SOP30200.SOPNUMBE AS [SOP Number], dbo.SOP30200.DOCDATE AS [Document Date], dbo.SOP30200.GLPOSTDT AS [GL Post Date],
                  CASE WHEN sop30200.soptype = 4 THEN sop30300.XTNDPRCE * - 1 ELSE sop30300.xtndprce END AS Sales, CASE WHEN sop30200.soptype = 4 THEN sop30300.EXTDCOST * - 1 ELSE sop30300.extdcost END AS Costs,
                  CASE WHEN sop30200.soptype = 4 THEN sop30300.quantity * - 1 ELSE sop30300.quantity END AS Qty, dbo.SOP30200.CUSTNMBR AS [Customer Number],
                  CASE WHEN sop30200.soptype = 1 THEN 'Quote' WHEN sop30200.soptype = 2 THEN 'Order' WHEN sop30200.soptype = 3 THEN 'Invoice' WHEN sop30200.soptype = 4 THEN 'Return' WHEN sop30200.soptype = 5 THEN 'BackOrder' WHEN sop30200.soptype = 6 THEN
                   'FulfilmentOrder' END AS [SOP Type], dbo.IV40400.ITMCLSDC AS [Item Class Description], dbo.IV40400.ITMCLSCD AS [Item Class Code], dbo.RM00101.CUSTNAME AS [Customer Name], dbo.RM00101.CUSTCLAS AS [Customer Class],
                  dbo.SOP30300.QTYBSUOM AS [Base Unit Of Measure], (CASE WHEN sop30200.soptype = 4 THEN sop30300.quantity * - 1 ELSE sop30300.quantity END) * dbo.SOP30300.QTYBSUOM AS [Units Sold], dbo.SOP30300.STATE, dbo.SOP30300.PRSTADCD,
                  dbo.SOP30200.SLPRSNID AS [Salesperson ID], dbo.IV00101.ITEMNMBR AS [Item Number], dbo.IV00101.ITEMDESC AS [Item Description], dbo.SOP30200.ShipToName, dbo.RM00101.COMMENT1, dbo.RM00101.SLPRSNID AS Customer_Slprsnid,
                  CASE WHEN sop30200.soptype = 4 THEN sop30300.trdisamt * - 1 ELSE sop30300.trdisamt END AS Trdisamt, CASE WHEN sop30200.soptype = 4 THEN sop30300.mrkdnamt * - 1 ELSE sop30300.mrkdnamt END AS Mrkdnamt,
                  CASE WHEN sop30200.soptype = 4 THEN sop30200.trdisamt * - 1 ELSE sop30200.trdisamt END AS DocDiscount
FROM        dbo.SOP30200 INNER JOIN
                  dbo.SOP30300 ON dbo.SOP30200.SOPTYPE = dbo.SOP30300.SOPTYPE AND dbo.SOP30200.SOPNUMBE = dbo.SOP30300.SOPNUMBE INNER JOIN
                  dbo.IV00101 ON dbo.SOP30300.ITEMNMBR = dbo.IV00101.ITEMNMBR INNER JOIN
                  dbo.IV40400 ON dbo.IV00101.ITMCLSCD = dbo.IV40400.ITMCLSCD INNER JOIN
                  dbo.RM00101 ON dbo.SOP30200.CUSTNMBR = dbo.RM00101.CUSTNMBR
WHERE     (dbo.SOP30200.VOIDSTTS = 0) AND (dbo.SOP30300.CMPNTSEQ = 0) AND (dbo.SOP30200.SOPTYPE IN (3, 4))

How to Import Excel 2003 32 bit file to SQL 2008 32 bit

sp_configure 'show advanced options', 1; RECONFIGURE; GO sp_configure 'Ad Hoc Distributed Queries', 1; RECONFIGURE; GO

Get the latest ACE OLE DB Priovider
https://www.microsoft.com/en-us/download/confirmation.aspx?id=54920


Related errors:
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "Unspecified error".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".

Solution1 

Change the TEMP and TMP variables on the machine to a public location, allow all users full access

(Right click my computer>properties>Advanced>Environment Variables> Change the paths)

Original Post
http://blogs.msdn.com/b/spike/archive/2008/07/23/ole-db-provider-microsoft-jet-oledb-4-0-for-linked-server-null-returned-message-unspecified-error.aspx


For 32-Bit Excel, and SQL use
--------------------------------------------------------------
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
    'Excel 8.0;HDR=YES;Database=C:\temp\test.xls',
    'select * from [sheet1$]')




If you continue to get the error, try installing the 2007 data access components. A Full install of office should work just as well.
Run windows update and install all service packs
http://www.microsoft.com/en-us/download/confirmation.aspx?id=23734

If you install the 2007 Data Access Components, you can use this command to open the files.
Both methods will work.

SELECT * FROM OPENROWSET ( 'Microsoft.ACE.OLEDB.12.0',
'Excel 12.0; Database=C:\test\test.xls;HDR=Yes', 'Select * from [sheet1$]' )

How to install 64-bit Data Access Component Engine on a machine with 32-bit office components.

Error:
You cannot install the 64-bit version of Microsoft access Database engine 2010 because you currently have 32-bit office products installed

Solution:

Install in passive mode

Remove the registry reference to mmo.dll

Original post

http://knowledge.autodesk.com/support/autocad-civil-3d/troubleshooting/caas/sfdcarticles/sfdcarticles/How-to-install-64-bit-Microsoft-Database-Drivers-alongside-32-bit-Microsoft-Office.html

Related Errors:
[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

SQL - How to get the day name of a date

select DATENAME( dw, CONVERT( date, getdate(), 103 ) ) as BDay

Replace getdate() with your field

Dynamics GP - How to fix discrepancies between GL and Subledger

• Smartlist the GL for the transaction Value to determine if the transactions may have gone to the wrong account.
• If a transaction exists in the Subledger, and not in the GL
• Manually enter a GL journal for the distribution.
• If a transaction exists in the GL and not in the subledger
• Void/reverse the GL transaction, and re-enter the transaction from the subledger
• If a transaction is not being displayed in your Bank rec, but is in the gl and not in the subledger
• Void/reverse the GL transaction, and re-enter the transaction from the Bank transaction entry screen.
• If a transaction is not being displayed in your Bank rec, but is in the gl and in the subledger
• Void/reverse the Subledger transaction, and re-enter the transaction from the Bank transaction entry screen.

Dynamics GP - Security Task Setup Window Locations. Where is the task located?

Reason Codes - Microsoft Dynamics GP>Windows>Company
Reason Code Setup - Microsoft Dynamics GP>Windows>Company

Dynamics GP - How to Reprint Outstanding Bank Transactions Before and After GP 2013 R2

Before GP2013 R2

This method using the smartlist

http://gp.rosebizincblogs.com/2010/05/outstanding-bank-transactions.html


After GP2013R2

There is a new report available
https://community.dynamics.com/gp/b/gpteamblog/archive/2014/05/15/microsoft-dynamics-gp-2013-r2-feature-of-the-day-reprint-outstanding-transaction-in-bank-reconciliation.aspx

Dynamics GP - picture printing is not available without the desktop experience feature

Enable the Desktop Experience Feature on windows server


go to Server Manager> Add Features>Features>User Interfaces and Infrastructure>Tick Desktop Experience

Dynamics GP - Smartlist Export Error - "Excel cannot open the file [filename] because the file format or file extension is not valid."

We got this error happening because one of the text fields was using "[" and "]" and "(" and ")" characters that was causing the smartlist to export incorrectly.

We removed the characters and everything exported fine.

Dynamics NAV - Warehouse Handling is Required

Check The fields on the Warehouse Tab, set them to true

1. Require Receive

2. Require Pick fields





Original Solution
http://dynamicsuser.net/forums/p/29337/154874.aspx

Dynamics GP - Modifier and VBA Visual Basic - ODBC Connection

Private Sub GetItemCost()

Dim objRec
Dim objConn
Dim cmdString

Set objRec = CreateObject("ADODB.Recordset")
Set objConn = CreateObject("ADODB.Connection")

objConn.ConnectionString = "Provider=SQLOLEDB;Data Source=GP\GPSQL;Initial Catalog=TWO;User Id=sa;Password=pw"
objConn.Open


cmdString = "Select CURRCOST from IV00101 where (ITEMNMBR='" + ItemNumber + "')"

Set objRec = objConn.Execute(cmdString)

If objRec.EOF = True Then
ManufacturingOrderReceiptEn.CurrencyM105 = ""
Else
ManufacturingOrderReceiptEn.CurrencyM105 = objRec!CURRCOST
End If
objConn.Close
End Sub

Ensure that you add all fields you need to VB
Ensure you run GP as administrator or the VB will not save
Call this in the body to execute for each record
ManufacturingOrderReceiptEn.CurrencyM105 is an empty calculated field
You must put the calculated field in the BODY or the appropriate section where the VB code is running

Dynamics GP - Business Analyzer - Reporting Services Web Service address

http://localhost/ReportServer_GP

Replace localhost with your server name

Dynamics GP - SQL View - Inventory Base UofM

SELECT     dbo.IV00101.ITEMNMBR, dbo.IV00101.UOMSCHDL, dbo.IV40201.BASEUOFM, dbo.IV40201.UMDPQTYS
FROM         dbo.IV00101 INNER JOIN
                      dbo.IV40201 ON dbo.IV00101.UOMSCHDL = dbo.IV40201.UOMSCHDL


------------------------------------------------------------------------------------
--UofM Conversion Table

SELECT        IV00101.ITEMNMBR, IV00101.UOMSCHDL, IV40201.BASEUOFM, IV40201.UMDPQTYS, IV40202.EQUIVUOM, IV40202.EQUOMQTY, IV40202.QTYBSUOM, IV40202.UOFM
FROM            IV00101 INNER JOIN
                         IV40201 ON IV00101.UOMSCHDL = IV40201.UOMSCHDL INNER JOIN
                         IV40202 ON IV40201.UOMSCHDL = IV40202.UOMSCHDL

eOne Smartconnect - Information Node taGLTransactionHeaderInsert not found in GLTransactionType. The element node is required for this transaction type.

Your map is grouping incorrectly on fields that are not the keys identified in the map header.

Either fix the keys in the map header, or change the grouping fields in the map.

SQL 2008 R2 - How to setup log shipping

Pre-requisites

• User account being used to setup the log shipping have sysadmin role in sql
• Primary SQL server
• Primary Backup Folder that Primary and Secondary servers can access
• Primary database set to Full or differential recovery
• Secondary SQL Server
• Secondary Backup Folder  that Primary and Secondary servers can access

Setup Log Shipping

• Right click on the database you want to ship on the primary server>Tasks>Ship Transaction Logs
• Add secondary server
• The easiest options is to let it generate a full backup and restore it to the secondary (first option)
• Restore Transaction Log in norecovery mode if you don't need to access it
• Restore Transaction Log in standby mode if you need to access it

To bring a restoring database from NORECOVERY to RECOVERY

RESTORE DATABASE database_name WITH RECOVERY

If you script out the whole thing to a query window, the script has two parts, one for the primary, one for the secondary.

Run each part of the script manually on each respective server to create the jobs if it doesn't do it automatically.

Good guide here
http://www.mssqltips.com/sqlservertip/2301/step-by-step-sql-server-log-shipping/

Dynamics GP - PO Prepayments

PO Prepayments allows you to create Check Payments against PO's as if they were invoices.

1. Setup PO Prepayments
1. Tools>Setup>Purchasing>PO Processing
2. Tick Allow Purchase Order Prepayments near the bottom of the window
1. If you tick create manual prepayment, it allows you to print the check directly from the PO screen when it is closed
2. If you do not tick create manual payment, you must use the check payment process to generate the payment
3. This enables the PO Prepayment field on the PO Entry Window on the left by the remaining subtotal
2. Create a PO
3. Enter a value in the prepayment field, save and close
4. Create Check Batch
1. Transactions>Purchasing>Edit check Batch
2. Create batch, tick Purchasing Prepayment Batch on the top right hand side
3. Close and reopen the Edit Check Batch window
4. The batch should only display PO's that can be prepaid
5. Select PO's, print checks
5. Once a payment is made against a PO, you cannot edit the PO
6. To edit the PO, you must remove the payment.

SQL 2008 R2 - Log Shipping Error - The log shipping secondary database TWO has restore threshold of 45 minutes and is out of sync

Problem:
This occurs when the logs are unable to be restored to the secondary database, and the secondary database falls out of sync or misses a log.

Solution:
Reconfigure log shipping completely.

Dynamics GP - How to reset all Average Costed Inventory Costs at a specific Historical Date

Adjust all stock out at that date to ensure your HITB reads 0

--Run this to identify all of the cost rows that need to be updated, change the date accordingly.
--copy the UpdOnHnd column, and run the scripts to update each row.

SELECT iv10200.qtyonhnd, 
       iv10200.itemnmbr, 
       iv10200.trxloctn, 
       iv10200.daterecd, 
       iv10200.rctseqnm, 
       iv10200.dex_row_id, 
       iv10200.unitcost, 
       'update iv10200 set qtyonhnd = 0 where dex_row_id = ' 
       + Cast(iv10200.dex_row_id AS VARCHAR(50)) AS UpdOnHnd, 
       'delete from see30303 where ITEMNMBR = ' 
       + '''' 
       + Rtrim(Cast(iv10200.itemnmbr AS VARCHAR(50))) 
       + ''''                                    AS DelHITB 
FROM   iv10200 
       INNER JOIN (SELECT itemnmbr, 
                          Max(expr1) AS MaxSeq 
                   FROM   (SELECT itemnmbr, 
                                  Max(rctseqnm) AS Expr1, 
                                  daterecd 
                           FROM   iv10200 AS IV10200_1 
                           GROUP  BY itemnmbr, 
                                     daterecd 
                          --                          HAVING ( daterecd <= CONVERT(DATETIME,  
                          --                                               '2014-06-30 00:00:00'  
                          --                                                , 102) ) 
                          ) AS atdate 
                   GROUP  BY itemnmbr) AS MaxLine 
               ON iv10200.itemnmbr = MaxLine.itemnmbr 
                  AND iv10200.rctseqnm = MaxLine.maxseq 
--WHERE  ( iv10200.qtyonhnd <> 0 )  



--If you HITB is showing 0 quantity, but +ve and -ve values, and you do not need the history
--Adjust the stock to 0
--Delete all HITB records for the item (Copy the DelHITB column)
delete from see30303 where ITEMNMBR = 'I0001'