USP_JOBOCCURRENCE_ASSIGN_GETASSIGNEDVOLUNTEERS

CREATE procedure dbo.USP_JOBOCCURRENCE_ASSIGN_GETASSIGNEDVOLUNTEERS 
(
    @JOBOCCURRENCEID uniqueidentifier = null, 
    @DATE date,
    @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,
        NF.NAME,
        cast(dbo.UFN_VOLUNTEER_JOBOCCURRENCE_GETMATCH(VA.VOLUNTEERID, @JOBOCCURRENCEID, @DATE, @DATE) as nvarchar(3)) + '%' MATCH
    from dbo.VOLUNTEERASSIGNMENT VA
    inner join dbo.CONSTITUENT
        on VA.VOLUNTEERID = CONSTITUENT.ID
  outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(CONSTITUENT.ID) NF
    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.KEYNAME desc