Update Setup Mistake On Inventory User Categories

Microsoft Dynamics GPWe implemented Microsoft Dynamics GP for a client a while ago; they are quite heavy users of the Inventory Control module.

Unfortunately, when they configured the User Categories in Inventory Control Setup (Inventory >> Setup >> Inventory Control) the description for the category was entered in the Image field instead of the Description field.

Rather than making them go through and manually change all of the categories that had been defined, I put together a small script to do the job.

The script checks if the length of the entry in the Image field is greater than zero and that the Description has not been set before doing the update.

UPDATE
	IV40600
SET
	UserCatLongDescr = Image_URL
	,Image_URL = ''
WHERE
	LEN(RTRIM(Image_URL)) > 0
 AND 
	LEN(RTRIM(UserCatLongDescr)) = 0
GO

As I was writing this script we encountered the same setup issue on a client installation we took over from another partner so this looks like it may be more common that I thought it would be; caused I think be the fact the window has the Image field visible by default and not the Description which people expect.

Change Rate Calculation Method To Divide

Microsoft Dynamics GPThis is a script I hope not to need in future, but as I have had to write it, I figured I might as well post it.

When creating an exchange rate table in Multicurrency Exchange Rate Table Setup (Administration >> Setup >> System >> Exchange Table)several fields need to be defined. One of them is the Rate Calculation Method which is set to either Divide or Multiply.

This field can be changed until there are rates entered. The reason this script was created was for a client who create several currencies, entered quite a few rates and then realised that the flag for Rate Calculation Method was set to Multiply instead of Divide.

The choices were either to delete all rates or to change the data behind the scenes; the script took five minutes to write and test, whereas doing the update manually would have taken near an hour.

So script it was. Before I ran the update, I double checked to make sure there were no transactions and also that there was a good backup of the system database.

UPDATE
	MC40300
SET
	RTCLCMTD = 1
WHERE
	EXGTBLID IN ('USD','EURO')
GO

The yellow highlighted section is the list of currencies to update.

This script should NOT be run when transactions for the currency being updated have been entered.

Update EU Member Country Code Information

Microsoft Dynamics GPThey say there are only two certainties in life; death and taxes. Well, one other certainty is that time passes and with the passage of time comes change.

One of the changes is that the European Union has become larger, but the VAT10001 (VAT Country Code MSTR) in Microsoft Dynamics GP which holds the country code information has not been updated.

As the information in this table has a direct impact on clients, I created a script to flag the missing current EU Member states (Croatia, Finland and Hungary):

UPDATE
	VAT10001
SET
	ECFLAG = 1
WHERE
	CCode IN ('HR' --Croatia
	,'FI' --Finland
	,'HU') --Hungary
GO

As always have a good backup of your databases before running the script and check afterwards that everything is good.

Security Views For Use In SmartList Designer: User Access & Granted Security Roles With Tasks & Operations

Microsoft Dynamics GPThe eighth and final view in the series, shows users with their access to companies, roles, tasks and security operations. I’ll state up front that this is probably the least useful fo the views due to the sheer number of rows that it returns.

CREATE VIEW [dbo].[uv_PI_UserAccessAndGrantedSecurityRolesWithTasksAndOperations] AS
SELECT
	['User Master'].USERID AS 'User ID'
	,['User Master'].USERNAME AS 'Username'
	,['User Master'].USRCLASS AS 'User Class'
	,ISNULL(['Class Master'].DSCRIPTN, '') AS 'User Class Description'
	,ISNULL(['Company Master'].INTERID, '') AS 'Intercompany ID'
	,ISNULL(['Company Master'].CMPNYNAM, '') AS 'Company Name'
	,ISNULL(['Security Assignment User Role'].SECURITYROLEID, '') AS 'Security Role ID'
	,ISNULL(['Security Roles Master'].SECURITYROLENAME, '') AS 'Security Role Name'
	,ISNULL(['Security Role Task Assignment'].SECURITYTASKID, '') AS 'Security Task ID'
	,ISNULL(['Security Task Master'].SECURITYTASKNAME, '') AS 'Security Task Name'
	,ISNULL(['Security Task Master'].SECURITYTASKDESC, '') AS 'Security Task Description'
	,ISNULL(['Security Resource Descriptions'].PRODNAME, '') AS 'Product Name'
	,ISNULL(['Security Resource Descriptions'].Series_Name, '') AS 'Series Name'
	,ISNULL(['Security Resource Descriptions'].DSPLNAME, '') AS 'Security Operation Name'
	,ISNULL(['Security Resource Descriptions'].TYPESTR, '') AS 'Security Operation Type'
FROM
	SY01400 AS ['User Master']
LEFT JOIN
	SY40400 AS ['Class Master']
		ON ['Class Master'].USRCLASS = ['User Master'].USRCLASS
LEFT JOIN
	SY60100 AS ['User-Company Access']
		ON ['User-Company Access'].USERID = ['User Master'].USERID
LEFT JOIN
	SY10500 AS ['Security Assignment User Role']
		ON ['Security Assignment User Role'].CMPANYID = ['User-Company Access'].CMPANYID
			AND ['Security Assignment User Role'].USERID = ['User-Company Access'].USERID
LEFT JOIN
	SY09100 AS ['Security Roles Master']
		ON ['Security Roles Master'].SECURITYROLEID = ['Security Assignment User Role'].SECURITYROLEID
LEFT JOIN
	SY10600 AS ['Security Role Task Assignment']
		ON ['Security Role Task Assignment'].SECURITYROLEID = ['Security Roles Master'].SECURITYROLEID
LEFT JOIN
	SY01500 AS ['Company Master']
		ON ['Company Master'].CMPANYID = ['User-Company Access'].CMPANYID
LEFT JOIN
	SY09000 AS ['Security Task Master']
		ON ['Security Task Master'].SECURITYTASKID = ['Security Role Task Assignment'].SECURITYTASKID
LEFT JOIN
	SY10700 AS ['Security Assignment Task Operations']
		ON ['Security Assignment Task Operations'].SECURITYTASKID = ['Security Task Master'].SECURITYTASKID
LEFT JOIN
	SY09400 AS ['Security Resource Descriptions']
		ON ['Security Resource Descriptions'].SECURITYID = ['Security Assignment Task Operations'].SECURITYID
GO

GRANT SELECT ON uv_PI_UserAccessAndGrantedSecurityRolesWithTasks TO DYNGRP
GO

Click to show/hide the Security Views For Use In SmartList Designer Series Index

Security Views For Use In SmartList Designer: Security Tasks & Operations

Microsoft Dynamics GPThe seventh SQL view in this series does not include users, but instead shows the tasks and the operations. To use this view you will need to have populated the Security Resource Descriptions table.

CREATE VIEW [dbo].[uv_PI_SecurityRolesWithTasksAndOperations] AS
SELECT
	['Security Task Master'].SECURITYTASKID AS 'Security Task ID'
	,['Security Task Master'].SECURITYTASKNAME AS 'Security Task Name'
	,['Security Task Master'].SECURITYTASKDESC AS 'Security Task Description'
	,['Security Resource Descriptions'].PRODNAME AS 'Product Name'
	,['Security Resource Descriptions'].Series_Name AS 'Series Name'
	,['Security Resource Descriptions'].DSPLNAME AS 'Security Operation Name'
	,['Security Resource Descriptions'].TYPESTR AS 'Security Operation Type'
FROM
	SY09000 AS ['Security Task Master']
INNER JOIN
	SY10700 AS ['Security Assignment Task Operations']
		ON ['Security Assignment Task Operations'].SECURITYTASKID = ['Security Task Master'].SECURITYTASKID
INNER JOIN
	SY09400 AS ['Security Resource Descriptions']
		ON ['Security Resource Descriptions'].SECURITYID = ['Security Assignment Task Operations'].SECURITYID
GO

GRANT SELECT ON uv_PI_SecurityRolesWithTasksAndOperations TO DYNGRP
GO

Click to show/hide the Security Views For Use In SmartList Designer Series Index

Security Views For Use In SmartList Designer: Security Roles With Tasks & Operations

Microsoft Dynamics GPThe sixth SQL view in this series does not include users, but instead shows the security roles, tasks and also the operations. To use this view you will need to have populated the Security Resource Descriptions table.

CREATE VIEW [dbo].[uv_PI_SecurityRolesWithTasksAndOperations] AS
SELECT
	['Security Roles Master'].SECURITYROLEID AS 'Security Role ID'
	,['Security Roles Master'].SECURITYROLENAME AS 'Security Role Name'
	,['Security Role Task Assignment'].SECURITYTASKID AS 'Security Task ID'
	,['Security Task Master'].SECURITYTASKNAME AS 'Security Task Name'
	,['Security Resource Descriptions'].Series_Name AS 'Series Name'
	,['Security Resource Descriptions'].DSPLNAME AS 'Security Operation Name'
FROM
	SY09100 AS ['Security Roles Master']
INNER JOIN
	SY10600 AS ['Security Role Task Assignment']
		ON ['Security Role Task Assignment'].SECURITYROLEID = ['Security Roles Master'].SECURITYROLEID
INNER JOIN
	SY09000 AS ['Security Task Master']
		ON ['Security Task Master'].SECURITYTASKID = ['Security Role Task Assignment'].SECURITYTASKID
INNER JOIN
	SY10700 AS ['Security Assignment Task Operations']
		ON ['Security Assignment Task Operations'].SECURITYTASKID = ['Security Task Master'].SECURITYTASKID
INNER JOIN
	SY09400 AS ['Security Resource Descriptions']
		ON ['Security Resource Descriptions'].SECURITYID = ['Security Assignment Task Operations'].SECURITYID
GO

GRANT SELECT ON uv_PI_SecurityRolesWithTasksAndOperations TO DYNGRP
GO

Click to show/hide the Security Views For Use In SmartList Designer Series Index

Security Views For Use In SmartList Designer: Populating Security Resource Descriptions

Microsoft Dynamics GPIn this post I’m taking a slight diversion and not posting a SQL view. The reason is that the next view I will be posting requires that the security resource descriptions be available which, by default, they’re not. However, they can be made available, which is what I will be covering in this post.

SY09400 is the Security Resource Descriptions table which is, by default, empty as the names are stored within the Microsoft Dynamics GP client itself. However, this table can be populated, perhaps somewhat counter-intuitively, by using the Clear Data window (Microsoft Dynamics GP menu >> Maintenance >> Clear Data).

To populate the table, open Clear Data, click on Display and then select Physical:

Clear Data

Continue reading

Security Views For Use In SmartList Designer: Security Roles With Tasks

Microsoft Dynamics GPThe fourth view being posted in this series takes a step back from the users and just shows the role and tasks within.

CREATE VIEW [dbo].[uv_PI_SecurityRolesWithTasks] AS
SELECT
	['Security Roles Master'].SECURITYROLEID AS 'Security Role ID'
	,['Security Roles Master'].SECURITYROLENAME AS 'Security Role Name'
	,['Security Role Task Assignment'].SECURITYTASKID AS 'Security Task ID'
	,['Security Task Master'].SECURITYTASKNAME AS 'Security Task Name'
FROM
	SY09100 AS ['Security Roles Master']
INNER JOIN
	SY10600 AS ['Security Role Task Assignment']
		ON ['Security Role Task Assignment'].SECURITYROLEID = ['Security Roles Master'].SECURITYROLEID
INNER JOIN
	SY09000 AS ['Security Task Master']
		ON ['Security Task Master'].SECURITYTASKID = ['Security Role Task Assignment'].SECURITYTASKID
GO

GRANT SELECT ON uv_PI_SecurityRolesWithTasks TO DYNGRP
GO

Click to show/hide the Security Views For Use In SmartList Designer Series Index

Security Views For Use In SmartList Designer: User Access & Granted Security Roles With Tasks

Microsoft Dynamics GPThe third SQL view I’m posting in this series is one which shows the user, the companies to which they have access and the security roles assigned within each company as well as the tasks within the role.

CREATE VIEW [dbo].[uv_PI_UserAccessAndGrantedSecurityRolesWithTasks] AS
SELECT
	['User Master'].USERID AS 'User ID'
	,['User Master'].USERNAME AS 'Username'
	,['User Master'].USRCLASS AS 'User Class'
	,ISNULL(['Class Master'].DSCRIPTN, '') AS 'User Class Description'
	,ISNULL(['Company Master'].INTERID, '') AS 'Intercompany ID'
	,ISNULL(['Company Master'].CMPNYNAM, '') AS 'Company Name'
	,ISNULL(['Security Assignment User Role'].SECURITYROLEID, '') AS 'Security Role ID'
	,ISNULL(['Security Roles Master'].SECURITYROLENAME, '') AS 'Security Role Name'
	,ISNULL(['Security Role Task Assignment'].SECURITYTASKID, '') AS 'Security Task ID'
	,ISNULL(['Security Task Master'].SECURITYTASKNAME, '') AS 'Security Task Name'
FROM
	SY01400 AS ['User Master']
LEFT JOIN
	SY40400 AS ['Class Master']
		ON ['Class Master'].USRCLASS = ['User Master'].USRCLASS
LEFT JOIN
	SY60100 AS ['User-Company Access']
		ON ['User-Company Access'].USERID = ['User Master'].USERID
LEFT JOIN
	SY10500 AS ['Security Assignment User Role']
		ON ['Security Assignment User Role'].CMPANYID = ['User-Company Access'].CMPANYID
			AND ['Security Assignment User Role'].USERID = ['User-Company Access'].USERID
LEFT JOIN
	SY09100 AS ['Security Roles Master']
		ON ['Security Roles Master'].SECURITYROLEID = ['Security Assignment User Role'].SECURITYROLEID
LEFT JOIN
	SY01500 AS ['Company Master']
		ON ['Company Master'].CMPANYID = ['User-Company Access'].CMPANYID
LEFT JOIN
	SY10600 AS ['Security Role Task Assignment']
		ON ['Security Role Task Assignment'].SECURITYROLEID = ['Security Roles Master'].SECURITYROLEID
LEFT JOIN
	SY09000 AS ['Security Task Master']
		ON ['Security Task Master'].SECURITYTASKID = ['Security Role Task Assignment'].SECURITYTASKID
GO

GRANT SELECT ON uv_PI_UserAccessAndGrantedSecurityRolesWithTasks TO DYNGRP
GO

Click to show/hide the Security Views For Use In SmartList Designer Series Index