USP_JOBOCCURRENCE_ASSIGN_VOLUNTEER

                    CREATE procedure dbo.USP_JOBOCCURRENCE_ASSIGN_VOLUNTEER
                    (
                    @ID nvarchar(max),
                    @CHANGEAGENTID uniqueidentifier,
                    @CURRENTAPPUSERID uniqueidentifier
                    )
                    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);


                        declare @NOW datetime;
                        declare @JOBOCCURRENCEID uniqueidentifier;
                        declare @VOLUNTEERID uniqueidentifier;
                        declare @DAYLIST nvarchar(max);
                        declare @DAYLISTORIGINAL nvarchar(max);
                        declare @DATE datetime;
                        declare @DAY table (DY datetime);

                        set @NOW = getdate()

                        if @CHANGEAGENTID is null  
                            exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

                        --get job and volunteer IDs

                        set @JOBOCCURRENCEID = cast(left(@ID, 36) as uniqueidentifier);
                        set @VOLUNTEERID = cast(substring(@ID, 37, 36) as uniqueidentifier);

                        if not (@ISADMIN = 1 or 
                            (@APPUSER_IN_NONRACROLE = 1 or
                            dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT(@CURRENTAPPUSERID, @VOLUNTEERID, @APPUSER_IN_NOSECGROUPROLE) = 1)
                            and
                            (@APPUSER_IN_NONSITEROLE = 1 or
                            dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT_BYSITE(@CURRENTAPPUSERID, @VOLUNTEERID, @APPUSER_IN_NOSITEROLE) = 1)
                            )
                        begin
                            raiserror ('ERR_NOVOLUNTEERACCESS',13,1);
                            return 0;
                        end
                        else
                        begin
                            --Put days into a table variable

                            set @DAYLIST = right(@ID, len(@ID) - 72)
                            set @DAYLISTORIGINAL = @DAYLIST;
                            set @DATE = cast(right(@ID, 8) as datetime);

                            Set @DAYLIST = ltrim(rtrim(@DAYLIST))
                            while len(@DAYLIST) >= 8
                            begin
                                set @DATE = left(@DAYLIST, 8)
                                insert into @DAY(DY) values(cast(@DATE as datetime));
                                if len(@DAYLIST) > 8
                                    set @DAYLIST = right(@DAYLIST, len(@DAYLIST) - 8)
                                else
                                    set @DAYLIST = ''
                            end

                            -- bwj WI#79005 Validate that the assignment doesn't overlap with another assignment by the volunteer.

                            if not exists(
                                select
                                    AVAILABLE.VOLUNTEERID
                                from
                                    dbo.UFN_JOBOCCURRENCE_SCHEDULE(@DATE,@DATE,@JOBOCCURRENCEID,null) JOBOCCURENCES_ONDATE
                                inner join
                                    dbo.JOBOCCURRENCE on JOBOCCURRENCE.ID = @JOBOCCURRENCEID
                                inner join
                                    dbo.UFN_VOLUNTEER_AVAILABLESPECIFICDAYS(@DAYLISTORIGINAL, @VOLUNTEERID, 1) AVAILABLE on AVAILABLE.AVAILABLEDATE = JOBOCCURENCES_ONDATE.ASSIGNMENTDATE
                                left outer join
                                    dbo.VOLUNTEERASSIGNMENT on AVAILABLE.VOLUNTEERID = VOLUNTEERASSIGNMENT.VOLUNTEERID and VOLUNTEERASSIGNMENT.DATE = JOBOCCURENCES_ONDATE.ASSIGNMENTDATE
                                left outer join
                                    dbo.JOBOCCURRENCE ASSIGNEDJOB on ASSIGNEDJOB.ID = VOLUNTEERASSIGNMENT.JOBOCCURRENCEID and 
                                    (
                                        ASSIGNEDJOB.STARTTIME between JOBOCCURRENCE.STARTTIME and JOBOCCURRENCE.ENDTIME    or
                                        ASSIGNEDJOB.ENDTIME between JOBOCCURRENCE.STARTTIME and JOBOCCURRENCE.ENDTIME or 
                                        ASSIGNEDJOB.STARTTIME = '' or ASSIGNEDJOB.ENDTIME = '' or 
                                        JOBOCCURRENCE.STARTTIME = '' or JOBOCCURRENCE.ENDTIME  = ''
                                    )
                                where
                                    (AVAILABLE.STARTTIME <= JOBOCCURRENCE.STARTTIME or JOBOCCURRENCE.STARTTIME = '')
                                and
                                    (AVAILABLE.ENDTIME >= JOBOCCURRENCE.ENDTIME or AVAILABLE.ENDTIME = '')
                                group by
                                    AVAILABLE.VOLUNTEERID
                                having
                                    max(case when ASSIGNEDJOB.ID is null then 0 else 1 end) = 0
                            )
                            begin
                                raiserror('ERR_VOLUNTEERALREADYSCHEDULED', 13, 1);            
                                return 0;
                            end                

                            insert into dbo.VOLUNTEERASSIGNMENT(JOBOCCURRENCEID, VOLUNTEERID,DATE,ADDEDBYID,CHANGEDBYID,DATECHANGED,DATEADDED)
                            select @JOBOCCURRENCEID, @VOLUNTEERID, DYTBL.DY, @CHANGEAGENTID,@CHANGEAGENTID, @NOW, @NOW
                            from @DAY DYTBL

                            return 0;
                        end