USP_DATAFORMTEMPLATE_VIEW_MEMBERSHIPPAGEDATA

The load procedure used by the view dataform template "Member Page Expression View Form"

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	IN	The input ID parameter used to load the fields defined on the form.
@DATALOADED	bit	INOUT	Output parameter indicating whether or not data was actually loaded.
@MEMBERSHIPPROGRAMID	uniqueidentifier	INOUT	Membership Program ID
@MEMBERSHIPID	uniqueidentifier	INOUT	Membership ID
@CONSTITUENTID	uniqueidentifier	INOUT	Constituent ID
@CONSTITUENTNAME	nvarchar(700)	INOUT	Constituent Name
@MEMBERSHIPPROGRAMNAME	nvarchar(100)	INOUT	Membership Program Name
@ISPRIMARY	bit	INOUT	Primary
@STATUSCODE	tinyint	INOUT	STATUSCODE
@RENEWALAFTEREXPIRATION	datetime	INOUT	RENEWALAFTEREXPIRATION
@RENEWALBEFOREEXPIRATION	datetime	INOUT	RENEWALBEFOREEXPIRATION
@MEMBERCOUNT	smallint	INOUT	Count
@ISACTIVE	tinyint	INOUT	Is active
@ISREFUNDABLE	bit	INOUT

Definition

Copy


            CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_MEMBERSHIPPAGEDATA
            (
                @ID uniqueidentifier,
                @DATALOADED bit=0 output,
                @MEMBERSHIPPROGRAMID uniqueidentifier=null output,
                @MEMBERSHIPID uniqueidentifier=null output,
                @CONSTITUENTID uniqueidentifier=null output,
                @CONSTITUENTNAME nvarchar(700)=null output,
                @MEMBERSHIPPROGRAMNAME nvarchar(100)=null output,
                @ISPRIMARY bit=null output,
                @STATUSCODE tinyint = null output,
                @RENEWALAFTEREXPIRATION datetime = null output,
                @RENEWALBEFOREEXPIRATION datetime = null output,
                @MEMBERCOUNT smallint = null output,
                @ISACTIVE tinyint = null output,
                @ISREFUNDABLE bit = null output
            )
            as 
                set nocount on;

                set @DATALOADED=0;

                select 
                    @DATALOADED=1,
                    @MEMBERSHIPPROGRAMID=MEMBERSHIP.MEMBERSHIPPROGRAMID,
                    @MEMBERSHIPID=MEMBER.MEMBERSHIPID,
                    @CONSTITUENTID=MEMBER.CONSTITUENTID,
                    @CONSTITUENTNAME=NF.NAME,
                    @MEMBERSHIPPROGRAMNAME=dbo.UFN_MEMBERSHIPPROGRAM_GETNAME(@MEMBERSHIPPROGRAMID),
                    @ISPRIMARY = MEMBER.ISPRIMARY,
                    @STATUSCODE = MEMBERSHIP.STATUSCODE,
                    @RENEWALAFTEREXPIRATION = dbo.UFN_MEMBERSHIPLEVEL_CREATERENEWALAFTEREXPIRATIONDATE(MEMBERSHIP.MEMBERSHIPLEVELID, MEMBERSHIP.EXPIRATIONDATE),
                    @RENEWALBEFOREEXPIRATION = dbo.UFN_MEMBERSHIPLEVEL_CREATERENEWALBEFOREEXPIRATIONDATE(MEMBERSHIP.MEMBERSHIPLEVELID, MEMBERSHIP.EXPIRATIONDATE),
                    @MEMBERCOUNT = dbo.UFN_CONSTITUENTMEMBERSHIP_GETCOUNT(@ID),
                    @ISACTIVE = MEMBERSHIPPROGRAM.ISACTIVE,
                    @ISREFUNDABLE = 
                        case when MEMBERSHIP.STATUSCODE = 0 and MEMBERSHIP.EXPIRATIONDATE > getDate() 
                                and exists(
                                        select MEMBERSHIPTRANSACTION.ID 
                                        from dbo.MEMBERSHIPTRANSACTION 
                                        where MEMBERSHIPTRANSACTION.MEMBERSHIPID = MEMBER.MEMBERSHIPID 
                                            and MEMBERSHIPTRANSACTION.REVENUESPLITID is not null
                                    )
                                then 1
                            else 0
                        end
                from 
                    dbo.MEMBER 
                inner join dbo.MEMBERSHIP on MEMBER.MEMBERSHIPID=MEMBERSHIP.ID
                outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(MEMBER.CONSTITUENTID) NF
                inner join dbo.MEMBERSHIPPROGRAM on MEMBERSHIP.MEMBERSHIPPROGRAMID = MEMBERSHIPPROGRAM.ID
                where
                    MEMBER.ID=@ID;