USP_DATALIST_QUEUEPROCESS

List of saved queue processes.

Parameters

Parameter	Parameter Type	Mode	Description
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
@SITEFILTERMODE	tinyint	IN	Sites
@SITESSELECTED	xml	IN
@SECURITYFEATUREID	uniqueidentifier	IN	Input parameter indicating the ID of the feature to use for site security checking.
@SECURITYFEATURETYPE	tinyint	IN	Input parameter indicating the type of the feature to use for site security checking.

Definition

Copy



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

    select QUEUEPROCESS.ID,
        QUEUEPROCESS.NAME,
        QUEUEPROCESS.DESCRIPTION,
        QUEUEPROCESS.DATEADDED,
        CHANGEAGENT.USERNAME,
        BPI.OWNERID as OWNERID,
        coalesce(SITE.NAME, 'All Sites') SITES,
        case
            when [QUEUEPROCESSWITHMISSINGSTEP].ID is null then null
            else 'RES:warning'
        end as IMAGEKEY
    from dbo.QUEUEPROCESS
    left join dbo.CHANGEAGENT on QUEUEPROCESS.ADDEDBYID = CHANGEAGENT.ID
    left join dbo.BUSINESSPROCESSINSTANCE BPI on QUEUEPROCESS.ID = BPI.BUSINESSPROCESSPARAMETERSETID
    left join dbo.SITE on SITE.ID = BPI.SITEID
    left join
    (    
        select distinct
            QUEUEPROCESS.ID
        from
            dbo.QUEUEPROCESS
            outer apply QUEUEPROCESS.STEPLISTXML.nodes('/ArrayOfQueueProcessStep/QueueProcessStep') QUEUEPROCESSSTEP(ELEMENT)
            left join dbo.V_BUSINESSPROCESSPARAMETERSETS on
                V_BUSINESSPROCESSPARAMETERSETS.BUSINESSPROCESSID = QUEUEPROCESSSTEP.ELEMENT.value('@BusinessProcessID', 'uniqueidentifier')
                and V_BUSINESSPROCESSPARAMETERSETS.PARAMETERSETID = QUEUEPROCESSSTEP.ELEMENT.value('@ParameterSetID', 'uniqueidentifier')
        where
            QUEUEPROCESSSTEP.ELEMENT.exist('@ParameterSetID') = 1
            and V_BUSINESSPROCESSPARAMETERSETS.PARAMETERSETID is null
    ) as [QUEUEPROCESSWITHMISSINGSTEP] on QUEUEPROCESS.ID = [QUEUEPROCESSWITHMISSINGSTEP].ID
    where (dbo.UFN_SECURITY_APPUSER_GRANTED_BUSINESSPROCESSINSTANCE_IN_SYSTEMROLE(@CURRENTAPPUSERID, QUEUEPROCESS.ID) = 1)
        and (
                (
                    (--Check site security

                        select count(*) 
                        from dbo.UFN_SITEID_MAPFROM_QUEUEPROCESSID(QUEUEPROCESS.ID) as QUEUEPROCESSSITE 
                        where (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[QUEUEPROCESSSITE].[SITEID] or (SITEID is null and [QUEUEPROCESSSITE].[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 QUEUEPROCESS.NAME;