UFN_MEMBERSHIPTRANSACTION_DETERMINENEWACTIONCODE

Determines the action code of a membership transaction.

Return

Return Type
tinyint

Parameters

Parameter	Parameter Type	Mode
@PREVIOUSMEMBERSHIPLEVELID	uniqueidentifier	IN
@PREVIOUSEXPIRATIONDATE	datetime	IN
@PREVIOUSACTIONCODE	tinyint	IN
@MEMBERSHIPLEVELID	uniqueidentifier	IN
@TRANSACTIONDATE	datetime	IN

Definition

Copy



            CREATE function dbo.UFN_MEMBERSHIPTRANSACTION_DETERMINENEWACTIONCODE
            (
                @PREVIOUSMEMBERSHIPLEVELID uniqueidentifier,
                @PREVIOUSEXPIRATIONDATE datetime,
                @PREVIOUSACTIONCODE tinyint,
                @MEMBERSHIPLEVELID uniqueidentifier,
                @TRANSACTIONDATE datetime
            )
            returns tinyint with execute as caller
            as
            begin
                --if there is no transaction before, this is a join

                if @PREVIOUSMEMBERSHIPLEVELID is null
                    return 0;

                --if the previous action is dropped, this is a rejoin

                if @PREVIOUSACTIONCODE = 4
                    return 5;                

                declare @ACTIONCODE tinyint
                declare @ENDEXPDATE datetime

                set @ENDEXPDATE = dbo.UFN_MEMBERSHIPLEVEL_CREATERENEWALAFTEREXPIRATIONDATE_2(@PREVIOUSMEMBERSHIPLEVELID, @PREVIOUSEXPIRATIONDATE, @TRANSACTIONDATE)

                --if the membership is past expiration window, this is a rejoin

                if @TRANSACTIONDATE > @ENDEXPDATE
                begin
                    return (select case when MEMBERSHIPLEVEL.RENEWALWINDOWREVENUETYPECODE = 1 then 0 else 5 end
                                from dbo.MEMBERSHIPLEVEL
                                where MEMBERSHIPLEVEL.ID = @PREVIOUSMEMBERSHIPLEVELID)
                end

                if @PREVIOUSMEMBERSHIPLEVELID = @MEMBERSHIPLEVELID
                    set @ACTIONCODE = 1
                else
                    begin
                        declare @NEWSEQUENCE integer

                        select @NEWSEQUENCE = SEQUENCE
                        from dbo.MEMBERSHIPLEVEL
                        where MEMBERSHIPLEVEL.ID = @MEMBERSHIPLEVELID

                        declare @LEVELSEQUENCE integer
                        select @LEVELSEQUENCE = SEQUENCE
                        from dbo.MEMBERSHIPLEVEL
                        where @PREVIOUSMEMBERSHIPLEVELID = ID

                        if @NEWSEQUENCE > @LEVELSEQUENCE
                            set @ACTIONCODE = 2
                        else
                            set @ACTIONCODE = 3
                    end

                return @ACTIONCODE
            end