USP_CONSTITUENT_GETINFORMATIONFORMEMBERSHIP

            CREATE procedure dbo.USP_CONSTITUENT_GETINFORMATIONFORMEMBERSHIP
            (
                @CONSTITUENTID uniqueidentifier,
                @CURRENTAPPUSERID uniqueidentifier
            )
            as
            begin
                declare @FEATUREID uniqueidentifier = '2852e35e-9b16-4f98-906c-51b7740e1ab4'
        if (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1) or
               ((dbo.UFN_SECURITY_APPUSER_GRANTED_FORM_FORCONSTIT(@CURRENTAPPUSERID, @FEATUREID, @CONSTITUENTID) = 1) and -- Constituent group security

               exists (select 1 from dbo.UFN_SITEID_MAPFROM_CONSTITUENTID(@CONSTITUENTID) where dbo.UFN_SECURITY_APPUSER_GRANTED_FORM_FORSITE(@CURRENTAPPUSERID, @FEATUREID, SITEID) = 1)) -- Site security

            begin
                  select
                      NF.NAME as NAME,
                      LOOKUPID,
                      ATC.DESCRIPTION as ADDRESSTYPE,
                      dbo.UFN_BUILDFULLADDRESS(A.ID, A.ADDRESSBLOCK, A.CITY, A.STATEID, A.POSTCODE, A.COUNTRYID) as ADDRESS,
                      EMAILADDRESS,
                      dbo.UFN_PHONE_GETINTERNATIONALNUMBER(P.COUNTRYID, P.NUMBER) as PHONE,
                      EI.NAME as SCHOOL,
                      EH.CLASSOF as CLASSOF,
                      C.ISORGANIZATION,
                      C.ISGROUP,
                      dbo.UFN_CONSTITUENT_ISHOUSEHOLD(C.ID) AS ISHOUSEHOLD
                  from
                      dbo.CONSTITUENT C
                      left join dbo.ADDRESS A on A.CONSTITUENTID = C.ID and A.ISPRIMARY = 1
                      left join dbo.ADDRESSTYPECODE ATC on A.ADDRESSTYPECODEID = ATC.ID
                      left join dbo.EMAILADDRESS E on E.CONSTITUENTID = C.ID and E.ISPRIMARY = 1
                      left join dbo.PHONE P on P.CONSTITUENTID = C.ID and P.ISPRIMARY = 1
                      left join dbo.EDUCATIONALHISTORY EH on C.ID = EH.CONSTITUENTID and EH.ISPRIMARYRECORD = 1
                      left join dbo.EDUCATIONALINSTITUTION EI on EH.EDUCATIONALINSTITUTIONID = EI.ID
                      outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(C.ID) NF
                  where
                      C.ID = @CONSTITUENTID
          end
            end