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.