USP_DATALIST_REVENUECOMMITMENTREVALUATION

                CREATE procedure dbo.USP_DATALIST_REVENUECOMMITMENTREVALUATION (@ID uniqueidentifier)
                as 
                    set nocount on;

                    declare @ORIGINCODE tinyint;
                    select @ORIGINCODE = ORGANIZATIONAMOUNTORIGINCODE 
                    from dbo.MULTICURRENCYCONFIGURATION;

                    with CTE_NEWPREVIOUSPAIRINGS
                    as(
                        select 
                            NEWREVALUATION.ID,
                            NEWREVALUATION.DATE,
                            NEWREVALUATION.SEQUENCE,
                            NEWREVALUATION.BASEEXCHANGERATEID NEWBASEEXCHANGERATEID,
                            NEWREVALUATION.ORGANIZATIONEXCHANGERATEID NEWORGANIZATIONEXCHANGERATEID,
                            coalesce(PREVIOUSBASEREVALUATION.BASEEXCHANGERATEID,FINANCIALTRANSACTION.BASEEXCHANGERATEID) PREVIOUSBASEEXCHANGERATEID,
                            coalesce(PREVIOUSORGANIZATIONREVALUATION.ORGANIZATIONEXCHANGERATEID,FINANCIALTRANSACTION.ORGEXCHANGERATEID) PREVIOUSORGANIZATIONEXCHANGERATEID,
                            FINANCIALTRANSACTION.TRANSACTIONCURRENCYID,
                            isnull(REVENUE_EXT.NONPOSTABLEBASECURRENCYID, CURRENCYSET.BASECURRENCYID) BASECURRENCYID,
                            case 
                                when FINANCIALTRANSACTION.TYPECODE in (1,4,6,15)
                                    then dbo.UFN_PLEDGE_GETBALANCEASOF(@ID,NEWREVALUATION.DATE)
                                else FINANCIALTRANSACTION.TRANSACTIONAMOUNT
                            end AMOUNTTOREVALUE
                        from dbo.FINANCIALTRANSACTION
                            inner join dbo.REVENUE_EXT on FINANCIALTRANSACTION.ID = REVENUE_EXT.ID
                            inner join dbo.PDACCOUNTSYSTEM on FINANCIALTRANSACTION.PDACCOUNTSYSTEMID = PDACCOUNTSYSTEM.ID
                            inner join dbo.CURRENCYSET on PDACCOUNTSYSTEM.CURRENCYSETID = CURRENCYSET.ID
                            cross apply dbo.UFN_REVENUE_GETREVALUATIONS(@ID) NEWREVALUATION
                            outer apply( 
                                select top 1 BASEEXCHANGERATEID
                                from dbo.UFN_REVENUE_GETREVALUATIONS(@ID) PREVIOUSREVALUATION
                                where (
                                        (NEWREVALUATION.DATE = PREVIOUSREVALUATION.DATE
                                            and NEWREVALUATION.SEQUENCE > PREVIOUSREVALUATION.SEQUENCE
                                        )
                                        or NEWREVALUATION.DATE > PREVIOUSREVALUATION.DATE 
                                    )
                                    and BASEEXCHANGERATEID is not null
                                order by PREVIOUSREVALUATION.DATE desc, PREVIOUSREVALUATION.SEQUENCE desc
                            ) PREVIOUSBASEREVALUATION
                            outer apply( 
                                select top 1 ORGANIZATIONEXCHANGERATEID
                                from dbo.UFN_REVENUE_GETREVALUATIONS(@ID) PREVIOUSREVALUATION
                                where (
                                        (NEWREVALUATION.DATE = PREVIOUSREVALUATION.DATE
                                            and NEWREVALUATION.SEQUENCE > PREVIOUSREVALUATION.SEQUENCE
                                        )
                                        or NEWREVALUATION.DATE > PREVIOUSREVALUATION.DATE 
                                    )
                                    and ORGANIZATIONEXCHANGERATEID is not null
                                order by PREVIOUSREVALUATION.DATE desc, PREVIOUSREVALUATION.SEQUENCE desc
                            ) PREVIOUSORGANIZATIONREVALUATION
                        where FINANCIALTRANSACTION.ID = @ID
                            and FINANCIALTRANSACTION.DELETEDON is null
                    )
                    select 
                        REVALUATIONS.ID ID,
                        REVALUATIONS.DATE DATE,
                        REVALUATIONS.SEQUENCE,
                        'Base' TYPE,
                        PREVIOUSBASERATE.RATE PREVIOUSBASERATE,
                        dbo.UFN_CURRENCY_CONVERT(REVALUATIONS.AMOUNTTOREVALUE,REVALUATIONS.PREVIOUSBASEEXCHANGERATEID) PREVIOUSBALANCE,
                        NEWBASERATE.RATE NEWBASERATE,
                        dbo.UFN_CURRENCY_CONVERT(REVALUATIONS.AMOUNTTOREVALUE,REVALUATIONS.NEWBASEEXCHANGERATEID) NEWBALANCE,
                        REVALUATIONS.BASECURRENCYID CURRENCYID
                    from CTE_NEWPREVIOUSPAIRINGS REVALUATIONS
                        left join dbo.CURRENCYEXCHANGERATE NEWBASERATE on NEWBASERATE.ID = REVALUATIONS.NEWBASEEXCHANGERATEID
                        left join dbo.CURRENCYEXCHANGERATE PREVIOUSBASERATE on PREVIOUSBASERATE.ID = REVALUATIONS.PREVIOUSBASEEXCHANGERATEID
                    where REVALUATIONS.TRANSACTIONCURRENCYID <> REVALUATIONS.BASECURRENCYID
                        and REVALUATIONS.NEWBASEEXCHANGERATEID is not null

                    union all

                    select 
                        REVALUATIONS.ID ID,
                        REVALUATIONS.DATE DATE,
                        REVALUATIONS.SEQUENCE,
                        'Organization' TYPE,
                        PREVIOUSORGANIZATIONRATE.RATE PREVIOUSORGANIZATIONRATE,
                        case @ORIGINCODE
                            when 0
                                then 
                                    case
                                        when REVALUATIONS.BASECURRENCYID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY()
                                            then dbo.UFN_CURRENCY_CONVERT(REVALUATIONS.AMOUNTTOREVALUE,REVALUATIONS.PREVIOUSBASEEXCHANGERATEID)
                                        else
                                            case
                                                when REVALUATIONS.TRANSACTIONCURRENCYID = REVALUATIONS.BASECURRENCYID
                                                    then dbo.UFN_CURRENCY_CONVERT(REVALUATIONS.AMOUNTTOREVALUE,REVALUATIONS.PREVIOUSORGANIZATIONEXCHANGERATEID)
                                                else dbo.UFN_CURRENCY_CONVERT(dbo.UFN_CURRENCY_CONVERT(REVALUATIONS.AMOUNTTOREVALUE,REVALUATIONS.PREVIOUSBASEEXCHANGERATEID),REVALUATIONS.PREVIOUSORGANIZATIONEXCHANGERATEID)
                                            end
                                    end
                            when 1
                                then dbo.UFN_CURRENCY_CONVERT(REVALUATIONS.AMOUNTTOREVALUE,REVALUATIONS.PREVIOUSORGANIZATIONEXCHANGERATEID)
                        end PREVIOUSBALANCE,
                        NEWORGANIZATIONRATE.RATE NEWORGANIZATIONRATE,                        
                        case @ORIGINCODE
                            when 0
                                then 
                                    case
                                        when REVALUATIONS.BASECURRENCYID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY()
                                            then dbo.UFN_CURRENCY_CONVERT(REVALUATIONS.AMOUNTTOREVALUE,REVALUATIONS.NEWBASEEXCHANGERATEID)
                                        else
                                            case
                                                when REVALUATIONS.TRANSACTIONCURRENCYID = REVALUATIONS.BASECURRENCYID
                                                    then dbo.UFN_CURRENCY_CONVERT(REVALUATIONS.AMOUNTTOREVALUE,REVALUATIONS.NEWORGANIZATIONEXCHANGERATEID)
                                                else dbo.UFN_CURRENCY_CONVERT(dbo.UFN_CURRENCY_CONVERT(REVALUATIONS.AMOUNTTOREVALUE,coalesce(REVALUATIONS.NEWBASEEXCHANGERATEID,REVALUATIONS.PREVIOUSBASEEXCHANGERATEID)),coalesce(REVALUATIONS.NEWORGANIZATIONEXCHANGERATEID,REVALUATIONS.PREVIOUSORGANIZATIONEXCHANGERATEID))
                                            end
                                    end
                            when 1
                                then dbo.UFN_CURRENCY_CONVERT(REVALUATIONS.AMOUNTTOREVALUE,REVALUATIONS.NEWORGANIZATIONEXCHANGERATEID)
                        end NEWBALANCE,
                        dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY() CURRENCYID
                    from CTE_NEWPREVIOUSPAIRINGS REVALUATIONS
                        left join dbo.CURRENCYEXCHANGERATE NEWORGANIZATIONRATE on NEWORGANIZATIONRATE.ID = REVALUATIONS.NEWORGANIZATIONEXCHANGERATEID
                        left join dbo.CURRENCYEXCHANGERATE PREVIOUSORGANIZATIONRATE on PREVIOUSORGANIZATIONRATE.ID = REVALUATIONS.PREVIOUSORGANIZATIONEXCHANGERATEID
                    where (@ORIGINCODE = 0
                            and not(
                                REVALUATIONS.TRANSACTIONCURRENCYID = REVALUATIONS.BASECURRENCYID
                                    and REVALUATIONS.BASECURRENCYID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY()
                            )
                            and not(
                                REVALUATIONS.BASECURRENCYID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY()
                                    and REVALUATIONS.NEWBASEEXCHANGERATEID is null
                            )
                            and not(
                                REVALUATIONS.TRANSACTIONCURRENCYID = REVALUATIONS.BASECURRENCYID
                                    and REVALUATIONS.NEWORGANIZATIONEXCHANGERATEID is null
                            )
                            and not( 
                                REVALUATIONS.NEWORGANIZATIONEXCHANGERATEID is null
                                    and    REVALUATIONS.NEWBASEEXCHANGERATEID is null
                            )
                        )
                        or (@ORIGINCODE = 1
                            and REVALUATIONS.TRANSACTIONCURRENCYID <> dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY()
                            and REVALUATIONS.NEWORGANIZATIONEXCHANGERATEID is not null
                        )                        
                    order by REVALUATIONS.DATE desc, REVALUATIONS.SEQUENCE desc, TYPE;