Dynamics GP eOne SmartConnect - Could not log on to SQL server

If you get any errors pertaining to logging on or connecting to SQL, chances are the default Smartconnect user is not setup correctly.

• Install dot net 4.5
• Turn off all firewalls
• Log in to SQL server
• Delete the Smartconnect user (Or confirm the password by logging into SQL with the smartconnect account)
• Reinstall Smartconnect
• Log into GP
• Include Code
• GP>Smartconnect>Build cache
• Launch Smartconnect

Dynamics GP - In-Transit Transfer Tables and Views

https://www.gptablereference.com/2010/Group/Field%20Service


SVC00700 - In-Transit Transfer Header Work
SVC00701 - In-Transit Transfer Line Work

IV30300 - Item transaction detail
SVC30700 - In-Transit Transfer History Hdr
SVC30701 - In-Transit Transfer History Dtl



/****** Object:  View [dbo].[BI_ITTRHist]    Script Date: 03/29/2017 11:16:42 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[BI_ITTRHist]
AS
SELECT     dbo.SVC30700.ORDDOCID, dbo.SVC30700.RFRNCDOC, dbo.SVC30700.LOCNCODE, dbo.SVC30700.TRNSFLOC, dbo.IV00101.ITEMNMBR, dbo.IV00101.ITEMDESC,
                      dbo.SVC30701.UOFM, dbo.SVC30701.TRNSFQTY, dbo.SVC30701.QTYFULFI, dbo.SVC30701.QTYSHPPD, dbo.SVC30701.QTY_To_Receive, dbo.SVC30701.QTYRECVD,
                      dbo.SVC30701.LNITMSEQ, dbo.SVC30701.STATUS, dbo.SVC30701.QTYBSUOM, dbo.SVC30701.TRNSFLOC AS Expr1, dbo.SVC30701.TRNSTLOC,
                      dbo.SVC30701.Reason_Code, dbo.SVC30701.ITLOCN
FROM         dbo.SVC30700 INNER JOIN
                      dbo.SVC30701 ON dbo.SVC30700.ORDDOCID = dbo.SVC30701.ORDDOCID INNER JOIN
                      dbo.IV00101 ON dbo.SVC30701.ITEMNMBR = dbo.IV00101.ITEMNMBR

GO

Dynamics GP - Unable to open customizations dictionary

Problem:
This is happening because someone has Dynamics GP open on their machine.

Solution:
Make sure no one is actually using the system, or doing work before kicking them out, or just killing GP.
Any one of the following will work

1. Ask all users to log out and CLOSE GP
2. Disable the network connection to kill all connections to the file, then re-enable it
3. If you're on a terminal server, open the task manager, show processes from all users and kill all instances of dynamics*32.exe

Dynamics GP - Smartlist Builder - You do not have security privileges to view all of the tables used in this SmartList

Problem:
Users get the error
"You do not have security privileges to view all of the tables used in this SmartList"
Even though you have set the SQL table security, and the Smartlist security correctly


Solution:
You will need to add permissions to the View SmartLists with SQL Tables operation. Here are the settings under Security Tasks:

Product: SmartList Builder
Type: SmartList Builder Permissions
Series: SmartList Builder Permissions or SmartList Builder (depends on the Service Pack)
Operations: View SmartLists with SQL Tables

Original Post
http://victoriayudin.com/2008/10/08/granting-access-to-a-new-smartlist-in-gp-10/

Dynamics GP - How to Import tax details on PO's

Integration Manager cannot do it.

You can update the PO tables in the background, then create a macro to cycle through all the PO's and recalculate the tax on each PO.

POP10100 - PO Hdr
POP10110 - PO Dtl

Dynamics GP - Integration Manager - - PO Integration - An item with the same key has already been added

A valid exchange rate for the date period of the PO has not been setup.

Check your currency setups and ensure that the currency is set to search for rates from a previous date to avoid having to set rates for each specific date.

Dynamics GP - How to update Item Decimal Places, Currency Decimal Places and Standard Price Lists without wiping out all the prices

This script will update ALL items and ALL UofM schedules. Use a WHERE if you need to update specific ranges to different values.
Do not use this on a live environment with transactions being processed
This is meant for setup only in a new environment or test environment.
Run check links after to ensure the item is setup correctly.



update IV40201 set UMDPQTYS = 3 --Sets all UofM dp's to 2
UPDATE IV00101 SET DECPLQTY=3 --Sets all Inventory DP's to 2
update IV00108 set TOQTY = 999999999999.99, FROMQTY = 0.01 --Sets all pricing ranges to the correct DP's

update sop10200 set decplqty = 3 --fix open sop line items
update POP10110 set decplqty = 3 --fix open pop line items
update iv10001 set decplqty=3 --fix open inventory trx line items

Rebuild any Inventory BOMs
Rebuild any MFG BOMS
Update iv10402 to fix the UofM if it has changed, and the QTYTO to set the correct FROMQTY and TOQTY
Update iv00106 to fix the UofM if it has changed


To change item currency decimals, refer to this article
http://dynamicsgpblogster.blogspot.com/2011/02/from-newsgroups-changing-item-currency.html

For multicurrency transactions, the decplcur values are as follows
Number = decimal places
7 = 0
8 = 1
9 = 2
10 = 3
11 = 4
12 = 5

The total scripts i used to update the currencies dp's to 5dp for all currencies for this item were as follows.
These scripts are repeated for each table identified from

SELECT * FROM sysobjects o, syscolumns c
WHERE o.id = c.id AND o.type = 'U' AND c.name = 'DECPLCUR'
ORDER BY o.name

that has an itemnmbr and a decplcur field
--This is for the functional currency
--select 'IV00101', * from IV00101 where itemnmbr = 'Item001' and decplcur <6
update IV00101 set decplcur = 6 where itemnmbr ='Item001' and decplcur <6

--This is for the multicurrency
--select 'IV00101', * from IV00101 where itemnmbr = 'Item001' and decplcur >6
update IV00101 set decplcur = 12 where itemnmbr ='Item001' and decplcur >6


Therefore a complete example of all the scripts i used for this one item are

update IV00101 set decplcur = 6 where itemnmbr ='Item001' and decplcur <6
update IV00105 set decplcur = 6 where itemnmbr ='Item001' and decplcur <6
update IV10001 set decplcur = 6 where itemnmbr ='Item001' and decplcur <6
update IV30300 set decplcur = 6 where itemnmbr ='Item001' and decplcur <6
update IV30701 set decplcur = 6 where itemnmbr ='Item001' and decplcur <6
update IV50300 set decplcur = 6 where itemnmbr ='Item001' and decplcur <6
update IVC10101 set decplcur = 6 where itemnmbr ='Item001' and decplcur <6
update IVC30102 set decplcur = 6 where itemnmbr ='Item001' and decplcur <6
update POP10110 set decplcur = 6 where itemnmbr ='Item001' and decplcur <6
update POP10310 set decplcur = 6 where itemnmbr ='Item001' and decplcur <6
update POP30110 set decplcur = 6 where itemnmbr ='Item001' and decplcur <6
update POP30310 set decplcur = 6 where itemnmbr ='Item001' and decplcur <6
update PT10000 set decplcur = 6 where itemnmbr ='Item001' and decplcur <6
update SEE30303 set decplcur = 6 where itemnmbr ='Item001' and decplcur <6
update SOP10200 set decplcur = 6 where itemnmbr ='Item001' and decplcur <6
update SOP30300 set decplcur = 6 where itemnmbr ='Item001' and decplcur <6
update IV00101 set decplcur = 12 where itemnmbr ='Item001' and decplcur >6
update IV00105 set decplcur = 12 where itemnmbr ='Item001' and decplcur >6
update IV10001 set decplcur = 12 where itemnmbr ='Item001' and decplcur >6
update IV30300 set decplcur = 12 where itemnmbr ='Item001' and decplcur >6
update IV30701 set decplcur = 12 where itemnmbr ='Item001' and decplcur >6
update IV50300 set decplcur = 12 where itemnmbr ='Item001' and decplcur >6
update IVC10101 set decplcur = 12 where itemnmbr ='Item001' and decplcur >6
update IVC30102 set decplcur = 12 where itemnmbr ='Item001' and decplcur >6
update POP10110 set decplcur = 12 where itemnmbr ='Item001' and decplcur >6
update POP10310 set decplcur = 12 where itemnmbr ='Item001' and decplcur >6
update POP30110 set decplcur = 12 where itemnmbr ='Item001' and decplcur >6
update POP30310 set decplcur = 12 where itemnmbr ='Item001' and decplcur >6
update PT10000 set decplcur = 12 where itemnmbr ='Item001' and decplcur >6
update SEE30303 set decplcur = 12 where itemnmbr ='Item001' and decplcur >6
update SOP10200 set decplcur = 12 where itemnmbr ='Item001' and decplcur >6
update SOP30300 set decplcur = 12 where itemnmbr ='Item001' and decplcur >6