Many time 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 Workitem 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 certifer 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
