USP_DATAFORMTEMPLATE_EDIT_MATCHINGGIFTCONDITION_3

The save procedure used by the edit dataform template "Matching Gift Condition Edit Form 2".
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.
@MATCHINGGIFTCONDITIONTYPECODEID	uniqueidentifier	IN	Type
@MATCHINGFACTOR	decimal(5, 2)	IN	Matching factor
@MINMATCHPERGIFT	money	IN	Min match per gift
@MAXMATCHPERGIFT	money	IN	Max match per gift
@MAXMATCHANNUAL	money	IN	Max match per year
@MAXMATCHTOTAL	money	IN	Max match total
@NOTES	nvarchar(max)	IN	Notes
@RELATIONSHIPS	xml	IN	Apply to
@MATCHTYPECODE	tinyint	IN	Matching amount
@REVENUETYPECODE	smallint	IN	Revenue type
Definition

Copy


                CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_MATCHINGGIFTCONDITION_3
                (
                    @ID uniqueidentifier,
                    @CHANGEAGENTID uniqueidentifier = null,
                    @MATCHINGGIFTCONDITIONTYPECODEID uniqueidentifier,
                    @MATCHINGFACTOR decimal(5,2),
                    @MINMATCHPERGIFT money,
                    @MAXMATCHPERGIFT money,
                    @MAXMATCHANNUAL money,
                    @MAXMATCHTOTAL money,
                    @NOTES nvarchar(max),
                    @RELATIONSHIPS xml,
                    @MATCHTYPECODE tinyint,
                    @REVENUETYPECODE smallint
                ) as
                    set nocount on;

                    declare @CURRENTDATE datetime;

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

                    set @CURRENTDATE = getdate();

                    declare @BASECURRENCYID uniqueidentifier;
                    select @BASECURRENCYID = BASECURRENCYID
                    from dbo.MATCHINGGIFTCONDITION
                    where ID = @ID;

                    declare @ORGANIZATIONCURRENCYID uniqueidentifier = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();
                    declare @ORGANIZATIONMINMATCHPERGIFT money
                    declare @ORGANIZATIONMAXMATCHPERGIFT money
                    declare @ORGANIZATIONMAXMATCHANNUAL money
                    declare @ORGANIZATIONMAXMATCHTOTAL money
                    declare @ORGANIZATIONEXCHANGERATEID uniqueidentifier

                    if @BASECURRENCYID = @ORGANIZATIONCURRENCYID
                    begin
                        set @ORGANIZATIONMINMATCHPERGIFT = @MINMATCHPERGIFT
                        set @ORGANIZATIONMAXMATCHPERGIFT = @MAXMATCHPERGIFT
                        set @ORGANIZATIONMAXMATCHANNUAL = @MAXMATCHANNUAL
                        set @ORGANIZATIONMAXMATCHTOTAL = @MAXMATCHTOTAL
                    end
                    else
                    begin
                        set @ORGANIZATIONEXCHANGERATEID = dbo.UFN_CURRENCYEXCHANGERATE_GETLATEST(@BASECURRENCYID,@ORGANIZATIONCURRENCYID,@CURRENTDATE,0,null);

                        set @ORGANIZATIONMINMATCHPERGIFT = dbo.UFN_CURRENCY_CONVERT(@MINMATCHPERGIFT,@ORGANIZATIONEXCHANGERATEID)
                        set @ORGANIZATIONMAXMATCHPERGIFT = dbo.UFN_CURRENCY_CONVERT(@MAXMATCHPERGIFT,@ORGANIZATIONEXCHANGERATEID)
                        set @ORGANIZATIONMAXMATCHANNUAL = dbo.UFN_CURRENCY_CONVERT(@MAXMATCHANNUAL,@ORGANIZATIONEXCHANGERATEID)
                        set @ORGANIZATIONMAXMATCHTOTAL = dbo.UFN_CURRENCY_CONVERT(@MAXMATCHTOTAL,@ORGANIZATIONEXCHANGERATEID)
                    end

                    begin try
                        update
                            dbo.MATCHINGGIFTCONDITION
                        set
                            MATCHINGGIFTCONDITIONTYPECODEID = @MATCHINGGIFTCONDITIONTYPECODEID,
                            MATCHINGFACTOR = @MATCHINGFACTOR,
                            MINMATCHPERGIFT = @MINMATCHPERGIFT,
                            MAXMATCHPERGIFT = @MAXMATCHPERGIFT,
                            MAXMATCHANNUAL = @MAXMATCHANNUAL,
                            MAXMATCHTOTAL = @MAXMATCHTOTAL,
                            NOTES = @NOTES,
                            MATCHTYPECODE = @MATCHTYPECODE,
                            DATECHANGED = @CURRENTDATE,
                            CHANGEDBYID = @CHANGEAGENTID,
                            REVENUETYPECODE = @REVENUETYPECODE,
                            BASECURRENCYID = @BASECURRENCYID,
                            ORGANIZATIONMINMATCHPERGIFT = @ORGANIZATIONMINMATCHPERGIFT,
                            ORGANIZATIONMAXMATCHPERGIFT = @ORGANIZATIONMAXMATCHPERGIFT,
                            ORGANIZATIONMAXMATCHANNUAL = @ORGANIZATIONMAXMATCHANNUAL,
                            ORGANIZATIONMAXMATCHTOTAL = @ORGANIZATIONMAXMATCHTOTAL,
                            ORGANIZATIONEXCHANGERATEID = @ORGANIZATIONEXCHANGERATEID
                        where
                            ID = @ID;

                        --JamesWill WI 13119 Fix relationships

                        with [CTE] as
                        (
                            select 
                                T.c.value('(ID)[1]', 'nvarchar(36)') as [ID],
                                T.c.value('(RELATIONSHIPTYPECODEID)[1]', 'nvarchar(36)') as [RELATIONSHIPTYPECODEID],
                                T.c.value('(JOBSCHEDULECODEID)[1]', 'nvarchar(36)') as [JOBSCHEDULECODEID],
                                T.c.value('(CAREERLEVELCODEID)[1]', 'nvarchar(36)') as [CAREERLEVELCODEID]
                            from @RELATIONSHIPS.nodes('/RELATIONSHIPS/ITEM') T(c)
                        )
                        select 
                            @RELATIONSHIPS = 
                            (    select
                                case when len([CTE].ID) > 0 then cast([CTE].ID as uniqueidentifier) else newid() end as [ID],
                                cast([CTE].RELATIONSHIPTYPECODEID as uniqueidentifier) as [RELATIONSHIPTYPECODEID],
                                cast([CTE].JOBSCHEDULECODEID as uniqueidentifier) as [JOBSCHEDULECODEID],
                                cast([CTE].CAREERLEVELCODEID as uniqueidentifier) as [CAREERLEVELCODEID]
                            from [CTE]
                            where len([CTE].RELATIONSHIPTYPECODEID) > 0
                            for xml raw('ITEM'), type, elements, root('RELATIONSHIPS'), binary base64 
                            )

                        exec dbo.USP_MATCHINGGIFTCONDITIONRELATIONSHIP_VALIDATERELATIONSHIPTYPES @ID, @RELATIONSHIPS;
                        exec dbo.USP_MATCHINGGIFTCONDITIONRELATIONSHIP_GETRELATIONSHIPS_UPDATEFROMXML @ID, @RELATIONSHIPS, @CHANGEAGENTID;
                    end try

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

                    return 0;