UFN_PLEDGE_GETREVALUEDSPLITBALANCESINCURRENCY

--The method used here to calc pledge balance is also in UFN_PLEDGE_GETREVALUEDBALANCEINCURRENCY_BULK. 

--    Changes here maybe need to be made there as well.


CREATE function dbo.UFN_PLEDGE_GETREVALUEDSPLITBALANCESINCURRENCY(
    -- 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


    @REVENUEID uniqueidentifier,
    @CURRENCYID uniqueidentifier,
    @ORGANIZATIONCURRENCYID uniqueidentifier,
    @DECIMALDIGITS tinyint,
    @ROUNDINGTYPECODE tinyint,
    @ASOFDATE datetime,
    @ORIGINCODE tinyint,
    @CURRENCYCODE tinyint
)
returns table
as return(

    with REVENUECTE
    as
    (
        select
            FT.ID,
            FT.TRANSACTIONAMOUNT

                - coalesce(
                (
                select sum(INSTALLMENTSPLITPAYMENT.AMOUNT) as AMOUNT
                from dbo.INSTALLMENTSPLITPAYMENT
                inner join dbo.FINANCIALTRANSACTIONLINEITEM as PAYMENTLINEITEM on PAYMENTLINEITEM.ID = INSTALLMENTSPLITPAYMENT.PAYMENTID
                inner join dbo.FINANCIALTRANSACTION PAYMENT on PAYMENT.ID = PAYMENTLINEITEM.FINANCIALTRANSACTIONID
                where 
                    PAYMENT.DATE <= @ASOFDATE and
                    PLEDGEID = FT.ID
                    and PAYMENT.TYPECODE in (0,1,2,3,4,5,6,7,8,9,15)
                    and PAYMENT.DELETEDON is NULL
                    ),0)

                - coalesce((

                select sum(INSTALLMENTSPLITWRITEOFF.TRANSACTIONAMOUNT) AMOUNT
                from dbo.INSTALLMENTSPLITWRITEOFF
                inner join dbo.FINANCIALTRANSACTION as WRITEOFF on INSTALLMENTSPLITWRITEOFF.WRITEOFFID = WRITEOFF.ID
                where
                    WRITEOFF.DATE <= @ASOFDATE and
                    WRITEOFF.PARENTID = FT.ID
                    and WRITEOFF.TYPECODE in (0,1,2,3,4,5,6,7,8,9,15)
                    and WRITEOFF.DELETEDON is NULL

                    ),0)

            as BALANCEINCURRENCY,
            FT.TRANSACTIONCURRENCYID,
            FTBC.BASECURRENCYID,
            FT.[DATE],
            FT.TYPECODE,
            BASEEXCHANGERATE.RATE as ORIGINALBASERATE,
            ORGEXCHANGERATE.RATE as ORIGINALORGANIZATIONRATE,
            case @CURRENCYCODE
                when 0 then FTBC.BASECURRENCYID
                when 1 then @ORGANIZATIONCURRENCYID
                when 2 then FT.TRANSACTIONCURRENCYID
                else @CURRENCYID
            end as CURRENCYID            
        from dbo.FINANCIALTRANSACTION as FT with (nolock)
          inner join dbo.V_BASECURRENCYFORFINANCIALTRANSACTION_I as FTBC on FT.ID = FTBC.FINANCIALTRANSACTIONID
            left join dbo.CURRENCYEXCHANGERATE BASEEXCHANGERATE on BASEEXCHANGERATE.ID = FT.BASEEXCHANGERATEID
            left join dbo.CURRENCYEXCHANGERATE ORGEXCHANGERATE on ORGEXCHANGERATE.ID = FT.ORGEXCHANGERATEID
        where FT.ID = @REVENUEID
        and FT.TYPECODE in (0,1,2,3,4,5,6,7,8,9,15)
        and FT.DELETEDON is null
    ),
    CTE_PLEDGEBALANCE as(

        select            -- Transaction currency

            REVENUECTE.BALANCEINCURRENCY,
            REVENUECTE.BALANCEINCURRENCY TRANSACTIONBALANCE,
            REVENUECTE.CURRENCYID
        from REVENUECTE
        where (REVENUECTE.CURRENCYID is null)
            or @CURRENCYCODE = 2    --We want transaction balance

            or (    --We want base balance and transaction = base currency

                @CURRENCYCODE = 0
                and REVENUECTE.TRANSACTIONCURRENCYID = REVENUECTE.BASECURRENCYID
            )
            or (    --We want organization balance.

                @CURRENCYCODE = 1
                and (
                    (    --The origin is T->B->O, and transaction = base = organization currency

                        @ORIGINCODE = 0
                        and REVENUECTE.TRANSACTIONCURRENCYID = REVENUECTE.BASECURRENCYID
                        and REVENUECTE.BASECURRENCYID = @ORGANIZATIONCURRENCYID
                    )
                    or(    --The origin is T->O, and transaction = organization currency

                        @ORIGINCODE = 1
                        and REVENUECTE.TRANSACTIONCURRENCYID = @ORGANIZATIONCURRENCYID                            
                    )
                )
            )
            or (    --We want an arbitrary balance and transaction = arbitrary currency

                (@CURRENCYCODE is null or not @CURRENCYCODE in (0,1,2))
                and REVENUECTE.TRANSACTIONCURRENCYID = REVENUECTE.CURRENCYID
            )
            or REVENUECTE.BALANCEINCURRENCY is null
            or REVENUECTE.BALANCEINCURRENCY = 0


        union all        -- Base or org currency


        select 
            case
                when REVENUECTE.CURRENCYID = REVENUECTE.BASECURRENCYID
                    then dbo.UFN_CURRENCY_ROUND(dbo.UFN_CURRENCY_APPLYRATE(REVENUECTE.BALANCEINCURRENCY, coalesce(REVALUATION.BASERATE,REVENUECTE.ORIGINALBASERATE)), BASECURRENCY.DECIMALDIGITS, BASECURRENCY.ROUNDINGTYPECODE)
                when REVENUECTE.CURRENCYID = @ORGANIZATIONCURRENCYID
                    then                                                        
                        case @ORIGINCODE
                            when 0
                                then 
                                    case
                                        when REVENUECTE.TRANSACTIONCURRENCYID = REVENUECTE.BASECURRENCYID
                                            then dbo.UFN_CURRENCY_ROUND(dbo.UFN_CURRENCY_APPLYRATE(REVENUECTE.BALANCEINCURRENCY, coalesce(REVALUATION.ORGANIZATIONRATE,REVENUECTE.ORIGINALORGANIZATIONRATE)), @DECIMALDIGITS, @ROUNDINGTYPECODE)
                                        else dbo.UFN_CURRENCY_ROUND(dbo.UFN_CURRENCY_APPLYRATE(dbo.UFN_CURRENCY_ROUND(dbo.UFN_CURRENCY_APPLYRATE(REVENUECTE.BALANCEINCURRENCY, coalesce(REVALUATION.BASERATE, REVENUECTE.ORIGINALBASERATE)), BASECURRENCY.DECIMALDIGITS, BASECURRENCY.ROUNDINGTYPECODE), coalesce(REVALUATION.ORGANIZATIONRATE, REVENUECTE.ORIGINALORGANIZATIONRATE)), @DECIMALDIGITS, @ROUNDINGTYPECODE)
                                    end
                            when 1
                                then dbo.UFN_CURRENCY_ROUND(dbo.UFN_CURRENCY_APPLYRATE(REVENUECTE.BALANCEINCURRENCY, coalesce(REVALUATION.ORGANIZATIONRATE,REVENUECTE.ORIGINALORGANIZATIONRATE)), @DECIMALDIGITS, @ROUNDINGTYPECODE)
                        end
                else 0
            end [BALANCEINCURRENCY],
            REVENUECTE.BALANCEINCURRENCY TRANSACTIONBALANCE,
            REVENUECTE.CURRENCYID
        from REVENUECTE
            outer apply(
                select top 1
                    REVENUECOMMITMENTREVALUATION.ID,
                    COMMITMENTREVALUATION.BASEEXCHANGERATEID,
                    COMMITMENTREVALUATION.ORGANIZATIONEXCHANGERATEID,
                    BASERATE.RATE BASERATE,
                    ORGANIZATIONRATE.RATE ORGANIZATIONRATE
                from dbo.REVENUECOMMITMENTREVALUATION
                    inner join dbo.COMMITMENTREVALUATION on REVENUECOMMITMENTREVALUATION.COMMITMENTREVALUATIONID = COMMITMENTREVALUATION.ID
                    left join dbo.CURRENCYEXCHANGERATE BASERATE on BASERATE.ID = COMMITMENTREVALUATION.BASEEXCHANGERATEID
                    left join dbo.CURRENCYEXCHANGERATE ORGANIZATIONRATE on ORGANIZATIONRATE.ID = COMMITMENTREVALUATION.ORGANIZATIONEXCHANGERATEID
                where REVENUECOMMITMENTREVALUATION.REVENUEID = REVENUECTE.ID
                    and COMMITMENTREVALUATION.DATE <= @ASOFDATE
                order by
                    COMMITMENTREVALUATION.DATE desc, 
                    COMMITMENTREVALUATION.SEQUENCE desc 
            ) REVALUATION
            left join dbo.CURRENCY BASECURRENCY on BASECURRENCY.ID = REVENUECTE.BASECURRENCYID
        where (REVENUECTE.CURRENCYID is not null)
            and
                REVENUECTE.TRANSACTIONCURRENCYID <> REVENUECTE.CURRENCYID
            and (
                (    --We want base balance and base <> transaction currency

                    (
                        @CURRENCYCODE = 0
                        or (
                            (@CURRENCYCODE is null or @CURRENCYCODE not in (0,1,2))
                            and @CURRENCYID = REVENUECTE.BASECURRENCYID
                        )
                    )
                    and REVENUECTE.TRANSACTIONCURRENCYID <> REVENUECTE.BASECURRENCYID
                )
                or (    --We want organization balance.

                    (
                        @CURRENCYCODE = 1
                        or (
                            (@CURRENCYCODE is null or @CURRENCYCODE not in (0,1,2))
                            and @CURRENCYID = @ORGANIZATIONCURRENCYID
                        )
                    )
                    and (
                        (    --The origin is T->B->O, but transaction <> base or base <> organization currency

                            @ORIGINCODE = 0
                            and (
                                REVENUECTE.TRANSACTIONCURRENCYID <> REVENUECTE.BASECURRENCYID
                                or REVENUECTE.BASECURRENCYID <> @ORGANIZATIONCURRENCYID
                            )
                        )
                        or(    --The origin is T->O, and transaction <> organization currency

                            @ORIGINCODE = 1
                            and REVENUECTE.TRANSACTIONCURRENCYID <> @ORGANIZATIONCURRENCYID                            
                        )
                    )
                )
            )
            and REVENUECTE.BALANCEINCURRENCY is not null
            and REVENUECTE.BALANCEINCURRENCY <> 0

        union all        -- Arbitrary currency


        select 
            case
                when LATESTEXCHANGERATE.RATE is not null
                    then dbo.UFN_CURRENCY_ROUND(dbo.UFN_CURRENCY_APPLYRATE(REVENUECTE.BALANCEINCURRENCY, LATESTEXCHANGERATE.RATE), @DECIMALDIGITS, @ROUNDINGTYPECODE)
                when LATESTINVERSEEXCHANGERATE.RATE is not null
                    then dbo.UFN_CURRENCY_ROUND(dbo.UFN_CURRENCY_APPLYRATE(REVENUECTE.BALANCEINCURRENCY, cast((1 / LATESTINVERSEEXCHANGERATE.RATE) as decimal(20,8))), @DECIMALDIGITS, @ROUNDINGTYPECODE)
                else 0
            end [BALANCEINCURRENCY],
            REVENUECTE.BALANCEINCURRENCY TRANSACTIONBALANCE,
            REVENUECTE.CURRENCYID
        from REVENUECTE
            outer apply
            (
                select
                    RATE
                from
                    dbo.CURRENCYEXCHANGERATE
                where
                    CURRENCYEXCHANGERATE.FROMCURRENCYID = REVENUECTE.TRANSACTIONCURRENCYID 
                    and CURRENCYEXCHANGERATE.TOCURRENCYID = REVENUECTE.CURRENCYID
                    and CURRENCYEXCHANGERATE.TYPECODE in (0,1)
                    and CURRENCYEXCHANGERATE.ISLATESTRATEFORASOFDATE = 1
                    and CURRENCYEXCHANGERATE.ASOFDATESDTZ <= dateadd(ms, 86399996, REVENUECTE.DATE)
                    and CURRENCYEXCHANGERATE.NEXTRATEASOFDATEORMAXSDTZ >= dateadd(ms, 86399996, REVENUECTE.DATE)
            ) LATESTEXCHANGERATE
            outer apply
            (
                select
                    RATE
                from
                    dbo.CURRENCYEXCHANGERATE
                where
                    CURRENCYEXCHANGERATE.FROMCURRENCYID = REVENUECTE.CURRENCYID
                    and CURRENCYEXCHANGERATE.TOCURRENCYID = REVENUECTE.TRANSACTIONCURRENCYID
                    and CURRENCYEXCHANGERATE.TYPECODE in (0,1)
                    and CURRENCYEXCHANGERATE.ISLATESTRATEFORASOFDATE = 1
                    and CURRENCYEXCHANGERATE.ASOFDATESDTZ <= dateadd(ms, 86399996, REVENUECTE.DATE)
                    and CURRENCYEXCHANGERATE.NEXTRATEASOFDATEORMAXSDTZ >= dateadd(ms, 86399996, REVENUECTE.DATE) 
            ) LATESTINVERSEEXCHANGERATE
        where (REVENUECTE.CURRENCYID is not null)
            and (    --We want an arbitrary balance and arbitrary <> transaction currency

                not @CURRENCYCODE in (0,1,2)
                and REVENUECTE.TRANSACTIONCURRENCYID <> REVENUECTE.CURRENCYID                    
                and REVENUECTE.BASECURRENCYID <> REVENUECTE.CURRENCYID
                and @ORGANIZATIONCURRENCYID <> REVENUECTE.CURRENCYID
            )
            and REVENUECTE.BALANCEINCURRENCY is not null
            and REVENUECTE.BALANCEINCURRENCY <> 0
    )

    select
        SPLITBALANCES.ID,
        SPLITBALANCES.TOAMOUNT BALANCE
    from CTE_PLEDGEBALANCE
        cross apply dbo.UFN_PLEDGE_CONVERTSPLITBALANCESBYPROPORTION(
            @REVENUEID,
            @ASOFDATE,
            CTE_PLEDGEBALANCE.TRANSACTIONBALANCE,
            CTE_PLEDGEBALANCE.BALANCEINCURRENCY,
            @DECIMALDIGITS
        ) SPLITBALANCES
)