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.
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
