USP_DATALIST_CONSTITUENTPROFILEDASHBOARDMEMBERSHIP

This datalist returns memberships data for the constituent profile report.

Parameters

Parameter	Parameter Type	Mode	Description
@CONSTITUENTID	uniqueidentifier	IN	Constituent ID
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
@ISVISIBLE	bit	IN	Is visible

Definition

Copy



                CREATE procedure dbo.USP_DATALIST_CONSTITUENTPROFILEDASHBOARDMEMBERSHIP
                (
                    @CONSTITUENTID uniqueidentifier,
                    @CURRENTAPPUSERID uniqueidentifier = null,
                    @ISVISIBLE bit = 1                    
                )
                as
                    set nocount on;
                    if @ISVISIBLE = 1
                    begin
                    select
                        MEMBERSHIP.LOOKUPID as MEMBERSHIPID,
                        MEMBERSHIP.STATUS,
                        MP.NAME + ' - ' + ML.NAME AS MEMBERSHIPNAME,
                        MEMBERSHIP.EXPIRATIONDATE,
                        MEMBERSHIP.JOINDATE,
                        LEVELTERM.TERM,
                        MEMBERSHIP.LASTRENEWEDON,
                        MEMBERSHIP.COMMENTS,
                        TYPECODE.DESCRIPTION as TYPE,
                        dbo.UFN_CONSTITUENTMEMBERSHIP_GETCOUNT(MEMBER.ID) as NUMBERMEMBERS,
                        dbo.UFN_CONSTITUENTMEMBERSHIP_GETOTHERMEMBERS(MEMBER.ID) as OTHERMEMBERS,
                        MEMBERSHIP.NUMBEROFCHILDREN as NUMBERCHILDREN
                    from dbo.MEMBER
                    inner join dbo.MEMBERSHIP
                        on MEMBER.MEMBERSHIPID=MEMBERSHIP.ID
                    inner join dbo.MEMBERSHIPLEVELTERM AS LEVELTERM on MEMBERSHIP.MEMBERSHIPLEVELTERMID=LEVELTERM.ID
                    inner join dbo.MEMBERSHIPLEVEL ML on MEMBERSHIP.MEMBERSHIPLEVELID = ML.ID
                    inner join dbo.MEMBERSHIPPROGRAM MP    on MEMBERSHIP.MEMBERSHIPPROGRAMID = MP.ID                    
                    left join dbo.MEMBERSHIPLEVELTYPECODE as TYPECODE on MEMBERSHIP.MEMBERSHIPLEVELTYPECODEID=TYPECODE.ID        
                    left join dbo.SITE on MP.SITEID = SITE.ID
                    where MEMBER.CONSTITUENTID=@CONSTITUENTID
                        and MEMBER.ISDROPPED = 0
                        and MEMBERSHIP.STATUSCODE = 0
                        and dbo.UFN_SITEALLOWEDFORUSER(@CURRENTAPPUSERID, MP.SITEID)=1;
                    end