UFN_RECOGNITIONLEVELBENEFITS_CONVERTAMOUNTSINXML

Fills in multicurrency fields in the given recognition level benefits XML.
Return

Return Type
xml
Parameters

Parameter	Parameter Type	Mode
@BENEFITS	xml	IN
@BASECURRENCYID	uniqueidentifier	IN
@ORGANIZATIONEXCHANGERATEID	uniqueidentifier	IN
@DATEADDED	datetime	IN
Definition

Copy


            CREATE function dbo.UFN_RECOGNITIONLEVELBENEFITS_CONVERTAMOUNTSINXML(
                @BENEFITS xml,
                @BASECURRENCYID uniqueidentifier,
                @ORGANIZATIONEXCHANGERATEID uniqueidentifier,
                @DATEADDED datetime
            ) returns xml
            as begin
                declare @ORGANIZATIONCURRENCYID uniqueidentifier = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();

                return (
                    select 
                        B.ID,
                        B.BENEFITID,
                        B.QUANTITY,
                        B.UNITVALUE,
                        B.DETAILS,
                        B.SEQUENCE,
                        coalesce(B.BASECURRENCYID, @BASECURRENCYID) BASECURRENCYID,
                        case
                            when coalesce(RLB.BASECURRENCYID, B.BASECURRENCYID, @BASECURRENCYID) <> @ORGANIZATIONCURRENCYID
                                then dbo.UFN_CURRENCY_CONVERT(B.UNITVALUE, 
                                    case
                                        when RLB.ORGANIZATIONEXCHANGERATEID is not null
                                            then RLB.ORGANIZATIONEXCHANGERATEID
                                        when coalesce(RLB.BASECURRENCYID, B.BASECURRENCYID, @BASECURRENCYID) = @BASECURRENCYID and @ORGANIZATIONEXCHANGERATEID is not null
                                            then @ORGANIZATIONEXCHANGERATEID
                                        else dbo.UFN_CURRENCYEXCHANGERATE_GETLATEST(coalesce(RLB.BASECURRENCYID, B.BASECURRENCYID, @BASECURRENCYID), @ORGANIZATIONCURRENCYID, @DATEADDED, 0, null)
                                    end
                                )
                            else B.UNITVALUE
                        end ORGANIZATIONUNITVALUE,
                        case
                            when coalesce(RLB.BASECURRENCYID, B.BASECURRENCYID, @BASECURRENCYID) = @ORGANIZATIONCURRENCYID
                                then null
                            when RLB.ORGANIZATIONEXCHANGERATEID is not null
                                then RLB.ORGANIZATIONEXCHANGERATEID
                            when coalesce(RLB.BASECURRENCYID, B.BASECURRENCYID, @BASECURRENCYID) = @BASECURRENCYID and @ORGANIZATIONEXCHANGERATEID is not null
                                then @ORGANIZATIONEXCHANGERATEID
                            else dbo.UFN_CURRENCYEXCHANGERATE_GETLATEST(coalesce(RLB.BASECURRENCYID, B.BASECURRENCYID, @BASECURRENCYID), @ORGANIZATIONCURRENCYID, @DATEADDED, 0, null)
                        end ORGANIZATIONEXCHANGERATEID
                    from dbo.UFN_RECOGNITIONLEVEL_GETBENEFITS_2_FROMITEMLISTXML(@BENEFITS) B
                    left join dbo.RECOGNITIONLEVELBENEFIT RLB on RLB.ID = B.ID
                    for xml raw('ITEM'),type,elements,root('BENEFITS'),BINARY BASE64
                )
            end