Limited generic Task Assignment

0

Hello,

after playing around with the answers in the former thread I think it may be helpful for others to present my approach here. If anybody wants to reply to improve it, I will be grateful to learn.

1) Requirement
Within a process a group of users is requested to deliver an approval by two different members from within the group. Also it shall be possible to change the process to two persons approving each of two approval steps (4 eye principle or 8 eye principle).

2) Task Creation
The user assignment to the single approval tasks shall be done through AD groups as the members change frequently.

3) Approach
In both steps the task assignment is performed through SQL queries that retrieve the users from the active directory. To support a simple configuration of the AD group names process constants are used to define the group name.

3.1) SQL query for the first user assignment
Here no filtering is required except the appropriate AD groups:
SELECT users.COS_BpsID
FROM [dbo].[CacheOrganizationStructure] as Users
join [dbo].CacheOrganizationStructureGroupRelations as groupMembers
on users.COS_ID = groupMembers.COSGR_UserID
join [dbo].[CacheOrganizationStructure] as [group]
on groupMembers.COSGR_GroupID = [group].COS_ID
where [group].COS_DisplayName = '<AD Display Name for Group, passed through process constant into the query>'
AND [users].COS_IsActive = 1
AND [users].COS_AccountType = 1

Besides the Display Name you may want to use the AD name of the group. It can be found in table CacheORganizationStructure in column COS_AD_Name.

3.2) SQL query for second approval step in what the approvers of the first step shall not be assigned
To be able to distinguish between tasks actually finished by the approvers and those not finished I set the task creation of the first step to cancel other tasks.
Within the following query I de-select the users who finished their tasks in the first approval:
SELECT users.COS_BpsID
FROM [dbo].[CacheOrganizationStructure] as Users
join [dbo].CacheOrganizationStructureGroupRelations as groupMembers
on users.COS_ID = groupMembers.COSGR_UserID
join [dbo].[CacheOrganizationStructure] as [group]
on groupMembers.COSGR_GroupID = [group].COS_ID
where [group].COS_DisplayName = '<AD Group Display Name>'
AND [users].COS_IsActive = 1
AND [users].COS_AccountType = 1
AND users.COS_BpsID NOT IN (SELECT WFT_User
FROM WFElementTasks
WHERE WFT_STPID = <Step ID> -- Step ID
AND WFT_WFDID = <Instance ID> -- Instance ID
AND WFT_IsFinished = 1)

4) Result
The entire member set of the AD group is assigned to the first approval.
The second approval is only assigned as task to those members not having finished their task in the first approval step.