Wednesday, October 14, 2009

Query to find all users associated with a resource in a provisioning or Provisioned state

/*List of all the users allocated with a Resource with a Specific Status*/
SELECT distinct(usr.usr_login),usr.usr_create,obj.obj_name,ost.ost_status from usr,act,oiu,obi,obj,ugp,usg,oug ,ugp ugp2,ost WHERE usr.act_key = act.act_key AND usr.usr_key = oiu.usr_key AND oiu.obi_key = obi.obi_key AND oiu.ost_key = ost.ost_key AND obi.obj_key = obj.obj_key AND usg.usr_key = usr.usr_key AND usg.ugp_key = ugp2.ugp_key AND obj.obj_key = oug.obj_key AND oug.ugp_key = ugp.ugp_key AND UPPER(obj.obj_name) like UPPER ('DR OID RO')AND OST.OST_STATUS LIKE 'Provisioning' and oug.obj_key in ( select distinct(d.obj_key) from ugp a, usg b , oug d ,ost s,obj where UPPER(obj.obj_name) like UPPER ('DR OID RO')AND s.OST_STATUS LIKE 'Provisioning' and b.ugp_key = a.ugp_key and d.ugp_key = a.ugp_key )order by usr.usr_create desc;