Request Pending for admin Approval

[Manu269]

Hello Team,

1. We are working on an analytics report which is used to fetch the list of request pending for admin approval.

2. We have 2 level workflow, where level 1 is manager and level 2 is usergroup.

3. We have a scenario where there is only 1 member in usergroup. When the member of this usergroup raise the request post level 1 approval, level 2 get assigned to admin. Hence for that we have created an analytics : 

Following is the query built :

SELECT DISTINCT SUBSTR(JBPMPROCESSINSTANCEID,
INSTR(JBPMPROCESSINSTANCEID,
'.') + 1, LENGTH(JBPMPROCESSINSTANCEID)) AS 'REQUEST ID',
CASE
WHEN AR.REQUESTTYPE =1 THEN
'Add Access'
WHEN AR.REQUESTTYPE = 2 THEN
'Remove Access / Account'
WHEN AR.REQUESTTYPE = 3 THEN
'NEW ACCOUNT'
WHEN AR.REQUESTTYPE = 12 THEN
'UPDATE ACCOUNT'
ELSE AR.REQUESTTYPE
END AS 'REQUESTTYPE', TIMESTAMP(convert_tz(AR.REQUESTDATE,'+00:00','+05:30')) AS 'DATE OF REQUEST' , U1.USERNAME AS 'REQUESTOR USERNAME', U.USERNAME AS 'APPROVER USERNAME',EV.ENTITLEMENT_VALUE AS 'ENTITLEMENT NAME', AR.ENDPOINTASCSV AS 'ENDPOINT NAME'
FROM ARS_REQUESTS AR, REQUEST_ACCESS RA, ACCESS_APPROVERS AA, USERS U, USERS U1,entitlement_values EV
WHERE AR.REQUESTKEY = RA.REQUESTKEY
AND AA.REQUEST_ACCESS_KEY = RA.REQUEST_ACCESSKEY
AND AA.APPROVERKEY = U.USERKEY
AND AA.STATUS = 1
AND AR.REQUESTOR = U1.USERKEY
AND AR.ENDPOINTASCSV IN ('Oracle_EBS')
AND AR.STATUS NOT IN (4)
AND AR.WORKFLOWNAME IN ('Oracle_EBS_Approval_WF')
AND EV.ENTITLEMENT_VALUEKEY = RA.ACCESSKEY
AND AA.APPROVERKEY IN
(select u.userkey from users u where u.username = 'admin')
GROUP BY `REQUEST ID`;

Assistance Required :

1. We need assistance to fetch only those request which were assigned to admin in last 30 min. i.e. once the level 1 has approved and then the request is assigned to admin. Hence need to fetch all those records which was assigned post level 1 approval.

[sk]

@Manu269 : I know you are looking for a way to identify the requests which got assigned to admin after level 1 approval.

But I am just suggesting you a way to avoid this scenario in further, You can change the WF logic putting if-else condition with groovy as language before sending it to 2nd level approval.

Sample logic: Below logic we used to check if requestor is member of user group that is associated as owner of respective account. You can modify this logic according to your requirement.

(com.saviynt.ecm.identitywarehouse.domain.Usergroup_users.executeQuery("select u.id from Users u,Usergroup_users ug where u.id=ug.userkey AND ug.user_groupkey='${dynamicAttributesReqAccess.get(requestaccesskey).get('USERGROUPOWNERKEY')}' AND u.id='${requestedby.id}'").size()!=0)

[Manu269]

@sk as per this Solved: Workflow Approval : Auto Approve if the user is pa... - Saviynt Forums - 62516

It says the natural behaviour.

[sk]

@Manu269 : Yeah that is correct statement if you send approval directly to 2nd level approval. But what I am saying is before sending it to 2nd level try to validate if requestor or user is part of usergroup and user group is having only one user using if-else condition if yes send it to grant block otherwise send it to 2nd level approval.

I am just giving suggestion to avoid the scenario you are facing, similar situation/use case we had where we had to avoid sending approval if requestor is member of usergroup which we handled with above shared query.

Condition you have used in referred thread is being checked only for, if requestor is member of usergroup or not but additionally you need to check the count of user group members and if it has only one member or more if only one member and he is requestor then send it to grant block.

Yeah if validating that condition is not possible yeah obviously there is no option. I just want to suggest to see if you did attempted that route or not. If you have already attempted and not successful in that approach please ignore my response.

[Manu269]

@sk many thanks for the explanation.

I believe while putting this in if else block i need to select groovy as option?

[sk]

@Manu269 Yes that's correct

[rushikeshvartak]

Ask is for Workflow or reporting purpose ? you can check level of workflow in access_approvers table

[Manu269]

Ask is for reporting reporting purpose.

When i see the request history, I do see a date attribute which shows when the request is assigned to approver (admin). 

Is there a way to extract this and use in reporting?

[rushikeshvartak]

Screenshot?

[Manu269]

Can you let me know, the date which showed beside manager approval can be considered when the request was assigned to group approval? Which table stores this attribute?

Also, in case I dont want to use the date is there a way to find that approval has gone to Group Approval block and assigned to admin?

[rushikeshvartak · Accepted solution]

its stored in access_Approvers table SUBMITDATE column

[Manu269]

@rushikeshvartak 1 last quick question, is there a table where I can also find the approval has gone to Group Approval block ?

[rushikeshvartak · Accepted solution]

access_approvers - JBPM_ACTIVITY_NAME column

[Manu269]

Hello ,

This is the final query I came up with which provides expected result :

SELECT
DISTINCT SUBSTR(
JBPMPROCESSINSTANCEID,
INSTR(JBPMPROCESSINSTANCEID, '.') + 1,
LENGTH(JBPMPROCESSINSTANCEID)
) AS 'REQUEST ID',
CASE WHEN AR.REQUESTTYPE = 1 THEN 'Add Access' WHEN AR.REQUESTTYPE = 2 THEN 'Remove Access / Account' WHEN AR.REQUESTTYPE = 3 THEN 'NEW ACCOUNT' WHEN AR.REQUESTTYPE = 12 THEN 'UPDATE ACCOUNT' ELSE AR.REQUESTTYPE END AS 'REQUESTTYPE',
TIMESTAMP(
convert_tz(
AR.REQUESTDATE, '+00:00', '+05:30'
)
) AS 'DATE OF REQUEST',
U1.USERNAME AS 'REQUESTOR USERNAME',
U.USERNAME AS 'APPROVER USERNAME',
AR.ENDPOINTASCSV AS 'ENDPOINT NAME',
TIMESTAMP(
convert_tz(
aa.SUBMITDATE, '+00:00', '+05:30'
)
) as 'Date Assigned to Admin for Approval'
FROM
ARS_REQUESTS AR,
REQUEST_ACCESS RA,
ACCESS_APPROVERS AA,
USERS U,
USERS U1,
entitlement_values EV
WHERE
AR.REQUESTKEY = RA.REQUESTKEY
AND AA.REQUEST_ACCESS_KEY = RA.REQUEST_ACCESSKEY
AND AA.APPROVERKEY = U.USERKEY
AND AA.STATUS = 1
AND AR.REQUESTOR = U1.USERKEY
AND AR.ENDPOINTASCSV IN ('XXXXX')
AND AR.STATUS NOT IN (4)
AND AR.WORKFLOWNAME IN ('XXXX')
AND EV.ENTITLEMENT_VALUEKEY = RA.ACCESSKEY
AND aa.SUBMITDATE > DATE_SUB(
sysdate(),
Interval 30 Minute
)
AND AA.APPROVERKEY IN (
select
u.userkey
from
users u
where
u.username = 'admin'
)
GROUP BY
`REQUEST ID`;