USP_DATAFORMTEMPLATE_ADD_MEMBERSHIPREJOIN_PRELOAD

The load procedure used by the edit dataform template "Membership Rejoin Add Data Form"

Parameters

Parameter	Parameter Type	Mode	Description
@MEMBERSHIPID	uniqueidentifier	INOUT
@MEMBERID	uniqueidentifier	IN	Input parameter indicating the context ID for the record being added.
@MEMBERSHIPPROGRAMID	uniqueidentifier	INOUT	Program
@MEMBERSHIPLEVELID	uniqueidentifier	INOUT	Level
@MEMBERSHIPLEVELTERMID	uniqueidentifier	INOUT	Term
@TRANSACTIONDATE	datetime	INOUT	Transaction date
@EXPIRATIONDATE	datetime	INOUT	Expiration date
@MEMBERSHIPTYPECODEID	uniqueidentifier	INOUT	Type
@NUMBEROFCHILDREN	smallint	INOUT	No. of children
@COMMENTS	nvarchar(1000)	INOUT	Comments
@MEMBERS	xml	INOUT	Members
@ISGIFT	bit	INOUT	This membership is a gift
@SENDRENEWALCODE	tinyint	INOUT	Send renewal notice to
@CURRENTCONSTITUENTID	uniqueidentifier	INOUT
@GIVENBYID	uniqueidentifier	INOUT	Given By
@ISREJOIN	bit	INOUT

Definition

Copy



CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_MEMBERSHIPREJOIN_PRELOAD
(
    @MEMBERSHIPID uniqueidentifier = null output,
    @MEMBERID uniqueidentifier,
    @MEMBERSHIPPROGRAMID uniqueidentifier = null output,
    @MEMBERSHIPLEVELID uniqueidentifier = null output,
    @MEMBERSHIPLEVELTERMID uniqueidentifier = null output,
    @TRANSACTIONDATE datetime = null output,
    @EXPIRATIONDATE datetime = null output,
    @MEMBERSHIPTYPECODEID uniqueidentifier = null output,
    @NUMBEROFCHILDREN smallint = null output,
    @COMMENTS nvarchar(1000) = null output,
    @MEMBERS xml = null output,
    @ISGIFT bit = null output,
    @SENDRENEWALCODE tinyint = null output,
    @CURRENTCONSTITUENTID uniqueidentifier = null output,
    @GIVENBYID uniqueidentifier = null output,
    @ISREJOIN bit = null output

)
as
    set nocount on;

    select @CURRENTCONSTITUENTID = CONSTITUENTID,
        @MEMBERSHIPID = MEMBERSHIPID
    from dbo.MEMBER
    where @MEMBERID = ID 

    select 
        @MEMBERSHIPPROGRAMID = MEMBERSHIPPROGRAMID,
        @MEMBERSHIPLEVELID = MEMBERSHIPLEVELID,
        @MEMBERSHIPLEVELTERMID = MEMBERSHIPLEVELTERMID,
        @TRANSACTIONDATE = dbo.UFN_DATE_GETEARLIESTTIME(getdate()),
        @EXPIRATIONDATE = dbo.UFN_MEMBERSHIP_CALCULATEEXPIRATIONDATEBYLEVEL(MEMBERSHIPLEVELID, MEMBERSHIPLEVELTERMID, getdate()),
        @MEMBERSHIPTYPECODEID = MEMBERSHIPLEVELTYPECODEID,
        @NUMBEROFCHILDREN = NUMBEROFCHILDREN,
        @COMMENTS = COMMENTS,
        @ISGIFT = ISGIFT,
        @SENDRENEWALCODE = SENDRENEWALCODE,
        @GIVENBYID = GIVENBYID
    from 
        dbo.MEMBERSHIP
    where 
        ID = @MEMBERSHIPID;

        declare @DATE datetime
        set @DATE = getdate();

        declare @ID uniqueidentifier
        set @ID = newid();

        declare @CARDID uniqueidentifier;
        set @CARDID = newid();

    set @MEMBERS =
    (
        select
            M.ID,
            M.CONSTITUENTID,
            M.ISPRIMARY,
            (
                select
                    MC.ID,
                    MC.NAMEONCARD,
                    MC.EXPIRATIONDATE
                from
                    dbo.MEMBERSHIPCARD MC
                where
                    MC.MEMBERID = M.ID and MC.STATUSCODE <> 2
                for xml raw('ITEM'),type,elements,root('MEMBERSHIPCARDS'),BINARY BASE64
            )
            from
                dbo.MEMBER M
            where
                MEMBERSHIPID = @MEMBERSHIPID and ISDROPPED = 0
            for xml raw ('ITEM'), type, elements, root('MEMBERS'),BINARY BASE64
    )

    set @ISREJOIN = 1;

    return 0;