SQL View to return Microsoft Dynamics GP Workflow step approvers

Microsoft Dynamics GPI was recently talking to a client who was looking at creating a SQL script which they could run for the auditors which shows the assigned approvers to the steps of a Microsoft Dynamics GP Workflow process.

I’d written similar code for others before so I was able to provide them with this view:

CREATE VIEW uv_AZRCRV_GetWorkflowSetupStepAssignment 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
[‘Workflow Master’].Workflow_name
,[‘Workflow Master’].Workflow_Description
,CASE WHEN [‘Workflow Master’].ACTIVE = 1 THEN ‘Yes’ ELSE ‘No’ END AS ACTIVE
,[‘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
WF100002 AS [‘Workflow Master’]
LEFT JOIN
WF100003 AS [‘Workflow Step Instance Table’]
ON
[‘Workflow Step Instance Table’].Workflow_Name= [‘Workflow Master’].Workflow_Name
LEFT JOIN
WF40200 AS [‘Workflow Users’]
ON
[‘Workflow Step Instance Table’].Workflow_Step_Assign_To = [‘Workflow Users’].UsersListGuid
GO

This can be deployed to a company database and a SmartList object created using either SmartList Designer or SmartList Builder.

Updated 27/2/2020 to add Active column