USP_DATALIST_JOBOCCURRENCESCHEDULE_1_1

This datalist returns all dates for an occurrence including assignments, used by the Job Occurrence Schedule Dashboard.
Parameters

Parameter	Parameter Type	Mode	Description
@JOBOCCURRENCEID	uniqueidentifier	IN	Job occurrence
@FROM	datetime	IN	From
@TO	datetime	IN	To
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
Definition

Copy


            create procedure dbo.USP_DATALIST_JOBOCCURRENCESCHEDULE_1_1 (
                    @JOBOCCURRENCEID uniqueidentifier = null, 
                    @FROM datetime, 
                    @TO 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    coalesce(SCHEDULE.ASSIGNMENTDATE, VA.DATE),
                        JOB.NAME,
                        JOBOCCURRENCE.STARTTIME,
                        JOBOCCURRENCE.ENDTIME,
                        coalesce(SCHEDULE.VOLUNTEERSNEEDED, 0),
                        coalesce(SCHEDULE.OPENINGS, -1),
                        ltrim(replace(dbo.UDA_BUILDLIST(distinct '|' + CONSTITUENT.NAME), '|', char(10)))
                from [UFN_JOBOCCURRENCE_SCHEDULE](@FROM,@TO,@JOBOCCURRENCEID,null) SCHEDULE
                full join dbo.VOLUNTEERASSIGNMENT VA
                    on VA.JOBOCCURRENCEID = SCHEDULE.OCCURRENCEID
                        and SCHEDULE.ASSIGNMENTDATE = VA.DATE
                inner join dbo.JOBOCCURRENCE on JOBOCCURRENCE.ID = coalesce(VA.JOBOCCURRENCEID, SCHEDULE.OCCURRENCEID)
                inner join dbo.JOB on JOB.ID = JOBOCCURRENCE.JOBID
                left outer join dbo.CONSTITUENT
                    on VA.VOLUNTEERID = CONSTITUENT.ID
                        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)
                            )
                where VA.DATE is null or VA.DATE between @FROM and @TO
                    and JOBOCCURRENCE.ID = @JOBOCCURRENCEID
                group by SCHEDULE.ASSIGNMENTDATE, JOB.NAME,
                        JOBOCCURRENCE.STARTTIME, JOBOCCURRENCE.ENDTIME,
                        SCHEDULE.VOLUNTEERSNEEDED, SCHEDULE.OPENINGS,
                        VA.DATE
                order by SCHEDULE.ASSIGNMENTDATE, JOB.NAME;