UFN_REVENUETRIBUTE_GETGROSSAMOUNTINCURRENCY

            CREATE function dbo.UFN_REVENUETRIBUTE_GETGROSSAMOUNTINCURRENCY
            (
                @REVENUETRIBUTEID uniqueidentifier,
                @CURRENCYID uniqueidentifier
            )
            returns money
            as
            begin
                declare @AMOUNT money = 0;

                if (@CURRENCYID is null) or (@CURRENCYID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY())
                begin
                    select
                        @AMOUNT = REVENUETRIBUTE.ORGANIZATIONAMOUNT + 
                                    coalesce(case when REVENUETRIBUTE.ORGANIZATIONEXCHANGERATEID is null then REVENUETRIBUTETAXCLAIMAMOUNT.TAXCLAIMAMOUNT
                                                                                            else dbo.UFN_CURRENCY_CONVERT(REVENUETRIBUTETAXCLAIMAMOUNT.TAXCLAIMAMOUNT, REVENUETRIBUTE.ORGANIZATIONEXCHANGERATEID) end, 0)
                    from
                        dbo.REVENUETRIBUTE
                    left join 
                        dbo.REVENUETRIBUTETAXCLAIMAMOUNT on REVENUETRIBUTE.ID = REVENUETRIBUTETAXCLAIMAMOUNT.ID
                    where
                        REVENUETRIBUTE.ID = @REVENUETRIBUTEID;
                end
                else
                begin
                    declare @RECORDBASECURRENCYID uniqueidentifier;
                    declare @RECORDDATE datetime;

                    select
                        @RECORDBASECURRENCYID = REVENUETRIBUTE.BASECURRENCYID,
                        @RECORDDATE = REVENUETRIBUTE.DATEADDED
                    from
                        dbo.REVENUETRIBUTE
                    where
                        REVENUETRIBUTE.ID = @REVENUETRIBUTEID;

                    if @CURRENCYID = @RECORDBASECURRENCYID
                    begin
                        select
                            @AMOUNT = REVENUETRIBUTE.AMOUNT + coalesce(REVENUETRIBUTETAXCLAIMAMOUNT.TAXCLAIMAMOUNT, 0)
                        from
                            dbo.REVENUETRIBUTE
                        left join 
                            dbo.REVENUETRIBUTETAXCLAIMAMOUNT on REVENUETRIBUTE.ID = REVENUETRIBUTETAXCLAIMAMOUNT.ID
                        where
                            REVENUETRIBUTE.ID = @REVENUETRIBUTEID;
                    end
                    else
                    begin
                        declare @CURRENCYEXCHANGERATEID uniqueidentifier;

                        set @CURRENCYEXCHANGERATEID = dbo.UFN_CURRENCYEXCHANGERATE_GETLATESTINCLUDEEXPIRED(dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY(), @CURRENCYID, @RECORDDATE, 1, null);
                        if (@CURRENCYEXCHANGERATEID is not null)
                        begin
                            select
                                @AMOUNT = dbo.UFN_CURRENCY_CONVERT(REVENUETRIBUTE.ORGANIZATIONAMOUNT +
                                                                    coalesce(case when REVENUETRIBUTE.ORGANIZATIONEXCHANGERATEID is null then REVENUETRIBUTETAXCLAIMAMOUNT.TAXCLAIMAMOUNT
                                                                                            else dbo.UFN_CURRENCY_CONVERT(REVENUETRIBUTETAXCLAIMAMOUNT.TAXCLAIMAMOUNT, REVENUETRIBUTE.ORGANIZATIONEXCHANGERATEID) end, 0), @CURRENCYEXCHANGERATEID)
                            from
                                dbo.REVENUETRIBUTE
                            left join 
                                dbo.REVENUETRIBUTETAXCLAIMAMOUNT on REVENUETRIBUTE.ID = REVENUETRIBUTETAXCLAIMAMOUNT.ID
                            where
                                REVENUETRIBUTE.ID = @REVENUETRIBUTEID;
                        end
                        else
                        begin
                            set @CURRENCYEXCHANGERATEID = dbo.UFN_CURRENCYEXCHANGERATE_GETLATESTINCLUDEEXPIRED(@CURRENCYID, dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY(), @RECORDDATE, 1, null);

                            select
                                @AMOUNT = dbo.UFN_CURRENCY_CONVERTINVERSE(REVENUETRIBUTE.ORGANIZATIONAMOUNT +
                                                                    coalesce(case when REVENUETRIBUTE.ORGANIZATIONEXCHANGERATEID is null then REVENUETRIBUTETAXCLAIMAMOUNT.TAXCLAIMAMOUNT
                                                                                            else dbo.UFN_CURRENCY_CONVERT(REVENUETRIBUTETAXCLAIMAMOUNT.TAXCLAIMAMOUNT, REVENUETRIBUTE.ORGANIZATIONEXCHANGERATEID) end, 0), @CURRENCYEXCHANGERATEID)
                            from
                                dbo.REVENUETRIBUTE
                            left join 
                                dbo.REVENUETRIBUTETAXCLAIMAMOUNT on REVENUETRIBUTE.ID = REVENUETRIBUTETAXCLAIMAMOUNT.ID
                            where
                                REVENUETRIBUTE.ID = @REVENUETRIBUTEID;
                        end
                    end
                end    

                return @AMOUNT;
            end