with subscription_count as
 (Select Ga.Series_Name,
         Count(distinct Subs.Target_Guid) As Subscribed_Targets,
         Ga.Platform_Name
    From sysman.EM_GAI_SERIES Ga
    Left Join sysman.Em_Gi_Agent_Subscribe Subs
      On Ga.Series_Name = Subs.Gold_Image_Series
   Group By Ga.Series_Name, Ga.Platform_Name),
Act_Ver_Count As
 (Select Ga.Series_Name, Count(distinct Ga.Revision) as rev_count
    From sysman.Em_Gai_Entities Ga, sysman.Mgmt$target_Properties Tp
   Where (Tp.property_value = Ga.Image_Name and
         Tp.Target_Type = 'oracle_emd' And
         Tp.Property_Name = 'GoldImageName' And
         (Ga.Maturity = 'Active' OR GA.MATURITY = 'Current'))
   Group By Ga.Series_Name),
Ser_Dep_Count As
 (Select Ga.series_name, Count(T.Target_Name) as dep_count
    From sysman.Em_Gai_Entities Ga
    Left Join (Select Tp.Target_Name, Tp.Property_Value As Image_Name
                From sysman.Mgmt$target_Properties Tp
               Where Tp.Target_Type = 'oracle_emd'
                 And Tp.Property_Name = 'GoldImageName') T
      on t.image_name = Ga.image_name
   Group By ga.series_name)
Select sc.Series_Name as Name,
       sc.Subscribed_Targets,
       nvl(Sdc.dep_count, 0) -
       sysman.EM_GOLD_AGENT_IMAGE.GET_ROGUE_COUNT(sc.Series_Name) as Deployments,
       nvl(avc.Rev_Count, 0) as Act_ver,
       sc.Platform_Name,
       sysman.EM_GOLD_AGENT_IMAGE.GET_SERIES_COMPLIANCE(sc.Series_Name) as Compliance
  From Subscription_Count Sc
  Left Join Act_Ver_Count Avc
    On Sc.Series_Name = Avc.Series_Name
  Left Join Ser_Dep_Count Sdc
    On Sc.Series_Name = Sdc.Series_Name
 order by Sc.Series_Name;