USP_DATAFORMTEMPLATE_EDIT_CONSTITUENTRECOGNITIONHISTORY

                CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_CONSTITUENTRECOGNITIONHISTORY
                (
                    @ID uniqueidentifier,
                    @CHANGEAGENTID uniqueidentifier = null,
                    @RECOGNITIONLEVELID uniqueidentifier,
                    @DATEACHIEVED datetime,
                    @COMMENTS nvarchar(1000),
                    @ISANONYMOUS bit
                )
                as
                    set nocount on;

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

                    declare @CURRENTDATE datetime = getdate();

                    if @ISANONYMOUS is null
                        set @ISANONYMOUS = 0;

                    declare @RECOGNITIONPROGRAMID uniqueidentifier;
                    declare @CONSTITUENTID uniqueidentifier;
                    declare @EXPIRATIONDATE datetime;

                    select
                        @RECOGNITIONPROGRAMID = CR.RECOGNITIONPROGRAMID,
                        @CONSTITUENTID = CR.CONSTITUENTID,
                        @EXPIRATIONDATE = 
                        case
                            when RP.TYPECODE = 1 then null
                            else
                            case
                                when RP.EXPIRESONCODE = 0 then dbo.UFN_DATE_THISCALENDARYEAR_LASTDAY(@DATEACHIEVED,1)
                                when RP.EXPIRESONCODE = 1 then dbo.UFN_DATE_THISFISCALYEAR_LASTDAY(@DATEACHIEVED,1)
                            end
                        end
                    from dbo.CONSTITUENTRECOGNITION CR
                        inner join dbo.RECOGNITIONPROGRAM RP on CR.RECOGNITIONPROGRAMID = RP.ID
                    where CR.ID = @ID;

                    --If the program level is in the declined levels list, throw error.

                    declare @DENIEDID uniqueidentifier = (select ID from dbo.CONSTITUENTRECOGNITIONDECLINEDLEVEL where CONSTITUENTID = @CONSTITUENTID and RECOGNITIONLEVELID = @RECOGNITIONLEVELID and RECOGNITIONPROGRAMID = @RECOGNITIONPROGRAMID)
                    if @DENIEDID is not null 
                        raiserror('BBERR_RECOGNITIONLEVELISDECLINED',13,1);

                    begin try
                        update dbo.CONSTITUENTRECOGNITION set
                            RECOGNITIONLEVELID = @RECOGNITIONLEVELID,
                            EXPIRATIONDATE = @EXPIRATIONDATE,
                            JOINDATE = @DATEACHIEVED,
                            COMMENTS = @COMMENTS,
                            ISANONYMOUS = @ISANONYMOUS,
                            CHANGEDBYID = @CHANGEAGENTID,
                            DATECHANGED = @CURRENTDATE
                        where ID = @ID;

                        declare @BASECURRENCYID uniqueidentifier;
                        declare @ORGANIZATIONCURRENCYID uniqueidentifier;

                        set @ORGANIZATIONCURRENCYID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();
                        select @BASECURRENCYID = RP.BASECURRENCYID
                        from dbo.RECOGNITIONPROGRAM RP where ID = @RECOGNITIONPROGRAMID;

                        -- Add the benefits associated with the new level

                        insert into dbo.CONSTITUENTRECOGNITIONBENEFIT
                        (
                            CONSTITUENTRECOGNITIONID,
                            BENEFITID,
                            QUANTITY,
                            UNITVALUE,
                            ORGANIZATIONUNITVALUE,
                            ORGANIZATIONEXCHANGERATEID,
                            DETAILS,
                            BASECURRENCYID,
                            SEQUENCE,
                            DATEADDED,
                            DATECHANGED,
                            ADDEDBYID,
                            CHANGEDBYID
                        ) 
                        select
                            @ID,
                            RLB.BENEFITID,
                            RLB.QUANTITY,
                            UNITVALUE,
                            case RLB.BASECURRENCYID
                                when @ORGANIZATIONCURRENCYID then RLB.UNITVALUE
                                else dbo.UFN_CURRENCY_CONVERT(RLB.UNITVALUE, dbo.UFN_CURRENCYEXCHANGERATE_GETLATEST(RLB.BASECURRENCYID, @ORGANIZATIONCURRENCYID, @CURRENTDATE, 0, null))
                            end ORGANIZATIONUNITVALUE,
                            case RLB.BASECURRENCYID
                                when @ORGANIZATIONCURRENCYID then null
                                else RLB.ORGANIZATIONEXCHANGERATEID
                            end ORGANIZATIONEXCHANGERATEID,
                            RLB.DETAILS,
                            RLB.BASECURRENCYID,
                            RLB.SEQUENCE,
                            @CURRENTDATE,
                            @CURRENTDATE,
                            @CHANGEAGENTID,
                            @CHANGEAGENTID
                        from dbo.RECOGNITIONLEVELBENEFIT RLB
                        where
                            RECOGNITIONLEVELID = @RECOGNITIONLEVELID and
                            RLB.BENEFITID not in
                            (
                                select BCD.BENEFITID
                                from dbo.BENEFITCONSTITUENTDECLINED BCD
                                where BCD.CONSTITUENTID = @CONSTITUENTID
                            ) and
                            RLB.BENEFITID not in
                            (
                                select CRB.BENEFITID
                                from dbo.CONSTITUENTRECOGNITIONBENEFIT CRB
                                where CRB.CONSTITUENTRECOGNITIONID = @ID
                            );
                    end try
                    begin catch
                        exec dbo.USP_RAISE_ERROR;
                        return 1;
                    end catch

                return 0;