Use Case
When the request is sent for approval to a person who becomes inactive in Saviynt before he can act, in that case the requests might get stuck and never approved (ultimately expire) in case escalation block is not setup in the workflow.
Pre-requisites
When request was submitted the approver was active
Applicable Version(s)
ALLSolution
Below query can be used to identify all the requests that are pending with inactive approvers.
SELECT DISTINCT
SUBSTR(jbpmprocessinstanceid,
INSTR(jbpmprocessinstanceid, '.') + 1,
LENGTH(jbpmprocessinstanceid)) REQUESTID,
'Pending' AS 'REQUEST STATUS',
CASE
WHEN ra.requesttype = 1 THEN 'Grant Access'
WHEN ra.requesttype = 2 THEN 'Revoke Access'
WHEN ra.requesttype = 3 THEN 'Role Modify'
END 'REQUEST TYPE',
ar.ENDPOINTASCSV AS 'APPLICATION INSTANCE',
r.username AS REQUESTEE,
GROUP_CONCAT(DISTINCT (u.username)) 'PENDING WITH USER NAME',
aa.jbpm_activity_name AS 'APPROVAL STAGE',
ar.REQUESTDATE 'REQUEST DATE',
(DATEDIFF(DATE_FORMAT(IFNULL(ar.DUEDATE, CURDATE()),
'%Y-%m-%d'),
DATE_FORMAT(ar.REQUESTDATE, '%Y-%m-%d'))) 'DAYS TO EXPIRE'
FROM
ARS_REQUESTS ar,
REQUEST_ACCESS ra,
ACCESS_APPROVERS aa,
USERS u,
users r
WHERE
ar.REQUESTKEY = ra.REQUESTKEY
AND ra.REQUEST_ACCESSKEY = aa.REQUEST_ACCESS_KEY
AND u.userkey = aa.approverkey
AND aa.STATUS = 1
AND ar.status <> 4
AND u.statuskey = 0
AND r.userkey = ar.requestor
GROUP BY REQUESTID
ORDER BY REQUESTID ASC
The query joins ARS requests to access approvers to find all assignments where access approvers status is pending approval and then joins with users to find access approvers users inactive for those pending approvals.
NOTE:
- This is a case where approver becomes inactive after the approval has started and before the approver has acted on approval.
- In case approver becomes inactive before the approval is started, Saviynt automatically assigns the approval to ADMIN.
