USP_DATALIST_COMMITMULTIPLEBATCHESPROCESS

This datalist returns a list of acknowledgment and receipt 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_COMMITMULTIPLEBATCHESPROCESS
(
    @CURRENTAPPUSERID uniqueidentifier = null,
    @SITEFILTERMODE tinyint = 0,
    @SITESSELECTED xml = null,
    @SECURITYFEATUREID uniqueidentifier = null,
    @SECURITYFEATURETYPE tinyint = null
)
as
    set nocount on;

    --Filter as many processes as we can so the later join to BUSINESSPROCESSSTATUS is as small as possible

    declare @BATCHESCOMMITPROCESS_FILTERED as table
    (
        ID uniqueidentifier,
        OWNERID uniqueidentifier,
        SITEID uniqueidentifier
    );

    insert into
        @BATCHESCOMMITPROCESS_FILTERED
    select
        BATCHESCOMMITPROCESS.ID,
        BPI.OWNERID,
        BPI.SITEID
    from
        dbo.BATCHESCOMMITPROCESS
    left join
        dbo.BUSINESSPROCESSINSTANCE as BPI on BPI.BUSINESSPROCESSPARAMETERSETID = BATCHESCOMMITPROCESS.ID
    where 
        (--Check site security

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

            @SITEFILTERMODE = 0
            or
            BPI.SITEID in
            (
                select
                    SITEID
                from
                    dbo.UFN_SITE_BUILDDATALISTSITEFILTER (@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED) 
            )
        )
    and
        (-- Check that user has permission to view business process instance

            dbo.UFN_SECURITY_APPUSER_GRANTED_BUSINESSPROCESSINSTANCE_IN_SYSTEMROLE(@CURRENTAPPUSERID, BATCHESCOMMITPROCESS.ID) = 1
        );

    with BPSTATUS_AGGREGATE as
    (
        select
            BUSINESSPROCESSPARAMETERSETID,
            max(STARTEDON) as STARTEDON_MAX
        from
            dbo.BUSINESSPROCESSSTATUS
        group by
            BUSINESSPROCESSPARAMETERSETID
    )
    select
        BCP.ID,
        BCP.NAME,
        BCP.[DESCRIPTION],
        convert(nvarchar(10), BPSTATUS_AGGREGATE.STARTEDON_MAX, 101) + right(convert(nvarchar(20), BPSTATUS_AGGREGATE.STARTEDON_MAX, 0), 8),
        coalesce(SITE.NAME, 'All Sites') as SITES,
        BCP_FILTER.OWNERID,
        BPSTATUS_AGGREGATE.STARTEDON_MAX
    from
        @BATCHESCOMMITPROCESS_FILTERED as BCP_FILTER
    inner join
        dbo.BATCHESCOMMITPROCESS as BCP on BCP.ID = BCP_FILTER.ID
    left join
        dbo.[SITE] on [SITE].ID = BCP_FILTER.SITEID
    left join
        BPSTATUS_AGGREGATE on BPSTATUS_AGGREGATE.BUSINESSPROCESSPARAMETERSETID = BCP_FILTER.ID;