UFN_SPLIT_GETREVALUEDBALANCEINCURRENCY_BULK

--This calculation method is also used in UFN_INSTALLMENT_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_SPLIT_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 REVENUESPLIT_CTE as( --Calculate transaction balance for each split

        select
            FINANCIALTRANSACTIONLINEITEM.ID,
            REVENUESPLIT_EXT.DESIGNATIONID,
            FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID REVENUEID,
            FINANCIALTRANSACTIONLINEITEM.TRANSACTIONAMOUNT
                - coalesce((
                    select sum(INSTALLMENTSPLITPAYMENT.AMOUNT) as AMOUNT
                    from dbo.INSTALLMENTSPLITPAYMENT
                        inner join dbo.FINANCIALTRANSACTIONLINEITEM PAYMENTSPLIT on PAYMENTSPLIT.ID = INSTALLMENTSPLITPAYMENT.PAYMENTID
                        inner join dbo.FINANCIALTRANSACTION PAYMENT on PAYMENT.ID = PAYMENTSPLIT.FINANCIALTRANSACTIONID
                        inner join dbo.INSTALLMENTSPLIT on INSTALLMENTSPLIT.ID = INSTALLMENTSPLITPAYMENT.INSTALLMENTSPLITID
                    where PAYMENT.DATE <= @ASOFDATE 
                        and INSTALLMENTSPLIT.PLEDGEID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID
                        and INSTALLMENTSPLIT.DESIGNATIONID = REVENUESPLIT_EXT.DESIGNATIONID
                        and PAYMENTSPLIT.DELETEDON is null
                        and PAYMENT.DELETEDON is null)
                ,0)
                - coalesce((
                    select sum(INSTALLMENTSPLITWRITEOFF.TRANSACTIONAMOUNT) AMOUNT
                    from dbo.INSTALLMENTSPLITWRITEOFF
                        inner join dbo.WRITEOFF on INSTALLMENTSPLITWRITEOFF.WRITEOFFID = WRITEOFF.ID
                        inner join dbo.INSTALLMENTSPLIT on INSTALLMENTSPLIT.ID = INSTALLMENTSPLITWRITEOFF.INSTALLMENTSPLITID
                    where WRITEOFF.DATE <= @ASOFDATE 
                        and INSTALLMENTSPLIT.PLEDGEID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID
                        and INSTALLMENTSPLIT.DESIGNATIONID = REVENUESPLIT_EXT.DESIGNATIONID)
                ,0)
            as TRANSACTIONSPLITBALANCE,
            FINANCIALTRANSACTIONLINEITEM.SEQUENCE,
            FINANCIALTRANSACTIONLINEITEM.TS
        from dbo.FINANCIALTRANSACTIONLINEITEM
            inner join dbo.REVENUESPLIT_EXT on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID
        where FINANCIALTRANSACTIONLINEITEM.DELETEDON is null
            and FINANCIALTRANSACTIONLINEITEM.TYPECODE != 1
    ),
    ROLLUP_CTE as( --Calculate the rollup of the balances for each split and those the come before it.

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

        select
            ROLLUP_CTE.ID,
            ROLLUP_CTE.DESIGNATIONID,
            ROLLUP_CTE.REVENUEID,
            ROLLUP_CTE.TRANSACTIONSPLITBALANCE,
            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 split balances in the target currency.

    select 
        CTE_WITHREVENUEBALANCES.REVENUEID, 
        CTE_WITHREVENUEBALANCES.TRANSACTIONPLEDGEBALANCE,
        CTE_WITHREVENUEBALANCES.PLEDGEBALANCEINCURRENCY,
        CTE_WITHREVENUEBALANCES.ID,
        CTE_WITHREVENUEBALANCES.SEQUENCE,
        CTE_WITHREVENUEBALANCES.TRANSACTIONSPLITBALANCE 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.TRANSACTIONSPLITBALANCE
            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
)