USP_MEMBERSHIP_RESETFROMLASTTRANSACTION

            CREATE procedure dbo.USP_MEMBERSHIP_RESETFROMLASTTRANSACTION
            (
                @MEMBERSHIPID uniqueidentifier,
                 --Date of membership cards in which to reset.  

                 --Usually, this is the expiration date of the membership transaction that was deleted prior to executing this sp.

                 --The idea is that if the cards had been set to expire on the same date as the membership, then that should probably still hold

                @RESETMEMBERSHIPCARDDATE date = null,
                @CHANGEAGENTID uniqueidentifier
            )
            as
            begin
                if @CHANGEAGENTID is null 
                    exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

                declare @CURRENTDATE datetime = getdate();
                declare @LATESTTRANSACTIONID uniqueidentifier = dbo.UFN_MEMBERSHIP_GETLASTMEMBERSHIPTRANSACTIONID(@MEMBERSHIPID);

                declare @MEMBERSHIPSTATUSCODE tinyint;
                select @MEMBERSHIPSTATUSCODE = STATUSCODE from dbo.UFN_MEMBERSHIPTRANSACTION_CALCULATEMEMBERSHIPSTATUSCODE(@LATESTTRANSACTIONID, @CURRENTDATE);

                update dbo.MEMBERSHIP 
                set 
                    MEMBERSHIP.MEMBERSHIPLEVELID = MEMBERSHIPTRANSACTION.MEMBERSHIPLEVELID,
                    MEMBERSHIP.MEMBERSHIPLEVELTERMID = MEMBERSHIPTRANSACTION.MEMBERSHIPLEVELTERMID, 
                    MEMBERSHIP.MEMBERSHIPLEVELTYPECODEID = MEMBERSHIPTRANSACTION.MEMBERSHIPLEVELTYPECODEID,
                    MEMBERSHIP.NUMBEROFCHILDREN = MEMBERSHIPTRANSACTION.NUMBEROFCHILDREN,
                    MEMBERSHIP.COMMENTS = MEMBERSHIPTRANSACTION.COMMENTS,
                    MEMBERSHIP.ISGIFT = MEMBERSHIPTRANSACTION.ISGIFT,
                    MEMBERSHIP.EXPIRATIONDATE = MEMBERSHIPTRANSACTION.EXPIRATIONDATE, 
                    MEMBERSHIP.STATUSCODE = @MEMBERSHIPSTATUSCODE,
                    MEMBERSHIP.GIVENBYID = MEMBERSHIPTRANSACTION.DONORID,
                    MEMBERSHIP.DATECHANGED = @CURRENTDATE,
                    MEMBERSHIP.CHANGEDBYID =  @CHANGEAGENTID
                from dbo.MEMBERSHIPTRANSACTION
                where 
                    MEMBERSHIPTRANSACTION.ID = @LATESTTRANSACTIONID
                    and MEMBERSHIP.ID = @MEMBERSHIPID

                update dbo.MEMBERSHIPCARD
                set
                    MEMBERSHIPCARD.EXPIRATIONDATE = MEMBERSHIPTRANSACTION.EXPIRATIONDATE,
                    MEMBERSHIPCARD.DATECHANGED = @CURRENTDATE,
                    MEMBERSHIPCARD.CHANGEDBYID =  @CHANGEAGENTID
                from dbo.MEMBERSHIPCARD
                inner join dbo.MEMBER
                    on MEMBERSHIPCARD.MEMBERID = MEMBER.ID
                inner join dbo.MEMBERSHIP
                    on MEMBER.MEMBERSHIPID = MEMBERSHIP.ID
                inner join dbo.MEMBERSHIPTRANSACTION
                    on MEMBERSHIP.ID = MEMBERSHIPTRANSACTION.MEMBERSHIPID
                where 
                    MEMBERSHIPTRANSACTION.ID = dbo.UFN_MEMBERSHIP_GETLASTMEMBERSHIPTRANSACTIONID([MEMBERSHIP].[ID]) and
                    MEMBERSHIP.ID = @MEMBERSHIPID and
                    cast(MEMBERSHIPCARD.EXPIRATIONDATE as date) = @RESETMEMBERSHIPCARDDATE

        --Drop additional members by date added if available member slots have changed

        declare @MEMBERSTODROP dbo.UDT_GENERICID;

            with CTE_ADDONINFO as
            (
                select
                    (
                        -- Number of members on the associated transaction

                        select
                            count(MEMBER.ID)
                        from dbo.MEMBERSHIPTRANSACTION
                        inner join dbo.MEMBERSHIP on MEMBERSHIP.ID = MEMBERSHIPTRANSACTION.MEMBERSHIPID
                        inner join dbo.MEMBER on MEMBER.MEMBERSHIPID = MEMBERSHIP.ID
                        where
                            MEMBERSHIPTRANSACTION.ID = @LATESTTRANSACTIONID
                            and MEMBER.ISDROPPED = 0
                    ) -
                    (
                        -- Number of members allowed, based on the current membership level and the add-ons remaining

                        select
                MEMBERSHIPLEVEL.MEMBERSALLOWED + isnull(sum(MEMBERSHIPADDON.QUANTITY) - sum(MEMBERSHIPADDON.NUMCANCELLED), 0)
              from dbo.MEMBERSHIPTRANSACTION
              inner join dbo.MEMBERSHIP on MEMBERSHIP.ID = MEMBERSHIPTRANSACTION.MEMBERSHIPID
              inner join dbo.MEMBERSHIPLEVEL on MEMBERSHIPLEVEL.ID = MEMBERSHIP.MEMBERSHIPLEVELID
              left outer join dbo.MEMBERSHIPADDON on MEMBERSHIPADDON.MEMBERSHIPTRANSACTIONID = MEMBERSHIPTRANSACTION.ID
                left outer join dbo.ADDON on ADDON.ID = MEMBERSHIPADDON.ADDONID
              where
                  MEMBERSHIPTRANSACTION.ID = @LATESTTRANSACTIONID
                  and (ADDON.ADDONTYPECODE = 1 or ADDON.ADDONTYPECODE is null)
              group by MEMBERSHIPLEVEL.MEMBERSALLOWED

                    ) as NUMBEROFMEMBERSTODROP
            ),
            CTE_MEMBERINFO as
            (
                select
                    MEMBER.ID as MEMBERID,
                    row_number() over (partition by MEMBERSHIPTRANSACTION.ID order by MEMBER.DATEADDED desc) as ROWNUMBER
                from dbo.MEMBERSHIPTRANSACTION 
                inner join dbo.MEMBER on MEMBER.MEMBERSHIPID = MEMBERSHIPTRANSACTION.MEMBERSHIPID
                where MEMBERSHIPTRANSACTION.ID = @LATESTTRANSACTIONID
                    and MEMBER.ISPRIMARY = 0
                    and MEMBER.ISDROPPED = 0
            )
            insert into @MEMBERSTODROP (ID)
            select MEMBERID
            from CTE_MEMBERINFO
            where ROWNUMBER <= (select NUMBEROFMEMBERSTODROP from CTE_ADDONINFO);

            update dbo.MEMBER set
                ISDROPPED = 1,
                CHANGEDBYID = @CHANGEAGENTID,
                DATECHANGED = @CURRENTDATE
            where
                ID in (select ID from @MEMBERSTODROP);

            update dbo.MEMBERSHIPCARD set
                STATUSCODE = 2,  -- Cancelled

                CHANGEDBYID = @CHANGEAGENTID,
                DATECHANGED = @CURRENTDATE
            where
                MEMBERID in (select ID from @MEMBERSTODROP)
                and STATUSCODE <> 2;  -- Cancelled


            end