UFN_MEMBERSHIPTRANSACTION_DETERMINEACTIONCODE

            CREATE function dbo.UFN_MEMBERSHIPTRANSACTION_DETERMINEACTIONCODE
            (
                @MEMBERSHIPID uniqueidentifier,
                @NEWMEMBERSHIPLEVELID uniqueidentifier,
                @TRANSACTIONDATE datetime
            )
            returns tinyint with execute as caller
            as
            begin
                --if there is no membership, this is a join

                if not exists (select ID from dbo.MEMBERSHIP where ID = @MEMBERSHIPID) 
                    return 0;

                --if the membership is dropped, this is a rejoin

                if exists (select ID from dbo.MEMBERSHIP where ID = @MEMBERSHIPID and STATUSCODE = 1)
                    return 5;                

                declare @ACTIONCODE tinyint
                declare @PRIORLEVELID uniqueidentifier
                declare @ENDEXPDATE datetime

                select @PRIORLEVELID = MEMBERSHIPLEVELID,
                    @ENDEXPDATE = dbo.UFN_MEMBERSHIPLEVEL_CREATERENEWALAFTEREXPIRATIONDATE_2(MEMBERSHIPLEVELID, MEMBERSHIP.EXPIRATIONDATE,@TRANSACTIONDATE)
                from dbo.MEMBERSHIP
                where ID = @MEMBERSHIPID

                --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
                                inner join dbo.MEMBERSHIP on MEMBERSHIPLEVEL.ID = MEMBERSHIP.MEMBERSHIPLEVELID
                                where MEMBERSHIP.ID = @MEMBERSHIPID)
                end

                if @PRIORLEVELID = @NEWMEMBERSHIPLEVELID
                    set @ACTIONCODE = 1
                else
                    begin
                        declare @NEWSEQUENCE integer

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

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

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

                return @ACTIONCODE
            end