USP_DATALIST_PARTICIPANT_EMAIL_TEMPLATE

CREATE procedure dbo.USP_DATALIST_PARTICIPANT_EMAIL_TEMPLATE
(
  @CLIENTUSERSID int,
    @EVENTID uniqueidentifier,
  @NAME nvarchar(255) = null,
  @COMMUNICATIONTYPE tinyint = 0 -- 1:Recruit participants, 2: Raise Money, 3: Fundraising Progress, 4: Gift Acknowledgement

)
as
    set nocount on;

  DECLARE @CONSTITUENTID uniqueidentifier
    SET @CONSTITUENTID = dbo.UFN_CONSTITUENT_GET_BY_CLIENTUSERID(@CLIENTUSERSID)

  DECLARE 
        @IsIndependentHouseholdMember bit 
        ,@IsIndependentHouseholdLeader bit  
        ,@IsIndependentTeamMember bit  
        ,@IsIndependentTeamLeader bit   
        ,@IsCompanyHouseholdMember bit   
        ,@IsCompanyHouseholdLeader bit   
        ,@IsCompanyTeamHouseholdLeader bit   
        ,@IsCompanyTeamMember bit   
        ,@IsCompanyTeamLeader bit   
        ,@IsCompanyLeader bit   
    ,@IsCompanyTeamHouseholdMember bit
    ,@IsTeamHouseholdMember bit
    ,@IsTeamHouseholdLeader bit
    ,@IsCompanyIndividual bit
    ,@IsIndependentIndividual bit

    EXEC dbo.USP_FAF_REGISTRANT_ROLE
        @CONSTITUENTID = @CONSTITUENTID
        ,@EVENTID =@EVENTID
        ,@IsIndependentHouseholdMember  = @IsIndependentHouseholdMember OUTPUT
        ,@IsIndependentHouseholdLeader  = @IsIndependentHouseholdLeader OUTPUT
        ,@IsIndependentTeamMember  = @IsIndependentTeamMember OUTPUT
        ,@IsIndependentTeamLeader   = @IsIndependentTeamLeader OUTPUT
        ,@IsCompanyHouseholdMember   = @IsCompanyHouseholdMember OUTPUT
        ,@IsCompanyHouseholdLeader   = @IsCompanyHouseholdLeader OUTPUT
        ,@IsCompanyTeamHouseholdLeader   = @IsCompanyTeamHouseholdLeader OUTPUT
        ,@IsCompanyTeamMember   = @IsCompanyTeamMember OUTPUT
        ,@IsCompanyTeamLeader   = @IsCompanyTeamLeader OUTPUT
        ,@IsCompanyLeader   = @IsCompanyLeader OUTPUT
    ,@IsCompanyTeamHouseholdMember = @IsCompanyTeamHouseholdMember OUTPUT
    ,@IsTeamHouseholdMember = @IsTeamHouseholdMember OUTPUT
    ,@IsTeamHouseholdLeader = @IsTeamHouseholdLeader OUTPUT
    ,@IsCompanyIndividual = @IsCompanyIndividual OUTPUT
    ,@IsIndependentIndividual = @IsIndependentIndividual OUTPUT


    IF (@IsCompanyLeader = 1)        -- Company Leader

    BEGIN 
      SELECT
           EET.ID,
           ET.NAME,
             ET.[DESCRIPTION],
           ET.ContentHTML,
             ET.[SUBJECT]    ,
           CONTACTTYPE = dbo.UFN_FAFPARTICIPANTEMAILCONTACTTYPES(ET.ID),
           1 AS [ADMINTEMPLATE],
           EET.EMAILTEMPLATEID,
           EET.DATEADDED,
           (CASE WHEN @COMMUNICATIONTYPE = 1 AND dbo.UFN_FAFPARTICIPANTEMAILCONTACTTYPES(ET.ID) like '%Team leaders - previous%' THEN 1
              WHEN @COMMUNICATIONTYPE = 1 AND dbo.UFN_FAFPARTICIPANTEMAILCONTACTTYPES(ET.ID) like '%Household leaders - previous%' THEN 1
              WHEN @COMMUNICATIONTYPE = 1 AND dbo.UFN_FAFPARTICIPANTEMAILCONTACTTYPES(ET.ID) like '%Individuals - previous%' THEN 1
              WHEN @COMMUNICATIONTYPE = 2 AND dbo.UFN_FAFPARTICIPANTEMAILCONTACTTYPES(ET.ID) like '%Donors - previous%' THEN 1
              WHEN @COMMUNICATIONTYPE IN (3,4) AND dbo.UFN_FAFPARTICIPANTEMAILCONTACTTYPES(ET.ID) like '%Team leaders - current%' THEN 1
              WHEN @COMMUNICATIONTYPE IN (3,4) AND dbo.UFN_FAFPARTICIPANTEMAILCONTACTTYPES(ET.ID) like '%Household leaders - current%' THEN 1
              WHEN @COMMUNICATIONTYPE IN (3,4) AND dbo.UFN_FAFPARTICIPANTEMAILCONTACTTYPES(ET.ID) like '%Individuals - current%' THEN 1
              WHEN @COMMUNICATIONTYPE = 4 AND dbo.UFN_FAFPARTICIPANTEMAILCONTACTTYPES(ET.ID) like '%Donors - current%' THEN 1
              WHEN @COMMUNICATIONTYPE IN (1,2) AND dbo.UFN_FAFPARTICIPANTEMAILCONTACTTYPES(ET.ID) like '%Contacts%' THEN 1
              ELSE 0 END) ISRECOMMENDED,
          EET.AVAILABLEASLETTER
      from dbo.EVENTEMAILTEMPLATE EET
          join EMAILTEMPLATE ET ON EET.EMAILTEMPLATEID = ET.ID
            where EET.EVENTID = @EVENTID and EET.CONFIRMATIONTYPECODE = 100
          AND EET.ISAVAILABLETOCOMPANYLEADER = 1
          AND EET.ISACTIVE = 1
          AND ET.NAME = ISNULL(@NAME,ET.NAME)

      UNION ALL

       select 
            PT.[ID],
            PT.[NAME],
            PT.[DESCRIPTION],
            PT.[CONTENTHTML],
            PT.[SUBJECT],
            PT.[CONTACTTYPE],
            0 AS [ADMINTEMPLATE],
            -1 AS EMAILTEMPLATEID,
            PT.DATEADDED,
            (CASE WHEN @COMMUNICATIONTYPE = 1 AND CONTACTTYPE like '%Team leaders - previous%' THEN 1
              WHEN @COMMUNICATIONTYPE = 1 AND CONTACTTYPE like '%Household leaders - previous%' THEN 1
              WHEN @COMMUNICATIONTYPE = 1 AND CONTACTTYPE like '%Individuals - previous%' THEN 1
              WHEN @COMMUNICATIONTYPE = 2 AND CONTACTTYPE like '%Donors - previous%' THEN 1
              WHEN @COMMUNICATIONTYPE IN (3,4) AND CONTACTTYPE like '%Team leaders - current%' THEN 1
              WHEN @COMMUNICATIONTYPE IN (3,4) AND CONTACTTYPE like '%Household leaders - current%' THEN 1
              WHEN @COMMUNICATIONTYPE IN (3,4) AND CONTACTTYPE like '%Individuals - current%' THEN 1
              WHEN @COMMUNICATIONTYPE = 4 AND CONTACTTYPE like '%Donors - current%' THEN 1
              WHEN @COMMUNICATIONTYPE IN (1,2) AND CONTACTTYPE like '%Prospects%' THEN 1
              ELSE 0 END) ISRECOMMENDED,
             PT.[AVAILABLEASLETTER]
         from 
            dbo.PARTICIPANTEMAILTEMPLATE PT
            WHERE PT.CLIENTUSERSID in (select CLIENTUSERID from dbo.UFN_CLIENTUSERS_GETIDLISTBYCONSTITUENTID(@CONSTITUENTID))
              AND PT.EVENTID = @EVENTID
            AND PT.DELETED = 0
            AND PT.NAME = ISNULL(@NAME,PT.NAME)

    END

    ELSE IF (@IsIndependentTeamLeader = 1 OR @IsCompanyTeamLeader = 1)    -- Team Leader

    BEGIN 
      SELECT
           EET.ID,
           ET.NAME,
             ET.[DESCRIPTION],
           ET.ContentHTML,
             ET.[SUBJECT]    ,
           CONTACTTYPE = dbo.UFN_FAFPARTICIPANTEMAILCONTACTTYPES(ET.ID),
           1 AS [ADMINTEMPLATE],
           EET.EMAILTEMPLATEID,
           EET.DATEADDED,
           (CASE WHEN @COMMUNICATIONTYPE = 1 AND dbo.UFN_FAFPARTICIPANTEMAILCONTACTTYPES(ET.ID) like '%Team members - previous%' THEN 1
              WHEN @COMMUNICATIONTYPE = 2 AND dbo.UFN_FAFPARTICIPANTEMAILCONTACTTYPES(ET.ID) like '%Donors - previous%' THEN 1
              WHEN @COMMUNICATIONTYPE IN (3,4) AND dbo.UFN_FAFPARTICIPANTEMAILCONTACTTYPES(ET.ID) like '%Team members - current%' THEN 1
              WHEN @COMMUNICATIONTYPE = 4 AND dbo.UFN_FAFPARTICIPANTEMAILCONTACTTYPES(ET.ID) like '%Donors - current%' THEN 1
              WHEN @COMMUNICATIONTYPE IN (1,2) AND dbo.UFN_FAFPARTICIPANTEMAILCONTACTTYPES(ET.ID) like '%Contacts%' THEN 1
              ELSE 0 END) ISRECOMMENDED,
              EET.AVAILABLEASLETTER

      from dbo.EVENTEMAILTEMPLATE EET
          join EMAILTEMPLATE ET ON EET.EMAILTEMPLATEID = ET.ID
            where EET.EVENTID = @EVENTID and EET.CONFIRMATIONTYPECODE = 100
          AND EET.ISAVAILABLETOTEAMLEADER = 1
          AND EET.ISACTIVE = 1
          AND ET.NAME = ISNULL(@NAME,ET.NAME)

      UNION ALL

        select 
            PT.[ID],
            PT.[NAME],
            PT.[DESCRIPTION],
            PT.[CONTENTHTML],
            PT.[SUBJECT],
            PT.[CONTACTTYPE],
            0 AS [ADMINTEMPLATE],
            -1 AS EMAILTEMPLATEID,
            PT.DATEADDED,
            (CASE WHEN @COMMUNICATIONTYPE = 1 AND CONTACTTYPE like '%Team members - previous%' THEN 1
               WHEN @COMMUNICATIONTYPE = 2 AND CONTACTTYPE like '%Donors - previous%' THEN 1
               WHEN @COMMUNICATIONTYPE IN (3,4) AND CONTACTTYPE like '%Team members - current%' THEN 1
               WHEN @COMMUNICATIONTYPE = 4 AND CONTACTTYPE like '%Donors - current%' THEN 1
               WHEN @COMMUNICATIONTYPE IN (1,2) AND CONTACTTYPE like '%Prospects%' THEN 1
               ELSE 0 END) ISRECOMMENDED,
             PT.[AVAILABLEASLETTER]
        from 
            dbo.PARTICIPANTEMAILTEMPLATE PT
            WHERE PT.CLIENTUSERSID in (select CLIENTUSERID from dbo.UFN_CLIENTUSERS_GETIDLISTBYCONSTITUENTID(@CONSTITUENTID))  
              AND PT.EVENTID = @EVENTID
            AND PT.DELETED = 0
            AND PT.NAME = ISNULL(@NAME,PT.NAME)

    END
      -- Household leader

    ELSE IF (@IsIndependentHouseholdLeader = 1 OR @IsCompanyHouseholdLeader = 1
        OR @IsCompanyTeamHouseholdLeader = 1 OR @IsTeamHouseholdLeader = 1)
    BEGIN 
      SELECT
           EET.ID,
           ET.NAME,
             ET.[DESCRIPTION],
           ET.ContentHTML,
             ET.[SUBJECT]    ,
           CONTACTTYPE = dbo.UFN_FAFPARTICIPANTEMAILCONTACTTYPES(ET.ID),
           1 AS [ADMINTEMPLATE],
           EET.EMAILTEMPLATEID,
           EET.DATEADDED,
           (CASE WHEN @COMMUNICATIONTYPE = 1 AND dbo.UFN_FAFPARTICIPANTEMAILCONTACTTYPES(ET.ID) like '%Household members - previous%' THEN 1
              WHEN @COMMUNICATIONTYPE = 2 AND dbo.UFN_FAFPARTICIPANTEMAILCONTACTTYPES(ET.ID) like '%Donors - previous%' THEN 1
              WHEN @COMMUNICATIONTYPE IN (3,4) AND dbo.UFN_FAFPARTICIPANTEMAILCONTACTTYPES(ET.ID) like '%Household members - current%' THEN 1
              WHEN @COMMUNICATIONTYPE = 4 AND dbo.UFN_FAFPARTICIPANTEMAILCONTACTTYPES(ET.ID) like '%Donors - current%' THEN 1
              WHEN @COMMUNICATIONTYPE IN (1,2) AND dbo.UFN_FAFPARTICIPANTEMAILCONTACTTYPES(ET.ID) like '%Contacts%' THEN 1
              ELSE 0 END) ISRECOMMENDED,
            EET.AVAILABLEASLETTER
      from dbo.EVENTEMAILTEMPLATE EET
          join EMAILTEMPLATE ET ON EET.EMAILTEMPLATEID = ET.ID
            where EET.EVENTID = @EVENTID and EET.CONFIRMATIONTYPECODE = 100
          AND EET.ISAVAILABLETOHOUSEHOLD = 1
          AND EET.ISACTIVE = 1
          AND ET.NAME = ISNULL(@NAME,ET.NAME)

      UNION ALL

        select 
            PT.[ID],
            PT.[NAME],
            PT.[DESCRIPTION],
            PT.[CONTENTHTML],
            PT.[SUBJECT],
            PT.[CONTACTTYPE],
            0 AS [ADMINTEMPLATE],
            -1 AS EMAILTEMPLATEID,
            PT.DATEADDED,
            (CASE WHEN @COMMUNICATIONTYPE = 1 AND CONTACTTYPE like '%Household members - previous%' THEN 1
               WHEN @COMMUNICATIONTYPE = 2 AND CONTACTTYPE like '%Donors - previous%' THEN 1
               WHEN @COMMUNICATIONTYPE = 3 AND CONTACTTYPE like '%Household members - current%' THEN 1
               WHEN @COMMUNICATIONTYPE = 4 AND CONTACTTYPE like '%Donors - current%' THEN 1
               WHEN @COMMUNICATIONTYPE IN (1,2) AND CONTACTTYPE like '%Prospects%' THEN 1
               ELSE 0 END) ISRECOMMENDED,
            PT.[AVAILABLEASLETTER]
        from 
            dbo.PARTICIPANTEMAILTEMPLATE PT
            WHERE PT.CLIENTUSERSID in (select CLIENTUSERID from dbo.UFN_CLIENTUSERS_GETIDLISTBYCONSTITUENTID(@CONSTITUENTID))  
              AND PT.EVENTID = @EVENTID
            AND PT.DELETED = 0
            AND PT.NAME = ISNULL(@NAME,PT.NAME)

      END
        -- Household member

    ELSE IF (@IsIndependentHouseholdMember = 1 OR @IsCompanyHouseholdMember = 1
        OR @IsCompanyTeamHouseholdMember = 1 OR @IsTeamHouseholdMember = 1)
    BEGIN 
      SELECT
           EET.ID,
           ET.NAME,
             ET.[DESCRIPTION],
           ET.ContentHTML,
             ET.[SUBJECT]    ,
           CONTACTTYPE = dbo.UFN_FAFPARTICIPANTEMAILCONTACTTYPES(ET.ID),
           1 AS [ADMINTEMPLATE],
           EET.EMAILTEMPLATEID,
           EET.DATEADDED,
           (CASE WHEN @COMMUNICATIONTYPE = 2 AND dbo.UFN_FAFPARTICIPANTEMAILCONTACTTYPES(ET.ID) like '%Donors - previous%' THEN 1
              WHEN @COMMUNICATIONTYPE = 4 AND dbo.UFN_FAFPARTICIPANTEMAILCONTACTTYPES(ET.ID) like '%Donors - current%' THEN 1
              WHEN @COMMUNICATIONTYPE IN (1,2) AND dbo.UFN_FAFPARTICIPANTEMAILCONTACTTYPES(ET.ID) like '%Contacts%' THEN 1
              ELSE 0 END) ISRECOMMENDED,
            EET.AVAILABLEASLETTER
      from dbo.EVENTEMAILTEMPLATE EET
          join EMAILTEMPLATE ET ON EET.EMAILTEMPLATEID = ET.ID
            where EET.EVENTID = @EVENTID and EET.CONFIRMATIONTYPECODE = 100
          AND EET.ISAVAILABLETOHOUSEHOLDMEMBER = 1
          AND EET.ISACTIVE = 1
          AND ET.NAME = ISNULL(@NAME,ET.NAME)

      UNION ALL

        select 
            PT.[ID],
            PT.[NAME],
            PT.[DESCRIPTION],
            PT.[CONTENTHTML],
            PT.[SUBJECT],
            PT.[CONTACTTYPE],
            0 AS [ADMINTEMPLATE],
            -1 AS EMAILTEMPLATEID,
            PT.DATEADDED,
            (CASE WHEN @COMMUNICATIONTYPE = 2 AND CONTACTTYPE like '%Donors - previous%' THEN 1
               WHEN @COMMUNICATIONTYPE = 4 AND CONTACTTYPE like '%Donors - current%' THEN 1
               WHEN @COMMUNICATIONTYPE IN (1,2) AND CONTACTTYPE like '%Prospects%' THEN 1
               ELSE 0 END) ISRECOMMENDED,
            PT.[AVAILABLEASLETTER]
        from 
            dbo.PARTICIPANTEMAILTEMPLATE PT
            WHERE PT.CLIENTUSERSID in (select CLIENTUSERID from dbo.UFN_CLIENTUSERS_GETIDLISTBYCONSTITUENTID(@CONSTITUENTID))  
              AND PT.EVENTID = @EVENTID
            AND PT.DELETED = 0
            AND PT.NAME = ISNULL(@NAME,PT.NAME)

      END

    ELSE IF (@IsIndependentTeamMember = 1 OR @IsCompanyTeamMember = 1)  -- Team Member

    BEGIN 
      SELECT
           EET.ID,
           ET.NAME,
             ET.[DESCRIPTION],
           ET.ContentHTML,
             ET.[SUBJECT]    ,
           CONTACTTYPE = dbo.UFN_FAFPARTICIPANTEMAILCONTACTTYPES(ET.ID),
           1 AS [ADMINTEMPLATE],
           EET.EMAILTEMPLATEID,
           EET.DATEADDED,
           (CASE WHEN @COMMUNICATIONTYPE = 2 AND dbo.UFN_FAFPARTICIPANTEMAILCONTACTTYPES(ET.ID) like '%Donors - previous%' THEN 1
              WHEN @COMMUNICATIONTYPE = 4 AND dbo.UFN_FAFPARTICIPANTEMAILCONTACTTYPES(ET.ID) like '%Donors - current%' THEN 1
              WHEN @COMMUNICATIONTYPE IN (1,2) AND dbo.UFN_FAFPARTICIPANTEMAILCONTACTTYPES(ET.ID) like '%Contacts%' THEN 1
              ELSE 0 END) ISRECOMMENDED,
              EET.AVAILABLEASLETTER

      from dbo.EVENTEMAILTEMPLATE EET
          join EMAILTEMPLATE ET ON EET.EMAILTEMPLATEID = ET.ID
            where EET.EVENTID = @EVENTID and EET.CONFIRMATIONTYPECODE = 100
          AND EET.ISAVAILABLETOTEAMMEMBER = 1
          AND EET.ISACTIVE = 1
          AND ET.NAME = ISNULL(@NAME,ET.NAME)

      UNION ALL

        select 
            PT.[ID],
            PT.[NAME],
            PT.[DESCRIPTION],
            PT.[CONTENTHTML],
            PT.[SUBJECT],
            PT.[CONTACTTYPE],
            0 AS [ADMINTEMPLATE],
            -1 AS EMAILTEMPLATEID,
            PT.DATEADDED,
            (CASE WHEN @COMMUNICATIONTYPE = 2 AND CONTACTTYPE like '%Donors - previous%' THEN 1
               WHEN @COMMUNICATIONTYPE = 4 AND CONTACTTYPE like '%Donors - current%' THEN 1
               WHEN @COMMUNICATIONTYPE IN (1,2) AND CONTACTTYPE like '%Prospects%' THEN 1
               ELSE 0 END) ISRECOMMENDED,
               PT.[AVAILABLEASLETTER]

        from 
            dbo.PARTICIPANTEMAILTEMPLATE PT
            WHERE PT.CLIENTUSERSID in (select CLIENTUSERID from dbo.UFN_CLIENTUSERS_GETIDLISTBYCONSTITUENTID(@CONSTITUENTID))  
              AND PT.EVENTID = @EVENTID
            AND PT.DELETED = 0
            AND PT.NAME = ISNULL(@NAME,PT.NAME)

    END

    ELSE IF (@IsCompanyIndividual = 1 OR @IsIndependentIndividual = 1)          -- Individual

    BEGIN   
      SELECT
           EET.ID,
           ET.NAME,
             ET.[DESCRIPTION],
           ET.ContentHTML,
             ET.[SUBJECT]    ,
           CONTACTTYPE = dbo.UFN_FAFPARTICIPANTEMAILCONTACTTYPES(ET.ID),
           1 AS [ADMINTEMPLATE],
           EET.EMAILTEMPLATEID,
           EET.DATEADDED,
           (CASE WHEN @COMMUNICATIONTYPE = 2 AND dbo.UFN_FAFPARTICIPANTEMAILCONTACTTYPES(ET.ID) like '%Donors - previous%' THEN 1
              WHEN @COMMUNICATIONTYPE = 4 AND dbo.UFN_FAFPARTICIPANTEMAILCONTACTTYPES(ET.ID) like '%Donors - current%' THEN 1
              WHEN @COMMUNICATIONTYPE IN (1,2) AND dbo.UFN_FAFPARTICIPANTEMAILCONTACTTYPES(ET.ID) like '%Contacts%' THEN 1
              ELSE 0 END) ISRECOMMENDED,
              EET.AVAILABLEASLETTER

      from dbo.EVENTEMAILTEMPLATE EET
          join EMAILTEMPLATE ET ON EET.EMAILTEMPLATEID = ET.ID
            where EET.EVENTID = @EVENTID and EET.CONFIRMATIONTYPECODE = 100
          AND EET.ISAVAILABLETOINDIVIDUAL = 1
          AND EET.ISACTIVE = 1
          AND ET.NAME = ISNULL(@NAME,ET.NAME)

      UNION ALL

        select 
            PT.[ID],
            PT.[NAME],
            PT.[DESCRIPTION],
            PT.[CONTENTHTML],
            PT.[SUBJECT],
            PT.[CONTACTTYPE],
            0 AS [ADMINTEMPLATE],
            -1 AS EMAILTEMPLATEID,
            PT.DATEADDED,
            (CASE WHEN @COMMUNICATIONTYPE = 2 AND CONTACTTYPE like '%Donors - previous%' THEN 1
               WHEN @COMMUNICATIONTYPE = 4 AND CONTACTTYPE like '%Donors - current%' THEN 1
               WHEN @COMMUNICATIONTYPE IN (1,2) AND CONTACTTYPE like '%Prospects%' THEN 1
               ELSE 0 END) ISRECOMMENDED,
              PT.[AVAILABLEASLETTER]
        from 
            dbo.PARTICIPANTEMAILTEMPLATE PT
            WHERE PT.CLIENTUSERSID in (select CLIENTUSERID from dbo.UFN_CLIENTUSERS_GETIDLISTBYCONSTITUENTID(@CONSTITUENTID))  
              AND PT.EVENTID = @EVENTID
            AND PT.DELETED = 0
            AND PT.NAME = ISNULL(@NAME,PT.NAME)

    END

    ELSE                          -- Participant Custom Templates

      select 
          PT.[ID],
          PT.[NAME],
          PT.[DESCRIPTION],
          PT.[CONTENTHTML],
          PT.[SUBJECT],
          PT.[CONTACTTYPE],
          0 AS [ADMINTEMPLATE],
          -1 AS EMAILTEMPLATEID,
          PT.DATEADDED,
          (CASE WHEN @COMMUNICATIONTYPE = 2 AND CONTACTTYPE like '%Donors - previous%' THEN 1
               WHEN @COMMUNICATIONTYPE = 4 AND CONTACTTYPE like '%Donors - current%' THEN 1
               WHEN @COMMUNICATIONTYPE IN (1,2) AND CONTACTTYPE like '%Prospects%' THEN 1
               ELSE 0 END) ISRECOMMENDED,
              PT.[AVAILABLEASLETTER]
      from 
          dbo.PARTICIPANTEMAILTEMPLATE PT
          WHERE PT.CLIENTUSERSID in (select CLIENTUSERID from dbo.UFN_CLIENTUSERS_GETIDLISTBYCONSTITUENTID(@CONSTITUENTID))  
            AND PT.EVENTID = @EVENTID
           AND PT.DELETED = 0
           AND PT.NAME = ISNULL(@NAME,PT.NAME)