USP_MATCHINGGIFTCLAIM_RECREATE

            CREATE procedure dbo.USP_MATCHINGGIFTCLAIM_RECREATE
            (
                @CHANGEAGENTID uniqueidentifier, 
                @GIFTID uniqueidentifier, 
                @DATE datetime
            )
            as begin
                set nocount on;

                declare @MGORGID uniqueidentifier;
                declare @MATCHINGGIFTCONDITIONID uniqueidentifier;
                declare @MATCHINGGIFTAMOUNT money;
                declare @MATCHINGGIFTMINAMOUNT money;
                declare @MATCHINGGIFTMAXAMOUNT money;
                declare @MATCHINGGIFTMAXMATCHANNUAL money;
                declare @MATCHINGGIFTMAXMATCHTOTAL money;
                declare @MATCHINGGIFTFACTOR decimal(30,2);
                declare @MGSPLITS xml;
                declare @MATCHTYPECODE tinyint;
                declare @REVENUETYPECODE tinyint;
                declare @APPLICATIONCODE tinyint = 0;
                declare @TRANSACTIONCURRENCYID uniqueidentifier;
                declare @BASECURRENCYID uniqueidentifier;
                declare @BASEEXCHANGERATEID uniqueidentifier;
                declare @SPLITS xml;
                declare @CONSTITUENTID uniqueidentifier;
                declare @RECEIPTAMOUNT money;
                declare @CURRENCYDECIMALDIGITS tinyint;
                declare @CURRENCYROUNDINGTYPECODE tinyint;
                declare @REVENUEMATCHINGGIFTID uniqueidentifier;
                declare @AMOUNT money;

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

                select
                    @TRANSACTIONCURRENCYID = REVENUE.TRANSACTIONCURRENCYID,
                    @BASECURRENCYID = REVENUE.BASECURRENCYID,
                    @BASEEXCHANGERATEID = REVENUE.BASEEXCHANGERATEID,
                    @CONSTITUENTID = REVENUE.CONSTITUENTID,
                    @RECEIPTAMOUNT = REVENUE.RECEIPTAMOUNT
                from
                    dbo.REVENUE
                where ID = @GIFTID;

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


                declare REVENUEMATCHINGGIFTDEFAULTCURSOR cursor local fast_forward for
                    select
                        REVENUEMATCHINGGIFT.ID,
                        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.MATCHINGGIFTCONDITION on MATCHINGGIFTCONDITION.ID = REVENUEMATCHINGGIFT.MATCHINGGIFTCONDITIONID
                    where
                        [MGSOURCEREVENUEID] = @GIFTID
                        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);


                open REVENUEMATCHINGGIFTDEFAULTCURSOR;
                fetch next from REVENUEMATCHINGGIFTDEFAULTCURSOR into @REVENUEMATCHINGGIFTID, @MATCHINGGIFTCONDITIONID, @REVENUETYPECODE, @MATCHTYPECODE, @MATCHINGGIFTMINAMOUNT, @MATCHINGGIFTMAXAMOUNT, @MATCHINGGIFTMAXMATCHANNUAL, @MATCHINGGIFTMAXMATCHTOTAL, @MATCHINGGIFTFACTOR, @MGORGID;

                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;

                    -- calculate matching gift amount                        

                    -- recalculate matchgift amount based on the revenue type code and get application code

                    select @AMOUNT = sum(TRANSACTIONAMOUNT)
                    from dbo.REVENUESPLIT
                    where REVENUEID = @GIFTID and REVENUESPLIT.TYPECODE = @REVENUETYPECODE;

                    --AKG CR299627-050108 check if need to apply full amount or tax portion

                    if @MATCHTYPECODE = 1 and exists(select ID from dbo.REVENUESPLIT where REVENUEID=@GIFTID and TYPECODE = @REVENUETYPECODE)
                    begin
                        if exists(
                            select ID
                            from dbo.REVENUESPLIT
                            where REVENUEID=@GIFTID and TYPECODE <> @REVENUETYPECODE
                        )
                        begin
                            --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 @MATCHINGGIFTAMOUNT = dbo.UFN_CURRENCY_ROUND(@RECEIPTAMOUNT * @MATCHINGGIFTFACTOR, @CURRENCYDECIMALDIGITS, @CURRENCYROUNDINGTYPECODE);
                        end
                    end
                    else
                    begin
                        set @MATCHINGGIFTAMOUNT = dbo.UFN_CURRENCY_ROUND(@AMOUNT * @MATCHINGGIFTFACTOR, @CURRENCYDECIMALDIGITS, @CURRENCYROUNDINGTYPECODE);
                    end

                    -- determine if the matching gift needs to be adjusted based on the conditions of the MG organization

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

                    if (@MATCHINGGIFTAMOUNT >= @MATCHINGGIFTMINAMOUNT) and @MATCHINGGIFTAMOUNT > 0 and @GENERATECLAIMS = 1
                    begin
                        -- Get new MG splits

                        -- get the splits for the current type code                        

                        select @SPLITS = (
                            select sum(TRANSACTIONAMOUNT) AMOUNT, DESIGNATIONID, TYPECODE, @TRANSACTIONCURRENCYID [TRANSACTIONCURRENCYID], REVENUESPLIT.APPLICATIONCODE
                            from dbo.REVENUESPLIT
                            where REVENUEID = @GIFTID and REVENUESPLIT.TYPECODE = @REVENUETYPECODE
                            group by DESIGNATIONID, REVENUESPLIT.TYPECODE, REVENUESPLIT.APPLICATIONCODE
                            for xml raw('ITEM'),type,elements,root('SPLITS'),binary base64
                        );

                        set @MGSPLITS = dbo.UFN_MATCHINGGIFT_GETAUTOADDSPLITS_3(@AMOUNT,@MATCHINGGIFTAMOUNT,@SPLITS,@REVENUETYPECODE)

                        -- add matching gift            

                        exec dbo.USP_MATCHINGGIFTCLAIM_READD @REVENUEMATCHINGGIFTID, @CHANGEAGENTID, @GIFTID, @MGORGID, @DATE, @MATCHINGGIFTAMOUNT, @MGSPLITS, @MATCHINGGIFTCONDITIONID, @TRANSACTIONCURRENCYID, @TRANSACTIONCURRENCYID, null;
                    end
                    else
                    begin
                        if @GENERATECLAIMS = 0
                        begin
                            --For some reason, we've decided to not create claims.  Flag the record as eligible.

                            update dbo.REVENUE
                            set ELIGIBLEFORMATCHINGGIFTCLAIM = 1,
                                CHANGEDBYID = @CHANGEAGENTID,
                                DATECHANGED = getdate()
                            where ID = @GIFTID
                        end
                    end

                    --clean up                            

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

                    fetch next from REVENUEMATCHINGGIFTDEFAULTCURSOR into @REVENUEMATCHINGGIFTID, @MATCHINGGIFTCONDITIONID, @REVENUETYPECODE, @MATCHTYPECODE, @MATCHINGGIFTMINAMOUNT, @MATCHINGGIFTMAXAMOUNT, @MATCHINGGIFTMAXMATCHANNUAL, @MATCHINGGIFTMAXMATCHTOTAL, @MATCHINGGIFTFACTOR, @MGORGID;
                end

                close REVENUEMATCHINGGIFTDEFAULTCURSOR;
                deallocate REVENUEMATCHINGGIFTDEFAULTCURSOR;

                return 0;

            end