USP_REPORT_EXPECTEDINCOME

create procedure dbo.USP_REPORT_EXPECTEDINCOME
(    
    @DESIGNATIONQUERY uniqueidentifier = null,
    @DESIGNATIONID uniqueidentifier = null,
    @YEARTYPE tinyint,
    @PERIODTYPE tinyint,
    @INCLUDEGIFTAID bit,
    @SITEID uniqueidentifier = null,
    @REPORTUSERID nvarchar(128) = null,
    @CURRENCYCODE tinyint = null,
    @ALTREPORTUSERID nvarchar(128) = null
)
with execute as owner
as
    set nocount on;

    declare @CURRENTAPPUSERID uniqueidentifier = dbo.UFN_APPUSER_GETREPORTAPPUSERID(@REPORTUSERID, @ALTREPORTUSERID);

    declare @SELECTEDCURRENCYID uniqueidentifier;

--    if @CURRENCYCODE = 3

--    begin

--        if dbo.UFN_CURRENCYSET_GETAPPUSERCURRENCYSET(@CURRENTAPPUSERID) is not null

--        begin                    

--            select @SELECTEDCURRENCYID = CURRENCYSET.BASECURRENCYID

--            from dbo.CURRENCYSET

--            where CURRENCYSET.ID = dbo.UFN_CURRENCYSET_GETAPPUSERCURRENCYSET(@CURRENTAPPUSERID);

--        end

--        else

--        begin

--            select @SELECTEDCURRENCYID = CURRENCYSET.BASECURRENCYID

--            from dbo.CURRENCYSET

--            where CURRENCYSET.ID = dbo.UFN_CURRENCYSET_GETAPPUSERDEFAULTCURRENCYSET();

--        end

--    end

--    else

--    begin

        set @SELECTEDCURRENCYID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();
--    end


    declare @PERIODS table(
        NAME nvarchar(100),
        STARTDATE date,
        ENDDATE date,
        SEQUENCE integer
    );

    -- Initialize variables for multicurrency bulk function.

    declare @NOW datetime = getdate();
    declare @ORIGINCODE tinyint;
    select @ORIGINCODE = coalesce(ORGANIZATIONAMOUNTORIGINCODE, 0) from dbo.MULTICURRENCYCONFIGURATION;

    declare @ORGANIZATIONCURRENCYID uniqueidentifier = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();
    declare @ORGANIZATIONCURRENCYDECIMALDIGITS tinyint;
    declare @ORGANIZATIONCURRENCYROUNDINGTYPECODE tinyint;
    select
        @ORGANIZATIONCURRENCYDECIMALDIGITS = CURRENCY.DECIMALDIGITS,
        @ORGANIZATIONCURRENCYROUNDINGTYPECODE = CURRENCY.ROUNDINGTYPECODE
    from dbo.CURRENCY 
    where CURRENCY.ID = @ORGANIZATIONCURRENCYID;

    declare @SELECTEDCURRENCYDECIMALDIGITS tinyint;
    declare @SELECTEDCURRENCYROUNDINGTYPECODE tinyint;
    select
        @SELECTEDCURRENCYDECIMALDIGITS = CURRENCY.DECIMALDIGITS,
        @SELECTEDCURRENCYROUNDINGTYPECODE = CURRENCY.ROUNDINGTYPECODE
    from dbo.CURRENCY 
    where CURRENCY.ID = @SELECTEDCURRENCYID;

    declare @STARTDATE date = getdate();
    declare @STARTMONTH smallint = MONTH(@STARTDATE);
    declare @ENDDATE date;

    if @YEARTYPE = 0 -- Calendar year

    begin
        set @ENDDATE = '12/31/' + cast(YEAR(@STARTDATE) as nvarchar);
    end
    else if @YEARTYPE = 1 -- Fiscal year

    begin
        set @ENDDATE = dbo.UFN_DATE_THISFISCALYEAR_LASTDAY(getdate(), 1);
    end

    declare @PERIODSTARTDATE date = @STARTDATE;
    declare @PERIODENDDATE date;

    if @PERIODTYPE = 2    -- Year

    begin
        insert into @PERIODS (NAME, STARTDATE, ENDDATE)
        values (convert(nvarchar, @STARTDATE) + N' to ' + convert(nvarchar, @ENDDATE), @STARTDATE, @ENDDATE)
    end
    else if @PERIODTYPE = 0        -- Month

    begin
        set @PERIODENDDATE = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@STARTDATE)+1,0))    

        while @PERIODENDDATE <= @ENDDATE
        begin
            if DAY(@PERIODSTARTDATE) = 1
            begin
                insert into @PERIODS (NAME, STARTDATE, ENDDATE)
                values (DATENAME(MONTH, @PERIODSTARTDATE) + ' ' + cast(YEAR(@PERIODSTARTDATE) as nvarchar), @PERIODSTARTDATE, @PERIODENDDATE);
            end
            else
            begin
                insert into @PERIODS (NAME, STARTDATE, ENDDATE)
                values (convert(nvarchar, @PERIODSTARTDATE) + N' to ' + convert(nvarchar, @PERIODENDDATE), @PERIODSTARTDATE, @PERIODENDDATE);
            end

            set @PERIODSTARTDATE = DATEADD(day, 1, @PERIODENDDATE);
            set @PERIODENDDATE = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@PERIODSTARTDATE)+1,0));            
        end    
    end
    else if @PERIODTYPE = 1        -- Quarter

    begin
        set @PERIODSTARTDATE = DATEADD(YEAR, -1, DATEADD(DAY, 1, @ENDDATE))
        set @PERIODENDDATE = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,DATEADD(MONTH, 2, @PERIODSTARTDATE))+1,0));

        while @PERIODENDDATE < @STARTDATE
        begin
            set @PERIODSTARTDATE = DATEADD(MONTH, 3, @PERIODSTARTDATE)            
            set @PERIODENDDATE = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,DATEADD(MONTH, 2, @PERIODSTARTDATE))+1,0));
        end

        insert into @PERIODS (NAME, STARTDATE, ENDDATE)
        values (CAST(@STARTDATE as nvarchar) + ' to ' + CAST(@PERIODENDDATE as nvarchar), @STARTDATE, @PERIODENDDATE);

        set @PERIODSTARTDATE = DATEADD(MONTH, 3, @PERIODSTARTDATE)            
        set @PERIODENDDATE = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,DATEADD(MONTH, 2, @PERIODSTARTDATE))+1,0));

        while @PERIODENDDATE <= @ENDDATE
        begin
            insert into @PERIODS (NAME, STARTDATE, ENDDATE)
            values (CAST(@PERIODSTARTDATE as nvarchar) + ' to ' + CAST(@PERIODENDDATE as nvarchar), @PERIODSTARTDATE, @PERIODENDDATE)

            set @PERIODSTARTDATE = DATEADD(MONTH, 3, @PERIODSTARTDATE)            
            set @PERIODENDDATE = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,DATEADD(MONTH, 2, @PERIODSTARTDATE))+1,0));
        end            
    end

    declare @REPORT table (
        DESIGNATIONID uniqueidentifier,
        PERIOD nvarchar(100),
        EXPECTEDFROMPLEDGES money,
        EXPECTEDFROMRECURRINGGIFTS money,
        PERIODSEQUENCE smallint,
        ISOCURRENCYCODE nvarchar(3),
        CURRENCYSYMBOL nvarchar(5),
        CURRENCYSYMBOLDISPLAYSETTINGCODE integer,
        DECIMALDIGITS integer
    );

    declare @PLEDGEWORK table (
        CONSTITUENTID uniqueidentifier,
        INSTALLMENTSPLITID uniqueidentifier,
        DESIGNATIONID uniqueidentifier,
        DATE date,
        AMOUNT money,
        DECLINESGIFTAID bit,
        ISOCURRENCYCODE nvarchar(3),
        CURRENCYSYMBOL nvarchar(5),
        CURRENCYSYMBOLDISPLAYSETTINGCODE integer,
        DECIMALDIGITS integer
    );

    create table #RECURRINGGIFTINSTALLMENTS (
        CONSTITUENTID uniqueidentifier,
        RECURRINGGIFTID uniqueidentifier,
        INSTALLMENTID uniqueidentifier,
        DATE datetime,
        TRANSACTIONCURRENCYID uniqueidentifier,
        BASECURRENCYID uniqueidentifier,
        BASEEXCHANGERATEID uniqueidentifier,
        ORGANIZATIONEXCHANGERATEID uniqueidentifier,
        DESIGNATIONID uniqueidentifier,
        AMOUNT money,
        DECLINESGIFTAID bit,
        ISOCURRENCYCODE nvarchar(3),
        CURRENCYSYMBOL nvarchar(5),
        CURRENCYSYMBOLDISPLAYSETTINGCODE integer,
        DECIMALDIGITS integer,
        REVALUEDINSTALLMENTSPLITAMOUNT money,
        TRANSACTIONINSTALLMENTBALANCE money,
        INSTALLMENTSPLITBALANCE money,
        ISLATESTINSTALLMENT bit
    );

    declare @RECURRINGGIFTWORK table (
        CONSTITUENTID uniqueidentifier,
        RECURRINGGIFTID uniqueidentifier,
        INSTALLMENTID uniqueidentifier,
        DATE datetime,
        DESIGNATIONID uniqueidentifier,
        AMOUNT money,
        DECLINESGIFTAID bit,
        ISOCURRENCYCODE nvarchar(3),
        CURRENCYSYMBOL nvarchar(5),
        CURRENCYSYMBOLDISPLAYSETTINGCODE integer,
        DECIMALDIGITS integer
    );

    /* WI127914: Switched to creating temp table here instead of table variable for performance reasons. */
    create table #FILTEREDREVENUE (
        ID uniqueidentifier,
        CONSTITUENTID uniqueidentifier,
        DATE date,        
        SPLITID uniqueidentifier,
        DESIGNATIONID uniqueidentifier,
        SPLITAMOUNT money,
        DECLINESGIFTAID bit,
        ISOCURRENCYCODE nvarchar(3),
        CURRENCYSYMBOL nvarchar(5),
        CURRENCYSYMBOLDISPLAYSETTINGCODE integer,
        DECIMALDIGITS integer,
        TYPECODE int
    );        

    declare @SQLTOEXEC nvarchar(max);
    declare @DBOBJECTNAME nvarchar(128);
    declare @DBOBJECTTYPE smallint;

    if @DESIGNATIONQUERY is not null 
    begin
        if not exists(
            select ID 
            from dbo.IDSETREGISTER 
            where ID = @DESIGNATIONQUERY
        )
        begin
            raiserror('ID set does not exist in the database.', 15, 1);
        end

        select 
            @DBOBJECTNAME = DBOBJECTNAME, 
            @DBOBJECTTYPE = OBJECTTYPE 
        from dbo.IDSETREGISTER 
        where ID = @DESIGNATIONQUERY;

        if @DBOBJECTTYPE = 1 
        begin
            set @DBOBJECTNAME = @DBOBJECTNAME + '()';
        end
        else if @DBOBJECTTYPE = 2 
        begin 
            set @DBOBJECTNAME = @DBOBJECTNAME + '(''' + convert(nvarchar(36), @DESIGNATIONQUERY) + ''')';
        end
    end

    set @SQLTOEXEC = '
        select
            FINANCIALTRANSACTION.ID,
            FINANCIALTRANSACTION.CONSTITUENTID,
            cast(FINANCIALTRANSACTION.DATE as datetime) [DATE],
            FINANCIALTRANSACTIONLINEITEM.ID,
            REVENUESPLIT_EXT.DESIGNATIONID,
            FINANCIALTRANSACTIONLINEITEM.TRANSACTIONAMOUNT,            -- Only used for RGs currently.

            case 
                when @INCLUDEGIFTAID = 1 
                    then coalesce(REVENUESPLITGIFTAID.DECLINESGIFTAID, 0) 
                else 0 
            end as DECLINESGIFTAID,
            CURRENCYPROPERTIES.ISO4217 [ISOCURRENCYCODE],
            CURRENCYPROPERTIES.CURRENCYSYMBOL,
            CURRENCYPROPERTIES.SYMBOLDISPLAYSETTINGCODE [CURRENCYSYMBOLDISPLAYSETTINGCODE],
            CURRENCYPROPERTIES.DECIMALDIGITS,
            FINANCIALTRANSACTION.TYPECODE
        from dbo.FINANCIALTRANSACTION
            inner join dbo.REVENUE_EXT on REVENUE_EXT.ID = FINANCIALTRANSACTION.ID
            inner join dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = FINANCIALTRANSACTION.ID
            inner join dbo.REVENUESPLIT_EXT on REVENUESPLIT_EXT.ID = FINANCIALTRANSACTIONLINEITEM.ID
            left join dbo.REVENUESPLITGIFTAID on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLITGIFTAID.ID
            outer apply dbo.UFN_CURRENCY_GETPROPERTIES(@SELECTEDCURRENCYID) CURRENCYPROPERTIES
            left outer join dbo.MEMBERSHIPTRANSACTION on MEMBERSHIPTRANSACTION.REVENUESPLITID = FINANCIALTRANSACTIONLINEITEM.ID
            left outer join dbo.MEMBERSHIP on MEMBERSHIP.ID = MEMBERSHIPTRANSACTION.MEMBERSHIPID
            left outer join dbo.MEMBERSHIPPROGRAM on MEMBERSHIPPROGRAM.ID = MEMBERSHIP.MEMBERSHIPPROGRAMID';

    if @DESIGNATIONQUERY is not null
    begin
        set @SQLTOEXEC = @SQLTOEXEC + '
            inner join dbo.' + @DBOBJECTNAME + ' as SELECTION on REVENUESPLIT_EXT.DESIGNATIONID = SELECTION.ID ';
    end

    set @SQLTOEXEC = @SQLTOEXEC + '
        where FINANCIALTRANSACTION.TYPECODE in (1,2) 
            and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null 
            and FINANCIALTRANSACTIONLINEITEM.TYPECODE != 1 
            and FINANCIALTRANSACTION.DELETEDON is null
            and (@DESIGNATIONID is null 
                or REVENUESPLIT_EXT.DESIGNATIONID = @DESIGNATIONID
            )
            and exists(
                select ID
                from dbo.REVENUESCHEDULE 
                where REVENUESCHEDULE.ID = FINANCIALTRANSACTION.ID
                    and REVENUESCHEDULE.STATUSCODE in (0,5)            -- Active & Lapsed

                    and REVENUESCHEDULE.STARTDATE <= @ENDDATE
                    and (REVENUESCHEDULE.ENDDATE is null 
                        or REVENUESCHEDULE.ENDDATE <= @ENDDATE
                    )
            )
            and (@SITEID is null 
                or (@SITEID = dbo.UFN_SITEID_MAPFROM_DESIGNATIONID(REVENUESPLIT_EXT.DESIGNATIONID)
                or (@SITEID = MEMBERSHIPPROGRAM.SITEID))
            )'

    if dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 0
    begin
        set @SQLTOEXEC = @SQLTOEXEC + '
            and exists(
                select REPORTPERMISSIONS.[HASPERMISSION]
                from dbo.UFN_SITEID_MAPFROM_REVENUESPLITID(FINANCIALTRANSACTIONLINEITEM.ID) as REVENUESITES
                cross apply dbo.UFN_REPORT_HASUSERSITEPERMISSION(@CURRENTAPPUSERID, ''91b72d58-a1e2-4770-bb90-2f145bd9b92c'', REVENUESITES.SITEID) as REPORTPERMISSIONS
            )'
    end

    insert into #FILTEREDREVENUE 
        (ID, CONSTITUENTID, DATE, SPLITID, DESIGNATIONID, SPLITAMOUNT, DECLINESGIFTAID, ISOCURRENCYCODE, CURRENCYSYMBOL, CURRENCYSYMBOLDISPLAYSETTINGCODE, DECIMALDIGITS, TYPECODE)            
    exec sp_executesql @SQLTOEXEC,
        N'@DESIGNATIONID uniqueidentifier, @STARTDATE datetime, @ENDDATE datetime, @SITEID uniqueidentifier, @CURRENTAPPUSERID uniqueidentifier, @INCLUDEGIFTAID bit, @SELECTEDCURRENCYID uniqueidentifier',
        @DESIGNATIONID = @DESIGNATIONID, @STARTDATE=@STARTDATE, @ENDDATE=@ENDDATE, @SITEID = @SITEID, @CURRENTAPPUSERID = @CURRENTAPPUSERID, @INCLUDEGIFTAID = @INCLUDEGIFTAID, @SELECTEDCURRENCYID=@SELECTEDCURRENCYID;

    /* Added indexes on ID and DESIGNATIONID for join to INSTALLMENTSPLIT */
    create index [IX_FILTEREDREVENUE_ID] on #FILTEREDREVENUE (ID) include (CONSTITUENTID, DECLINESGIFTAID, ISOCURRENCYCODE, CURRENCYSYMBOL, CURRENCYSYMBOLDISPLAYSETTINGCODE, DECIMALDIGITS, TYPECODE, DESIGNATIONID);
    create index [IX_FILTEREDREVENUE_DESIGNATIONID] on #FILTEREDREVENUE (DESIGNATIONID);

    -- Get next recurring gift installment split information

    insert into #RECURRINGGIFTINSTALLMENTS (RECURRINGGIFTID, INSTALLMENTID, DATE, TRANSACTIONCURRENCYID, BASECURRENCYID, BASEEXCHANGERATEID, ORGANIZATIONEXCHANGERATEID, CONSTITUENTID, DESIGNATIONID, AMOUNT, DECLINESGIFTAID, ISOCURRENCYCODE, CURRENCYSYMBOL, CURRENCYSYMBOLDISPLAYSETTINGCODE, DECIMALDIGITS, REVALUEDINSTALLMENTSPLITAMOUNT, TRANSACTIONINSTALLMENTBALANCE, INSTALLMENTSPLITBALANCE, ISLATESTINSTALLMENT)
    select
        RECURRINGGIFTINSTALLMENT.REVENUEID,
        RECURRINGGIFTINSTALLMENT.ID,
        RECURRINGGIFTINSTALLMENT.DATE,
        RECURRINGGIFTINSTALLMENT.TRANSACTIONCURRENCYID,
        RECURRINGGIFTINSTALLMENT.BASECURRENCYID,
        RECURRINGGIFTINSTALLMENT.BASEEXCHANGERATEID,
        RECURRINGGIFTINSTALLMENT.ORGANIZATIONEXCHANGERATEID,
        FILTEREDREVENUE.CONSTITUENTID,
        FILTEREDREVENUE.DESIGNATIONID,
        FILTEREDREVENUE.SPLITAMOUNT,
        FILTEREDREVENUE.DECLINESGIFTAID,
        FILTEREDREVENUE.ISOCURRENCYCODE,
        FILTEREDREVENUE.CURRENCYSYMBOL,
        FILTEREDREVENUE.CURRENCYSYMBOLDISPLAYSETTINGCODE,
        FILTEREDREVENUE.DECIMALDIGITS,
        0,                -- To be populated later.

        RECURRINGGIFTINSTALLMENT.TRANSACTIONAMOUNT - coalesce(sum(RECURRINGGIFTINSTALLMENTPAYMENT.AMOUNT),0) as TRANSACTIONINSTALLMENTBALANCE,
        0,                -- To be populated later.

        0                -- To be populated later.

    from dbo.RECURRINGGIFTINSTALLMENT
        inner join #FILTEREDREVENUE FILTEREDREVENUE on RECURRINGGIFTINSTALLMENT.REVENUEID = FILTEREDREVENUE.ID
        left join dbo.RECURRINGGIFTINSTALLMENTPAYMENT on RECURRINGGIFTINSTALLMENTPAYMENT.RECURRINGGIFTINSTALLMENTID = RECURRINGGIFTINSTALLMENT.ID
    where FILTEREDREVENUE.TYPECODE = 2
    group by
        RECURRINGGIFTINSTALLMENT.REVENUEID,
        RECURRINGGIFTINSTALLMENT.ID,
        RECURRINGGIFTINSTALLMENT.DATE,
        RECURRINGGIFTINSTALLMENT.TRANSACTIONCURRENCYID,
        RECURRINGGIFTINSTALLMENT.BASECURRENCYID,
        RECURRINGGIFTINSTALLMENT.BASEEXCHANGERATEID,
        RECURRINGGIFTINSTALLMENT.ORGANIZATIONEXCHANGERATEID,
        RECURRINGGIFTINSTALLMENT.TRANSACTIONAMOUNT,
        FILTEREDREVENUE.CONSTITUENTID,
        FILTEREDREVENUE.DESIGNATIONID,
        FILTEREDREVENUE.SPLITAMOUNT,
        FILTEREDREVENUE.DECLINESGIFTAID,
        FILTEREDREVENUE.ISOCURRENCYCODE,
        FILTEREDREVENUE.CURRENCYSYMBOL,
        FILTEREDREVENUE.CURRENCYSYMBOLDISPLAYSETTINGCODE,
        FILTEREDREVENUE.DECIMALDIGITS;

    create index [IX_RECURRINGGIFTINSTALLMENTS_DESIGNATIONID] on #RECURRINGGIFTINSTALLMENTS (DESIGNATIONID) include (INSTALLMENTID, TRANSACTIONINSTALLMENTBALANCE, AMOUNT, TRANSACTIONCURRENCYID);

    -- Figure out which installment for each RG is the latest for projection purposes.

    update #RECURRINGGIFTINSTALLMENTS 
    set    
        ISLATESTINSTALLMENT = 1
    from #RECURRINGGIFTINSTALLMENTS RECURRINGGIFTINSTALLMENTS
    where RECURRINGGIFTINSTALLMENTS.DATE = (
        select max(DATE)
        from #RECURRINGGIFTINSTALLMENTS INNERINSTALLMENT
        where RECURRINGGIFTINSTALLMENTS.RECURRINGGIFTID = INNERINSTALLMENT.RECURRINGGIFTID
    );

    -- Delete installments from the temp table if they have no balance and aren't the latest installment.

    delete #RECURRINGGIFTINSTALLMENTS
    where ISLATESTINSTALLMENT = 0 
        and TRANSACTIONINSTALLMENTBALANCE = 0;

    -- Calculate "installment split" balances in transaction currency.

    update #RECURRINGGIFTINSTALLMENTS 
    set
        INSTALLMENTSPLITBALANCE =
            case 
                when RECURRINGGIFTINSTALLMENTS.TRANSACTIONINSTALLMENTBALANCE = 0
                    then 0
                else(
                    -- RobertDi 10/15/2010 - This is likely to be quite slow, and it doesn't correctly

                    -- calculate the payment amounts when the payment revenue had other application(s)

                    -- or donation(s).  (The convert-by-proportion uses the full payment revenue amount

                    -- rather than adding up which splits correspond to this recurring gift, because there

                    -- isn't really a way to know that.)  It also ignores writeoffs.

                    --

                    -- This should be addressed with a change in the recurring gift

                    -- table structure - we need to have RECURRINGGIFTINSTALLMENTSPLIT,

                    -- RECURRINGGIFTINSTALLMENTSPLITPAYMENT, and RECURRINGGIFTINSTALLMENTSPLITWRITEOFF

                    -- tables if we want this report to calculate the amount correctly here.


                    select
                        INNERINSTALLMENT.AMOUNT - coalesce (
                        sum (
                            dbo.UFN_CURRENCY_CONVERTBYPROPORTION(
                                case 
                                    when REVENUESPLIT_EXT.ID is null 
                                        then null 
                                    else PAYMENTSPLIT.TRANSACTIONAMOUNT 
                                end,
                                case 
                                    when REVENUE_EXT.ID is null 
                                        then null 
                                    else PAYMENTREVENUE.TRANSACTIONAMOUNT 
                                end,
                                RECURRINGGIFTINSTALLMENTPAYMENT.AMOUNT,
                                CURRENCY.DECIMALDIGITS
                            )
                        -- The above isn't a currency conversion when the currencies are the same,

                        -- but that function does exactly what we want anyway.

                        )
                    ,0)
                    from #RECURRINGGIFTINSTALLMENTS INNERINSTALLMENT
                        left join dbo.RECURRINGGIFTINSTALLMENTPAYMENT on RECURRINGGIFTINSTALLMENTPAYMENT.RECURRINGGIFTINSTALLMENTID = RECURRINGGIFTINSTALLMENTS.INSTALLMENTID
                        left join dbo.FINANCIALTRANSACTION PAYMENTREVENUE
                            on RECURRINGGIFTINSTALLMENTPAYMENT.PAYMENTID = PAYMENTREVENUE.ID 
                                and PAYMENTREVENUE.TYPECODE in (0,1,2,3,4,5,6,7,8,9) 
                                and PAYMENTREVENUE.DELETEDON is null
                        left join dbo.REVENUE_EXT on REVENUE_EXT.ID = PAYMENTREVENUE.ID
                        left join dbo.FINANCIALTRANSACTIONLINEITEM PAYMENTSPLIT on PAYMENTREVENUE.ID = PAYMENTSPLIT.FINANCIALTRANSACTIONID and PAYMENTSPLIT.DELETEDON is null and PAYMENTSPLIT.TYPECODE != 1
                        left join dbo.REVENUESPLIT_EXT on REVENUESPLIT_EXT.ID = PAYMENTSPLIT.ID
                        left join dbo.CURRENCY on INNERINSTALLMENT.TRANSACTIONCURRENCYID = CURRENCY.ID
                    where (REVENUESPLIT_EXT.DESIGNATIONID is null 
                            or REVENUESPLIT_EXT.DESIGNATIONID = INNERINSTALLMENT.DESIGNATIONID
                        )
                        and (REVENUESPLIT_EXT.APPLICATIONCODE is null 
                            or REVENUESPLIT_EXT.APPLICATIONCODE = 3
                        )    -- Recurring gift

                        and INNERINSTALLMENT.DESIGNATIONID = RECURRINGGIFTINSTALLMENTS.DESIGNATIONID
                        and INNERINSTALLMENT.INSTALLMENTID = RECURRINGGIFTINSTALLMENTS.INSTALLMENTID
                    group by INNERINSTALLMENT.AMOUNT, INNERINSTALLMENT.DESIGNATIONID, INNERINSTALLMENT.INSTALLMENTID
                )
            end
    from #RECURRINGGIFTINSTALLMENTS RECURRINGGIFTINSTALLMENTS;

    -- Calculate the revalued installment split amount according to the latest revaluation rate.

    update #RECURRINGGIFTINSTALLMENTS 
    set    
        REVALUEDINSTALLMENTSPLITAMOUNT = 
            case
                when @SELECTEDCURRENCYID = RECURRINGGIFTINSTALLMENTS.TRANSACTIONCURRENCYID
                    then RECURRINGGIFTINSTALLMENTS.AMOUNT
                when @SELECTEDCURRENCYID = RECURRINGGIFTINSTALLMENTS.BASECURRENCYID
                    then dbo.UFN_CURRENCY_ROUND(dbo.UFN_CURRENCY_APPLYRATE(RECURRINGGIFTINSTALLMENTS.AMOUNT, coalesce(REVALUATION.BASERATE,ORIGINALBASERATE.RATE)), BASECURRENCY.DECIMALDIGITS, BASECURRENCY.ROUNDINGTYPECODE)
                when @SELECTEDCURRENCYID = @ORGANIZATIONCURRENCYID
                    then                                                     
                        case @ORIGINCODE
                            when 0
                                then 
                                    case
                                        when RECURRINGGIFTINSTALLMENTS.TRANSACTIONCURRENCYID = RECURRINGGIFTINSTALLMENTS.BASECURRENCYID
                                            then dbo.UFN_CURRENCY_ROUND(dbo.UFN_CURRENCY_APPLYRATE(RECURRINGGIFTINSTALLMENTS.AMOUNT, coalesce(REVALUATION.ORGANIZATIONRATE,ORIGINALORGANIZATIONRATE.RATE)), @ORGANIZATIONCURRENCYDECIMALDIGITS, @ORGANIZATIONCURRENCYROUNDINGTYPECODE)
                                        else dbo.UFN_CURRENCY_ROUND(dbo.UFN_CURRENCY_APPLYRATE(dbo.UFN_CURRENCY_ROUND(dbo.UFN_CURRENCY_APPLYRATE(RECURRINGGIFTINSTALLMENTS.AMOUNT, coalesce(REVALUATION.BASERATE, ORIGINALBASERATE.RATE)), BASECURRENCY.DECIMALDIGITS, BASECURRENCY.ROUNDINGTYPECODE), coalesce(REVALUATION.ORGANIZATIONRATE, ORIGINALORGANIZATIONRATE.RATE)), @ORGANIZATIONCURRENCYDECIMALDIGITS, @ORGANIZATIONCURRENCYROUNDINGTYPECODE)
                                    end
                            when 1
                                then dbo.UFN_CURRENCY_ROUND(dbo.UFN_CURRENCY_APPLYRATE(RECURRINGGIFTINSTALLMENTS.AMOUNT, coalesce(REVALUATION.ORGANIZATIONRATE,ORIGINALORGANIZATIONRATE.RATE)), @ORGANIZATIONCURRENCYDECIMALDIGITS, @ORGANIZATIONCURRENCYROUNDINGTYPECODE)
                        end
                else dbo.UFN_CURRENCY_CONVERT(RECURRINGGIFTINSTALLMENTS.AMOUNT, dbo.UFN_CURRENCYEXCHANGERATE_GETLATEST(RECURRINGGIFTINSTALLMENTS.TRANSACTIONCURRENCYID, @SELECTEDCURRENCYID, @STARTDATE, 1, null))
            end
        from #RECURRINGGIFTINSTALLMENTS RECURRINGGIFTINSTALLMENTS
            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 = RECURRINGGIFTINSTALLMENTS.RECURRINGGIFTID
                    and COMMITMENTREVALUATION.DATE <= RECURRINGGIFTINSTALLMENTS.date
                order by
                    COMMITMENTREVALUATION.DATE desc, 
                    COMMITMENTREVALUATION.SEQUENCE desc 
            ) REVALUATION
            left join dbo.CURRENCYEXCHANGERATE ORIGINALBASERATE on RECURRINGGIFTINSTALLMENTS.BASEEXCHANGERATEID = ORIGINALBASERATE.ID
            left join dbo.CURRENCYEXCHANGERATE ORIGINALORGANIZATIONRATE on RECURRINGGIFTINSTALLMENTS.ORGANIZATIONEXCHANGERATEID = ORIGINALORGANIZATIONRATE.ID
            left join dbo.CURRENCY BASECURRENCY on RECURRINGGIFTINSTALLMENTS.BASECURRENCYID = BASECURRENCY.ID;


    -- Finally, convert the revalued installment split balance by proportion with the total amount.

    update #RECURRINGGIFTINSTALLMENTS 
    set    
        INSTALLMENTSPLITBALANCE =
            case 
                when RECURRINGGIFTINSTALLMENTS.INSTALLMENTSPLITBALANCE < 0 
                    then 0
                else dbo.UFN_CURRENCY_CONVERTBYPROPORTION(RECURRINGGIFTINSTALLMENTS.INSTALLMENTSPLITBALANCE, RECURRINGGIFTINSTALLMENTS.AMOUNT, RECURRINGGIFTINSTALLMENTS.REVALUEDINSTALLMENTSPLITAMOUNT, @SELECTEDCURRENCYDECIMALDIGITS)
            end
    from #RECURRINGGIFTINSTALLMENTS RECURRINGGIFTINSTALLMENTS;

    -- Populate recurring gift amounts in @RECURRINGGIFTWORK.

    -- This will require looping to get new installment dates.

    declare @RG_REVENUEID uniqueidentifier, @INSTALLMENTID uniqueidentifier, @RG_DESIGNATIONID uniqueidentifier;
    declare @DATE date;
    declare @CONSTITUENTID uniqueidentifier;
    declare @DECLINESGIFTAID bit;
    declare @ISOCURRENCYCODE nvarchar(3);
    declare @CURRENCYSYMBOL nvarchar(5);
    declare @CURRENCYSYMBOLDISPLAYSETTINGCODE integer;
    declare @DECIMALDIGITS integer;
    declare @REVALUEDINSTALLMENTSPLITAMOUNT money = 0;
    declare @INSTALLMENTSPLITBALANCE money = 0;
    declare @ISLATESTINSTALLMENT bit = 0;

    declare INSTALLMENT_CURSOR cursor local STATIC FOR
    select
        CONSTITUENTID,
        RECURRINGGIFTID,
        INSTALLMENTID,
        DATE,
        DESIGNATIONID,
        DECLINESGIFTAID,
        ISOCURRENCYCODE,
        CURRENCYSYMBOL,
        CURRENCYSYMBOLDISPLAYSETTINGCODE,
        DECIMALDIGITS,
        REVALUEDINSTALLMENTSPLITAMOUNT,
        INSTALLMENTSPLITBALANCE,
        ISLATESTINSTALLMENT
    from #RECURRINGGIFTINSTALLMENTS
    where DATE <= @ENDDATE
        and(DATE >= @STARTDATE
            or ISLATESTINSTALLMENT = 1
        );

    open INSTALLMENT_CURSOR;
    fetch next from INSTALLMENT_CURSOR into @CONSTITUENTID, @RG_REVENUEID, @INSTALLMENTID, @DATE, @RG_DESIGNATIONID, @DECLINESGIFTAID, @ISOCURRENCYCODE, @CURRENCYSYMBOL, @CURRENCYSYMBOLDISPLAYSETTINGCODE, @DECIMALDIGITS, @REVALUEDINSTALLMENTSPLITAMOUNT, @INSTALLMENTSPLITBALANCE, @ISLATESTINSTALLMENT;

    declare @INSTALLMENTPROJECTED bit = 0;



    while @@FETCH_STATUS = 0
    begin                            

        -- RobertDi 1/5/11 - Include balance of installments before the most recent one.  These installments

        --                   could have a balance if the system recurring gift setting is "Maintain

        --                   installment amounts";  if the installments are paid off, reducing/deleting the

        --                   payment later will leave them with a balance.


        if @ISLATESTINSTALLMENT = 0
        begin
            if @DATE <= @ENDDATE and @DATE >= @STARTDATE
            begin
                insert into @RECURRINGGIFTWORK (RECURRINGGIFTID,INSTALLMENTID, DATE, DESIGNATIONID, AMOUNT, CONSTITUENTID, DECLINESGIFTAID, ISOCURRENCYCODE, CURRENCYSYMBOL, CURRENCYSYMBOLDISPLAYSETTINGCODE, DECIMALDIGITS)
                values (@RG_REVENUEID, null, @DATE, @RG_DESIGNATIONID, @INSTALLMENTSPLITBALANCE, @CONSTITUENTID, @DECLINESGIFTAID, @ISOCURRENCYCODE, @CURRENCYSYMBOL, @CURRENCYSYMBOLDISPLAYSETTINGCODE, @DECIMALDIGITS);
            end
        end
        else
        begin

            while @DATE <= @ENDDATE
            begin
                if @DATE >= @STARTDATE
                begin
                    -- subtract existing payments, if this is the actual installment date

                    -- RobertDi 10/12/10 - This was previously done incorrectly, in a way that made each

                    -- projected installment amount dependent on the payments on the "actual" installments.

                    if @INSTALLMENTPROJECTED = 0
                    begin
                        insert into @RECURRINGGIFTWORK (RECURRINGGIFTID,INSTALLMENTID, DATE, DESIGNATIONID, AMOUNT, CONSTITUENTID, DECLINESGIFTAID, ISOCURRENCYCODE, CURRENCYSYMBOL, CURRENCYSYMBOLDISPLAYSETTINGCODE, DECIMALDIGITS)
                        values (@RG_REVENUEID, null, @DATE, @RG_DESIGNATIONID, @INSTALLMENTSPLITBALANCE, @CONSTITUENTID, @DECLINESGIFTAID, @ISOCURRENCYCODE, @CURRENCYSYMBOL, @CURRENCYSYMBOLDISPLAYSETTINGCODE, @DECIMALDIGITS);
                    end
                    else
                    begin
                        insert into @RECURRINGGIFTWORK (RECURRINGGIFTID,INSTALLMENTID, DATE, DESIGNATIONID, AMOUNT, CONSTITUENTID, DECLINESGIFTAID, ISOCURRENCYCODE, CURRENCYSYMBOL, CURRENCYSYMBOLDISPLAYSETTINGCODE, DECIMALDIGITS)
                        values (@RG_REVENUEID, null, @DATE, @RG_DESIGNATIONID, @REVALUEDINSTALLMENTSPLITAMOUNT, @CONSTITUENTID, @DECLINESGIFTAID, @ISOCURRENCYCODE, @CURRENCYSYMBOL, @CURRENCYSYMBOLDISPLAYSETTINGCODE, @DECIMALDIGITS);
                    end
                end

                set @DATE = dbo.UFN_REVENUE_GETNEXTTRANSACTIONDATE_BYID_1_1(@RG_REVENUEID, @DATE);
                set @INSTALLMENTPROJECTED = 1;

            end
        end

        fetch next from INSTALLMENT_CURSOR into @CONSTITUENTID, @RG_REVENUEID, @INSTALLMENTID, @DATE, @RG_DESIGNATIONID, @DECLINESGIFTAID, @ISOCURRENCYCODE, @CURRENCYSYMBOL, @CURRENCYSYMBOLDISPLAYSETTINGCODE, @DECIMALDIGITS, @REVALUEDINSTALLMENTSPLITAMOUNT, @INSTALLMENTSPLITBALANCE, @ISLATESTINSTALLMENT;
        set @INSTALLMENTPROJECTED = 0;
    end

    close INSTALLMENT_CURSOR;
    deallocate INSTALLMENT_CURSOR;

    if @INCLUDEGIFTAID = 1
    begin
        update @RECURRINGGIFTWORK 
        set
            AMOUNT = AMOUNT + dbo.UFN_GIFTAID_CALCULATEINSTALLMENTSPLITTAXCLAIMAMOUNT(CONSTITUENTID, DESIGNATIONID, DATE, AMOUNT)
        where DECLINESGIFTAID = 0;
    end

    -- Pledges

    if exists(
        select ID 
        from dbo.CONDITIONSETTING
        where NAME = 'Multicurrency'
    )
    begin 
        insert into @PLEDGEWORK (INSTALLMENTSPLITID, DESIGNATIONID, AMOUNT, DATE, CONSTITUENTID, DECLINESGIFTAID, ISOCURRENCYCODE, CURRENCYSYMBOL, CURRENCYSYMBOLDISPLAYSETTINGCODE, DECIMALDIGITS)
        select                    
            INSTALLMENTSPLITBALANCE.ID,
            FILTEREDREVENUE.DESIGNATIONID,
            INSTALLMENTSPLITBALANCE.ORGANIZATIONBALANCE as TRANSACTIONBALANCE,
            INSTALLMENTSPLITBALANCE.DATE,
            FILTEREDREVENUE.CONSTITUENTID,
            FILTEREDREVENUE.DECLINESGIFTAID,
            FILTEREDREVENUE.ISOCURRENCYCODE,
            FILTEREDREVENUE.CURRENCYSYMBOL,
            FILTEREDREVENUE.CURRENCYSYMBOLDISPLAYSETTINGCODE,
            FILTEREDREVENUE.DECIMALDIGITS
        from #FILTEREDREVENUE FILTEREDREVENUE 
            cross apply dbo.UFN_PLEDGE_GETREVALUEDINSTALLMENTSPLITBALANCES_INDATERANGE
            (
                FILTEREDREVENUE.ID,
                @NOW,
                0,
                @STARTDATE,
                @ENDDATE
            )INSTALLMENTSPLITBALANCE
        where FILTEREDREVENUE.TYPECODE = 1
            and INSTALLMENTSPLITBALANCE.DESIGNATIONID = FILTEREDREVENUE.DESIGNATIONID

    end
    else
    begin
        insert into @PLEDGEWORK (INSTALLMENTSPLITID, DESIGNATIONID, AMOUNT, DATE, CONSTITUENTID, DECLINESGIFTAID, ISOCURRENCYCODE, CURRENCYSYMBOL, CURRENCYSYMBOLDISPLAYSETTINGCODE, DECIMALDIGITS)
        select                    
            INSTALLMENTSPLIT.ID,
            FILTEREDREVENUE.DESIGNATIONID,
            INSTALLMENTSPLIT.TRANSACTIONAMOUNT - (
                coalesce(
                    (select
                        sum(INSTALLMENTSPLITPAYMENT.AMOUNT)
                    from dbo.INSTALLMENTSPLITPAYMENT
                        inner join dbo.FINANCIALTRANSACTIONLINEITEM as PAYMENTSPLIT on PAYMENTSPLIT.ID = INSTALLMENTSPLITPAYMENT.PAYMENTID
                        inner join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTION.ID = PAYMENTSPLIT.FINANCIALTRANSACTIONID
                    where INSTALLMENTSPLITPAYMENT.INSTALLMENTSPLITID = INSTALLMENTSPLIT.ID
                        and FINANCIALTRANSACTION.DATE <= @NOW)
                , 0)
                + coalesce(
                    (select
                        sum(INSTALLMENTSPLITWRITEOFF.TRANSACTIONAMOUNT)
                    from dbo.INSTALLMENTSPLITWRITEOFF 
                        inner join dbo.WRITEOFF on INSTALLMENTSPLITWRITEOFF.WRITEOFFID = WRITEOFF.ID
                    where INSTALLMENTSPLITWRITEOFF.INSTALLMENTSPLITID = INSTALLMENTSPLIT.ID
                        and WRITEOFF.DATE <= @NOW)
                , 0)
            ) as TRANSACTIONBALANCE,
            INSTALLMENT.DATE,
            FILTEREDREVENUE.CONSTITUENTID,
            FILTEREDREVENUE.DECLINESGIFTAID,
            FILTEREDREVENUE.ISOCURRENCYCODE,
            FILTEREDREVENUE.CURRENCYSYMBOL,
            FILTEREDREVENUE.CURRENCYSYMBOLDISPLAYSETTINGCODE,
            FILTEREDREVENUE.DECIMALDIGITS
        from #FILTEREDREVENUE FILTEREDREVENUE 
            inner join dbo.INSTALLMENTSPLIT on (INSTALLMENTSPLIT.PLEDGEID = FILTEREDREVENUE.ID and INSTALLMENTSPLIT.DESIGNATIONID = FILTEREDREVENUE.DESIGNATIONID)
            inner join dbo.INSTALLMENT on INSTALLMENT.ID = INSTALLMENTSPLIT.INSTALLMENTID
        where FILTEREDREVENUE.TYPECODE = 1
    end

    if @INCLUDEGIFTAID = 1
    begin
        update @PLEDGEWORK 
        set
            AMOUNT = AMOUNT + dbo.UFN_GIFTAID_CALCULATEINSTALLMENTSPLITTAXCLAIMAMOUNT(CONSTITUENTID, DESIGNATIONID, DATE, AMOUNT)
        where DECLINESGIFTAID = 0;
    end

    declare @PERIOD as nvarchar(100);
    declare @PERIODSEQUENCE as integer = 1;

    -- Iterate through Periods

    declare PERIOD_CURSOR cursor local STATIC FOR 
    select 
        NAME, 
        STARTDATE, 
        ENDDATE
    from @PERIODS;

    open PERIOD_CURSOR;
    fetch next from PERIOD_CURSOR into @PERIOD, @PERIODSTARTDATE, @PERIODENDDATE;

    while @@FETCH_STATUS = 0
    begin
        -- Insert pledge info into report table

        insert into @REPORT (DESIGNATIONID, PERIOD, EXPECTEDFROMPLEDGES, EXPECTEDFROMRECURRINGGIFTS, PERIODSEQUENCE, ISOCURRENCYCODE, CURRENCYSYMBOL, CURRENCYSYMBOLDISPLAYSETTINGCODE, DECIMALDIGITS)
        select 
            DESIGNATIONID,
            @PERIOD,
            SUM(AMOUNT),
            0,
            @PERIODSEQUENCE,
            ISOCURRENCYCODE, 
            CURRENCYSYMBOL, 
            CURRENCYSYMBOLDISPLAYSETTINGCODE, 
            DECIMALDIGITS
        from @PLEDGEWORK
        where DATE >= @PERIODSTARTDATE 
            and DATE <= @PERIODENDDATE
        group by DESIGNATIONID, ISOCURRENCYCODE, CURRENCYSYMBOL, CURRENCYSYMBOLDISPLAYSETTINGCODE, DECIMALDIGITS;


        -- Merge recurring gift info into report table

        merge @REPORT as Target
        using (
            select
                DESIGNATIONID,
                sum(AMOUNT) as AMOUNT,
                ISOCURRENCYCODE,
                CURRENCYSYMBOL,
                CURRENCYSYMBOLDISPLAYSETTINGCODE,
                DECIMALDIGITS
            from @RECURRINGGIFTWORK
            where DATE <= @PERIODENDDATE 
                and DATE >= @PERIODSTARTDATE
            group by DESIGNATIONID, ISOCURRENCYCODE, CURRENCYSYMBOL, CURRENCYSYMBOLDISPLAYSETTINGCODE, DECIMALDIGITS
        ) as Source
        on Target.DESIGNATIONID = Source.DESIGNATIONID and Target.PERIOD = @PERIOD and Target.ISOCURRENCYCODE = Source.ISOCURRENCYCODE and Target.CURRENCYSYMBOL = Source.CURRENCYSYMBOL and Target.CURRENCYSYMBOLDISPLAYSETTINGCODE = Source.CURRENCYSYMBOLDISPLAYSETTINGCODE and Target.DECIMALDIGITS = Source.DECIMALDIGITS
        when matched 
            then 
                update set Target.EXPECTEDFROMRECURRINGGIFTS = Source.AMOUNT
        when not matched by Target 
            then
                insert (DESIGNATIONID, PERIOD, EXPECTEDFROMPLEDGES, EXPECTEDFROMRECURRINGGIFTS, PERIODSEQUENCE, ISOCURRENCYCODE, CURRENCYSYMBOL, CURRENCYSYMBOLDISPLAYSETTINGCODE, DECIMALDIGITS)
                values (Source.DESIGNATIONID, @PERIOD, 0, Source.AMOUNT, @PERIODSEQUENCE, Source.ISOCURRENCYCODE, Source.CURRENCYSYMBOL, Source.CURRENCYSYMBOLDISPLAYSETTINGCODE, Source.DECIMALDIGITS); 


        set @PERIODSEQUENCE = @PERIODSEQUENCE + 1

        fetch next from PERIOD_CURSOR into @PERIOD, @PERIODSTARTDATE, @PERIODENDDATE;
    end

    close PERIOD_CURSOR;
    deallocate PERIOD_CURSOR;

    declare @PERIODTABLE table(PERIOD nvarchar(100)) 
    insert into @PERIODTABLE 
    select distinct PERIOD 
    from @REPORT;

    declare @DESIGNATIONTABLE table(DESIGNATIONID uniqueidentifier)
    insert into @DESIGNATIONTABLE 
    select distinct DESIGNATIONID 
    from @REPORT

    declare @CURRENTPERIOD nvarchar(100)
    declare @CURRENTPERIODSEQUENCE integer = 0
    declare CURRENT_PERIOD_CURSOR cursor local STATIC FOR 
    select 
        PERIODTABLE.PERIOD
    from @PERIODTABLE PERIODTABLE
        inner join @PERIODS PERIODS on PERIODS.NAME = PERIODTABLE.PERIOD;

    open CURRENT_PERIOD_CURSOR;
    fetch next from CURRENT_PERIOD_CURSOR into @CURRENTPERIOD;

    while @@FETCH_STATUS = 0
    begin
        set @CURRENTPERIODSEQUENCE += 1
        if (select COUNT(*) from @REPORT where PERIOD = @CURRENTPERIOD) < (select COUNT(*) from @DESIGNATIONTABLE)
        begin
            insert into @REPORT(DESIGNATIONID, PERIOD, EXPECTEDFROMPLEDGES, EXPECTEDFROMRECURRINGGIFTS, PERIODSEQUENCE, ISOCURRENCYCODE, CURRENCYSYMBOL, CURRENCYSYMBOLDISPLAYSETTINGCODE, DECIMALDIGITS)
            select 
                DESIGNATIONID,
                @CURRENTPERIOD,
                0,
                0,
                @CURRENTPERIODSEQUENCE,
                CURRENCYPROPERTIES.ISO4217 [ISOCURRENCYCODE],
                CURRENCYPROPERTIES.CURRENCYSYMBOL,
                CURRENCYPROPERTIES.SYMBOLDISPLAYSETTINGCODE [CURRENCYSYMBOLDISPLAYSETTINGCODE],
                CURRENCYPROPERTIES.DECIMALDIGITS
            from @DESIGNATIONTABLE
                outer apply dbo.UFN_CURRENCY_GETPROPERTIES(@SELECTEDCURRENCYID) CURRENCYPROPERTIES
            where DESIGNATIONID not in (
                    select DESIGNATIONID 
                    from @REPORT 
                    where PERIOD = @CURRENTPERIOD
                )
        end
        fetch next from CURRENT_PERIOD_CURSOR into @CURRENTPERIOD;
    end

    close CURRENT_PERIOD_CURSOR;
    deallocate CURRENT_PERIOD_CURSOR;

    select 
        DESIGNATIONID,
        'http://www.blackbaud.com/DESIGNATIONID?DESIGNATIONID=' + CONVERT(nvarchar(36),DESIGNATIONID) DESIGNATIONLINK,
        coalesce(dbo.UFN_DESIGNATION_BUILDNAME(DESIGNATIONID), 'None (Earned income)') DESIGNATIONNAME,
        PERIOD,
        EXPECTEDFROMPLEDGES,
        EXPECTEDFROMRECURRINGGIFTS,
        PERIODSEQUENCE,
        ISOCURRENCYCODE,
        CURRENCYSYMBOL,
        CURRENCYSYMBOLDISPLAYSETTINGCODE,
        DECIMALDIGITS
    from @REPORT;