Retrieve Status ID from Request

[HichamElk]

I'm trying to create an analytics application that retrieves all requests by application or by user

Here's my query:

SELECT 
	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'
		WHEN AR.REQUESTTYPE = 3 THEN 'New Account'
		WHEN AR.REQUESTTYPE = 12 THEN 'Update Account'
		WHEN AR.REQUESTTYPE = 18 THEN 'Create User'
		WHEN AR.REQUESTTYPE = 19 THEN 'Update User'
		WHEN AR.REQUESTTYPE = 30 THEN 'Disable Account'
		WHEN AR.REQUESTTYPE = 30 THEN 'Enable Account'
	ELSE AR.REQUESTTYPE
	END AS 'REQUEST TYPE',
	ar.REQUESTDATE AS 'REQUEST SUBMIT DATE',
		CASE
		WHEN aa.status = 1 THEN (SELECT CONCAT(u3.FIRSTNAME, ' ', u3.LASTNAME) FROM USERS U3 WHERE U3.USERKEY=APPROVERKEY LIMIT 1)
		ELSE CONCAT(u.FIRSTNAME, ' ', u.LASTNAME)
	END AS 'REQUESTEE FOR NAME',
    CONCAT(CONCAT(u2.FIRSTNAME, ' ', u2.LASTNAME), '  (', u2.username, ')') AS 'REQUESTEE NAME',

	ENDPOINTASCSV AS 'APPLICATION',
	CASE
		WHEN ar.requesttype != 11 THEN IFNULL(v.entitlement_value, 'Account')
		ELSE r.role_name
	END AS 'REQUESTED ENTITLEMENT',
	UREQESTOR.username AS 'REQUESTED BY',
	CASE
		WHEN aa.status = 1 THEN (SELECT U4.USERNAME FROM USERS U4 WHERE U4.USERKEY=APPROVERKEY LIMIT 1)
		ELSE u.username 
	END AS 'ASSIGNEE ID',
	CASE
		WHEN aa.status = 1 THEN (SELECT CONCAT(U5.FIRSTNAME,' ',U5.LASTNAME) FROM USERS U5 WHERE U5.USERKEY=APPROVERKEY LIMIT 1)
		ELSE CONCAT(u.FIRSTNAME, ' ', u.LASTNAME) 
	END AS 'ASSIGNEE NAME',
	CASE
		WHEN aa.status = 1 THEN 'New'
		WHEN aa.STATUS = 2 THEN 'Approved'
		WHEN aa.STATUS = 3 THEN 'Rejected'
		WHEN aa.status = 4 THEN 'Escalated'
		WHEN aa.status = 5 THEN 'Expired'
		WHEN aa.STATUS = 6 THEN 'Discontinued'
		ELSE aa.STATUS
	END AS 'REQUEST STATUS',
	ra.status 'REQUEST ACCESS STATUS'
FROM
	ARS_REQUESTS ar LEFT JOIN request_access ra ON ar.REQUESTKEY = ra.REQUESTKEY
	LEFT JOIN ACCESS_APPROVERS aa ON ra.REQUEST_ACCESSKEY = aa.REQUEST_ACCESS_KEY
	LEFT JOIN USERS U ON u.userkey = aa.APPROVEBY
	LEFT JOIN USERS u2 ON u2.userkey = ra.userkey
	LEFT JOIN arstasks ars ON ars.requestaccesskey = ra.request_accesskey
	LEFT JOIN entitlement_values v ON v.entitlement_valuekey = ra.accesskey
	LEFT JOIN USERS UREQESTOR ON UREQESTOR.userkey = ar.requestor
	LEFT JOIN roles r ON r.ROLEKEY = ra.ACCESSKEY
WHERE
	ars.ASSIGNEDFROMRule IS NULL
	AND ar.ENDPOINTASCSV = 'ENDPOINTNAME' 
	ORDER BY 
		jbpmprocessinstanceid,
		JBPM_ACTIVITY_NAME,
		u.username

However, I can't find the status of the requests
Approved” or ‘Rejected’ status and all the others 

I'm getting the status of the task, of the request in the request, but not the request itself

I also have all the details of the steps of the request when I only want to know what access were given during the request

Thank you

[rushikeshvartak · Accepted solution]

SELECT    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'
                    WHEN ar.requesttype = 3 THEN 'New Account'
                    WHEN ar.requesttype = 12 THEN 'Update Account'
                    WHEN ar.requesttype = 18 THEN 'Create User'
                    WHEN ar.requesttype = 19 THEN 'Update User'
                    WHEN ar.requesttype = 30 THEN 'Disable Account'
                    WHEN ar.requesttype = 30 THEN 'Enable Account'
                    ELSE ar.requesttype
          END            AS 'REQUEST TYPE',
          ar.requestdate AS 'REQUEST SUBMIT DATE',
          CASE
                    WHEN aa.status = 1 THEN
                              (
                                     SELECT Concat(u3.firstname, ' ', u3.lastname)
                                     FROM   users U3
                                     WHERE  u3.userkey=approverkey limit 1)
                    ELSE concat(u.firstname, ' ', u.lastname)
          END                                                                               AS 'REQUESTEE FOR NAME',
                    concat(concat(u2.firstname, ' ', u2.lastname), '  (', u2.username, ')') AS 'REQUESTEE NAME',
          endpointascsv                                                                     AS 'APPLICATION',
          CASE
                    WHEN ar.requesttype != 11 THEN ifnull(v.entitlement_value, 'Account')
                    ELSE r.role_name
          END                AS 'REQUESTED ENTITLEMENT',
          ureqestor.username AS 'REQUESTED BY',
          CASE
                    WHEN aa.status = 1 THEN
                              (
                                     SELECT u4.username
                                     FROM   users u4
                                     WHERE  u4.userkey=approverkey limit 1)
                    ELSE u.username
          END AS 'ASSIGNEE ID',
          CASE
                    WHEN aa.status = 1 THEN
                              (
                                     SELECT concat(u5.firstname,' ',u5.lastname)
                                     FROM   users u5
                                     WHERE  u5.userkey=approverkey limit 1)
                    ELSE concat(u.firstname, ' ', u.lastname)
          END AS 'ASSIGNEE NAME',
          CASE
                    WHEN aa.status = 1 THEN 'New'
                    WHEN aa.status = 2 THEN 'Approved'
                    WHEN aa.status = 3 THEN 'Rejected'
                    WHEN aa.status = 4 THEN 'Escalated'
                    WHEN aa.status = 5 THEN 'Expired'
                    WHEN aa.status = 6 THEN 'Discontinued'
                    ELSE aa.status
          END AS 'REQUEST STATUS',
          ra.status 'REQUEST ACCESS STATUS',
          CASE
                    WHEN ar.status = 1 THEN 'Open'
                    WHEN ar.status = 3 THEN 'Completed'
                    WHEN ar.status = 4 THEN 'Expired'
                    WHEN ar.status = 6 THEN 'Discontinued'
                    ELSE NULL
          END 'Overall request status', (
          CASE ars.status
                    WHEN 1 THEN 'New'
                    WHEN 2 THEN 'In Progress'
                    WHEN 3 THEN 'Completed'
                    WHEN 4 THEN 'Discontinued'
                    WHEN 8 THEN 'Error'
                    WHEN 9 THEN 'No Action Required'
          END) AS 'TASK STATUS'
FROM      ars_requests ar
LEFT JOIN request_access ra
ON        ar.requestkey = ra.requestkey
LEFT JOIN access_approvers aa
ON        ra.request_accesskey = aa.request_access_key
LEFT JOIN users u
ON        u.userkey = aa.approveby
LEFT JOIN users u2
ON        u2.userkey = ra.userkey
LEFT JOIN arstasks ars
ON        ars.requestaccesskey = ra.request_accesskey
LEFT JOIN entitlement_values v
ON        v.entitlement_valuekey = ra.accesskey
LEFT JOIN users ureqestor
ON        ureqestor.userkey = ar.requestor
LEFT JOIN roles r
ON        r.rolekey = ra.accesskey
WHERE     ars.assignedfromrule IS NULL
AND       ar.endpointascsv = 'ENDPOINTNAME'
ORDER BY  jbpmprocessinstanceid,
          jbpm_activity_name,
          u.username

[HichamElk]

Thank you, I used the Overall Request Status