UFN_PROSPECTPLANSUMMARY

CREATE function dbo.UFN_PROSPECTPLANSUMMARY()
returns table
as
  return (
  select
    C.ID,
    (
        select
            max(ACTUALDATE)
        from
            dbo.PROSPECTPLAN PP
            inner join dbo.INTERACTION I on I.PROSPECTPLANID=PP.ID
        where
            PP.PROSPECTID=C.ID
    ) LASTSTEP,
    case when PP.ISACTIVE = 1 and datediff(day,(
        select
            max(ACTUALDATE)
        from
            dbo.PROSPECTPLAN PP
            inner join dbo.INTERACTION I on I.PROSPECTPLANID=PP.ID
        where
            PP.PROSPECTID=C.ID
    ),getdate())>=90 then 'RES:warning' else '' end NORECENT,
    (
        select min(I.EXPECTEDDATE)
        from PROSPECTPLAN PP
            inner join dbo.INTERACTION I on I.PROSPECTPLANID=PP.ID
        where
            PP.PROSPECTID=C.ID and
            I.ID = dbo.UFN_PROSPECTPLAN_GETNEXTSTEP(PP.ID) and
            I.COMPLETED=0
    ) NEXTSTEP,
    NF.NAME,
    C.KEYNAME,
    (
        select dbo.UDA_BUILDLIST(distinct SITE.NAME)
        from
            dbo.PROSPECTPLAN PP
      cross apply dbo.UFN_PROSPECTPLAN_GETSITES(PP.ID) PLANSITE
      inner join dbo.SITE on SITE.ID = PLANSITE.SITEID
        where
            PP.PROSPECTID=C.ID
    ) SITES
    from 
      dbo.CONSTITUENT C 
      left outer join dbo.PROSPECTPLAN PP on PP.PROSPECTID=C.ID
      outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(C.ID) NF
    where
      dbo.UFN_CONSTITUENT_ISPROSPECT(C.ID) = 1
    )