USP_DATAFORMTEMPLATE_ADD_CONSTITUENTRECOGNITIONDECLINEDLEVELSBYPROGRAM

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

      set nocount on;

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

      declare @CURRENTDATE datetime
      set @CURRENTDATE = getdate()

      begin try


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

            --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


          end

            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;