Item was revoked but has not been removed - Items database query

[#1]

Many times in certification we see the message "Item was revoked but has not been removed.", this comes when the certifier takes the action on the item and either it get failed (in case of the connected system) or have generated the Work item or ticket and the file is not aggregated back . below is the query which gives the information of the such items such as the identity, entitlement name, application to which this entitlement belongs, native identity of the user for the application and date on which the certifier took the action . 

This query can we further modified to get more information

SELECT 
SPT_IDENTITY.NAME,
SPT_IDENTITY_ENTITLEMENT.VALUE,
SPT_CERTIFICATION_ITEM.EXCEPTION_APPLICATION,
SPT_PROVISIONING_TRANSACTION.STATUS,
SPT_IDENTITY_ENTITLEMENT.NATIVE_IDENTITY,
SPT_CERTIFICATION_ACTION.STATUS,
( To_date('1970-01-01 00', 'yyyy-mm-dd hh24') + ( SPT_CERTIFICATION_ACTION.DECISION_DATE) / 1000 / 60 / 60 / 24 )                    AS "CERT_DECISION_DATE" 
FROM 
  SPT_IDENTITY_ENTITLEMENT,
  SPT_CERTIFICATION_ITEM,
  SPT_CERTIFICATION_ACTION,
  SPT_IDENTITY,
  SPT_APPLICATION,
  SPT_MANAGED_ATTRIBUTE,
  SPT_CERTIFICATION_ENTITY,
  SPT_PROVISIONING_TRANSACTION
WHERE 
CERTIFICATION_ITEM IS NOT NULL
AND SPT_CERTIFICATION_ITEM.ID=SPT_IDENTITY_ENTITLEMENT.CERTIFICATION_ITEM
AND SPT_CERTIFICATION_ACTION.ID=SPT_CERTIFICATION_ITEM.ACTION
AND SPT_CERTIFICATION_ACTION.STATUS='Remediated'
AND SPT_IDENTITY.ID=SPT_IDENTITY_ENTITLEMENT.IDENTITY_ID
AND SPT_CERTIFICATION_ITEM.EXCEPTION_APPLICATION=SPT_APPLICATION.NAME
AND SPT_MANAGED_ATTRIBUTE.APPLICATION=SPT_APPLICATION.ID
AND SPT_MANAGED_ATTRIBUTE.VALUE=SPT_IDENTITY_ENTITLEMENT.VALUE 
AND SPT_CERTIFICATION_ENTITY.TARGET_ID=SPT_IDENTITY.ID 
AND SPT_CERTIFICATION_ENTITY.CERTIFICATION_ID=SPT_PROVISIONING_TRANSACTION.CERTIFICATION_ID 
AND SPT_PROVISIONING_TRANSACTION.SOURCE='Certification' 
AND SPT_PROVISIONING_TRANSACTION.application_NAME=SPT_APPLICATION.NAME 
AND SPT_IDENTITY_ENTITLEMENT.NATIVE_IDENTITY=SPT_PROVISIONING_TRANSACTION.NATIVE_IDENTITY

https://sailpointworks.blogspot.com/2020/02/sailpoint-iiq-item-was-revoked-but-has-not-removed-certi...

[anihema]

Can we get account details? which came back in current certification.

The above query gives information only on Entitlements.

[nmakgato]

Is there a way to bulk process revocations for these type of entitlements?