CustomQuery to assign request for Rank 4 approval | Workflow

[NishkalaPuli]

Hi Team,

We have a requirement where there are set of users who are assigned as Rank 1 and Rank 4 approvers for entitlements. If one user who has approved the request for Rank 1, then the request must be redirected to other users for Rank 4 approval. 

To achieve this we have build a query, Validation looks good in Data analyzer but the request is getting assigned to admin when tested through workflow. Need assistance to get this fixed

select eo.userkey from entitlement_owners eo, users u where eo.entitlement_valuekey=${REQUESTACCESSOBJ.id} and eo.rank = 4 and u.userkey = eo.userkey and not eo.userkey in (select u.userkey from users u, access_approvers aa, request_access ra where ra.REQUEST_ACCESSKEY = aa.REQUEST_ACCESS_KEY and u.userkey = aa.APPROVEBY and ra.REQUESTKEY=${task.requestKey})

[CR]

can you try below and confirm @NishkalaPuli 

select eo.userkey from entitlement_owners eo, users u where eo.entitlement_valuekey=${REQUESTACCESSOBJ.id} and eo.rank = 4 and u.userkey = eo.userkey and eo.userkey not in (select u.userkey from users u, access_approvers aa, request_access ra where ra.REQUEST_ACCESSKEY = aa.REQUEST_ACCESS_KEY and u.userkey = aa.APPROVEBY and ra.REQUESTKEY=${task.requestKey})

[NishkalaPuli]

@CR , It's the same, Still the request is getting assigned to admin.

[CR · Accepted solution]

try below

select eo.userkey from entitlement_owners eo, users u where eo.entitlement_valuekey=${REQUESTACCESSOBJ.id} and eo.rank = 4 and u.userkey = eo.userkey and eo.userkey not in (select u.userkey from users u, access_approvers aa, request_access ra where ra.REQUEST_ACCESSKEY = aa.REQUEST_ACCESS_KEY and u.userkey = aa.APPROVEBY and ra.REQUESTKEY=${ARSREQUEST.id})

[NishkalaPuli]

@CR It Worked, It was the binding Variable (${ARSREQUEST.id}). 

Thanks