USP_DATAFORMTEMPLATE_VIEW_CONSTITUTIONMEMBERSHIP

The load procedure used by the view dataform template "Constituent Membership 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.
@MEMBERSHIPID	nvarchar(25)	INOUT	Member ID
@JOINDATE	datetime	INOUT	Member since
@TERM	nvarchar(8)	INOUT	Term
@STATUS	nvarchar(9)	INOUT	Status
@LASTRENEWEDON	datetime	INOUT	Last renewed
@COMMENTS	nvarchar(1000)	INOUT	Comments
@EXPIRATIONDATE	datetime	INOUT	Expiration date
@TYPE	nvarchar(100)	INOUT	Type
@NUMBEROFCHILDREN	smallint	INOUT	Children
@NUMBERMEMBERS	smallint	INOUT	Members
@OTHERMEMBERS	nvarchar(1000)	INOUT	Other members

Definition

Copy



            CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_CONSTITUTIONMEMBERSHIP
            (
                @ID uniqueidentifier,
                @DATALOADED bit=0 output,
                @MEMBERSHIPID nvarchar(25)=null output,
                @JOINDATE datetime=null output,
                @TERM nvarchar(8)=null output,
                @STATUS nvarchar(9)=null output,
                @LASTRENEWEDON datetime= null output,
                @COMMENTS nvarchar(1000) = null output,
                @EXPIRATIONDATE datetime= null output,
                @TYPE nvarchar(100) = null output,
                @NUMBEROFCHILDREN smallint = null output,
                @NUMBERMEMBERS smallint = null output,
                @OTHERMEMBERS nvarchar(1000) = null output
            )
            as 
            set nocount on;
                declare @TODAY datetime;        
                set @TODAY = getdate();


                set @DATALOADED = 0;

                select 
            @DATALOADED = 1,
            @MEMBERSHIPID = MEMBERSHIP.LOOKUPID,
            @JOINDATE=MEMBERSHIP.JOINDATE,
            @TERM=LEVELTERM.TERM,
            @STATUS = case 
                        when MEMBERSHIP.STATUSCODE = 0 and MEMBERSHIP.EXPIRATIONDATE < @TODAY then 'Lapsed'
                        else MEMBERSHIP.STATUS
                        end,
                    @LASTRENEWEDON=MEMBERSHIP.LASTRENEWEDON,
                    @COMMENTS=MEMBERSHIP.COMMENTS,
                    @EXPIRATIONDATE=MEMBERSHIP.EXPIRATIONDATE,
                    @TYPE=TYPECODE.DESCRIPTION,
                    @NUMBEROFCHILDREN=MEMBERSHIP.NUMBEROFCHILDREN,
                    @NUMBERMEMBERS=dbo.UFN_CONSTITUENTMEMBERSHIP_GETCOUNT(@ID),
                    @OTHERMEMBERS=dbo.UFN_CONSTITUENTMEMBERSHIP_GETOTHERMEMBERS(@ID)
                from dbo.MEMBERSHIP
                inner join dbo.MEMBERSHIPLEVELTERM AS LEVELTERM on 
                    MEMBERSHIP.MEMBERSHIPLEVELTERMID=LEVELTERM.ID
                inner join dbo.MEMBERSHIPLEVEL as LEVEL on
                    MEMBERSHIP.MEMBERSHIPLEVELID = LEVEL.ID
                left join dbo.MEMBERSHIPLEVELTYPECODE as TYPECODE on                 
                    MEMBERSHIP.MEMBERSHIPLEVELTYPECODEID=TYPECODE.ID        
                inner join dbo.MEMBER on
                    MEMBER.MEMBERSHIPID=MEMBERSHIP.ID 
                where
                    MEMBER.ID=@ID and MEMBER.ISDROPPED = 0;