UFN_QUERY_FAFREGISTRATION_LISTBUILDER

CREATE function dbo.UFN_QUERY_FAFREGISTRATION_LISTBUILDER
(
    @ID         uniqueidentifier
)
returns table 
as

return

select  
      R.ID as [ID] 
      , R.LOOKUPID 
      , R.NAME 
      , R.ROLE
      , case when M.[StatusCode] != 2 then M.NAME else '' end as [GROUP] 
      , R.TARGETFUNDRAISINGGOAL
      , VF.AMOUNTRAISED AS AMOUNT 
      , case when dbo.UFN_REGISTRANT_ISCANCELLED (R.ID)=0 then 'Active' else 'Cancelled' end as [STATUS]
      , '' as [URL] 
      , 0 as PROGRESSMEMBERECRUITMENTGOAL
      , 0 as PROGRESSDONORRETENTIONGOAL
      , 0 as PROGRESSCOMMUNICATIONGOAL
      , R.CONSTITUENTID
    from 
      (
            select 
              REGISTRANT.ID as [ID] 
              , REGISTRANT.CONSTITUENTID
              , REGISTRANT.EVENTID
              , REGISTRANT.LOOKUPID 
              , REGISTRANTEXTENSION.TARGETFUNDRAISINGGOAL
              , REGISTRANT.DATEADDED
              , CONSTITUENT.NAME
            , AG.Role as Role 
            from dbo.REGISTRANT With (NOLOCK)
                  inner join    dbo.CONSTITUENT With (NOLOCK) on REGISTRANT.CONSTITUENTID = CONSTITUENT.ID
                  left join dbo.REGISTRANTEXTENSION With (NOLOCK) on REGISTRANT.ID = REGISTRANTEXTENSION.REGISTRANTID
                left join dbo.UFN_REGISTRANT_GETFAFROLE (@ID, NULL) AG on REGISTRANT.ID = AG.REGISTRANTID
            where REGISTRANT.EVENTID = @ID 
     ) R

    left join dbo.UFN_REVENUE_GETPARTICIPANTRAISEDTOTAL_2(@ID) VF ON VF.CONSTITUENTID = R.CONSTITUENTID 
    left join 
    (
        select TF.CONSTITUENTID, TX.EVENTID, TFT.NAME, TX.StatusCode
        from dbo.TEAMFUNDRAISINGTEAM TFT With (NOLOCK)
        join dbo.TEAMEXTENSION TX With (NOLOCK) on TFT.ID = TX.TEAMFUNDRAISINGTEAMID
        left join dbo.TEAMFUNDRAISINGTEAMMEMBER TFTM With (NOLOCK) on TFT.ID = TFTM.TEAMFUNDRAISINGTEAMID
        left join dbo.TEAMFUNDRAISER TF With (NOLOCK) on TF.ID = TFTM.TEAMFUNDRAISERID
        where TX.EVENTID = @ID
    ) M
    on M.CONSTITUENTID = R.CONSTITUENTID