USP_DATALIST_ACKNOWLEDGEMENTPROCESS

A datalist of saved acknowledgement process parameter sets.
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_ACKNOWLEDGEMENTPROCESS(
                    @CURRENTAPPUSERID uniqueidentifier = null,
                    @SITEFILTERMODE tinyint = 0,
                    @SITESSELECTED xml = null,
                    @SECURITYFEATUREID uniqueidentifier = null,
                    @SECURITYFEATURETYPE tinyint = null
                )
                as 
                begin
                    set nocount on

                    select 
                        ACKNOWLEDGEMENTPROCESS.ID, 
                        ACKNOWLEDGEMENTPROCESS.NAME as PROCESSNAME,
                        ACKNOWLEDGEMENTPROCESS.DESCRIPTION, 
                        LETTERCODE.NAME,
                        case ACKNOWLEDGEMENTPROCESS.MARKLETTERSACKNOWLEDGED
                            when 0 then null
                            else
                                case ACKNOWLEDGEMENTPROCESS.ACKNOWLEDGEDATETYPECODE
                                    when 0 then getdate()
                                    else ACKNOWLEDGEMENTPROCESS.ACKNOWLEDGEDATE 
                                end 
                        end    as ACKNOWLEDGEDATE,
                        BPI.OWNERID as OWNERID,
                        0 as TYPECODE,
                        'Revenue' as TYPE,
                        coalesce(SITE.NAME,'All Sites') SITES
                    from dbo.ACKNOWLEDGEMENTPROCESS
                        inner join dbo.LETTERCODE on ACKNOWLEDGEMENTPROCESS.LETTERCODEID = LETTERCODE.ID
                        left join dbo.BUSINESSPROCESSINSTANCE BPI on ACKNOWLEDGEMENTPROCESS.ID = BPI.BUSINESSPROCESSPARAMETERSETID
                        left join dbo.SITE on SITE.ID = BPI.SITEID
                    where (dbo.UFN_SECURITY_APPUSER_GRANTED_BUSINESSPROCESSINSTANCE_IN_SYSTEMROLE(@CURRENTAPPUSERID, ACKNOWLEDGEMENTPROCESS.ID) = 1)
                        and (
                                (
                                    (--Check site security

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

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

                    union all

                    select 
                        TAP.ID, 
                        TAP.NAME as PROCESSNAME,
                        TAP.DESCRIPTION, 
                        TRIBUTELETTERCODE.NAME,
                        case TAP.MARKLETTERSACKNOWLEDGED
                            when 0 then null
                            else
                                case TAP.ACKNOWLEDGEDATETYPECODE
                                    when 0 then getdate()
                                    else TAP.ACKNOWLEDGEDATE 
                                end 
                        end    as ACKNOWLEDGEDATE,
                        BPI.OWNERID as OWNERID,
                        1 as TYPECODE,
                        'Tribute' as TYPE,
                        coalesce(SITE.NAME,'All Sites') SITES
                    from dbo.TRIBUTEACKNOWLEDGEMENTPROCESS TAP
                        inner join dbo.TRIBUTELETTERCODE on TAP.TRIBUTELETTERCODEID = TRIBUTELETTERCODE.ID
                        left join dbo.BUSINESSPROCESSINSTANCE BPI on TAP.ID = BPI.BUSINESSPROCESSPARAMETERSETID
                        left join dbo.SITE on SITE.ID = BPI.SITEID
                    where (dbo.UFN_SECURITY_APPUSER_GRANTED_BUSINESSPROCESSINSTANCE_IN_SYSTEMROLE(@CURRENTAPPUSERID, TAP.ID) = 1)
                        and (
                                (
                                    (--Check site security

                                        select count(*) 
                                        from dbo.UFN_SITEID_MAPFROM_ACKNOWLEDGEMENTPROCESSID(TAP.ID) as ACKNOWLEDGEMENTPROCESSSITE 
                                        where (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[ACKNOWLEDGEMENTPROCESSSITE].[SITEID] or (SITEID is null and [ACKNOWLEDGEMENTPROCESSSITE].[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 TYPE,PROCESSNAME;
                end