Sunday, November 30, 2014

OIM Reports : Closed Provisioning Tasks Counts


Query for Closed Provisioning Tasks Counts:

select assigned_to_usr.usr_login as "Completed By",
    assigned_to_usr.usr_first_name || ' ' || assigned_to_usr.usr_last_name as "Full Name",
    count(assigned_to_usr.usr_login)
  FROM osi,mil,orc,sch,sta,usr assigned_to_usr, usr target_usr,pkg,oiu,obi,obj
  WHERE oiu.usr_key           = target_usr.usr_key
  AND osi.mil_key             = mil.mil_key
  AND osi.orc_key             = orc.orc_key
  AND sch.sch_key             = osi.sch_key
  and sta.sta_status          = sch.sch_status
  AND assigned_to_usr.usr_key = sch.sch_updateBY
  AND pkg.pkg_key             = osi.pkg_key
  AND osi.orc_key             = orc.orc_key
  AND orc.orc_key             = oiu.orc_key
  AND obi.obi_key             = oiu.obi_key
  and obj.obj_key             = obi.obj_key
  and sta.sta_bucket         ='Completed'
 and pkg.pkg_type = 'Provisioning'
  and  assigned_to_usr.usr_login not in ('XELSYSADM','OIMINTERNAL')
  and sch.sch_actual_start between :task_completed_date_from and :task_completed_date_to
  group by assigned_to_usr.usr_login, assigned_to_usr.usr_first_name || ' ' || assigned_to_usr.usr_last_name

Thanks !!!

No comments:

Post a Comment

Other Posts