OIM Query to fetch Process Tasks with Status as Cancelled

If you find yourself in a situation where you need to find all the process tasks that got cancelled,dont worry ,below query will help you to fetch that.

SELECT USR.USR_LOGIN,USR1.USR_LOGIN AS UPDATEBY_USERLOGIN,MIL.MIL_NAME,
SCH.SCH_STATUS,STA.STA_BUCKET,OSI.SCH_KEY,USR.USR_START_DATE,
SCH_ACTUAL_START, SCH_ACTUAL_END
FROM
USR,OSI,OIU,OST,ORC,OBJ,USR USR1,SCH,STA,MIL,TOS,PKG,UD_EBSUM
WHERE
OSI.MIL_KEY=MIL.MIL_KEY
AND OIU.USR_KEY=USR.USR_KEY
AND OIU.OST_KEY=OST.OST_KEY
AND OST.OBJ_KEY=OBJ.OBJ_KEY
AND OSI.ORC_KEY=OIU.ORC_KEY
AND SCH.SCH_KEY=OSI.SCH_KEY
AND STA.STA_STATUS=SCH.SCH_STATUS
AND TOS.PKG_KEY=PKG.PKG_KEY
AND MIL.TOS_KEY=TOS.TOS_KEY
AND OST.OST_STATUS = 'Provisioned'    // any account status
AND STA.STA_BUCKET = 'Cancelled'      // any process task status
AND PKG.PKG_NAME= 'EBSUM'  //resource name
AND ORC.ORC_KEY=UD_EBSUM.ORC_KEY
AND ORC.ORC_KEY=OIU.ORC_KEY
AND OSI.OSI_UPDATEBY = USR1.USR_KEY;

Table Name	Description
USR	All user information
OSI	Holds information about tasks that are created for an order
OIU	OBJECT INSTANCE REQUEST TARGET USER INFORMATION
OST	OST: OBJECT STATUS INFORMATION. - Contains users, resource objects and all objects
SCH	Scheduled Item Table - Used by tcScheduledTask to run scheduled Tasks
STA	Status Codes
TOS	Holds information about a process
MIL	Tasks in Processes - Contains all tasks from all processes.
PKG	Package Hierarchy Table Holds The Parent-child Relationships Between Processes
ORC	Order Content Item Table - Used by ScheduledTask to run a set of ordered events
OBJ	Resource Object definition information.

Note: You can use the above query to get process tasks for any status and resource object.