Missing Security Roles In Microsoft Dynamics GP 2018 RTM

Microsoft Dynamics GPThe Dynamics GP Support and Services Blog has a post on the missing security in Microsoft Dynamics GP 2018 RTM; this is the new security roles and tasks created for the new functionality.

For features which are being enhanced, the new security tasks may be being added to roles which already exist and are assigned to users, the security is not automatically updated with the upgrade, but instead scripts are made available to add the missing security; this places the onus on the client to determine if the roles should be updated or not.

I have updated my original post with the new script, but you can also download it here.

SQL Function To Return Last Workflow Comment

Microsoft Dynamics GPBack in April 2017 I posted an SQL function which can be used to return the workflow status of a transaction or card. This function is now complimented by another one which can be used to return the last comment recorded. This was created for use on a customisation of the Purchase Requisition Entry window which added a Rejection Reason field so users could see at a glance why a purchase requisition had been rejected.

IF OBJECT_ID (N'uf_AZRCRV_GetWorkflowApprovalComments', N'FN') IS NOT NULL
    DROP FUNCTION uf_AZRCRV_GetWorkflowApprovalComments
GO
CREATE FUNCTION dbo.uf_AZRCRV_GetWorkflowApprovalComments(@WorkflowTypeName CHAR(50),@WfBusObjKey CHAR(20))
	RETURNS VARCHAR(14)
AS
/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (http://www.azurecurve.co.uk)
This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int).

Returns Workflow Approval status of a specified workflow item.

Requires input parameters of WorkflowTypeName and WfBusObjKey

Valid Workflow Type Names are (as of Microsoft Dynamics GP 2016 R2):
	General Ledger Batch Approval
	Receivables Batch Approval
	Payables Batch Approval
	Payables Transaction Approval
	Purchase Order Approval
	Purchase Requisition Approval
	Vendor Approval
	Employee Profile Approval
	Employee Skills Approval
	Payroll Direct Deposit Approval
	Payroll Timecard Approval
	Payroll W4 Approval
	Expense Report Approval
	Timesheet Approval
	Smartlist Designer View Approval
*/
BEGIN
	RETURN ISNULL((
		SELECT TOP 1 
			WF30100 AS ['Workflow History']
		FROM 
			WF30100 AS ['Workflow History']
		INNER JOIN
			WFI10002 AS ['Workflow Master']
				ON
					['Workflow Master'].WorkflowInstanceID = ['Workflow History'].WorkflowInstanceID
		WHERE
			['Workflow Master'].Workflow_Type_Name = @WorkflowTypeName
		AND
			['Workflow Master'].WfBusObjKey = @WfBusObjKey
		ORDER BY
			['Workflow History'].DEX_ROW_ID DESC)
	,'Not Submitted')
END
GO
GRANT EXECUTE ON uf_AZRCRV_GetWorkflowApprovalComments TO DYNGRP
GO

This function can easily be included in a view or other query used in a variety of reporting tools.

SQL Script To Verify Sales Invoice Extended Cost Against Subtotal

Microsoft Dynamics GPThis script is the result of a support call logged by a client where the incorrect value on sales invoices was being invoiced to customers. There was a small number of invoices being created which were showing the incorrect value; there was concern that the issue might be wider than thought, so I wrote this script to verify the sum of the Extended Cost of the lines on an invoice against the Subtotal.

This script is configured to check invoices, but could be used against other transaction types if the highlighted section is changed.

IF OBJECT_ID (N'uv_AZRCRV_CompareEXTDCOSTAgainstSubtotal', N'V') IS NOT NULL
	DROP VIEW uv_AZRCRV_CompareEXTDCOSTAgainstSubtotal
GO
CREATE VIEW uv_AZRCRV_CompareEXTDCOSTAgainstSubtotal AS
/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (http://www.azurecurve.co.uk)
This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int).
*/
SELECT
	['Sales Transaction Work'].SOPNUMBE
	,FORMAT(['Sales Transaction Work'].DOCDATE, 'yyyy-MM-dd') AS DOCDATE
	,['Sales Transaction Amounts Work'].XTNDPRCE
	,['Sales Transaction Work'].SUBTOTAL
	,'OPEN' AS TRXSTATUS
FROM
	SOP10100 AS ['Sales Transaction Work']
INNER JOIN 
	(SELECT
		SOPNUMBE
		,SOPTYPE
		,SUM(XTNDPRCE) AS XTNDPRCE
	FROM
		SOP10200
	WHERE
		SOPTYPE = 3 --invoice
	GROUP BY
		SOPNUMBE,SOPTYPE) AS ['Sales Transaction Amounts Work']
			ON
				['Sales Transaction Amounts Work'].SOPNUMBE = ['Sales Transaction Work'].SOPNUMBE
			AND
				['Sales Transaction Amounts Work'].SOPTYPE = ['Sales Transaction Work'].SOPTYPE
WHERE
	['Sales Transaction Amounts Work'].XTNDPRCE <> ['Sales Transaction Work'].SUBTOTAL
UNION ALL
	SELECT
		['Sales Transaction History'].SOPNUMBE
		,FORMAT(['Sales Transaction History'].DOCDATE, 'yyyy-MM-dd') AS DOCDATE
		,['Sales Transaction Amounts History'].XTNDPRCE
		,['Sales Transaction History'].SUBTOTAL
		,'HIST' AS TRXSTATUS
	FROM
		SOP30200 AS ['Sales Transaction History']
	INNER JOIN 
		(SELECT
			SOPNUMBE
			,SOPTYPE
			,SUM(XTNDPRCE) AS XTNDPRCE
		FROM
			SOP30300
		WHERE
			SOPTYPE = 3 --invoice
		GROUP BY
			SOPNUMBE,SOPTYPE) AS ['Sales Transaction Amounts History']
				ON
					['Sales Transaction Amounts History'].SOPNUMBE = ['Sales Transaction History'].SOPNUMBE
				AND
					['Sales Transaction Amounts History'].SOPTYPE = ['Sales Transaction History'].SOPTYPE
	WHERE
		['Sales Transaction Amounts History'].XTNDPRCE <> ['Sales Transaction History'].SUBTOTAL
GO
GRANT SELECT ON uv_AZRCRV_CompareEXTDCOSTAgainstSubtotal TO DYNGRP
GO

The view can easily be plugged into SmartList Designer, SmartList Builder, a refreshable Excel Report, a SQL Server Reporting Services report or any other type of reporting tool.

SQL Script to Set EFT Output Locations When Copying Live To Test

Microsoft Dynamics GPAs mentioned in the last post I’ve had a couple of scripts recently which I needed to create in order to facilitate clients easily making live to test copies without issues arising.

The script, below, can be used to set the EFT file locations in Checkbook Master. For the client I was working with, we only needed to set the file location of the Domestic Payments File Location field, but I am including all of the file location fields in the script.

Simply change the highlighted path locations to the required paths and set the Checkbook ID at the bottom:

/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (http://www.azurecurve.co.uk)
This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int).
*/
UPDATE
	CM00101
SET
	DomPmtsFile = '{path}'
	,ForeignPmtsFile = '{path}'
	,DomDirDbtCollectFile = '{path}'
	,DomDirDbtRefundFile = '{path}'
	,EFTPMPrenoteFile = '{path}'
	,EFTRMPrenoteFile = '{path}'
	,EFTCommunicationFile = '{path}'
WHERE
	CHEKBKID = '{chequebook id}'

As always with scripts, please make sure you have a good backup before using the script.

SQL Script to Update Web Services Server in Workflow Setup When Copying Live To Test

Microsoft Dynamics GPAll clients will at some point copy their live company into a test one, whether or not the test system is on the same server or a different one. If on the same machine, they will usually use an automated process to perform the backup and restore.

There is usually other tasks which will need to be undertaken such as changing the output location of the EFT Payment Register Report or prefixing email message subjects with “TEST” or setting all email addresses to internal ones.

I’ve recently encountered a couple more fields which need to be reset to avoid problems when copying live to test. The one prompting this script, was when the live company was copied to the standalone test system, the Web Services server needed to be changed to a different value:

/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (http://www.azurecurve.co.uk)
This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int).
*/
UPDATE WF00100
	SET
		Web_Service_Server = '{servername}'
WHERE
	SETUPKEY = 0
GO

The highlighted section, above, needs to be changed to your Web Services server.

In addition, a repair needed to be run using the Web Services Configuration Wizard, but at least the script removes one manual step.

As always with scripts, please make sure you have a good backup before using the script.

Security Views For Use In SmartList Designer: Group Based Company Access In Management Reporter

Microsoft Dynamics GPA while ago, I did a series of views on the Microsoft Dynamics GP security model. Well, a little after that I wrote a couple of scripts to allow the security configuration of Management Reporter to easily be enquired upon.

This, the second Management Reporter security script, shows security for users as granted by their Group membership. the previous post, on Friday, showed the user based company access.

The view is configured to read the security from a database called ManagementReporter and assumes the user who runs the report has select permissions on this database and relevant tables.

IF OBJECT_ID (N'uv_AZRCRV_GetManagementReporterGroupBasedSecurity', N'V') IS NOT NULL
	DROP VIEW uv_AZRCRV_GetManagementReporterGroupBasedSecurity
GO
CREATE VIEW uv_AZRCRV_GetManagementReporterGroupBasedSecurity AS
/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (http://www.azurecurve.co.uk)
This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int).
*/
SELECT
	['Security User'].UserName AS 'Username'
	,['Security User Principal'].Name AS 'Domain Name'
	,['Security User'].LastLoginAttempt AS 'Last Login Attempt'
	,CASE ['Security User'].RoleType
		 WHEN 2 THEN
			'Viewer'
		 WHEN 3 THEN
			'Generator'
		 WHEN 4 THEN
			'Designer'
		 WHEN 5 THEN
			'Administrator'
		ELSE
			'None'
		END AS 'Role'
		,['Security Group Principal'].Name AS 'Group Name'
		,['Security Group Principal'].Description AS 'Group Description'
		,['Control Company'].Code AS 'INTERID'
		,['Control Company'].Name AS 'Company Name'
 FROM 
	Reporting.SecurityUser AS ['Security User'] WITH (NOLOCK)
INNER JOIN
	Reporting.SecurityPrincipal AS ['Security User Principal'] WITH (NOLOCK)
		ON
			['Security User'].UserID = ['Security User Principal'].ID
LEFT JOIN
	Reporting.SecurityGroupUser AS ['Security Group User'] WITH (NOLOCK)
		ON
			['Security User'].UserID = ['Security Group User'].UserID
LEFT JOIN
	Reporting.SecurityPrincipal AS ['Security Group Principal']  WITH (NOLOCK)
		ON
			 ['Security Group User'].GroupID = ['Security Group Principal'].ID
LEFT JOIN
	Reporting.SecurityCompanyPermission AS ['Security Company Group Permission'] WITH (NOLOCK)
		ON
			['Security Group Principal'].ID = ['Security Company Group Permission'].PrincipalID
LEFT JOIN
	Reporting.ControlCompany AS ['Control Company'] WITH (NOLOCK)
		ON
			['Security Company Group Permission'].CompanyID = ['Control Company'].ID
GO
GRANT SELECT ON uv_AZRCRV_GetManagementReporterGroupBasedSecurity 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 Based Company Access In Management Reporter

Microsoft Dynamics GPA while ago, I did a series of views on the Microsoft Dynamics GP security model. Well, a little after that I wrote a couple of scripts to allow the security configuration of Management Reporter to easily be enquired upon.

This first script returns the security based on how the user is configured; the view I will post on Monday shows Group based security.

The view is configured to read the security from a database called ManagementReporter and assumes the user who runs the report has select permissions on this database and relevant tables.

IF OBJECT_ID (N'uv_AZRCRV_GetManagementReporterUserBasedSecurity', N'V') IS NOT NULL
	DROP VIEW uv_AZRCRV_GetManagementReporterUserBasedSecurity 
GO
CREATE VIEW uv_AZRCRV_GetManagementReporterUserBasedSecurity AS
/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (http://www.azurecurve.co.uk)
This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int).
*/
SELECT
	['Security User'].UserName AS 'Username'
	,['Security User Principal'].Name AS 'Domain Name'
	,['Security User'].LastLoginAttempt AS 'Last Login Attempt'
	,CASE ['Security User'].RoleType
		 WHEN 2 THEN
			'Viewer'
		 WHEN 3 THEN
			'Generator'
		 WHEN 4 THEN
			'Designer'
		 WHEN 5 THEN
			'Administrator'
		ELSE
			'None'
		END AS 'Role'
	,['Control Company'].Code AS 'INTERID'
	,['Control Company'].Name AS 'Company Name'
FROM 
	ManagementReporter.Reporting.SecurityUser AS ['Security User'] WITH (NOLOCK)
INNER JOIN
	ManagementReporter.Reporting.SecurityPrincipal AS ['Security User Principal'] WITH (NOLOCK)
		ON
			['Security User'].UserID = ['Security User Principal'].ID
LEFT JOIN
	ManagementReporter.Reporting.SecurityCompanyPermission AS ['Security Company Permission'] WITH (NOLOCK)
		ON
			['Security User Principal'].ID = ['Security Company Permission'].PrincipalID
LEFT JOIN
	ManagementReporter.Reporting.ControlCompany AS ['Control Company'] WITH (NOLOCK)
		ON
			['Security Company Permission'].CompanyID = ['Control Company'].ID
GO
GRANT SELECT ON uv_AZRCRV_GetManagementReporterUserBasedSecurity TO DYNGRP
GO

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

SQL Script to Remove Purchase Requisition Workflow Status

Microsoft Dynamics GPA client recently had some hardware issues causing problems with the submission of purchases requisitions into a Workflow approval process. The hardware issues were causing some speed issues which resulted in the workflow timing out and the approval status becoming corrupt.

Instead of the PR being approved and going to a status of Final Approved, it was going to Approved, even though there was only one level of approval required by the workflow.

When the hardware issues were resolved, the error stopped occurring, but a number of corrupt PRs were remaining.

The way to correct the data is to reset the workflow status. I produced a small script which does this by resetting the Workflow_Status on the Purchasing Requisition Work (POP10200) table and removing the entries from the following tables:

  1. Workflow Step Instance Table (WFI10003)
  2. Workflow Tasks Table (WFI10004)
  3. Workflow Instance Master (WFI10002)

The list of PRs to be reset are entered in the highlighted part of the script:

/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (http://www.azurecurve.co.uk)
This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int).
*/
CREATE TABLE #POPRequisitions(
	POPRequisitionNumber VARCHAR(21)
)
GO

INSERT INTO
	#POPRequisitions
VALUES
	('REQ00000000000017')
GO

UPDATE POP10200 SET Workflow_Status = 1 WHERE POPRequisitionNumber IN (SELECT POPRequisitionNumber FROM #POPRequisitions)

DELETE FROM WFI10003 WHERE WorkflowInstanceID IN 
	(SELECT WorkflowInstanceID FROM WFI10002 WHERE Workflow_Type_Name='Purchase Requisition Approval' AND WfBusObjKey IN (SELECT POPRequisitionNumber FROM #POPRequisitions))

DELETE FROM WFI10004 WHERE WorkflowInstanceID IN 
	(SELECT WorkflowInstanceID FROM WFI10002 WHERE Workflow_Type_Name='Purchase Requisition Approval' AND WfBusObjKey IN (SELECT POPRequisitionNumber FROM #POPRequisitions))

DELETE FROM WFI10002 WHERE Workflow_Type_Name='Purchase Requisition Approval' AND WfBusObjKey IN
	(SELECT POPRequisitionNumber FROM #POPRequisitions)
GO

DROP TABLE #POPRequisitions
GO

As always before running a script on live, make sure you have a good backup and test the script in a test company.

Copy Posting Report Configuration Between Companies

Microsoft Dynamics GPI wrote the first version of this script a long time ago, but recently had cause to return to it. A client I am working with has configured the posting reports in one company and wanted to copy the same configuration to the other companies.

This script allows you to do this, on a one by one basis. Set the first highlighted parameter to the source company, and the second to the destination company.

/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (http://www.azurecurve.co.uk)
This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int).
*/
DECLARE @SQLStatement VARCHAR(8000)
DECLARE @SourceCompany VARCHAR(5) = 'T16R2'
DECLARE @DestinationCompany VARCHAR(5) = 'P16R2'

SET @SQLStatement = 'UPDATE
				['Destination Posting Journal Destinations']
			SET
				PRNTJRNL = ['Source Posting Journal Destinations'].PRNTJRNL
				,ASECTMNT = ['Source Posting Journal Destinations'].ASECTMNT
				,PRTOPRNT = ['Source Posting Journal Destinations'].PRTOPRNT
				,PRTOFLNT = ['Source Posting Journal Destinations'].PRTOFLNT
			FROM
				' + @DestinationCompany + '.dbo.SY02200 AS ['Destination Posting Journal Destinations']
			INNER JOIN
				' + @SourceCompany + '.dbo.SY02200 AS ['Source Posting Journal Destinations'] ON ['Source Posting Journal Destinations'].SERIES = ['Destination Posting Journal Destinations'].SERIES
					AND ['Source Posting Journal Destinations'].TRXSOURC = ['Destination Posting Journal Destinations'].TRXSOURC
					AND ['Source Posting Journal Destinations'].PTGRPTNM = ['Destination Posting Journal Destinations'].PTGRPTNM'
EXEC (@SQLStatement)

As always with an SQL script, please make sure you have a good backup before running it.

SQL Script to Prefix Email Message Subjects with Test

Microsoft Dynamics GPI had an issue reported with purchase requisitions the other day. I logged on and did a little testing and then, a short wile later, got an email from the client asking about some workflow approval emails some managers had received.

The email to the approvers did not contain the word “test” anywhere in either the subject or body. This lead to me having a horrible moment when I thought I might have done my testing on live; I was able to confirm quickly that I had not.

After this I decided I had better knock a quick script together which the client could incorporate into their live to test copy.

The script is more engineered than it might otherwise need to be as I have included code to only do the update when it is running against a test company (flagged with <TEST> at the end of the Company Name) and where the email subject isn’t already prefixed with the word TEST:

/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (http://www.azurecurve.co.uk)
This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int).
*/
UPDATE
	['Email Messages']
SET
	EmailMessageSubject = LEFT('TEST: ' + CAST(['Email Messages'].EmailMessageSubject AS CHAR(150)), 150)
FROM
	SY04901 AS ['Email Messages']
INNER JOIN
	DYNAMICS..SY01500 AS ['Company Master']
		ON
			['Company Master'].INTERID = DB_NAME()
WHERE
	['Company Master'].CMPNYNAM LIKE '%<TEST>'
AND
	LEFT(['Email Messages'].EmailMessageSubject, 4) <> 'TEST'
GO

As always, before running a script against a database, make sure you understand the scirpt and have a good backup of the database.