Query to find out the list of all 'Create User' tasks that did not succeed on a given application instance in OIM

Query to find out the list of all 'Create User' tasks that did not succeed on a given application instance in OIM

 

Say, you are asked as below... Hey.... can you give me the list of all 'Create User' tasks that got rejected on a given application instance say... 'ActiveDirectory'... 

How to find that.... no issues, use below query.... :) 

SELECT APP_INSTANCE.APP_INSTANCE_name,mil.mil_name, sch.SCH_STATUS FROM OIU, APP_INSTANCE, ORC, OSI, SCH, MIL, USR, OST
WHERE OIU.USR_KEY = USR.USR_KEY
AND OIU.APP_INSTANCE_KEY = APP_INSTANCE.APP_INSTANCE_KEY
AND OIU.ORC_KEY = ORC.ORC_KEY
AND ORC.ORC_KEY = OSI.ORC_KEY
AND OSI.SCH_KEY = SCH.SCH_KEY
AND MIL.MIL_KEY = OSI.MIL_KEY
and oiu.ost_key = OST.OST_KEY
and MIL.MIL_NAME  ='Create User'
and APP_INSTANCE.APP_INSTANCE_NAME = 'ActiveDirectory'
and SCH_STATUS!='C'