UFN_REVENUERECOGNITION_GETAMOUNTINCURRENCY_BULK_4

CREATE function [dbo].[UFN_REVENUERECOGNITION_GETAMOUNTINCURRENCY_BULK_4]
(
    @CURRENCYID uniqueidentifier,
    @ORGANIZATIONCURRENCYID uniqueidentifier,
    @DECIMALDIGITS tinyint,
    @ROUNDINGTYPECODE tinyint
)
returns table
as
return
(
    select
        REVENUERECOGNITION.ID,
        REVENUERECOGNITION.REVENUESPLITID,
        REVENUERECOGNITION.CONSTITUENTID,
        REVENUERECOGNITION.EFFECTIVEDATE,
        case
            when (@CURRENCYID is null) or (@CURRENCYID = @ORGANIZATIONCURRENCYID)
                then REVENUERECOGNITION.ORGANIZATIONAMOUNT

            else
                REVENUERECOGNITION.AMOUNT
        end [AMOUNTINCURRENCY],
        [REVENUERECOGNITION].[DATEADDED],
        [REVENUERECOGNITION].[REVENUERECOGNITIONTYPECODEID],
        [REVENUERECOGNITION].[TSLONG],
        FINANCIALTRANSACTION.[ID] as [REVENUEID],
        FINANCIALTRANSACTION.[TYPECODE] as [TRANSACTIONTYPECODE],
        FINANCIALTRANSACTION.[CONSTITUENTID] as [REVENUECONSTITUENTID],
        FINANCIALTRANSACTION.[DATE],
        FINANCIALTRANSACTION.[DATEADDED] as [REVENUEDATEADDED],
        FINANCIALTRANSACTIONLINEITEM.[TSLONG] as [REVENUESPLITTSLONG],
        FINANCIALTRANSACTION.[TYPE] as [TRANSACTIONTYPE],
        case
            when (@CURRENCYID is null) or (@CURRENCYID = @ORGANIZATIONCURRENCYID)
                then null

            else
                REVENUERECOGNITION.ORGANIZATIONEXCHANGERATEID
        end [ORGANIZATIONEXCHANGERATEID],
        REVENUERECOGNITION.ORGANIZATIONAMOUNT
    from
        dbo.REVENUERECOGNITION with (nolock)
        inner join dbo.FINANCIALTRANSACTIONLINEITEM with (nolock) on FINANCIALTRANSACTIONLINEITEM.ID = REVENUERECOGNITION.REVENUESPLITID and FINANCIALTRANSACTIONLINEITEM.TYPECODE = 0
        inner join dbo.FINANCIALTRANSACTION with (nolock) on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = FINANCIALTRANSACTION.ID
    where 
        (
            (@CURRENCYID is null) 
            or (@CURRENCYID = @ORGANIZATIONCURRENCYID)
            or @CURRENCYID = REVENUERECOGNITION.BASECURRENCYID
        ) 
        and FINANCIALTRANSACTIONLINEITEM.TYPECODE = 0  -- Standard line items only

        and FINANCIALTRANSACTIONLINEITEM.DELETEDON is NULL -- Non deleted line items only

        and FINANCIALTRANSACTION.DELETEDON is NULL
        and ((FINANCIALTRANSACTION.TYPECODE between 0 and 9) or (FINANCIALTRANSACTION.TYPECODE = 15)) -- Only FT that used to be in the REVENUE table.


    union all

    select
        REVENUERECOGNITION.ID,
        REVENUERECOGNITION.REVENUESPLITID,
        REVENUERECOGNITION.CONSTITUENTID,
        REVENUERECOGNITION.EFFECTIVEDATE,
        case
            when [LATESTORGANIZATIONEXCHANGERATE].RATE is not null
                then dbo.UFN_CURRENCY_ROUND(dbo.UFN_CURRENCY_APPLYRATE(REVENUERECOGNITION.ORGANIZATIONAMOUNT, [LATESTORGANIZATIONEXCHANGERATE].RATE), @DECIMALDIGITS, @ROUNDINGTYPECODE)

            when [LATESTINVERSEORGANIZATIONEXCHANGERATE].RATE is not null
                then dbo.UFN_CURRENCY_ROUND(dbo.UFN_CURRENCY_APPLYRATE(REVENUERECOGNITION.ORGANIZATIONAMOUNT, cast((1 / [LATESTINVERSEORGANIZATIONEXCHANGERATE].RATE) as decimal(20,8))), @DECIMALDIGITS, @ROUNDINGTYPECODE)
            else
                0

        end [AMOUNTINCURRENCY],
        [REVENUERECOGNITION].[DATEADDED],
        [REVENUERECOGNITION].[REVENUERECOGNITIONTYPECODEID],
        [REVENUERECOGNITION].[TSLONG],
        FINANCIALTRANSACTION.[ID] as [REVENUEID],
        FINANCIALTRANSACTION.[TYPECODE] as [TRANSACTIONTYPECODE],
        FINANCIALTRANSACTION.[CONSTITUENTID] as [REVENUECONSTITUENTID],
        FINANCIALTRANSACTION.[DATE],
        FINANCIALTRANSACTION.[DATEADDED] as [REVENUEDATEADDED],
        FINANCIALTRANSACTIONLINEITEM.[TSLONG] as [REVENUESPLITTSLONG],
        FINANCIALTRANSACTION.[TYPE] as [TRANSACTIONTYPE],
        case
            when REVENUERECOGNITION.ORGANIZATIONEXCHANGERATEID is not null
                then REVENUERECOGNITION.ORGANIZATIONEXCHANGERATEID

            when [LATESTORGANIZATIONEXCHANGERATE].RATE is not null
                then [LATESTORGANIZATIONEXCHANGERATE].ID

            when [LATESTINVERSEORGANIZATIONEXCHANGERATE].RATE is not null
                then [LATESTINVERSEORGANIZATIONEXCHANGERATE].ID
            else
                null
        end [ORGANIZATIONEXCHANGERATEID],
        REVENUERECOGNITION.ORGANIZATIONAMOUNT
    from
        dbo.REVENUERECOGNITION with (nolock)
        inner join dbo.FINANCIALTRANSACTIONLINEITEM with (nolock) on FINANCIALTRANSACTIONLINEITEM.ID = REVENUERECOGNITION.REVENUESPLITID
        inner join dbo.FINANCIALTRANSACTION with (nolock) on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = FINANCIALTRANSACTION.ID
        left outer join dbo.CURRENCYEXCHANGERATE as LATESTORGANIZATIONEXCHANGERATE 
            on @ORGANIZATIONCURRENCYID = LATESTORGANIZATIONEXCHANGERATE.FROMCURRENCYID
                and @CURRENCYID = LATESTORGANIZATIONEXCHANGERATE.TOCURRENCYID
                and LATESTORGANIZATIONEXCHANGERATE.TYPECODE in (0,1)
                and LATESTORGANIZATIONEXCHANGERATE.ISLATESTRATEFORASOFDATE = 1
                and dateadd(ms, 86399996, FINANCIALTRANSACTION.[DATE]) >= LATESTORGANIZATIONEXCHANGERATE.ASOFDATESDTZ
                and dateadd(ms, 86399996, FINANCIALTRANSACTION.[DATE]) <= LATESTORGANIZATIONEXCHANGERATE.NEXTRATEASOFDATEORMAXSDTZ
        left outer join dbo.CURRENCYEXCHANGERATE as LATESTINVERSEORGANIZATIONEXCHANGERATE
            on @CURRENCYID = LATESTINVERSEORGANIZATIONEXCHANGERATE.FROMCURRENCYID
                and @ORGANIZATIONCURRENCYID= LATESTINVERSEORGANIZATIONEXCHANGERATE.TOCURRENCYID
                and LATESTINVERSEORGANIZATIONEXCHANGERATE.TYPECODE in (0,1)
                and LATESTINVERSEORGANIZATIONEXCHANGERATE.ISLATESTRATEFORASOFDATE = 1
                and dateadd(ms, 86399996, FINANCIALTRANSACTION.[DATE]) >= LATESTINVERSEORGANIZATIONEXCHANGERATE.ASOFDATESDTZ
                and dateadd(ms, 86399996, FINANCIALTRANSACTION.[DATE]) <= LATESTINVERSEORGANIZATIONEXCHANGERATE.NEXTRATEASOFDATEORMAXSDTZ
        where
            (@CURRENCYID is not null) 
            and (@CURRENCYID <> @ORGANIZATIONCURRENCYID)
            and @CURRENCYID <> REVENUERECOGNITION.BASECURRENCYID    
and FINANCIALTRANSACTIONLINEITEM.TYPECODE = 0  -- Standard line items only

        and FINANCIALTRANSACTIONLINEITEM.DELETEDON is NULL -- Non deleted line items only

        and FINANCIALTRANSACTION.DELETEDON is NULL
        and ((FINANCIALTRANSACTION.TYPECODE between 0 and 9) or (FINANCIALTRANSACTION.TYPECODE = 15)) -- Only FT that used to be in the REVENUE table.

)