USP_DATALIST_RESEARCHGROUPMEMBERHEADER

CREATE procedure dbo.USP_DATALIST_RESEARCHGROUPMEMBERHEADER
(
    @RESEARCHGROUPID uniqueidentifier,
    @REPORTUSERID nvarchar(128) = null,
    @ALTREPORTUSERID nvarchar(128) = null
)
as
    set nocount on;

  declare @CURRENTAPPUSERID uniqueidentifier

    set @CURRENTAPPUSERID = dbo.UFN_APPUSER_GETREPORTAPPUSERID(@REPORTUSERID, @ALTREPORTUSERID);

    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 
        CONSTITUENT.ID,
        NF.NAME,
        dbo.UFN_BUILDFULLADDRESS(a.ID, a.ADDRESSBLOCK, a.CITY, a.STATEID, a.POSTCODE, a.COUNTRYID) ADDRESS,
        p.NUMBER,
        e.EMAILADDRESS,
    'http://www.blackbaud.com/CONSTITUENTID?CONSTITUENTID=' + CONVERT(nvarchar(36),CONSTITUENT.ID) as CONSTITUENTURL
    from 
        dbo.RESEARCHGROUPMEMBER
        left join dbo.CONSTITUENT on RESEARCHGROUPMEMBER.CONSTITUENTID = CONSTITUENT.ID
        left join dbo.ADDRESS a on CONSTITUENT.ID = a.CONSTITUENTID and a.ISPRIMARY = 1
        left join dbo.PHONE p on CONSTITUENT.ID = p.CONSTITUENTID and p.ISPRIMARY = 1
        left join dbo.EMAILADDRESS e on CONSTITUENT.ID = e.CONSTITUENTID and e.ISPRIMARY = 1
        outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(RESEARCHGROUPMEMBER.CONSTITUENTID) NF
    where
        RESEARCHGROUPMEMBER.RESEARCHGROUPID = @RESEARCHGROUPID
    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 asc, CONSTITUENT.FIRSTNAME asc;