OIM Reports:Open Provisioning Tasks

Query for Open Provisioning Tasks:

Query to see the Open Tasks under Administration Tab on the Left pane in the OIM.

select distinct 
oti.sch_key as TaskKey,
mil.mil_name as TaskName,
oti.sta_bucket as TaskStatus,
oti.request_key,
obj.obj_name,
app_instance.app_instance_display_name as AppInstance,usr.usr_login as Beneficiary,
assigned_to_ugp.ugp_name AS assigned_to,
oti.osi_assign_type,
oti.osi_assigned_date 
from OTI,obj,mil,usr,orc,app_instance,oiu,oug,ugp assigned_to_ugp
  \where oti.obj_key=obj.obj_key and mil.mil_key=oti.mil_key and orc.orc_key=oti.orc_key and orc.usr_key=usr.usr_key and app_instance.app_instance_key=oiu.app_instance_key and app_instance.object_key = oug.obj_key
  and obj.obj_key=app_instance.object_key and app_instance_is_soft_delete='0'  AND assigned_to_ugp.ugp_key = oti.osi_assigned_to_ugp_key 
  and oti.osi_assign_type     = 'Group'   
  AND oti.sta_bucket         IN ('Pending', 'Rejected')
and oti.osi_assign_type =:AssignType
and oti.sta_bucket =:TaskStatus
  union 
  select distinct oti.sch_key as TaskKey,mil.mil_name as TaskName,oti.sta_bucket as TaskStatus,oti.request_key,obj.obj_name,app_instance.app_instance_display_name as AppInstance,usr.usr_login as Beneficiary,assigned_to_usr.usr_login AS assigned_to,
  oti.osi_assign_type,oti.osi_assigned_date from OTI,obj,mil,usr ,orc,app_instance,oiu,oug,usr assigned_to_usr
  where oti.obj_key=obj.obj_key and mil.mil_key=oti.mil_key and orc.orc_key=oti.orc_key and orc.usr_key=usr.usr_key and app_instance.app_instance_key=oiu.app_instance_key and app_instance.object_key = oug.obj_key
  and obj.obj_key=app_instance.object_key and app_instance_is_soft_delete='0'  AND assigned_to_usr.usr_key = oti.osi_assigned_to_usr_key 
and oti.osi_assign_type     = 'User' 
 AND oti.sta_bucket         IN ('Pending', 'Rejected')
 --and oti.osi_assign_type =:AssignType
 --and oti.sta_bucket =:TaskStatus
 and (NVL(:AssignType,null) is null or UPPER(oti.osi_assign_type) like UPPER(:AssignType))
 and (NVL(:TaskStatus,null) is null or UPPER(oti.sta_bucket) like UPPER(:TaskStatus))

Thanks !!!