Query to get the OIM process Task failure Count and its failure reason

select ml.MIL_NAME, ml.MIL_KEY,count(*) cnt

from MIL ml, OSI os, SCH sc

where

ml.MIL_KEY = os.MIL_KEY

and os.SCH_KEY = sc.SCH_KEY

and sc.SCH_STATUS = 'R'

and os.osi_retry_counter > 0

--and os.osi_retry_on >= sysdate - 15

group by ml.mil_name, ml.mil_key

order by cnt desc

Task failure reason

select ml.mil_name, ml.mil_key, os.orc_key,usr.usr_login,sc.sch_reason,count(*) cnt

from MIL ml, OSI os, SCH sc, oiu oiu, usr usr

where

 os.orc_key = oiu.orc_key

and oiu.usr_key=usr.usr_key

and  ml.MIL_KEY = '82'

and ml.MIL_KEY = os.MIL_KEY

and os.SCH_KEY = sc.SCH_KEY

and sc.sch_status = 'R'

group by ml.MIL_NAME, ml.MIL_KEY, os.orc_key,usr.usr_login,sc.sch_reason

order by cnt asc