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 ,