Fetch Accountkey in Custom Assignment block of workflow

[AS5278]

Hi,

I am trying to fetch the rank 1 owner of a particular account in the Custom Assignment block of Workflow.

I have used the below query for this:

select u.userkey from users u join accountowners ao on ao.OWNERUSERKEY=u.userkey where ao.ACCOUNTKEY=(select ra.ACCESSKEY from request_access ra where ra.REQUESTKEY=${request_access.requestkey}) and ao.rank=1

The above is not working and the request is going for admin approval. When I hardcode the requestkey in above query(or) use below query it works fine and request goes for rank 1 approval.

select u.userkey from users u join accountowners ao on ao.OWNERUSERKEY=u.userkey where ao.ACCOUNTKEY='2178357' and ao.rank=1

Here, I have hardcoded the accountkey for which I am raising the request.

Is there a way to fetch the accountkey from the request in the Custom Assignment block in Workflow?.

Or is there a way to fetch the requestkey inside the Custom Assignment block in Workflow?.

I have tried with the below as well but it doesn't work:

For RequestKey:

${ars_requests.requestkey}

${ars_requests.requestkey.id}

${request_access.requestkey}

${request_access.requestkey.id}

${reqid}

${com.saviynt.ecm.identitywarehouse.domain.Accounts.get(request_access.requestkey)}

For AccountKey:

${com.saviynt.ecm.identitywarehouse.domain.Accounts.get(request_access.accesskey)}

Please advice.

Thanks,

Atul Singh

[rushikeshvartak · Accepted solution]

${ARSREQUEST.id} - for requestkey

SELECT u.userkey
FROM users u
JOIN accountowners ao ON ao.OWNERUSERKEY = u.userkey
JOIN request_access ra ON ao.ACCOUNTKEY = ra.ACCESSKEY
WHERE ra.REQUESTKEY =${ARSREQUEST.id} AND ao.rank = 1;

[AS5278]

@rushikeshvartak 

We have started to face an issue:

Let's say user raised a request for two privileged accounts, in that case this query will fail as it will fetch the Rank 1 owner for both the requested accounts and hence will give 2 results in output.

SELECT u.userkey
FROM users u
JOIN accountowners ao ON ao.OWNERUSERKEY = u.userkey
JOIN request_access ra ON ao.ACCOUNTKEY = ra.ACCESSKEY
WHERE ra.REQUESTKEY =${ARSREQUEST.id} AND ao.rank = 1;

Is there a way to fix this issue?. I figured out that if we make an addition to above query like below it might fix the issue as the workflow is parallel:

SELECT u.userkey
FROM users u
JOIN accountowners ao ON ao.OWNERUSERKEY = u.userkey
JOIN request_access ra ON ao.ACCOUNTKEY = ra.ACCESSKEY
WHERE ra.REQUESTKEY =${ARSREQUEST.id} AND ao.rank = 1 and ao.accountkey=(select accountkey from accounts where accountid={requested account id});

Problem is what would be the dynamic attribute to fetch the requested account's key or ID ?.

Please advice.

Thanks.

[AS5278]

@rushikeshvartak 

Thanks!.