Query not working in Consult/Reassign query of Advanced configuration of entitlement owner campaign

[varunakarnia]

I am trying to use below query in Consult/Reassign query of Entitlement owner campaign configuration but it is not working?

select u from users u where 1=1 and (u.id in (select manager from users where id=:loggedInUser) or u.id in ((select distinct manager from users) and (select count(*) from com.saviynt.ecm.identitywarehouse.domain.user_savroles usr where usr.rolekey=1 and u.userkey=:loggedInUser)=1))

I just want to show the direct manager in pop-up when certifier tries to consult, but if any admin tries to consult they should see all managers from users table (usr.rolekey=1). 
Is the usage of "com.saviynt.ecm.identitywarehouse.domain.user_savroles" table in correct format?

Thanks in advance

[rushikeshvartak]

users table is not mapped 

2024-06-18T13:36:33-04:00-ecm-services.SaviyntCommonUtilityService-http-nio-8080-exec-39-s4wm2-ERROR-users is not mapped [select u from users u where 1=1 and (u.id in (select manager from users where id=:loggedInUser) or u.id in ((select distinct manager from users) and (select count(*) from com.saviynt.ecm.identitywarehouse.domain.user_savroles usr where usr.rolekey=1 and u.userkey=:loggedInUser)=1))]; nested exception is org.hibernate.hql.ast.QuerySyntaxException: users is not mapped [select u from users u where 1=1 and (u.id in (select manager from users where id=:loggedInUser) or u.id in ((select distinct manager from users) and (select count(*) from com.saviynt.ecm.identitywarehouse.domain.user_savroles usr where usr.rolekey=1 and u.userkey=:loggedInUser)=1))]

[rushikeshvartak]

user_savroles is not exposed.

select u from Users u where 1=1 and (u.id in (select manager from Users where id=:loggedInUser) or (u.id in (select distinct manager from Users) and (select count(*) from com.saviynt.ecm.identitywarehouse.domain.user_savroles usr where usr.rolekey=1 and usr.userkey=:loggedInUser)=1))

2024-06-18T13:40:55-04:00-ecm-services.SaviyntCommonUtilityService-http-nio-8080-exec-39-s4wm2-ERROR-com.saviynt.ecm.identitywarehouse.domain.user_savroles is not mapped [select u from com.saviynt.ecm.identitywarehouse.domain.Users u where 1=1 and (u.id in (select manager from com.saviynt.ecm.identitywarehouse.domain.Users where id=:loggedInUser) or (u.id in (select distinct manager from Users) and (select count(*) from com.saviynt.ecm.identitywarehouse.domain.user_savroles usr where usr.rolekey=1 and usr.userkey=:loggedInUser)=1))]; nested exception is org.hibernate.hql.ast.QuerySyntaxException: com.saviynt.ecm.identitywarehouse.domain.user_savroles is not mapped [select u from com.saviynt.ecm.identitywarehouse.domain.Users u where 1=1 and (u.id in (select manager from com.saviynt.ecm.identitywarehouse.domain.Users where id=:loggedInUser) or (u.id in (select distinct manager from Users) and (select count(*) from com.saviynt.ecm.identitywarehouse.domain.user_savroles usr where usr.rolekey=1 and usr.userkey=:loggedInUser)=1))]

[varunakarnia]

What is the possible solution?

[rushikeshvartak]

Use some user's customproperty to identify admin

[varunakarnia]

Can we make any configuration to expose user_savroles table and utilize it in the query?

[rushikeshvartak · Accepted solution]

select u from Users u where 1=1 and (u.id in (select manager from Users where id=:loggedInUser) or (u.id in (select distinct manager from Users) and (select count(*) from com.saviynt.ecm.identitywarehouse.domain.User_savroles usr where usr.rolekey=1 and usr.userkey=:loggedInUser)=1))

[PremMahadikar · Accepted solution]

Hi @varunakarnia ,

Below are the exposed tables in backend. Use the same format.

• com.saviynt.ecm.identitywarehouse.domain.User_savroles
• User_savroles

Your query:

select u from Users u where 1=1 and (u.id in (select manager from Users where id=:loggedInUser) or (u.id in (select distinct manager from Users) and (select count(*) from User_savroles usr where usr.rolekey=1 and usr.userkey=:loggedInUser)=1))

If this answers your question, please consider selecting Accept As Solution and hit Kudos

[varunakarnia]

Thank you very much, it worked.

Looks like it is strictly case sensitive. Appreciate your help