How to clean up resource history for an account of a user in OIM

Ever came across a trouble some account in the list of accounts provisioned to a user ? Ever felt to mark all the tasks triggered on that particular instance as cancel? 

If the answer is yes, then you have the solution below.


            UPDATE SCH SET SCH_STATUS='X' where SCH_KEY IN (
            SELECT SCH.SCH_KEY 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 ! ='Delete User'
  
            AND OIU.ORC_KEY =GIVE ORC KEY HERE);