USP_DATALIST_CONSTITUENTPROFILEDASHBOARDSPOUSE

                    CREATE procedure dbo.USP_DATALIST_CONSTITUENTPROFILEDASHBOARDSPOUSE
                    (
                        @CONSTITUENTID uniqueidentifier,
                        @ISVISIBLE bit = 1,
                        @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);

                        if @ISVISIBLE = 1
                        begin
                            select
                                NF.NAME,
                                C.NICKNAME,
                                C.MAIDENNAME,
                                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,
                                cast(case when DC.ID is null then 0 else 1 end as bit) DECEASED,
                                DC.DECEASEDDATE,
                                dbo.UFN_BUILDFULLADDRESS(A.ID, A.ADDRESSBLOCK, A.CITY, A.STATEID, A.POSTCODE, A.COUNTRYID) as ADDRESS,
                                --WI 203780.  Pick up middlename,title1&2,suffix1&2

                                C.MIDDLENAME,T1.DESCRIPTION as TITLE1,T2.DESCRIPTION as TITLE2,
                                S1.DESCRIPTION as SUFFIX1,S2.DESCRIPTION as SUFFIX2
                            from dbo.RELATIONSHIP R
                            inner join dbo.CONSTITUENT C on R.RECIPROCALCONSTITUENTID = C.ID
                            left join dbo.DECEASEDCONSTITUENT DC on C.ID = DC.ID
                            left join dbo.ADDRESS A on C.ID = A.CONSTITUENTID and A.ISPRIMARY = 1
                            left join dbo.TITLECODE T1 on T1.ID = C.TITLECODEID
                            left join dbo.TITLECODE T2 on T2.ID = C.TITLE2CODEID
                            left join dbo.SUFFIXCODE S1 on S1.ID = C.SUFFIXCODEID
                            left join dbo.SUFFIXCODE S2 on S2.ID = C.SUFFIX2CODEID                            
                            outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(C.ID) NF
                            where
                                R.ISSPOUSE = 1 and
                                R.RELATIONSHIPCONSTITUENTID = @CONSTITUENTID
                                and (@ISADMIN = 1 or 
                                        (@APPUSER_IN_NONRACROLE = 1 or
                                        dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT(@CURRENTAPPUSERID, R.RECIPROCALCONSTITUENTID, @APPUSER_IN_NOSECGROUPROLE) = 1)
                                        and
                                        (@APPUSER_IN_NONSITEROLE = 1 or
                                        dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT_BYSITE(@CURRENTAPPUSERID, R.RECIPROCALCONSTITUENTID, @APPUSER_IN_NOSITEROLE) = 1)
                                    );
                        end