USP_DATALIST_IMPORTPROCESS_2

CREATE procedure dbo.USP_DATALIST_IMPORTPROCESS_2(
    @CURRENTAPPUSERID uniqueidentifier,
    @SITEFILTERMODE tinyint = 0,
    @SITESSELECTED xml = null,
    @SECURITYFEATUREID uniqueidentifier = null,
    @SECURITYFEATURETYPE tinyint = null,
    @BATCHTEMPLATEID uniqueidentifier = null
)
with execute as caller
as
set nocount on;

create table #BPS
(
    ID uniqueidentifier, BUSINESSPROCESSPARAMETERSETID uniqueidentifier, STARTEDON datetime, [STATUS] nvarchar(25) collate database_default
);

insert into #BPS(ID, BUSINESSPROCESSPARAMETERSETID, STARTEDON, [STATUS])
select BPS.ID, BPS.BUSINESSPROCESSPARAMETERSETID, BPS.STARTEDON, BPS.STATUS
from dbo.BUSINESSPROCESSSTATUS as BPS
inner join dbo.IMPORTPROCESS as IP on BPS.BUSINESSPROCESSPARAMETERSETID = IP.ID
where BPS.STARTEDON in
(
    select MAX(STARTEDON)
    from dbo.BUSINESSPROCESSSTATUS 
    inner join dbo.IMPORTPROCESS on BUSINESSPROCESSSTATUS.BUSINESSPROCESSPARAMETERSETID = IMPORTPROCESS.ID
    group by IMPORTPROCESS.ID
);

select
    IMPORTPROCESS.ID,
    IMPORTPROCESS.NAME,
    BATCHTEMPLATE.NAME as BATCHTEMPLATE,
    IMPORTPROCESS.DATEADDED as DATECREATED,
    BPS1.STARTEDON as LASTRUN,
    BPS1.STATUS as LASTRUNSTATUS,
    coalesce(SITE.NAME, 'All Sites') SITES,
    IMPORTPROCESS.DESCRIPTION,
    BPI.OWNERID as OWNERID,
    IMPORTPROCESS.USEWILDCARDS
from dbo.IMPORTPROCESS
inner join dbo.BATCHTEMPLATE on IMPORTPROCESS.BATCHTEMPLATEID = BATCHTEMPLATE.ID
left join dbo.CHANGEAGENT on IMPORTPROCESS.ADDEDBYID = CHANGEAGENT.ID
left join dbo.BUSINESSPROCESSINSTANCE BPI on IMPORTPROCESS.ID = BPI.BUSINESSPROCESSPARAMETERSETID
left join dbo.SITE on SITE.ID = BPI.SITEID
left join #BPS BPS1 on BPI.BUSINESSPROCESSPARAMETERSETID = BPS1.BUSINESSPROCESSPARAMETERSETID
where (dbo.UFN_SECURITY_APPUSER_GRANTED_BUSINESSPROCESSINSTANCE_IN_SYSTEMROLE(@CURRENTAPPUSERID, IMPORTPROCESS.ID) = 1)
and (@BATCHTEMPLATEID is null or IMPORTPROCESS.BATCHTEMPLATEID = @BATCHTEMPLATEID)
and    (
        (
            (--Check site security

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

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

    drop table #BPS;