USP_DATAFORMTEMPLATE_EDITSAVE_CONSTITUENTRECOGNITIONBENEFIT

The save procedure used by the edit dataform template "Constituent Recognition Benefits Edit Form".
Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	IN	The input ID parameter indicating the ID of the record being edited.
@CHANGEAGENTID	uniqueidentifier	IN	Input parameter indicating the ID of the change agent invoking the procedure.
@BENEFITS	xml	IN	Benefits
Definition

Copy


                CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDITSAVE_CONSTITUENTRECOGNITIONBENEFIT
                (
                    @ID uniqueidentifier,
                    @CHANGEAGENTID uniqueidentifier = null,
                    @BENEFITS xml
                ) as
                set nocount on;

                begin try
                    declare @BASECURRENCYID uniqueidentifier;
                    declare @ORGANIZATIONEXCHANGERATEID uniqueidentifier;

                    select
                        @BASECURRENCYID = CR.BASECURRENCYID,
                        @ORGANIZATIONEXCHANGERATEID = CR.ORGANIZATIONEXCHANGERATEID
                    from
                        dbo.CONSTITUENTRECOGNITION CR
                    where
                        CR.ID = @ID;

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

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

                    declare @OLDBENEFITS xml
                    declare @CONSTITUENTID uniqueidentifier
                    select
                        @OLDBENEFITS = dbo.UFN_CONSTITUENTRECOGNITION_GETBENEFITS_TOITEMLISTXML(@ID),
                        @CONSTITUENTID = CR.CONSTITUENTID
                    from dbo.CONSTITUENTRECOGNITION CR
                    where CR.ID = @ID

                    insert into dbo.BENEFITCONSTITUENTDECLINED
                    (
                        BENEFITID,
                        CONSTITUENTID,
                        ADDEDBYID, 
                        CHANGEDBYID, 
                        DATEADDED, 
                        DATECHANGED
                    )
                    select 
                        T.c.value('(BENEFITID)[1]','uniqueidentifier') as BENEFITID,
                        @CONSTITUENTID,
                        @CHANGEAGENTID, 
                        @CHANGEAGENTID, 
                        @CURRENTDATE, 
                        @CURRENTDATE                        
                    from @OLDBENEFITS.nodes('/BENEFITS/ITEM') T(c)
                    where
                        -- Benefit has been deleted

                        T.c.value('(BENEFITID)[1]','uniqueidentifier') not in
                        (
                            select 
                                T.benefits.value('(BENEFITID)[1]','uniqueidentifier')                        
                            from @BENEFITS.nodes('/BENEFITS/ITEM') T(benefits)
                        )
                        and
                        -- Record does not already exist

                        T.c.value('(BENEFITID)[1]','uniqueidentifier') not in
                        (
                            select BCD.BENEFITID
                            from dbo.BENEFITCONSTITUENTDECLINED BCD
                            where BCD.CONSTITUENTID = @CONSTITUENTID
                        )

                    -- If benefit is added constituent is no longer declining it

                    delete from dbo.BENEFITCONSTITUENTDECLINED
                    where
                        CONSTITUENTID = @CONSTITUENTID
                        and BENEFITID in
                        (
                            select T.c.value('(BENEFITID)[1]','uniqueidentifier')                        
                            from @BENEFITS.nodes('/BENEFITS/ITEM') T(c)
                        )
                        and BENEFITID not in
                        (
                            select T.c.value('(BENEFITID)[1]','uniqueidentifier')                        
                            from @OLDBENEFITS.nodes('/BENEFITS/ITEM') T(c)
                        )

                    set @BENEFITS = dbo.UFN_CONSTITUENTRECOGNITIONBENEFITS_CONVERTAMOUNTSINXML(@BENEFITS, @BASECURRENCYID, @ORGANIZATIONEXCHANGERATEID, @CURRENTDATE);
                    exec dbo.USP_CONSTITUENTRECOGNITION_GETBENEFITS_2_UPDATEFROMXML @ID, @BENEFITS, @CHANGEAGENTID, @CURRENTDATE;
                end try
                begin catch
                    exec dbo.USP_RAISE_ERROR;
                    return 1;
                end catch

                return 0;