USP_DATALIST_CONSTITUENTMEMBERSHIP

This datalist returns the memberships of a constituent.
Parameters

Parameter	Parameter Type	Mode	Description
@CONSTITUENTID	uniqueidentifier	IN	Input parameter indicating the context ID for the data list.
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
@SITEFILTERMODE	tinyint	IN	Sites
@SITESSELECTED	xml	IN
@SECURITYFEATUREID	uniqueidentifier	IN	Input parameter indicating the ID of the feature to use for site security checking.
@SECURITYFEATURETYPE	tinyint	IN	Input parameter indicating the type of the feature to use for site security checking.
Definition

Copy


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

                    select
                        MEMBER.ID,
                        MEMBERSHIP.ID as MEMBERSHIPID,
                        MEMBERSHIP.STATUSCODE,
                        MEMBERSHIP.MEMBERSHIPPROGRAMID as MEMBERSHIPPROGRAMID,
                        (dbo.UFN_MEMBERSHIPPROGRAM_GETNAME(MEMBERSHIP.MEMBERSHIPPROGRAMID) + ' - ' + dbo.UFN_MEMBERSHIPLEVEL_GETNAME(MEMBERSHIP.MEMBERSHIPLEVELID)) AS MEMBERSHIPS,
                        dbo.UFN_MEMBERSHIPLEVEL_CREATERENEWALAFTEREXPIRATIONDATE(MEMBERSHIP.MEMBERSHIPLEVELID, MEMBERSHIP.EXPIRATIONDATE) as RENEWALAFTEREXPIRATION,
                        dbo.UFN_MEMBERSHIPLEVEL_CREATERENEWALBEFOREEXPIRATIONDATE(MEMBERSHIP.MEMBERSHIPLEVELID, MEMBERSHIP.EXPIRATIONDATE) as RENEWALBEFOREEXPIRATION,
                        cast(MEMBERSHIP.EXPIRATIONDATE as date) EXPIRATIONDATE,
                        MEMBERSHIPPROGRAM.ISACTIVE,
                        MEMBERSHIP.MEMBERSHIPLEVELID,
                        MEMBERSHIP.MEMBERSHIPLEVELTERMID,
                        MEMBERSHIP.MEMBERSHIPLEVELTYPECODEID,    
                        NF.NAME as CONSTITUENT,
                        MEMBERSHIP.LOOKUPID,
                        case 
                            when MEMBERSHIP.STATUSCODE = 0 and MEMBERSHIP.EXPIRATIONDATE < getdate() then 'Lapsed'
                            else MEMBERSHIP.STATUS
                        end as STATUS,
                        dbo.UFN_MEMBERSHIPLEVELTERM_GETVALUE(MEMBERSHIP.MEMBERSHIPLEVELTERMID) as TERM,
                        SITE.NAME SITE
                    from  dbo.MEMBER
                    inner join dbo.MEMBERSHIP
                        on MEMBER.MEMBERSHIPID=MEMBERSHIP.ID
                    inner join dbo.MEMBERSHIPLEVEL
                        on MEMBERSHIP.MEMBERSHIPLEVELID = MEMBERSHIPLEVEL.ID
                    inner join dbo.MEMBERSHIPPROGRAM
                        on MEMBERSHIPPROGRAM.ID = MEMBERSHIP.MEMBERSHIPPROGRAMID
                    left join dbo.SITE 
                        on MEMBERSHIPPROGRAM.SITEID = SITE.ID
                    outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(MEMBER.CONSTITUENTID) NF
                    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
                        )
                        and MEMBER.ISDROPPED = 0
                        and (
                            MEMBER.CONSTITUENTID=@CONSTITUENTID or
                            MEMBER.CONSTITUENTID in (
                                select ID
                                from dbo.UFN_GROUP_GETCURRENTMEMBERS(@CONSTITUENTID, @CURRENTAPPUSERID, 'aef629df-9698-41b4-97c3-a83a40f87b18', 0)
                                -- Check security against Constituent Membership View Form

                            ) or
                            MEMBER.CONSTITUENTID in (
                                select ID
                                from dbo.UFN_CONSTITUENT_GETGROUPS(@CONSTITUENTID)
                                where dbo.UFN_CONSTITUENT_ISHOUSEHOLD(ID) = 1
                            )
                        );