Sailpoint IIQ List of Role Mapped for Entitlement API DB Query

SQL to get the list of Business IT Role in Sailpoint IIQ which is mapped for the particular Entitlement

select bun.id,bun.type,bun.name,bun.display_name,bun.requestable,
prof_cons.ELT
from spt_bundle bun,spt_profile profile,spt_profile_constraints prof_cons,spt_bundle_requirements bun_req
where bun.id=profile.bundle_id and
profile.id=prof_cons.profile and
bun_req.child = bun.id
and prof_cons.ELT like '%%'

JAVA API to get the list of Business IT Role in Sailpoint IIQ which is mapped for the particular Entitlement

String appName="Active Directory";
String entitlementName = "CN=blah, etc, etc";
QueryOptions profileQo = new QueryOptions();
profileQo.addFilter(Filter.eq("application.name", appName));

List profiles = context.getObjects(Profile.class, profileQo);
if (profiles != null) {
    for (Object profileObj : profiles) {
        Profile p = (Profile) profileObj;
        List constraints = p.getConstraints();
        List filterList = null;
        if (constraints != null) {
            for (Object filter : constraints) {
                filterList = new ArrayList();
                if (filter instanceof sailpoint.object.Filter.LeafFilter) {
                    sailpoint.object.Filter.LeafFilter f = (sailpoint.object.Filter.LeafFilter) filter;
                    //Filter can have multiple values 
                    Object value = f.getValue();
                    if (value instanceof ArrayList) {
                        ArrayList entitlements = new ArrayList();
                        entitlements = (ArrayList) value;
                        for (Object obj : entitlements) {
                            if (obj.toString().equals(entitlementName)) {
                                Bundle bun = p.getBundle();
                                // Do something with the Bundle here
                            }
                        }
                    }
                }
            }
        }
    }
}

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

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