Featured Post

Dynamics GP 2016 R2 is Now Available .. and #MSDynGP will Always be Avilable

The Microsoft Dynamics GP team announced today that Microsoft Dynamics GP 2016 R2 has released . Dynamics GP 2016 R2 continues the grea...

Thursday, March 10, 2016

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 

3 comments:

  1. Hi Mahmood, Thank you for the answer, this is great.

    I had to amend the stored procedure as some of the fields had been merged together in your sample.

    This is the updated code.

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

    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) ;

    DECLARE @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



    Regards,

    Geoff James
    Geoff.James@praxa.com.au

    ReplyDelete
    Replies
    1. Thank you Geoff

      Best Regards,
      Mahmoud M. AlSaadi

      Delete
  2. Because I'm fearful of altering default stored procedures, would you please confirm your script is for wfOutOfOfficeDelegationCleanupCompany? I'm asking because everywhere I look in your script, it's listed as wfOutOfOfficeDelegationCleanup (without "Company") and it's the "Company" version we have deployed in our environment. Or, is the omission on purpose so we don't overwrite the default package? And then, the next task would be to schedule a job to run the new stored procedure rather than the default SP, is that the idea?

    ReplyDelete