SQL Function to Return Last Workflow Final Approval Date

The below SQL function returns the last Microsoft Dynamics GP workflow final approval date and time; it is always returned, not just when final approved is the current status.

This function was created to be used in a check of approved documents to see when they had last been approved; it was used in conjunction with the SQL function which returns the current workflow status.

IF object_id(N'uf_AZRCRV_GetLastWorkflowFinalApprovalDate', N'FN') IS NOT NULL
    DROP FUNCTION uf_AZRCRV_GetLastWorkflowFinalApprovalDate

GO
CREATE FUNCTION dbo.uf_AZRCRV_GetLastWorkflowFinalApprovalDate(@WorkflowTypeName CHAR(50),@WfBusObjKey CHAR(200))
	RETURNS DATETIME

AS
/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (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).
*/
BEGIN
	RETURN ISNULL((
		SELECT TOP 1

			FORMAT(['Workflow History'].Workflow_Completion_Date, 'yyyy-MM-dd') + ' ' + FORMAT(['Workflow History'].Workflow_Completion_Time, 'HH:mm:ss.fff')
		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 = 10

		ORDER BY
			['Workflow History'].DEX_ROW_ID DESC)
	,'1900-01-01 00:00:00.000')
END

GO
GRANT EXECUTE ON uf_AZRCRV_GetLastWorkflowFinalApprovalDate TO DYNGRP

GO

Read original post SQL Function to Return Last Workflow Final Approval Date at azurecurve|Ramblings of a Dynamics GP Consultant