USP_GROUP_RECOGNITIONSUMMARYGET

            CREATE procedure dbo.USP_GROUP_RECOGNITIONSUMMARYGET
            (
                @CONSTITUENTID uniqueidentifier,
                @RECOGNITIONTOTALNUMBER int = null output,
                @RECOGNITIONTOTALAMOUNT money = null output,
                @RECOGNITIONTOTALGROUPAMOUNT money = null output,
                @RECOGNITIONTOTALGROUPMEMBERAMOUNT money = null output,
                @RECOGNITIONAVERAGEAMOUNT money = null output,
                @RECOGNITIONMOSTFREQUENTAMOUNT money = null output,
                @RECOGNITIONTOTALYEARS int = null output,
                @RECOGNITIONCONSECUTIVEYEARS int = null output,
                @RECOGNITIONGIVENSINCEFISCALYEAR datetime = null output,
                @CURRENTAPPUSERID uniqueidentifier = null,
                @SITEFILTERMODE tinyint = 0,
                @SITESSELECTED xml = null,
                @SECURITYFEATUREID uniqueidentifier = null,
                @SECURITYFEATURETYPE tinyint = null,
                @RECOGNITIONGROUPMEMBERTAXCLAIMAMOUNT money = null output,
                @CURRENCYID uniqueidentifier = null
            )
            as
                set nocount on

                declare @CURRENTDATE datetime;
                set @CURRENTDATE = getdate();

                declare @ORGANIZATIONCURRENCYID uniqueidentifier;
                declare @DECIMALDIGITS tinyint;
                declare @ROUNDINGTYPECODE tinyint;

                select @ORGANIZATIONCURRENCYID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY(),
                        @DECIMALDIGITS = DECIMALDIGITS,
                        @ROUNDINGTYPECODE = ROUNDINGTYPECODE
                from 
                    dbo.CURRENCY
                where
                    CURRENCY.ID = @CURRENCYID;

                declare @GROUPINCLUDESMEMBERGIVING bit
                select
                    @GROUPINCLUDESMEMBERGIVING = 
                        case
                            when GD.GROUPTYPECODE = 0 then 1
                            when GD.GROUPTYPECODE = 1 then GT.INCLUDEMEMBERGIVING
                        end
                from dbo.GROUPDATA GD
                left join dbo.GROUPTYPE GT on GD.GROUPTYPEID = GT.ID
                where GD.ID = @CONSTITUENTID

                declare @RECOGNITIONGROUPMEMBERNUMBER integer;
                select
                    @RECOGNITIONGROUPMEMBERNUMBER = count(RR.ID),
                    @RECOGNITIONTOTALGROUPMEMBERAMOUNT = sum(RR.AMOUNTINCURRENCY),
                    @RECOGNITIONGROUPMEMBERTAXCLAIMAMOUNT = sum(case when RS.AMOUNTINCURRENCY > 0 then
                                                                    case R.TYPECODE when 0 then
                                                                            case when RR.AMOUNTINCURRENCY > RS.AMOUNTINCURRENCY then RSGA.TAXCLAIMAMOUNTINCURRENCY
                                                                                 else RR.AMOUNTINCURRENCY/RS.AMOUNTINCURRENCY * RSGA.TAXCLAIMAMOUNTINCURRENCY end
                                                                        when 1 then
                                                                            case when RR.AMOUNTINCURRENCY > RS.AMOUNTINCURRENCY then PLEDGEINSTALLMENTSPLITTAXCLAIMAMOUNT.TAXCLAIMAMOUNTINCURRENCY
                                                                                 else RR.AMOUNTINCURRENCY/RS.AMOUNTINCURRENCY * PLEDGEINSTALLMENTSPLITTAXCLAIMAMOUNT.TAXCLAIMAMOUNTINCURRENCY end
                                                                        else 0
                                                                    end
                                                                 else 0 end)
                from 
                    dbo.UFN_REVENUERECOGNITION_GETAMOUNTINCURRENCY_BULK(@CURRENCYID, @ORGANIZATIONCURRENCYID, @DECIMALDIGITS, @ROUNDINGTYPECODE) RR
                    inner join dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY_BULK(@CURRENCYID, @ORGANIZATIONCURRENCYID, @DECIMALDIGITS, @ROUNDINGTYPECODE) RS on RR.REVENUESPLITID = RS.ID
                    inner join dbo.FINANCIALTRANSACTION R on RS.REVENUEID = R.ID
                    inner join dbo.GROUPMEMBER GM on RR.CONSTITUENTID = GM.MEMBERID
                    left join dbo.GROUPMEMBERDATERANGE GMDR on GMDR.GROUPMEMBERID = GM.ID
                    left join dbo.UFN_REVENUESPLITGIFTAID_GETELIGIBLE(1) ELIGIBLEGIFTAID on ELIGIBLEGIFTAID.ID = RS.ID
                    left join dbo.UFN_REVENUESPLITGIFTAID_GETAMOUNTSINCURRENCY_BULK(@CURRENCYID, @ORGANIZATIONCURRENCYID, @DECIMALDIGITS, @ROUNDINGTYPECODE) RSGA on RSGA.ID = ELIGIBLEGIFTAID.ID
                    left join dbo.UFN_PLEDGEINSTALLMENTSPLIT_CALCULATETAXCLAIMAMOUNTSINCURRENCY_BULK(@CURRENCYID, @ORGANIZATIONCURRENCYID, @DECIMALDIGITS, @ROUNDINGTYPECODE) PLEDGEINSTALLMENTSPLITTAXCLAIMAMOUNT on PLEDGEINSTALLMENTSPLITTAXCLAIMAMOUNT.REVENUESPLITID = RS.ID
                where @GROUPINCLUDESMEMBERGIVING = 1
                    and R.DELETEDON is null
                    and GM.GROUPID = @CONSTITUENTID
                    and (R.TYPECODE = 1 or (R.TYPECODE = 0 and RS.APPLICATIONCODE in (0, 3, 18, 19)) or R.TYPECODE = 7) --Pledge, Payment (Gift or Recurring Gift Payment), or Auction Donation

                    and ((GMDR.DATEFROM is null and (GMDR.DATETO is null or GMDR.DATETO >= RR.EFFECTIVEDATE))
                        or (GMDR.DATETO is null and (GMDR.DATEFROM is null or GMDR.DATEFROM <= RR.EFFECTIVEDATE)) 
                        or (GMDR.DATEFROM <= RR.EFFECTIVEDATE and GMDR.DATETO >= RR.EFFECTIVEDATE))
                    and    exists 
                    (
                        select top 1 RSSUB.ID from dbo.REVENUESPLIT RSSUB
                        cross apply dbo.UFN_SITEID_MAPFROM_REVENUESPLITID(RSSUB.ID) REVSITES
                        where RSSUB.REVENUEID = R.ID
                        /*next line is #SITEEXTENTION code*/
                        and (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[REVSITES].[SITEID] or (SITEID is null and [REVSITES].[SITEID] is null)))
                        and 
                        (
                            @SITEFILTERMODE = 0
                            or 
                            exists(select UFN_SITE_BUILDDATALISTSITEFILTER.SITEID from dbo.UFN_SITE_BUILDDATALISTSITEFILTER(@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED) where UFN_SITE_BUILDDATALISTSITEFILTER.SITEID = REVSITES.SITEID)
                        )
                    )

                declare @RECOGNITIONGROUPNUMBER integer;

                select
                    @RECOGNITIONGROUPNUMBER = count(RR.ID),
                    @RECOGNITIONTOTALGROUPAMOUNT = sum(RR.AMOUNTINCURRENCY)
                from dbo.UFN_REVENUERECOGNITION_GETAMOUNTINCURRENCY_BULK(@CURRENCYID, @ORGANIZATIONCURRENCYID, @DECIMALDIGITS, @ROUNDINGTYPECODE) RR
                    inner join dbo.FINANCIALTRANSACTIONLINEITEM RS on RR.REVENUESPLITID = RS.ID
                    inner join REVENUESPLIT_EXT RSE on RSE.ID = RS.ID
                    inner join dbo.FINANCIALTRANSACTION R on RS.FINANCIALTRANSACTIONID = R.ID
                where (R.TYPECODE = 1 or (R.TYPECODE = 0 and RSE.APPLICATIONCODE in (0, 3, 18, 19)) or R.TYPECODE = 7) --Pledge, Payment (Gift or Recurring Gift Payment), or Auction Donation

                    and RR.CONSTITUENTID = @CONSTITUENTID
                    and R.DELETEDON is null
                    and RS.DELETEDON is null
                    and RS.TYPECODE <> 1
                    and    exists 
                    (
                        select top 1 RSSUB.ID from dbo.REVENUESPLIT RSSUB
                        cross apply dbo.UFN_SITEID_MAPFROM_REVENUESPLITID(RSSUB.ID) REVSITES
                        where RSSUB.REVENUEID = R.ID
                        /*next line is #SITEEXTENTION code*/
                        and (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[REVSITES].[SITEID] or (SITEID is null and [REVSITES].[SITEID] is null)))
                        and 
                        (
                            @SITEFILTERMODE = 0
                            or 
                            exists(select UFN_SITE_BUILDDATALISTSITEFILTER.SITEID from dbo.UFN_SITE_BUILDDATALISTSITEFILTER(@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED) where UFN_SITE_BUILDDATALISTSITEFILTER.SITEID = REVSITES.SITEID)
                        )
                    );

                set @RECOGNITIONGROUPNUMBER = coalesce(@RECOGNITIONGROUPNUMBER, 0);
                set @RECOGNITIONGROUPMEMBERNUMBER = coalesce(@RECOGNITIONGROUPMEMBERNUMBER, 0);
                set @RECOGNITIONTOTALNUMBER = @RECOGNITIONGROUPNUMBER + @RECOGNITIONGROUPMEMBERNUMBER;

                set @RECOGNITIONTOTALGROUPAMOUNT = coalesce(@RECOGNITIONTOTALGROUPAMOUNT, 0);
                set @RECOGNITIONTOTALGROUPMEMBERAMOUNT = coalesce(@RECOGNITIONTOTALGROUPMEMBERAMOUNT, 0);
                set @RECOGNITIONTOTALAMOUNT = @RECOGNITIONTOTALGROUPAMOUNT + @RECOGNITIONTOTALGROUPMEMBERAMOUNT;

                set @RECOGNITIONAVERAGEAMOUNT = 0;
                if @RECOGNITIONTOTALNUMBER <> 0
                    set @RECOGNITIONAVERAGEAMOUNT = @RECOGNITIONTOTALAMOUNT / @RECOGNITIONTOTALNUMBER;

                /*
                  For the most frequent, largest, first, and last gifts, the pattern taken is:
                    1. Figure out what the variant would be only for the group members IF
                        members are included in the giving.
                    2. Over-ride those values using the variant for the group itself IF the group 
                        can be a donor AND a comparison passes (i.e. the largest gift from the group
                        is larger than the largest gift from the group members)
                */

                -- most frequent group member amount

                declare @RECOGNITIONGROUPMEMBERAMOUNTFREQUENCY integer;
                declare @RECOGNITIONMOSTFREQUENTGROUPMEMBERAMOUNT money;
                select top 1
                    @RECOGNITIONMOSTFREQUENTGROUPMEMBERAMOUNT = X.AMOUNT,
                    @RECOGNITIONGROUPMEMBERAMOUNTFREQUENCY = X.MAXCOUNT
                from
                    (select
                        RR.AMOUNTINCURRENCY as AMOUNT,
                        count(RR.ID) TOTAL,
                        max(count(RR.ID)) over () MAXCOUNT
                    from
                        dbo.UFN_REVENUERECOGNITION_GETAMOUNTINCURRENCY_BULK(@CURRENCYID, @ORGANIZATIONCURRENCYID, @DECIMALDIGITS, @ROUNDINGTYPECODE) RR
                        inner join dbo.FINANCIALTRANSACTIONLINEITEM RS on RR.REVENUESPLITID = RS.ID
                        inner join dbo.REVENUESPLIT_EXT RSE on RSE.ID = RS.ID
                        inner join dbo.FINANCIALTRANSACTION R on RS.FINANCIALTRANSACTIONID = R.ID
                        inner join dbo.GROUPMEMBER GM on RR.CONSTITUENTID = GM.MEMBERID
                        left join dbo.GROUPMEMBERDATERANGE GMDR on GMDR.GROUPMEMBERID = GM.ID
                    where @GROUPINCLUDESMEMBERGIVING = 1
                        and R.DELETEDON is null
                        and RS.DELETEDON is null
                        and RS.TYPECODE <> 1
                        and (R.TYPECODE = 1 or (R.TYPECODE = 0 and RSE.APPLICATIONCODE in (0, 3, 18, 19)) or R.TYPECODE = 7) --Pledge, Payment (Gift or Recurring Gift Payment), or Auction Donation

                        and GM.GROUPID = @CONSTITUENTID
                        and ((GMDR.DATEFROM is null and (GMDR.DATETO is null or GMDR.DATETO >= RR.EFFECTIVEDATE))
                            or (GMDR.DATETO is null and (GMDR.DATEFROM is null or GMDR.DATEFROM <= RR.EFFECTIVEDATE)) 
                            or (GMDR.DATEFROM <= RR.EFFECTIVEDATE and GMDR.DATETO >= RR.EFFECTIVEDATE))
                        and    exists 
                        (
                            select top 1 RSSUB.ID from dbo.REVENUESPLIT RSSUB
                            cross apply dbo.UFN_SITEID_MAPFROM_REVENUESPLITID(RSSUB.ID) REVSITES
                            where RSSUB.REVENUEID = R.ID
                            /*next line is #SITEEXTENTION code*/
                            and (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[REVSITES].[SITEID] or (SITEID is null and [REVSITES].[SITEID] is null)))
                            and 
                            (
                                @SITEFILTERMODE = 0
                                or 
                                exists(select UFN_SITE_BUILDDATALISTSITEFILTER.SITEID from dbo.UFN_SITE_BUILDDATALISTSITEFILTER(@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED) where UFN_SITE_BUILDDATALISTSITEFILTER.SITEID = REVSITES.SITEID)
                            )
                        )
                    group by RR.AMOUNTINCURRENCY) X
                where
                    X.TOTAL = X.MAXCOUNT and X.TOTAL > 1
                order by
                    X.AMOUNT desc;

                -- most frequent group amount

                declare @RECOGNITIONMOSTFREQUENTGROUPAMOUNT money;
                declare @RECOGNITIONGROUPAMOUNTFREQUENCY integer;
                select top 1
                    @RECOGNITIONMOSTFREQUENTGROUPAMOUNT = X.AMOUNT,
                    @RECOGNITIONGROUPAMOUNTFREQUENCY = X.MAXCOUNT
                from
                    (select
                        RR.AMOUNTINCURRENCY as AMOUNT,
                        count(RR.ID) TOTAL,
                        max(count(RR.ID)) over () MAXCOUNT
                    from
                        dbo.UFN_REVENUERECOGNITION_GETAMOUNTINCURRENCY_BULK(@CURRENCYID, @ORGANIZATIONCURRENCYID, @DECIMALDIGITS, @ROUNDINGTYPECODE) RR
                        inner join dbo.FINANCIALTRANSACTIONLINEITEM RS on RR.REVENUESPLITID = RS.ID
                        inner join REVENUESPLIT_EXT RSE on RSE.ID = RS.ID
                        inner join dbo.FINANCIALTRANSACTION R on RS.FINANCIALTRANSACTIONID = R.ID
                    where RR.CONSTITUENTID = @CONSTITUENTID
                        and R.DELETEDON is null
                        and RS.DELETEDON is null
        and RS.TYPECODE <> 1                    
                        and (R.TYPECODE = 1 or (R.TYPECODE = 0 and RSE.APPLICATIONCODE in (0, 3, 18, 19)) or R.TYPECODE = 7) --Pledge, Payment (Gift or Recurring Gift Payment), or Auction Donation

                        and    exists 
                        (
                            select top 1 RSSUB.ID from dbo.REVENUESPLIT RSSUB
                            cross apply dbo.UFN_SITEID_MAPFROM_REVENUESPLITID(RSSUB.ID) REVSITES
                            where RSSUB.REVENUEID = R.ID
                            /*next line is #SITEEXTENTION code*/
                            and (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[REVSITES].[SITEID] or (SITEID is null and [REVSITES].[SITEID] is null)))
                            and 
                            (
                                @SITEFILTERMODE = 0
                                or 
                                exists(select UFN_SITE_BUILDDATALISTSITEFILTER.SITEID from dbo.UFN_SITE_BUILDDATALISTSITEFILTER(@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED) where UFN_SITE_BUILDDATALISTSITEFILTER.SITEID = REVSITES.SITEID)
                            )
                        )                        
                    group by RR.AMOUNTINCURRENCY) X
                where
                    X.TOTAL = X.MAXCOUNT and X.TOTAL > 1
                order by
                    X.AMOUNT desc;

                select top(1)
                    @RECOGNITIONMOSTFREQUENTAMOUNT = coalesce(AMOUNT, 0)
                from
                    (select @RECOGNITIONMOSTFREQUENTGROUPAMOUNT as AMOUNT, @RECOGNITIONGROUPAMOUNTFREQUENCY as FREQUENCY
                     union all
                     select @RECOGNITIONMOSTFREQUENTGROUPMEMBERAMOUNT as AMOUNT, @RECOGNITIONGROUPMEMBERAMOUNTFREQUENCY as FREQUENCY) X
                order by X.AMOUNT desc;



                --TOTALYEARS

                select
                    @RECOGNITIONTOTALYEARS = count(distinct(dbo.UFN_DATE_THISFISCALYEAR_FIRSTDAY(RR.EFFECTIVEDATE,0)))
                from UFN_CONSTITUENT_GETGROUPRECOGNITIONS(@CONSTITUENTID, @GROUPINCLUDESMEMBERGIVING) RR
                    inner join dbo.FINANCIALTRANSACTIONLINEITEM RS on RR.REVENUESPLITID = RS.ID
                    inner join dbo.REVENUESPLIT_EXT RSE on RSE.ID = RS.ID
                    inner join dbo.FINANCIALTRANSACTION R on RS.FINANCIALTRANSACTIONID = R.ID
                where (R.TYPECODE = 1 or (R.TYPECODE = 0 and RSE.APPLICATIONCODE in (0, 3)) or R.TYPECODE = 7) --Pledge, Payment (Gift or Recurring Gift Payment), or Auction Donation

                    and R.DELETEDON is null
                    and RS.DELETEDON is null
                    and RS.TYPECODE <> 1
                    and    exists 
                    (
                        select top 1 RSSUB.ID from dbo.REVENUESPLIT RSSUB
                        cross apply dbo.UFN_SITEID_MAPFROM_REVENUESPLITID(RSSUB.ID) REVSITES
                        where RSSUB.REVENUEID = R.ID
                        /*next line is #SITEEXTENTION code*/
                        and (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[REVSITES].[SITEID] or (SITEID is null and [REVSITES].[SITEID] is null)))
                        and 
                        (
                            @SITEFILTERMODE = 0
                            or 
                            exists(select UFN_SITE_BUILDDATALISTSITEFILTER.SITEID from dbo.UFN_SITE_BUILDDATALISTSITEFILTER(@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED) where UFN_SITE_BUILDDATALISTSITEFILTER.SITEID = REVSITES.SITEID)
                        )
                    );

                --CONSECUTIVE YEARS

                declare @FISCALYEAR_FIRSTDAY datetime;
                declare @PREVIOUSFISCALYEAR_FIRSTDAY datetime;
                declare @CONTINUE bit;

                declare FISCALYEARCURSOR cursor local fast_forward for
                select
                    dbo.UFN_DATE_THISFISCALYEAR_FIRSTDAY(RR.EFFECTIVEDATE,0) FISCALYEAR_FIRSTDAY
                from UFN_CONSTITUENT_GETGROUPRECOGNITIONS(@CONSTITUENTID, @GROUPINCLUDESMEMBERGIVING) RR
                    inner join dbo.FINANCIALTRANSACTIONLINEITEM RS on RR.REVENUESPLITID = RS.ID
                    inner join dbo.REVENUESPLIT_EXT RSE on RSE.ID = RS.ID
                    inner join dbo.FINANCIALTRANSACTION R on RS.FINANCIALTRANSACTIONID = R.ID
                where (R.TYPECODE = 1 or (R.TYPECODE = 0 and RSE.APPLICATIONCODE in (0, 3, 18, 19)) or R.TYPECODE = 7) --Pledge, Payment (Gift or Recurring Gift Payment), or Auction Donation

                    and R.DELETEDON is null
                    and RS.DELETEDON is null
                    and RS.TYPECODE <> 1
                    and    exists 
                    (
                        select top 1 RSSUB.ID from dbo.REVENUESPLIT RSSUB
                        cross apply dbo.UFN_SITEID_MAPFROM_REVENUESPLITID(RSSUB.ID) REVSITES
                        where RSSUB.REVENUEID = R.ID
                        /*next line is #SITEEXTENTION code*/
                        and (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[REVSITES].[SITEID] or (SITEID is null and [REVSITES].[SITEID] is null)))
                        and 
                        (
                            @SITEFILTERMODE = 0
                            or 
                            exists(select UFN_SITE_BUILDDATALISTSITEFILTER.SITEID from dbo.UFN_SITE_BUILDDATALISTSITEFILTER(@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED) where UFN_SITE_BUILDDATALISTSITEFILTER.SITEID = REVSITES.SITEID)
                        )
                    )

                group by
                    dbo.UFN_DATE_THISFISCALYEAR_FIRSTDAY(RR.EFFECTIVEDATE,0)
                order by
                    dbo.UFN_DATE_THISFISCALYEAR_FIRSTDAY(RR.EFFECTIVEDATE,0) desc;

                set @RECOGNITIONCONSECUTIVEYEARS = 0;
                set @RECOGNITIONGIVENSINCEFISCALYEAR = null;

                declare @CURRENTFIRSCALYEAR_FIRSTDAY datetime, @LASTFISCALYEAR_FIRSTDAY datetime
                set @CURRENTFIRSCALYEAR_FIRSTDAY = dbo.UFN_DATE_THISFISCALYEAR_FIRSTDAY(@CURRENTDATE,0)
                set @LASTFISCALYEAR_FIRSTDAY = dbo.UFN_DATE_THISFISCALYEAR_FIRSTDAY(dateadd(year,-1,@CURRENTDATE),0)

                open FISCALYEARCURSOR;
                    fetch next from FISCALYEARCURSOR into @FISCALYEAR_FIRSTDAY;
                    if @@FETCH_STATUS = 0 begin
                        if @FISCALYEAR_FIRSTDAY > @CURRENTFIRSCALYEAR_FIRSTDAY begin
                            set @CONTINUE = 1;
                            fetch next from FISCALYEARCURSOR into @PREVIOUSFISCALYEAR_FIRSTDAY;
                        end
                        else if @FISCALYEAR_FIRSTDAY = @CURRENTFIRSCALYEAR_FIRSTDAY or
                           @FISCALYEAR_FIRSTDAY = @LASTFISCALYEAR_FIRSTDAY begin
                            set @RECOGNITIONCONSECUTIVEYEARS = @RECOGNITIONCONSECUTIVEYEARS + 1;
                            set @CONTINUE = 1;
                            fetch next from FISCALYEARCURSOR into @PREVIOUSFISCALYEAR_FIRSTDAY;
                        end
                        else
                            set @CONTINUE = 0;
                    end

                    while @@FETCH_STATUS = 0 and @CONTINUE = 1 begin
                        if @RECOGNITIONCONSECUTIVEYEARS = 0 begin
                            if @PREVIOUSFISCALYEAR_FIRSTDAY > @CURRENTFIRSCALYEAR_FIRSTDAY begin
                                set @FISCALYEAR_FIRSTDAY = @PREVIOUSFISCALYEAR_FIRSTDAY;
                                fetch next from FISCALYEARCURSOR into @PREVIOUSFISCALYEAR_FIRSTDAY;
                            end
                            else if @PREVIOUSFISCALYEAR_FIRSTDAY = @CURRENTFIRSCALYEAR_FIRSTDAY or
                               @PREVIOUSFISCALYEAR_FIRSTDAY = @LASTFISCALYEAR_FIRSTDAY begin
                                set @RECOGNITIONCONSECUTIVEYEARS = @RECOGNITIONCONSECUTIVEYEARS + 1;
                                set @FISCALYEAR_FIRSTDAY = @PREVIOUSFISCALYEAR_FIRSTDAY;
                                fetch next from FISCALYEARCURSOR into @PREVIOUSFISCALYEAR_FIRSTDAY;
                            end
                            else
                                set @CONTINUE = 0
                        end
                        else if @PREVIOUSFISCALYEAR_FIRSTDAY = dateadd(year, -1, @FISCALYEAR_FIRSTDAY) begin
                            set @RECOGNITIONCONSECUTIVEYEARS = @RECOGNITIONCONSECUTIVEYEARS + 1;
                            set @FISCALYEAR_FIRSTDAY = @PREVIOUSFISCALYEAR_FIRSTDAY;
                            fetch next from FISCALYEARCURSOR into @PREVIOUSFISCALYEAR_FIRSTDAY;
                        end
                        else
                            set @CONTINUE = 0;
                    end
                        --When a cursor is used, it should be explicitly closed/deallocated in case of blocking or USP running long

                        close FISCALYEARCURSOR;
                        deallocate FISCALYEARCURSOR;

                if @RECOGNITIONCONSECUTIVEYEARS > 0
                    set @RECOGNITIONGIVENSINCEFISCALYEAR = @FISCALYEAR_FIRSTDAY;