USP_DATALIST_EVENTMERGEDATA

CREATE procedure dbo.USP_DATALIST_EVENTMERGEDATA
  (
        @EVENTID uniqueidentifier
  )
  as
      set nocount on;

  declare @ManagerNotificationPageID int
  select top 1 @ManagerNotificationPageID = VPC.PageID
    from vwPageContent VPC
    inner join dbo.SitePages SP on SP.ID = VPC.PageID
    inner join dbo.EVENTEXTENSION EE ON EE.CLIENTSITESID = SP.ClientSitesID and EE.EVENTID = @EVENTID
    LEFT OUTER JOIN
        (select PAGEMODELITEM.OBJECTGUID
        from dbo.PAGEMODELITEM 
        inner join dbo.PAGEMODEL on PAGEMODEL.ID = PAGEMODELITEM.PAGEMODELID
        where PAGEMODEL.LOCKARTIFACTS = 1
        ) PMI on PMI.OBJECTGUID = VPC.PageGuid    
    inner join dbo.APEXFORMCONTENT AFC ON AFC.ID = VPC.Guid
    inner join dbo.APEXFORM AF ON AF.ID = AFC.FORMID AND AF.FORMTYPEID = '89D3C4CC-4D01-4E8B-B82E-04AD966EEE09'     
    WHERE VPC.IsTemplatePage = 0
        and VPC.ContentTypesID = 121
        and (PMI.OBJECTGUID is null)

select 
  SystemDate = getdate()
    ,EventName= EV.NAME
    ,EventLookupID=ET.LOOKUPEVENTID 
    ,EventLocation=EL.NAME 
    ,EventStartDate=EV.STARTDATE
    ,EventEndDate=EV.ENDDATE
    ,EventStartTime=EV.STARTTIME
    ,EventEndTime=EV.ENDTIME
    ,EventContactName= C.NAME
    ,EventContactEmail=EMAIL.EMAILADDRESS
    ,EventContactPhone=PHONE.NUMBER
    ,EventSupportPhone=ET.SUPPORTPHONE
    ,EventSupportEmail=ET.EVENTSUPPORTEMAIL
    ,EventSupportURL=ET.SUPPORTURL
    ,EventLoginLink=CMSS.Value + 'page.aspx?pid=' + CAST(ISNULL(LP.Value, '') AS NVARCHAR(10))
  ,EventComment=''
  ,LastLoginDate=null
  ,WAIVER = RW.WAIVERTEXT
    ,FacebookUserName= null
  ,FacebookUrl= null
  ,MySpaceUserName= null
  ,MySpaceUrl= null
  ,YoutubeUserName= null
  ,YoutubeUrl= null
  ,TwitterUserName= null
  ,TwitterUrl= null
  ,ManageNotificationUrl = cmss2.VALUE + 'page.aspx?pid=' + CAST(@ManagerNotificationPageID AS NVARCHAR(50)) + '&frmfaf=1' -- to identify that the link is in a faf email

from [EVENT] EV
inner join dbo.EVENTEXTENSION ET on EV.ID = ET.EVENTID
left outer join dbo.EVENTLOCATION EL on EV.EVENTLOCATIONID = EL.ID
left outer join dbo.CONSTITUENT C on EV.EVENTLOCATIONCONTACTID = C.ID
left outer join dbo.EMAILADDRESS EMAIL on C.ID = EMAIL.CONSTITUENTID and EMAIL.ISPRIMARY = 1
left outer join dbo.PHONE on C.ID = PHONE.CONSTITUENTID and PHONE.ISPRIMARY = 1
left outer join dbo.CMSSITESETTING CMSS on ET.CLIENTSITESID = CMSS.CLIENTSITESID and CMSS.ENUMID = 11
left outer join dbo.CMSSITESETTING LP on ET.CLIENTSITESID = LP.CLIENTSITESID and LP.ENUMID = 38 /* use HQ link here as default, redirect to login if user is not log in*/
left outer join dbo.REGISTRATIONWAIVER RW on RW.EVENTID = EV.ID and  RW.ISCURRENT=1
left join dbo.CMSSITESETTING CMSS2 on ET.CLIENTSITESID = CMSS2.CLIENTSITESID and CMSS2.ENUMID = 11 /*use to get the manage notification url*/
WHERE EV.id = @EVENTID