UFN_PLEDGE_GETREVALUEDINSTALLMENTSPLITBALANCES

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

                declare @TRANSACTIONCURRENCYID uniqueidentifier;
                declare @BASECURRENCYID uniqueidentifier;
                declare @ORGANIZATIONCURRENCYID uniqueidentifier = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();
                declare @BASEDECIMALDIGITS int;
                declare @ORGANIZATIONDECIMALDIGITS int;

                select
                    @TRANSACTIONCURRENCYID = FINANCIALTRANSACTION.TRANSACTIONCURRENCYID,
                    @BASECURRENCYID = CURRENCYSET.BASECURRENCYID
                from dbo.FINANCIALTRANSACTION
                    inner join dbo.PDACCOUNTSYSTEM on FINANCIALTRANSACTION.PDACCOUNTSYSTEMID = PDACCOUNTSYSTEM.ID
                    inner join dbo.CURRENCYSET on PDACCOUNTSYSTEM.CURRENCYSETID = CURRENCYSET.ID                
                where FINANCIALTRANSACTION.ID = @PLEDGEID;

                select @BASEDECIMALDIGITS = DECIMALDIGITS from dbo.CURRENCY where ID = @BASECURRENCYID;
                select @ORGANIZATIONDECIMALDIGITS = DECIMALDIGITS from dbo.CURRENCY where ID = @ORGANIZATIONCURRENCYID;

                --If all the currencies are the same, we can skip the conversions and just return transaction currency for all values.

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

                    declare @DESIGNATIONAMOUNTLIST table
                    (
                        DESIGNATIONID uniqueidentifier,
                        SEQUENCE int,
                        TRANSACTIONBALANCE money,
                        BASEBALANCE money,
                        ORGANIZATIONBALANCE money,
                        TSLONG bigint
                    );

                    declare @INSTALLMENTAMOUNTLIST table
                    (
                        INSTALLMENTID uniqueidentifier,
                        SEQUENCE int,
                        TRANSACTIONBALANCE money,
                        BASEBALANCE money,
                        ORGANIZATIONBALANCE money,
                        TSLONG bigint
                    );

                    declare @INSTALLMENTMAX integer;    
                    select 
                        @INSTALLMENTMAX = max(SEQUENCE)
                    from 
                        dbo.INSTALLMENT 
                    where 
                        INSTALLMENT.REVENUEID = @PLEDGEID;

                    --Get split information, including their total balances.

                    insert into @DESIGNATIONAMOUNTLIST(DESIGNATIONID, SEQUENCE, TRANSACTIONBALANCE, BASEBALANCE, ORGANIZATIONBALANCE, TSLONG) 
                    select
                        REVENUESPLIT_EXT.DESIGNATIONID,
                        FINANCIALTRANSACTIONLINEITEM.SEQUENCE,
                        REVALUED.TRANSACTIONBALANCE,
                        REVALUED.BASEBALANCE,
                        REVALUED.ORGANIZATIONBALANCE,
                        FINANCIALTRANSACTIONLINEITEM.TSLONG
                    from 
                        dbo.FINANCIALTRANSACTIONLINEITEM
                        inner join dbo.REVENUESPLIT_EXT on REVENUESPLIT_EXT.ID = FINANCIALTRANSACTIONLINEITEM.ID
                        inner join dbo.UFN_PLEDGE_GETREVALUEDSPLITBALANCES(
                            @PLEDGEID, 
                            @ASOFDATE, 
                            @USEORIGINALRATE
                        ) as REVALUED on REVALUED.ID = FINANCIALTRANSACTIONLINEITEM.ID
                    where 
                        FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = @PLEDGEID
                        and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null
                        and FINANCIALTRANSACTIONLINEITEM.TYPECODE = 0;

                    --Get info for all the installments on the given pledge.

                    insert into @INSTALLMENTAMOUNTLIST(INSTALLMENTID, SEQUENCE, TRANSACTIONBALANCE, BASEBALANCE, ORGANIZATIONBALANCE, TSLONG) 
                    select
                        INSTALLMENT.ID,
                        INSTALLMENT.SEQUENCE,
                        REVALUED.TRANSACTIONBALANCE,
                        REVALUED.BASEBALANCE,
                        REVALUED.ORGANIZATIONBALANCE,
                        INSTALLMENT.TSLONG
                    from 
                        dbo.INSTALLMENT
                        inner join dbo.UFN_PLEDGE_GETREVALUEDINSTALLMENTBALANCES(
                            @PLEDGEID, 
                            @ASOFDATE, 
                            @USEORIGINALRATE
                        ) as REVALUED on REVALUED.ID = INSTALLMENT.ID
                    where 
                        INSTALLMENT.REVENUEID = @PLEDGEID;    

                    --Join the installment and split lists to build out info on installment splits.

                    declare @INSTALLMENTSPLIT table(
                        INSTALLMENTID uniqueidentifier,
                        INSTALLMENTSEQUENCE int,
                        TRANSACTIONINSTALLMENTBALANCE money,
                        BASEINSTALLMENTBALANCE money,
                        ORGANIZATIONINSTALLMENTBALANCE money,
                        INSTALLMENTSPLITID uniqueidentifier,
                        TRANSACTIONBALANCE money,
                        DESIGNATIONID uniqueidentifier,
                        DESIGNATIONSEQUENCE int,
                        DESIGNATIONTSLONG bigint,
                        BASEDESIGNATIONBALANCE money,
                        ORGANIZATIONDESIGNATIONBALANCE money
                    )        
                    insert into @INSTALLMENTSPLIT    
                    select 
                        INSTALLMENTLIST.INSTALLMENTID,
                        INSTALLMENTLIST.SEQUENCE INSTALLMENTSEQUENCE,
                        INSTALLMENTLIST.TRANSACTIONBALANCE as TRANSACTIONINSTALLMENTBALANCE,
                        INSTALLMENTLIST.BASEBALANCE as BASEINSTALLMENTBALANCE,
                        INSTALLMENTLIST.ORGANIZATIONBALANCE as ORGANIZATIONINSTALLMENTBALANCE,
                        INSTALLMENTSPLIT.ID as INSTALLMENTSPLITID,
                        INSTALLMENTSPLIT.TRANSACTIONAMOUNT - (
                            coalesce(
                                (select
                                    sum(INSTALLMENTSPLITPAYMENT.AMOUNT)
                                from dbo.INSTALLMENTSPLITPAYMENT
                                    inner join dbo.FINANCIALTRANSACTIONLINEITEM as PAYMENTSPLIT on PAYMENTSPLIT.ID = INSTALLMENTSPLITPAYMENT.PAYMENTID
                                    inner join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTION.ID = PAYMENTSPLIT.FINANCIALTRANSACTIONID
                                where INSTALLMENTSPLITPAYMENT.INSTALLMENTSPLITID = INSTALLMENTSPLIT.ID
                                    and FINANCIALTRANSACTION.DATE <= @ASOFDATE)
                            , 0)
                            + coalesce(
                                (select
                                    sum(INSTALLMENTSPLITWRITEOFF.TRANSACTIONAMOUNT)
                                from dbo.INSTALLMENTSPLITWRITEOFF 
                                    inner join dbo.WRITEOFF on INSTALLMENTSPLITWRITEOFF.WRITEOFFID = WRITEOFF.ID
                                where INSTALLMENTSPLITWRITEOFF.INSTALLMENTSPLITID = INSTALLMENTSPLIT.ID
                                    and WRITEOFF.DATE <= @ASOFDATE)
                            , 0)
                        ) as TRANSACTIONBALANCE,
                        DESIGNATIONLIST.DESIGNATIONID,
                        DESIGNATIONLIST.SEQUENCE as DESIGNATIONSEQUENCE,
                        DESIGNATIONLIST.TSLONG as DESIGNATIONTSLONG,
                        DESIGNATIONLIST.BASEBALANCE as BASEDESIGNATIONBALANCE,
                        DESIGNATIONLIST.ORGANIZATIONBALANCE as ORGANIZATIONDESIGNATIONBALANCE
                    from @INSTALLMENTAMOUNTLIST INSTALLMENTLIST
                        inner join dbo.INSTALLMENTSPLIT on INSTALLMENTLIST.INSTALLMENTID = INSTALLMENTSPLIT.INSTALLMENTID
                        inner join @DESIGNATIONAMOUNTLIST DESIGNATIONLIST on DESIGNATIONLIST.DESIGNATIONID = INSTALLMENTSPLIT.DESIGNATIONID

                    --For each included installment split, calculate rollups of all previous installments splits 

                    --    that share the installment, when ordered by split.  We'll need rollups both including and excluding 

                    --    the current installment splits.

                    declare @INSTALLMENTROLLUP table(
                        INSTALLMENTID uniqueidentifier,
                        INSTALLMENTSEQUENCE bigint,
                        TRANSACTIONINSTALLMENTBALANCE money,
                        BASEINSTALLMENTBALANCE money,
                        ORGANIZATIONINSTALLMENTBALANCE money,
                        DESIGNATIONID uniqueidentifier,
                        INSTALLMENTSPLITID uniqueidentifier,
                        TRANSACTIONBALANCE money,
                        PREVIOUSTRANSACTIONRUNNINGTOTAL money,
                        TRANSACTIONRUNNINGTOTAL money    
                    )
                    insert into @INSTALLMENTROLLUP
                    select
                        CURRENTINSTALLMENTSPLIT.INSTALLMENTID,
                        CURRENTINSTALLMENTSPLIT.INSTALLMENTSEQUENCE,
                        CURRENTINSTALLMENTSPLIT.TRANSACTIONINSTALLMENTBALANCE,
                        CURRENTINSTALLMENTSPLIT.BASEINSTALLMENTBALANCE,
                        CURRENTINSTALLMENTSPLIT.ORGANIZATIONINSTALLMENTBALANCE,
                        CURRENTINSTALLMENTSPLIT.DESIGNATIONID,
                        CURRENTINSTALLMENTSPLIT.INSTALLMENTSPLITID,
                        CURRENTINSTALLMENTSPLIT.TRANSACTIONBALANCE,
                        coalesce(SUM(PREVIOUSINSTALLMENTSPLIT.TRANSACTIONBALANCE),0) 
                            - CURRENTINSTALLMENTSPLIT.TRANSACTIONBALANCE PREVIOUSTRANSACTIONRUNNINGTOTAL,
                        coalesce(SUM(PREVIOUSINSTALLMENTSPLIT.TRANSACTIONBALANCE),0) TRANSACTIONRUNNINGTOTAL        
                    from @INSTALLMENTSPLIT CURRENTINSTALLMENTSPLIT
                        cross join @INSTALLMENTSPLIT PREVIOUSINSTALLMENTSPLIT
                    where PREVIOUSINSTALLMENTSPLIT.INSTALLMENTID = CURRENTINSTALLMENTSPLIT.INSTALLMENTID
                        and(
                            PREVIOUSINSTALLMENTSPLIT.DESIGNATIONSEQUENCE < CURRENTINSTALLMENTSPLIT.DESIGNATIONSEQUENCE
                            or(
                                PREVIOUSINSTALLMENTSPLIT.DESIGNATIONSEQUENCE = CURRENTINSTALLMENTSPLIT.DESIGNATIONSEQUENCE
                                and PREVIOUSINSTALLMENTSPLIT.DESIGNATIONTSLONG <= CURRENTINSTALLMENTSPLIT.DESIGNATIONTSLONG
                            )
                        )
                    group by 
                        CURRENTINSTALLMENTSPLIT.INSTALLMENTID,
                        CURRENTINSTALLMENTSPLIT.INSTALLMENTSEQUENCE,
                        CURRENTINSTALLMENTSPLIT.TRANSACTIONINSTALLMENTBALANCE,
                        CURRENTINSTALLMENTSPLIT.BASEINSTALLMENTBALANCE,
                        CURRENTINSTALLMENTSPLIT.ORGANIZATIONINSTALLMENTBALANCE,
                        CURRENTINSTALLMENTSPLIT.DESIGNATIONID,
                        CURRENTINSTALLMENTSPLIT.INSTALLMENTSPLITID,
                        CURRENTINSTALLMENTSPLIT.TRANSACTIONBALANCE

                    --Calculate the balances on the installment splits

                    declare @INTERMEDIATEBALANCE table(
                        INSTALLMENTSPLITID uniqueidentifier,
                        INSTALLMENTSEQUENCE bigint,
                        DESIGNATIONID uniqueidentifier,
                        TRANSACTIONBALANCE money,
                        BASEBALANCE money,
                        ORGANIZATIONBALANCE money
                    )
                    insert into @INTERMEDIATEBALANCE
                    --For all installments but the last, we convert by proportion the calculated rollups

                    --    and take their difference to get the balance for each installment split.

                    select
                        INSTALLMENTSPLITID,
                        INSTALLMENTROLLUP.INSTALLMENTSEQUENCE,
                        INSTALLMENTROLLUP.DESIGNATIONID,
                        INSTALLMENTROLLUP.TRANSACTIONBALANCE,
                        CURRENTRUNNINGTOTAL.BASEAMOUNT - PREVIOUSRUNNINGTOTAL.BASEAMOUNT BASEBALANCE,
                        CURRENTRUNNINGTOTAL.ORGANIZATIONAMOUNT - PREVIOUSRUNNINGTOTAL.ORGANIZATIONAMOUNT ORGANIZATIONBALANCE
                    from @INSTALLMENTROLLUP INSTALLMENTROLLUP
                        cross apply dbo.UFN_CURRENCY_GETCURRENCYVALUESBYPROPORTION(
                            INSTALLMENTROLLUP.TRANSACTIONRUNNINGTOTAL,
                            INSTALLMENTROLLUP.TRANSACTIONINSTALLMENTBALANCE,
                            @BASECURRENCYID,
                            INSTALLMENTROLLUP.BASEINSTALLMENTBALANCE,
                            @BASEDECIMALDIGITS,
                            @TRANSACTIONCURRENCYID,
                            default,
                            @ORGANIZATIONCURRENCYID,
                            INSTALLMENTROLLUP.ORGANIZATIONINSTALLMENTBALANCE,
                            @ORGANIZATIONDECIMALDIGITS,
                            default
                        ) CURRENTRUNNINGTOTAL
                        cross apply dbo.UFN_CURRENCY_GETCURRENCYVALUESBYPROPORTION(
                            INSTALLMENTROLLUP.PREVIOUSTRANSACTIONRUNNINGTOTAL,
                            INSTALLMENTROLLUP.TRANSACTIONINSTALLMENTBALANCE,
                            @BASECURRENCYID,
                            INSTALLMENTROLLUP.BASEINSTALLMENTBALANCE,
                            @BASEDECIMALDIGITS,
                            @TRANSACTIONCURRENCYID,
                            default,
                            @ORGANIZATIONCURRENCYID,
                            INSTALLMENTROLLUP.ORGANIZATIONINSTALLMENTBALANCE,
                            @ORGANIZATIONDECIMALDIGITS,
                            default
                        ) PREVIOUSRUNNINGTOTAL
                    where INSTALLMENTROLLUP.INSTALLMENTSEQUENCE < @INSTALLMENTMAX

                    --For the installment that is the last on the pledge, include a row in the table, but don't calculate anything yet.

                    union 
                    select
                        INSTALLMENTSPLITID,
                        INSTALLMENTROLLUP.INSTALLMENTSEQUENCE,
                        INSTALLMENTROLLUP.DESIGNATIONID,
                        INSTALLMENTROLLUP.TRANSACTIONBALANCE,
                        0 BASEBALANCE,
                        0 ORGANIZATIONBALANCE
                    from @INSTALLMENTROLLUP INSTALLMENTROLLUP
                    where INSTALLMENTROLLUP.INSTALLMENTSEQUENCE = @INSTALLMENTMAX

                    --Pump balances for all but the last installment into the output table.

                    insert into @BALANCE(ID, TRANSACTIONBALANCE, BASEBALANCE, ORGANIZATIONBALANCE)
                    select
                        INTERMEDIATEBALANCE.INSTALLMENTSPLITID,
                        INTERMEDIATEBALANCE.TRANSACTIONBALANCE,
                        INTERMEDIATEBALANCE.BASEBALANCE,
                        INTERMEDIATEBALANCE.ORGANIZATIONBALANCE
                    from @INTERMEDIATEBALANCE INTERMEDIATEBALANCE
                    where  INTERMEDIATEBALANCE.INSTALLMENTSEQUENCE < @INSTALLMENTMAX;


                    --For each split, calculate the total converted balances of the installment splits within it.  Then,

                    --    subtract that from the split total balance to get the balances for the installment splits of

                    --    the last installment

                    with CTE_SPLITROLLUP
                    as(
                        select
                            INTERMEDIATEBALANCE.DESIGNATIONID,
                            sum(INTERMEDIATEBALANCE.BASEBALANCE) BASEROLLUP,
                            sum(INTERMEDIATEBALANCE.ORGANIZATIONBALANCE) ORGANIZATIONROLLUP
                        from @INTERMEDIATEBALANCE INTERMEDIATEBALANCE
                        group by INTERMEDIATEBALANCE.DESIGNATIONID
                    )
                    insert into @BALANCE(ID, TRANSACTIONBALANCE, BASEBALANCE, ORGANIZATIONBALANCE)
                    select
                        INSTALLMENTSPLIT.INSTALLMENTSPLITID,
                        INSTALLMENTSPLIT.TRANSACTIONBALANCE,        
                        INSTALLMENTSPLIT.BASEDESIGNATIONBALANCE - CTE_SPLITROLLUP.BASEROLLUP,
                        INSTALLMENTSPLIT.ORGANIZATIONDESIGNATIONBALANCE - CTE_SPLITROLLUP.ORGANIZATIONROLLUP
                    from @INSTALLMENTSPLIT INSTALLMENTSPLIT
                        inner join CTE_SPLITROLLUP on CTE_SPLITROLLUP.DESIGNATIONID = INSTALLMENTSPLIT.DESIGNATIONID
                    where INSTALLMENTSPLIT.INSTALLMENTSEQUENCE = @INSTALLMENTMAX

                end

                return

            end