SQL View to return Microsoft Dynamics GP Workflow step approvers

I 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 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
    ['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