Workflow Delegation Cleanup - Delegations are being removed automatically

In workflow 2.0, Delegations of the workflow tasks are managed by the users per workflow type to ensure that proper actions are taken when a user is out of the office. A previous associated topic has shed a light on an issue with the delegation and a work-around solution was provided on Workflow Delegations under User Preferences, Access Denied for Limited Users. In this post, I am shedding a light on another issue in which the delegations submitted by the users are removed automatically by the system by midnight, everyday !

Workflow Delegation Overview

Delegations are managed by the user through the "Workflow Delegations" on the user preferences window, the user can create automatic delegations for any or all of the workflow types, so that specific users are alternatively selected by the system when a new task is received.

Workflow Delegations

The delegations are recorded in WF40500 (Workflow User Delegation Master) and WF40510 (Workflow User Delegation Lines) as shown below:

Workflow Delegation Tables

Workflow Delegation Company Cleanup

There is a job which purpose is "to remove out of office delegation records that are tied to invalid AD users.  It will also turn off Delegation for records that have expired."

Scan for Invalid Active Directory Users and Expired Delegations (all companies)

The job is scheduled to run on a daily bases once at 12:00 am, it primarily has one step which is running a stored procedure wfOutOfOfficeDelegationCleanupCompany in Dynamics DB. In Turn, this stored procedure checks for the available companies within Dynamics and clear all delegations regardless of the fact that they are active or inactive.

>> Therefore, any delegation that is submitted by any user will be deleted automatically at the end of every day, whether it is expired or not. 

Here is the original stored procedure which runs through the workflow delegation tables, and remove all delegations for all the users [wfOutOfOfficeDelegationCleanup]

 

CREATE PROCEDURE [dbo].[wfOutOfOfficeDelegationCleanup]

AS

BEGIN

SET NOCOUNT ON;

DECLARE @DomainUserName CHAR(255);

DECLARE @UsersListGuid CHAR(37) ,

    @ActualUsersListGuid CHAR(37);

DECLARE Delegation_Users CURSOR LOCAL FAST_FORWARD

FOR

    SELECT  DomainUserName ,

            UsersListGuid

    FROM    WF40500;

OPEN Delegation_Users;

FETCH NEXT FROM Delegation_Users INTO @DomainUserName, @UsersListGuid;

WHILE @@FETCH_STATUS = 0

    BEGIN

        SELECT  @ActualUsersListGuid =

                                  RTRIM(UPPER(ISNULL(( SELECT

                    DYN15.dbo.GetObjectGuidByUser(@DomainUserName,

                    1, 0)

                                                        ), '')));

        IF RTRIM(UPPER(@UsersListGuid)) <> RTRIM(@ActualUsersListGuid)

            BEGIN

                DELETE  FROM WF40510

                WHERE   UsersListGuid = @UsersListGuid;

                DELETE  FROM WF40500

                WHERE   UsersListGuid = @UsersListGuid;

            END;

        FETCH NEXT FROM Delegation_Users INTO @DomainUserName,

            @UsersListGuid;

    END;

CLOSE Delegation_Users;

DEALLOCATE Delegation_Users;

DECLARE Delegation_Lines CURSOR LOCAL FAST_FORWARD

FOR

    SELECT  DomainUserName

    FROM    WF40510;

OPEN Delegation_Lines;

FETCH NEXT FROM Delegation_Lines INTO @DomainUserName;

WHILE @@FETCH_STATUS = 0

    BEGIN

        IF DYN15.dbo.IsValidUserByUser(@DomainUserName, 1, 0) = 0

            BEGIN

                DELETE  FROM WF40510

                WHERE   DomainUserName = @DomainUserName;

            END;

        FETCH NEXT FROM Delegation_Lines INTO @DomainUserName;

    END;

CLOSE Delegation_Lines;

DEALLOCATE Delegation_Lines;

DELETE  FROM WF40500

WHERE   RTRIM(UPPER(UsersListGuid)) NOT IN (

        SELECT  RTRIM(UPPER(UsersListGuid))

        FROM    WF40510 );

DECLARE @UserDelegationDates TABLE

    (

        ENDDATE DATETIME ,

        UsersListGuid CHAR(37)

    );

INSERT  INTO @UserDelegationDates

        SELECT  MAX(ENDDATE) AS ENDDATE ,

                UsersListGuid

        FROM    WF40510

        GROUP BY UsersListGuid;

UPDATE  WF40500

SET     WF_Auto_Delegate_Tasks = 0

WHERE   WF_Auto_Delegate_Tasks = 1

        AND UsersListGuid IN

              ( SELECT   UsersListGuid

                     FROM     @UserDelegationDates

            WHERE    ENDDATE < SYSDATETIME() );

END;  

    GO

There are two cursors in the code above, to go through all the records in both WF40500 and WF40510 tables, and remove all records. Therefore, the delegations are removed on a daily bases regardless of its expiration date.

Workflow Delegation Cleanup - Solution 

You could either disable the SQL job, or run the query below on each of the company databases in order to modify the stored procedure responsible for the delegation removal, and ensure that only expired delegations are removed. Here is the modified version of the same stored procedure.

/*-----------------------------------------------------------------------------

Modified by: Mahmoud M. AlSaadi

The stored procedure is originally packed with Dynamics GP. The script below 
is a modification in order to ensure that only expired delegations are removed.

The script has been tested on a very limited sample data.

Revision History:

Revision No.            Revision Date    Description

1                       10/03/2016      Modified Version

------------------------------------------------------------------------------- */

ALTER PROCEDURE [dbo].[wfOutOfOfficeDelegationCleanup]

AS

BEGIN

SET NOCOUNT ON;

DECLARE @Today AS DATETIME;

SET @Today = CONVERT(DATE, GETDATE());

SET NOCOUNT ON;

DECLARE @DomainUserName CHAR(255);

DECLARE @UsersListGuid CHAR(37) ,

    @ActualUsersListGuid CHAR(37);

DECLARE Delegation_Users CURSOR LOCAL FAST_FORWARD

FOR

    SELECT  A.DomainUserName ,

            A.UsersListGuid

    FROM    WF40500 AS A

            LEFT OUTER JOIN WF40510 AS B ON A.UsersListGuid = B.UsersListGuid

    WHERE   B.ENDDATE < @Today;

                    

OPEN Delegation_Users;

FETCH NEXT FROM Delegation_Users INTO @DomainUserName, @UsersListGuid;

WHILE @@FETCH_STATUS = 0

    BEGIN

        SELECT  @ActualUsersListGuid =

              RTRIM(UPPER(ISNULL(( SELECT

        DYNAMICS.dbo.GetObjectGuidByUser(@DomainUserName,

        1, 0)

        ), '')));

        IF RTRIM(UPPER(@UsersListGuid)) <> RTRIM(@ActualUsersListGuid)

            BEGIN

                DELETE  FROM WF40510

                WHERE   UsersListGuid = @UsersListGuid;

                DELETE  FROM WF40500

                WHERE   UsersListGuid = @UsersListGuid;

            END;

        FETCH NEXT FROM Delegation_Users INTO @DomainUserName,

            @UsersListGuid;

    END;

CLOSE Delegation_Users;

DEALLOCATE Delegation_Users;

DECLARE Delegation_Lines CURSOR LOCAL FAST_FORWARD

FOR

    SELECT  DomainUserName

    FROM    WF40510

    WHERE   ENDDATE < @Today;

OPEN Delegation_Lines;

FETCH NEXT FROM Delegation_Lines INTO @DomainUserName;

WHILE @@FETCH_STATUS = 0

    BEGIN

        IF DYNAMICS.dbo.IsValidUserByUser(@DomainUserName, 1, 0) = 0

            BEGIN

                DELETE  FROM WF40510

                WHERE   DomainUserName = @DomainUserName;

            END;

        FETCH NEXT FROM Delegation_Lines INTO @DomainUserName;

    END;

CLOSE Delegation_Lines;

DEALLOCATE Delegation_Lines;

DELETE  FROM WF40500

WHERE   RTRIM(UPPER(UsersListGuid)) NOT IN (

        SELECT  RTRIM(UPPER(UsersListGuid))

        FROM    WF40510 );

DECLARE @UserDelegationDates TABLE

    (

        ENDDATE DATETIME ,

        UsersListGuid CHAR(37)

    );

INSERT  INTO @UserDelegationDates

        SELECT  MAX(ENDDATE) AS ENDDATE ,

                UsersListGuid

        FROM    WF40510

        GROUP BY UsersListGuid;

UPDATE  WF40500

SET     WF_Auto_Delegate_Tasks = 0

WHERE   WF_Auto_Delegate_Tasks = 1

        AND UsersListGuid IN ( SELECT   UsersListGuid

                                FROM     @UserDelegationDates

                                WHERE    ENDDATE < SYSDATETIME() );

END;  

GO

Best Regards, 

Mahmoud M. AlSaadi