USP_DATALIST_WEALTHINFORMATIONDASHBOARDPROSPECTSUMMARY

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

                    declare @ISADMIN bit;
                    declare @APPUSER_IN_NONRACROLE bit;
                    declare @APPUSER_IN_NOSECGROUPROLE 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);


                if @ISVISIBLE = 1
                begin
                    select
                        org.NAME as PRIMARYBUSINESS,
                        rji.JOBTITLE POSITION, 
                        c.NICKNAME, 
                        gc.DESCRIPTION GENDER, 
                        c.BIRTHDATE, 
                        spouse_NF.NAME as SPOUSENAME,
                        c.MAIDENNAME,
                        c.WEBADDRESS,
                        ms.DESCRIPTION MARITALSTATUS,
                        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
                    from
                        dbo.CONSTITUENT c
                        left outer join dbo.RELATIONSHIP r
                            on r.RELATIONSHIPCONSTITUENTID = c.ID and r.ISPRIMARYBUSINESS = 1
                        left join dbo.RELATIONSHIPSET rs 
                            on r.RELATIONSHIPSETID = rs.ID
                        left join dbo.RELATIONSHIPJOBINFO rji 
                            on rji.RELATIONSHIPSETID = rs.ID
                        left outer join dbo.CONSTITUENT org
                            on r.RECIPROCALCONSTITUENTID = org.ID
                        left outer join dbo.RELATIONSHIP s
                            on s.RELATIONSHIPCONSTITUENTID = c.ID and s.ISSPOUSE = 1
                        left outer join dbo.CONSTITUENT spouse
                            on s.RECIPROCALCONSTITUENTID = spouse.ID
                        left outer join dbo.WEALTHCAPACITY w
                            on w.ID = c.ID
                        outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(spouse.ID) spouse_NF
                      left outer join dbo.MARITALSTATUSCODE ms
                          on c.MARITALSTATUSCODEID = ms.ID
                      left outer join dbo.DECEASEDCONSTITUENT dc
                            on c.ID = dc.ID
                        left outer join dbo.GENDERCODE gc
                            on c.GENDERCODEID = gc.ID
                    where
                        c.ID = @CONSTITUENTID
                        and (@ISADMIN = 1 or 
                            @APPUSER_IN_NONRACROLE = 1 or
                            dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT(@CURRENTAPPUSERID, c.ID, @APPUSER_IN_NOSECGROUPROLE) = 1);
                end
                else
                begin
                    select top 1
                        org.NAME as PRIMARYBUSINESS,
                        rji.JOBTITLE POSITION, 
                        c.NICKNAME, 
                        gc.DESCRIPTION GENDER, 
                        c.BIRTHDATE, 
                        spouse_NF.NAME as SPOUSENAME,
                        c.MAIDENNAME,
                        c.WEBADDRESS,
                        ms.DESCRIPTION MARITALSTATUS,
                        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
                    from
                        dbo.CONSTITUENT c
                        left outer join dbo.RELATIONSHIP r
                            on r.RELATIONSHIPCONSTITUENTID = c.ID and r.ISPRIMARYBUSINESS = 1
                        left join dbo.RELATIONSHIPSET rs 
                            on r.RELATIONSHIPSETID = rs.ID
                        left join dbo.RELATIONSHIPJOBINFO rji 
                            on rji.RELATIONSHIPSETID = rs.ID
                        left outer join dbo.CONSTITUENT org
                            on r.RECIPROCALCONSTITUENTID = org.ID
                        left outer join dbo.RELATIONSHIP s
                            on s.RELATIONSHIPCONSTITUENTID = c.ID and s.ISSPOUSE = 1
                        left outer join dbo.CONSTITUENT spouse
                            on s.RECIPROCALCONSTITUENTID = spouse.ID
                        left outer join dbo.WEALTHCAPACITY w
                            on w.ID = c.ID
                        outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(spouse.ID) spouse_NF             
                        left outer join dbo.MARITALSTATUSCODE ms
                          on c.MARITALSTATUSCODEID = ms.ID
                        left outer join dbo.DECEASEDCONSTITUENT dc
                            on c.ID = dc.ID
                        left outer join dbo.GENDERCODE gc
                            on c.GENDERCODEID = gc.ID
                    where
                        c.ID = @CONSTITUENTID
                end