UFN_INSTALLMENT_GETREVALUEDBALANCEINCURRENCY_BULK

--This calculation method is also used in UFN_SPLIT_GETREVALUEDBALANCEINCURRENCY_BULK.  If there's

--    a bug here, it likely also appears there.  UFN_INSTALLMENTSPLIT_GETREVALUEDBALANCEINCURRENCY_BULK

--    uses a variation of this, so check there as well.


CREATE function dbo.UFN_INSTALLMENT_GETREVALUEDBALANCEINCURRENCY_BULK
(
    -- If you want to convert to...

    --    The base currency of each row:

    --        set @CURRENCYCODE = 0

    --        set @ORGANIZATIONCURRENCYID, @ASOFDATE, and @ORIGINCODE appropriately

    --    The organization currency:

    --        set @CURRENCYCODE = 1

    --        set @ORGANIZATIONCURRENCYID, @ASOFDATE, and @ORIGINCODE appropriately

    --    The transaction currency of each row:

    --        set @CURRENCYCODE = 2

    --        set @ORGANIZATIONCURRENCYID, @ASOFDATE and @ORIGINCODE appropriately

    --    An arbitrary currency:

    --        set @CURRENCYCODE = anything but 0 or 2

    --        set @CURRENCYID = the currency's ID

    --        set @DECIMALDIGITS and @ROUNDINGTYPECODE according to the currency

    --        set @ORGANIZATIONCURRENCYID, @ASOFDATE and @ORIGINCODE appropriately


    @CURRENCYID uniqueidentifier,
    @ORGANIZATIONCURRENCYID uniqueidentifier,
    @DECIMALDIGITS tinyint,
    @ROUNDINGTYPECODE tinyint,
    @ASOFDATE datetime,
    @ORIGINCODE tinyint,
    @CURRENCYCODE tinyint
)
    returns table
    as
    return
    (
    with REVENUEINSTALLMENT_CTE as( --Calculate transaction balance for each installment

        select
            INSTALLMENT.ID,
            INSTALLMENT.REVENUEID,
            INSTALLMENT.TRANSACTIONAMOUNT 
                - coalesce((
                    select sum(INSTALLMENTPAYMENT.AMOUNT)
                    from dbo.INSTALLMENTPAYMENT
                    where INSTALLMENTPAYMENT.INSTALLMENTID = INSTALLMENT.ID)
                , 0)
                - coalesce((
                    select sum(INSTALLMENTWRITEOFF.TRANSACTIONAMOUNT)
                    from dbo.INSTALLMENTWRITEOFF
                    where INSTALLMENTWRITEOFF.INSTALLMENTID = INSTALLMENT.ID)
                , 0)            
            as TRANSACTIONINSTALLMENTBALANCE,
            INSTALLMENT.SEQUENCE,
            INSTALLMENT.TS
        from dbo.INSTALLMENT
    ),
    ROLLUP_CTE as( --Calculate the rollup of the balances for each installment and those the come before it.

        select
            REVENUEINSTALLMENT_CTE.ID,
            REVENUEINSTALLMENT_CTE.REVENUEID,
            REVENUEINSTALLMENT_CTE.TRANSACTIONINSTALLMENTBALANCE,
            REVENUEINSTALLMENT_CTE.SEQUENCE,
            coalesce(SUM(PREV.TRANSACTIONINSTALLMENTBALANCE),0) - REVENUEINSTALLMENT_CTE.TRANSACTIONINSTALLMENTBALANCE PREVIOUSTRANSACTIONRUNNINGTOTAL,
            coalesce(SUM(PREV.TRANSACTIONINSTALLMENTBALANCE),0) TRANSACTIONRUNNINGTOTAL
        from REVENUEINSTALLMENT_CTE
            cross join REVENUEINSTALLMENT_CTE PREV
        where PREV.REVENUEID = REVENUEINSTALLMENT_CTE.REVENUEID
            and(
                PREV.SEQUENCE < REVENUEINSTALLMENT_CTE.SEQUENCE
                or(
                    PREV.SEQUENCE = REVENUEINSTALLMENT_CTE.SEQUENCE
                    and PREV.TS <= REVENUEINSTALLMENT_CTE.TS
                )
            )
        group by
            REVENUEINSTALLMENT_CTE.ID,
            REVENUEINSTALLMENT_CTE.REVENUEID,
            REVENUEINSTALLMENT_CTE.TRANSACTIONINSTALLMENTBALANCE,
            REVENUEINSTALLMENT_CTE.SEQUENCE
    ),
    CTE_WITHREVENUEBALANCES    as( --Retrieve pledge balances in the target currency

        select
            ROLLUP_CTE.ID,
            ROLLUP_CTE.REVENUEID,
            ROLLUP_CTE.TRANSACTIONINSTALLMENTBALANCE,
            ROLLUP_CTE.SEQUENCE,
            ROLLUP_CTE.TRANSACTIONRUNNINGTOTAL,
            ROLLUP_CTE.PREVIOUSTRANSACTIONRUNNINGTOTAL,
            REVENUE.TRANSACTIONBALANCE TRANSACTIONPLEDGEBALANCE,
            REVENUE.BALANCEINCURRENCY PLEDGEBALANCEINCURRENCY,
            REVENUE.INTERMEDIATEBALANCE INTERMEDIATEPLEDGEBALANCE,
            REVENUE.CURRENCYID
        from dbo.UFN_PLEDGE_GETREVALUEDBALANCEINCURRENCY_BULK(
                @CURRENCYID,
                @ORGANIZATIONCURRENCYID,
                @DECIMALDIGITS,
                @ROUNDINGTYPECODE,
                @ASOFDATE,
                @ORIGINCODE,
                @CURRENCYCODE
            ) REVENUE
            inner join ROLLUP_CTE on ROLLUP_CTE.REVENUEID = REVENUE.ID 
    )
    --Using pledge balances and rollup numbers, calculate the installment balances in the target currency.

    select 
        CTE_WITHREVENUEBALANCES.REVENUEID, 
        CTE_WITHREVENUEBALANCES.TRANSACTIONPLEDGEBALANCE,
        CTE_WITHREVENUEBALANCES.PLEDGEBALANCEINCURRENCY,
        CTE_WITHREVENUEBALANCES.ID,
        CTE_WITHREVENUEBALANCES.SEQUENCE,
        CTE_WITHREVENUEBALANCES.TRANSACTIONINSTALLMENTBALANCE TRANSACTIONBALANCE,
        case
            when CTE_WITHREVENUEBALANCES.TRANSACTIONPLEDGEBALANCE = 0 
                or CTE_WITHREVENUEBALANCES.PLEDGEBALANCEINCURRENCY = 0
                then convert(money,0)
            when CTE_WITHREVENUEBALANCES.TRANSACTIONPLEDGEBALANCE = CTE_WITHREVENUEBALANCES.PLEDGEBALANCEINCURRENCY
                then convert(money,0)
            when CTE_WITHREVENUEBALANCES.INTERMEDIATEPLEDGEBALANCE = 0
                then convert(money,0)
            else 
                dbo.UFN_CURRENCY_ROUND(
                    convert(decimal(20,8),CTE_WITHREVENUEBALANCES.TRANSACTIONRUNNINGTOTAL)
                        / CTE_WITHREVENUEBALANCES.TRANSACTIONPLEDGEBALANCE 
                        * CTE_WITHREVENUEBALANCES.INTERMEDIATEPLEDGEBALANCE
                    ,@DECIMALDIGITS,@ROUNDINGTYPECODE)
                - coalesce(dbo.UFN_CURRENCY_ROUND(
                    convert(decimal(20,8),CTE_WITHREVENUEBALANCES.PREVIOUSTRANSACTIONRUNNINGTOTAL)
                        / CTE_WITHREVENUEBALANCES.TRANSACTIONPLEDGEBALANCE 
                        * CTE_WITHREVENUEBALANCES.INTERMEDIATEPLEDGEBALANCE
                    ,@DECIMALDIGITS,@ROUNDINGTYPECODE)
                ,0)
        end INTERMEDIATEBALANCE,
        case
            when CTE_WITHREVENUEBALANCES.TRANSACTIONPLEDGEBALANCE = 0 
                or CTE_WITHREVENUEBALANCES.PLEDGEBALANCEINCURRENCY = 0
                then convert(money,0)
            when CTE_WITHREVENUEBALANCES.TRANSACTIONPLEDGEBALANCE = CTE_WITHREVENUEBALANCES.PLEDGEBALANCEINCURRENCY
                then CTE_WITHREVENUEBALANCES.TRANSACTIONINSTALLMENTBALANCE
            when CTE_WITHREVENUEBALANCES.INTERMEDIATEPLEDGEBALANCE = 0
                then 
                    dbo.UFN_CURRENCY_ROUND(
                        convert(decimal(20,8),CTE_WITHREVENUEBALANCES.TRANSACTIONRUNNINGTOTAL)
                            / CTE_WITHREVENUEBALANCES.TRANSACTIONPLEDGEBALANCE 
                            * CTE_WITHREVENUEBALANCES.PLEDGEBALANCEINCURRENCY
                        ,@DECIMALDIGITS,@ROUNDINGTYPECODE) 
                    - coalesce(dbo.UFN_CURRENCY_ROUND(
                        convert(decimal(20,8),CTE_WITHREVENUEBALANCES.PREVIOUSTRANSACTIONRUNNINGTOTAL)
                            / CTE_WITHREVENUEBALANCES.TRANSACTIONPLEDGEBALANCE 
                            * CTE_WITHREVENUEBALANCES.PLEDGEBALANCEINCURRENCY
                        ,@DECIMALDIGITS,@ROUNDINGTYPECODE)
                    ,0)
            else 
                dbo.UFN_CURRENCY_ROUND(
                    convert(decimal(20,8),
                            dbo.UFN_CURRENCY_ROUND(
                                convert(decimal(20,8),CTE_WITHREVENUEBALANCES.TRANSACTIONRUNNINGTOTAL)
                                    / CTE_WITHREVENUEBALANCES.TRANSACTIONPLEDGEBALANCE 
                                    * CTE_WITHREVENUEBALANCES.INTERMEDIATEPLEDGEBALANCE
                                ,@DECIMALDIGITS,@ROUNDINGTYPECODE) 
                        )
                        / CTE_WITHREVENUEBALANCES.INTERMEDIATEPLEDGEBALANCE 
                        * CTE_WITHREVENUEBALANCES.PLEDGEBALANCEINCURRENCY
                    ,@DECIMALDIGITS,@ROUNDINGTYPECODE) 
                - coalesce(dbo.UFN_CURRENCY_ROUND(
                    convert(decimal(20,8),
                            dbo.UFN_CURRENCY_ROUND(
                                convert(decimal(20,8),CTE_WITHREVENUEBALANCES.PREVIOUSTRANSACTIONRUNNINGTOTAL)
                                    / CTE_WITHREVENUEBALANCES.TRANSACTIONPLEDGEBALANCE 
                                    * CTE_WITHREVENUEBALANCES.INTERMEDIATEPLEDGEBALANCE
                                ,@DECIMALDIGITS,@ROUNDINGTYPECODE) 
                        )
                        / CTE_WITHREVENUEBALANCES.INTERMEDIATEPLEDGEBALANCE 
                        * CTE_WITHREVENUEBALANCES.PLEDGEBALANCEINCURRENCY
                    ,@DECIMALDIGITS,@ROUNDINGTYPECODE) 
                ,0)
        end BALANCEINCURRENCY,
        CTE_WITHREVENUEBALANCES.CURRENCYID
    from CTE_WITHREVENUEBALANCES
)