UFN_REVENUE_GETTRIBUTESFORAMOUNT

            CREATE function dbo.UFN_REVENUE_GETTRIBUTESFORAMOUNT
             (
                @REVENUEID uniqueidentifier,
                @TRANSACTIONAMOUNT money,
                @BASEEXCHANGERATEID uniqueidentifier,
                @EXCHANGERATE decimal(20,8)
            )
            returns @TRIBUTETABLE table
            (
                TRIBUTEID uniqueidentifier,
                AMOUNT money,
                DESIGNATIONID uniqueidentifier,
                SEQUENCE int,
                REVENUETRIBUTEID uniqueidentifier,
                BASECURRENCYID uniqueidentifier,
                ORGANIZATIONAMOUNT money,
                ORGANIZATIONEXCHANGERATEID uniqueidentifier
            )
            as begin
                --Tributes are in base and not transaction, so convert the revenue transaction amount to base using the 

                --provided exchange rate information. 

                declare @UNROUNDED decimal(20,8);
                declare @BASEAMOUNT money; 
                declare @BASECURRENCYID uniqueidentifier;
        declare @TRIBUTES xml;

                if not @BASEEXCHANGERATEID = '00000000-0000-0000-0000-000000000001'
                    select @EXCHANGERATE = RATE from dbo.CURRENCYEXCHANGERATE where ID = @BASEEXCHANGERATEID;

                --We can get the rounding information from the revenue record. 

                declare @DECIMALDIGITS tinyint;
                declare @ROUNDINGTYPECODE tinyint;

                select 
                    @DECIMALDIGITS = CURRENCY.DECIMALDIGITS, 
                    @ROUNDINGTYPECODE = CURRENCY.ROUNDINGTYPECODE,
                    @BASECURRENCYID = CURRENCY.ID
                from dbo.CURRENCY
                inner join dbo.CURRENCYSET on CURRENCY.ID = CURRENCYSET.BASECURRENCYID
                inner join dbo.PDACCOUNTSYSTEM on CURRENCYSET.ID = PDACCOUNTSYSTEM.CURRENCYSETID 
                inner join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTION.PDACCOUNTSYSTEMID = PDACCOUNTSYSTEM.ID
                where FINANCIALTRANSACTION.ID = @REVENUEID;

                set @UNROUNDED = dbo.UFN_CURRENCY_APPLYRATE(@TRANSACTIONAMOUNT, @EXCHANGERATE);
                 if not @BASEEXCHANGERATEID = '00000000-0000-0000-0000-000000000001' and @BASEEXCHANGERATEID is not null
                    set @BASEAMOUNT = dbo.UFN_CURRENCY_ROUND(@UNROUNDED, @DECIMALDIGITS, @ROUNDINGTYPECODE);
                else
                    set @BASEAMOUNT = @TRANSACTIONAMOUNT

                --Get the current organization exchange rate so we can convert the new tribute amount from base to organization. 

                declare @ORGANIZATIONCURRENCYID uniqueidentifier = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();
                declare @ORGANIZATIONEXCHANGERATEID uniqueidentifier = null;
                if not @ORGANIZATIONCURRENCYID = @BASECURRENCYID
                    set @ORGANIZATIONEXCHANGERATEID = dbo.UFN_CURRENCYEXCHANGERATE_GETLATESTORGANIZATIONRATE(@BASECURRENCYID, @ORGANIZATIONCURRENCYID, null, null, null);


                declare @OLDREVENUEBASEAMOUNT money;
                select @OLDREVENUEBASEAMOUNT = FINANCIALTRANSACTION.BASEAMOUNT from dbo.FINANCIALTRANSACTION where FINANCIALTRANSACTION.ID = @REVENUEID;

            set @TRIBUTES = (
                    select AMOUNT, ID 
                        from REVENUETRIBUTE
                        where REVENUEID = @REVENUEID
                        for xml raw('ITEM'),type,elements,root('AMOUNTSTOPRORATE'),binary base64
                );

                with [CTE] as 
                (
                    select
                        REVENUETRIBUTE.TRIBUTEID,
                        NEWAMOUNT.AMOUNT as AMOUNT,
                        TRIBUTE.DESIGNATIONID,
                        row_number() over(order by REVENUETRIBUTE.DATEADDED) as SEQUENCE,
                        REVENUETRIBUTE.ID as REVENUETRIBUTEID,
                        REVENUETRIBUTE.BASECURRENCYID
                    from dbo.REVENUETRIBUTE
          inner join dbo.UFN_SPLITS_GETPRORATEDSPLITS(@OLDREVENUEBASEAMOUNT, @BASEAMOUNT, @TRIBUTES) NEWAMOUNT on REVENUETRIBUTE.ID = NEWAMOUNT.ID
                    inner join dbo.TRIBUTE on TRIBUTE.ID = REVENUETRIBUTE.TRIBUTEID
                    left join dbo.CURRENCY on CURRENCY.ID = REVENUETRIBUTE.BASECURRENCYID
                    where REVENUETRIBUTE.REVENUEID = @REVENUEID
                )
                insert into @TRIBUTETABLE(TRIBUTEID, AMOUNT, DESIGNATIONID, SEQUENCE, REVENUETRIBUTEID, 
                                          BASECURRENCYID, ORGANIZATIONAMOUNT, ORGANIZATIONEXCHANGERATEID)
                    select 
                        TRIBUTEID,
                        AMOUNT,
                        DESIGNATIONID,
                        SEQUENCE,
                        REVENUETRIBUTEID,
                        BASECURRENCYID,
                        case 
                            when @ORGANIZATIONEXCHANGERATEID is null then AMOUNT 
                            else dbo.UFN_CURRENCY_CONVERT(AMOUNT, @ORGANIZATIONEXCHANGERATEID) 
                        end,
                        @ORGANIZATIONEXCHANGERATEID
                    from [CTE];


                return
            end