If you have been paying any sort of attention, you will know that I do quite a lot of work with the Workflow 2.0 module of Microsoft Dynamics GP.
We have created a number of reports in the past which allows the status of transactions or card going through a Workflow Approval process; to simplify matters somewhat, I created a SQL function which can be called with the Workflow Type Name and the Workflow Business Object Key and have the approval status returned.
CREATE FUNCTION dbo.uf_AZRCRV_GetWorkflowApprovalStatus(@WorkflowTypeName CHAR(50),@WfBusObjKey CHAR(200))
RETURNS VARCHAR(14)
AS
/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (https://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).
Returns Workflow Approval status of a specified workflow item.
Requires input parameters of WorkflowTypeName and WfBusObjKey
Valid Workflow Type Names are (as of Microsoft Dynamics GP 2016 R2):
General Ledger Batch Approval
Receivables Batch Approval
Payables Batch Approval
Payables Transaction Approval
Purchase Order Approval
Purchase Requisition Approval
Vendor Approval
Employee Profile Approval
Employee Skills Approval
Payroll Direct Deposit Approval
Payroll Timecard Approval
Payroll W4 Approval
Expense Report Approval
Timesheet Approval
Smartlist Designer View Approval
*/
BEGIN
RETURN ISNULL((
SELECT TOP 1
CASE WHEN ['Workflow History'].Workflow_Action = 1 THEN
'Submitted'
WHEN ['Workflow History'].Workflow_Action = 2 THEN
'Resubmitted'
WHEN ['Workflow History'].Workflow_Action = 3 THEN
'Approved'
WHEN ['Workflow History'].Workflow_Action = 4 THEN
'Task Complete'
WHEN ['Workflow History'].Workflow_Action = 5 THEN
'Rejected'
WHEN ['Workflow History'].Workflow_Action = 6 THEN
'Delegated'
WHEN ['Workflow History'].Workflow_Action = 7 THEN
'Recalled'
WHEN ['Workflow History'].Workflow_Action = 8 THEN
'Escalated'
WHEN ['Workflow History'].Workflow_Action = 9 THEN
'Edit'
WHEN ['Workflow History'].Workflow_Action = 10 THEN
'Final Approved'
END
FROM
WF30100 AS ['Workflow History']
INNER JOIN
WFI10002 AS ['Workflow Master']
ON
['Workflow Master'].WorkflowInstanceID = ['Workflow History'].WorkflowInstanceID
WHERE
['Workflow Master'].Workflow_Type_Name = @WorkflowTypeName
AND
['Workflow Master'].WfBusObjKey = @WfBusObjKey
AND
['Workflow History'].Workflow_Action <> 11
ORDER BY
['Workflow History'].DEX_ROW_ID DESC)
,'Not Submitted')
END
GO
GRANT EXECUTE ON uf_AZRCRV_GetWorkflowApprovalStatus TO DYNGRP
GO
This view can be called from a SmartList created using either SmartList Designer, SmartList Builder, via a Reporting Services or Excel Report or any other type of report.
Updated 09/02/2022 – Added the highlighted clause which is needed to filter out no action required status after final approval (thanks to Calum for the reminder).