USP_DATALIST_PARTCIPANTNOTIFICATIONTEMPLATES

CREATE procedure dbo.USP_DATALIST_PARTCIPANTNOTIFICATIONTEMPLATES(
  @EVENTID uniqueidentifier,
  @CONFIRMATIONTYPECODE tinyint = null
 )
as
    set nocount on;

With EventEmailTemplatelist
AS
(
select
    EET.ID
    ,EET.CONFIRMATIONTYPE
      ,ET.NAME
      ,ET.[DESCRIPTION]
      ,ET.[SUBJECT]    
    ,ISACTIVE
    ,ISAVAILABLETOINDIVIDUAL
    ,ISAVAILABLETOHOUSEHOLDMEMBER
    ,ISAVAILABLETOHOUSEHOLD
    ,ISAVAILABLETOTEAMMEMBER
    ,ISAVAILABLETOTEAMLEADER
    ,ISAVAILABLETOCOMPANYLEADER
    --,ISAVAILABLETOFUNDRAISINGCOACH

    --,ISAVAILABLETOSPONSOR

    ,ET.ContentHTML
    ,CONFIRMATIONTYPECODE

    --, EET.RECIPIENTDONOR

    --, EET.RECIPIENTPROSPECT

    --, EET.RECIPIENTINDIVIDUAL

    --, EET.RECIPIENTHOUSEHOLDMEMBER

    --, EET.RECIPIENTHOUSEHOLDLEADER

    --, EET.RECIPIENTTEAMMEMBER

    --, EET.RECIPIENTTEAMLEADER

    --, EET.RECIPIENTCOMPANYLEADER

    --, EET.RECIPIENTSTATUSPREVIOUS

    --, EET.RECIPIENTSTATUSCURRENT


    from dbo.EVENTEMAILTEMPLATE EET
  join EMAILTEMPLATE ET ON EET.EMAILTEMPLATEID = ET.ID
    where EET.EVENTID = @EVENTID and EET.CONFIRMATIONTYPECODE between 20 and 35
  AND  EET.CONFIRMATIONTYPECODE <> 33 --need to exclude 33; it is a marketing email that should not be added to the list

  AND (EET.CONFIRMATIONTYPECODE = @CONFIRMATIONTYPECODE or nullif(@CONFIRMATIONTYPECODE,0) is null)

)

SELECT * FROM [EventEmailTemplatelist]
--union all

--select * from UFN_DEFAULTNOTIFICATIONTEMPLATELIST(@EVENTID)