How to exclude the Request if the Approver list Contains Admin User along with another approver

[AravindK]

Hi Team,

We have an requirement to extract the List of All the Requests which are pending with Admin(user).

Below Query is perfectly working fine if the Request is pending with only Admin User( I mean for request only one approver is there which is Admin).

However , if the Approver list Contains Admin User along with another approver - These requests should be ignored.
 Ex : For one requests, there are two or three approvers , One Approver is Admin and rest are other users - These Requests should be excluded from the report. 

SELECT DISTINCT SUBSTR(jbpmprocessinstanceid, INSTR(jbpmprocessinstanceid, '.') + 1, LENGTH(jbpmprocessinstanceid)) AS 'REQUEST ID', ar.REQUESTTYPE as 'Request Type', ar.STATUS as 'Request Status', ar.REQUESTDATE as 'Request Date', ar.DUEDATE as 'Due Date', ar.REQUESTOR as 'Requestor', ar.REQUESTORIGIN as 'Request Origin', ar.workflowname as 'Workflow Name', U.FIRSTNAME as 'Approver First Name', U.LASTNAME as 'Approver Last Name', U.USERKEY as 'Approver User ID' FROM ARS_REQUESTS AR, REQUEST_ACCESS RA, ACCESS_APPROVERS AA, Users U WHERE AR.REQUESTKEY = RA.REQUESTKEY AND AA.REQUEST_ACCESS_KEY = RA.REQUEST_ACCESSKEY AND AA.APPROVERKEY = U. USERKEY and AA.STATUS = 1 and AR.STATUS not in (4) and aa.APPROVERKEY in (Select USERKEY from user_savroles usa, savroles sav where usa.rolekey = sav.rolekey and usa.userkey=1)

Can someone modify the query as per the above requirement and share please?

[rushikeshvartak]

Above query works when even if admin is alone or admin is with some other approver 

SELECT DISTINCT SUBSTR(jbpmprocessinstanceid, INSTR(jbpmprocessinstanceid, '.') + 1, LENGTH(jbpmprocessinstanceid)) AS 'REQUEST ID', ar.REQUESTTYPE as 'Request Type', ar.STATUS as 'Request Status', ar.REQUESTDATE as 'Request Date', ar.DUEDATE as 'Due Date', ar.REQUESTOR as 'Requestor', ar.REQUESTORIGIN as 'Request Origin', ar.workflowname as 'Workflow Name', U.FIRSTNAME as 'Approver First Name', U.LASTNAME as 'Approver Last Name', U.USERKEY as 'Approver User ID' FROM ARS_REQUESTS AR, REQUEST_ACCESS RA, ACCESS_APPROVERS AA, Users U WHERE AR.REQUESTKEY = RA.REQUESTKEY AND AA.REQUEST_ACCESS_KEY = RA.REQUEST_ACCESSKEY AND AA.APPROVERKEY = U. USERKEY and AA.STATUS = 1 and AR.STATUS not in (4) and aa.APPROVERKEY in (Select USERKEY from user_savroles usa, savroles sav where usa.rolekey = sav.rolekey and usa.userkey=1)

[AravindK]

Hi @rushikeshvartak  Thanks for the response.

Above query works when even if admin is alone or admin is with some other approver 

  - yes it returns the data of both cases but we need the data of only Admin alone and the requests which are pending with admin along with some other approver should be ignored. Is there anyway to modify the above query to get the requests only pending with admin alone ?

Thanks,
Aravind

[rushikeshvartak · Accepted solution]

SELECT DISTINCT Substr(jbpmprocessinstanceid, Instr(jbpmprocessinstanceid, '.')
                                              + 1, Length(
                                jbpmprocessinstanceid)) AS 'REQUEST ID',
                ar.requesttype                          AS 'Request Type',
                ar.status                               AS 'Request Status',
                ar.requestdate                          AS 'Request Date',
                ar.duedate                              AS 'Due Date',
                ar.requestor                            AS 'Requestor',
                ar.requestorigin                        AS 'Request Origin',
                ar.workflowname                         AS 'Workflow Name',
                U.firstname                             AS 'Approver First Name'
                ,
                U.lastname                              AS
                'Approver Last Name',
                U.userkey                               AS 'Approver User ID'
FROM   ars_requests AR,
       request_access RA,
       access_approvers AA,
       users U
WHERE  AR.requestkey = RA.requestkey
       AND AA.request_access_key = RA.request_accesskey
       AND AA.approverkey = U.userkey
       AND AA.status = 1
       AND AR.status NOT IN ( 4 )
       AND AA.approverkey IN (SELECT userkey
                              FROM   user_savroles usa,
                                     savroles sav
                              WHERE  usa.rolekey = sav.rolekey
                                     AND usa.userkey = 1)
GROUP  BY AR.requestkey,
          ar.requesttype,
          ar.status,
          ar.requestdate,
          ar.duedate,
          ar.requestor,
          ar.requestorigin,
          ar.workflowname,
          U.firstname,
          U.lastname,
          U.userkey
HAVING Count(AA.approverkey) = 1; 

[AravindK]

Hi @rushikeshvartak ,

It is working partially but some cases it is failing. 

Ex: Below request pending with two approvers but still this request is coming in the report with above query

when we check in the approverkey coloumn it stores two approverkeys like (1,another approverkey)