Oracle Identity Manager SQL Queries

SQL Query  to check Account Status

select usr.usr_login,usr.usr_key,ost.ost_status,obj.obj_name,oiu.obi_key
from OST INNER JOIN (OBJ INNER JOIN (OBI INNER JOIN (USR INNER JOIN OIU on oiu.usr_key = usr.usr_key)
on obi.obi_key = oiu.obi_key)
on obj.obj_key = obi.obj_key)
on ost.ost_key = oiu.ost_key
where usr.usr_login = 'XXXXX';

Checks the Policy Evaluation Queue

SELECT USR.USR_KEY,

USR.USR_LOGIN,

USR.USR_STATUS,

USER_PROVISIONING_ATTRS.POLICY_EVAL_NEEDED ,

USER_PROVISIONING_ATTRS.POLICY_EVAL_IN_PROGRESS

FROM USER_PROVISIONING_ATTRS USER_PROVISIONING_ATTRS ,

USR USR

WHERE USER_PROVISIONING_ATTRS.USR_KEY = USR.USR_KEY

AND USER_PROVISIONING_ATTRS.POLICY_EVAL_NEEDED =1

AND USER_PROVISIONING_ATTRS.POLICY_EVAL_IN_PROGRESS != 1

AND USR_STATUS NOT IN ('Deleted','Disabled','Rejected','Disabled Until Start Date');

Query to find Audit Records

select * from upa_fields where upa_usr_key in (select upa_usr_key from upa_usr where usr_key ='XXXXX') order by update_date;


Thanks to Vasanth for Sharing these queries ,