Category: GP (Great Plains)

I created the script in this post for a client a while ago who wanted to use workflow approvals for purchase orders, but didn’t quite understand how a workflow process should be built.

He needed approximately thirty different approvals and build a step for each approver, all correct so far. Unfortunately, he created them each as a single step under a separate workflow process instead of thirty steps under one workflow process. Asking him to manually recreate all of the steps correctly under one workflow process was not going to be feasible so I did a little table exploring and came up with the below script which will move all steps for a specified workflow type under a single specified workflow process.

The two highlighted parameters at the top are where you need to specify a valid workflow type and destination workflow process. because of how the client had named his workflow processes and steps this script will change the name and description of each step to be the workflow process name and step name concatenated together and separated with a hyphen.

/*
Created by Ian Grieve of azurecurve | Ramblings of an IT Professional (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 @WorkflowType VARCHAR(50) = 'Purchase Order Approval'
DECLARE @WorkflowName VARCHAR(50) = 'PO Approval v1';

WITH WFUpdate AS(
	SELECT
		['Workflow Step Table'].Workflow_Name
		,['Workflow Step Table'].Workflow_Step_Name
		,ROW_NUMBER() OVER(ORDER BY ['Workflow Step Table'].Workflow_Name,['Workflow Step Table'].Workflow_Step_Sequence) * 10 AS ROWNUMBER
	FROM
		WF100003 AS ['Workflow Step Table'] -- Workflow Step Table (WF100003)
	INNER JOIN
		WF100002 AS ['Workflow Master - Step Parent'] -- Workflow Master (WF100002)
			ON
				['Workflow Master - Step Parent'].Workflow_Name = ['Workflow Step Table'].Workflow_Name
	WHERE
		['Workflow Master - Step Parent'].Workflow_Type_Name = @WorkflowType
	AND
		['Workflow Step Table'].Workflow_Step_Order = 0
)

UPDATE
	['Workflow Step Table']
SET
	Workflow_Name = @WorkflowName
	,Workflow_Step_Name = LEFT(RTRIM(['Workflow Master - Step Parent'].Workflow_Name) + - + RTRIM(['Workflow Step Table'].Workflow_Step_Name), 50)
	,WF_Step_Description = LEFT(RTRIM(['Workflow Master - Step Parent'].Workflow_Name) + - + RTRIM(['Workflow Step Table'].WF_Step_Description), 100)
	,Workflow_Step_Sequence = ISNULL(WFUpdate.ROWNUMBER, ['Workflow Step Table'].Workflow_Step_Sequence)
	,WF_Step_Predecessor = CASE WHEN LEN(['Workflow Step Table'].WF_Step_Predecessor) > 0 THEN
								LEFT(RTRIM(['Workflow Master - Step Parent'].Workflow_Name) + - + RTRIM(['Workflow Step Table'].WF_Step_Predecessor), 100)
							ELSE
								''
							END
FROM
	WF100003 AS ['Workflow Step Table'] -- Workflow Step Table (WF100003)
INNER JOIN
	WF100002 AS ['Workflow Master - Step Parent'] -- Workflow Master (WF100002)
		ON
			['Workflow Master - Step Parent'].Workflow_Name = ['Workflow Step Table'].Workflow_Name
LEFT JOIN
	WFUpdate
		ON
			WFUpdate.Workflow_Name = ['Workflow Step Table'].Workflow_Name
		AND
			WFUpdate.Workflow_Step_Name = ['Workflow Step Table'].Workflow_Step_Name
WHERE
	['Workflow Master - Step Parent'].Workflow_Type_Name = @WorkflowType

As always with scripts, before you run the one above please make sure you understand what the script will do and have a good backup of your company database.

A common request from clients using Microsoft Dynamics GP workflow was a way to easily be able to review to whom workflow steps were assigned; this was often a request from auditors.

I produced a SQL view which could easily be deployed for use in any reporting tool (SmartList Designer or SmartList Builder, SSRS, refreshable Excel, etc.) and which returned who the step was assigned as well showing the predecessor step.

/*
Created by Ian Grieve of azurecurve | Ramblings of an IT Professional (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 VIEW uv_AZRCRV_GetWorkflowSetupStepAssignment AS
SELECT
       ['Workflow Step Instance Table'].Workflow_name
       ,['Workflow Step Instance Table'].WF_Step_Predecessor
       ,['Workflow Step Instance Table'].Workflow_Step_Name
       ,['Workflow Step Instance Table'].WF_Step_Description
       ,['Workflow Step Instance Table'].EmailMessageID
       ,['Workflow Users'].ADLogin
       ,['Workflow Users'].ADDisplayName
FROM
       WF100003 AS ['Workflow Step Instance Table']
LEFT JOIN
       WF40200 AS ['Workflow Users']
ON
       ['Workflow Step Instance Table'].Workflow_Step_Assign_To = ['Workflow Users'].UsersListGuid
GO

GRANT SELECT ON uv_AZRCRV_GetWorkflowSetupStepAssignment TO DYNGRP
GO

I did some work for a client a while ago where we created a lot of email steps in Microsoft Dynamics GP workflow after which the client decided they would be using email after all and needed email on those steps activating and a custom message assigned. Rather than going through each step manually and switching the email on, I created a small script to do the job for us.

The script was for a PM Batch Approval type so also enables the include Document Attachment option.

The highlighted section is the name of the E-Mail Message ID to assign:

UPDATE
	WF100003
SET
	EmailMessageID = 'AZRCRV PO APPROVAL'
	,Workflow_Step_Send_Email = 1
	,WFIncludeDocumentAttach = 1
WHERE
	Workflow_Step_Send_Email = 0

As always before running a script make sure you understand what it does, test it on a test system before deploying live and make sure you have a good backup before running and verify afterwards.

This script was written for a client who uses Inventory BOMs in Microsoft Dynamics GP and wanted to be able to see all of the component items from those BOMs when they were looking at Sales Order transactions; this would give them some visibility of which component items were going to be needed from quotes or orders on the system.

They only use BOMs in a light way so this script doesn’t cater for BOMs which are inactive or contains inactive items; it wouldn’t be too difficult to update to include these, but the particular question would edit a BOM adding or removing items, rather than creating a new version of the BOM.

-- drop view if it exists
IF OBJECT_ID(N'uv_AZRCRV_GetBOMComponentsOnSalesTrx', N'V') IS NOT NULL
	DROP VIEW uv_AZRCRV_GetBOMComponentsOnSalesTrx
GO
-- create view
CREATE VIEW uv_AZRCRV_GetBOMComponentsOnSalesTrx AS
/*
Created by Ian Grieve of azurecurve | Ramblings of an IT Professional (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 Amounts'].SOPNUMBE
	,['Sales Transaction Amounts'].ITEMNMBR
	,['Sales Transaction Amounts'].ITEMDESC
	,['Bill of Materials Component'].CMPTITNM
	,['Item Master - Component'].ITEMDESC AS COMPDESC
	,['Bill of Materials Component'].Design_Qty * ['Sales Transaction Amounts'].QTYFULFI AS COMPQTY
FROM
	(
		SELECT
			SOPTYPE
			,SOPNUMBE
			,ITEMNMBR
			,ITEMDESC
			,QTYFULFI
		FROM
			SOP10200 AS ['Sales Transaction Amounts Work'] WITH (NOLOCK) --Sales Transaction Amounts Work (SOP10200)
		UNION ALL
			SELECT
				SOPTYPE
				,SOPNUMBE
				,ITEMNMBR
				,ITEMDESC
				,QTYFULFI
			FROM
				SOP30300 AS ['Sales Transaction Amounts History'] WITH (NOLOCK) --Sales Transaction Amounts History (SOP30300)
	) AS ['Sales Transaction Amounts']
INNER JOIN
	(
		SELECT
			SOPTYPE
			,SOPNUMBE
			,DOCDATE
		FROM
			SOP10100 AS ['Sales Transaction Work'] WITH (NOLOCK) --Sales Transaction Work (SOP10100)
		UNION ALL
			SELECT
				SOPTYPE
				,SOPNUMBE
				,DOCDATE
			FROM
				SOP30200 AS ['Sales Transaction History'] WITH (NOLOCK) --Sales Transaction History (SOP30200)
	) AS ['Sales Transactions']
		ON
			['Sales Transactions'].SOPTYPE = ['Sales Transaction Amounts'].SOPTYPE
		AND
			['Sales Transactions'].SOPNUMBE = ['Sales Transaction Amounts'].SOPNUMBE
LEFT JOIN
	BM00111 AS ['Bill of Materials Component'] WITH (NOLOCK) --Bill of Materials Component (BM00111)
		ON
			['Bill of Materials Component'].ITEMNMBR = ['Sales Transaction Amounts'].ITEMNMBR
INNER JOIN
	IV00101 AS ['Item Master - Component'] WITH (NOLOCK) --Item Master (IV00101)
		ON
			['Item Master - Component'].ITEMNMBR = ['Bill of Materials Component'].CMPTITNM
GO

GRANT SELECT ON uv_AZRCRV_GetBOMComponentsOnSalesTrx TO DYNGRP
GO

Too many times as a consultant I have discovered that users have been grant full access to a database when they only need select permissions on a handful of SQL views or tables. Whenever I create a new SQL view for a client I will create a database role to go with it, as best practice is to only grant the minimum permissions needed.

Below is a script example of creating a database role and then adding a couple of views in with select permissions; tables would be added in exactly the same way.

Once the role has been created you can assign it to any of the users required to have access to the objects to which it is granting access.

-- CREATE ROLE
CREATE ROLE rpt_AZRCRV_Reports
GO

-- ADD SELECT PERMISSIONS FOR VIEWS TO ROLE
GRANT SELECT ON uv_AZRCRV_POReqApprovalStatus TO rpt_AZRCRV_Reports
GRANT SELECT ON uv_AZRCRV_POPOrderApprovalStatus TO rpt_AZRCRV_Reports
GO

A role for stored procedures or function can be created in exactly the same way; the only difference is that you would be assigning EXECUTE permissions instead of SELECT.

While it isn’t listed on the GP Elementz website, there is a module available called PO Management which replaces the standard POP/SOP link in Microsoft Dynamics GP. The standard functionality requires that all of a sales order line where there is a commitment to a PO must be fulfilled before that line can be invoiced; the PO Management module allows partial invoicing of a line committed to a PO.

After implementing the module for a client, they required a report showing the status of a SO line in terms of whether there was stock, if the SO line had been committed to a PO and if the line had been partially or fully received. As the PO Management module stores it data in a custom table, I had to create a SQL view pulling the relevant tables together to determine the status.

-- drop view if it exists
IF OBJECT_ID(N'uv_AZRCRV_POMStatus', N'V') IS NOT NULL
	DROP VIEW uv_AZRCRV_POMStatus
GO
-- create view
CREATE VIEW [dbo].uv_AZRCRV_POMStatus AS
/*
Created by Ian Grieve of azurecurve | Ramblings of an IT Professional (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 Amounts Work'].SOPNUMBE
	,['Sales Transaction Amounts Work'].SOPTYPE
	,['Sales Transaction Amounts Work'].LNITMSEQ
	,['Sales Transaction Amounts Work'].ITEMNMBR
	,CASE WHEN ['Sales Transaction Amounts Work'].SOPTYPE NOT IN (2,6) THEN
		''
	WHEN ['ISC Back to Back'].PONUMBER IS NULL
		AND
			(['Sales Transaction Amounts Work'].QTYFULFI - ['Sales Transaction Amounts Work'].QTYCANCE) <[/sqlgrey] (['Item Quantity Master'].QTYONHND - ['Item Quantity Master'].ATYALLOC) -- AVAILABLE
		THEN
			'Needs Purchase'
	WHEN ['Purchasing Receipt Line Quantities'].QTYSHPPD IS NULL OR ['Purchasing Receipt Line Quantities'].QTYSHPPD = 0 THEN
		'Purchased'
	WHEN ['Purchasing Receipt Line Quantities'].QTYSHPPD < (['Purchase Order Line'].QTYORDER - ['Purchase Order Line'].QTYCANCE) THEN
		'Partially Received'
	WHEN ['Purchasing Receipt Line Quantities'].QTYSHPPD >= (['Purchase Order Line'].QTYORDER - ['Purchase Order Line'].QTYCANCE) THEN
		'Fully Received'
	ELSE
		'None'
	END AS 'Purchasing Status'
FROM
	SOP10200 AS ['Sales Transaction Amounts Work'] -- Sales Transaction Amounts Work (SOP10200)
LEFT JOIN
	IV00102 AS ['Item Quantity Master'] -- Item Quantity Master (IV00102)
		ON
			['Item Quantity Master'].ITEMNMBR = ['Sales Transaction Amounts Work'].ITEMNMBR
		AND
			['Item Quantity Master'].LOCNCODE = ['Sales Transaction Amounts Work'].LOCNCODE
LEFT JOIN
	ISC_BACK AS ['ISC Back to Back'] -- ISC_Back_to_Back (ISC_Back)
		ON
			['ISC Back to Back'].SOPNUMBE = ['Sales Transaction Amounts Work'].SOPNUMBE
		AND
			['ISC Back to Back'].SOPTYPE = ['Sales Transaction Amounts Work'].SOPTYPE
		AND
			['ISC Back to Back'].CMPNTSEQ = ['Sales Transaction Amounts Work'].CMPNTSEQ
		AND
			['ISC Back to Back'].LNITMSEQ = ['Sales Transaction Amounts Work'].LNITMSEQ
LEFT JOIN
	POP10110 AS ['Purchase Order Line'] -- Purchase Order Line (POP10110)
		ON
			['Purchase Order Line'].PONUMBER = ['ISC Back to Back'].PONUMBER
		AND
			['Purchase Order Line'].ORD = ['ISC Back to Back'].ORD
LEFT JOIN
	(
		SELECT
			PONUMBER
			,POLNENUM
			,SUM(QTYINVCD) AS QTYSHPPD
		FROM
			POP10500 -- Purchasing Receipt Line Quantities (POP10500)
		GROUP BY
			PONUMBER
			,POLNENUM
	)  AS ['Purchasing Receipt Line Quantities']
		ON
			['Purchasing Receipt Line Quantities'].PONUMBER = ['ISC Back to Back'].PONUMBER
		AND
			['Purchasing Receipt Line Quantities'].POLNENUM = ['ISC Back to Back'].ORD
GO

GRANT SELECT ON uv_AZRCRV_POMStatus TO DYNGRP
GO

The workflow module in Microsoft Dynamics GP can send quite a lot of different notification emails, but the one I am often asked for, an email notification when a purchase is created from a purchase requisition, does not exist.

After being asked a few times, I had a think about how this could be done and came up with a SQL trigger on the Purchasing Requisition History (POP30200) table which joins to the SOP_POPLink (SOP60100) table when a PO has been created. This is easily deployed and also easily customised by customers to meet their own needs by, for example, changing the content of the notification message.

The first highlighted section is the email address of the receipient, in the example below I am building the email using the requested by username concatenated with a email domain internal to my test VM; the second highlighted section is the name of the SQL Database Mail profile which will be used to send the email.

/*
Created by Ian Grieve of azurecurve | Ramblings of an IT Professional (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 TRIGGER utr_AZRCRV_POP30200_PurchaseRequisition ON POP30200 AFTER INSERT AS
	
	DECLARE @EmailRecipient VARCHAR(100)
	DECLARE @EmailSubject VARCHAR(1000)
	DECLARE @EmailBody VARCHAR(MAX)
	DECLARE @PRFound BIT = 0

	SELECT
		@EmailRecipient = RTRIM(['Purchase Requisition Work'].REQSTDBY) + '@azurecurve.isc'
		,@EmailSubject = 'Purchase Order ' + RTRIM(CAST(['SOP_POP Link'].PONUMBER AS VARCHAR(100))) + ' has been created'
		,@EmailBody = 'Purchase Order ' + RTRIM(CAST(['SOP_POP Link'].PONUMBER AS VARCHAR(100))) + ' has been created from purchase requisition ' + RTRIM(CAST(['Purchase Requisition Work'].POPRequisitionNumber AS VARCHAR(100)))
		,@PRFound = 1
	FROM
		inserted AS ['Purchase Requisition Work']
	INNER JOIN
		SOP60100 AS ['SOP_POP Link'] -- SOP_POPLink (SOP60100)
			ON
				['SOP_POP Link'].SOPNUMBE = ['Purchase Requisition Work'].POPRequisitionNumber

	/* send email using database mail
	https://msdn.microsoft.com/en-us/library/ms190307.aspx
	*/
	IF (SELECT @PRFound) = 1
	BEGIN
		EXEC msdb.dbo.sp_send_dbmail
			@profile_name = 'MDGP Workflow'
			,@recipients = @EmailRecipient
			,@subject = @EmailSubject
			,@body = @EmailBody
			,@body_format ='HTML'
	END
GO