USP_DATAFORMTEMPLATE_ADD_MEMBERSHIPREJOIN

The save procedure used by the add dataform template "Membership Rejoin Add Data Form".

Parameters

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

Definition

Copy



CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_MEMBERSHIPREJOIN
(
    @ID uniqueidentifier = null output,
    @CURRENTCONSTITUENTID uniqueidentifier = null,
    @MEMBERID uniqueidentifier,
    @MEMBERSHIPID uniqueidentifier = null,
    @CHANGEAGENTID uniqueidentifier = null,
    @MEMBERSHIPLEVELID uniqueidentifier = null,
    @MEMBERSHIPLEVELTERMID uniqueidentifier = null,
    @MEMBERSHIPTYPECODEID uniqueidentifier = null,
    @STATUSCODE smallint = 0,
    @TRANSACTIONDATE datetime = null,
    @NUMBEROFCHILDREN smallint = 0,
    @COMMENTS nvarchar(1000) = null,
    @ISGIFT bit = 0,
    @SENDRENEWALCODE smallint = 1,
    @EXPIRATIONDATE datetime = null,
    @MEMBERS xml = null,
    @GIVENBYID uniqueidentifier = null
)
as

    set nocount on;

    begin try
        declare @MEMBERSHIPPROGRAMID uniqueidentifier;
        declare @ALLOWMULTIPLEMEMBERSHIPS bit;
        declare @PREVIOUSSTATUSCODE tinyint;

        select @CURRENTCONSTITUENTID = MEMBER.CONSTITUENTID,
            @MEMBERSHIPID = MEMBER.MEMBERSHIPID,
            @ALLOWMULTIPLEMEMBERSHIPS = MEMBERSHIPPROGRAM.ALLOWMULTIPLEMEMBERSHIPS,
            @PREVIOUSSTATUSCODE = MEMBERSHIP.STATUSCODE,
            @MEMBERSHIPPROGRAMID = MEMBERSHIP.MEMBERSHIPPROGRAMID
        from dbo.MEMBER
        inner join dbo.MEMBERSHIP
            on MEMBERSHIP.ID = MEMBER.MEMBERSHIPID
        inner join dbo.MEMBERSHIPPROGRAM
            on MEMBERSHIPPROGRAM.ID = MEMBERSHIP.MEMBERSHIPPROGRAMID
        where @MEMBERID = MEMBER.ID;

        -- Do not allow backdated transaction on rejoin

        if exists (select * from dbo.MEMBERSHIPTRANSACTION 
                    where MEMBERSHIPID = @MEMBERSHIPID and 
                        TRANSACTIONDATE > @TRANSACTIONDATE)
            raiserror('ERR_TRANSACTIONDATE_ISBACKDATEDTRANSACTION', 13,1);

        -- Bug #164762    

        -- Multiple memberships validation

        if @ALLOWMULTIPLEMEMBERSHIPS = 0 and @PREVIOUSSTATUSCODE = 1
        begin
            if exists
            (
                select 1 
                from dbo.MEMBER 
                inner join dbo.MEMBERSHIP
                on MEMBER.MEMBERSHIPID = MEMBERSHIP.ID
                where
                    MEMBERSHIPPROGRAMID = @MEMBERSHIPPROGRAMID 
                    and STATUSCODE <> 1
                    and MEMBER.ISDROPPED <> 1
                    and MEMBER.CONSTITUENTID in 
                    (
                        select
                            T.members.value('(CONSTITUENTID)[1]','uniqueidentifier') as 'CONSTITUENTID'
                        from 
                            @MEMBERS.nodes('/MEMBERS/ITEM') T(members)
                    )
            )
            begin
                raiserror('ERR_MEMBER_MULTIPLEMEMBERSHIPS', 13, 1);
            end
        end


        exec dbo.USP_MEMBERSHIP_ADD
                    @MEMBERSHIPID,
                    @CHANGEAGENTID,
                    @CURRENTCONSTITUENTID,
                    null,
                    @MEMBERSHIPLEVELID,
                    @MEMBERSHIPLEVELTERMID,
                    @MEMBERSHIPTYPECODEID,
                    @STATUSCODE,
                    @TRANSACTIONDATE,
                    @NUMBEROFCHILDREN,
                    @COMMENTS,
                    @ISGIFT,
                    @SENDRENEWALCODE,
                    @EXPIRATIONDATE,
                    @MEMBERS,
                    @GIVENBYID,
                    null;

    end try
    begin catch
        exec dbo.USP_RAISE_ERROR
        return 1
    end catch

    return 0