UFN_REVENUEBATCH_GETDEFAULTMATCHINGGIFTS_FORCONSTITUENT_3

            /****
                NOTE: 
                    This function is used by UFN_REVENUEBATCH_GETMATCHINGGIFTCONDITIONS to avoid code duplication. You almost certainly want to call UFN_REVENUEBATCH_GETMATCHINGGIFTCONDITIONS
                    instead of this function. 

            ****/
            CREATE function dbo.UFN_REVENUEBATCH_GETDEFAULTMATCHINGGIFTS_FORCONSTITUENT_3
            (
                @CONSTITUENTID uniqueidentifier,
                @DATE datetime,
                @DONATIONAMOUNT money,
                @DONATIONRECEIPTAMOUNT money,
                @DONATIONSPLITS xml,
                @DONATIONTRANSACTIONCURRENCYID uniqueidentifier,
                @REVENUESTREAMS xml = null
            )
            returns @R table
            (
                ORGANIZATIONID uniqueidentifier,
                RELATIONSHIPID uniqueidentifier,
                MATCHINGGIFTCONDITIONTYPEID uniqueidentifier,
                AMOUNT money,
                DATE datetime,
                SPLITS xml,
                COULDNOTDEFAULTMATCHINGGIFT bit
            )
            as 
            begin
                declare @MATCHINGGIFTCONDITIONS table
                (
                    RELATIONSHIPID uniqueidentifier,
                    ORGANIZATIONID uniqueidentifier,
                    MATCHINGGIFTCONDITIONID uniqueidentifier,
                    MATCHINGFACTOR decimal(5,2),
                    MINMATCHPERGIFT money,
                    MAXMATCHPERGIFT money,
                    MAXMATCHANNUAL money,
                    MAXMATCHTOTAL money,
                    MATCHTYPECODE tinyint,
                    REVENUETYPECODE tinyint
                );

                declare @DONATIONSPLITS_T table
                (
                    ID uniqueidentifier,
                    DESIGNATIONID uniqueidentifier,
                    DESIGNATIONTRANSLATION nvarchar(512),
                    AMOUNT money,
                    SEQUENCE int,
                    APPLICATIONCODE tinyint,
                    TYPECODE tinyint,
                    TRANSACTIONCURRENCYID uniqueidentifier
                );

                declare @RELATIONSHIPID uniqueidentifier;
                declare @ORGANIZATIONID uniqueidentifier;
                declare @MATCHINGGIFTCONDITIONID uniqueidentifier;
                declare @MATCHINGFACTOR decimal(5,2);
                declare @MINMATCHPERGIFT money;
                declare @MAXMATCHPERGIFT money;
                declare @MAXMATCHANNUAL money;
                declare @MAXMATCHTOTAL money;
                declare @MATCHTYPECODE tinyint;
                declare @REVENUETYPECODE tinyint;
                declare @CONDITIONBASECURRENCYID uniqueidentifier;

                declare @APPLICATIONCODE tinyint;

                declare @AMOUNT money;
                declare @MGAMOUNT money;

                declare @MGSPLITS xml;

                if @DONATIONTRANSACTIONCURRENCYID is null set @DONATIONTRANSACTIONCURRENCYID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();

                set @AMOUNT = @DONATIONAMOUNT; --We'll be modifying the amount and I want to have a pristine copy available for the future


                insert into @DONATIONSPLITS_T(ID, DESIGNATIONID, DESIGNATIONTRANSLATION, AMOUNT, SEQUENCE, APPLICATIONCODE, TYPECODE, TRANSACTIONCURRENCYID)
                    select 
                        T.c.value('(ID)[1]', 'uniqueidentifier'), 
                        T.c.value('(DESIGNATIONID)[1]', 'uniqueidentifier'), 
                        DESIGNATION.NAME,
                        T.c.value('(AMOUNT)[1]', 'money'), 
                        T.c.value('(SEQUENCE)[1]', 'int'), 
                        T.c.value('(APPLICATIONCODE)[1]', 'tinyint'), 
                        case T.c.value('(TYPECODE)[1]', 'tinyint') when 3 then 9 else T.c.value('(TYPECODE)[1]', 'tinyint') end as TYPECODE, -- 3 is Sponsorship additional donation

                        T.c.value('(TRANSACTIONCURRENCYID)[1]', 'uniqueidentifier')
                    from @DONATIONSPLITS.nodes('/SPLITS/ITEM') T(c)
                    inner join DESIGNATION on DESIGNATION.ID = T.c.value('(DESIGNATIONID)[1]', 'uniqueidentifier');

                if @REVENUESTREAMS is not null
                    insert into @DONATIONSPLITS_T(ID, DESIGNATIONID, DESIGNATIONTRANSLATION, AMOUNT, SEQUENCE, APPLICATIONCODE, TYPECODE, TRANSACTIONCURRENCYID)
                        select 
                            RECURRINGGIFTSPLIT.ID,
                            RECURRINGGIFTSPLIT.DESIGNATIONID,
                            DESIGNATION.NAME,
                            RECURRINGGIFTSPLIT.AMOUNT,
                            RECURRINGGIFTSPLIT.SEQUENCE,
                            RECURRINGGIFTSPLIT.APPLICATIONCODE, 
                            RECURRINGGIFTSPLIT.TYPECODE,
                            RECURRINGGIFTSPLIT.TRANSACTIONCURRENCYID
                        from
                        (
                            select
                                T.c.value('(APPLICATIONID)[1]', 'uniqueidentifier') as ID,
                                PRORATEDSPLITS.DESIGNATIONID,
                                PRORATEDSPLITS.AMOUNT,
                                0 as SEQUENCE,
                                0 as APPLICATIONCODE,
                                PRORATEDSPLITS.TYPECODE,
                                T.c.value('(TRANSACTIONCURRENCYID)[1]', 'uniqueidentifier') as TRANSACTIONCURRENCYID
                            from
                                @REVENUESTREAMS.nodes('/REVENUESTREAMS/ITEM') T(c)
                                outer apply dbo.UFN_REVENUE_GETPRORATEDSPLITS_2(T.c.value('(APPLICATIONID)[1]', 'uniqueidentifier'), T.c.value('(APPLIED)[1]', 'money'), T.c.value('(TRANSACTIONCURRENCYID)[1]', 'uniqueidentifier')) PRORATEDSPLITS
                                where
                                    T.c.value('(APPLIED)[1]', 'money') > 0    --Filter Streams with no applied amount

                                    --Include all applied streams regardless of type to ensure mixed payments to not get MG claims Autogenerated

                                    --and

                                    --T.c.value('(TYPECODE)[1]', 'tinyint') in (1, 2, 4, 10) -- Pledge, Recurring Gift, Planned Gift, Donor Challenge Claim

                        ) as RECURRINGGIFTSPLIT
                        left join DESIGNATION on DESIGNATION.ID = RECURRINGGIFTSPLIT.DESIGNATIONID;

                insert into @MATCHINGGIFTCONDITIONS(RELATIONSHIPID, ORGANIZATIONID, MATCHINGGIFTCONDITIONID, MATCHINGFACTOR, MINMATCHPERGIFT, MAXMATCHPERGIFT, MAXMATCHANNUAL, MAXMATCHTOTAL, MATCHTYPECODE, REVENUETYPECODE)
                    select RELATIONSHIPID, ORGANIZATIONID, MATCHINGGIFTCONDITIONID, MATCHINGFACTOR, MINMATCHPERGIFT, MAXMATCHPERGIFT, MAXMATCHANNUAL, MAXMATCHTOTAL, MATCHTYPECODE, REVENUETYPECODE
                    from dbo.UFN_REVENUEBATCH_GETMATCHINGGIFTCONDITIONS(@CONSTITUENTID);

                declare CUR_MATCHINGGIFTCONDITIONS cursor local fast_forward for 
                    select 
                        [CONDITIONS].RELATIONSHIPID, 
                        [CONDITIONS].ORGANIZATIONID, 
                        [CONDITIONS].MATCHINGGIFTCONDITIONID, 
                        [CONDITIONS].MATCHINGFACTOR, 
                        [CONDITIONS].MINMATCHPERGIFT, 
                        [CONDITIONS].MAXMATCHPERGIFT, 
                        [CONDITIONS].MAXMATCHANNUAL, 
                        [CONDITIONS].MAXMATCHTOTAL, 
                        [CONDITIONS].MATCHTYPECODE, 
                        [CONDITIONS].REVENUETYPECODE, 
                        MATCHINGGIFTCONDITION.BASECURRENCYID
                    from 
                        @MATCHINGGIFTCONDITIONS [CONDITIONS]
                        inner join dbo.MATCHINGGIFTCONDITION on [CONDITIONS].MATCHINGGIFTCONDITIONID = MATCHINGGIFTCONDITION.ID

                open CUR_MATCHINGGIFTCONDITIONS;
                fetch next from CUR_MATCHINGGIFTCONDITIONS into @RELATIONSHIPID, @ORGANIZATIONID, @MATCHINGGIFTCONDITIONID, @MATCHINGFACTOR, @MINMATCHPERGIFT, @MAXMATCHPERGIFT, 
                                                                @MAXMATCHANNUAL, @MAXMATCHTOTAL, @MATCHTYPECODE, @REVENUETYPECODE, @CONDITIONBASECURRENCYID;

                while @@FETCH_STATUS = 0
                begin

                    --If the MG condition base currency does not match the donation transaction currency, do not default MGs

                    if @CONDITIONBASECURRENCYID = @DONATIONTRANSACTIONCURRENCYID
                    begin
                        declare @GENERATECLAIMS bit;
                        set @GENERATECLAIMS = 1; 

                        select @AMOUNT = coalesce(sum(AMOUNT), 0)
                        from @DONATIONSPLITS_T where TYPECODE = @REVENUETYPECODE;

                        --check if we need to apply the full amount or the tax portion

                        if @MATCHTYPECODE = 1
                        begin
                            if exists(select ID from @DONATIONSPLITS_T where TYPECODE <> @REVENUETYPECODE) or @AMOUNT = 0
                                set @GENERATECLAIMS = 0; --If we're only matching the tax-deductible portions but some of the splits aren't to be matched, we can't figure out how much to match

                            else
                                set @MGAMOUNT = @DONATIONRECEIPTAMOUNT * @MATCHINGFACTOR;
                        end
                        else
                        begin
                            set @MGAMOUNT = @AMOUNT * @MATCHINGFACTOR;
                        end

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

                        --REVISIT: This function will not take batch gifts into account. Not sure how to deal with it. 

                        set @MGAMOUNT = dbo.UFN_MATCHINGGIFTPLEDGE_CALCULATEMGAMOUNT(@CONSTITUENTID, @ORGANIZATIONID, @MGAMOUNT, @DATE, @MAXMATCHPERGIFT, @MAXMATCHANNUAL, @MAXMATCHTOTAL);

                        if @MGAMOUNT >= @MINMATCHPERGIFT and @MGAMOUNT > 0 and @GENERATECLAIMS = 1
                        begin
                            --Get new MG splits

                            select @MGSPLITS = (select sum(AMOUNT) as AMOUNT, DESIGNATIONID, DESIGNATIONTRANSLATION, TYPECODE, TRANSACTIONCURRENCYID
                                                from @DONATIONSPLITS_T where TYPECODE = @REVENUETYPECODE
                                                group by DESIGNATIONID, DESIGNATIONTRANSLATION, TYPECODE, TRANSACTIONCURRENCYID
                                                for xml raw('ITEM'),type,elements,root('SPLITS'),binary base64);

                            select @APPLICATIONCODE = APPLICATIONCODE from @DONATIONSPLITS_T where TYPECODE = @REVENUETYPECODE;

                            set @MGSPLITS = dbo.UFN_REVENUEBATCH_GETAUTOMGSPLITS(@AMOUNT, @MGAMOUNT, @MGSPLITS, @REVENUETYPECODE, @APPLICATIONCODE);

                            insert into @R(ORGANIZATIONID, RELATIONSHIPID, MATCHINGGIFTCONDITIONTYPEID, AMOUNT, DATE, SPLITS, COULDNOTDEFAULTMATCHINGGIFT)
                                values(@ORGANIZATIONID, @RELATIONSHIPID, @MATCHINGGIFTCONDITIONID, @MGAMOUNT, @DATE, @MGSPLITS, 0);

                        end
                        else
                        begin
                            if @GENERATECLAIMS = 0 or @MGAMOUNT < @MINMATCHPERGIFT
                            begin
                                --For some reason, we've decided not to default claims. Indicate this fact.

                                insert into @R(ORGANIZATIONID, RELATIONSHIPID, MATCHINGGIFTCONDITIONTYPEID, AMOUNT, DATE, SPLITS, COULDNOTDEFAULTMATCHINGGIFT)
                                    values (@ORGANIZATIONID, @RELATIONSHIPID, @MATCHINGGIFTCONDITIONID, null, null, null, 1);
                            end
                        end
                    end

                    fetch next from CUR_MATCHINGGIFTCONDITIONS into @RELATIONSHIPID, @ORGANIZATIONID, @MATCHINGGIFTCONDITIONID, @MATCHINGFACTOR, @MINMATCHPERGIFT, @MAXMATCHPERGIFT, 
                                                                    @MAXMATCHANNUAL, @MAXMATCHTOTAL, @MATCHTYPECODE, @REVENUETYPECODE, @CONDITIONBASECURRENCYID;
                end

                --When a cursor is used, it should be explicitly closed/deallocated in case of blocking or USP running long

                close CUR_MATCHINGGIFTCONDITIONS;
                deallocate CUR_MATCHINGGIFTCONDITIONS;

                return;
            end