UFN_PLEDGE_GETREVALUEDINSTALLMENTBALANCES

CREATE function dbo.UFN_PLEDGE_GETREVALUEDINSTALLMENTBALANCES
(
    @PLEDGEID uniqueidentifier,
    @ASOFDATE datetime,
    @USEORIGINALRATE bit = 0
) 
returns @BALANCE table (
    ID uniqueidentifier,
    TRANSACTIONBALANCE money,
    BASEBALANCE money,
    ORGANIZATIONBALANCE money
)
with execute as caller
as begin

    -- Get currency data in preparation for conversion.


    declare @TRANSACTIONCURRENCYID uniqueidentifier;
    declare @BASECURRENCYID uniqueidentifier;
    declare @ORGANIZATIONCURRENCYID uniqueidentifier;

    set @ORGANIZATIONCURRENCYID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();

    select
        @TRANSACTIONCURRENCYID = TRANSACTIONCURRENCYID,
        @BASECURRENCYID = isnull(REVENUE_EXT.NONPOSTABLEBASECURRENCYID, V.BASECURRENCYID)
    from dbo.FINANCIALTRANSACTION REVENUE
    inner join dbo.REVENUE_EXT on REVENUE.ID = REVENUE_EXT.ID
    left outer join dbo.V_BASECURRENCYFORFINANCIALTRANSACTION_I V with (noexpand) on REVENUE.ID = V.FINANCIALTRANSACTIONID
    where REVENUE.ID = @PLEDGEID
        and REVENUE.DELETEDON is null;

    -- Short-circuit single-currency evaluation.


    if (@TRANSACTIONCURRENCYID = @ORGANIZATIONCURRENCYID) and (@BASECURRENCYID = @ORGANIZATIONCURRENCYID)
    begin
        with CTE_BALANCE
        as(
            select
                INSTALLMENT.ID,
                INSTALLMENT.TRANSACTIONAMOUNT - (
                    coalesce((
                        select sum(INSTALLMENTPAYMENT.AMOUNT)
                        from dbo.INSTALLMENTPAYMENT
                            inner join dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTIONLINEITEM.ID = INSTALLMENTPAYMENT.PAYMENTID
                            inner join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTION.ID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID
                        where INSTALLMENTPAYMENT.INSTALLMENTID = INSTALLMENT.ID
                            and FINANCIALTRANSACTION.DATE <= @ASOFDATE), 0
                    )
                    + coalesce((
                        select sum(INSTALLMENTWRITEOFF.TRANSACTIONAMOUNT)
                        from dbo.INSTALLMENTWRITEOFF
                            inner join dbo.WRITEOFF on INSTALLMENTWRITEOFF.WRITEOFFID = WRITEOFF.ID
                        where INSTALLMENTWRITEOFF.INSTALLMENTID = INSTALLMENT.ID
                            and WRITEOFF.DATE <= @ASOFDATE), 0
                    )
                ) as AMOUNT
            from dbo.INSTALLMENT 
            where INSTALLMENT.REVENUEID = @PLEDGEID
        )
        insert into @BALANCE(
            ID,
            TRANSACTIONBALANCE,
            BASEBALANCE,
            ORGANIZATIONBALANCE
        )
        select
            CTE_BALANCE.ID,
            CTE_BALANCE.AMOUNT,
            CTE_BALANCE.AMOUNT,
            CTE_BALANCE.AMOUNT
        from CTE_BALANCE            
    end
    else
    begin

        declare @TRANSACTIONFULLAMOUNT money = dbo.UFN_PLEDGE_GETBALANCEASOF(@PLEDGEID, @ASOFDATE);
        declare @BASEFULLAMOUNT money  = dbo.UFN_PLEDGE_GETREVALUEDBASEBALANCEASOF(@PLEDGEID, @ASOFDATE, @USEORIGINALRATE);
        declare @ORGANIZATIONFULLAMOUNT money  = dbo.UFN_PLEDGE_GETREVALUEDORGANIZATIONBALANCEASOF(@PLEDGEID, @ASOFDATE, @USEORIGINALRATE);

        declare @BASECURRENCYDECIMALDIGITS int
        select @BASECURRENCYDECIMALDIGITS = CURRENCY.DECIMALDIGITS
        from dbo.CURRENCY
        where CURRENCY.ID = @BASECURRENCYID 

        declare @ORGANIZATIONCURRENCYDECIMALDIGITS int
        select @ORGANIZATIONCURRENCYDECIMALDIGITS = CURRENCY.DECIMALDIGITS
        from dbo.CURRENCY
        where CURRENCY.ID = @ORGANIZATIONCURRENCYID

        -- Convert the split balances proportionally, so they will add up to the total pledge

        -- balance in each currency.


        --Calculate the transaction balance on each installment.

        declare @INSTALLMENT table(
            ID uniqueidentifier,
            SEQUENCE int,
            TSLONG bigint,
            TRANSACTIONBALANCE money
        )        
        insert into @INSTALLMENT(
            ID,
            SEQUENCE,
            TSLONG,
            TRANSACTIONBALANCE
        )        
        select
            INSTALLMENT.ID,
            INSTALLMENT.SEQUENCE,
            INSTALLMENT.TSLONG,
            INSTALLMENT.TRANSACTIONAMOUNT - (
                coalesce((
                    select sum(INSTALLMENTPAYMENT.AMOUNT)
                    from dbo.INSTALLMENTPAYMENT
                        inner join dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTIONLINEITEM.ID = INSTALLMENTPAYMENT.PAYMENTID
                        inner join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTION.ID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID
                    where INSTALLMENTPAYMENT.INSTALLMENTID = INSTALLMENT.ID
                        and FINANCIALTRANSACTION.DATE <= @ASOFDATE), 0
                )
                + coalesce((
                    select sum(INSTALLMENTWRITEOFF.TRANSACTIONAMOUNT)
                    from dbo.INSTALLMENTWRITEOFF
                        inner join dbo.WRITEOFF on INSTALLMENTWRITEOFF.WRITEOFFID = WRITEOFF.ID
                    where INSTALLMENTWRITEOFF.INSTALLMENTID = INSTALLMENT.ID
                        and WRITEOFF.DATE <= @ASOFDATE), 0
                )
            ) as AMOUNT
        from dbo.INSTALLMENT 
        where INSTALLMENT.REVENUEID = @PLEDGEID

        --For any given installment, calculate the summation of the amounts on all the installments previous to it.

        --    We'll need that value both including and excluding the current installment

        declare @INSTALLMENTROLLUP table(
            ID uniqueidentifier,
            TRANSACTIONBALANCE money,
            PREVIOUSTRANSACTIONRUNNINGTOTAL money,
            TRANSACTIONRUNNINGTOTAL money    
        )
        insert into @INSTALLMENTROLLUP(
            ID,
            TRANSACTIONBALANCE,
            PREVIOUSTRANSACTIONRUNNINGTOTAL,
            TRANSACTIONRUNNINGTOTAL    
        )
        select
            INSTALLMENT.ID,
            INSTALLMENT.TRANSACTIONBALANCE,
            coalesce(SUM(PREVIOUS.TRANSACTIONBALANCE),0) 
                - INSTALLMENT.TRANSACTIONBALANCE 
            as PREVIOUSTRANSACTIONRUNNINGTOTAL,
            coalesce(SUM(PREVIOUS.TRANSACTIONBALANCE),0) as TRANSACTIONRUNNINGTOTAL        
        from @INSTALLMENT INSTALLMENT
            cross join @INSTALLMENT PREVIOUS
        where PREVIOUS.SEQUENCE < INSTALLMENT.SEQUENCE
            or(PREVIOUS.SEQUENCE = INSTALLMENT.SEQUENCE
                and PREVIOUS.TSLONG <= INSTALLMENT.TSLONG
            )
        group by 
            INSTALLMENT.ID,
            INSTALLMENT.TRANSACTIONBALANCE

        --Convert the summations calculated previously and take their difference to

        --     to get the converted balance.

        insert into @BALANCE(
            ID,
            TRANSACTIONBALANCE,
            BASEBALANCE,
            ORGANIZATIONBALANCE
        )
        select
            INSTALLMENTROLLUP.ID,
            INSTALLMENTROLLUP.TRANSACTIONBALANCE,
            CURRENTRUNNINGTOTAL.BASEAMOUNT - PREVIOUSRUNNINGTOTAL.BASEAMOUNT BASEBALANCE,
            CURRENTRUNNINGTOTAL.ORGANIZATIONAMOUNT - PREVIOUSRUNNINGTOTAL.ORGANIZATIONAMOUNT ORGANIZATIONBALANCE
        from @INSTALLMENTROLLUP INSTALLMENTROLLUP
            cross apply dbo.UFN_CURRENCY_GETCURRENCYVALUESBYPROPORTION(
                INSTALLMENTROLLUP.TRANSACTIONRUNNINGTOTAL,
                @TRANSACTIONFULLAMOUNT,
                @BASECURRENCYID,
                @BASEFULLAMOUNT,
                @BASECURRENCYDECIMALDIGITS,
                @TRANSACTIONCURRENCYID,
                default,
                @ORGANIZATIONCURRENCYID,
                @ORGANIZATIONFULLAMOUNT,
                @ORGANIZATIONCURRENCYDECIMALDIGITS,
                default
            ) CURRENTRUNNINGTOTAL
            cross apply dbo.UFN_CURRENCY_GETCURRENCYVALUESBYPROPORTION(
                INSTALLMENTROLLUP.PREVIOUSTRANSACTIONRUNNINGTOTAL,
                @TRANSACTIONFULLAMOUNT,
                @BASECURRENCYID,
                @BASEFULLAMOUNT,
                @BASECURRENCYDECIMALDIGITS,
                @TRANSACTIONCURRENCYID,
                default,
                @ORGANIZATIONCURRENCYID,
                @ORGANIZATIONFULLAMOUNT,
                @ORGANIZATIONCURRENCYDECIMALDIGITS,
                default
            ) PREVIOUSRUNNINGTOTAL
    end

    return
end