USP_DATAFORMTEMPLATE_ADD_CONSTITUENTRECOGNITION

The save procedure used by the add dataform template "Constituent Recognition 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.
@CONSTITUENTID	uniqueidentifier	IN	Input parameter indicating the context ID for the record being added.
@RECOGNITIONPROGRAMID	uniqueidentifier	IN	Recognition program
@RECOGNITIONLEVELID	uniqueidentifier	IN	Recognition level
@STATUSCODE	tinyint	IN	Status
@DATEACHIEVED	datetime	IN	Date achieved
@COMMENTS	nvarchar(1000)	IN	Comments
@ISANONYMOUS	bit	IN	Anonymous
@DECLINEDRECOGNITIONLEVELS	xml	IN
@REASONCODE	uniqueidentifier	IN
Definition

Copy


                    CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_CONSTITUENTRECOGNITION
                    (
                        @ID uniqueidentifier = null output,
                        @CHANGEAGENTID uniqueidentifier = null,
                        @CONSTITUENTID uniqueidentifier,
                        @RECOGNITIONPROGRAMID uniqueidentifier = null,
                        @RECOGNITIONLEVELID uniqueidentifier = null,
                        @STATUSCODE tinyint = 0,
                        @DATEACHIEVED datetime = null,
                        @COMMENTS nvarchar(1000) = null,
                        @ISANONYMOUS bit = 0,
            @DECLINEDRECOGNITIONLEVELS xml = null,
            @REASONCODE uniqueidentifier = null
                    )
                    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 @EXPIRATIONDATE datetime;
                        declare @BACKDATEMEMBERSHIPS bit;
                        select @EXPIRATIONDATE = 
                            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,
                            @BACKDATEMEMBERSHIPS = RP.BACKDATEMEMBERSHIPS
                        from dbo.RECOGNITIONPROGRAM RP
                        where RP.ID = @RECOGNITIONPROGRAMID
                            and RP.TYPECODE = 0;

                        if @BACKDATEMEMBERSHIPS = 1 and @DATEACHIEVED < @EXPIRATIONDATE
                            set @EXPIRATIONDATE = dateadd(year, 1, @EXPIRATIONDATE)

                        if exists(
                            select ID from dbo.CONSTITUENTRECOGNITION
                            where ID <> @ID
                                and CONSTITUENTID = @CONSTITUENTID
                                and RECOGNITIONPROGRAMID = @RECOGNITIONPROGRAMID
                                and STATUSCODE <> 1                        
                            )
                                raiserror('ERR_CONSITITUENTRECOGNITION_EXISTS', 16, 1);

                        declare @BASECURRENCYID uniqueidentifier;
                        declare @ORGANIZATIONCURRENCYID uniqueidentifier;

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

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

                        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
                        )



            exec dbo.USP_CONSTITUENTRECOGNITION_GETDECLINEDLEVELS_UPDATEFROMXML @CONSTITUENTID,@RECOGNITIONPROGRAMID,@DECLINEDRECOGNITIONLEVELS, @CHANGEAGENTID, @CURRENTDATE,@REASONCODE,@RECOGNITIONLEVELID,1

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

                    return 0