fnAcknowledgementMessageList

CREATE function [dbo].[fnAcknowledgementMessageList](@EmailTemplateID int) returns table as return

        select
            e.[ID] ID, 
            r.[ID] EmailJobRecipientID,
            e.EmailTemplateID, 
            e.OwnerID, 
            e.Guid,
            e.[Name],
            e.Subject, 
            e.CreateDate,
            t.Guid TemplateGuid,
            j.[ID] EmailJobID,
            j.Status,
            er.EmailAddress,
            r.SentDate,
            r.OpenedDate,
            r.RecentDSNDate,
            r.RecentDSNCategory,
            er.DisplayName,
            er.UserID,
            bosp.BackofficeRecordID BORECID,
            er.BackofficeSystemPeopleID,
            er.InvalidAccount,
            er.OptOut,
            er.InvalidAddress,
            e.ClientSitesID,
            isnull(er.Inactive,0) as Inactive

        FROM Email e 
        INNER JOIN EmailTemplate as t ON e.EmailTemplateID = t.ID

        LEFT OUTER JOIN EmailJob j on j.EmailID = e.ID AND j.[Current] = 1

        LEFT OUTER JOIN Email_Recipient er on er.EmailID = e.ID

        LEFT OUTER JOIN EmailJob_Recipient r
 on r.EmailRecipientID = er.id and r.EmailJobId = j.ID and r.[Current] = 1

        LEFT OUTER JOIN [dbo].[BackOfficeSystemPeople] bosp on bosp.id = er.BackofficeSystemPeopleID and bosp.BackOfficeSystemid = 0

        WHERE e.Deleted = 0
        AND    t.Deleted = 0
        AND t.type in (1,18)
        AND t.id = @EmailTemplateID
        AND er.EmailAddress is not null