USP_MEMBERSHIPTRANSACTION_DELETE

                    CREATE procedure dbo.USP_MEMBERSHIPTRANSACTION_DELETE
                    (
                        @ID uniqueidentifier,
                        @CHANGEAGENTID uniqueidentifier
                    )
                    as begin
                        begin try
                            declare @CURRENTDATE datetime
                            set @CURRENTDATE = getdate()

                            declare @MEMBERSHIPID uniqueidentifier
                            declare @ACTIONCODE tinyint
                            declare @TRANSACTIONDATE datetime
                            declare @DATEADDED datetime
                            declare @DELETINGCURRENTTRANSACTION bit

                            select @MEMBERSHIPID = MEMBERSHIPID, 
                                    @ACTIONCODE = ACTIONCODE,
                                    @TRANSACTIONDATE = TRANSACTIONDATE,
                                    @DATEADDED = DATEADDED
                            from dbo.MEMBERSHIPTRANSACTION 
                            where ID = @ID

                            select top 1 @DELETINGCURRENTTRANSACTION = case ID when @ID then 1 else 0 end 
                            from dbo.MEMBERSHIPTRANSACTION 
                            where MEMBERSHIPID = @MEMBERSHIPID 
                            order by TRANSACTIONDATE desc,DATEADDED desc

                            --don't delete if attached to revenue

                            if (select REVENUESPLITID from dbo.MEMBERSHIPTRANSACTION where ID = @ID) is not null
                              begin
                                  raiserror('This membership transaction is attached to revenue and cannot be deleted.', 13, 1);
                                  return 1;
                              end    

                            --If this is the current cancel transaction, see if there are any commitments associated to the membership. If there is a recurring gift, it needs to be

                            --  set back to a status of 'Active'. If there is an installment plan, then we need to get the associated write-off and delete it, if one exists.

                            if @DELETINGCURRENTTRANSACTION = 1 and @ACTIONCODE = 4
                            begin
                                declare @RGID uniqueidentifier = (select top 1 FINANCIALTRANSACTION.ID
                                                                    from dbo.MEMBERSHIPTRANSACTION
                                                                    inner join dbo.MEMBERSHIP on MEMBERSHIPTRANSACTION.MEMBERSHIPID = MEMBERSHIP.ID
                                                                    inner join dbo.FINANCIALTRANSACTIONLINEITEM on MEMBERSHIPTRANSACTION.REVENUESPLITID = FINANCIALTRANSACTIONLINEITEM.ID
                                                                    inner join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = FINANCIALTRANSACTION.ID
                                                                    inner join REVENUESCHEDULE on FINANCIALTRANSACTION.ID = REVENUESCHEDULE.ID
                                                                    where MEMBERSHIPTRANSACTION.MEMBERSHIPID = (select MEMBERSHIPID from dbo.MEMBERSHIPTRANSACTION where ID = @ID)
                                                                        and FINANCIALTRANSACTION.TYPECODE = 2
                                                                        and MEMBERSHIPTRANSACTION.TRANSACTIONDATE >= MEMBERSHIP.JOINDATE
                                                                        order by MEMBERSHIPTRANSACTION.TRANSACTIONDATE desc, MEMBERSHIPTRANSACTION.DATEADDED desc);
                                declare @IPID uniqueidentifier = (select dbo.UFN_MEMBERSHIP_GETPLEDGE(MEMBERSHIPID) from dbo.MEMBERSHIPTRANSACTION where ID = @ID);

                                if @RGID is not null
                                begin                                    
                                    exec dbo.USP_RECURRINGGIFT_EDITSTATUS @RGID, 0, @CHANGEAGENTID, @CURRENTDATE, null, null, null, 14;
                                end
                                else if @IPID is not null
                                begin
                                    declare @WRITEOFFID uniqueidentifier;
                                    select top 1 @WRITEOFFID = ID 
                                        from dbo.WRITEOFF 
                                        where REVENUEID = @IPID and DATEADDED = (select DATEADDED from dbo.MEMBERSHIPTRANSACTION where ID = @ID)

                                    if @WRITEOFFID is not null
                                    begin
                                        delete from dbo.WRITEOFF where ID = @WRITEOFFID
                                    end
                                end
                            end

                            --don't delete if this is a join transaction

                            if @ACTIONCODE = 0
                              begin
                                  raiserror('The join membership transaction cannot be deleted.', 13, 1);
                                  return 1;
                              end        

                            declare @EXPIRATIONDATE date
                            select @EXPIRATIONDATE = [EXPIRATIONDATE] from dbo.MEMBERSHIPTRANSACTION where ID = @ID

              delete from dbo.MEMBERSHIPPROGRAMCONTRIBUTIONREVENUE
              where ORIGINALMEMBERSHIPTRANSACTIONID = @ID or CURRENTMEMBERSHIPTRANSACTIONID = @ID

                            exec USP_MEMBERSHIPTRANSACTION_DELETEBYID_WITHCHANGEAGENTID @ID, @CHANGEAGENTID

                            if @DELETINGCURRENTTRANSACTION = 1 
                              --Try to roll back the membership record to the state as of the previous transaction.

                                exec dbo.USP_MEMBERSHIP_RESETFROMLASTTRANSACTION @MEMBERSHIPID, @EXPIRATIONDATE, @CHANGEAGENTID
                            else
                            begin
                                -- Reset the action code of the next transaction

                                declare @PREVEXPRDATE datetime,
                                        @PREVLEVELID uniqueidentifier,
                                        @PREVACTIONCODE tinyint,
                                        @NEXTLEVELID uniqueidentifier,
                                        @NEXTTRANSDATE datetime,
                                        @NEXTTRANSID uniqueidentifier,
                                        @NEXTACTIONCODE tinyint;

                                select top 1
                                    @PREVEXPRDATE = EXPIRATIONDATE,
                                    @PREVLEVELID = MEMBERSHIPLEVELID,
                                    @PREVACTIONCODE = ACTIONCODE
                                from
                                    dbo.MEMBERSHIPTRANSACTION
                                where
                                    MEMBERSHIPID = @MEMBERSHIPID
                                    and (
                                        TRANSACTIONDATE < @TRANSACTIONDATE
                                        or (
                                            TRANSACTIONDATE = @TRANSACTIONDATE
                                            and DATEADDED < @DATEADDED
                                        )
                                    )
                                order by
                                    TRANSACTIONDATE desc, DATEADDED desc;

                                select top 1
                                    @NEXTTRANSDATE = TRANSACTIONDATE,
                                    @NEXTLEVELID = MEMBERSHIPLEVELID,
                                    @NEXTACTIONCODE = ACTIONCODE,
                                    @NEXTTRANSID = ID
                                from
                                    dbo.MEMBERSHIPTRANSACTION
                                where
                                    MEMBERSHIPID = @MEMBERSHIPID
                                    and (
                                        TRANSACTIONDATE > @TRANSACTIONDATE
                                        or (
                                            TRANSACTIONDATE = @TRANSACTIONDATE
                                            and DATEADDED > @DATEADDED
                                        )
                                    )
                                order by
                                    TRANSACTIONDATE asc, DATEADDED asc;

                                set @NEXTACTIONCODE = case @NEXTACTIONCODE
                                        when 4 then 4
                                        else
                                            dbo.UFN_MEMBERSHIPTRANSACTION_DETERMINENEWACTIONCODE(@PREVLEVELID,
                                                                                        @PREVEXPRDATE,
                                                                                        @PREVACTIONCODE,
                                                                                        @NEXTLEVELID,
                                                                                        @NEXTTRANSDATE)
                                        end

                                update dbo.MEMBERSHIPTRANSACTION set
                                    ACTIONCODE = @NEXTACTIONCODE,
                                    CHANGEDBYID = @CHANGEAGENTID,
                                    DATECHANGED = @CURRENTDATE
                                where ID = @NEXTTRANSID;

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