USP_DATALIST_EMAILTEMPLATE

CREATE procedure dbo.USP_DATALIST_EMAILTEMPLATE
(
    @CLIENTUSERID int,
    @TYPE int = null,
    @DATASOURCEID int = null,
    @CLIENTSITESID int = null,
    @EXCLUDEDBACKOFFICESYSTEMS xml = null
)
as
    set nocount on;

    declare @EXCLUDEDSYSTEMS table (ID int)

    insert into @EXCLUDEDSYSTEMS
    select E.S.value('BACKOFFICESYSTEMID[1]', 'int')
    from @EXCLUDEDBACKOFFICESYSTEMS.nodes('EXCLUDEDBACKOFFICESYSTEMS/ITEM') E(S)

    select ET.ID,
           ET.NAME
    from dbo.EMAILTEMPLATE ET
    inner join [dbo].UFN_CLIENTUSERS_OBJECTSWITHTASKRIGHT(@CLIENTUSERID,14,0,'034D2C4C-C6C7-4E55-8D4B-77C216EE3E25') SEC ON ET.Guid=SEC.ObjectGuid --Has All email template rights ID

    inner join [dbo].DATASOURCES DS on DS.DATASOURCEID = ET.DATASOURCEID
    left outer join [dbo].PAGEMODELITEM PMI on PMI.OBJECTGUID = et.Guid
    left outer join [dbo].PAGEMODEL PM on PMI.PAGEMODELID = PM.ID
    left outer join @EXCLUDEDSYSTEMS ES on ES.ID = DS.BACKOFFICESYSTEMID
    where
        CLIENTSID = 1
        and ES.ID is null
        and ET.DELETED = 0
        and (PM.LOCKARTIFACTS is null or PM.LOCKARTIFACTS = 0)
        and (@DATASOURCEID is null or ET.DATASOURCEID = @DATASOURCEID or ET.DATASOURCEID = 302)
        and (@TYPE is null OR ET.TYPE = @TYPE)
        and (@CLIENTSITESID is null or ET.CLIENTSITESID = @CLIENTSITESID)
        and ET.ISSAMPLE = 0