Send Role modification request to Endpoint owner of the entitlements

[h_sapkota]

Hi Team,

We have a requirement that whenever role is modified with the access part i.e. Access is added to roles or removed from roles in that case the approval should go to the Endpoint owner of the endpoint where access (Entitlement_value resides).

And whenever role is modified with change in attribute such as displayname, customproperties in that case approval should get auto approved.

We have achieved the second requirement using below if else condition block:
com.saviynt.ecm.workflow.Request_Access.get(new Long(requestaccesskey)).accesstype == 2

For us to achieve the first requirement where it should go to endpoint owner, 
We are using the below query:
SELECT DISTINCT userkey
FROM users
WHERE userkey IN (
SELECT ep.ownerkey
FROM endpoints ep
WHERE ep.endpointkey IN (
SELECT DISTINCT et.endpointkey
FROM entitlement_types et
JOIN entitlement_values ev ON et.ENTITLEMENTTYPEKEY = ev.ENTITLEMENTTYPEKEY
WHERE ev.ENTITLEMENT_VALUEKEY IN (
SELECT DISTINCT ra.ACCESSKEY
FROM ars_requests ar
JOIN request_access ra ON ar.REQUESTKEY = ra.REQUESTKEY
JOIN request_access ra1 ON ra1.REQUEST_ACCESSKEY = ra.PARENTREQUEST
WHERE ar.requestkey = ${ARSREQUEST.id}
AND ra.status = 1
AND ra.accesstype = '2'
AND ra1.ACCESSKEY = ${REQUESTACCESSOBJ.id}
)
)
)

Now this is also fine but the request for one access is going to all the endpoint owner of all the access for which we are modifying.

Scenarios:
We created one request for Role 1 where we are adding access ABC (belongs to endpoint EP1) and remove access BCD (belongs to endpoint EP2).

On submitting the request, the approval for Request to Add access and revoke access both goes to Owner of EP1 and EP2.

Requirement:
Request to Add Access -> Approval should go to Owner of EP1
Revoke Access -> Approval should go to Owner of EP2

From logs, I have pull below details:
[[rolekey:144, rolehistorykey:49, oldvalue:2024-04-09 13:38:49.0, newvalue:2024-04-09 14:58:22.0, fieldname:updatedate, operation:MODIFIED],
[rolekey:144, rolehistorykey:49, oldvalue:Branch Managers, newvalue:, fieldname:ENTITLEMENT VALUE, operation:REMOVED, entitlementvaluekey:15376], [rolekey:144, rolehistorykey:49, oldvalue:, newvalue:ROLE_ADMIN, fieldname:ENTITLEMENT VALUE, operation:ADDED, comments:null, entitlementvaluekey:19972]]

If we can pull entitlementvaluekey from roles_historychangelog, then I think we can achieve this requirement. But not sure what is the column name where entitlementvalue key is stored in roles_historychangelog table.

[rushikeshvartak]

select ROLES_HISTORYCHANGELOGKEY,COMMENTS Comment,CREATIONDATE,FIELDNAME,NEWVALUE,OLDVALUE,OPERATION,ROLEHISTORYKEY,ROLEKEY,UPDATEUSER from roles_historychangelog where fieldname like '%ENTITLEMENTVALUE%'

[h_sapkota]

Hi @rushikeshvartak ,

select ROLES_HISTORYCHANGELOGKEY,COMMENTS Comment,CREATIONDATE,FIELDNAME,NEWVALUE,OLDVALUE,OPERATION,ROLEHISTORYKEY,ROLEKEY,UPDATEUSER from roles_historychangelog where fieldname like '%ENTITLEMENTVALUE%'

From this query, we can see newvalue or oldvalue is storing the entitlementvalue. From this entitlementvalue, I can get the endpoint to which this entitlementvalue belongs but this wont work if we have same entitlementvalue for different different endpoints.

We need to get the entitlementvaluekey for each entitlements which is being added or removed from the roles.

Any idea what is the column name in roles_historychangelog for the entitlementvaluekey . And if this key is exposed in the workflow binding variables for the role modifications.

Currently, I can see only two variable I can use : ${ARSREQUEST.id} and ${REQUESTACCESSOBJ.id}