USP_DATAFORMTEMPLATE_EDIT_CONSTITUENTRECOGNITIONDECLINEDLEVELSBYLEVEL

        CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_CONSTITUENTRECOGNITIONDECLINEDLEVELSBYLEVEL (
            @ID uniqueidentifier,
            @CHANGEAGENTID uniqueidentifier = null,
            @RECOGNITIONPROGRAMID uniqueidentifier,
            @DECLINEPROGRAM bit,
            @DECLINEDRECOGNITIONLEVELS xml,
          @REASONCODE uniqueidentifier
        )
        as

            set nocount on;

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

            declare @CURRENTDATE datetime
            set @CURRENTDATE = getdate()

          declare @CONSTITUENTID uniqueidentifier
          select @CONSTITUENTID = CONSTITUENTID
          from dbo.CONSTITUENTRECOGNITIONDECLINEDLEVEL 
          where ID = @ID 

         select @CONSTITUENTID = CONSTITUENTID,
                 @RECOGNITIONPROGRAMID = RECOGNITIONPROGRAMID
          from dbo.CONSTITUENTRECOGNITIONDECLINEDPROGRAM
          where ID = @ID 

          declare @ROWEXISTS bit = 0
          if exists(select ID from dbo.CONSTITUENTRECOGNITIONDECLINEDPROGRAM CRDP where CRDP.CONSTITUENTID = @CONSTITUENTID and CRDP.RECOGNITIONPROGRAMID = @RECOGNITIONPROGRAMID)
            set @ROWEXISTS = 1


          if @DECLINEPROGRAM = 1 and @ROWEXISTS = 0
          begin
            insert into dbo.CONSTITUENTRECOGNITIONDECLINEDPROGRAM (CONSTITUENTID,RECOGNITIONPROGRAMID,ADDEDBYID,CHANGEDBYID,DATEADDED,DATECHANGED) 
            values
            (@CONSTITUENTID,@RECOGNITIONPROGRAMID,@CHANGEAGENTID,@CHANGEAGENTID,@CURRENTDATE,@CURRENTDATE) 

             --Delete their entries in the CONSTITUENTRECOGNITION table since they are now declined
             declare @CRID uniqueidentifier
             declare DELETE_CURSOR cursor for
             select ID from dbo.CONSTITUENTRECOGNITION where CONSTITUENTID = @CONSTITUENTID AND RECOGNITIONPROGRAMID = @RECOGNITIONPROGRAMID
             open DELETE_CURSOR
             fetch next from DELETE_CURSOR into @CRID
                   while @@FETCH_STATUS = 0
                   begin
                    exec USP_CONSTITUENTRECOGNITION_DELETE @CRID, @CHANGEAGENTID
                    fetch next from DELETE_CURSOR into @CRID
                    end
             close DELETE_CURSOR
             deallocate DELETE_CURSOR

            delete from dbo.CONSTITUENTRECOGNITIONDECLINEDLEVEL where CONSTITUENTID = @CONSTITUENTID and RECOGNITIONPROGRAMID = @RECOGNITIONPROGRAMID
          end

          if @DECLINEPROGRAM = 0 and @ROWEXISTS = 1
          begin
            delete from dbo.CONSTITUENTRECOGNITIONDECLINEDPROGRAM where CONSTITUENTID = @CONSTITUENTID and RECOGNITIONPROGRAMID = @RECOGNITIONPROGRAMID 
          end

          update dbo.CONSTITUENTRECOGNITIONDECLINEDPROGRAM set REASONCODEID = @REASONCODE
          where CONSTITUENTID = @CONSTITUENTID and RECOGNITIONPROGRAMID = @RECOGNITIONPROGRAMID

          update dbo.CONSTITUENTRECOGNITIONDECLINEDLEVEL set REASONCODEID = @REASONCODE
          where CONSTITUENTID = @CONSTITUENTID and RECOGNITIONPROGRAMID = @RECOGNITIONPROGRAMID

            begin try
                exec USP_CONSTITUENTRECOGNITION_GETDECLINEDLEVELS_UPDATEFROMXML @CONSTITUENTID,@RECOGNITIONPROGRAMID,@DECLINEDRECOGNITIONLEVELS,@CHANGEAGENTID,@CURRENTDATE, @REASONCODE
            end try
            begin catch
                exec dbo.USP_RAISE_ERROR
                return 1
            end catch

        return 0;