SQL Scripts for Microsoft Dynamics GP: SQL Function To Return Approver

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:

• Return approval status
• Return last approval comment

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