USP_DATALIST_WEALTHINFORMATIONDASHBOARDSPOUSEDETAILS

                CREATE procedure dbo.USP_DATALIST_WEALTHINFORMATIONDASHBOARDSPOUSEDETAILS
                (
                    @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);

                    declare @CURRENTDATE datetime;
                    set @CURRENTDATE = getdate();

                if @ISVISIBLE = 1
                begin
                    select
                        c.MAIDENNAME,
                        gc.DESCRIPTION GENDER,
                        c.BIRTHDATE,
                        c.LOOKUPID,
                        case when c.BIRTHDATE <> '00000000' and not substring(c.BIRTHDATE,1,4) = '0000' then c.AGE else null end as AGE,
                        rji.JOBTITLE as POSITION,
                        org.NAME as PRIMARYBUSINESS
                     from dbo.RELATIONSHIP R
                    inner join dbo.CONSTITUENT C 
                        on R.RECIPROCALCONSTITUENTID = C.ID
                    left outer join dbo.RELATIONSHIP rpb
                        on rpb.RELATIONSHIPCONSTITUENTID = c.ID and rpb.ISPRIMARYBUSINESS = 1
                    left join dbo.RELATIONSHIPSET rs 
                        on rpb.RELATIONSHIPSETID = rs.ID
                    left join dbo.RELATIONSHIPJOBINFO rji 
                        on rji.RELATIONSHIPSETID = rs.ID
                        and (rji.STARTDATE is NULL or rji.STARTDATE <= @CURRENTDATE)
                        and (rji.ENDDATE is NULL or rji.ENDDATE >= @CURRENTDATE)
                    left outer join dbo.CONSTITUENT org
                        on rpb.RECIPROCALCONSTITUENTID = org.ID
                    left outer join dbo.GENDERCODE gc
                        on c.GENDERCODEID = gc.ID
                    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, c.ID, @APPUSER_IN_NOSECGROUPROLE) = 1);
                end
                else
                begin
                    select top 1
                        c.MAIDENNAME,
                        gc.DESCRIPTION GENDER,
                        c.BIRTHDATE,
                        c.LOOKUPID,
                        case when c.BIRTHDATE <> '00000000' and not substring(c.BIRTHDATE,1,4) = '0000' then c.AGE else null end as AGE,
                        rji.JOBTITLE as POSITION,
                        org.NAME as PRIMARYBUSINESS
                    from dbo.RELATIONSHIP R
                    inner join dbo.CONSTITUENT C 
                        on R.RECIPROCALCONSTITUENTID = C.ID
                    left outer join dbo.RELATIONSHIP rpb
                        on rpb.RELATIONSHIPCONSTITUENTID = c.ID and rpb.ISPRIMARYBUSINESS = 1
                    left join dbo.RELATIONSHIPSET rs 
                        on rpb.RELATIONSHIPSETID = rs.ID
                    left join dbo.RELATIONSHIPJOBINFO rji 
                        on rji.RELATIONSHIPSETID = rs.ID
                    left outer join dbo.CONSTITUENT org
                        on rpb.RECIPROCALCONSTITUENTID = org.ID
                    left outer join dbo.GENDERCODE gc
                        on c.GENDERCODEID = gc.ID
                    where
                        R.ISSPOUSE = 1
                        and R.RELATIONSHIPCONSTITUENTID = @CONSTITUENTID
                end