UFN_REVENUE_BUILDSPLITSFOROPPORTUNITYGRANTAWARD

            create function dbo.UFN_REVENUE_BUILDSPLITSFOROPPORTUNITYGRANTAWARD
            (
                @OPPORTUNITYID uniqueidentifier,
                @AMOUNT money
            ) returns xml as
            begin

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

                insert into @OUTSTANDINGDESIGNATIONS
                select
                    OD.DESIGNATIONID,
                    OD.AMOUNT - coalesce((
                        select
                            sum(RS.AMOUNT)
                        from
                            dbo.REVENUEOPPORTUNITY RO
                        inner join
                            dbo.REVENUESPLIT RS on RS.ID = RO.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 @SPLITS xml;
                if @AMOUNT >= @TOTALOUTSTANDINGAMOUNT
                    set @SPLITS = 
                    (
                        select 
                            null [ID], 
                            DESIGNATIONID, 
                            AMOUNT,
                            3 TYPECODE,
                            8 APPLICATIONCODE
                        from 
                            @OUTSTANDINGDESIGNATIONS
                        group by 
                            DESIGNATIONID, AMOUNT
                        for xml raw('ITEM'), type, elements, root('SPLITS'), BINARY BASE64
                    );
                else
                    set @SPLITS = 
                    (
                        select 
                            null [ID], 
                            DESIGNATIONID, 
                            ((AMOUNT / @TOTALOUTSTANDINGAMOUNT) * @AMOUNT) [AMOUNT],
                            3 TYPECODE,
                            8 APPLICATIONCODE
                        from
                            @OUTSTANDINGDESIGNATIONS
                        group by
                            DESIGNATIONID, AMOUNT
                        for xml raw('ITEM'), type, elements, root('SPLITS'), BINARY BASE64
                    );

                return @SPLITS;
            end