Workflow for Assigning Approval Requests to Rank 1 Service Account Owner for Create/Modify Request

[AtrayeeDutta]

Hi Team

We are currently developing a service account management workflow that focuses on two specific use cases:

1. When a request is initiated for the creation of a service account, the approval should be sent to the rank 1 owner.
2. Additionally, when a modification request is made, such as removing an existing owner and adding a new one, the system should seek approval from both the removed and newly added owners.

Could you help us in sharing the If-else condition query or custom query in workflow to achieve the above use case or any idea how to achieve this.

[naveenss]

@AtrayeeDutta  Use the below custom query in your custom assignment block of the workflow for case 1

SELECT 
    userkey
FROM
    users
WHERE
    FIND_IN_SET(users.userkey,
            (SELECT DISTINCT
                    REPLACE(raa.attribute_value, ' ', '')
                FROM
                    request_access_attrs raa,
                    ars_requests ar,
                    request_access ra
                WHERE
                    ar.REQUESTKEY = ra.REQUESTKEY
                        AND ra.REQUEST_ACCESSKEY = raa.REQUEST_ACCESS_KEY
                        AND ar.requestkey = ${ARSREQUEST.id}
                        AND raa.ATTRIBUTE_NAME = 'USEROWNERKEY')) != 0;

[rushikeshvartak]

select userkey from users where FIND_IN_SET(users.userkey,(select distinct REPLACE(raa.attribute_value," ","")  from request_access_attrs raa , ars_requests ar, request_access ra WHERE ar.REQUESTKEY = ra.REQUESTKEY and  ra.REQUEST_ACCESSKEY = raa.REQUEST_ACCESS_KEY and ar.requestkey=${ARSREQUEST.id} and raa.ATTRIBUTE_NAME="USEROWNERKEY"))!=0  union  select users.userkey as userkey from usergroup_users, users where usergroup_users.USERKEY = users.USERKEY AND FIND_IN_SET(usergroup_users.user_groupkey,(select distinct REPLACE(raa.attribute_value," ","")  from request_access_attrs raa , ars_requests ar, request_access ra WHERE ar.REQUESTKEY = ra.REQUESTKEY and  ra.REQUEST_ACCESSKEY = raa.REQUEST_ACCESS_KEY and ar.requestkey=${ARSREQUEST.id} and raa.ATTRIBUTE_NAME="USERGROUPOWNERKEY"))!=0

​

When a request is initiated for the creation of a service account, the approval should be sent to the rank 1 owner. --> you need to use USERRAN

select userkey from users where FIND_IN_SET(users.userkey,(select distinct REPLACE(raa.attribute_value," ","")  from request_access_attrs raa , ars_requests ar, request_access ra WHERE ar.REQUESTKEY = ra.REQUESTKEY and  ra.REQUEST_ACCESSKEY = raa.REQUEST_ACCESS_KEY and ar.requestkey=${ARSREQUEST.id} and raa.ATTRIBUTE_NAME="USEROWNERKEYADDED"))!=0  union  select users.userkey as userkey from usergroup_users, users where usergroup_users.USERKEY = users.USERKEY AND FIND_IN_SET(usergroup_users.user_groupkey,(select distinct REPLACE(raa.attribute_value," ","")  from request_access_attrs raa , ars_requests ar, request_access ra WHERE ar.REQUESTKEY = ra.REQUESTKEY and  ra.REQUEST_ACCESSKEY = raa.REQUEST_ACCESS_KEY and ar.requestkey=${ARSREQUEST.id} and raa.ATTRIBUTE_NAME="USERGROUPOWNERKEY"))!=0 union select userkey from users where FIND_IN_SET(users.userkey,(select distinct REPLACE(raa.attribute_value," ","")  from request_access_attrs raa , ars_requests ar, request_access ra WHERE ar.REQUESTKEY = ra.REQUESTKEY and  ra.REQUEST_ACCESSKEY = raa.REQUEST_ACCESS_KEY and ar.requestkey=${ARSREQUEST.id} and raa.ATTRIBUTE_NAME="USEROWNERKEYREMOVED"))!=0 

1. Additionally, when a modification request is made, such as removing an existing owner and adding a new one, the system should seek approval from both the removed and newly added owners.

1.