11/11/2022 03:53 AM
Hi Team,
I'm trying to export all requests completed within 7 days
I'm using the below query but it returning a zero record
Can team help me with this
SELECT DISTINCT
u2.USERNAME as 'REQUESTED FOR', u2.DISPLAYNAME, u2.JOBDESCRIPTION as "JOB TITLE", u2.DEPARTMENTNAME as "DEPARTMENT", u2.EMPLOYEETYPE as "TYPE OF EMPLOYEMENT", u2.CUSTOMPROPERTY6 as "LINE MANAGER EMAIL", SUBSTRING_INDEX(ars_requests.JBPMPROCESSINSTANCEID, '.', - 1) AS 'REQUEST_ID', ars_requests.REQUESTDATE, CASE WHEN ars_requests.REQUESTTYPE = '1' THEN 'ADD'
WHEN ars_requests.REQUESTTYPE = '3' THEN 'NEWACCOUNT'
WHEN ars_requests.REQUESTTYPE = '12' THEN 'UPDATEACCOUNT' WHEN ars_requests.REQUESTTYPE = '2' THEN 'DELETE' END AS 'REQUEST_TYPE', CASE ra.ACCESSTYPE WHEN '3' THEN 'NEW_ACCOUNT_REQUEST' WHEN '2' THEN 'ENTITLEMENT_VALUE'WHEN '1' THEN 'ROLE' END AS ACCESS_TYPE, group_concat((SELECT entitlement_value FROM entitlement_values WHERE entitlement_values.ENTITLEMENT_VALUEKEY = ra.ACCESSKEY))'ENTITLEMENT_VALUE'
FROM request_access ra, ars_requests, users u2
WHERE
ra.REQUESTKEY = ars_requests.REQUESTKEY
AND ra.STATUS IN (2 , 3)
AND ars_requests.ENDPOINTASCSV LIKE 'Oracle_ERP_TH'
AND ars_requests.COMPLETEDATE > DATE_SUB(CURDATE(), INTERVAL 7 DAY)
AND ra.userkey= u2.userkey
and ra.ACCESSTYPE not in (3)
group by SUBSTRING_INDEX(ars_requests.JBPMPROCESSINSTANCEID, '.', - 1);
11/11/2022 07:56 AM
Hi ,
How about this..Just tested in one of my DEV env.It seem to pull the data.
SELECT DISTINCT
u2.USERNAME as 'REQUESTED FOR', u2.DISPLAYNAME, u2.JOBDESCRIPTION as "JOB TITLE", u2.DEPARTMENTNAME as "DEPARTMENT", u2.EMPLOYEETYPE as "TYPE OF EMPLOYEMENT", u2.CUSTOMPROPERTY6 as "LINE MANAGER EMAIL", SUBSTR(jbpmprocessinstanceid, INSTR(jbpmprocessinstanceid, '.') ) AS 'REQUEST_ID', ar.REQUESTDATE, CASE WHEN ar.REQUESTTYPE = '1' THEN 'ADD'
WHEN ar.REQUESTTYPE = '3' THEN 'NEWACCOUNT'
WHEN ar.REQUESTTYPE = '12' THEN 'UPDATEACCOUNT' WHEN ar.REQUESTTYPE = '2' THEN 'DELETE' END AS 'REQUEST_TYPE', CASE ra.ACCESSTYPE WHEN '3' THEN 'NEW_ACCOUNT_REQUEST' WHEN '2' THEN 'ENTITLEMENT_VALUE'WHEN '1' THEN 'ROLE' END AS ACCESS_TYPE, group_concat((SELECT entitlement_value FROM entitlement_values WHERE entitlement_values.ENTITLEMENT_VALUEKEY = ra.ACCESSKEY))'ENTITLEMENT_VALUE'
FROM request_access ra, ars_requests ar, users u2
WHERE
ra.REQUESTKEY = ar.REQUESTKEY
AND ra.STATUS IN (2 , 3)
AND ar.ENDPOINTASCSV LIKE 'Oracle_ERP_TH'
AND ar.REQUESTDATE> DATE_SUB(NOW(),INTERVAL 7 DAY)
AND ra.userkey= u2.userkey
and ra.ACCESSTYPE not in (3)
ORDER BY jbpmprocessinstanceid;
Thanks
Shyam
11/11/2022 07:13 PM
Hi Shyam
Initially, I tested with ar.REQUESTDATE , but the issue with this is, suppose a user has submitted the request 20 days back and his request was approved today by the manager, then that user will not come in my report.
My expectation is all requests, were completed within an interval of 7 days irrespective of the request submitted date. then request and user details should be present in my report.
11/11/2022 08:51 PM
Join with access_approvers table to get max date i.e. approval date
11/13/2022 12:41 AM
Hi Rushikesh
Our environment's Current version is SP3.12.14
I'm not sure, if it is the version issue or not but this table(access_approvers) is not present in the data analyzer's Schema table from UI
And about this table(ars_requests),This column(COMPLETEDATE) is full blank
11/13/2022 06:20 AM
Completedate under ars_request won’t populate.
access_approvers table might not exposed in data analyzer
refer tables