USP_DATALIST_CONSTITUENTMEMBERSHIPS_2

      create procedure dbo.USP_DATALIST_CONSTITUENTMEMBERSHIPS_2
      (
        @CONSTITUENTID uniqueidentifier,
        @CURRENTAPPUSERID uniqueidentifier = null,
        @SECURITYFEATUREID uniqueidentifier = null,
        @SECURITYFEATURETYPE tinyint = null,
        @SITEFILTERMODE tinyint = 0,
        @SITESSELECTED xml = null
      )
      as
        set nocount on;

                declare @CONSTITUENTS table (ID uniqueidentifier);

                insert into @CONSTITUENTS
                select @CONSTITUENTID as ID
                union all
                select ID from dbo.UFN_GROUP_GETCURRENTMEMBERS(@CONSTITUENTID, @CURRENTAPPUSERID, '3550edaf-780c-48c8-9700-f478eeac9e51', 0)
                union all
                select ID from dbo.UFN_CONSTITUENT_GETGROUPS(@CONSTITUENTID) where dbo.UFN_CONSTITUENT_ISHOUSEHOLD(ID) = 1;

        select distinct
          MEMBERSHIP.ID,
          MEMBERSHIPPROGRAM.NAME as MEMBERSHIPNAME,
          dbo.UFN_CONSTITUENT_BUILDNAME(PRIMARYMEMBER.CONSTITUENTID) as PRIMARYMEMBERNAME,
          case MEMBERSHIP.STATUSCODE
            When 0 then 1
            When 2 then 2
            When 3 then 3
            When 4 then 4
            When 5 then 5
            when 1 then 6
            else 7 end as STATUSCODE
        from dbo.MEMBERSHIP
          inner join dbo.MEMBERSHIPPROGRAM on MEMBERSHIP.MEMBERSHIPPROGRAMID = MEMBERSHIPPROGRAM.ID
          inner join dbo.MEMBER on MEMBER.MEMBERSHIPID = MEMBERSHIP.ID and MEMBER.ISDROPPED = 0
                    inner join @CONSTITUENTS C on C.ID = MEMBER.CONSTITUENTID
          inner join dbo.MEMBER PRIMARYMEMBER on PRIMARYMEMBER.MEMBERSHIPID = MEMBERSHIP.ID and PRIMARYMEMBER.ISPRIMARY = 1 and PRIMARYMEMBER.ISDROPPED = 0
        where
          (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[MEMBERSHIPPROGRAM].[SITEID] or (SITEID is null and [MEMBERSHIPPROGRAM].[SITEID] is null))) and
          (
            @SITEFILTERMODE = 0 or
            MEMBERSHIPPROGRAM.SITEID in
            (
              select SITEID
              from dbo.UFN_SITE_BUILDDATALISTSITEFILTER (@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED)
            ) or
            @CURRENTAPPUSERID is null
          )
          order by case MEMBERSHIP.STATUSCODE
                  When 0 then 1
                  When 2 then 2
                  When 3 then 3
                  When 4 then 4
                  When 5 then 5
                  when 1 then 6
                  else 7 end