USP_DATALIST_WEALTHINFORMATIONDASHBOARDHEADER

                CREATE procedure dbo.USP_DATALIST_WEALTHINFORMATIONDASHBOARDHEADER(
                    @CONSTITUENTID uniqueidentifier,
                    @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();

                    select
                        NF.NAME,
                        dbo.UFN_BUILDFULLADDRESS(a.ID, a.ADDRESSBLOCK, a.CITY, a.STATEID, a.POSTCODE, a.COUNTRYID) as ADDRESS,
                        dbo.UFN_PHONE_GETINTERNATIONALNUMBER(phone.COUNTRYID, phone.NUMBER) as NUMBER,
                        e.EMAILADDRESS,
                        org.NAME as PRIMARYBUSINESS, 
                        rji.JOBTITLE as POSITION, 
                        spouse_NF.NAME as SPOUSENAME,
                        c.LOOKUPID as LOOKUPID,
                        PROSPECTMANAGER_NF.NAME as PROSPECTMANAGER,
                        c.[PICTURE],
            ptc.DESCRIPTION as PHONETYPE
                    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
              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 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
                        left outer join dbo.PROSPECT p
                            on c.ID = p.ID
                        left outer join dbo.ADDRESS a
                            on c.ID = a.CONSTITUENTID
                            and a.ISPRIMARY = 1
              and a.ISCONFIDENTIAL = 0
              and a.DONOTMAIL = 0
                        left outer join dbo.PHONE phone
                            on c.ID = phone.CONSTITUENTID
                            and phone.ISPRIMARY = 1
              and phone.ISCONFIDENTIAL = 0
              and phone.DONOTCALL = 0
                        left outer join dbo.EMAILADDRESS e
                            on c.ID = e.CONSTITUENTID
                            and e.ISPRIMARY = 1
              and e.DONOTEMAIL = 0
            left outer join dbo.PHONETYPECODE ptc
              on ptc.ID = phone.PHONETYPECODEID
                        outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(spouse.ID) spouse_NF
                        outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(p.PROSPECTMANAGERFUNDRAISERID) PROSPECTMANAGER_NF
                        outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(C.ID) NF
                    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);