UFN_MATCHINGGIFT_GETAUTOADDSPLITS_3

            CREATE function dbo.UFN_MATCHINGGIFT_GETAUTOADDSPLITS_3
            (
                @AMOUNT money,
                @MATCHINGGIFTAMOUNT money,
                @SPLITS xml,
                @TYPECODE tinyint
            )
            returns xml
            as
            BEGIN
                -- Update the xml so that it has the proper root element

                -- 17 and 9 are both sponsorships (sponsorship additional gift and sponsorship)

                set @SPLITS = (
                    select AMOUNT, DESIGNATIONID as ID, TRANSACTIONCURRENCYID, APPLICATIONCODE
                        from dbo.UFN_REVENUE_GETSPLITS_2_FROMITEMLISTXML(@SPLITS)
                        where  (case when TYPECODE = 17 then 9 else TYPECODE end) = @TYPECODE
                        for xml raw('ITEM'),type,elements,root('AMOUNTSTOPRORATE'),binary base64
                )

                declare @DECIMALDIGITS tinyint = 2;
                select
                    @DECIMALDIGITS = DECIMALDIGITS
                from dbo.CURRENCY
                where CURRENCY.ID = @SPLITS.value('(/AMOUNTSTOPRORATE/ITEM/TRANSACTIONCURRENCYID)[1]', 'uniqueidentifier');

            -- Added  to select distinct items out of duplicates

            declare @FINDDISTINCTITEMSFROMSPLIT table 
                ( 
                 APPLICATIONCODE       tinyint, 
                 TRANSACTIONCURRENCYID uniqueidentifier,
                 DESIGNATIONID         uniqueidentifier
                ); 

            insert into @FINDDISTINCTITEMSFROMSPLIT 
            select distinct ORIGINALSPLIT.item.value('APPLICATIONCODE[1]', 'tinyint') 
                            APPLICATIONCODE, 
                            ORIGINALSPLIT.item.value('TRANSACTIONCURRENCYID[1]', 'uniqueidentifier') 
                            [TRANSACTIONCURRENCYID], 
                            ORIGINALSPLIT.item.value('ID[1]', 'uniqueidentifier') 
            from   @SPLITS.nodes('/AMOUNTSTOPRORATE/ITEM') ORIGINALSPLIT(item);

            return 
                (
                select 
                    AMOUNT, 
                    DESIGNATIONID, 
                    @TYPECODE as TYPECODE,
                    APPLICATIONCODE,
                    TRANSACTIONCURRENCYID
                     from   (select sum(PRORATEDSPLIT.AMOUNT) AMOUNT, 
                                            PRORATEDSPLIT.id as DESIGNATIONID, 
                                            ITEMTABLE.APPLICATIONCODE, 
                                            ITEMTABLE.[TRANSACTIONCURRENCYID] 
                                    from dbo.UFN_SPLITS_PRORATEAMOUNTS(
                                            @AMOUNT, 
                                            @MATCHINGGIFTAMOUNT, 
                                            @DECIMALDIGITS, 
                                            @SPLITS) PRORATEDSPLIT 
                                    inner join @FINDDISTINCTITEMSFROMSPLIT ITEMTABLE 
                                            on ITEMTABLE.DESIGNATIONID = PRORATEDSPLIT.ID 
                                    group by PRORATEDSPLIT.ID, 
                                    APPLICATIONCODE, 
                                    [TRANSACTIONCURRENCYID]) as SPLITDESIGNATION
                     for xml raw('ITEM'), type, elements, root('SPLITS'), binary base64
                     );
            END