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 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).
*/
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