UFN_APPEAL_GETGOALINCURRENCY_BULK2

Returns the goal of all appeals in the given currency. Extra parameter to allow the default currency to be set to the appeal.
Return

Return Type
table
Parameters

Parameter	Parameter Type	Mode
@CURRENCYID	uniqueidentifier	IN
@ORGANIZATIONCURRENCYID	uniqueidentifier	IN
@DECIMALDIGITS	tinyint	IN
@ROUNDINGTYPECODE	tinyint	IN
@APPEALCURRENCYASDEFAULT	tinyint	IN
Definition

Copy


    CREATE function dbo.UFN_APPEAL_GETGOALINCURRENCY_BULK2
    (
        @CURRENCYID uniqueidentifier,
        @ORGANIZATIONCURRENCYID uniqueidentifier,
        @DECIMALDIGITS tinyint,
        @ROUNDINGTYPECODE tinyint,
        @APPEALCURRENCYASDEFAULT tinyint = 0
    )
    returns table
            as
            return(
                --when @CURRENCYID is null return all Appeals in their base currency

                select
                    APPEAL.ID,
                    case 
                    when ((@APPEALCURRENCYASDEFAULT = 1) and (@CURRENCYID is null) and (APPEAL.BASECURRENCYID is not null))
                        then APPEAL.GOAL
                    when (@CURRENCYID is null) or (@CURRENCYID = @ORGANIZATIONCURRENCYID)
                        then APPEAL.ORGANIZATIONGOAL
                    else
                        APPEAL.GOAL
                    end [GOALINCURRENCY],
                    APPEAL.BASECURRENCYID,
                    APPEAL.APPEALREPORT1CODEID,
                    APPEAL.APPEALCATEGORYCODEID,
                    APPEAL.SITEID,
                    APPEAL.NAME
                from dbo.APPEAL
                where (@CURRENCYID is null) 
                        or (@CURRENCYID = @ORGANIZATIONCURRENCYID)
                        or @CURRENCYID = APPEAL.BASECURRENCYID
                union all
                select
                    APPEAL.ID,
                    case
                    when [LATESTORGANIZATIONEXCHANGERATE].RATE is not null
                        then dbo.UFN_CURRENCY_ROUND(dbo.UFN_CURRENCY_APPLYRATE(APPEAL.ORGANIZATIONGOAL, [LATESTORGANIZATIONEXCHANGERATE].RATE), @DECIMALDIGITS, @ROUNDINGTYPECODE)
                    when [LATESTINVERSEORGANIZATIONEXCHANGERATE].RATE is not null
                        then dbo.UFN_CURRENCY_ROUND(dbo.UFN_CURRENCY_APPLYRATE(APPEAL.ORGANIZATIONGOAL, cast((1 / [LATESTINVERSEORGANIZATIONEXCHANGERATE].RATE) as decimal(20,8))), @DECIMALDIGITS, @ROUNDINGTYPECODE)
                    else
                        0
                    end [GOALINCURRENCY],
                    APPEAL.BASECURRENCYID,
                    APPEAL.APPEALREPORT1CODEID,
                    APPEAL.APPEALCATEGORYCODEID,
                    APPEAL.SITEID,
                    APPEAL.NAME
                from
                    dbo.APPEAL
                    outer apply
                    (
                        select
                            RATE
                        from
                            dbo.CURRENCYEXCHANGERATE
                        where
                            @ORGANIZATIONCURRENCYID = CURRENCYEXCHANGERATE.FROMCURRENCYID
                            and @CURRENCYID = CURRENCYEXCHANGERATE.TOCURRENCYID
                            and CURRENCYEXCHANGERATE.TYPECODE in (0,1)
                            and CURRENCYEXCHANGERATE.ISLATESTRATEFORASOFDATE = 1
                            and APPEAL.DATEADDED >= CURRENCYEXCHANGERATE.ASOFDATESDTZ
                            and APPEAL.DATEADDED <= CURRENCYEXCHANGERATE.NEXTRATEASOFDATEORMAXSDTZ
                    ) LATESTORGANIZATIONEXCHANGERATE
                    outer apply
                    (
                        select
                            RATE
                        from
                            dbo.CURRENCYEXCHANGERATE
                        where
                            @CURRENCYID = CURRENCYEXCHANGERATE.FROMCURRENCYID
                            and @ORGANIZATIONCURRENCYID= CURRENCYEXCHANGERATE.TOCURRENCYID
                            and CURRENCYEXCHANGERATE.TYPECODE in (0,1)
                            and CURRENCYEXCHANGERATE.ISLATESTRATEFORASOFDATE = 1
                            and APPEAL.DATEADDED >= CURRENCYEXCHANGERATE.ASOFDATESDTZ
                            and APPEAL.DATEADDED <= CURRENCYEXCHANGERATE.NEXTRATEASOFDATEORMAXSDTZ
                    ) LATESTINVERSEORGANIZATIONEXCHANGERATE
                where (@CURRENCYID is not null) 
                    and (@CURRENCYID <> @ORGANIZATIONCURRENCYID)
                    and @CURRENCYID <> APPEAL.BASECURRENCYID
            );