UFN_PLEDGE_CONVERTSPLITBALANCESBYPROPORTION

--The method used here to calculate initial balance of the pledge splits is also used in 

--UFN_PLEDGE_GETREVALUEDSPLITBALANCEINCURRENCY_BULK.  Changes made here may need to be made there.


CREATE function dbo.UFN_PLEDGE_CONVERTSPLITBALANCESBYPROPORTION(
    @REVENUEID uniqueidentifier,
    @ASOFDATE datetime,
    @PLEDGEBALANCETRANSACTIONCURRENCY money,
    @PLEDGEBALANCETARGETCURRENCY money,
    @DECIMALDIGITSTARGETCURRENCY int
) 
returns table
as 
return(    

    with CTE_ITEMLIST as ( --Generate row numbers and calculate balances for splits on the given revenue as of the given date.        

        select
            row_number() over (order by FTLI.ID) ROW,
            FTLI.ID as ID,
            FTLI.TRANSACTIONAMOUNT  
            - coalesce(PLEDGEINSTALLMENTSPLITPAYMENT.AMOUNT,0)  
            - coalesce(PLEDGEINSTALLMENTSPLITWRITEOFF.AMOUNT,0)  
            as AMOUNT   
        from dbo.FINANCIALTRANSACTIONLINEITEM as FTLI with (nolock)
        inner join dbo.REVENUESPLIT_EXT as RSEXT on FTLI.ID = RSEXT.ID
       left join(  
            select
                PLEDGESPLIT.ID as PLEDGESPLITID, 
                sum(INSTALLMENTSPLITPAYMENT.AMOUNT) AMOUNT  
            from dbo.INSTALLMENTSPLITPAYMENT  
            inner join dbo.FINANCIALTRANSACTIONLINEITEM as FTLI on FTLI.ID = INSTALLMENTSPLITPAYMENT.PAYMENTID  
            inner join dbo.FINANCIALTRANSACTION as FT on FT.ID = FTLI.FINANCIALTRANSACTIONID
            inner join dbo.INSTALLMENTSPLIT on INSTALLMENTSPLITPAYMENT.INSTALLMENTSPLITID = INSTALLMENTSPLIT.ID
            inner join dbo.FINANCIALTRANSACTIONLINEITEM PLEDGESPLIT on INSTALLMENTSPLIT.REVENUESPLITID = PLEDGESPLIT.ID
            inner join dbo.REVENUESPLIT_EXT on PLEDGESPLIT.ID = REVENUESPLIT_EXT.ID
            where FT.[DATE] <= @ASOFDATE  
            and FT.TYPECODE in (0,1,2,3,4,5,6,7,8,9,15)
            and FT.DELETEDON is NULL
            and FTLI.DELETEDON is null
            and FTLI.TYPECODE <> 1
            and PLEDGESPLIT.DELETEDON is null
            and PLEDGESPLIT.TYPECODE <> 1
            group by PLEDGESPLIT.ID 
       ) PLEDGEINSTALLMENTSPLITPAYMENT on FTLI.ID = PLEDGEINSTALLMENTSPLITPAYMENT.PLEDGESPLITID  
       left join (  
            select 
                FTWRITEOFF.PARENTID as PLEDGEID,
                INSTALLMENTSPLIT.REVENUESPLITID,
                sum(INSTALLMENTSPLITWRITEOFF.TRANSACTIONAMOUNT) AMOUNT
            from dbo.INSTALLMENTSPLITWRITEOFF  
            inner join dbo.FINANCIALTRANSACTION as FTWRITEOFF on INSTALLMENTSPLITWRITEOFF.WRITEOFFID = FTWRITEOFF.ID
            inner join dbo.INSTALLMENTSPLIT on INSTALLMENTSPLITWRITEOFF.INSTALLMENTSPLITID = INSTALLMENTSPLIT.ID
            where FTWRITEOFF.[DATE] <= @ASOFDATE   and FTWRITEOFF.TYPECODE = 20 and FTWRITEOFF.DELETEDON is NULL
            group by FTWRITEOFF.PARENTID, INSTALLMENTSPLIT.REVENUESPLITID
       ) PLEDGEINSTALLMENTSPLITWRITEOFF on PLEDGEINSTALLMENTSPLITWRITEOFF.PLEDGEID = FTLI.FINANCIALTRANSACTIONID
       and PLEDGEINSTALLMENTSPLITWRITEOFF.REVENUESPLITID = RSEXT.ID
      where FTLI.FINANCIALTRANSACTIONID = @REVENUEID
      and FTLI.TYPECODE = 0 and FTLI.DELETEDON is NULL 
    ),
    CTE_ITEMTORUNNINGTOTAL as ( --Calculate the running total for the target currency amount.

        select
            ID,
            ROW,
            AMOUNT,
            case  --If the full amount is the same in source and target currency, no math needs to be done

                when @PLEDGEBALANCETRANSACTIONCURRENCY <> @PLEDGEBALANCETARGETCURRENCY
                    --If full amounts are different, use a proportional running total based on the source full amount.

                    then dbo.UFN_CURRENCY_CONVERTBYPROPORTION(
                            (
                                select sum(ALLPREVIOUSITEMAMOUNT.AMOUNT) 
                                from CTE_ITEMLIST ALLPREVIOUSITEMAMOUNT 
                                where ALLPREVIOUSITEMAMOUNT.ROW <= ITEMLIST.ROW
                            ),
                            @PLEDGEBALANCETRANSACTIONCURRENCY,
                            @PLEDGEBALANCETARGETCURRENCY,
                            @DECIMALDIGITSTARGETCURRENCY
                        )
                else --Otherwise, just pass the source currency amount to be pulled out later.

                    AMOUNT
            end TOAMOUNTORRUNNINGTOTAL
        from CTE_ITEMLIST as ITEMLIST
    )    
    select
        ID,
        AMOUNT,
        case
            when @PLEDGEBALANCETRANSACTIONCURRENCY <> @PLEDGEBALANCETARGETCURRENCY
                --If full amounts are different, we know a proportional running total was created above, so 

                --    do subtraction here.

                 then ITEMTORUNNINGTOTAL.TOAMOUNTORRUNNINGTOTAL 
                    - coalesce(
                        (
                            select PREVIOUSCONVERTEDRUNNINGTOTAL.TOAMOUNTORRUNNINGTOTAL
                            from CTE_ITEMTORUNNINGTOTAL as PREVIOUSCONVERTEDRUNNINGTOTAL
                            where PREVIOUSCONVERTEDRUNNINGTOTAL.ROW = ITEMTORUNNINGTOTAL.ROW - 1
                        )
                        ,0
                    )
             else  --Otherwise, we know the "running total" is really just the source currency amount, so no math needed.

                ITEMTORUNNINGTOTAL.TOAMOUNTORRUNNINGTOTAL
        end TOAMOUNT,
        TOAMOUNTORRUNNINGTOTAL
    from CTE_ITEMTORUNNINGTOTAL as ITEMTORUNNINGTOTAL        
)