SOD Reports

[Manu269]

Hello Team,

We need an assistance in creating a analytic report for SOD.

Requirement :

1. Manger need to able to extract this report.

2. As a part of this report he need to extract the reportee details for which entitlement was requested with SOD Conlficts and was approved with Business Justification.

Apart from above details we also need to fetch approval date, task completion date, task status.

Please assist with sample query.

[nimitdave]

request_exceptions table captures SOD Data for the requests,

[Manu269]

@nimitdave  if possible can you help with sample report query?

[rushikeshvartak · Accepted solution]

SELECT distinct 

  CASE

      WHEN ar.STATUS = 1 THEN 'Open'

      WHEN ar.STATUS = 3 THEN 'Completed'

  END AS 'Request Status',

  ar.ENDPOINTASCSV as ENDPOINTNAME,

  u.USERNAME,

  SUBSTRING_INDEX(ar.JBPMPROCESSINSTANCEID, '.', - 1) AS RequestId,

  ev.ENTITLEMENT_VALUE as 'Entitlement',

   case 

when ev.status = 1 then 'ACTIVE'

when ev.status = 2 then 'INACTIVE' end as 'ENTITLEMENT STATUS VALUE',

re.REQUEST_EXCEPTIONSKEY,re.APPROVEDBY,re.APPROVEDON,re.COMMENTS cmt ,re.EXCEPTIONKEY,re.EXCEPTIONLEVEL,re.EXCEPTIONNAME,re.MITIGATINGCONTROL,re.MITIGATINGCONTROLDETAILS,re.mitigatingenddate,re.mitigatingstartdate,re.REQUESTKEY,re.SODDETAILDATA,re.USERKEY,re.EXTERNALSODRISKLEVEL,re.TYPE

FROM

  request_exceptions re,

  ars_requests ar,

  users u,

  request_access ra,

  entitlement_values ev

WHERE

  re.REQUESTKEY = ar.REQUESTKEY

  and ra.REQUESTKEY = ar.REQUESTKEY

  and ev.ENTITLEMENT_VALUEKEY = ra.ACCESSKEY

      AND re.USERKEY = u.USERKEY

      AND ar.STATUS IN (1 , 3) 

      AND ra.accesstype = 2

and SUBSTRING_INDEX(ar.JBPMPROCESSINSTANCEID, '.', - 1)='4420827'

limit 100;

[Manu269]

@rushikeshvartak  many thanks.

Let me check and update you.

[sai_sp]

@Manu269  you can get this data from the OOB summary report as well from the SOD workbench. Search for the user you need the info for and export the report.