SQL ACCESS REVIEW CERTIFICATION ACTION ITEMS DETAILS SAILPOINT IDENTITY IQ

This query is used to get the details about the Access Review , Certification which is generated and will give all the details 
for all the items for which the action is already taken.

SELECT SPT_CERTIFICATION.ID AS "CERT_ID",
   (
      TO_DATE('1970-01-01 00', 'YYYY-MM-DD HH24') + (SPT_CERTIFICATION.ACTIVATED) / 1000 / 60 / 60 / 24
   )
   AS "CREATE DATE",
   (
      TO_DATE('1970-01-01 00', 'YYYY-MM-DD HH24') + (SPT_CERTIFICATION.EXPIRATION) / 1000 / 60 / 60 / 24
   )
   AS "EXPIRATION DATE",
   (
      TO_DATE('1970-01-01 00', 'YYYY-MM-DD HH24') + (SPT_CERTIFICATION.SIGNED) / 1000 / 60 / 60 / 24
   )
   AS "SIGNED DATE",
  SPT_CERTIFICATION.SHORT_NAME AS "CERT_NAME",
  SPT_CERTIFICATION_ITEM.SUMMARY_STATUS AS "CERT_STATUS",
  SPT_CERTIFICATION.MANAGER AS "CERT_ASSIGNED_TO_ID",
  SPT_IDENTITY.EMAIL AS "CERT_ASSIGNED_TO_EMAIL",
  SPT_IDENTITY.DISPLAY_NAME AS "CERT_ASSIGNED_TO_NAME",
  
  user2.DISPLAY_NAME AS "MANAGER_DISPLAY_NAME",
  USER2.EMAIL AS "MANAGER_EMAIL",
  
  SPT_CERTIFICATION_ENTITY.TARGET_NAME AS "USER_ID",
  SPT_CERTIFICATION_ENTITY.TARGET_DISPLAY_NAME AS "USER_DISPLAY_NAME",
  SPT_CERTIFICATION_ENTITY.FIRSTNAME AS "USER_FIRST_NAME",
  SPT_CERTIFICATION_ENTITY.LASTNAME AS "USER_LAST_NAME",
  user1.EMAIL AS "USER_EMAIL",
  
  SPT_CERTIFICATION.TOTAL_ENTITIES,
  SPT_CERTIFICATION.EXCLUDED_ENTITIES,
  SPT_CERTIFICATION.COMPLETED_ENTITIES,
  SPT_CERTIFICATION.PERCENT_COMPLETE,
  SPT_CERTIFICATION.CERTIFIED_ENTITIES,
  SPT_CERTIFICATION.TOTAL_ITEMS,
  SPT_CERTIFICATION.EXCLUDED_ITEMS,
  SPT_CERTIFICATION.COMPLETED_ITEMS,
  SPT_CERTIFICATION.ITEM_PERCENT_COMPLETE,
  SPT_CERTIFICATION.CERTIFIED_ITEMS,
  SPT_CERTIFICATION.REMEDIATIONS_KICKED_OFF,
  SPT_CERTIFICATION.REMEDIATIONS_COMPLETED,
   (
      TO_DATE('1970-01-01 00', 'YYYY-MM-DD HH24') + (SPT_CERTIFICATION.MODIFIED) / 1000 / 60 / 60 / 24
   )
   AS "CERT_UPDATE_DATE",
   
  SPT_CERTIFICATION_ITEM.EXCEPTION_APPLICATION AS "ACCOUNT_APPLICATION_NAME",
  SPT_CERTIFICATION_ITEM.EXCEPTION_ATTRIBUTE_NAME AS "RECORD_TYPE",
  SPT_IDENTITY_ENTITLEMENT.NATIVE_IDENTITY AS "ACCOUNT_NAME",
  SPT_CERTIFICATION_ITEM.EXCEPTION_ATTRIBUTE_VALUE AS "ACCOUNT_ENTITLEMENT_NAME",
  SPT_CERTIFICATION_ACTION.STATUS AS "CERT_DECISION",
  SPT_CERTIFICATION_ACTION.DESCRIPTION AS "CERT_DECISION_COMMENTS",
  SPT_CERTIFICATION_ACTION.REMEDIATION_ACTION AS "REMEDIATION_ACTION",
  TO_CHAR(SPT_CERTIFICATION_ACTION.REMEDIATION_DETAILS) AS "REMEDIATION_DETAILS",
   (
      TO_DATE('1970-01-01 00', 'YYYY-MM-DD HH24') + (SPT_CERTIFICATION_ACTION.DECISION_DATE) / 1000 / 60 / 60 / 24
   )
   AS "CERT_DECISION_DATE",
  TO_CHAR(Regexp_substr(SPT_CERTIFICATION_ACTION.REMEDIATION_DETAILS, 'requestID="(.*?)"\sstatus', 1, 1, NULL, 1)) AS TICKET,
  TO_CHAR(Regexp_substr(SPT_CERTIFICATION_ACTION.REMEDIATION_DETAILS, 'status="(.*?)"\/', 1, 1, NULL, 1)) AS TICKET_STATUS,
  SPT_CERTIFICATION_ITEM.ACTION AS "ACTION"
FROM SPT_CERTIFICATION_ENTITY,
  SPT_CERTIFICATION,
  SPT_CERTIFICATION_ITEM,
  SPT_IDENTITY,
  SPT_CERTIFICATION_ACTION,
  SPT_IDENTITY user1,
  SPT_IDENTITY user2,
  SPT_IDENTITY_ENTITLEMENT,
  SPT_APPLICATION
WHERE SPT_CERTIFICATION_ENTITY.CERTIFICATION_ID = SPT_CERTIFICATION.ID
  AND SPT_CERTIFICATION_ITEM.CERTIFICATION_ENTITY_ID = SPT_CERTIFICATION_ENTITY.ID
  AND SPT_IDENTITY.NAME = SPT_CERTIFICATION.MANAGER
  AND SPT_CERTIFICATION_ACTION.ID = SPT_CERTIFICATION_ITEM.ACTION
  AND SPT_CERTIFICATION_ENTITY.TARGET_NAME = user1.NAME
  AND user1.MANAGER = user2.ID
  AND SPT_CERTIFICATION_ITEM.EXCEPTION_APPLICATION = SPT_APPLICATION.NAME
  AND SPT_CERTIFICATION_ITEM.EXCEPTION_ATTRIBUTE_VALUE = SPT_IDENTITY_ENTITLEMENT.VALUE
  AND user1.ID = SPT_IDENTITY_ENTITLEMENT.IDENTITY_ID
  AND SPT_APPLICATION.ID = SPT_IDENTITY_ENTITLEMENT.APPLICATION