USP_DATALIST_CONSTITUENTPROFILEDASHBOARDGROUPMEMBERS

                    CREATE procedure dbo.USP_DATALIST_CONSTITUENTPROFILEDASHBOARDGROUPMEMBERS
                    (
                        @CONSTITUENTID uniqueidentifier,
                        @ISVISIBLE bit = 1,
                        @CURRENTAPPUSERID uniqueidentifier
                    )
                    as
                        set nocount on;

                    -- Bug 26927 - AdamBu - Security is not enforced in the members datalist, so don't enforce it here.

                    --    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);


                        if @ISVISIBLE = 1
                        begin
                            declare @CURRENTDATE date;
                            set @CURRENTDATE = getdate()

                            declare @PRIMARYMEMBERID uniqueidentifier
                            select @PRIMARYMEMBERID = MEMBERID 
                            from dbo.GROUPMEMBER 
                            where 
                                GROUPID = @CONSTITUENTID and
                                ISPRIMARY = 1

                            select
                                NF.NAME + case
                                    when GM.ISPRIMARY = 1 then ' (Primary)'
                                    else ''
                                end NAME,
                                C.NICKNAME,
                                C.MAIDENNAME,
                                dbo.UFN_BUILDFULLADDRESS(A.ID, A.ADDRESSBLOCK, A.CITY, A.STATEID, A.POSTCODE, A.COUNTRYID) ADDRESS,
                                C.BIRTHDATE,
                                case when DC.ID is null and C.BIRTHDATE <> '00000000' and not substring(C.BIRTHDATE,1,4) = '0000' then C.AGE else null end as AGE,
                                case when GM.ISPRIMARY = 1 
                                    then ''
                                    else (select dbo.UDA_BUILDLIST(distinct RTC.DESCRIPTION)
                                            from dbo.RELATIONSHIP R
                                            join dbo.RELATIONSHIPTYPECODE RTC
                                            on R.RELATIONSHIPTYPECODEID = RTC.ID
                                            where C.ID = R.RELATIONSHIPCONSTITUENTID
                                            and R.RECIPROCALCONSTITUENTID = @PRIMARYMEMBERID)
                                    end as RELATIONSHIPWITHPRIMARY,
                                (select dbo.UDA_BUILDLIST(dbo.UFN_GROUPMEMBERROLECODE_GETDESCRIPTION(GMR.GROUPMEMBERROLECODEID)) 
                                    from dbo.GROUPMEMBERROLE GMR
                                    where GMR.GROUPMEMBERID = GM.ID
                                    and ((GMR.STARTDATE is null and (GMR.ENDDATE is null or GMR.ENDDATE > @CURRENTDATE))
                                        or (GMR.ENDDATE is null and (GMR.STARTDATE is null or GMR.STARTDATE <= @CURRENTDATE)) 
                                        or (GMR.STARTDATE <= @CURRENTDATE and GMR.ENDDATE > @CURRENTDATE))) as ROLES
                            from dbo.GROUPMEMBER GM
                            inner join dbo.CONSTITUENT C on GM.MEMBERID = C.ID
                            left join dbo.ADDRESS A on C.ID = A.CONSTITUENTID and A.ISPRIMARY = 1
                            left outer join dbo.DECEASEDCONSTITUENT DC on DC.ID = C.ID
                            left outer join dbo.GROUPMEMBERDATERANGE as GMDR on GM.ID = GMDR.GROUPMEMBERID
                            outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(C.ID) NF
                            where ((GMDR.DATEFROM is null and (GMDR.DATETO is null or GMDR.DATETO > @CURRENTDATE))
                                or (GMDR.DATETO is null and (GMDR.DATEFROM is null or GMDR.DATEFROM <= @CURRENTDATE)) 
                                or (GMDR.DATEFROM <= @CURRENTDATE and GMDR.DATETO > @CURRENTDATE))
                                and GM.GROUPID = @CONSTITUENTID
                            -- Bug 26927 - AdamBu - Security is not enforced in the members datalist, so don't enforce it here.

                            --    and (@ISADMIN = 1 or

                            --            (@APPUSER_IN_NONRACROLE = 1 or

                            --            dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT(@CURRENTAPPUSERID, GM.MEMBERID, @APPUSER_IN_NOSECGROUPROLE) = 1)

                            --            and

                            --            (@APPUSER_IN_NONSITEROLE = 1 or

                            --            dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT_BYSITE(@CURRENTAPPUSERID, GM.MEMBERID, @APPUSER_IN_NOSITEROLE) = 1)

                            --        )

                            order by GM.ISPRIMARY desc, C.KEYNAME, C.FIRSTNAME
                        end