USP_DATALIST_CONSTITUENTPROFILEDASHBOARDBIO

                    CREATE procedure dbo.USP_DATALIST_CONSTITUENTPROFILEDASHBOARDBIO
                    (
                        @CONSTITUENTID uniqueidentifier,
                        @ISVISIBLE bit = 1,
                        @CURRENTAPPUSERID uniqueidentifier
                    )
                    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
                            declare @SSN nvarchar(11);
                            exec dbo.USP_GET_KEY_ACCESS;
                            select @SSN = coalesce(convert(nvarchar(20), DecryptByKey(SSN)),'')
                            from dbo.CONSTITUENT
                            where ID = @CONSTITUENTID;
                            close symmetric key sym_BBInfinity;

                            if @SSN <> ''
                                set @SSN = 'xxx-xx-' + substring(replace(@SSN, '-', ''), 6, 4)

                            select 
                                c.NICKNAME,
                                c.MAIDENNAME,
                                gc.DESCRIPTION GENDER,
                                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 dc.ID is null then 'No' else 'Yes' end DECEASED,
                                coalesce(dc.DECEASEDDATE, '') DECEASEDDATE,
                                @SSN SSN,
                                c.LOOKUPID,
                                case when c.GIVESANONYMOUSLY = 0 then 'No' else 'Yes' end GIVESANONYMOUSLY,
                                case when c.DONOTMAIL = 0 then 'No' else 'Yes' end DONOTMAIL,
                                case when c.DONOTEMAIL = 0 then 'No' else 'Yes' end DONOTEMAIL,
                                case when c.DONOTPHONE = 0 then 'No' else 'Yes' end DONOTPHONE,
                                ms.DESCRIPTION MARITALSTATUS,
                                case when c.ISINACTIVE = 0 then 'No' else 'Yes' end ISINACTIVE,
                                aca.DESCRIPTION ADDEDBY,
                                c.DATEADDED,
                                cca.DESCRIPTION CHANGEDBY,
                                c.DATECHANGED,
                                c.WEBADDRESS,
                                c.ISORGANIZATION,
                                dbo.UFN_INDUSTRYCODE_GETDESCRIPTION(od.INDUSTRYCODEID) as INDUSTRY,
                                coalesce(od.NUMEMPLOYEES,0) as NUMEMPLOYEES,
                                coalesce(od.NUMSUBSIDIARIES,0) as NUMSUBSIDIARIES,
                                (select NAME from dbo.CONSTITUENT where ID = od.PARENTCORPID) as PARENTCORP,
                                (select NF.NAME
                                from dbo.RELATIONSHIP R
                                    outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(R.RECIPROCALCONSTITUENTID) NF
                                    where C.ID = R.RELATIONSHIPCONSTITUENTID and R.ISSPOUSE = 1 
                                        and (@ISADMIN = 1 or 
                                        @APPUSER_IN_NONRACROLE = 1 or
                                        dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT(@CURRENTAPPUSERID, R.RECIPROCALCONSTITUENTID, @APPUSER_IN_NOSECGROUPROLE) = 1)) as SPOUSENAME,

                                (select CODE + ' - ' + DESCRIPTION from dbo.CONSTITUENTINACTIVITYREASONCODE where ID = CONSTITUENTINACTIVEDETAIL.CONSTITUENTINACTIVITYREASONCODEID) as INACTIVEREASONCODE,
                                CONSTITUENTINACTIVEDETAIL.DETAILS as INACTIVEDETAILS,
                                --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.CONSTITUENT c
                                left outer join dbo.DECEASEDCONSTITUENT dc
                                    on c.ID = dc.ID
                                left outer join dbo.MARITALSTATUSCODE ms
                                    on c.MARITALSTATUSCODEID = ms.ID
                                left join dbo.ORGANIZATIONDATA od
                                    on c.ID = od.ID
                                inner join dbo.CHANGEAGENT aca
                                    on c.ADDEDBYID = aca.ID
                                inner join dbo.CHANGEAGENT cca
                                    on c.CHANGEDBYID = cca.ID
                                left outer join dbo.CONSTITUENTINACTIVEDETAIL 
                                    on c.ID = CONSTITUENTINACTIVEDETAIL.ID
                                left outer join dbo.TITLECODE T1 on T1.ID = c.TITLECODEID
                                left outer join dbo.TITLECODE T2 on T2.ID = c.TITLE2CODEID
                                left outer join dbo.SUFFIXCODE S1 on S1.ID = c.SUFFIXCODEID
                                left outer join dbo.SUFFIXCODE S2 on S2.ID = c.SUFFIX2CODEID
                                left outer join dbo.GENDERCODE gc on gc.ID = c.GENDERCODEID
                            where
                                c.ID = @CONSTITUENTID
                        end