UFN_PLEDGE_GETBALANCEASOFINCURRENCY_BULK

CREATE function dbo.UFN_PLEDGE_GETBALANCEASOFINCURRENCY_BULK
(
    @CURRENCYID uniqueidentifier,    -- if null and @CURRENCYCODE is not in (0,2), transaction currency will be used

    @ORGANIZATIONCURRENCYID uniqueidentifier,
    @DECIMALDIGITS tinyint,
    @ROUNDINGTYPECODE tinyint,
    @ASOFDATE datetime,
    @ORIGINCODE tinyint,
    @CURRENCYCODE tinyint    -- 0 = base, 2 = transaction, else = use passed-in-values

)
returns table
as
return
(
    with REVENUECTE
    as
    (
        select
            REVENUE.ID,
            SUM(
                INSTALLMENT.TRANSACTIONAMOUNT
                - coalesce(PLEDGEINSTALLMENTPAYMENT.AMOUNT,0)
                - coalesce(PLEDGEINSTALLMENTWRITEOFF.AMOUNT,0)
            ) as BALANCEINCURRENCY,
            REVENUE.TRANSACTIONCURRENCYID,
            V.BASECURRENCYID,
            cast(REVENUE.DATE as datetime) [DATE],
            REVENUE.TYPECODE TRANSACTIONTYPECODE,
            coalesce(BASEEXCHANGERATE.RATE,1) as ORIGINALBASERATE,
            coalesce(ORGEXCHANGERATE.RATE,1) as ORIGINALORGANIZATIONRATE,
            case @CURRENCYCODE
                when 0 then V.BASECURRENCYID
                when 2 then REVENUE.TRANSACTIONCURRENCYID
                else @CURRENCYID
            end as CURRENCYID,
            REVENUE.TYPE TRANSACTIONTYPE,
            REVENUE.CONSTITUENTID,
            REVENUE.TRANSACTIONAMOUNT,
            REVENUE.BASEAMOUNT AMOUNT,
            REVENUE.ORGAMOUNT ORGANIZATIONAMOUNT            
        from
            dbo.FINANCIALTRANSACTION REVENUE with (nolock)
        left outer join  dbo.V_BASECURRENCYFORFINANCIALTRANSACTION_I V with (noexpand) on REVENUE.ID = V.FINANCIALTRANSACTIONID
        left join dbo.INSTALLMENT on INSTALLMENT.REVENUEID = REVENUE.ID
        left join(
            select INSTALLMENTSPLIT.INSTALLMENTID, sum(INSTALLMENTSPLITPAYMENT.AMOUNT) AMOUNT
            from dbo.INSTALLMENTSPLITPAYMENT
            inner join dbo.INSTALLMENTSPLIT on INSTALLMENTSPLIT.ID = INSTALLMENTSPLITPAYMENT.INSTALLMENTSPLITID
            inner join dbo.FINANCIALTRANSACTIONLINEITEM REVENUESPLIT on REVENUESPLIT.ID = INSTALLMENTSPLITPAYMENT.PAYMENTID
            inner join dbo.FINANCIALTRANSACTION REVENUE on REVENUE.ID = REVENUESPLIT.FINANCIALTRANSACTIONID
            where REVENUE.TYPECODE in (0,1,2,3,4,5,6,7,8,9) and REVENUESPLIT.DELETEDON is null and REVENUESPLIT.TYPECODE != 1
                and cast(REVENUE.DATE as datetime) <= @ASOFDATE
            group by INSTALLMENTSPLIT.INSTALLMENTID
        ) PLEDGEINSTALLMENTPAYMENT on PLEDGEINSTALLMENTPAYMENT.INSTALLMENTID = INSTALLMENT.ID
        left join (
            select INSTALLMENTSPLIT.INSTALLMENTID, sum(INSTALLMENTSPLITWRITEOFF.TRANSACTIONAMOUNT) AMOUNT
            from dbo.INSTALLMENTSPLITWRITEOFF
            inner join dbo.INSTALLMENTSPLIT on INSTALLMENTSPLIT.ID = INSTALLMENTSPLITWRITEOFF.INSTALLMENTSPLITID
            inner join dbo.FINANCIALTRANSACTION WRITEOFF on INSTALLMENTSPLITWRITEOFF.WRITEOFFID = WRITEOFF.ID
            where WRITEOFF.TYPECODE = 20 and WRITEOFF.DELETEDON is null
                and cast(WRITEOFF.DATE as datetime) <= @ASOFDATE
            group by INSTALLMENTSPLIT.INSTALLMENTID
        ) PLEDGEINSTALLMENTWRITEOFF on PLEDGEINSTALLMENTWRITEOFF.INSTALLMENTID = INSTALLMENT.ID
        left join dbo.CURRENCYEXCHANGERATE BASEEXCHANGERATE on BASEEXCHANGERATE.ID = REVENUE.BASEEXCHANGERATEID
        left join dbo.CURRENCYEXCHANGERATE ORGEXCHANGERATE on ORGEXCHANGERATE.ID = REVENUE.ORGEXCHANGERATEID
        where REVENUE.TYPECODE in (0,1,2,3,4,5,6,7,8,9) and REVENUE.DELETEDON is null
        group by 
            REVENUE.ID, 
            REVENUE.TRANSACTIONCURRENCYID,
            V.BASECURRENCYID,
            REVENUE.TYPECODE,
            REVENUE.DATE,
            BASEEXCHANGERATE.RATE,
            ORGEXCHANGERATE.RATE,
            REVENUE.TYPE,
            REVENUE.CONSTITUENTID,
            REVENUE.TRANSACTIONAMOUNT,
            REVENUE.BASEAMOUNT,
            REVENUE.ORGAMOUNT
    )
    select            -- Transaction currency

        REVENUECTE.ID, 
        REVENUECTE.BALANCEINCURRENCY,
        REVENUECTE.TRANSACTIONCURRENCYID,
        REVENUECTE.BASECURRENCYID,
        REVENUECTE.DATE,
        REVENUECTE.TRANSACTIONTYPECODE,
          REVENUECTE.TRANSACTIONTYPE,
        REVENUECTE.CONSTITUENTID,
        REVENUECTE.TRANSACTIONAMOUNT,
        REVENUECTE.AMOUNT,
        REVENUECTE.ORGANIZATIONAMOUNT
    from REVENUECTE
    where (REVENUECTE.CURRENCYID is null) 
        or REVENUECTE.CURRENCYID = REVENUECTE.TRANSACTIONCURRENCYID

    union all        -- Base or org currency


    select 
        REVENUECTE.ID,
        case
            when REVENUECTE.CURRENCYID = @ORGANIZATIONCURRENCYID
                then                                                        
                    case @ORIGINCODE
                        when 0
                            then 
                                case
                                    when REVENUECTE.TRANSACTIONCURRENCYID = REVENUECTE.BASECURRENCYID
                                        then dbo.UFN_CURRENCY_ROUND(dbo.UFN_CURRENCY_APPLYRATE(REVENUECTE.BALANCEINCURRENCY, REVENUECTE.ORIGINALORGANIZATIONRATE), @DECIMALDIGITS, @ROUNDINGTYPECODE)
                                    when REVENUECTE.CURRENCYID = REVENUECTE.BASECURRENCYID and @CURRENCYCODE = 0
                                        then dbo.UFN_CURRENCY_ROUND(dbo.UFN_CURRENCY_APPLYRATE(REVENUECTE.BALANCEINCURRENCY, REVENUECTE.ORIGINALBASERATE), BASECURRENCY.DECIMALDIGITS, BASECURRENCY.ROUNDINGTYPECODE)
                                    else dbo.UFN_CURRENCY_ROUND(dbo.UFN_CURRENCY_APPLYRATE(dbo.UFN_CURRENCY_ROUND(dbo.UFN_CURRENCY_APPLYRATE(REVENUECTE.BALANCEINCURRENCY, REVENUECTE.ORIGINALBASERATE), BASECURRENCY.DECIMALDIGITS, BASECURRENCY.ROUNDINGTYPECODE), REVENUECTE.ORIGINALORGANIZATIONRATE), @DECIMALDIGITS, @ROUNDINGTYPECODE)
                                end
                        when 1
                            then dbo.UFN_CURRENCY_ROUND(dbo.UFN_CURRENCY_APPLYRATE(REVENUECTE.BALANCEINCURRENCY, REVENUECTE.ORIGINALORGANIZATIONRATE), @DECIMALDIGITS, @ROUNDINGTYPECODE)
                    end
            when REVENUECTE.CURRENCYID = REVENUECTE.BASECURRENCYID
                then dbo.UFN_CURRENCY_ROUND(dbo.UFN_CURRENCY_APPLYRATE(REVENUECTE.BALANCEINCURRENCY, REVENUECTE.ORIGINALBASERATE), BASECURRENCY.DECIMALDIGITS, BASECURRENCY.ROUNDINGTYPECODE)
            else 0
        end [BALANCEINCURRENCY],

        REVENUECTE.TRANSACTIONCURRENCYID,
        REVENUECTE.BASECURRENCYID,
        REVENUECTE.DATE,
        REVENUECTE.TRANSACTIONTYPECODE,
          REVENUECTE.TRANSACTIONTYPE,
        REVENUECTE.CONSTITUENTID,
        REVENUECTE.TRANSACTIONAMOUNT,
        REVENUECTE.AMOUNT,
        REVENUECTE.ORGANIZATIONAMOUNT
    from REVENUECTE
    left join dbo.CURRENCY BASECURRENCY on BASECURRENCY.ID = REVENUECTE.BASECURRENCYID
    where (REVENUECTE.CURRENCYID is not null)
        and (REVENUECTE.CURRENCYID <> REVENUECTE.TRANSACTIONCURRENCYID)
        and (REVENUECTE.CURRENCYID = @ORGANIZATIONCURRENCYID
            or REVENUECTE.CURRENCYID = REVENUECTE.BASECURRENCYID)

    union all        -- Arbitrary currency


    select 
        REVENUECTE.ID,
        case
            when LATESTEXCHANGERATE.RATE is not null
                then dbo.UFN_CURRENCY_ROUND(dbo.UFN_CURRENCY_APPLYRATE(REVENUECTE.BALANCEINCURRENCY, LATESTEXCHANGERATE.RATE), @DECIMALDIGITS, @ROUNDINGTYPECODE)
            when LATESTINVERSEEXCHANGERATE.RATE is not null
                then dbo.UFN_CURRENCY_ROUND(dbo.UFN_CURRENCY_APPLYRATE(REVENUECTE.BALANCEINCURRENCY, cast((1 / LATESTINVERSEEXCHANGERATE.RATE) as decimal(20,8))), @DECIMALDIGITS, @ROUNDINGTYPECODE)
            else 0
        end [BALANCEINCURRENCY],

        REVENUECTE.TRANSACTIONCURRENCYID,
        REVENUECTE.BASECURRENCYID,
        REVENUECTE.DATE,
        REVENUECTE.TRANSACTIONTYPECODE,
          REVENUECTE.TRANSACTIONTYPE,
        REVENUECTE.CONSTITUENTID,
        REVENUECTE.TRANSACTIONAMOUNT,
        REVENUECTE.AMOUNT,
        REVENUECTE.ORGANIZATIONAMOUNT
    from REVENUECTE
        outer apply
        (
            select
                RATE
            from
                dbo.CURRENCYEXCHANGERATE
            where
                CURRENCYEXCHANGERATE.FROMCURRENCYID = REVENUECTE.TRANSACTIONCURRENCYID 
                and CURRENCYEXCHANGERATE.TOCURRENCYID = REVENUECTE.CURRENCYID
                and CURRENCYEXCHANGERATE.TYPECODE in (0,1)
                and CURRENCYEXCHANGERATE.ISLATESTRATEFORASOFDATE = 1
                and CURRENCYEXCHANGERATE.ASOFDATESDTZ <= dateadd(ms, 86399996, REVENUECTE.DATE)
                and CURRENCYEXCHANGERATE.NEXTRATEASOFDATEORMAXSDTZ >= dateadd(ms, 86399996, REVENUECTE.DATE)
        ) LATESTEXCHANGERATE
        outer apply
        (
            select
                RATE
            from
                dbo.CURRENCYEXCHANGERATE
            where
                CURRENCYEXCHANGERATE.FROMCURRENCYID = REVENUECTE.CURRENCYID
                and CURRENCYEXCHANGERATE.TOCURRENCYID = REVENUECTE.TRANSACTIONCURRENCYID
                and CURRENCYEXCHANGERATE.TYPECODE in (0,1)
                and CURRENCYEXCHANGERATE.ISLATESTRATEFORASOFDATE = 1
                and CURRENCYEXCHANGERATE.ASOFDATESDTZ <= dateadd(ms, 86399996, REVENUECTE.DATE)
                and CURRENCYEXCHANGERATE.NEXTRATEASOFDATEORMAXSDTZ >= dateadd(ms, 86399996, REVENUECTE.DATE)
        ) LATESTINVERSEEXCHANGERATE
    where (REVENUECTE.CURRENCYID is not null)
        and REVENUECTE.CURRENCYID <> REVENUECTE.TRANSACTIONCURRENCYID
        and REVENUECTE.CURRENCYID <> REVENUECTE.BASECURRENCYID
        and REVENUECTE.CURRENCYID <> @ORGANIZATIONCURRENCYID
)