USP_DATALIST_WEALTHINFORMATIONADDRESSES

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




            select A.ID,
                   A.ISPRIMARY,
                   A.DONOTMAIL,
               A.ISCONFIDENTIAL,
                   A.STARTDATE,
               A.ENDDATE,
               A.ADDRESSBLOCK, 
               A.CITY,
               ST.ABBREVIATION,
               A.POSTCODE,
               C.DESCRIPTION,
               C.ABBREVIATION as COUNTRYABBREVIATION,
               ATC.DESCRIPTION as ADDRESSTYPE
            from dbo.ADDRESS A
          inner join dbo.STATE ST on a.STATEID = ST.ID
          inner join dbo.COUNTRY C on a.COUNTRYID = C.ID
          left outer join dbo.ADDRESSTYPECODE ATC on A.ADDRESSTYPECODEID = ATC.ID
            where CONSTITUENTID = @CONSTITUENTID
          and (@ISADMIN = 1 or 
                                @APPUSER_IN_NONRACROLE = 1 or
                                dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT(@CURRENTAPPUSERID, A.CONSTITUENTID, @APPUSER_IN_NOSECGROUPROLE) = 1) 
                ORDER BY A.SEQUENCE;