UFN_REVENUEBENEFIT_CONVERTAMOUNTSINXML_2

            CREATE function dbo.UFN_REVENUEBENEFIT_CONVERTAMOUNTSINXML_2
            (
                @BENEFITS xml,
                @REVENUETRANSACTIONCURRENCYID uniqueidentifier,
                @REVENUEBASECURRENCYID uniqueidentifier
            )
            returns xml
            as
            begin

                declare @ORGANIZATIONCURRENCYID uniqueidentifier = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();
                declare @CURRENTDATE datetime = getdate();

                declare @ORIGINCODE tinyint;
                select @ORIGINCODE = coalesce(ORGANIZATIONAMOUNTORIGINCODE, 0)
                from dbo.MULTICURRENCYCONFIGURATION

                return (
                    select 
                        ID,
                        BENEFITID,
                        QUANTITY,
                        UNITVALUE,
                        TOTALVALUE,
                        DETAILS,
                        SEQUENCE,
                        PERCENTAPPLICABLEAMOUNT,
                        VALUEPERCENT,
                        USEPERCENT,
                        BASECURRENCYID,
                        @REVENUETRANSACTIONCURRENCYID TRANSACTIONCURRENCYID,
                        BASEEXCHANGERATEID,
                        ORGANIZATIONEXCHANGERATEID,
                        CONVERSION.TRANSACTIONTOTALVALUE,
                        REVENUESPLITID,
                        case
                            when @ORIGINCODE = 0 then
                                case
                                    when BASECURRENCYID = @ORGANIZATIONCURRENCYID
                                        then TOTALVALUE
                                    else dbo.UFN_CURRENCY_CONVERT(TOTALVALUE, ORGANIZATIONEXCHANGERATEID)
                                end
                            when @ORIGINCODE = 1 then
                                case                    
                                    when @REVENUETRANSACTIONCURRENCYID = @ORGANIZATIONCURRENCYID  
                                        then CONVERSION.TRANSACTIONTOTALVALUE
                                    else dbo.UFN_CURRENCY_CONVERT(CONVERSION.TRANSACTIONTOTALVALUE, ORGANIZATIONEXCHANGERATEID)
                                end
                        end ORGANIZATIONTOTALVALUE
                    from(
                        select 
                            REVBENEFITSFROMXML.ID,
                            REVBENEFITSFROMXML.BENEFITID,
                            REVBENEFITSFROMXML.QUANTITY,
                            REVBENEFITSFROMXML.UNITVALUE,
                            cast(coalesce((REVBENEFITSFROMXML.QUANTITY * REVBENEFITSFROMXML.UNITVALUE),0) + coalesce((REVBENEFITSFROMXML.PERCENTAPPLICABLEAMOUNT * REVBENEFITSFROMXML.VALUEPERCENT/100),0) as money) TOTALVALUE,
                            REVBENEFITSFROMXML.DETAILS,
                            REVBENEFITSFROMXML.SEQUENCE,
                            REVBENEFITSFROMXML.PERCENTAPPLICABLEAMOUNT,
                            REVBENEFITSFROMXML.VALUEPERCENT,
                            REVBENEFITSFROMXML.USEPERCENT,
                            REVBENEFITSFROMXML.BASECURRENCYID,
                            REVBENEFITSFROMXML.REVENUESPLITID,
                            case
                                when REVBENEFITSFROMXML.BASECURRENCYID <> @REVENUETRANSACTIONCURRENCYID
                                    then dbo.UFN_CURRENCYEXCHANGERATE_GETLATEST(REVBENEFITSFROMXML.BASECURRENCYID,@REVENUETRANSACTIONCURRENCYID, @CURRENTDATE, 1, null)
                                else null
                            end BASEEXCHANGERATEID,
                            case
                                when @ORIGINCODE = 0 then
                                    case
                                        when REVBENEFITSFROMXML.BASECURRENCYID <> @ORGANIZATIONCURRENCYID
                                            then dbo.UFN_CURRENCYEXCHANGERATE_GETLATEST(REVBENEFITSFROMXML.BASECURRENCYID, @ORGANIZATIONCURRENCYID, @CURRENTDATE, 0, null)
                                        else null
                                    end
                                when @ORIGINCODE = 1 then
                                    case                    
                                        when @REVENUETRANSACTIONCURRENCYID <> @ORGANIZATIONCURRENCYID
                                            then dbo.UFN_CURRENCYEXCHANGERATE_GETLATEST(@REVENUETRANSACTIONCURRENCYID, @ORGANIZATIONCURRENCYID, @CURRENTDATE, 0, null)
                                        else null
                                    end
                            end ORGANIZATIONEXCHANGERATEID
                        from ( --Bug 92734 - AdamBu 5/19/10 - Created subselect so that case/coalesce to figure out base currency only needs to happen once.

                            select
                                REVBENEFITSFROMXML.ID,
                                REVBENEFITSFROMXML.BENEFITID,
                                REVBENEFITSFROMXML.QUANTITY,
                                REVBENEFITSFROMXML.UNITVALUE,
                                REVBENEFITSFROMXML.DETAILS,
                                REVBENEFITSFROMXML.SEQUENCE,
                                REVBENEFITSFROMXML.PERCENTAPPLICABLEAMOUNT,
                                REVBENEFITSFROMXML.VALUEPERCENT,
                                REVBENEFITSFROMXML.USEPERCENT,
                                REVBENEFITSFROMXML.REVENUESPLITID,
                                case 
                                    when REVBENEFITSFROMXML.BENEFITID = REVENUEBENEFIT.BENEFITID
                                        --Bug 92734 - AdamBu 5/19/10 - We did't change the benefit on an existing revenue benefit, 

                                        --    so use the old base currency.

                                        then REVENUEBENEFIT.BASECURRENCYID
                                    else  
                                        --Bug 92734 - AdamBu 5/19/10 - This is a new revenue benefit, or we have changed the benefit 

                                        --    on an old one, so we pull the base currency    from the benefit table (for unit benefits) 

                                        --    or the revenue transaction (for percent benefits).

                                        coalesce(BENEFIT.BASECURRENCYID,@REVENUEBASECURRENCYID)
                                end BASECURRENCYID
                            from dbo.UFN_REVENUE_GETBENEFITS_3_FROMITEMLISTXML(@BENEFITS) REVBENEFITSFROMXML
                                inner join dbo.BENEFIT on REVBENEFITSFROMXML.BENEFITID = BENEFIT.ID
                                left join dbo.REVENUEBENEFIT on REVBENEFITSFROMXML.ID = REVENUEBENEFIT.ID
                        ) REVBENEFITSFROMXML
                    ) BENEFITS
                    cross apply(
                        select 
                            case
                                when BASECURRENCYID <> @REVENUETRANSACTIONCURRENCYID
                                    then dbo.UFN_CURRENCY_CONVERT(TOTALVALUE, BASEEXCHANGERATEID)
                                else TOTALVALUE
                            end TRANSACTIONTOTALVALUE
                    ) CONVERSION
                    for xml raw('ITEM'),type,elements,root('BENEFITS'),BINARY BASE64
                )
            end