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