fnEmailList

CREATE FUNCTION [dbo].[fnEmailList](@ClientsID int, @Type tinyint) 
    RETURNS TABLE AS RETURN

    SELECT     e.ID, 
    e.EmailTemplateID, 
    e.Name, 
    e.Description,
    a.NAME AppealName, 
    a.DESCRIPTION AppealDescription, 
    e.OwnerID, 
    e.Deleted, 
    e.Guid, 
    e.Type, 
    ej.Status, 
    e.ContentHTML, 
    e.ContentText, 
    e.FromAddress, 
    e.FromDisplayName, 
    e.Subject, 
    e.Priority, 
    e.ReplyAddress, 
    e.ReturnReceipt, 
    e.ReturnReceiptAddress, 
    e.ForwardDSN, 
    e.ForwardDSNAddress, 
    e.SendAfterDate, 
    e.CreateDate, 
    e.ProjectAppealListEmailID,
    t.Name TemplateName,
    t.DataSourceID, 
    t.Guid TemplateGuid,
    vw.DataSourceName,
    e.ClientSitesID,
    e.AppealID,
    a.SITEID AppealSiteID,
    EJ.CreateDate DateSent,
    T.Deleted IsTemplateDeleted
    FROM Email e
        INNER JOIN EmailTemplate as t ON e.EmailTemplateID = t.ID
        left outer join vwEmailTemplateDataSources as vw on t.DataSourceID = vw.DataSourceID
        left outer join BBNCAPPEALIDMAP m on e.AppealID = m.ID
        left outer join APPEAL a on m.APPEALID=a.ID
        left outer join EmailJob EJ on E.ID=EJ.EmailID and EJ.ConditionalParentJobID is NULL
    WHERE e.Deleted = 0
    AND    t.ClientsID = @ClientsID
    AND    (t.Deleted = 0 or EJ.Status > 1)
    AND t.type = @Type
    AND ISNULL(e.ParentEmailID,0) = 0