USP_MATCHINGGIFTCLAIM_AMOUNT_UPDATE

        CREATE procedure dbo.USP_MATCHINGGIFTCLAIM_AMOUNT_UPDATE
        (
            @ID as uniqueidentifier, 
            @NEWPAYMENTAMOUNT money, 
            @OLDAMOUNT money, 
            @OPTIONSELECTED smallint, 
            @CHANGEAGENTID uniqueidentifier, 
            @CURRENTDATE datetime
        )
        as
        begin
            set nocount on;  

            declare @TRANSACTIONCURRENCYID uniqueidentifier;
            declare @BASECURRENCYID uniqueidentifier;
            declare @ORGANIZATIONEXCHANGERATEID uniqueidentifier;
            declare @BASEEXCHANGERATEID uniqueidentifier;
            declare @REVENUEMATCHINGGIFTID uniqueidentifier;
            declare @OLDTRANSACTIONAMOUNT money;
            declare @NEWTRANSACTIONAMOUNT money;
            declare @NEWBASEAMOUNT money;
            declare @NEWORGANIZATIONAMOUNT money;
            declare @DATE datetime
            declare @DECIMALDIGITS smallint;
            declare @ROUNDINGTYPECODE smallint;
            declare @NUMBEROFMATCHINGGIFTS smallint;
            declare @MATCHINGFACTOR decimal(5,2);
            declare @SPLITS xml;
            declare @MAXMATCHPERGIFT money;
            declare @MATCHINGGIFTMAXMATCHANNUAL money;
            declare @MATCHINGGIFTMAXMATCHTOTAL money;
            declare @CAPPEDNEWTRANSACTIONAMOUNT money;

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

            if @CURRENTDATE is null
                set @CURRENTDATE = GetDate();

            --Option Selected: 0 - Do not adjust, 1 - Adjust Proportionally, 2 - Adjust based on Constituent defaults 

            --New amount - Proportionally


            if @OLDAMOUNT > 0 and @OPTIONSELECTED = 1 
            begin   

                --Include only those matching gifts that do not have a payment associated with it.

                select @NUMBEROFMATCHINGGIFTS = count(ID)
                from dbo.[REVENUEMATCHINGGIFT] 
                where [MGSOURCEREVENUEID] = @ID 
                and REVENUEMATCHINGGIFT.ID not in (select PLEDGEID from INSTALLMENTSPLITPAYMENT);

                --Checking to make sure matching gifts exists before creating a cursor.

                if @NUMBEROFMATCHINGGIFTS > 0
                begin      
                    declare REVENUEMATCHINGGIFTCURSOR cursor local fast_forward for
                    select [ID] 
                    from dbo.[REVENUEMATCHINGGIFT] 
                    where [MGSOURCEREVENUEID] = @ID 
                    and REVENUEMATCHINGGIFT.ID not in (select PLEDGEID from INSTALLMENTSPLITPAYMENT);

                    open REVENUEMATCHINGGIFTCURSOR;
                    fetch next from REVENUEMATCHINGGIFTCURSOR into @REVENUEMATCHINGGIFTID;

                    while (@@FETCH_STATUS = 0)
                    begin
                        select
                            @DATE = DATE,
                            @BASECURRENCYID = CURRENCY.ID,
                            @TRANSACTIONCURRENCYID = FINANCIALTRANSACTION.TRANSACTIONCURRENCYID,
                            @ORGANIZATIONEXCHANGERATEID = FINANCIALTRANSACTION.ORGEXCHANGERATEID,
                            @BASEEXCHANGERATEID = FINANCIALTRANSACTION.BASEEXCHANGERATEID,
                            @OLDTRANSACTIONAMOUNT = FINANCIALTRANSACTION.TRANSACTIONAMOUNT,
                            @DECIMALDIGITS = CURRENCY.DECIMALDIGITS,
                            @ROUNDINGTYPECODE = CURRENCY.ROUNDINGTYPECODE
                        from dbo.FINANCIALTRANSACTION
                        inner join REVENUE_EXT on FINANCIALTRANSACTION.ID = REVENUE_EXT.ID
                        inner join dbo.PDACCOUNTSYSTEM on FINANCIALTRANSACTION.PDACCOUNTSYSTEMID = PDACCOUNTSYSTEM.ID
                        inner join dbo.CURRENCYSET on PDACCOUNTSYSTEM.CURRENCYSETID = CURRENCYSET.ID
                        inner join dbo.CURRENCY on isnull(REVENUE_EXT.NONPOSTABLEBASECURRENCYID, CURRENCYSET.BASECURRENCYID) = CURRENCY.ID
                        where FINANCIALTRANSACTION.ID = @REVENUEMATCHINGGIFTID
                            and FINANCIALTRANSACTION.DELETEDON is null;

                        --In a nutshell here is what we are doing below. Consider the following example: We have a payment for $100, there is a

                        --matching gift claim for $50 with 3 splits of $10, $15, and $25 each

                        --When we change the original payment to $50 and select the option to adjust proportionally in the dialog shown,

                        --we calculate the new matching gift claim amount $50 (new payment amount) * $50 (matching gift claim amount)/$100 (original payment)

                        --we also get the decimal digits and rounding type from the currency and set the amount appropriately. This is step 1. You will see

                        --more steps below.


                        merge dbo.FINANCIALTRANSACTION as target
                        using (select FINANCIALTRANSACTION.ID, dbo.UFN_CURRENCY_ROUND((@NEWPAYMENTAMOUNT*(FINANCIALTRANSACTION.TRANSACTIONAMOUNT/@OLDAMOUNT)), @DECIMALDIGITS, @ROUNDINGTYPECODE) as TranAmount
                            from dbo.FINANCIALTRANSACTION
                            inner join dbo.REVENUE_EXT on FINANCIALTRANSACTION.ID = REVENUE_EXT.ID
                            inner join dbo.PDACCOUNTSYSTEM on FINANCIALTRANSACTION.PDACCOUNTSYSTEMID = PDACCOUNTSYSTEM.ID
                            inner join dbo.CURRENCYSET on PDACCOUNTSYSTEM.CURRENCYSETID = CURRENCYSET.ID
                            inner join dbo.CURRENCY on CURRENCY.ID = isnull(REVENUE_EXT.NONPOSTABLEBASECURRENCYID, CURRENCYSET.BASECURRENCYID)
                            where FINANCIALTRANSACTION.ID = @REVENUEMATCHINGGIFTID
                                and FINANCIALTRANSACTION.DELETEDON is null) as source
                        on (source.ID = target.ID)
                        when matched then
                          update set
                          TRANSACTIONAMOUNT = source.TranAmount,
                          CHANGEDBYID = @CHANGEAGENTID,
                          DATECHANGED = @CURRENTDATE;
                        /*
                        update dbo.REVENUE 
                        set TRANSACTIONAMOUNT = dbo.UFN_CURRENCY_ROUND((@NEWPAYMENTAMOUNT*(REVENUE.TRANSACTIONAMOUNT/@OLDAMOUNT)), @DECIMALDIGITS, @ROUNDINGTYPECODE), 
                            CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CURRENTDATE from dbo.REVENUE 
                        inner join dbo.CURRENCY on CURRENCY.ID = REVENUE.BASECURRENCYID
                        where REVENUE.ID = @REVENUEMATCHINGGIFTID;
                        */
                        select 
                            @NEWTRANSACTIONAMOUNT = FINANCIALTRANSACTION.TRANSACTIONAMOUNT,
                            @MAXMATCHPERGIFT = MATCHINGGIFTCONDITION.MAXMATCHPERGIFT
                        from dbo.FINANCIALTRANSACTION
                        inner join dbo.REVENUE_EXT on FINANCIALTRANSACTION.ID = REVENUE_EXT.ID
                        inner join dbo.REVENUEMATCHINGGIFT on FINANCIALTRANSACTION.ID = REVENUEMATCHINGGIFT.ID
                        inner join dbo.MATCHINGGIFTCONDITION on REVENUEMATCHINGGIFT.MATCHINGGIFTCONDITIONID = MATCHINGGIFTCONDITION.ID
                        where FINANCIALTRANSACTION.ID = @REVENUEMATCHINGGIFTID
                            and FINANCIALTRANSACTION.DELETEDON is null;

                        set @CAPPEDNEWTRANSACTIONAMOUNT = @NEWTRANSACTIONAMOUNT;
                        if @NEWTRANSACTIONAMOUNT > @MAXMATCHPERGIFT
                            set @CAPPEDNEWTRANSACTIONAMOUNT = @MAXMATCHPERGIFT;

                        --step 2: Look at step 1 above for more information and I am following the same example I provided above.

                        --Get base and organization amounts based on currency, exchange rates, and transaction amount.

                        --Update these amounts


                        exec dbo.USP_CURRENCY_GETCURRENCYVALUES 
                            @AMOUNT=@CAPPEDNEWTRANSACTIONAMOUNT, 
                            @DATE=@DATE,    
                            @BASECURRENCYID=@BASECURRENCYID, 
                            @BASEEXCHANGERATEID=@BASEEXCHANGERATEID, 
                            @TRANSACTIONCURRENCYID=@TRANSACTIONCURRENCYID,
                            @BASEAMOUNT=@NEWBASEAMOUNT output,
                            @ORGANIZATIONAMOUNT=@NEWORGANIZATIONAMOUNT output,
                            @ORGANIZATIONEXCHANGERATEID=@ORGANIZATIONEXCHANGERATEID

                        update dbo.FINANCIALTRANSACTION set
                            TRANSACTIONAMOUNT = @CAPPEDNEWTRANSACTIONAMOUNT,
                            BASEAMOUNT = @NEWBASEAMOUNT,
                            ORGAMOUNT = @NEWORGANIZATIONAMOUNT,
                            ORGEXCHANGERATEID = @ORGANIZATIONEXCHANGERATEID,
                            TRANSACTIONCURRENCYID = @TRANSACTIONCURRENCYID,
                            BASEEXCHANGERATEID = @BASEEXCHANGERATEID,
                            CHANGEDBYID = @CHANGEAGENTID,
                            DATECHANGED = @CURRENTDATE
                        where ID = @REVENUEMATCHINGGIFTID
                            and DELETEDON is null;  

                        --step 3: Look at steps 1 and 2 above for more information and I am following the same example I provided above.

                        --Get the data you need into SPLITS xml and recalculate the split amounts based on proportions. Take a look at

                        --UFN_MATCHINGGIFTCLAIM_REPROPORTIONAMOUNTS for the logic.

                        --In the example above, we have 3 split amounts $10, $15, and $25 each. New matching gift amount is $25.

                        --These split amounts will be converted to $5, $7.5, and $12.5 each.

                        --using UFN_REVENUESPLIT_CONVERTAMOUNTSINXML, get the right base and organization amounts

                        --using USP_REVENUE_GETSPLITS_2_CUSTOMUPDATEFROMXML, update the amounts.


                        set @SPLITS =(
                            select
                                SPLIT.ID,
                                SPLIT.DESIGNATIONID,
                                SPLIT.APPLICATIONCODE,
                                SPLIT.TYPECODE,
                                SPLIT.TRANSACTIONAMOUNT as AMOUNT,
                                SPLIT.AMOUNT as BASEAMOUNT,
                                SPLIT.ORGANIZATIONAMOUNT,
                                SPLIT.BASECURRENCYID,
                                SPLIT.ORGANIZATIONEXCHANGERATEID,
                                SPLIT.TRANSACTIONCURRENCYID    ,
                                SPLIT.BASEEXCHANGERATEID
                            from dbo.UFN_REVENUE_GETSPLITS_2(@REVENUEMATCHINGGIFTID) SPLIT
                            for xml raw('ITEM'),type,elements,root('SPLITS'),BINARY BASE64
                        );

                        set @SPLITS = dbo.UFN_MATCHINGGIFTCLAIM_REPROPORTIONAMOUNTS(@SPLITS, @CAPPEDNEWTRANSACTIONAMOUNT, @OLDTRANSACTIONAMOUNT,@DECIMALDIGITS, @ROUNDINGTYPECODE);

                        set @SPLITS = dbo.UFN_REVENUESPLIT_CONVERTAMOUNTSINXML(@SPLITS, @BASECURRENCYID, @ORGANIZATIONEXCHANGERATEID, @TRANSACTIONCURRENCYID, @BASEEXCHANGERATEID)          

                        exec dbo.USP_REVENUE_GETSPLITS_2_CUSTOMUPDATEFROMXML @REVENUEMATCHINGGIFTID, @SPLITS, @CHANGEAGENTID,@CURRENTDATE;

                        fetch next from REVENUEMATCHINGGIFTCURSOR into @REVENUEMATCHINGGIFTID;
                    end

                    close REVENUEMATCHINGGIFTCURSOR;
                    deallocate REVENUEMATCHINGGIFTCURSOR; 
                end
            end


            -- Adjust the unpaid, automatically generated matching gifts according to the organization's settings

            if @OPTIONSELECTED = 2 
            begin    
                -- Store matching gifts to update in a table variable to avoid running the query several times

                declare @MATCHINGGIFTS table (
                    ID uniqueidentifier,
                    [DATE] datetime,
                    MATCHINGGIFTCONDITIONID uniqueidentifier,
                    REVENUETYPECODE tinyint,
                    MATCHTYPECODE tinyint,
                    MINMATCHPERGIFT money,
                    MAXMATCHPERGIFT money,
                    MAXMATCHANNUAL money,
                    MAXMATCHTOTAL money,
                    MATCHINGFACTOR decimal(5,2),
                    MGORGID uniqueidentifier
                );
                --Include only those matching gifts that do not have a payment associated with them

                --and that are associated with matching gift conditions.

                insert into @MATCHINGGIFTS (
                    ID, 
                    [DATE],
                    MATCHINGGIFTCONDITIONID,
                    REVENUETYPECODE,
                    MATCHTYPECODE,
                    MINMATCHPERGIFT,
                    MAXMATCHPERGIFT,
                    MAXMATCHANNUAL,
                    MAXMATCHTOTAL,
                    MATCHINGFACTOR,
                    MGORGID
                )
                select 
                    REVENUEMATCHINGGIFT.ID,
                    cast(FINANCIALTRANSACTION.[DATE] as datetime) as DATE,
                    MATCHINGGIFTCONDITION.ID as MATCHINGGIFTCONDITIONID,
                    MATCHINGGIFTCONDITION.REVENUETYPECODE,
                    MATCHINGGIFTCONDITION.MATCHTYPECODE,
                    MATCHINGGIFTCONDITION.MINMATCHPERGIFT,
                    MATCHINGGIFTCONDITION.MAXMATCHPERGIFT,
                    MATCHINGGIFTCONDITION.MAXMATCHANNUAL,
                    MATCHINGGIFTCONDITION.MAXMATCHTOTAL,
                    MATCHINGGIFTCONDITION.MATCHINGFACTOR,
                    MATCHINGGIFTCONDITION.ORGANIZATIONID as MGORGID
                from 
                    dbo.REVENUEMATCHINGGIFT
                    inner join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTION.ID = REVENUEMATCHINGGIFT.ID
                    inner join dbo.MATCHINGGIFTCONDITION on MATCHINGGIFTCONDITION.ORGANIZATIONID = FINANCIALTRANSACTION.CONSTITUENTID
                where
                    MGSOURCEREVENUEID = @ID
                    -- must filter matching gift conditions by revenue in REVENUEMATCHINGGIFT

                    -- previous code would return a row for each matching gift condition

                    and REVENUEMATCHINGGIFT. MATCHINGGIFTCONDITIONID = MATCHINGGIFTCONDITION. ID
                    and FINANCIALTRANSACTION.DELETEDON is null
                    and REVENUEMATCHINGGIFT.ID not in (select PLEDGEID from INSTALLMENTSPLITPAYMENT)
                    and REVENUEMATCHINGGIFT.ID not in (select WRITEOFF.REVENUEID from dbo.WRITEOFFSPLIT inner join dbo.WRITEOFF on WRITEOFF.ID = WRITEOFFSPLIT.WRITEOFFID)
                    and exists (select 1 from FINANCIALTRANSACTIONLINEITEM 
                                         inner join REVENUESPLIT_EXT on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID
                                         where FINANCIALTRANSACTIONID = FINANCIALTRANSACTION.ID and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null
                                         and REVENUESPLIT_EXT.TYPECODE = MATCHINGGIFTCONDITION.REVENUETYPECODE);

                if exists (select 1 from @MATCHINGGIFTS)
                begin
                    declare @MGORGID uniqueidentifier;
                    declare @MATCHINGGIFTCONDITIONID uniqueidentifier;
                    declare @MATCHTYPECODE tinyint;
                    declare @MINMATCHPERGIFT money;
                    declare @ORIGINALGIFTAMOUNT money;
                    declare @MGSPLITS xml;
                    declare @REVENUETYPECODE tinyint;
                    declare @APPLICATIONCODE tinyint = 0;
                    declare @CONSTITUENTID uniqueidentifier;
                    declare @RECEIPTAMOUNT money;

                    -- Get source revenue information

                    select
                        @CONSTITUENTID = FINANCIALTRANSACTION.CONSTITUENTID,
                        @TRANSACTIONCURRENCYID = FINANCIALTRANSACTION.TRANSACTIONCURRENCYID,
                        @BASECURRENCYID = isnull(REVENUE_EXT.NONPOSTABLEBASECURRENCYID, CURRENCYSET.BASECURRENCYID),
                        @BASEEXCHANGERATEID = FINANCIALTRANSACTION.BASEEXCHANGERATEID,
                        @RECEIPTAMOUNT = REVENUE_EXT.RECEIPTAMOUNT
                    from dbo.FINANCIALTRANSACTION
                    inner join dbo.REVENUE_EXT on FINANCIALTRANSACTION.ID = REVENUE_EXT.ID
                    inner join dbo.PDACCOUNTSYSTEM on FINANCIALTRANSACTION.PDACCOUNTSYSTEMID = PDACCOUNTSYSTEM.ID
                    inner join dbo.CURRENCYSET on PDACCOUNTSYSTEM.CURRENCYSETID = CURRENCYSET.ID
                    where FINANCIALTRANSACTION.ID = @ID
                        and FINANCIALTRANSACTION.DELETEDON is null;

                    select
                        @DECIMALDIGITS = DECIMALDIGITS,
                        @ROUNDINGTYPECODE = ROUNDINGTYPECODE
                    from dbo.CURRENCY
                    where ID = @TRANSACTIONCURRENCYID;

                    -- Clear the revenue amounts so that they don't affect total mg claims made to the organization

                    update dbo.FINANCIALTRANSACTION
                    set
                        BASEAMOUNT = 0,
                        TRANSACTIONAMOUNT = 0,
                        ORGAMOUNT = 0
                    where
                        exists (select 1 from @MATCHINGGIFTS MATCHINGGIFTS where MATCHINGGIFTS.ID = FINANCIALTRANSACTION.ID)
                        and FINANCIALTRANSACTION.DELETEDON is null

                    -- Clear all revenue splits so that they can be regenerated.

                    delete from dbo.REVENUESPLIT
                    where 
                        exists (
                            select 
                                1 
                            from 
                                dbo.REVENUEMATCHINGGIFT
                            where
                                REVENUEMATCHINGGIFT.MGSOURCEREVENUEID = @ID
                                and REVENUEMATCHINGGIFT.ID = REVENUESPLIT.REVENUEID
                                and REVENUEMATCHINGGIFT.MATCHINGGIFTCONDITIONID is not null
                                and REVENUEMATCHINGGIFT.ID not in (select PLEDGEID from INSTALLMENTSPLITPAYMENT)
                                and REVENUEMATCHINGGIFT.ID not in (select WRITEOFF.REVENUEID from dbo.WRITEOFFSPLIT inner join dbo.WRITEOFF on WRITEOFF.ID = WRITEOFFSPLIT.WRITEOFFID)

                        )

                    declare REVENUEMATCHINGGIFTDEFAULTCURSOR cursor local fast_forward for
                        select 
                            MATCHINGGIFTS.ID,
                            MATCHINGGIFTS.DATE,
                            MATCHINGGIFTS.MATCHINGGIFTCONDITIONID,
                            MATCHINGGIFTS.REVENUETYPECODE,
                            MATCHINGGIFTS.MATCHTYPECODE,
                            MATCHINGGIFTS.MINMATCHPERGIFT,
                            MATCHINGGIFTS.MAXMATCHPERGIFT,
                            MATCHINGGIFTS.MAXMATCHANNUAL,
                            MATCHINGGIFTS.MAXMATCHTOTAL,
                            MATCHINGGIFTS.MATCHINGFACTOR,
                            MATCHINGGIFTS.MGORGID
                        from 
                            @MATCHINGGIFTS MATCHINGGIFTS

                    open REVENUEMATCHINGGIFTDEFAULTCURSOR;
                    fetch next from REVENUEMATCHINGGIFTDEFAULTCURSOR into @REVENUEMATCHINGGIFTID, @DATE, @MATCHINGGIFTCONDITIONID, @REVENUETYPECODE, @MATCHTYPECODE, @MINMATCHPERGIFT, @MAXMATCHPERGIFT, @MATCHINGGIFTMAXMATCHANNUAL, @MATCHINGGIFTMAXMATCHTOTAL, @MATCHINGFACTOR, @MGORGID;

                    -- For each matching gift claim, regenerate the fields as if the system were auto-generating it.

                    while (@@FETCH_STATUS = 0)
                    begin

                        -- Bug 30719 - AdamBu 4/1/09 - Only generate matching gift claims based on RECEIPTAMOUNT 

                        -- if all the splits in the revenue should be matched

                        declare @GENERATECLAIMS bit = 1;

                        -- Initialize/Reset variables                        

                        set @MATCHINGGIFTCONDITIONID = null;
                        set @CAPPEDNEWTRANSACTIONAMOUNT = 0;
                        set @APPLICATIONCODE = 0;

                        -- Recalculate the claim amount based on the revenue type code and get application code

                        select @ORIGINALGIFTAMOUNT = sum(TRANSACTIONAMOUNT)
                        from dbo.FINANCIALTRANSACTIONLINEITEM
                        inner join dbo.REVENUESPLIT_EXT on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID
                        where FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = @ID and REVENUESPLIT_EXT.TYPECODE = @REVENUETYPECODE
                            and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null and FINANCIALTRANSACTIONLINEITEM.TYPECODE <> 1;

                        --AKG CR299627-050108 Determine if the full amount or only the receipt amount should be used for claim generation

                        if @MATCHTYPECODE = 1 and exists(select FINANCIALTRANSACTIONLINEITEM.ID from dbo.FINANCIALTRANSACTIONLINEITEM inner join dbo.REVENUESPLIT_EXT on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID where FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID=@ID and (case when REVENUESPLIT_EXT.TYPECODE = 17 then 9 else REVENUESPLIT_EXT.TYPECODE end) = @REVENUETYPECODE and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null and FINANCIALTRANSACTIONLINEITEM.TYPECODE <> 1)
                        begin
                            set @CAPPEDNEWTRANSACTIONAMOUNT = dbo.UFN_CURRENCY_ROUND(@RECEIPTAMOUNT * @MATCHINGFACTOR, @DECIMALDIGITS, @ROUNDINGTYPECODE);
                            if exists(
                                select FINANCIALTRANSACTIONLINEITEM.ID
                                from dbo.FINANCIALTRANSACTIONLINEITEM
                                inner join dbo.REVENUESPLIT_EXT on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID
                                where FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = @ID and (case when REVENUESPLIT_EXT.TYPECODE = 17 then 9 else REVENUESPLIT_EXT.TYPECODE end) <> @REVENUETYPECODE
                                    and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null and FINANCIALTRANSACTIONLINEITEM.TYPECODE <> 1
                            )
                                --If we only match tax deductible portions, but not all the splits on a revenue should

                                --    be matched, then we don't know how much to match for, so don't auto-generate claims.

                                set @GENERATECLAIMS = 0;
                        end
                        else
                        begin
                            set @CAPPEDNEWTRANSACTIONAMOUNT = dbo.UFN_CURRENCY_ROUND(@ORIGINALGIFTAMOUNT * @MATCHINGFACTOR, @DECIMALDIGITS, @ROUNDINGTYPECODE);
                        end

                        -- Adjust the amount based on the organization's max, min, and eligibility settings.

                        set @CAPPEDNEWTRANSACTIONAMOUNT = dbo.UFN_MATCHINGGIFTPLEDGE_CALCULATEMGAMOUNTINCURRENCY(@CONSTITUENTID,@MGORGID,@CAPPEDNEWTRANSACTIONAMOUNT,@DATE,@MAXMATCHPERGIFT,@MATCHINGGIFTMAXMATCHANNUAL,@MATCHINGGIFTMAXMATCHTOTAL,@TRANSACTIONCURRENCYID);

                        --If we cannot automatically generate claims, flag the record as eligible.

                        if @GENERATECLAIMS = 0 and (@CAPPEDNEWTRANSACTIONAMOUNT >= @MINMATCHPERGIFT) and @CAPPEDNEWTRANSACTIONAMOUNT > 0
                            update dbo.REVENUE_EXT
                            set ELIGIBLEFORMATCHINGGIFTCLAIM = 1,
                                CHANGEDBYID = @CHANGEAGENTID,
                                DATECHANGED = getdate()
                            where ID = @ID
                        else
                            update dbo.REVENUE_EXT
                            set ELIGIBLEFORMATCHINGGIFTCLAIM = 0,
                                CHANGEDBYID = @CHANGEAGENTID,
                                DATECHANGED = getdate()
                            where ID = @ID

                        -- If a claim would not have been originally generated, set the amount to 0. Leave the claim around for record keeping.

                        if (@CAPPEDNEWTRANSACTIONAMOUNT < @MINMATCHPERGIFT) or @CAPPEDNEWTRANSACTIONAMOUNT < 0 or @GENERATECLAIMS = 0
                            set @CAPPEDNEWTRANSACTIONAMOUNT = 0;

                        --Get base and organization amounts based on currency, exchange rates, and transaction amount.


                        exec dbo.USP_CURRENCY_GETCURRENCYVALUES 
                            @AMOUNT=@CAPPEDNEWTRANSACTIONAMOUNT, 
                            @DATE=@DATE,    
                            @BASECURRENCYID=@BASECURRENCYID, 
                            @BASEEXCHANGERATEID=@BASEEXCHANGERATEID, 
                            @TRANSACTIONCURRENCYID=@TRANSACTIONCURRENCYID,
                            @BASEAMOUNT=@NEWBASEAMOUNT output,
                            @ORGANIZATIONAMOUNT=@NEWORGANIZATIONAMOUNT output,
                            @ORGANIZATIONEXCHANGERATEID=@ORGANIZATIONEXCHANGERATEID output

                        update dbo.REVENUE set
                            TRANSACTIONAMOUNT = @CAPPEDNEWTRANSACTIONAMOUNT,
                            AMOUNT = @NEWBASEAMOUNT,
                            ORGANIZATIONAMOUNT = @NEWORGANIZATIONAMOUNT,
                            ORGANIZATIONEXCHANGERATEID = @ORGANIZATIONEXCHANGERATEID,
                            TRANSACTIONCURRENCYID = @TRANSACTIONCURRENCYID,
                            BASECURRENCYID = @BASECURRENCYID,
                            BASEEXCHANGERATEID = @BASEEXCHANGERATEID,
                            CHANGEDBYID = @CHANGEAGENTID,
                            DATECHANGED = @CURRENTDATE
                        where ID = @REVENUEMATCHINGGIFTID;  

                        --Update the installments tables

                        update dbo.INSTALLMENT set 
                            TRANSACTIONAMOUNT = @CAPPEDNEWTRANSACTIONAMOUNT,
                            AMOUNT = @NEWBASEAMOUNT,
                            ORGANIZATIONAMOUNT = @NEWORGANIZATIONAMOUNT,
                            ORGANIZATIONEXCHANGERATEID = @ORGANIZATIONEXCHANGERATEID,
                            TRANSACTIONCURRENCYID = @TRANSACTIONCURRENCYID,
                            BASECURRENCYID = @BASECURRENCYID,
                            BASEEXCHANGERATEID = @BASEEXCHANGERATEID,
                            CHANGEDBYID = @CHANGEAGENTID,
                            DATECHANGED = @CURRENTDATE
                        where REVENUEID = @REVENUEMATCHINGGIFTID;          

                        if @GENERATECLAIMS = 1
                        begin
                            -- Get new MG splits

                            -- get the splits for the current type code                        

                            select @SPLITS = (
                                select sum(FINANCIALTRANSACTIONLINEITEM.TRANSACTIONAMOUNT) AMOUNT, REVENUESPLIT_EXT.DESIGNATIONID, REVENUESPLIT_EXT.TYPECODE, @TRANSACTIONCURRENCYID [TRANSACTIONCURRENCYID], REVENUESPLIT_EXT.APPLICATIONCODE
                                from dbo.FINANCIALTRANSACTIONLINEITEM
                                inner join dbo.REVENUESPLIT_EXT on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID
                                where FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = @ID and REVENUESPLIT_EXT.TYPECODE = @REVENUETYPECODE
                                    and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null and FINANCIALTRANSACTIONLINEITEM.TYPECODE <> 1
                                group by REVENUESPLIT_EXT.DESIGNATIONID, REVENUESPLIT_EXT.TYPECODE, REVENUESPLIT_EXT.APPLICATIONCODE
                                for xml raw('ITEM'),type,elements,root('SPLITS'),binary base64
                            );

                            set @MGSPLITS = dbo.UFN_MATCHINGGIFT_GETAUTOADDSPLITS_3(@ORIGINALGIFTAMOUNT,@CAPPEDNEWTRANSACTIONAMOUNT,@SPLITS,@REVENUETYPECODE)

                            -- add matching gift            

                            exec dbo.USP_MATCHINGGIFTCLAIM_READD @REVENUEMATCHINGGIFTID, @CHANGEAGENTID, @ID, @MGORGID, @DATE, @CAPPEDNEWTRANSACTIONAMOUNT, @MGSPLITS, @MATCHINGGIFTCONDITIONID, @TRANSACTIONCURRENCYID, @TRANSACTIONCURRENCYID, null;

                            delete from dbo.INSTALLMENTSPLIT where PLEDGEID = @REVENUEMATCHINGGIFTID;
                            exec dbo.USP_PLEDGE_GENERATEINSTALLMENTSPLITS @REVENUEMATCHINGGIFTID, @CHANGEAGENTID, @CURRENTDATE;
                        end

                        fetch next from REVENUEMATCHINGGIFTDEFAULTCURSOR into @REVENUEMATCHINGGIFTID, @DATE, @MATCHINGGIFTCONDITIONID, @REVENUETYPECODE, @MATCHTYPECODE, @MINMATCHPERGIFT, @MAXMATCHPERGIFT, @MATCHINGGIFTMAXMATCHANNUAL, @MATCHINGGIFTMAXMATCHTOTAL, @MATCHINGFACTOR, @MGORGID;
                    end

                    close REVENUEMATCHINGGIFTDEFAULTCURSOR;
                    deallocate REVENUEMATCHINGGIFTDEFAULTCURSOR; 
                end

            end
        end