Useful OIM Queries and tables in 11G R2 PS3

1. ORG_USER_MEMBERSHIPS --- Users Organizations details
The below query give the list of users belongs to organization XXXXX

select USR_LOGIN,USR_DISPLAY_NAME,ACT_NAME as "ORGANIZATION" from ORG_USER_MEMBERSHIPS,usr,act
where
ORG_USER_MEMBERSHIPS.act_key = act.act_key
and usr.usr_key =ORG_USER_MEMBERSHIPS.usr_key
and ACT_NAME = 'XXXXX'
order by ACT_NAME desc;


2. USR -  User and Users Manager login in single query


select u.USR_KEY,u.USR_LOGIN,u.USR_DISPLAY_NAME,
u1.USR_KEY as "Manager USR_KEY",u1.USR_LOGIN as "Manager Login",u1.USR_DISPLAY_NAME as "Manager DisplayName"
from usr u
join usr u1 on u1.USR_KEY = u.USR_MANAGER_KEY
--where u.usr_login ='XXXX'; for XXXX user manager details


3. USR -- The below query gives you list of users whose manager is XXXXXXX

select u1.USR_KEY,u1.USR_LOGIN,u1.USR_DISPLAY_NAME,u.USR_KEY as "Manager Key",u.USR_LOGIN as "Manager Login",u.USR_DISPLAY_NAME as "Manager DisplayName"
from usr u
join usr u1 on u1.USR_MANAGER_KEY = u.USR_KEY
where u.usr_login ='XXXXXXX';



4. List of process tasks triggered and their status for a particular application instance

select usr.usr_login,usr.usr_status,ai.app_instance_name,ml.mil_name,sc.sch_status
from MIL ml, OSI os,oiu oiu, usr usr,sch sc,app_instance ai
where os.orc_key = oiu.orc_key
and oiu.usr_key=usr.usr_key
and oiu.app_instance_key=ai.app_instance_key
--and mil_name='Create User'
and ai.app_instance_name = 'XXXXX'
and ml.MIL_KEY = os.MIL_KEY
and os.sch_key=sc.sch_key
--and sc.sch_status=upper('C')
and usr.usr_login ='XXXXX';


5. List of available account status for a particular application instance

select ost.OST_KEY,ost.OST_STATUS,app_instance.APP_INSTANCE_DISPLAY_NAME from ost
join obj on obj.obj_key = ost.obj_key
join app_instance on obj.obj_key = app_instance.object_key
where app_instance.APP_INSTANCE_DISPLAY_NAME ='XXXXXXX';


6. Change application status using SQL Query

ost_key you can get it from query 5

update oiu set oiu.ost_Key=XXX where oiu_key in (select oiu.oiu_key
from OIU join ost on ost.ost_key=oiu.ost_key join obi on obi.obi_key=oiu.obi_key
join obj on obj.obj_key=obi.obj_key join usr on usr.usr_key=oiu.usr_key join app_instance
on app_instance.app_instance_key=oiu.app_instance_key
where obj.obj_name = 'XXXXXXXX' and
app_instance.app_instance_name = 'XXXXXXXX' and USR_LOGIN ='XXXX');

7. List of Accounts attached to user with accounts status

select usr.usr_login, obj.obj_name, app_instance.app_instance_display_name,ost.ost_status
from usr, oiu, ost,orc, obi, obj,
app_instance
where oiu.usr_key=usr.usr_key
and ost.ost_key=oiu.ost_key
and oiu.obi_key=obi.obi_key
and obi.obj_key=obj.obj_key
and app_instance.app_instance_key=oiu.app_instance_key
and orc.orc_key=oiu.orc_key
and usr.usr_login='XXXXX';

8. List of roles published to particular organization

select  ugp_name ,ROLE_PUBLICATION_VW.act_name
from ROLE_PUBLICATION_VW,ugp,act
where
ROLE_PUBLICATION_VW.entity_id=ugp.ugp_key
and ROLE_PUBLICATION_VW.act_name=act.act_name
and act.act_name = 'XXXXXXXX';

all above queries are written based on my knowledge, please verify before using it.