USP_DATALIST_JOBOCCURRENCEDAYASSIGNED

This datalist returns all Volunteers for an occurrence on a particular day.

Parameters

Parameter	Parameter Type	Mode	Description
@JOBOCCURRENCEID	uniqueidentifier	IN	Input parameter indicating the context ID for the data list.
@DATE	datetime	IN	Date
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.

Definition

Copy



            create procedure dbo.USP_DATALIST_JOBOCCURRENCEDAYASSIGNED (
                    @JOBOCCURRENCEID uniqueidentifier = null, 
                    @DATE datetime,
                    @CURRENTAPPUSERID uniqueidentifier = null            
                    )
            as
                set nocount on;

                declare @ISADMIN bit;
                declare @APPUSER_IN_NONRACROLE bit;
                declare @APPUSER_IN_NOSECGROUPROLE bit;
                declare @APPUSER_IN_NONSITEROLE bit;
                declare @APPUSER_IN_NOSITEROLE bit;

                set @ISADMIN = dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID);
                set @APPUSER_IN_NONRACROLE = dbo.UFN_SECURITY_APPUSER_IN_NONRACROLE(@CURRENTAPPUSERID);
                set @APPUSER_IN_NOSECGROUPROLE = dbo.UFN_SECURITY_APPUSER_IN_NO_SECURITY_GROUP_ROLE(@CURRENTAPPUSERID);
                set @APPUSER_IN_NONSITEROLE = dbo.UFN_SECURITY_APPUSER_IN_NONSITEROLE(@CURRENTAPPUSERID);
                set @APPUSER_IN_NOSITEROLE = dbo.UFN_SECURITY_APPUSER_IN_NO_SITE_ROLE(@CURRENTAPPUSERID);

                select VA.ID,
                        VA.VOLUNTEERID,
                        CONSTITUENT.NAME 
                from dbo.VOLUNTEERASSIGNMENT VA
                inner join dbo.CONSTITUENT
                    on VA.VOLUNTEERID = CONSTITUENT.ID
                where VA.JOBOCCURRENCEID = @JOBOCCURRENCEID 
                        and VA.DATE = @DATE
                        and (@ISADMIN = 1 or 
                                (@APPUSER_IN_NONRACROLE = 1 or
                                dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT(@CURRENTAPPUSERID, CONSTITUENT.ID, @APPUSER_IN_NOSECGROUPROLE) = 1)
                                and
                                (@APPUSER_IN_NONSITEROLE = 1 or
                                dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT_BYSITE(@CURRENTAPPUSERID, CONSTITUENT.ID, @APPUSER_IN_NOSITEROLE) = 1)
                            )
                order by CONSTITUENT.NAME