spLoadRecord_EmailTemplate

CREATE PROCEDURE [dbo].[spLoadRecord_EmailTemplate] (@PKID int)
AS
-- email template fields

SELECT 
    E.[ID],
    E.[ClientsID],
    E.[Name],
    E.[Description],
    E.[OwnerID],
    E.[Deleted],
    E.[Guid],
    E.[ContentHTML],
    E.[ContentText]
,
    E.[FromAddress],
    E.[FromDisplayName],
    E.[Subject],
    E.[Priority],
    E.[ReplyAddress],
    E.[ReturnReceipt],
    E.[ReturnReceiptAddress],
    E.[ForwardDSN],
    E.[ForwardDSNAddress],
    E.[Type],
    E.[IsInternalUse],
    E.[DataSourceID],
    E.[ClientSitesID],
    E.[CopySourceID],
    E.[CopyBatchGUID],
    E.[AppealID],
    A.[DESCRIPTION] as AppealDescription,
    dbo.fnEmailTemplateInUse(E.ID) InUse,
    E.EXPORTDEFINITIONID,
    ED.NAME as EXPORTDEFINITIONNAME,
    E.IsSample,
    E.[EmailAddressTypeID],
  isnull(E.InactiveRecipientFromMonths,0) as InactiveRecipientFromMonths,
  E.IsExcludeInactiveRecipient,
      E.[AlternateSubject]
FROM [dbo].EmailTemplate E 
LEFT JOIN dbo.BBNCAPPEALIDMAP B on B.ID = E.AppealID
LEFT JOIN dbo.APPEAL A on A.ID = B.AppealID 
left join dbo.EXPORTDEFINITION ED on ED.ID = E.EXPORTDEFINITIONID
WHERE E.ID = @PKID

-- email template lists

SELECT
    tl.[ID],
    tl.EmailListID,
    l.[Name],
    l.[Description],
    tl.EmailTemplateID,
    l.[Subscription],
    l.DataSourceID,
    (SELECT [Name] FROM dbo.Chapters WHERE MemberEmailListID = l.[ID]) ChapterName
FROM [dbo].EmailList l INNER JOIN [dbo].EmailTemplate_EmailList tl ON l.[ID] = tl.EmailListID
WHERE l.Deleted = 0
AND tl.EmailTemplateID = @PKID

-- email template datasources

SELECT 
    ds.DataSourceID, 
    ds.[Name], 
    ds.TypeNamespace, 
    ds.TypeName, 
    ds.IsEmailListSource, 

   ds.BackOfficeSystemId, 
    ds.Available, 
    ds.SubscriberProviderForSystemID,
    ds.ImportListID
FROM 
[dbo].[DataSources] ds inner join dbo.EmailTemplate et
on ds.DatasourceID = et.DatasourceID
WHERE et.ID=@pkid