SQL Function To Return Last Workflow Comment

Back in April 2017 I posted an SQL function which can be used to return the workflow status of a transaction or card. This function is now complimented by another one which can be used to return the last comment recorded. This was created for use on a customisation of the Purchase Requisition Entry window which added a Rejection Reason field so users could see at a glance why a purchase requisition had been rejected.

IF OBJECT_ID (N'uf_AZRCRV_GetWorkflowApprovalComments', N'FN') IS NOT NULL
    DROP FUNCTION uf_AZRCRV_GetWorkflowApprovalComments
GO
CREATE FUNCTION dbo.uf_AZRCRV_GetWorkflowApprovalComments(@WorkflowTypeName CHAR(50),@WfBusObjKey CHAR(20))
	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 
			WF30100 AS ['Workflow History']
		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
		ORDER BY
			['Workflow History'].DEX_ROW_ID DESC)
	,'Not Submitted')
END
GO
GRANT EXECUTE ON uf_AZRCRV_GetWorkflowApprovalComments TO DYNGRP
GO

This function can easily be included in a view or other query used in a variety of reporting tools.