USP_DATALIST_MEMBERSHIPPROJECTEDREVENUEREPORT

                CREATE procedure dbo.USP_DATALIST_MEMBERSHIPPROJECTEDREVENUEREPORT
                    (
                    @CURRENTAPPUSERID uniqueidentifier = null,
                    @YEARENDING datetime = null,
                    @YEAROPTION tinyint = 0,
                    @MEMBERSHIPPROGRAMID uniqueidentifier = null,
                    @CURRENCYCODE tinyint
                    )
                as
                set nocount on;

                declare @STARTDATE datetime;
                declare @ENDDATE datetime;

                declare @SELECTEDCURRENCYID uniqueidentifier;
                if coalesce(@CURRENCYCODE, 1) = 1
                    set @SELECTEDCURRENCYID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();
                else if @CURRENCYCODE = 3
                    set @SELECTEDCURRENCYID = dbo.UFN_APPUSER_GETBASECURRENCY(@CURRENTAPPUSERID);

                declare @DECIMALDIGITS tinyint;
                declare @ROUNDINGTYPECODE tinyint;

                select
                    @DECIMALDIGITS = CURRENCYPROPERTIES.DECIMALDIGITS,
                    @ROUNDINGTYPECODE = CURRENCYPROPERTIES.ROUNDINGTYPECODE
                from
                    dbo.UFN_CURRENCY_GETPROPERTIES(@SELECTEDCURRENCYID) CURRENCYPROPERTIES

                declare @ORGANIZATIONCURRENCYID uniqueidentifier;
                set @ORGANIZATIONCURRENCYID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();

                if @YEAROPTION = 0
                    begin
                        select @ENDDATE = dbo.UFN_DATE_LASTCALENDARYEAR_LASTDAY(getdate(), 1),
                            @STARTDATE = dbo.UFN_DATE_LASTCALENDARYEAR_FIRSTDAY(getdate(), 0);
                    end

                if @YEAROPTION = 1
                    begin 
                        select @ENDDATE = dateadd(yyyy,-1,dbo.UFN_DATE_THISFISCALYEAR_LASTDAY(getdate(), 1)),
                            @STARTDATE = dateadd(yyyy,-1,dbo.UFN_DATE_THISFISCALYEAR_FIRSTDAY(getdate(), 0));
                    end

                if @YEAROPTION = 2
                    begin 
                        select @STARTDATE = dateadd(dd,1,DATEADD(YYYY,-1, @YEARENDING)),
                            @ENDDATE = dbo.UFN_DATE_GETLATESTTIME(@YEARENDING);

                        if MONTH(@STARTDATE) = MONTH(@ENDDATE) 
                            begin
                            select @STARTDATE = dbo.UFN_DATE_NEXTMONTH_FIRSTDAY(@STARTDATE,0);
                            end
                    end

                declare @CALCYEAR int;
                set @CALCYEAR = YEAR(@ENDDATE);

                --GET DATA YEAR

                declare @AMOUNTS table
                    (TERMID uniqueidentifier,
                     TERMMONTH tinyint,
                     YEARMONTH int,
                     AMOUNT money,
                     RATE decimal(20,4),
                     MONTHORDER tinyint)

                insert into @AMOUNTS
                select MT.MEMBERSHIPLEVELTERMID, 
                    month(MT.TRANSACTIONDATE),
                    @CALCYEAR,
                    sum(REVENUESPLIT.REVENUESPLITAMOUNTINCURRENCY) as AMOUNT,
                    cast(COUNT(mt.ID) as decimal(20,4))/cast((case when (select COUNT(mt2.ID) from dbo.MEMBERSHIPTRANSACTION MT2
                                    inner join dbo.MEMBERSHIPLEVEL ML2 on MT2.MEMBERSHIPLEVELID = ML2.ID
                                    where EXPIRATIONDATE >= @STARTDATE and EXPIRATIONDATE <= @ENDDATE and
                                        ML2.MEMBERSHIPPROGRAMID = ML.MEMBERSHIPPROGRAMID) = 0 then 1 
                                    else
                                        (select COUNT(mt2.ID) from dbo.MEMBERSHIPTRANSACTION MT2
                                    inner join dbo.MEMBERSHIPLEVEL ML2 on MT2.MEMBERSHIPLEVELID = ML2.ID
                                    where EXPIRATIONDATE >= @STARTDATE and EXPIRATIONDATE <= @ENDDATE and
                                        ML2.MEMBERSHIPPROGRAMID = ML.MEMBERSHIPPROGRAMID)
                                    end) as decimal(20,4)) as RATE,
                    --Get the relative month order in respect to start/end dates

                    case when month(MT.TRANSACTIONDATE) <= MONTH(@ENDDATE) then
                        12 - (MONTH(@ENDDATE) - MONTH(mt.transactiondate))
                    else
                        1 + (MONTH(mt.transactiondate) - MONTH(@STARTDATE))
                    end as MONTHORDER
                from dbo.MEMBERSHIPTRANSACTION MT
                inner join dbo.MEMBERSHIPLEVEL ML on MT.MEMBERSHIPLEVELID = ML.ID
                inner join dbo.MEMBERSHIPPROGRAM MP on ML.MEMBERSHIPPROGRAMID = MP.ID
                inner join dbo.UFN_MEMBERSHIPLEVELREVENUESPLIT_GETAMOUNTSINCURRENCY_BULK(@SELECTEDCURRENCYID, @ORGANIZATIONCURRENCYID, @DECIMALDIGITS, @ROUNDINGTYPECODE, @CURRENCYCODE) REVENUESPLIT on REVENUESPLIT.MEMBERSHIPTRANSACTIONID = MT.ID
                outer apply dbo.UFN_CURRENCY_GETPROPERTIES(coalesce(@SELECTEDCURRENCYID, ML.BASECURRENCYID)) as CURRENCYPROPERTIES
                where mt.TRANSACTIONDATE >= @STARTDATE and mt.TRANSACTIONDATE <= @ENDDATE
                    and MT.ACTIONCODE <> 4
                    and (MP.ID = @MEMBERSHIPPROGRAMID or @MEMBERSHIPPROGRAMID is null)
                    and dbo.UFN_SITEALLOWEDFORUSER(@CURRENTAPPUSERID, MP.SITEID) = 1
                group by ML.MEMBERSHIPPROGRAMID, MT.MEMBERSHIPLEVELTERMID, MONTH(mt.TRANSACTIONDATE), YEAR(mt.TRANSACTIONDATE)

                declare @EXPIRATIONS table
                    (PROGRAMID uniqueidentifier,
                     NUMEXPIRING bigint,
                     EXPYEAR int)

                insert into @EXPIRATIONS
                select MP.ID,
                    (select COUNT(mt2.ID) from dbo.MEMBERSHIPTRANSACTION MT2
                                    inner join dbo.MEMBERSHIPLEVEL ML2 on MT2.MEMBERSHIPLEVELID = ML2.ID
                                    where EXPIRATIONDATE >= @STARTDATE and EXPIRATIONDATE <= @ENDDATE and
                                        ML2.MEMBERSHIPPROGRAMID = MP.ID),
                    @CALCYEAR
                from dbo.MEMBERSHIPPROGRAM mp
                where (mp.ID = @MEMBERSHIPPROGRAMID or @MEMBERSHIPPROGRAMID is null)
                    and dbo.UFN_SITEALLOWEDFORUSER(@CURRENTAPPUSERID, MP.SITEID) = 1
                union all
                select MP.ID,
                    (select COUNT(mt2.ID) from dbo.MEMBERSHIPTRANSACTION MT2
                                    inner join dbo.MEMBERSHIPLEVEL ML2 on MT2.MEMBERSHIPLEVELID = ML2.ID
                                    where YEAR(EXPIRATIONDATE) = (@CALCYEAR + 1) and
                                        ML2.MEMBERSHIPPROGRAMID = MP.ID),
                    @CALCYEAR + 1
                from dbo.MEMBERSHIPPROGRAM mp
                where (mp.ID = @MEMBERSHIPPROGRAMID or @MEMBERSHIPPROGRAMID is null)
                    and dbo.UFN_SITEALLOWEDFORUSER(@CURRENTAPPUSERID, MP.SITEID) = 1
                union all
                select MP.ID,
                    (select COUNT(mt2.ID) from dbo.MEMBERSHIPTRANSACTION MT2
                                    inner join dbo.MEMBERSHIPLEVEL ML2 on MT2.MEMBERSHIPLEVELID = ML2.ID
                                    where YEAR(EXPIRATIONDATE) = @CALCYEAR + 2 and
                                        ML2.MEMBERSHIPPROGRAMID = MP.ID),
                    @CALCYEAR + 2
                from dbo.MEMBERSHIPPROGRAM mp
                where (mp.ID = @MEMBERSHIPPROGRAMID or @MEMBERSHIPPROGRAMID is null)
                    and dbo.UFN_SITEALLOWEDFORUSER(@CURRENTAPPUSERID, MP.SITEID) = 1
                union all  
                select MP.ID,
                    (select COUNT(mt2.ID) from dbo.MEMBERSHIPTRANSACTION MT2
                                    inner join dbo.MEMBERSHIPLEVEL ML2 on MT2.MEMBERSHIPLEVELID = ML2.ID
                                    where YEAR(EXPIRATIONDATE) = (@CALCYEAR + 3) and
                                        ML2.MEMBERSHIPPROGRAMID = MP.ID),
                    @CALCYEAR + 3
                from dbo.MEMBERSHIPPROGRAM mp
                where (mp.ID = @MEMBERSHIPPROGRAMID or @MEMBERSHIPPROGRAMID is null)
                    and dbo.UFN_SITEALLOWEDFORUSER(@CURRENTAPPUSERID, MP.SITEID) = 1;

                --get year 2 totals

                --number of expected 1 year renewals from year 1

                declare @YEAR2EXP table
                    (PROGID uniqueidentifier,
                     NUMEXP bigint)

                insert into @YEAR2EXP
                select mp.ID,
                sum(A.RATE) * (select NUMEXPIRING from @EXPIRATIONS where PROGRAMID = MP.ID and EXPYEAR = (@CALCYEAR + 1))
                from @AMOUNTS A
                inner join dbo.MEMBERSHIPLEVELTERM MLT on A.TERMID = MLT.ID
                inner join dbo.MEMBERSHIPLEVEL ML on MLT.LEVELID = ML.ID
                inner join dbo.MEMBERSHIPPROGRAM MP on ML.MEMBERSHIPPROGRAMID = MP.ID
                where mlt.TERMCODE = 0 --1 year term

                group by MP.ID

                update @EXPIRATIONS
                set NUMEXPIRING = NUMEXPIRING + y2.numexp
                from @YEAR2EXP y2
                where PROGRAMID = y2.PROGID
                and EXPYEAR = (@CALCYEAR + 2)

                --get year 3 totals

                --number of expected 1 year renewals from year 2

                declare @YEAR3EXPRENEWAL1 table
                    (PROGID uniqueidentifier,
                     NUMEXP bigint)

                insert into @YEAR3EXPRENEWAL1
                select MP.ID,
                sum(A.RATE) * (select NUMEXPIRING from @EXPIRATIONS where PROGRAMID = MP.ID and EXPYEAR = (@CALCYEAR + 2))
                from @AMOUNTS A
                inner join dbo.MEMBERSHIPLEVELTERM MLT on A.TERMID = MLT.ID
                inner join dbo.MEMBERSHIPLEVEL ML on MLT.LEVELID = ML.ID
                inner join dbo.MEMBERSHIPPROGRAM MP on ML.MEMBERSHIPPROGRAMID = MP.ID
                where mlt.TERMCODE = 0 --1 year term

                group by MP.ID

                --number of expected 2 year renewals from year 1

                declare @YEAR3EXPRENEWAL2 table
                    (PROGID uniqueidentifier,
                     NUMEXP bigint)

                insert into @YEAR3EXPRENEWAL2
                select MP.ID, sum(A.RATE) * (select NUMEXPIRING from @EXPIRATIONS where PROGRAMID = MP.ID and EXPYEAR = (@CALCYEAR + 1))
                from @AMOUNTS A
                inner join dbo.MEMBERSHIPLEVELTERM MLT on A.TERMID = MLT.ID
                inner join dbo.MEMBERSHIPLEVEL ML on MLT.LEVELID = ML.ID
                inner join dbo.MEMBERSHIPPROGRAM MP on ML.MEMBERSHIPPROGRAMID = MP.ID
                where mlt.TERMCODE = 1 --2 year term

                group by MP.Id

                -- updating expirations with 1 year renewals from year 2

                update @EXPIRATIONS
                set NUMEXPIRING = NUMEXPIRING + Y3ONEYEAR.NUMEXP
                from @YEAR3EXPRENEWAL1 Y3ONEYEAR
                where (PROGRAMID = Y3ONEYEAR.PROGID)
                    and EXPYEAR = (@CALCYEAR + 3)

                -- updating expirations with 2 year renewals from year 1

                update @EXPIRATIONS
                set NUMEXPIRING = NUMEXPIRING + Y3TWOYEAR.NUMEXP
                from @YEAR3EXPRENEWAL2 Y3TWOYEAR
                where (PROGRAMID = Y3TWOYEAR.PROGID)
                    and EXPYEAR = (@CALCYEAR + 3)

                declare @YEAR nvarchar(11);
                declare @YEAR1 nvarchar(11);
                declare @YEAR2 nvarchar(11);
                declare @YEAR3 nvarchar(11);
                if YEAR(@STARTDATE) <> YEAR(@ENDDATE)
                    begin
                    set @YEAR = cast(YEAR(@STARTDATE) as nvarchar(4)) + ' - ' + cast(YEAR(@ENDDATE) as nvarchar(4))
                    set @YEAR1 = cast(YEAR(DATEADD(YYYY,1,@STARTDATE)) as nvarchar(4)) + ' - ' + cast(YEAR(DATEADD(YYYY,1,@ENDDATE)) as nvarchar(4))
                    set @YEAR2 = cast(YEAR(DATEADD(YYYY,2,@STARTDATE)) as nvarchar(4)) + ' - ' + cast(YEAR(DATEADD(YYYY,2,@ENDDATE)) as nvarchar(4))
                    set @YEAR3 = cast(YEAR(DATEADD(YYYY,3,@STARTDATE)) as nvarchar(4)) + ' - ' + cast(YEAR(DATEADD(YYYY,3,@ENDDATE)) as nvarchar(4))
                    end
                else
                    begin
                    set @YEAR = cast(YEAR(@ENDDATE) as nvarchar(4)); 
                    set @YEAR1 = cast(YEAR(DATEADD(YYYY,1,@ENDDATE)) as nvarchar(4));
                    set @YEAR2 = cast(YEAR(DATEADD(YYYY,2,@ENDDATE)) as nvarchar(4)); 
                    set @YEAR3 = cast(YEAR(DATEADD(YYYY,3,@ENDDATE)) as nvarchar(4));
                    end

                --DISPLAY DATA YEAR

                select @YEAR as YR,
                    MP.ID as PROGRAMID,
                    dbo.UFN_MEMBERSHIPPROGRAM_GETNAME(MP.ID) as PROGRAM,
                    ML.ID as LEVELID,
                    ML.NAME as LEVELNAME,
                    dbo.UFN_MEMBERSHIPLEVELTERMS_PAYMENTOPTIONDESCRIPTION(MLT.ID) as TERM,
                    coalesce((SELECT AMOUNT FROM @AMOUNTS WHERE TERMID = MLT.ID AND MONTHORDER = 1), 0) AS [JAN],
                    coalesce((SELECT AMOUNT FROM @AMOUNTS WHERE TERMID = MLT.ID AND MONTHORDER = 2), 0) AS [FEB],
                    coalesce((SELECT AMOUNT FROM @AMOUNTS WHERE TERMID = MLT.ID AND MONTHORDER = 3), 0) AS [MAR],
                    coalesce((SELECT AMOUNT FROM @AMOUNTS WHERE TERMID = MLT.ID AND MONTHORDER = 4), 0) AS [APR],
                    coalesce((SELECT AMOUNT FROM @AMOUNTS WHERE TERMID = MLT.ID AND MONTHORDER = 5), 0) AS [MAY],
                    coalesce((SELECT AMOUNT FROM @AMOUNTS WHERE TERMID = MLT.ID AND MONTHORDER = 6), 0) AS [JUN],
                    coalesce((SELECT AMOUNT FROM @AMOUNTS WHERE TERMID = MLT.ID AND MONTHORDER = 7), 0) AS [JUL],
                    coalesce((SELECT AMOUNT FROM @AMOUNTS WHERE TERMID = MLT.ID AND MONTHORDER = 8), 0) AS [AUG],
                    coalesce((SELECT AMOUNT FROM @AMOUNTS WHERE TERMID = MLT.ID AND MONTHORDER = 9), 0) AS [SEP],
                    coalesce((SELECT AMOUNT FROM @AMOUNTS WHERE TERMID = MLT.ID AND MONTHORDER = 10), 0) AS [OCT],
                    coalesce((SELECT AMOUNT FROM @AMOUNTS WHERE TERMID = MLT.ID AND MONTHORDER = 11), 0) AS [NOV],
                    coalesce((SELECT AMOUNT FROM @AMOUNTS WHERE TERMID = MLT.ID AND MONTHORDER = 12), 0) AS [DECEM],
                    MONTH(@STARTDATE) as STARTMONTH,
                    CURRENCYPROPERTIES.CURRENCYSYMBOL,
                    CURRENCYPROPERTIES.ISO4217 as ISOCURRENCYCODE,
                    CURRENCYPROPERTIES.DECIMALDIGITS,
                    CURRENCYPROPERTIES.SYMBOLDISPLAYSETTINGCODE as CURRENCYSYMBOLDISPLAYSETTINGCODE,
                    coalesce(@SELECTEDCURRENCYID, MLT.BASECURRENCYID)
                FROM dbo.MEMBERSHIPLEVELTERM MLT 
                INNER JOIN dbo.MEMBERSHIPLEVEL ML ON MLT.LEVELID = ML.ID
                INNER JOIN dbo.MEMBERSHIPPROGRAM MP ON ML.MEMBERSHIPPROGRAMID = MP.ID
                outer apply dbo.UFN_CURRENCY_GETPROPERTIES(coalesce(@SELECTEDCURRENCYID, MLT.BASECURRENCYID)) as CURRENCYPROPERTIES
                where (mp.ID = @MEMBERSHIPPROGRAMID or @MEMBERSHIPPROGRAMID is null)
                    and dbo.UFN_SITEALLOWEDFORUSER(@CURRENTAPPUSERID, MP.SITEID) = 1

                union all 

                --DISPLAY FIRST CALC YEAR

                select @YEAR1 as YR,
                    MP.ID as PROGRAMID,
                    dbo.UFN_MEMBERSHIPPROGRAM_GETNAME(MP.ID) as PROGRAM,
                    ML.ID as LEVELID,
                    ML.NAME as LEVELNAME,
                    dbo.UFN_MEMBERSHIPLEVELTERMS_PAYMENTOPTIONDESCRIPTION(MLT.ID) as TERM,
                    coalesce((SELECT (RATE * (select NUMEXPIRING from @EXPIRATIONS where PROGRAMID = MP.ID and EXPYEAR = (@CALCYEAR + 1))) FROM @AMOUNTS WHERE TERMID = MLT.ID AND MONTHORDER = 1) * dbo.UFN_MEMBERSHIPLEVELTERM_GETAMOUNTINCURRENCY(mlt.ID, coalesce(@SELECTEDCURRENCYID, MLT.BASECURRENCYID)), 0) AS [JAN],
                    coalesce((SELECT (RATE * (select NUMEXPIRING from @EXPIRATIONS where PROGRAMID = MP.ID and EXPYEAR = (@CALCYEAR + 1))) FROM @AMOUNTS WHERE TERMID = MLT.ID AND MONTHORDER = 2) * dbo.UFN_MEMBERSHIPLEVELTERM_GETAMOUNTINCURRENCY(mlt.ID, coalesce(@SELECTEDCURRENCYID, MLT.BASECURRENCYID)), 0) AS [FEB],
                    coalesce((SELECT (RATE * (select NUMEXPIRING from @EXPIRATIONS where PROGRAMID = MP.ID and EXPYEAR = (@CALCYEAR + 1))) FROM @AMOUNTS WHERE TERMID = MLT.ID AND MONTHORDER = 3) * dbo.UFN_MEMBERSHIPLEVELTERM_GETAMOUNTINCURRENCY(mlt.ID, coalesce(@SELECTEDCURRENCYID, MLT.BASECURRENCYID)), 0) AS [MAR],
                    coalesce((SELECT (RATE * (select NUMEXPIRING from @EXPIRATIONS where PROGRAMID = MP.ID and EXPYEAR = (@CALCYEAR + 1))) FROM @AMOUNTS WHERE TERMID = MLT.ID AND MONTHORDER = 4) * dbo.UFN_MEMBERSHIPLEVELTERM_GETAMOUNTINCURRENCY(mlt.ID, coalesce(@SELECTEDCURRENCYID, MLT.BASECURRENCYID)), 0) AS [APR],
                    coalesce((SELECT (RATE * (select NUMEXPIRING from @EXPIRATIONS where PROGRAMID = MP.ID and EXPYEAR = (@CALCYEAR + 1))) FROM @AMOUNTS WHERE TERMID = MLT.ID AND MONTHORDER = 5) * dbo.UFN_MEMBERSHIPLEVELTERM_GETAMOUNTINCURRENCY(mlt.ID, coalesce(@SELECTEDCURRENCYID, MLT.BASECURRENCYID)), 0) AS [MAY],
                    coalesce((SELECT (RATE * (select NUMEXPIRING from @EXPIRATIONS where PROGRAMID = MP.ID and EXPYEAR = (@CALCYEAR + 1))) FROM @AMOUNTS WHERE TERMID = MLT.ID AND MONTHORDER = 6) * dbo.UFN_MEMBERSHIPLEVELTERM_GETAMOUNTINCURRENCY(mlt.ID, coalesce(@SELECTEDCURRENCYID, MLT.BASECURRENCYID)), 0) AS [JUN],
                    coalesce((SELECT (RATE * (select NUMEXPIRING from @EXPIRATIONS where PROGRAMID = MP.ID and EXPYEAR = (@CALCYEAR + 1))) FROM @AMOUNTS WHERE TERMID = MLT.ID AND MONTHORDER = 7) * dbo.UFN_MEMBERSHIPLEVELTERM_GETAMOUNTINCURRENCY(mlt.ID, coalesce(@SELECTEDCURRENCYID, MLT.BASECURRENCYID)), 0) AS [JUL],
                    coalesce((SELECT (RATE * (select NUMEXPIRING from @EXPIRATIONS where PROGRAMID = MP.ID and EXPYEAR = (@CALCYEAR + 1))) FROM @AMOUNTS WHERE TERMID = MLT.ID AND MONTHORDER = 8) * dbo.UFN_MEMBERSHIPLEVELTERM_GETAMOUNTINCURRENCY(mlt.ID, coalesce(@SELECTEDCURRENCYID, MLT.BASECURRENCYID)), 0) AS [AUG],
                    coalesce((SELECT (RATE * (select NUMEXPIRING from @EXPIRATIONS where PROGRAMID = MP.ID and EXPYEAR = (@CALCYEAR + 1))) FROM @AMOUNTS WHERE TERMID = MLT.ID AND MONTHORDER = 9) * dbo.UFN_MEMBERSHIPLEVELTERM_GETAMOUNTINCURRENCY(mlt.ID, coalesce(@SELECTEDCURRENCYID, MLT.BASECURRENCYID)), 0) AS [SEP],
                    coalesce((SELECT (RATE * (select NUMEXPIRING from @EXPIRATIONS where PROGRAMID = MP.ID and EXPYEAR = (@CALCYEAR + 1))) FROM @AMOUNTS WHERE TERMID = MLT.ID AND MONTHORDER = 10) * dbo.UFN_MEMBERSHIPLEVELTERM_GETAMOUNTINCURRENCY(mlt.ID, coalesce(@SELECTEDCURRENCYID, MLT.BASECURRENCYID)), 0) AS [OCT],
                    coalesce((SELECT (RATE * (select NUMEXPIRING from @EXPIRATIONS where PROGRAMID = MP.ID and EXPYEAR = (@CALCYEAR + 1))) FROM @AMOUNTS WHERE TERMID = MLT.ID AND MONTHORDER = 11) * dbo.UFN_MEMBERSHIPLEVELTERM_GETAMOUNTINCURRENCY(mlt.ID, coalesce(@SELECTEDCURRENCYID, MLT.BASECURRENCYID)), 0) AS [NOV],
                    coalesce((SELECT (RATE * (select NUMEXPIRING from @EXPIRATIONS where PROGRAMID = MP.ID and EXPYEAR = (@CALCYEAR + 1))) FROM @AMOUNTS WHERE TERMID = MLT.ID AND MONTHORDER = 12) * dbo.UFN_MEMBERSHIPLEVELTERM_GETAMOUNTINCURRENCY(mlt.ID, coalesce(@SELECTEDCURRENCYID, MLT.BASECURRENCYID)), 0) AS [DECEM],
                    MONTH(@STARTDATE) as STARTMONTH,
                    CURRENCYPROPERTIES.CURRENCYSYMBOL,
                    CURRENCYPROPERTIES.ISO4217 as ISOCURRENCYCODE,
                    CURRENCYPROPERTIES.DECIMALDIGITS,
                    CURRENCYPROPERTIES.SYMBOLDISPLAYSETTINGCODE as CURRENCYSYMBOLDISPLAYSETTINGCODE,
                    coalesce(@SELECTEDCURRENCYID, MLT.BASECURRENCYID)
                FROM dbo.MEMBERSHIPLEVELTERM MLT 
                INNER JOIN dbo.MEMBERSHIPLEVEL ML ON MLT.LEVELID = ML.ID
                INNER JOIN dbo.MEMBERSHIPPROGRAM MP ON ML.MEMBERSHIPPROGRAMID = MP.ID
                outer apply dbo.UFN_CURRENCY_GETPROPERTIES(coalesce(@SELECTEDCURRENCYID, MLT.BASECURRENCYID)) as CURRENCYPROPERTIES
                where (mp.ID = @MEMBERSHIPPROGRAMID or @MEMBERSHIPPROGRAMID is null)
                    and dbo.UFN_SITEALLOWEDFORUSER(@CURRENTAPPUSERID, MP.SITEID) = 1

                union all 

                --display second calc year

                select @YEAR2 as YR,
                    MP.ID as PROGRAMID,
                    dbo.UFN_MEMBERSHIPPROGRAM_GETNAME(MP.ID) as PROGRAM,
                    ML.ID as LEVELID,
                    ML.NAME as LEVELNAME,
                    dbo.UFN_MEMBERSHIPLEVELTERMS_PAYMENTOPTIONDESCRIPTION(MLT.ID) as TERM,
                    coalesce((SELECT (RATE * (select NUMEXPIRING from @EXPIRATIONS where PROGRAMID = MP.ID and EXPYEAR = (@CALCYEAR + 2))) FROM @AMOUNTS WHERE TERMID = MLT.ID AND MONTHORDER = 1) * dbo.UFN_MEMBERSHIPLEVELTERM_GETAMOUNTINCURRENCY(mlt.ID, coalesce(@SELECTEDCURRENCYID, MLT.BASECURRENCYID)), 0) AS [JAN],
                    coalesce((SELECT (RATE * (select NUMEXPIRING from @EXPIRATIONS where PROGRAMID = MP.ID and EXPYEAR = (@CALCYEAR + 2))) FROM @AMOUNTS WHERE TERMID = MLT.ID AND MONTHORDER = 2) * dbo.UFN_MEMBERSHIPLEVELTERM_GETAMOUNTINCURRENCY(mlt.ID, coalesce(@SELECTEDCURRENCYID, MLT.BASECURRENCYID)), 0)  AS [FEB],
                    coalesce((SELECT (RATE * (select NUMEXPIRING from @EXPIRATIONS where PROGRAMID = MP.ID and EXPYEAR = (@CALCYEAR + 2))) FROM @AMOUNTS WHERE TERMID = MLT.ID AND MONTHORDER = 3) * dbo.UFN_MEMBERSHIPLEVELTERM_GETAMOUNTINCURRENCY(mlt.ID, coalesce(@SELECTEDCURRENCYID, MLT.BASECURRENCYID)), 0)  AS [MAR],
                    coalesce((SELECT (RATE * (select NUMEXPIRING from @EXPIRATIONS where PROGRAMID = MP.ID and EXPYEAR = (@CALCYEAR + 2))) FROM @AMOUNTS WHERE TERMID = MLT.ID AND MONTHORDER = 4) * dbo.UFN_MEMBERSHIPLEVELTERM_GETAMOUNTINCURRENCY(mlt.ID, coalesce(@SELECTEDCURRENCYID, MLT.BASECURRENCYID)), 0)  AS [APR],
                    coalesce((SELECT (RATE * (select NUMEXPIRING from @EXPIRATIONS where PROGRAMID = MP.ID and EXPYEAR = (@CALCYEAR + 2))) FROM @AMOUNTS WHERE TERMID = MLT.ID AND MONTHORDER = 5) * dbo.UFN_MEMBERSHIPLEVELTERM_GETAMOUNTINCURRENCY(mlt.ID, coalesce(@SELECTEDCURRENCYID, MLT.BASECURRENCYID)), 0)  AS [MAY],
                    coalesce((SELECT (RATE * (select NUMEXPIRING from @EXPIRATIONS where PROGRAMID = MP.ID and EXPYEAR = (@CALCYEAR + 2))) FROM @AMOUNTS WHERE TERMID = MLT.ID AND MONTHORDER = 6) * dbo.UFN_MEMBERSHIPLEVELTERM_GETAMOUNTINCURRENCY(mlt.ID, coalesce(@SELECTEDCURRENCYID, MLT.BASECURRENCYID)), 0)  AS [JUN],
                    coalesce((SELECT (RATE * (select NUMEXPIRING from @EXPIRATIONS where PROGRAMID = MP.ID and EXPYEAR = (@CALCYEAR + 2))) FROM @AMOUNTS WHERE TERMID = MLT.ID AND MONTHORDER = 7) * dbo.UFN_MEMBERSHIPLEVELTERM_GETAMOUNTINCURRENCY(mlt.ID, coalesce(@SELECTEDCURRENCYID, MLT.BASECURRENCYID)), 0)  AS [JUL],
                    coalesce((SELECT (RATE * (select NUMEXPIRING from @EXPIRATIONS where PROGRAMID = MP.ID and EXPYEAR = (@CALCYEAR + 2))) FROM @AMOUNTS WHERE TERMID = MLT.ID AND MONTHORDER = 8) * dbo.UFN_MEMBERSHIPLEVELTERM_GETAMOUNTINCURRENCY(mlt.ID, coalesce(@SELECTEDCURRENCYID, MLT.BASECURRENCYID)), 0)  AS [AUG],
                    coalesce((SELECT (RATE * (select NUMEXPIRING from @EXPIRATIONS where PROGRAMID = MP.ID and EXPYEAR = (@CALCYEAR + 2))) FROM @AMOUNTS WHERE TERMID = MLT.ID AND MONTHORDER = 9) * dbo.UFN_MEMBERSHIPLEVELTERM_GETAMOUNTINCURRENCY(mlt.ID, coalesce(@SELECTEDCURRENCYID, MLT.BASECURRENCYID)), 0) AS [SEP],
                    coalesce((SELECT (RATE * (select NUMEXPIRING from @EXPIRATIONS where PROGRAMID = MP.ID and EXPYEAR = (@CALCYEAR + 2))) FROM @AMOUNTS WHERE TERMID = MLT.ID AND MONTHORDER = 10) * dbo.UFN_MEMBERSHIPLEVELTERM_GETAMOUNTINCURRENCY(mlt.ID, coalesce(@SELECTEDCURRENCYID, MLT.BASECURRENCYID)), 0)  AS [OCT],
                    coalesce((SELECT (RATE * (select NUMEXPIRING from @EXPIRATIONS where PROGRAMID = MP.ID and EXPYEAR = (@CALCYEAR + 2))) FROM @AMOUNTS WHERE TERMID = MLT.ID AND MONTHORDER = 11) * dbo.UFN_MEMBERSHIPLEVELTERM_GETAMOUNTINCURRENCY(mlt.ID, coalesce(@SELECTEDCURRENCYID, MLT.BASECURRENCYID)), 0)  AS [NOV],
                    coalesce((SELECT (RATE * (select NUMEXPIRING from @EXPIRATIONS where PROGRAMID = MP.ID and EXPYEAR = (@CALCYEAR + 2))) FROM @AMOUNTS WHERE TERMID = MLT.ID AND MONTHORDER = 12) * dbo.UFN_MEMBERSHIPLEVELTERM_GETAMOUNTINCURRENCY(mlt.ID, coalesce(@SELECTEDCURRENCYID, MLT.BASECURRENCYID)), 0)  AS [DECEM],
                    MONTH(@STARTDATE) as STARTMONTH,
                    CURRENCYPROPERTIES.CURRENCYSYMBOL,
                    CURRENCYPROPERTIES.ISO4217 as ISOCURRENCYCODE,
                    CURRENCYPROPERTIES.DECIMALDIGITS,
                    CURRENCYPROPERTIES.SYMBOLDISPLAYSETTINGCODE as CURRENCYSYMBOLDISPLAYSETTINGCODE,
                    coalesce(@SELECTEDCURRENCYID, MLT.BASECURRENCYID)
                FROM dbo.MEMBERSHIPLEVELTERM MLT 
                INNER JOIN dbo.MEMBERSHIPLEVEL ML ON MLT.LEVELID = ML.ID
                INNER JOIN dbo.MEMBERSHIPPROGRAM MP ON ML.MEMBERSHIPPROGRAMID = MP.ID
                outer apply dbo.UFN_CURRENCY_GETPROPERTIES(coalesce(@SELECTEDCURRENCYID, MLT.BASECURRENCYID)) as CURRENCYPROPERTIES
                where (mp.ID = @MEMBERSHIPPROGRAMID or @MEMBERSHIPPROGRAMID is null)
                    and dbo.UFN_SITEALLOWEDFORUSER(@CURRENTAPPUSERID, MP.SITEID) = 1

                union all

                -- display third calc year

                select @YEAR3 as YR,
                    MP.ID as PROGRAMID,
                    dbo.UFN_MEMBERSHIPPROGRAM_GETNAME(MP.ID) as PROGRAM,
                    ML.ID as LEVELID,
                    ML.NAME as LEVELNAME,
                    dbo.UFN_MEMBERSHIPLEVELTERMS_PAYMENTOPTIONDESCRIPTION(MLT.ID) as TERM,
                    coalesce((SELECT (RATE * (select NUMEXPIRING from @EXPIRATIONS where PROGRAMID = MP.ID and EXPYEAR = (@CALCYEAR + 3))) FROM @AMOUNTS WHERE TERMID = MLT.ID AND MONTHORDER = 1) * dbo.UFN_MEMBERSHIPLEVELTERM_GETAMOUNTINCURRENCY(mlt.ID, coalesce(@SELECTEDCURRENCYID, MLT.BASECURRENCYID)), 0) AS [JAN],
                    coalesce((SELECT (RATE * (select NUMEXPIRING from @EXPIRATIONS where PROGRAMID = MP.ID and EXPYEAR = (@CALCYEAR + 3))) FROM @AMOUNTS WHERE TERMID = MLT.ID AND MONTHORDER = 2) * dbo.UFN_MEMBERSHIPLEVELTERM_GETAMOUNTINCURRENCY(mlt.ID, coalesce(@SELECTEDCURRENCYID, MLT.BASECURRENCYID)), 0)  AS [FEB],
                    coalesce((SELECT (RATE * (select NUMEXPIRING from @EXPIRATIONS where PROGRAMID = MP.ID and EXPYEAR = (@CALCYEAR + 3))) FROM @AMOUNTS WHERE TERMID = MLT.ID AND MONTHORDER = 3) * dbo.UFN_MEMBERSHIPLEVELTERM_GETAMOUNTINCURRENCY(mlt.ID, coalesce(@SELECTEDCURRENCYID, MLT.BASECURRENCYID)), 0)  AS [MAR],
                    coalesce((SELECT (RATE * (select NUMEXPIRING from @EXPIRATIONS where PROGRAMID = MP.ID and EXPYEAR = (@CALCYEAR + 3))) FROM @AMOUNTS WHERE TERMID = MLT.ID AND MONTHORDER = 4) * dbo.UFN_MEMBERSHIPLEVELTERM_GETAMOUNTINCURRENCY(mlt.ID, coalesce(@SELECTEDCURRENCYID, MLT.BASECURRENCYID)), 0)  AS [APR],
                    coalesce((SELECT (RATE * (select NUMEXPIRING from @EXPIRATIONS where PROGRAMID = MP.ID and EXPYEAR = (@CALCYEAR + 3))) FROM @AMOUNTS WHERE TERMID = MLT.ID AND MONTHORDER = 5) * dbo.UFN_MEMBERSHIPLEVELTERM_GETAMOUNTINCURRENCY(mlt.ID, coalesce(@SELECTEDCURRENCYID, MLT.BASECURRENCYID)), 0)  AS [MAY],
                    coalesce((SELECT (RATE * (select NUMEXPIRING from @EXPIRATIONS where PROGRAMID = MP.ID and EXPYEAR = (@CALCYEAR + 3))) FROM @AMOUNTS WHERE TERMID = MLT.ID AND MONTHORDER = 6) * dbo.UFN_MEMBERSHIPLEVELTERM_GETAMOUNTINCURRENCY(mlt.ID, coalesce(@SELECTEDCURRENCYID, MLT.BASECURRENCYID)), 0)  AS [JUN],
                    coalesce((SELECT (RATE * (select NUMEXPIRING from @EXPIRATIONS where PROGRAMID = MP.ID and EXPYEAR = (@CALCYEAR + 3))) FROM @AMOUNTS WHERE TERMID = MLT.ID AND MONTHORDER = 7) * dbo.UFN_MEMBERSHIPLEVELTERM_GETAMOUNTINCURRENCY(mlt.ID, coalesce(@SELECTEDCURRENCYID, MLT.BASECURRENCYID)), 0)  AS [JUL],
                    coalesce((SELECT (RATE * (select NUMEXPIRING from @EXPIRATIONS where PROGRAMID = MP.ID and EXPYEAR = (@CALCYEAR + 3))) FROM @AMOUNTS WHERE TERMID = MLT.ID AND MONTHORDER = 8) * dbo.UFN_MEMBERSHIPLEVELTERM_GETAMOUNTINCURRENCY(mlt.ID, coalesce(@SELECTEDCURRENCYID, MLT.BASECURRENCYID)), 0)  AS [AUG],
                    coalesce((SELECT (RATE * (select NUMEXPIRING from @EXPIRATIONS where PROGRAMID = MP.ID and EXPYEAR = (@CALCYEAR + 3))) FROM @AMOUNTS WHERE TERMID = MLT.ID AND MONTHORDER = 9) * dbo.UFN_MEMBERSHIPLEVELTERM_GETAMOUNTINCURRENCY(mlt.ID, coalesce(@SELECTEDCURRENCYID, MLT.BASECURRENCYID)), 0)  AS [SEP],
                    coalesce((SELECT (RATE * (select NUMEXPIRING from @EXPIRATIONS where PROGRAMID = MP.ID and EXPYEAR = (@CALCYEAR + 3))) FROM @AMOUNTS WHERE TERMID = MLT.ID AND MONTHORDER = 10) * dbo.UFN_MEMBERSHIPLEVELTERM_GETAMOUNTINCURRENCY(mlt.ID, coalesce(@SELECTEDCURRENCYID, MLT.BASECURRENCYID)), 0)  AS [OCT],
                    coalesce((SELECT (RATE * (select NUMEXPIRING from @EXPIRATIONS where PROGRAMID = MP.ID and EXPYEAR = (@CALCYEAR + 3))) FROM @AMOUNTS WHERE TERMID = MLT.ID AND MONTHORDER = 11) * dbo.UFN_MEMBERSHIPLEVELTERM_GETAMOUNTINCURRENCY(mlt.ID, coalesce(@SELECTEDCURRENCYID, MLT.BASECURRENCYID)), 0)  AS [NOV],
                    coalesce((SELECT (RATE * (select NUMEXPIRING from @EXPIRATIONS where PROGRAMID = MP.ID and EXPYEAR = (@CALCYEAR + 3))) FROM @AMOUNTS WHERE TERMID = MLT.ID AND MONTHORDER = 12) * dbo.UFN_MEMBERSHIPLEVELTERM_GETAMOUNTINCURRENCY(mlt.ID, coalesce(@SELECTEDCURRENCYID, MLT.BASECURRENCYID)), 0)  AS [DECEM],
                    MONTH(@STARTDATE) as STARTMONTH,
                    CURRENCYPROPERTIES.CURRENCYSYMBOL,
                    CURRENCYPROPERTIES.ISO4217 as ISOCURRENCYCODE,
                    CURRENCYPROPERTIES.DECIMALDIGITS,
                    CURRENCYPROPERTIES.SYMBOLDISPLAYSETTINGCODE as CURRENCYSYMBOLDISPLAYSETTINGCODE,
                    coalesce(@SELECTEDCURRENCYID, MLT.BASECURRENCYID)
                FROM dbo.MEMBERSHIPLEVELTERM MLT 
                INNER JOIN dbo.MEMBERSHIPLEVEL ML ON MLT.LEVELID = ML.ID
                INNER JOIN dbo.MEMBERSHIPPROGRAM MP ON ML.MEMBERSHIPPROGRAMID = MP.ID
                outer apply dbo.UFN_CURRENCY_GETPROPERTIES(coalesce(@SELECTEDCURRENCYID, MLT.BASECURRENCYID)) as CURRENCYPROPERTIES
                where (mp.ID = @MEMBERSHIPPROGRAMID or @MEMBERSHIPPROGRAMID is null)
                    and dbo.UFN_SITEALLOWEDFORUSER(@CURRENTAPPUSERID, MP.SITEID) = 1;

                return 0;