UFN_REVENUE_BUILDSPLITSFOROPPORTUNITYGRANTAWARD_2

            CREATE function dbo.UFN_REVENUE_BUILDSPLITSFOROPPORTUNITYGRANTAWARD_2
            (
                @OPPORTUNITYID uniqueidentifier,
                @AMOUNT money,
                @TRANSACTIONCURRENCYID uniqueidentifier
            ) returns xml as
            begin

                declare @OUTSTANDINGDESIGNATIONS table
                (
                    DESIGNATIONID uniqueidentifier,
                    AMOUNT money
                );

                declare @BASECURRENCYID uniqueidentifier;
                select top 1 @BASECURRENCYID = BASECURRENCYID from dbo.OPPORTUNITYDESIGNATION where OPPORTUNITYDESIGNATION.OPPORTUNITYID = @OPPORTUNITYID;

                insert into @OUTSTANDINGDESIGNATIONS
                select
                    OD.DESIGNATIONID,
                    OD.AMOUNT - coalesce((
                        select
                            sum
                            (
                                -- When the base currency of the designation does not match the transaction currency of the split,

                                -- we need to convert the split amount to the designation currency in order to perform the subtraction

                                -- on like currencies.

                                case when @BASECURRENCYID = RS.TRANSACTIONCURRENCYID        
                                    then RS.TRANSACTIONAMOUNT
                                    else dbo.UFN_CURRENCY_CONVERT(RS.TRANSACTIONAMOUNT, dbo.UFN_CURRENCYEXCHANGERATE_GETLATEST(RS.TRANSACTIONCURRENCYID, @BASECURRENCYID, REVENUE.DATE, 1, null)) 
                                end
                            )
                        from
                            dbo.REVENUEOPPORTUNITY RO
                            inner join dbo.REVENUESPLIT RS on RS.ID = RO.ID
                            inner join dbo.REVENUE on RS.REVENUEID = REVENUE.ID
                        where
                            RO.OPPORTUNITYID = OD.OPPORTUNITYID
                            and RS.DESIGNATIONID = OD.DESIGNATIONID
                    ), 0)
                from
                    dbo.OPPORTUNITYDESIGNATION OD
                where
                    OD.OPPORTUNITYID = @OPPORTUNITYID;

                delete from @OUTSTANDINGDESIGNATIONS where AMOUNT <= 0;

                declare @TOTALOUTSTANDINGAMOUNT money;
                select @TOTALOUTSTANDINGAMOUNT = sum(AMOUNT) from @OUTSTANDINGDESIGNATIONS;

                declare @TRANSACTIONEXCHANGERATEID uniqueidentifier;
                set @TRANSACTIONEXCHANGERATEID = dbo.UFN_CURRENCYEXCHANGERATE_GETLATEST(@BASECURRENCYID, @TRANSACTIONCURRENCYID, getdate(), 1, null);

                declare @SPLITS xml;
                if @AMOUNT >= @TOTALOUTSTANDINGAMOUNT
                    set @SPLITS = 
                    (
                        select 
                            null [ID], 
                            DESIGNATIONID, 
                            -- When the base currency of the designation does not match the transaction currency of the split,

                            -- we need to convert the designation amount to the transaction currency

                            case when @BASECURRENCYID = @TRANSACTIONCURRENCYID
                                then AMOUNT
                                else dbo.UFN_CURRENCY_CONVERT(AMOUNT, @TRANSACTIONEXCHANGERATEID) 
                            end as AMOUNT,
                            3 TYPECODE,
                            8 APPLICATIONCODE,
                            @TRANSACTIONCURRENCYID TRANSACTIONCURRENCYID
                        from 
                            @OUTSTANDINGDESIGNATIONS
                        group by 
                            DESIGNATIONID, AMOUNT
                        for xml raw('ITEM'), type, elements, root('SPLITS'), BINARY BASE64
                    );
                else
                    set @SPLITS = 
                    (
                        select 
                            null [ID], 
                            DESIGNATIONID,
                            -- When the base currency of the designation does not match the transaction currency of the split,

                            -- we need to convert the designation amount to the transaction currency

                            case when @BASECURRENCYID = @TRANSACTIONCURRENCYID
                                then ((AMOUNT / @TOTALOUTSTANDINGAMOUNT) * @AMOUNT)
                                else dbo.UFN_CURRENCY_CONVERT(((AMOUNT / @TOTALOUTSTANDINGAMOUNT) * @AMOUNT), @TRANSACTIONEXCHANGERATEID)
                            end as AMOUNT,
                            3 TYPECODE,
                            8 APPLICATIONCODE,
                            @TRANSACTIONCURRENCYID TRANSACTIONCURRENCYID
                        from
                            @OUTSTANDINGDESIGNATIONS
                        group by
                            DESIGNATIONID, AMOUNT
                        for xml raw('ITEM'), type, elements, root('SPLITS'), BINARY BASE64
                    );

                return @SPLITS;
            end