USP_DATAFORMTEMPLATE_EDIT_RECOGNITIONLEVEL

          CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_RECOGNITIONLEVEL
          (
            @ID uniqueidentifier,
            @CHANGEAGENTID uniqueidentifier = null,
            @NAME nvarchar(100),
            @DESCRIPTION nvarchar(255),
            @TIERCODEID uniqueidentifier,
            @AMOUNT money,
            @PLANNEDGIFTAMOUNT money,
            @UPDATEFUTUREAMOUNT bit,
            @NEWMINIMUMAMOUNT money,
            @NEWPLANNEDGIFTAMOUNT money,
            @EFFECTIVEDATE datetime
          )
          as
            set nocount on;

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

            declare @CURRENTDATE datetime;
            set @CURRENTDATE = getdate();

            begin try
              if @UPDATEFUTUREAMOUNT = 1 and @EFFECTIVEDATE is null
              begin
                raiserror('ERR_RECOGNITIONLEVEL_EFFECTIVEDATEREQUIRED', 16, 1);
              end

              update dbo.RECOGNITIONLEVEL set
                NAME = @NAME,
                DESCRIPTION = @DESCRIPTION,
                TIERCODEID = @TIERCODEID,
                CHANGEDBYID = @CHANGEAGENTID,
                DATECHANGED = @CURRENTDATE
              where ID = @ID;

              declare @BASECURRENCYID uniqueidentifier;
              declare @DATEADDED datetime;
              declare @ORGANIZATIONEXCHANGERATEID uniqueidentifier;
              declare @ORGANIZATIONAMOUNT money;
              declare @ORGANIZATIONPLANNEDGIFTAMOUNT money;
              declare @ORGANIZATIONNEWMINIMUMAMOUNT money;
              declare @ORGANIZATIONNEWPLANNEDGIFTAMOUNT money;
              declare @ORGANIZATIONCURRENCYID uniqueidentifier = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();

              select
                @BASECURRENCYID = BASECURRENCYID,
                @DATEADDED = DATEADDED,
                @ORGANIZATIONEXCHANGERATEID = ORGANIZATIONEXCHANGERATEID
              from dbo.RECOGNITIONLEVEL
              where ID = @ID;

              if @ORGANIZATIONCURRENCYID = @BASECURRENCYID
              begin
                set @ORGANIZATIONAMOUNT = @AMOUNT;
                set @ORGANIZATIONPLANNEDGIFTAMOUNT = @PLANNEDGIFTAMOUNT;
                set @ORGANIZATIONNEWMINIMUMAMOUNT = @NEWMINIMUMAMOUNT;
                set @ORGANIZATIONNEWPLANNEDGIFTAMOUNT = @NEWPLANNEDGIFTAMOUNT;
              end
              else
              begin
                if @ORGANIZATIONEXCHANGERATEID is null
                  set @ORGANIZATIONEXCHANGERATEID = dbo.UFN_CURRENCYEXCHANGERATE_GETLATEST(@BASECURRENCYID, @ORGANIZATIONCURRENCYID, @DATEADDED, 0, null);

                set @ORGANIZATIONAMOUNT = dbo.UFN_CURRENCY_CONVERT(@AMOUNT, @ORGANIZATIONEXCHANGERATEID);
                set @ORGANIZATIONPLANNEDGIFTAMOUNT = dbo.UFN_CURRENCY_CONVERT(@PLANNEDGIFTAMOUNT, @ORGANIZATIONEXCHANGERATEID);
                set @ORGANIZATIONNEWMINIMUMAMOUNT = dbo.UFN_CURRENCY_CONVERT(@NEWMINIMUMAMOUNT, @ORGANIZATIONEXCHANGERATEID);
                set @ORGANIZATIONNEWPLANNEDGIFTAMOUNT = dbo.UFN_CURRENCY_CONVERT(@NEWPLANNEDGIFTAMOUNT, @ORGANIZATIONEXCHANGERATEID);
              end

              --jdp 10/19/2010

              --bug 112767

              --if the user has deleted the effective RLA record, this procedure would at this 

              --point attempt to update a non-existent record, with the result that any amount

              --you entered for RecognitionLevelAmount would not be saved.

              --to fix this, we query to see if an effective RLA record exists, and if not

              --we insert one, in the same way as the RLA add spec.  If count = 1

              --we do the update as before.


              if (select count(*) from dbo.RECOGNITIONLEVELAMOUNT where RECOGNITIONLEVELID = @ID and  ACTIVEDATE is null) = 0
              begin
                -- No "effective" RECOGNITIONLEVELAMOUNT record exists, so create one.

                -- Insert statement cribbed verbatim from the RecognitionLevel.Add spec.

                insert into dbo.RECOGNITIONLEVELAMOUNT
                (
                  ID,
                  RECOGNITIONLEVELID,
                  AMOUNT,
                  ORGANIZATIONAMOUNT,
                  PLANNEDGIFTAMOUNT,
                  ORGANIZATIONPLANNEDGIFTAMOUNT,
                  ORGANIZATIONEXCHANGERATEID,
                  BASECURRENCYID,
                  ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
                )
                values
                (
                  newid(),
                  @ID,
                  @AMOUNT,
                  @ORGANIZATIONAMOUNT,
                  @PLANNEDGIFTAMOUNT,
                  @ORGANIZATIONPLANNEDGIFTAMOUNT,
                  @ORGANIZATIONEXCHANGERATEID,
                  @BASECURRENCYID,
                  @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
                );
              end
              else
              begin
                --an RLA record exists, so issue an update


                update dbo.RECOGNITIONLEVELAMOUNT set
                  AMOUNT = @AMOUNT,
                  ORGANIZATIONAMOUNT = @ORGANIZATIONAMOUNT,
                  PLANNEDGIFTAMOUNT = @PLANNEDGIFTAMOUNT,
                  ORGANIZATIONPLANNEDGIFTAMOUNT = @ORGANIZATIONPLANNEDGIFTAMOUNT,
                  ORGANIZATIONEXCHANGERATEID = @ORGANIZATIONEXCHANGERATEID,
                  CHANGEDBYID = @CHANGEAGENTID,
                  DATECHANGED = @CURRENTDATE
                where ID =
                  (
                    select top 1 ID
                    from dbo.RECOGNITIONLEVELAMOUNT
                    where
                      RECOGNITIONLEVELID = @ID and
                      coalesce(ACTIVEDATE, 0) < @CURRENTDATE
                    order by ACTIVEDATE desc);
              end

              if @UPDATEFUTUREAMOUNT = 1 
              begin
                if exists (select ID from dbo.RECOGNITIONLEVELAMOUNT where RECOGNITIONLEVELID = @ID and ACTIVEDATE > @CURRENTDATE)
                begin
                  update dbo.RECOGNITIONLEVELAMOUNT set
                    AMOUNT = @NEWMINIMUMAMOUNT,
                    ORGANIZATIONAMOUNT = @ORGANIZATIONNEWMINIMUMAMOUNT,
                    PLANNEDGIFTAMOUNT = @NEWPLANNEDGIFTAMOUNT,
                    ORGANIZATIONPLANNEDGIFTAMOUNT = @ORGANIZATIONNEWPLANNEDGIFTAMOUNT,
                    ACTIVEDATE = @EFFECTIVEDATE,
                    ORGANIZATIONEXCHANGERATEID = @ORGANIZATIONEXCHANGERATEID,
                    CHANGEDBYID = @CHANGEAGENTID,
                    DATECHANGED = @CURRENTDATE
                  where ID = 
                    (
                      select top 1 ID
                      from dbo.RECOGNITIONLEVELAMOUNT
                      where
                        RECOGNITIONLEVELID = @ID and
                        ACTIVEDATE > @CURRENTDATE
                      order by ACTIVEDATE asc
                    );
                end
                else
                begin 
                  insert into dbo.RECOGNITIONLEVELAMOUNT
                  (
                    ID,
                    RECOGNITIONLEVELID,
                    AMOUNT,
                    ORGANIZATIONAMOUNT,
                    PLANNEDGIFTAMOUNT,
                    ORGANIZATIONPLANNEDGIFTAMOUNT,
                    ACTIVEDATE,
                    ORGANIZATIONEXCHANGERATEID,
                    BASECURRENCYID,
                    ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
                  )
                  values
                  (
                    newid(),
                    @ID,
                    @NEWMINIMUMAMOUNT,
                    @ORGANIZATIONNEWMINIMUMAMOUNT,
                    @NEWPLANNEDGIFTAMOUNT,
                    @ORGANIZATIONNEWPLANNEDGIFTAMOUNT,
                    @EFFECTIVEDATE,
                    @ORGANIZATIONEXCHANGERATEID,
                    @BASECURRENCYID,
                    @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
                  );
                end
                --jdp 10/19/2010 if future effective amount is de-selected,

                --go ahead and delete any of these that exist.

              end
              else
              begin
                --Cache current context information

                declare @CONTEXTCACHE varbinary(128);
                set @CONTEXTCACHE = CONTEXT_INFO();

                --Set CONTEXT_INFO to @CHANGEAGENTID

                if @CHANGEAGENTID is not null
                  set CONTEXT_INFO @CHANGEAGENTID;

                delete from dbo.RECOGNITIONLEVELAMOUNT
                where RECOGNITIONLEVELID = @ID and ACTIVEDATE > @CURRENTDATE;

                --Reset CONTEXT_INFO to previous value

                if not @CONTEXTCACHE is null
                  set CONTEXT_INFO @CONTEXTCACHE;
              end
            end try
            begin catch
              exec dbo.USP_RAISE_ERROR;
              return 1;
            end catch

            return 0;