Day: November 18, 2022

SQL View to Return the Microsoft Dynamics GP Workflow Step Assignments

Ian Grieve3 minute read0 comment

Microsoft Dynamics GPA 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