Announcing the Saviynt Knowledge Exchange unifying the Saviynt forums, documentation, training,
and more in a single search tool across platforms. Read the announcement here.

Checking Inactive Role Owner's Manager in Workflow

h_sapkota
Regular Contributor II
Regular Contributor II

Hi Team,

We have the requirement to send the Assign Roles Workflow Approval to Role Owner's Manager. Sending the REQUEST to role owner manager is working fine but we are doing the check if role owner's manager is null or is inactive. 

I have tried below query in the Condition IF ELSE block to test for null manager, but it is throwing error in the logs.

(com.saviynt.ecm.identitywarehouse.domain.Users.executeQuery("select u.id from Users u where u.id in (select userkey from role_owners where rolekey=${role.id}) AND u.manager is null")?.size() != 0)

ERROR:

 Exception in workflow service
 
2023-08-04T07:10:48+05:30-ecm-2023-08-04T01:40:48.459498518Z stdout F org.jbpm.api.JbpmException: script evaluation error: javax.script.ScriptException: org.springframework.orm.hibernate3.HibernateQueryException: role_owners is not mapped [select u.id from com.saviynt.ecm.identitywarehouse.domain.Users u where u.id in (select userkey from role_owners where rolekey=3) AND u.manager is null]; nested exception is org.hibernate.hql.ast.QuerySyntaxException: role_owners is not mapped [select u.id from com.saviynt.ecm.identitywarehouse.domain.Users u where u.id in (select userkey from role_owners where rolekey=3) AND u.manager is null]
 
2023-08-04T07:10:48+05:30-ecm-2023-08-04T01:40:48.459501518Z stdout F at org.jbpm.pvm.internal.script.ScriptManager.evaluate(ScriptManager.java:127)
 
WORKFLOW
h_sapkota_0-1691113658556.png

Regards,
Hitesh Sapkota

 

 

7 REPLIES 7

rushikeshvartak
All-Star
All-Star

This workflow attached where ? Security System /Role Management ?


Regards,
Rushikesh Vartak
If you find the response useful, kindly consider selecting Accept As Solution and clicking on the kudos button.

rushikeshvartak
All-Star
All-Star

if Access Request :

com.saviynt.ecm.identitywarehouse.domain.Roles.executeQuery("Select m.statuskey as rostatus from Users u,role_owners ro,Users m where m.id=u.manager.id and and u.id=ro.userkey.id AND ro.id='${entitlement.id}'").get(0).toString().contains('0'))

 

if Role Request :

com.saviynt.ecm.identitywarehouse.domain.Roles.executeQuery("Select m.statuskey as rostatus from Users u,role_owners ro,Users m where m.id=u.manager.id and and u.id=ro.userkey.id AND ro.id='${role.id}'").get(0).toString().contains('0'))


Regards,
Rushikesh Vartak
If you find the response useful, kindly consider selecting Accept As Solution and clicking on the kudos button.

h_sapkota
Regular Contributor II
Regular Contributor II

Hi Rushikesh,

The request is for Role assignment.

I tired the above query:

com.saviynt.ecm.identitywarehouse.domain.Roles.executeQuery("Select m.statuskey as rostatus from Users u,role_owners ro,Users m where m.id=u.manager.id and and u.id=ro.userkey.id AND ro.id='${role.id}'").get(0).toString().contains('0'))

Here initially I see there is syntax error so I rectified that and used below:
(com.saviynt.ecm.identitywarehouse.domain.Roles.executeQuery("Select m.statuskey as rostatus from Users u,role_owners ro,Users m where m.id=u.manager.id and u.id=ro.userkey.id and ro.id=${role.id}").get(0).toString().contains('0'))

It is throwing error as:

 Exception in workflow service
 
2023-08-04T12:52:20+05:30-ecm-2023-08-04T07:22:20.886701108Z stdout F org.jbpm.api.JbpmException: script evaluation error: javax.script.ScriptException: org.springframework.orm.hibernate3.HibernateQueryException: unexpected char: '&' [Select m.statuskey as rostatus from com.saviynt.ecm.identitywarehouse.domain.Users u,role_owners ro,com.saviynt.ecm.identitywarehouse.domain.Users m where m.id=u.manager.id && u.id=ro.userkey.id && ro.id=2]; nested exception is org.hibernate.QueryException: unexpected char: '&' [Select m.statuskey as rostatus from com.saviynt.ecm.identitywarehouse.domain.Users u,role_owners ro,com.saviynt.ecm.identitywarehouse.domain.Users m where m.id=u.manager.id && u.id=ro.userkey.id && ro.id=2]

h_sapkota_0-1691133956736.png

Regards,
Hitesh Sapkota

@h_sapkota  use the below query

Select m.statuskey as rostatus from Users u,role_owners ro,Users m where m.id=u.manager.id AND u.id=ro.userkey.id AND ro.id='${role.id}'

Regards,
Naveen Sakleshpur
If this reply answered your question, please click the Accept As Solution button to help future users who may have a similar problem.

h_sapkota
Regular Contributor II
Regular Contributor II

Hey I just tried this as well:

(com.saviynt.ecm.identitywarehouse.domain.Roles.executeQuery("Select m.statuskey as rostatus from Users u,role_owners ro,Users m where m.id=u.manager.id AND u.id=ro.userkey.id AND ro.id=${role.id}").get(0).toString().contains('0'))

h_sapkota_1-1691134351127.png

It is throwing me below error:

- Exception in workflow service
 
2023-08-04T12:58:26+05:30-ecm-2023-08-04T07:28:26.904597322Z stdout F org.jbpm.api.JbpmException: script evaluation error: javax.script.ScriptException: org.springframework.orm.hibernate3.HibernateQueryException: role_owners is not mapped [Select m.statuskey as rostatus from com.saviynt.ecm.identitywarehouse.domain.Users u,role_owners ro,com.saviynt.ecm.identitywarehouse.domain.Users m where m.id=u.manager.id AND u.id=ro.userkey.id AND ro.id=2]; nested exception is org.hibernate.hql.ast.QuerySyntaxException: role_owners is not mapped [Select m.statuskey as rostatus from com.saviynt.ecm.identitywarehouse.domain.Users u,role_owners ro,com.saviynt.ecm.identitywarehouse.domain.Users m where m.id=u.manager.id AND u.id=ro.userkey.id AND ro.id=2]
 
Regards,
Hitesh Sapkota

@h_sapkota ok thanks for checking. just a few corrections in the query,

(com.saviynt.ecm.identitywarehouse.domain.Roles.executeQuery("Select m.statuskey as rostatus from Users u,Role_Owners ro,Users m where m.id=u.manager.id AND u.id=ro.userkey.id AND ro.rolekey.id='${role.id}'").get(0).toString().contains('0'))

 

Regards,
Naveen Sakleshpur
If this reply answered your question, please click the Accept As Solution button to help future users who may have a similar problem.

h_sapkota
Regular Contributor II
Regular Contributor II

Hi Naveen,

I have tried both query: 

(com.saviynt.ecm.identitywarehouse.domain.Roles.executeQuery("Select m.statuskey as rostatus from Users u,Role_Owners ro,Users m where m.id=u.manager.id AND u.id=ro.userkey.id AND ro.rolekey.id='${role.id}'").get(0).toString().contains('0'))
----------------------------------------------------------------------------------------------------
(com.saviynt.ecm.identitywarehouse.domain.Roles.executeQuery("Select m.id as rostatus from Users u,Role_Owners ro,Users m where m.id=u.manager.id AND u.id=ro.userkey.id AND ro.rolekey.id='${role.id}' AND m.statuskey = '0'")?.size() == 0)

Now below is the test case for this:
ROLE: ROLE1
ROLE_OWNER: testuser14 and testuser15
testuser14 -> Manager is testuser12 who is inactive
testuser15 -> Manager is testuser13 who is inactive

When I raise request for ROLE1 then the above condition evalutes to TRUE and goes to Manager Approval.
The actual flow should be condition should evalutes to FALSE and goes to Info Sec Approval.

h_sapkota_0-1691146178502.png

 

I used below query (converted format of the above query) in Analytics to see the data and it is returning 2 userkey as well. Here Rolekey = 2 is rolekey of ROLE1

Select m.userkey as rostatus
from Users u,Role_Owners ro,Users m
where m.userkey=u.manager AND u.userkey=ro.userkey AND ro.rolekey='2' AND m.statuskey = '0'

So this :
(com.saviynt.ecm.identitywarehouse.domain.Roles.executeQuery("Select m.id as rostatus from Users u,Role_Owners ro,Users m where m.id=u.manager.id AND u.id=ro.userkey.id AND ro.rolekey.id='${role.id}' AND m.statuskey = '0'")?.size() == 0)

--> query returns 2 rows so that size != 0 and should go to Info Sec for approval as it evalutes to FALSE.
But opposite is happening.

Regards,
Hitesh Sapkota