USP_DATAFORMTEMPLATE_VIEW_CONSTITUENT_CUMULATIVEGIVINGSUMMARY

The load procedure used by the view dataform template "Constituent Cumulative Giving Summary View Form"
Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	IN	The input ID parameter used to load the fields defined on the form.
@DATALOADED	bit	INOUT	Output parameter indicating whether or not data was actually loaded.
@TOTALNUMBER	int	INOUT	Total number
@TOTALAMOUNT	money	INOUT	Total giving
@AVERAGEAMOUNT	money	INOUT	Average amount
@MOSTFREQUENTAMOUNT	money	INOUT	Most frequent amount
@TOTALPLEDGEBALANCE	money	INOUT	Total pledge balance
@TOTALYEARS	int	INOUT	Total years given
@CONSECUTIVEYEARS	int	INOUT	Consecutive years
@GIVENSINCEFISCALYEAR	datetime	INOUT	Given secutively since
@LARGESTID	uniqueidentifier	INOUT	ID
@LARGESTRECORDID	uniqueidentifier	INOUT	Record ID
@LARGESTDATE	datetime	INOUT	Date
@LARGESTTYPECODE	tinyint	INOUT	Type code
@LARGESTTYPE	nvarchar(22)	INOUT	Type
@LARGESTAMOUNT	money	INOUT	Amount
@LARGESTPLEDGEBALANCE	money	INOUT	Balance
@LARGESTSPLITS	xml	INOUT	Splits
@FIRSTID	uniqueidentifier	INOUT	ID
@FIRSTRECORDID	uniqueidentifier	INOUT	Record ID
@FIRSTDATE	datetime	INOUT	Date
@FIRSTTYPECODE	tinyint	INOUT	Type code
@FIRSTTYPE	nvarchar(22)	INOUT	Type
@FIRSTAMOUNT	money	INOUT	Amount
@FIRSTPLEDGEBALANCE	money	INOUT	Pledge balance
@FIRSTSPLITS	xml	INOUT	Splits
@LATESTID	uniqueidentifier	INOUT	ID
@LATESTRECORDID	uniqueidentifier	INOUT	Record ID
@LATESTDATE	datetime	INOUT	Date
@LATESTTYPECODE	tinyint	INOUT	Type code
@LATESTTYPE	nvarchar(22)	INOUT	Type
@LATESTAMOUNT	money	INOUT	Amount
@LATESTPLEDGEBALANCE	money	INOUT	Pledge balance
@LATESTSPLITS	xml	INOUT	Splits
@RECOGNITIONTOTALNUMBER	int	INOUT	Total number
@RECOGNITIONTOTALAMOUNT	money	INOUT	Total recognition
@RECOGNITIONAVERAGEAMOUNT	money	INOUT	Average amount
@RECOGNITIONMOSTFREQUENTAMOUNT	money	INOUT	Most frequent amount
@RECOGNITIONTOTALYEARS	int	INOUT	Total years with recognition credit
@RECOGNITIONCONSECUTIVEYEARS	int	INOUT	Consecutive years with recognition credit
@RECOGNITIONGIVENSINCEFISCALYEAR	datetime	INOUT	Recognition credit given consecutively since
@RECOGNITIONLARGESTID	uniqueidentifier	INOUT	ID
@RECOGNITIONLARGESTRECORDID	uniqueidentifier	INOUT	Record ID
@RECOGNITIONLARGESTDATE	datetime	INOUT	Date
@RECOGNITIONLARGESTTYPECODE	tinyint	INOUT	Type code
@RECOGNITIONLARGESTTYPE	nvarchar(22)	INOUT	Type
@RECOGNITIONLARGESTAMOUNT	money	INOUT	Amount
@RECOGNITIONFIRSTID	uniqueidentifier	INOUT	ID
@RECOGNITIONFIRSTRECORDID	uniqueidentifier	INOUT	Record ID
@RECOGNITIONFIRSTDATE	datetime	INOUT	Date
@RECOGNITIONFIRSTTYPECODE	tinyint	INOUT	Type code
@RECOGNITIONFIRSTTYPE	nvarchar(22)	INOUT	Type
@RECOGNITIONFIRSTAMOUNT	money	INOUT	Amount
@RECOGNITIONLATESTID	uniqueidentifier	INOUT	ID
@RECOGNITIONLATESTRECORDID	uniqueidentifier	INOUT	Record ID
@RECOGNITIONLATESTDATE	datetime	INOUT	Date
@RECOGNITIONLATESTTYPECODE	tinyint	INOUT	Type code
@RECOGNITIONLATESTTYPE	nvarchar(22)	INOUT	Type
@RECOGNITIONLATESTAMOUNT	money	INOUT	Amount
@HOUSEHOLDID	uniqueidentifier	INOUT	Household ID
@TOTALHOUSEHOLDGIVING	money	INOUT	Total household giving
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
Definition

Copy


    CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_CONSTITUENT_CUMULATIVEGIVINGSUMMARY (
        @ID uniqueidentifier,
        @DATALOADED bit = 0 output,
        @TOTALNUMBER int = null output,
        @TOTALAMOUNT money = null output,
        @AVERAGEAMOUNT money = null output,
        @MOSTFREQUENTAMOUNT money = null output,
        @TOTALPLEDGEBALANCE money = null output,
        @TOTALYEARS int = null output,
        @CONSECUTIVEYEARS int = null output,
        @GIVENSINCEFISCALYEAR datetime = null output,
        @LARGESTID uniqueidentifier = null output,
        @LARGESTRECORDID uniqueidentifier = null output,
        @LARGESTDATE datetime = null output,
        @LARGESTTYPECODE tinyint = null output,
        @LARGESTTYPE nvarchar(22) = null output,
        @LARGESTAMOUNT money = null output,
        @LARGESTPLEDGEBALANCE money = null output,
        @LARGESTSPLITS xml = null output,
        @FIRSTID uniqueidentifier = null output,
        @FIRSTRECORDID uniqueidentifier = null output,
        @FIRSTDATE datetime = null output,
        @FIRSTTYPECODE tinyint = null output,
        @FIRSTTYPE nvarchar(22) = null output,
        @FIRSTAMOUNT money = null output,
        @FIRSTPLEDGEBALANCE money = null output,
        @FIRSTSPLITS xml = null output,
        @LATESTID uniqueidentifier = null output,
        @LATESTRECORDID uniqueidentifier = null output,
        @LATESTDATE datetime = null output,
        @LATESTTYPECODE tinyint = null output,
        @LATESTTYPE nvarchar(22) = null output,
        @LATESTAMOUNT money = null output,
        @LATESTPLEDGEBALANCE money = null output,
        @LATESTSPLITS xml = null output,
        @RECOGNITIONTOTALNUMBER int = null output,
        @RECOGNITIONTOTALAMOUNT money = null output,
        @RECOGNITIONAVERAGEAMOUNT money = null output,
        @RECOGNITIONMOSTFREQUENTAMOUNT money = null output,
        @RECOGNITIONTOTALYEARS int = null output,
        @RECOGNITIONCONSECUTIVEYEARS int = null output,
        @RECOGNITIONGIVENSINCEFISCALYEAR datetime = null output,
        @RECOGNITIONLARGESTID uniqueidentifier = null output,
        @RECOGNITIONLARGESTRECORDID uniqueidentifier = null output,
        @RECOGNITIONLARGESTDATE datetime = null output,
        @RECOGNITIONLARGESTTYPECODE tinyint = null output,
        @RECOGNITIONLARGESTTYPE nvarchar(22) = null output,
        @RECOGNITIONLARGESTAMOUNT money = null output,
        @RECOGNITIONFIRSTID uniqueidentifier = null output,
        @RECOGNITIONFIRSTRECORDID uniqueidentifier = null output,
        @RECOGNITIONFIRSTDATE datetime = null output,
        @RECOGNITIONFIRSTTYPECODE tinyint = null output,
        @RECOGNITIONFIRSTTYPE nvarchar(22) = null output,
        @RECOGNITIONFIRSTAMOUNT money = null output,
        @RECOGNITIONLATESTID uniqueidentifier = null output,
        @RECOGNITIONLATESTRECORDID uniqueidentifier = null output,
        @RECOGNITIONLATESTDATE datetime = null output,
        @RECOGNITIONLATESTTYPECODE tinyint = null output,
        @RECOGNITIONLATESTTYPE nvarchar(22) = null output,
        @RECOGNITIONLATESTAMOUNT money = null output,
        @HOUSEHOLDID uniqueidentifier = null output,
        @TOTALHOUSEHOLDGIVING money = null output,
        @CURRENTAPPUSERID uniqueidentifier
      ) as
        set nocount on;

        set @DATALOADED = 0;

        select @DATALOADED = 1
        from dbo.CONSTITUENT
        where CONSTITUENT.ID = @ID;

        declare @ORGANIZATIONCURRENCYID uniqueidentifier = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();
        declare @CURRENCYID uniqueidentifier = @ORGANIZATIONCURRENCYID; --default to org currency

        declare @DECIMALDIGITS tinyint;
        declare @ROUNDINGTYPECODE tinyint;

        select 
            @ROUNDINGTYPECODE = ROUNDINGTYPECODE,
            @DECIMALDIGITS = DECIMALDIGITS
        from
            dbo.CURRENCY
        where
            CURRENCY.ID = @CURRENCYID

        declare @CURRENTDATEEARLIESTTIME datetime
        set @CURRENTDATEEARLIESTTIME = dbo.UFN_DATE_GETEARLIESTTIME(getdate());    

        IF OBJECT_ID('tempdb..#ALLREVENUE') IS NOT NULL
            DROP TABLE #ALLREVENUE

        create table #ALLREVENUE 
        (
            REVENUEID uniqueidentifier,
            TRANSACTIONTYPECODE tinyint,
            TRANSACTIONTYPE nvarchar(21) collate DATABASE_DEFAULT,
            REVENUEAMOUNT money,
            [DATE] datetime,
            DATEADDED datetime,
            SPLITID uniqueidentifier,
            APPLICATIONCODE tinyint,
            DESIGNATIONID uniqueidentifier,
            SPLITAMOUNT money,
            WRITEOFFAMOUNT money,
            SPLITNETAMOUNT money
        );

        insert #ALLREVENUE
        select * 
        from dbo.UFN_CONSTITUENT_GIVINGHISTORYINORGCURRENCY_BULK(
            @ID,
            @CURRENTAPPUSERID)
        where dbo.UFN_REVENUE_USERHASSITEACCESS(REVENUEID,@CURRENTAPPUSERID) = 1

        select
            @TOTALNUMBER = count(distinct REVENUEID),
            @TOTALAMOUNT = cast(sum(cast(SPLITNETAMOUNT as decimal(20,5))) as money),
            @TOTALPLEDGEBALANCE = cast(sum(case TRANSACTIONTYPECODE when 1 then cast(dbo.UFN_PLEDGESPLIT_GETBALANCEINCURRENCY(SPLITID, @CURRENCYID) as decimal(20,5)) else 0 end) as money)
        from
            #ALLREVENUE REV;

        set @TOTALNUMBER = coalesce(@TOTALNUMBER, 0);
        set @TOTALAMOUNT = coalesce(@TOTALAMOUNT, 0);
        set @TOTALPLEDGEBALANCE = coalesce(@TOTALPLEDGEBALANCE, 0);

        if @TOTALNUMBER > 0
            set @AVERAGEAMOUNT = cast(@TOTALAMOUNT as decimal(20,5)) / cast(@TOTALNUMBER as decimal(20,5));

        --MODE

        with PLEDGEANDPAYMENTCTE as
        (
            select
                REVENUEID ID,
                sum(SPLITNETAMOUNT) as NETAMOUNT
            from #ALLREVENUE 
            group by REVENUEID
        )
        select top 1
            @MOSTFREQUENTAMOUNT = X.NETAMOUNT
        from
            (select
                NETAMOUNT,
                count(R.ID) TOTAL,
                max(count(R.ID)) over () MAXCOUNT
            from
                PLEDGEANDPAYMENTCTE R
            group by
                NETAMOUNT) X
        where
            X.TOTAL = X.MAXCOUNT and X.TOTAL > 1
        order by
            NETAMOUNT desc;

        --TOTALYEARS

        select
            @TOTALYEARS = count(distinct(dbo.UFN_DATE_THISFISCALYEAR_FIRSTDAY(DATE,0)))
        from
            #ALLREVENUE 

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

        set @CURRENTDATE = getdate();

        declare FISCALYEARCURSOR cursor local fast_forward for
        select
            dbo.UFN_DATE_THISFISCALYEAR_FIRSTDAY(DATE,0) FISCALYEAR_FIRSTDAY
        from
            #ALLREVENUE
        group by
            dbo.UFN_DATE_THISFISCALYEAR_FIRSTDAY(DATE,0)
        order by
            dbo.UFN_DATE_THISFISCALYEAR_FIRSTDAY(DATE,0) desc;

        set @CONSECUTIVEYEARS = 0;
        set @GIVENSINCEFISCALYEAR = null;

        open FISCALYEARCURSOR;
            fetch next from FISCALYEARCURSOR into @FISCALYEAR_FIRSTDAY;
            if @@FETCH_STATUS = 0 begin
                if @FISCALYEAR_FIRSTDAY > dbo.UFN_DATE_THISFISCALYEAR_FIRSTDAY(@CURRENTDATE,0) begin
                    set @CONTINUE = 1;
                    fetch next from FISCALYEARCURSOR into @PREVIOUSFISCALYEAR_FIRSTDAY;
                end
                else if @FISCALYEAR_FIRSTDAY = dbo.UFN_DATE_THISFISCALYEAR_FIRSTDAY(@CURRENTDATE,0) or
                   @FISCALYEAR_FIRSTDAY = dbo.UFN_DATE_THISFISCALYEAR_FIRSTDAY(dateadd(year,-1,@CURRENTDATE),0) begin
                    set @CONSECUTIVEYEARS = @CONSECUTIVEYEARS + 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 @CONSECUTIVEYEARS = 0 begin
                    if @PREVIOUSFISCALYEAR_FIRSTDAY > dbo.UFN_DATE_THISFISCALYEAR_FIRSTDAY(@CURRENTDATE,0) begin
                        set @FISCALYEAR_FIRSTDAY = @PREVIOUSFISCALYEAR_FIRSTDAY;
                        fetch next from FISCALYEARCURSOR into @PREVIOUSFISCALYEAR_FIRSTDAY;
                    end
                    else if @PREVIOUSFISCALYEAR_FIRSTDAY = dbo.UFN_DATE_THISFISCALYEAR_FIRSTDAY(@CURRENTDATE,0) or
                       @PREVIOUSFISCALYEAR_FIRSTDAY = dbo.UFN_DATE_THISFISCALYEAR_FIRSTDAY(dateadd(year,-1,@CURRENTDATE),0) begin
                        set @CONSECUTIVEYEARS = @CONSECUTIVEYEARS + 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 @CONSECUTIVEYEARS = @CONSECUTIVEYEARS + 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 @CONSECUTIVEYEARS > 0
            set @GIVENSINCEFISCALYEAR = @FISCALYEAR_FIRSTDAY;

        select
            @HOUSEHOLDID = GM.GROUPID
        from
            dbo.GROUPMEMBER GM
        left outer join
            dbo.GROUPDATA GD on GD.ID = GM.GROUPID
        left outer join
            dbo.GROUPMEMBERDATERANGE GMDR on GMDR.GROUPMEMBERID = GM.ID
        where
            GM.MEMBERID = @ID
        and
            GD.GROUPTYPECODE = 0
        and ((GMDR.DATEFROM is null and (GMDR.DATETO is null or GMDR.DATETO > @CURRENTDATEEARLIESTTIME))
            or (GMDR.DATETO is null and (GMDR.DATEFROM is null or GMDR.DATEFROM <= @CURRENTDATEEARLIESTTIME)) 
            or (GMDR.DATEFROM <= @CURRENTDATEEARLIESTTIME and GMDR.DATETO > @CURRENTDATEEARLIESTTIME));

        if @HOUSEHOLDID is not null
        begin
            declare @GIVINGBYGROUPMEMBERS money;
            declare @GIVINGBYGROUP money;
            with MEMBERIDS_CTE as (
            select MEMBERID from dbo.GROUPMEMBER where GROUPID = @HOUSEHOLDID)
            select
                @GIVINGBYGROUPMEMBERS = cast(sum(cast(SPLITNETAMOUNT as decimal(20,5))) as money)
            from
                MEMBERIDS_CTE
            cross apply dbo.UFN_CONSTITUENT_GIVINGHISTORYINCURRENCY_BULK(
                MEMBERIDS_CTE.MEMBERID,
                @CURRENTAPPUSERID,
                0,
                null,
                null,
                null,
                @CURRENCYID,
                @ORGANIZATIONCURRENCYID,
                @DECIMALDIGITS,
                @ROUNDINGTYPECODE) REV
            inner join dbo.GROUPMEMBER GM on MEMBERIDS_CTE.MEMBERID = GM.MEMBERID
            left join dbo.GROUPMEMBERDATERANGE GMDR on GMDR.GROUPMEMBERID = GM.ID
            where 
                GM.GROUPID = @HOUSEHOLDID and
                ((GMDR.DATEFROM is null and (GMDR.DATETO is null or GMDR.DATETO >= REV.DATE))
                or (GMDR.DATETO is null and (GMDR.DATEFROM is null or GMDR.DATEFROM <= REV.DATE)) 
                or (GMDR.DATEFROM <= REV.DATE and GMDR.DATETO >= REV.DATE));

            select
                @GIVINGBYGROUP = cast(sum(cast(SPLITNETAMOUNT as decimal(20,5))) as money)
            from dbo.UFN_CONSTITUENT_GIVINGHISTORYINCURRENCY_BULK(
                @HOUSEHOLDID,
                @CURRENTAPPUSERID,
                0,
                null,
                null,
                null,
                @CURRENCYID,
                @ORGANIZATIONCURRENCYID,
                @DECIMALDIGITS,
                @ROUNDINGTYPECODE) REV;

            select @GIVINGBYGROUPMEMBERS = coalesce(@GIVINGBYGROUPMEMBERS, 0);
            select @GIVINGBYGROUP = coalesce(@GIVINGBYGROUP, 0);
            select @TOTALHOUSEHOLDGIVING = @GIVINGBYGROUPMEMBERS + @GIVINGBYGROUP;
        end

        --LARGEST GIFT

        select top 1
          @LARGESTID = R.REVENUEID,
          @LARGESTRECORDID = R.REVENUEID,
          @LARGESTDATE = R.DATE,
          @LARGESTTYPECODE = R.TRANSACTIONTYPECODE,
          @LARGESTTYPE = R.TRANSACTIONTYPE,
          @LARGESTAMOUNT = sum(R.SPLITNETAMOUNT),
          @LARGESTPLEDGEBALANCE = case R.TRANSACTIONTYPECODE when 1 then dbo.UFN_PLEDGE_GETBALANCE(R.REVENUEID) else null end,
          --using this instead of TOXML function, because a different root element is needed

          @LARGESTSPLITS = (select
                    [AMOUNT],
                    [ID],
                    [PURPOSE]
                  from
                    dbo.[UFN_REVENUE_GETSPLITS_LIST](R.REVENUEID)
                  order by
                    ID
                  for xml raw('ITEM'),type,elements,root('LARGESTSPLITS'),BINARY BASE64)
        from #ALLREVENUE R
        group by
          R.REVENUEID, R.TRANSACTIONTYPE, R.TRANSACTIONTYPECODE, R.DATE, R.DATEADDED
        order by
          sum(R.SPLITNETAMOUNT) desc, R.DATE desc, R.DATEADDED desc;

        --FIRST GIFT

        select top 1
          @FIRSTID = R.REVENUEID,
          @FIRSTRECORDID = R.REVENUEID,
          @FIRSTDATE = R.DATE,
          @FIRSTTYPECODE = R.TRANSACTIONTYPECODE,
          @FIRSTTYPE = R.TRANSACTIONTYPE,
          @FIRSTAMOUNT = sum(R.SPLITNETAMOUNT),
          @FIRSTPLEDGEBALANCE = case R.TRANSACTIONTYPECODE when 1 then dbo.UFN_PLEDGE_GETBALANCE(R.REVENUEID) else null end,
          --using this instead of TOXML function, because a different root element is needed

          @FIRSTSPLITS = (select
                    [AMOUNT],
                    [ID],
                    [PURPOSE]
                  from
                    dbo.[UFN_REVENUE_GETSPLITS_LIST](R.REVENUEID)
                  order by
                    ID
                  for xml raw('ITEM'),type,elements,root('FIRSTSPLITS'),BINARY BASE64)
        from #ALLREVENUE R
        group by
          R.REVENUEID, R.TRANSACTIONTYPE, R.TRANSACTIONTYPECODE, R.DATE, R.DATEADDED
        order by
          R.DATE asc, R.DATEADDED asc;

        --LATEST GIFT

        select top 1
          @LATESTID = R.REVENUEID,
          @LATESTRECORDID = R.REVENUEID,
          @LATESTDATE = R.DATE,
          @LATESTTYPECODE = R.TRANSACTIONTYPECODE,
          @LATESTTYPE = R.TRANSACTIONTYPE,
          @LATESTAMOUNT = sum(R.SPLITNETAMOUNT),
          @LATESTPLEDGEBALANCE = case R.TRANSACTIONTYPECODE when 1 then dbo.UFN_PLEDGE_GETBALANCE(R.REVENUEID) else null end,
          --using this instead of TOXML function, because a different root element is needed

          @LATESTSPLITS = (select
                    [AMOUNT],
                    [ID],
                    [PURPOSE]
                  from
                    dbo.[UFN_REVENUE_GETSPLITS_LIST](R.REVENUEID)
                  order by
                    ID
                  for xml raw('ITEM'),type,elements,root('LATESTSPLITS'),BINARY BASE64)
        from #ALLREVENUE R
        group by
          R.REVENUEID, R.TRANSACTIONTYPE, R.TRANSACTIONTYPECODE, R.DATE, R.DATEADDED
        order by
          R.DATE desc, R.DATEADDED desc;

        declare @FIRSTDAYTHISFISCALYEAR datetime;
        declare @FIRSTDAYPREVIOUSFISCALYEAR datetime;
        set @FIRSTDAYTHISFISCALYEAR = dbo.UFN_DATE_THISFISCALYEAR_FIRSTDAY(@CURRENTDATE,0);
        set @FIRSTDAYPREVIOUSFISCALYEAR = dbo.UFN_DATE_THISFISCALYEAR_FIRSTDAY(dateadd(year,-1,@CURRENTDATE),0);


        create table #RECOGNITIONS (
            ID uniqueidentifier primary key,
            REVENUESPLITID uniqueidentifier,
            REVENUEID uniqueidentifier,
            EFFECTIVEDATE datetime,
            TRANSACTIONTYPECODE tinyint,
            TRANSACTIONTYPE nvarchar(21) collate DATABASE_DEFAULT,
            FIRSTDAYOFEFFECTIVEDATEYEAR datetime,
            APPLICATIONCODE tinyint,
            AMOUNT money,
            DATEADDED datetime
        );

        insert into #RECOGNITIONS (ID, REVENUESPLITID, REVENUEID, TRANSACTIONTYPECODE, TRANSACTIONTYPE, FIRSTDAYOFEFFECTIVEDATEYEAR, APPLICATIONCODE, EFFECTIVEDATE, AMOUNT, DATEADDED)
        select
            RR.ID,
            RR.REVENUESPLITID,
            RR.REVENUEID,
            RR.TRANSACTIONTYPECODE,
            RR.TRANSACTIONTYPE,
            dbo.UFN_DATE_THISFISCALYEAR_FIRSTDAY(RR.EFFECTIVEDATE,0) as FIRSTDAYOFEFFECTIVEDATEYEAR,
            RR.APPLICATIONCODE,
            RR.EFFECTIVEDATE,
            RR.AMOUNTINCURRENCY,
            RR.DATEADDED
        from dbo.UFN_RECOGNITIONCREDIT_GETRECOGNITIONS_NOCONSTITNAME(@CURRENCYID, @DECIMALDIGITS, @ROUNDINGTYPECODE, @ORGANIZATIONCURRENCYID) RR
        left join dbo.V_PLANNEDGIFTREVENUE_WITHADDITIONS PGR on PGR.REVENUEID = RR.REVENUEID
        left join dbo.PLANNEDGIFT PG on PG.ID = PGR.PLANNEDGIFTID
        where RR.CONSTITUENTID = @ID
            and (RR.TRANSACTIONTYPECODE in (1, 3, 7, 8) or --Pledge, Matching Gift Claim, Auction donation, Donor challenge claim

                (RR.TRANSACTIONTYPECODE = 4 and PG.VEHICLECODE in (0,1,2,5,6,7,8,9,10,11,12,13)) or --Planned gift

                (RR.TRANSACTIONTYPECODE = 0 and (RR.APPLICATIONCODE in (0, 3, 7) or (RR.APPLICATIONCODE = 1 and RR.REVENUESPLITTYPECODE = 0)))) --Payment;

            and dbo.UFN_REVENUE_USERHASSITEACCESS(RR.REVENUEID,@CURRENTAPPUSERID) = 1

        select
          @RECOGNITIONTOTALNUMBER = count(RR.ID),
          @RECOGNITIONTOTALAMOUNT = sum(RR.AMOUNT),
          @RECOGNITIONAVERAGEAMOUNT = cast(avg(cast(RR.AMOUNT as decimal(20,5))) as money)
        from #RECOGNITIONS RR

        set @RECOGNITIONTOTALNUMBER = coalesce(@RECOGNITIONTOTALNUMBER, 0);
        set @RECOGNITIONTOTALAMOUNT = coalesce(@RECOGNITIONTOTALAMOUNT, 0);

        --MODE

        select top 1
          @RECOGNITIONMOSTFREQUENTAMOUNT = X.AMOUNT
        from
          (select
            dbo.UFN_REVENUERECOGNITION_GETAMOUNTINCURRENCY(RR.ID, @CURRENCYID) as AMOUNT,
            count(RR.ID) TOTAL,
            max(count(RR.ID)) over () MAXCOUNT
          from #RECOGNITIONS RR
          group by
            dbo.UFN_REVENUERECOGNITION_GETAMOUNTINCURRENCY(RR.ID, @CURRENCYID)) X
        where
          X.TOTAL = X.MAXCOUNT and X.TOTAL > 1
        order by
          X.AMOUNT desc;

        --TOTALYEARS

        select
          @RECOGNITIONTOTALYEARS = count(distinct(RR.FIRSTDAYOFEFFECTIVEDATEYEAR))
        from #RECOGNITIONS RR

        --CONSECUTIVE YEARS

        declare @RECOGNITIONFISCALYEAR_FIRSTDAY datetime;
        declare @RECOGNITIONPREVIOUSFISCALYEAR_FIRSTDAY datetime;
        set @CONTINUE = 0;

        declare RECOGNITIONFISCALYEARCURSOR cursor local fast_forward for
        select
          RR.FIRSTDAYOFEFFECTIVEDATEYEAR FISCALYEAR_FIRSTDAY
        from #RECOGNITIONS RR
        group by RR.FIRSTDAYOFEFFECTIVEDATEYEAR
        order by RR.FIRSTDAYOFEFFECTIVEDATEYEAR desc;

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

        open RECOGNITIONFISCALYEARCURSOR;
          fetch next from RECOGNITIONFISCALYEARCURSOR into @RECOGNITIONFISCALYEAR_FIRSTDAY;
          if @@FETCH_STATUS = 0 begin
            if @RECOGNITIONFISCALYEAR_FIRSTDAY > @FIRSTDAYTHISFISCALYEAR begin
              set @CONTINUE = 1;
              fetch next from RECOGNITIONFISCALYEARCURSOR into @RECOGNITIONPREVIOUSFISCALYEAR_FIRSTDAY;
            end
            else if @RECOGNITIONFISCALYEAR_FIRSTDAY = @FIRSTDAYTHISFISCALYEAR or
              @RECOGNITIONFISCALYEAR_FIRSTDAY = @FIRSTDAYPREVIOUSFISCALYEAR begin
              set @RECOGNITIONCONSECUTIVEYEARS = @RECOGNITIONCONSECUTIVEYEARS + 1;
              set @CONTINUE = 1;
              fetch next from RECOGNITIONFISCALYEARCURSOR into @RECOGNITIONPREVIOUSFISCALYEAR_FIRSTDAY;
            end
            else
              set @CONTINUE = 0;
          end

          while @@FETCH_STATUS = 0 and @CONTINUE = 1 begin
            if @RECOGNITIONCONSECUTIVEYEARS = 0 begin
              if @RECOGNITIONPREVIOUSFISCALYEAR_FIRSTDAY > @FIRSTDAYTHISFISCALYEAR begin
                set @RECOGNITIONFISCALYEAR_FIRSTDAY = @RECOGNITIONPREVIOUSFISCALYEAR_FIRSTDAY;
                fetch next from RECOGNITIONFISCALYEARCURSOR into @RECOGNITIONPREVIOUSFISCALYEAR_FIRSTDAY;
              end
              else if @RECOGNITIONPREVIOUSFISCALYEAR_FIRSTDAY = @FIRSTDAYTHISFISCALYEAR or
                @RECOGNITIONPREVIOUSFISCALYEAR_FIRSTDAY = @FIRSTDAYPREVIOUSFISCALYEAR begin
                set @RECOGNITIONCONSECUTIVEYEARS = @RECOGNITIONCONSECUTIVEYEARS + 1;
                set @RECOGNITIONFISCALYEAR_FIRSTDAY = @RECOGNITIONPREVIOUSFISCALYEAR_FIRSTDAY;
                fetch next from RECOGNITIONFISCALYEARCURSOR into @RECOGNITIONPREVIOUSFISCALYEAR_FIRSTDAY;
              end
              else
                set @CONTINUE = 0
            end
            else if @RECOGNITIONPREVIOUSFISCALYEAR_FIRSTDAY = dateadd(year, -1, @RECOGNITIONFISCALYEAR_FIRSTDAY) begin
              set @RECOGNITIONCONSECUTIVEYEARS = @RECOGNITIONCONSECUTIVEYEARS + 1;
              set @RECOGNITIONFISCALYEAR_FIRSTDAY = @RECOGNITIONPREVIOUSFISCALYEAR_FIRSTDAY;
              fetch next from RECOGNITIONFISCALYEARCURSOR into @RECOGNITIONPREVIOUSFISCALYEAR_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 RECOGNITIONFISCALYEARCURSOR;
        deallocate RECOGNITIONFISCALYEARCURSOR;

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

        --LARGEST GIFT

        select top 1
          @RECOGNITIONLARGESTID = RR.ID,
          @RECOGNITIONLARGESTRECORDID = RR.REVENUEID,
          @RECOGNITIONLARGESTDATE = RR.EFFECTIVEDATE,
          @RECOGNITIONLARGESTTYPECODE = RR.TRANSACTIONTYPECODE,
          @RECOGNITIONLARGESTTYPE = RR.TRANSACTIONTYPE,
          @RECOGNITIONLARGESTAMOUNT = RR.AMOUNT
        from #RECOGNITIONS RR
        order by
          RR.AMOUNT desc, RR.EFFECTIVEDATE desc, RR.DATEADDED desc;

        --FIRST GIFT

        select top 1
          @RECOGNITIONFIRSTID = RR.ID,
          @RECOGNITIONFIRSTRECORDID = RR.REVENUEID,
          @RECOGNITIONFIRSTDATE = RR.EFFECTIVEDATE,
          @RECOGNITIONFIRSTTYPECODE = RR.TRANSACTIONTYPECODE,
          @RECOGNITIONFIRSTTYPE = RR.TRANSACTIONTYPE,
          @RECOGNITIONFIRSTAMOUNT = RR.AMOUNT
        from #RECOGNITIONS RR
        order by
          RR.EFFECTIVEDATE asc, RR.DATEADDED asc;

        --LATEST GIFT

        select top 1
          @RECOGNITIONLATESTID = RR.ID,
          @RECOGNITIONLATESTRECORDID = RR.REVENUEID,
          @RECOGNITIONLATESTDATE = RR.EFFECTIVEDATE,
          @RECOGNITIONLATESTTYPECODE = RR.TRANSACTIONTYPECODE,
          @RECOGNITIONLATESTTYPE = RR.TRANSACTIONTYPE,
          @RECOGNITIONLATESTAMOUNT = RR.AMOUNT
        from #RECOGNITIONS RR
        order by
          RR.EFFECTIVEDATE desc, RR.DATEADDED desc;


        drop table #RECOGNITIONS;

        return 0;