UFN_PLEDGE_GETREVALUEDSPLITBALANCES

CREATE function dbo.UFN_PLEDGE_GETREVALUEDSPLITBALANCES
(
    @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;
    declare @DECIMALDIGITSBASECURRENCY int;
    declare @DECIMALDIGITSORGANIZATIONCURRENCY int;

    set @ORGANIZATIONCURRENCYID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();

    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 @DECIMALDIGITSBASECURRENCY = DECIMALDIGITS from dbo.CURRENCY where ID = @BASECURRENCYID;
    select @DECIMALDIGITSORGANIZATIONCURRENCY = DECIMALDIGITS from dbo.CURRENCY where ID = @ORGANIZATIONCURRENCYID;

    -- Get the total balance of the revenue in each currency.


    declare @TRANSACTIONFULLAMOUNT money;
    declare @BASEFULLAMOUNT money;
    declare @ORGANIZATIONFULLAMOUNT money;

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

    if (@TRANSACTIONCURRENCYID = @ORGANIZATIONCURRENCYID) and (@BASECURRENCYID = @ORGANIZATIONCURRENCYID)
    begin
        with CTE_BALANCE
        as(
            select
                FINANCIALTRANSACTIONLINEITEM.ID,
                case 
                    when FINANCIALTRANSACTION.TYPECODE = 7 
                        then --Auction donations do not have installments

                            FINANCIALTRANSACTIONLINEITEM.TRANSACTIONAMOUNT 
                            - coalesce(
                                (select
                                    sum(INSTALLMENTSPLITWRITEOFF.TRANSACTIONAMOUNT) as AMOUNT
                                from dbo.INSTALLMENTSPLIT
                                    inner join dbo.INSTALLMENTSPLITWRITEOFF on INSTALLMENTSPLITWRITEOFF.INSTALLMENTSPLITID = INSTALLMENTSPLIT.ID 
                                    inner join dbo.WRITEOFF on INSTALLMENTSPLITWRITEOFF.WRITEOFFID = WRITEOFF.ID                            
                                where 
                                    INSTALLMENTSPLIT.PLEDGEID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID
                                    and INSTALLMENTSPLIT.DESIGNATIONID = REVENUESPLIT_EXT.DESIGNATIONID
                                    and WRITEOFF.DATE <= @ASOFDATE)
                            , 0)

                    else
                        FINANCIALTRANSACTIONLINEITEM.TRANSACTIONAMOUNT - (
                            coalesce(
                                (select 
                                    sum(INSTALLMENTSPLITPAYMENT.AMOUNT) 
                                from dbo.INSTALLMENTSPLITPAYMENT
                                    inner join dbo.INSTALLMENTSPLIT on INSTALLMENTSPLITPAYMENT.INSTALLMENTSPLITID = INSTALLMENTSPLIT.ID
                                    inner join dbo.FINANCIALTRANSACTIONLINEITEM as PAYMENTSPLIT on PAYMENTSPLIT.ID = INSTALLMENTSPLITPAYMENT.PAYMENTID
                                    inner join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTION.ID = PAYMENTSPLIT.FINANCIALTRANSACTIONID
                                where
                                    INSTALLMENTSPLIT.PLEDGEID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID
                                    and INSTALLMENTSPLIT.DESIGNATIONID = REVENUESPLIT_EXT.DESIGNATIONID
                                    and FINANCIALTRANSACTION.DATE <= @ASOFDATE)
                            , 0) 
                            + coalesce(
                                (select
                                    sum(INSTALLMENTSPLITWRITEOFF.TRANSACTIONAMOUNT) as AMOUNT
                                from dbo.INSTALLMENTSPLIT
                                    inner join dbo.INSTALLMENTSPLITWRITEOFF on INSTALLMENTSPLITWRITEOFF.INSTALLMENTSPLITID = INSTALLMENTSPLIT.ID 
                                    inner join dbo.WRITEOFF on INSTALLMENTSPLITWRITEOFF.WRITEOFFID = WRITEOFF.ID 
                                where 
                                    INSTALLMENTSPLIT.PLEDGEID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID
                                    and INSTALLMENTSPLIT.DESIGNATIONID = REVENUESPLIT_EXT.DESIGNATIONID
                                    and WRITEOFF.DATE <= @ASOFDATE)
                            , 0)
                        )
                end as TRANSACTIONBALANCE
            from dbo.FINANCIALTRANSACTIONLINEITEM
                inner join dbo.REVENUESPLIT_EXT on REVENUESPLIT_EXT.ID = FINANCIALTRANSACTIONLINEITEM.ID
                inner join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTION.ID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID
            where FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = @PLEDGEID
                and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null
                and FINANCIALTRANSACTIONLINEITEM.TYPECODE = 0
        )
        insert into @BALANCE(
            ID,
            TRANSACTIONBALANCE,
            BASEBALANCE,
            ORGANIZATIONBALANCE
        )
        select
            CTE_BALANCE.ID, 
            CTE_BALANCE.TRANSACTIONBALANCE, 
            CTE_BALANCE.TRANSACTIONBALANCE, 
            CTE_BALANCE.TRANSACTIONBALANCE
        from CTE_BALANCE            
    end
    else
    begin

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

        -- balance in each currency.


        --Calculate the transaction balance on each split.

        declare @SPLIT table(
            ID uniqueidentifier,
            SEQUENCE int,
            TSLONG bigint,
            TRANSACTIONBALANCE money
        )        
        insert into @SPLIT(
            ID,
            SEQUENCE,
            TSLONG,
            TRANSACTIONBALANCE
        )
        select
            FINANCIALTRANSACTIONLINEITEM.ID,
            FINANCIALTRANSACTIONLINEITEM.SEQUENCE, 
            FINANCIALTRANSACTIONLINEITEM.TS,
            case 
                when FINANCIALTRANSACTION.TYPECODE = 7 
                    then --Auction donations do not have installments

                        FINANCIALTRANSACTIONLINEITEM.TRANSACTIONAMOUNT 
                        - coalesce(
                            (select
                                sum(INSTALLMENTSPLITWRITEOFF.TRANSACTIONAMOUNT) as AMOUNT
                            from dbo.INSTALLMENTSPLIT
                                inner join dbo.INSTALLMENTSPLITWRITEOFF on INSTALLMENTSPLITWRITEOFF.INSTALLMENTSPLITID = INSTALLMENTSPLIT.ID 
                                inner join dbo.WRITEOFF on INSTALLMENTSPLITWRITEOFF.WRITEOFFID = WRITEOFF.ID                            
                            where 
                                INSTALLMENTSPLIT.PLEDGEID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID
                                and INSTALLMENTSPLIT.DESIGNATIONID = REVENUESPLIT_EXT.DESIGNATIONID
                                and WRITEOFF.DATE <= @ASOFDATE)
                        , 0)

                else
                    FINANCIALTRANSACTIONLINEITEM.TRANSACTIONAMOUNT - (
                        coalesce(
                            (select 
                                sum(INSTALLMENTSPLITPAYMENT.AMOUNT) 
                            from dbo.INSTALLMENTSPLITPAYMENT
                                inner join dbo.INSTALLMENTSPLIT on INSTALLMENTSPLITPAYMENT.INSTALLMENTSPLITID = INSTALLMENTSPLIT.ID
                                inner join dbo.FINANCIALTRANSACTIONLINEITEM as PAYMENTSPLIT on PAYMENTSPLIT.ID = INSTALLMENTSPLITPAYMENT.PAYMENTID
                                inner join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTION.ID = PAYMENTSPLIT.FINANCIALTRANSACTIONID
                            where
                                INSTALLMENTSPLIT.PLEDGEID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID
                                and INSTALLMENTSPLIT.DESIGNATIONID = REVENUESPLIT_EXT.DESIGNATIONID
                                and FINANCIALTRANSACTION.DATE <= @ASOFDATE)
                        , 0) 
                        + coalesce(
                            (select
                                sum(INSTALLMENTSPLITWRITEOFF.TRANSACTIONAMOUNT) as AMOUNT
                            from dbo.INSTALLMENTSPLIT
                                inner join dbo.INSTALLMENTSPLITWRITEOFF on INSTALLMENTSPLITWRITEOFF.INSTALLMENTSPLITID = INSTALLMENTSPLIT.ID 
                                inner join dbo.WRITEOFF on INSTALLMENTSPLITWRITEOFF.WRITEOFFID = WRITEOFF.ID 
                            where 
                                INSTALLMENTSPLIT.PLEDGEID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID
                                and INSTALLMENTSPLIT.DESIGNATIONID = REVENUESPLIT_EXT.DESIGNATIONID
                                and WRITEOFF.DATE <= @ASOFDATE)
                        , 0)
                    )
                end as TRANSACTIONBALANCE
        from dbo.FINANCIALTRANSACTIONLINEITEM
            inner join dbo.REVENUESPLIT_EXT on REVENUESPLIT_EXT.ID = FINANCIALTRANSACTIONLINEITEM.ID
            inner join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTION.ID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID
        where FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = @PLEDGEID
            and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null
            and FINANCIALTRANSACTIONLINEITEM.TYPECODE = 0

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

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

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

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

        --     to get the converted balance.

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

    return
end