USP_DATAFORMTEMPLATE_EDIT_MEMBERSHIPLEVELRENEWALRULES

The save procedure used by the edit dataform template "Membership Level Renewal Rules Edit Form".
Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	IN	The input ID parameter indicating the ID of the record being edited.
@CHANGEAGENTID	uniqueidentifier	IN	Input parameter indicating the ID of the change agent invoking the procedure.
@BEFOREEXPIRATION	tinyint	IN	month(s) before expiration
@AFTEREXPIRATION	tinyint	IN	month(s) after expiration
@FORCEMANUALDOWNGRADES	bit	IN	Members must be downgraded manually
Definition

Copy


                CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_MEMBERSHIPLEVELRENEWALRULES
                (
                @ID uniqueidentifier,
                @CHANGEAGENTID uniqueidentifier,
                @BEFOREEXPIRATION tinyint,
                @AFTEREXPIRATION tinyint,
                @FORCEMANUALDOWNGRADES bit
                )
                as set nocount on;

                if @CHANGEAGENTID is null  
                        exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output


                declare @CURRENTDATE datetime
                set @CURRENTDATE = getdate()

             begin try
                declare @OLDBEFOREEXPIRATION tinyint
                declare @OLDAFTEREXPIRATION tinyint

                select @OLDBEFOREEXPIRATION = BEFOREEXPIRATION, @OLDAFTEREXPIRATION = AFTEREXPIRATION
                from dbo.MEMBERSHIPLEVEL
                where ID = @ID

                update dbo.MEMBERSHIPLEVEL set
                    BEFOREEXPIRATION=@BEFOREEXPIRATION,
                    AFTEREXPIRATION=@AFTEREXPIRATION,
                    FORCEMANUALDOWNGRADES=@FORCEMANUALDOWNGRADES,
                    CHANGEDBYID= @CHANGEAGENTID,  
                    DATECHANGED=@CURRENTDATE
               where ID = @ID;

               -- Save renewal rule history

               if @OLDBEFOREEXPIRATION <> @BEFOREEXPIRATION or @OLDAFTEREXPIRATION <> @AFTEREXPIRATION
               begin
                   -- if there is already a renewal rule history record has the current date as start date

                   -- update it with the new before and after the expiration values

                   -- if not insert a new record

                   if exists (select 1 from dbo.MEMBERSHIPLEVELRENEWALRULES 
                                where MEMBERSHIPLEVELID = @ID and 
                                    STARTDATE = dbo.UFN_DATE_GETEARLIESTTIME(@CURRENTDATE))
                        update dbo.MEMBERSHIPLEVELRENEWALRULES
                        set BEFOREEXPIRATION = @BEFOREEXPIRATION,
                            AFTEREXPIRATION = @AFTEREXPIRATION,
                            ENDDATE = null,
                            CHANGEDBYID= @CHANGEAGENTID,  
                            DATECHANGED=@CURRENTDATE
                        where MEMBERSHIPLEVELID = @ID and STARTDATE = dbo.UFN_DATE_GETEARLIESTTIME(@CURRENTDATE)
                   else
                   begin
                       update dbo.MEMBERSHIPLEVELRENEWALRULES
                       set ENDDATE = @CURRENTDATE,
                            CHANGEDBYID= @CHANGEAGENTID,  
                            DATECHANGED=@CURRENTDATE
                       where MEMBERSHIPLEVELID = @ID and ENDDATE is null

                        insert into dbo.MEMBERSHIPLEVELRENEWALRULES
                        (
                            MEMBERSHIPLEVELID,
                            BEFOREEXPIRATION,
                            AFTEREXPIRATION,
                            STARTDATE,
                            ADDEDBYID, 
                            CHANGEDBYID, 
                            DATEADDED, 
                            DATECHANGED
                        )
                        values
                        (
                            @ID,
                            @BEFOREEXPIRATION,
                            @AFTEREXPIRATION,
                            @CURRENTDATE,
                            @CHANGEAGENTID, 
                            @CHANGEAGENTID, 
                            @CURRENTDATE, 
                            @CURRENTDATE
                        )
                    end
                end
            end try
            begin catch
                exec dbo.USP_RAISE_ERROR
                return 1
            end catch

            return 0;