USP_DATAFORMTEMPLATE_ADD_CONSTITUENTRECOGNITIONHISTORY

The save procedure used by the add dataform template "Constituent Recognition History Add Data Form".
Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	INOUT	The output parameter indicating the ID of the record added.
@CHANGEAGENTID	uniqueidentifier	IN	Input parameter indicating the ID of the change agent invoking the procedure.
@CONSTITUENTRECOGNITIONID	uniqueidentifier	IN	Input parameter indicating the context ID for the record being added.
@RECOGNITIONLEVELID	uniqueidentifier	IN	Recognition level
@DATEACHIEVED	datetime	IN	Date achieved
@COMMENTS	nvarchar(1000)	IN	Comments
@ISANONYMOUS	bit	IN	Anonymous
Definition

Copy


                    CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_CONSTITUENTRECOGNITIONHISTORY
                    (
                        @ID uniqueidentifier = null output,
                        @CHANGEAGENTID uniqueidentifier = null,
                        @CONSTITUENTRECOGNITIONID uniqueidentifier,
                        @RECOGNITIONLEVELID uniqueidentifier = null,
                        @DATEACHIEVED datetime = null,
                        @COMMENTS nvarchar(1000) = null,
                        @ISANONYMOUS bit = 0
                    )
                    as

                        set nocount on;

                    begin try                    
                        if @ID is null
                            set @ID = newid()

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

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

                        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 = @CONSTITUENTRECOGNITIONID;

                        declare @BASECURRENCYID uniqueidentifier;
                        declare @ORGANIZATIONCURRENCYID uniqueidentifier;

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


                        --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 
                        begin
                         raiserror('BBERR_RECOGNITIONLEVELISDECLINED',13,1)
                        end

                        insert into dbo.CONSTITUENTRECOGNITION
                            (ID,
                            RECOGNITIONPROGRAMID,
                            RECOGNITIONLEVELID,
                            JOINDATE,
                            COMMENTS,
                            CONSTITUENTID,
                            EXPIRATIONDATE,
                            ISANONYMOUS,
                            BASECURRENCYID,
                            DATEADDED,
                            DATECHANGED,
                            ADDEDBYID,
                            CHANGEDBYID)
                        values
                            (@ID,
                            @RECOGNITIONPROGRAMID,
                            @RECOGNITIONLEVELID,
                            @DATEACHIEVED,
                            @COMMENTS,
                            @CONSTITUENTID,
                            @EXPIRATIONDATE,
                            @ISANONYMOUS,
                            @BASECURRENCYID,
                            @CURRENTDATE,
                            @CURRENTDATE,
                            @CHANGEAGENTID,
                            @CHANGEAGENTID)

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

                    end try
                    begin catch
                        exec dbo.USP_RAISE_ERROR
                        return 1
                    end catch                        

                    return 0