Query to find the list of all process tasks associated with a resource object.

 Query to find the list of all process tasks associated with a resource object in OIM.


select * from mil
join tos on tos.tos_key = mil.tos_key
join pkg on tos.pkg_key = pkg.pkg_key
join obj on  pkg.obj_key = obj.obj_key
where obj.obj_name = [Give the resource object name here in single quotes] 



Well, lets go a step further...

I want to know the adapter that is mapped to each task listed with above query too...

hmm okay... here you go...


select OBJ.OBJ_NAME, MIL.MIL_NAME,ADP.ADP_NAME from mil
join tos on tos.tos_key = mil.tos_key
join pkg on tos.pkg_key = pkg.pkg_key
join obj on  pkg.obj_key = obj.obj_key
LEFT OUTER JOIN ADP ON ADP.EVT_KEY = MIL.EVT_KEY
where obj.obj_name = [Give the resource object name here in single quotes] 


So sweet .... right?

Lets go one step ahead, How nice would it be, if I have a query that will tell me, the process task name, the adapter that the task is mapped to, and all the task adapter mappings, for a given resource object....

In such a case, we can save some time, instead of opening design console... right?

Alright... here is your query...

select OBJ.OBJ_NAME, Mil.mil_key,MIL.MIL_NAME,adp.adp_key,ADP.ADP_NAME,ADV.ADV_NAME, MAV.MAV_MAP_TO,MAV.MAV_MAP_QUALIFIER,MAV.MAV_MAP_VALUE from mil
join tos on tos.tos_key = mil.tos_key
join pkg on tos.pkg_key = pkg.pkg_key
join obj on  pkg.obj_key = obj.obj_key
LEFT OUTER JOIN ADP ON ADP.EVT_KEY = MIL.EVT_KEY
left outer join mav on mav.mil_key = mil.mil_key
left outer JOIN ADV ON MAV.ADV_KEY = ADV.ADV_KEY
where obj.obj_name = [Give the resource object name here in single quotes] order by 3 asc


loved the journey?  :)