USP_DATALIST_GENERATECONSTITUENTUPDATEBATCHPROCESS

CREATE procedure dbo.USP_DATALIST_GENERATECONSTITUENTUPDATEBATCHPROCESS
(
    @CURRENTAPPUSERID uniqueidentifier = null, 
    @SHOWINACTIVE bit = 0,
    @SITEFILTERMODE tinyint = 0,
    @SITESSELECTED xml = null,
    @SECURITYFEATUREID uniqueidentifier = null,
    @SECURITYFEATURETYPE tinyint = null
)
as
    set nocount on;

    select 
        GENERATEBATCHPROCESSCUB.ID,
        GENERATEBATCHPROCESSCUB.NAME,
        IDSETREGISTER.NAME as SELECTION,
        BATCHTEMPLATE.NAME as BATCHTEMPLATE,
        BPI.OWNERID as OWNERID,
        GENERATEBATCHPROCESSCUB.ISACTIVE,
        coalesce(SITE.NAME,'All Sites') SITES
    from 
    dbo.GENERATEBATCHPROCESSCUB
        inner join dbo.IDSETREGISTER on GENERATEBATCHPROCESSCUB.IDSETREGISTERID = IDSETREGISTER.ID
        inner join dbo.BATCHTEMPLATE on GENERATEBATCHPROCESSCUB.BATCHTEMPLATEID = BATCHTEMPLATE.ID
        left join dbo.BUSINESSPROCESSINSTANCE BPI on GENERATEBATCHPROCESSCUB.ID = BPI.BUSINESSPROCESSPARAMETERSETID
        left join dbo.SITE on SITE.ID = BPI.SITEID
    where (dbo.UFN_SECURITY_APPUSER_GRANTED_BUSINESSPROCESSINSTANCE_IN_SYSTEMROLE(@CURRENTAPPUSERID, GENERATEBATCHPROCESSCUB.ID) = 1)  
    and (@SHOWINACTIVE = 1 or GENERATEBATCHPROCESSCUB.ISACTIVE = 1)
    and (
            (
                (--Check site security

                 select count(1) 
                 from dbo.UFN_BUSINESSPROCESSPARAMETERSET_GETSITES(GENERATEBATCHPROCESSCUB.ID) as GENERATEBATCHPROCESSCUBSITE 
                 where (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[GENERATEBATCHPROCESSCUBSITE].[SITEID] or (SITEID is null and [GENERATEBATCHPROCESSCUBSITE].[SITEID] is null)))
                ) > 0
            )
            and (--Apply site filter

                @SITEFILTERMODE = 0
                or BPI.SITEID in (
                select SITEID
                from dbo.UFN_SITE_BUILDDATALISTSITEFILTER (@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED) 
                )
            )
        )
    order by GENERATEBATCHPROCESSCUB.NAME;