USP_DATALIST_CONSTITUENTMEMBERSHIPS

            CREATE procedure dbo.USP_DATALIST_CONSTITUENTMEMBERSHIPS
            (
                @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,
                    convert(nvarchar(36), MEMBERSHIP.ID) + convert(nvarchar(36), @CONSTITUENTID) as COMBOID,
                    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