USP_DATALIST_REGISTRANT_EXTENSION

CREATE procedure dbo.USP_DATALIST_REGISTRANT_EXTENSION
(
        @REGISTRANTID uniqueidentifier = null,
      @SEQUENCEID int = null,
      @ISPREVIOUSEVENT bit = 0 -- if this flag is set to 1 thne EVENTID will be replaced by future event id

)
as
    set nocount on;

  DECLARE @FUTUREEVENTID uniqueidentifier

  IF @ISPREVIOUSEVENT = 1
  SELECT @FUTUREEVENTID = EE.EVENTID
  FROM dbo.REGISTRANT R (NOLOCK)
  INNER JOIN dbo.EVENTEXTENSION EE (NOLOCK)
  ON R.EVENTID = EE.PRIORYEAREVENTID
  WHERE R.SEQUENCEID = ISNULL(@SEQUENCEID,R.SEQUENCEID)
  AND R.ID = ISNULL(@REGISTRANTID,R.ID)

    select 
        RE.[ID],
        RE.[REGISTRANTID],
        RE.[FUNDRAISINGGOAL],
        RE.[ADVOCACYACTIONGOAL],
        RE.[MEMBERECRUITMENTGOAL],
        RE.[COMMUNICATIONGOAL],
        RE.[VOLUNTEERRECRUITMENTGOAL],
            RE.[TARGETFUNDRAISINGGOAL],
            RE.[DONORRETENTIONGOAL],
            RE.[OTHERUNITGOAL],
            REGISTRATIONWAIVER.[WAIVERTEXT],
        RE.[ALLOWADMINACCESSHQ],
        RE.[ISDASHBOARDHELPERVISIBLE],
        (CASE @ISPREVIOUSEVENT WHEN 1 THEN @FUTUREEVENTID ELSE R.EVENTID END) as EVENTID,
        R.CONSTITUENTID,
        R.SEQUENCEID
    from 
        dbo.REGISTRANTEXTENSION RE (NOLOCK)
        INNER JOIN dbo.REGISTRANT R (NOLOCK)
        ON RE.REGISTRANTID = R.ID
            LEFT OUTER JOIN dbo.REGISTRATIONWAIVER
            ON RE.WAIVERID = REGISTRATIONWAIVER.ID
          WHERE RE.REGISTRANTID = ISNULL(@REGISTRANTID,RE.REGISTRANTID)
        AND R.SEQUENCEID = ISNULL(@SEQUENCEID,R.SEQUENCEID)