UFN_FUNDINGREQUEST_GETAMOUNTREQUESTEDINCURRENCY_BULK

Returns the requested amount of a funding request in a given currency.
Return

Return Type
table
Parameters

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

Copy


CREATE function dbo.UFN_FUNDINGREQUEST_GETAMOUNTREQUESTEDINCURRENCY_BULK
(
    @CURRENCYID uniqueidentifier,
    @ORGANIZATIONCURRENCYID uniqueidentifier,
    @DECIMALDIGITS tinyint,
    @ROUNDINGTYPECODE tinyint
)
    returns table
    as
    return
    (
        select
            case
                when (@CURRENCYID is null) or (@CURRENCYID = @ORGANIZATIONCURRENCYID)
                    then FUNDINGREQUEST.ORGANIZATIONAMOUNTREQUESTED                        
                else FUNDINGREQUEST.AMOUNTREQUESTED
            end [AMOUNTINCURRENCY],
            FUNDINGREQUEST.ID
        from dbo.FUNDINGREQUEST
        where (@CURRENCYID is null) 
            or (@CURRENCYID = @ORGANIZATIONCURRENCYID)
            or @CURRENCYID = FUNDINGREQUEST.BASECURRENCYID

        union all

        select
            case
                when [LATESTORGANIZATIONEXCHANGERATE].RATE is not null
                    then dbo.UFN_CURRENCY_ROUND(dbo.UFN_CURRENCY_APPLYRATE(FUNDINGREQUEST.ORGANIZATIONAMOUNTREQUESTED, [LATESTORGANIZATIONEXCHANGERATE].RATE), @DECIMALDIGITS, @ROUNDINGTYPECODE)
                when [LATESTINVERSEORGANIZATIONEXCHANGERATE].RATE is not null
                    then dbo.UFN_CURRENCY_ROUND(dbo.UFN_CURRENCY_APPLYRATE(FUNDINGREQUEST.ORGANIZATIONAMOUNTREQUESTED, cast((1 / [LATESTINVERSEORGANIZATIONEXCHANGERATE].RATE) as decimal(20,8))), @DECIMALDIGITS, @ROUNDINGTYPECODE)
                else 0
            end [AMOUNTINCURRENCY],
            FUNDINGREQUEST.ID
        from dbo.FUNDINGREQUEST
            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 dateadd(ms, 86399996,FUNDINGREQUEST.DATESUBMITTED) >= CURRENCYEXCHANGERATE.ASOFDATESDTZ
                    and dateadd(ms, 86399996,FUNDINGREQUEST.DATESUBMITTED) <= 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 dateadd(ms, 86399996,FUNDINGREQUEST.DATESUBMITTED) >= CURRENCYEXCHANGERATE.ASOFDATESDTZ
                    and dateadd(ms, 86399996,FUNDINGREQUEST.DATESUBMITTED) <= CURRENCYEXCHANGERATE.NEXTRATEASOFDATEORMAXSDTZ
            ) LATESTINVERSEORGANIZATIONEXCHANGERATE
        where (@CURRENCYID is not null) 
            and (@CURRENCYID <> @ORGANIZATIONCURRENCYID)
            and @CURRENCYID <> FUNDINGREQUEST.BASECURRENCYID    
    )