Monday, September 30, 2013

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

Sunday, September 29, 2013

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.

Friday, September 27, 2013

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

Dynamics GP - Set the default selling UofM of all items to the base UofM for that item

update IV00101 set selnguom = baseuofm
--SELECT     a.BASEUOFM, IV00101.SELNGUOM
FROM         IV00101 INNER JOIN
                          (SELECT     IV00101_1.ITEMNMBR, IV00101_1.ITEMDESC, IV40201_1.BASEUOFM, IV00101_1.DECPLQTY
                            FROM          IV00101 AS IV00101_1 INNER JOIN
                                                   IV40201 AS IV40201_1 ON IV00101_1.UOMSCHDL = IV40201_1.UOMSCHDL) AS a ON IV00101.ITEMNMBR = a.ITEMNMBR
WHERE     (IV00101.SELNGUOM = '')



Tuesday, September 17, 2013

SSRS - You do not have sufficient priveleges to access this report

Other Errors:
There are a myriad of error messages pertaining to security access that you may encounter when attempting to access the SSRS portal using Internet Explorer.

Solution:

Make sure you login as the Domain Admin first so that you can give admin access to any other users

  1. Add the ssrs website to your trusted intranet zone
    1. Find your SSRS site address by using the Reporting Services configuration Manager or going to GP>Tools>Setup>System>Reporting Tools Setup
    2. Browse to your SSRS site, get the error
    3. Internet Options>Security>Local Intranet>Sites>Add the current site
  2. Right click Internet explorer and choose Run as Administrator
    1. If it brings up another menu with a small internet explorer icon, just right click on that icon then run as administrator
  3. Open your SSRS site
    1. Click on Site settings in the top right hand corner
    2. Security>New Role Assignment
    3. Type in windows username that should be an administrator in SSRS, tick System Administrator, click ok
    4. Repeat this for as many administrators as you need
  4. Now you should be able to login with a normal windows account (you will have to repeat steps 1 and 2 to set the IE settings for this windows account)
    1. You should be able to login and access the reports normally.
    2. If this still does not work, right click and run IE as administrator.

Monday, September 9, 2013

SQL 2008 - The database is set to single user mode and a user is accessing it

Problem:
You set your database to single user mode in the hopes of restoring a company, but then it hangs, or some other process grabs your single login, and you can't get back in to do anything.

Solution:

--Find the spid of the user logged into your company and kill it
-------------------------------------------------------------------------
use master
select * from master.sys.sysprocesses
where spid>50
and dbid = db_id('TWO')
-------------------------------------------------------------------------
KILL 59  -- or whatever spid comes up in the results
-------------------------------------------------------------------------
--You can set the database back to multi user mode
------------------------------------------------------------------------
alter database [TWO] set multi_user with rollback immediate

--Or you can go ahead with your original restore script and just restore over it immediately.
--Always try to run all of these activities in a single script so any automated processes don't get a chance to
--grab the connection again
-----------------------------------------------------------------------



RESTORE DATABASE [CILT] FROM  DISK = N'D:\Live.bak' WITH  FILE = 1,  MOVE N'GPSLiveDat.mdf' TO N'D:\GPSTestDat.mdf',  MOVE N'GPSLiveLog.ldf' TO N'D:\GPSTestLog.ldf',  NOUNLOAD,  REPLACE,  STATS = 10
GO

Wednesday, September 4, 2013

Dynamics GP - Changing UofM schedules without removing all prices

You have to update the qtyinbs and uofm fields for the following tables

RM00101
IV10401
IV10402
IV00106

Tuesday, September 3, 2013

Dynamics GP 2013 - Assign item to site script

This was taken from the sql profiler, and can be used to mass generate scripts to assign items to sites.
Using an excel sheet with this script and all items and sites, you can specifically assign items to sites as required for an integration.


EXEC zDP_IV00102SI '256RAM', 'WAREHOUSE', '', 2, '', 0, 0.00000, 0.00000, 0.00000, '1900.01.01', '', '1900.01.01', 0.00000, 0.00000, 0.00000, 0.00000, 0.00000, 0.00000, 0.00000, 0.00000, 0.00000, 0.00000, 0.00000, 0.00000, '1900.01.01', '00:00:00', '1900.01.01', '00:00:00', 0, '', '', '', 1, 0.00000, 0.00000, 1, 0.00000, 0.00000, 1.00000, 2, 0.00000, 0.00000, 0.00000, 0.00000, 0.00000, 0, 0, 1, 0, 0, 0, 3, 0.00000, 0.00000, 0.00000, '', '', '', '', '', '', '', '', 1, 1, '', 1, 1, 0.00000, 1, 1, 1, 0, 0, 0, 0, 0, 0, 0

Monday, September 2, 2013

Dynamics GP - "You don't have security priveleges to open this window" when printing an SOP invoice

Problem:
You get a security error when trying to print an SOP invoice even when you have all the correct security setup.

this problem occurs if you have manufacturing installed.
Security for an additional window is required to print invoices.

Solution:
Add access to the SOP_Dummy_Report_Form


  1. I suggest adding the security to the DefaultUser Task ID
  2. Assign the Defaultuser task id to all of the Roles
    1. Tools>System>Security Role
    2. Select Role
    3. Find and Tick defaultuser
  3. Go to Tools>Setup>System>Security Tasks
  4. Select Defaultuser
  5. select Product: Manufacturing
  6. Select type: Windows
  7. Select series: 3rdparty
  8. Scroll down to SOP_Dummy_Report_Form
  9. Tick it
  10. Save
  11. Let users close GP and login again
  12. You should be able to print invoices properly now

Original Post
https://community.dynamics.com/gp/f/32/p/65173/118686.aspx#.UiSXrTbbM6U