Excluding the First Approver From the Second Approval Step for Workflow

This solution works for me (OneIM 9.2) p.s. it's not rolled out in my production environment yet.
Approval workflow with 2 approval levels (one approval step per level) where the first step need only one approval.
Configuration parameter QER | ITShop | ReducedApproverCalculation is disabled or set to 'No'.
Queries for approver selection

Create new 'Approval procedure': S1 - Members of a system role (excludes previous step approver)

Approver selection query:

select p.uid_person, null as UID_PWORulerOrigin
from PersonWantsOrg pwo
join ESet es on es.XObjectKey = pwo.ObjectKeyOrdered and es.IsInActive=0
join PersonHasEset phe on phe.UID_ESet = es.UID_ESet and phe.XMarkedForDeletion=0 and phe.XIsInEffect=1
join Person p on p.UID_Person = phe.UID_Person and p.UID_Person <> pwo.UID_PersonHead
where pwo.UID_PersonWantsOrg=@UID_PersonWantsOrg and isnull(pwo.UID_PersonHead, '') <> ''

If the first step is approved the UID_PersonHead@PersonWantOrg is filled (this is the person that approved).
Now the approval policy is calculated again the p.UID_Person <> pwo.UID_PersonHead part of the query
will exclude the approver of the first step.

If you want multiple approvals in the first step or more than 2 approval levels than you will have to
include the PWOHelperPWO in the approver selection query and make use of the properties:
Decision, RulerLevel, LevelNumber
Ex. p.UID_Person not in (select UID_PersonHead from PWOHelperPWO where Decision = 'P' and RulerLevel = 0)
I haven't tested it yet, so just theory.

It would be nice if there was a checkbox feature that guarantees unique approvers across the whole workflow.
And maybe there is, but I'm unaware of it. If somebody else got a beter solution, please share.

This solution works for me (OneIM 9.2) p.s. it's not rolled out in my production environment yet.
Approval workflow with 2 approval levels (one approval step per level) where the first step need only one approval.
Configuration parameter QER | ITShop | ReducedApproverCalculation is disabled or set to 'No'.
Queries for approver selection

Create new 'Approval procedure': S1 - Members of a system role (excludes previous step approver)

Approver selection query:

select p.uid_person, null as UID_PWORulerOrigin
from PersonWantsOrg pwo
join ESet es on es.XObjectKey = pwo.ObjectKeyOrdered and es.IsInActive=0
join PersonHasEset phe on phe.UID_ESet = es.UID_ESet and phe.XMarkedForDeletion=0 and phe.XIsInEffect=1
join Person p on p.UID_Person = phe.UID_Person and p.UID_Person <> pwo.UID_PersonHead
where pwo.UID_PersonWantsOrg=@UID_PersonWantsOrg and isnull(pwo.UID_PersonHead, '') <> ''

If the first step is approved the UID_PersonHead@PersonWantOrg is filled (this is the person that approved).
Now the approval policy is calculated again the p.UID_Person <> pwo.UID_PersonHead part of the query
will exclude the approver of the first step.

If you want multiple approvals in the first step or more than 2 approval levels than you will have to
include the PWOHelperPWO in the approver selection query and make use of the properties:
Decision, RulerLevel, LevelNumber
Ex. p.UID_Person not in (select UID_PersonHead from PWOHelperPWO where Decision = 'P' and RulerLevel = 0)
I haven't tested it yet, so just theory.

It would be nice if there was a checkbox feature that guarantees unique approvers across the whole workflow.
And maybe there is, but I'm unaware of it. If somebody else got a beter solution, please share.

Hello Niels,

QER | ITShop | ReducedApproverCalculation is disabled in our environment. I use Person table for CD. I wrote query like below but second approver step includes first approver again.
Will using directly person table make difficulties for calculation? Where do I make mistake?

Select P.UID_Person as UID_Person, null as UID_PWORulerOrigin From Person p
Where (p.CCC_JobCode = 'G5BRMGM1' or p.CCC_JobCode = 'G6MDYGM1') And p.IsInActive = 0
And p.UID_Person <> (Select UID_PersonHead from PersonWantsOrg Where UID_PersonWantsOrg = @UID_PersonWantsOrg)

Thank you so much for your reply.

Best Regards,

Volkan Ceylan

You are missing this part in your query:

And Exists (Select 1 from PersonWantsOrg Where UID_PersonWantsOrg = @UID_PersonWantsOrg and isnull(pwo.UID_PersonHead, '') <> '' )

When the approval policy is calculated the isnull(pwo.UID_PersonHead, '') <> '' part of the query
will make sure that there are no approvers returned for the second step until the first step is approved.
Hence you will see only the first step approvers entries in the PWOHelperPWO table.