This script is part of the SQL Scripts for Microsoft Dynamics GP where I will be posting the scripts I wrote against Microsoft Dynamics GP over the 19 years before I stopped working with Dynamics GP.
This script will return the approver of a workflow. It complements two other functions which I have previously posted:
IF OBJECT_ID (N'uf_AZRCRV_GetWorkflowApprover', N'FN') IS NOT NULL
DROP FUNCTION uf_AZRCRV_GetWorkflowApprover
GO
CREATE FUNCTION [dbo].[uf_AZRCRV_GetWorkflowApprover](@WorkflowTypeName CHAR(50),@WfBusObjKey CHAR(20))
RETURNS VARCHAR(50)
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).
*/
/*
Author: Ian Grieve
Version: 1.0
Date: 1/3/2017
Returns Workflow Approval status of a specified workflow item.
Requires 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
WFU.ADDisplayName
FROM
WF30100 AS WF --Workflow History (WF30100)
INNER JOIN
WFI10002 AS WFI --Workflow Instance Master (WFI10002)
ON WFI.WorkflowInstanceID = WF.WorkflowInstanceID
INNER JOIN
WF40200 WFU --Workflow Users (WF40200)
ON WFU.ADLogin = WF.Workflow_History_User
WHERE
WFI.Workflow_Type_Name = @WorkflowTypeName
AND
WFI.WfBusObjKey = @WfBusObjKey
AND
WF.Workflow_Action IN (3) --APPROVED
ORDER BY
WF.DEX_ROW_ID DESC)
,'')
END
GO
GRANT SELECT ON uf_AZRCRV_GetWorkflowApprover TO DYNGRP
GO