USP_DATAFORMTEMPLATE_VIEW_CONSTITUENTGROUP_CUMULATIVEGIVINGSUMMARY

The load procedure used by the view dataform template "Constituent Group 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.
@GROUPINCLUDESMEMBERGIVING	bit	INOUT	Group includes member giving
@ISHOUSEHOLD	bit	INOUT	Is group household
@TOTALNUMBER	int	INOUT	Total number
@TOTALAMOUNT	money	INOUT	Total giving
@TOTALGROUPAMOUNT	money	INOUT	Total giving by group
@TOTALGROUPMEMBERAMOUNT	money	INOUT	Total giving by group members
@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 consecutively 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
@LARGESTCONSTITUENTNAME	nvarchar(300)	INOUT	Constituent
@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
@FIRSTCONSTITUENTNAME	nvarchar(300)	INOUT	Constituent
@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
@LATESTCONSTITUENTNAME	nvarchar(300)	INOUT	Constituent
@RECOGNITIONTOTALNUMBER	int	INOUT	Total number
@RECOGNITIONTOTALAMOUNT	money	INOUT	Total recognition
@RECOGNITIONTOTALGROUPAMOUNT	money	INOUT	Total recognition for group
@RECOGNITIONTOTALGROUPMEMBERAMOUNT	money	INOUT	Total recognition for group members
@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 given consecutively since
@RECOGNITIONLARGESTRECORDID	uniqueidentifier	INOUT	Record ID
@RECOGNITIONLARGESTDATE	datetime	INOUT	Date
@RECOGNITIONLARGESTTYPECODE	tinyint	INOUT	Type code
@RECOGNITIONLARGESTTYPE	nvarchar(22)	INOUT	Type
@RECOGNITIONLARGESTAMOUNT	money	INOUT	Amount
@RECOGNITIONLARGESTCONSTITUENTNAME	nvarchar(300)	INOUT	Constituent
@RECOGNITIONFIRSTRECORDID	uniqueidentifier	INOUT	Record ID
@RECOGNITIONFIRSTDATE	datetime	INOUT	Date
@RECOGNITIONFIRSTTYPECODE	tinyint	INOUT	Type code
@RECOGNITIONFIRSTTYPE	nvarchar(22)	INOUT	Type
@RECOGNITIONFIRSTAMOUNT	money	INOUT	Amount
@RECOGNITIONFIRSTCONSTITUENTNAME	nvarchar(300)	INOUT	Constituent
@RECOGNITIONLATESTRECORDID	uniqueidentifier	INOUT	Record ID
@RECOGNITIONLATESTDATE	datetime	INOUT	Date
@RECOGNITIONLATESTTYPECODE	tinyint	INOUT	Type code
@RECOGNITIONLATESTTYPE	nvarchar(22)	INOUT	Type
@RECOGNITIONLATESTAMOUNT	money	INOUT	Amount
@RECOGNITIONLATESTCONSTITUENTNAME	nvarchar(300)	INOUT	Constituent
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
Definition

Copy


        CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_CONSTITUENTGROUP_CUMULATIVEGIVINGSUMMARY (
            @ID uniqueidentifier,
            @DATALOADED bit = 0 output,
            @GROUPINCLUDESMEMBERGIVING bit = null output,
            @ISHOUSEHOLD bit = null output,
            @TOTALNUMBER int = null output,
            @TOTALAMOUNT money = null output,
            @TOTALGROUPAMOUNT money = null output,
            @TOTALGROUPMEMBERAMOUNT 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,
            @LARGESTCONSTITUENTNAME nvarchar(300) = 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,
            @FIRSTCONSTITUENTNAME nvarchar(300) = 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,
            @LATESTCONSTITUENTNAME nvarchar(300) = null output,
            @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,
            @RECOGNITIONLARGESTRECORDID uniqueidentifier = null output,
            @RECOGNITIONLARGESTDATE datetime = null output,
            @RECOGNITIONLARGESTTYPECODE tinyint = null output,
            @RECOGNITIONLARGESTTYPE nvarchar(22) = null output,
            @RECOGNITIONLARGESTAMOUNT money = null output,
            @RECOGNITIONLARGESTCONSTITUENTNAME nvarchar(300) = null output,
            @RECOGNITIONFIRSTRECORDID uniqueidentifier = null output,
            @RECOGNITIONFIRSTDATE datetime = null output,
            @RECOGNITIONFIRSTTYPECODE tinyint = null output,
            @RECOGNITIONFIRSTTYPE nvarchar(22) = null output,
            @RECOGNITIONFIRSTAMOUNT money = null output,
            @RECOGNITIONFIRSTCONSTITUENTNAME nvarchar(300) = null output,
            @RECOGNITIONLATESTRECORDID uniqueidentifier = null output,
            @RECOGNITIONLATESTDATE datetime = null output,
            @RECOGNITIONLATESTTYPECODE tinyint = null output,
            @RECOGNITIONLATESTTYPE nvarchar(22) = null output,
            @RECOGNITIONLATESTAMOUNT money = null output,
            @RECOGNITIONLATESTCONSTITUENTNAME nvarchar(300) = null output,
            @CURRENTAPPUSERID uniqueidentifier
            ) as
                set nocount on;
                set @DATALOADED = 0;

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

                exec dbo.USP_GROUP_GIVINGSUMMARYGET
                    @ID,
                    @GROUPINCLUDESMEMBERGIVING = @GROUPINCLUDESMEMBERGIVING output,
                    @ISHOUSEHOLD = @ISHOUSEHOLD output,
                    @TOTALNUMBER = @TOTALNUMBER output,
                    @TOTALAMOUNT = @TOTALAMOUNT output,
                    @TOTALGROUPAMOUNT = @TOTALGROUPAMOUNT output,
                    @TOTALGROUPMEMBERAMOUNT = @TOTALGROUPMEMBERAMOUNT output,
                    @AVERAGEAMOUNT = @AVERAGEAMOUNT output,
                    @MOSTFREQUENTAMOUNT = @MOSTFREQUENTAMOUNT output,
                    @TOTALPLEDGEBALANCE = @TOTALPLEDGEBALANCE output,
                    @TOTALYEARS = @TOTALYEARS output,
                    @CONSECUTIVEYEARS = @CONSECUTIVEYEARS output,
                    @GIVENSINCEFISCALYEAR = @GIVENSINCEFISCALYEAR output,
                    @CURRENTAPPUSERID = @CURRENTAPPUSERID;

                --group member largest gift

                select top 1
                    @LARGESTCONSTITUENTNAME = (select NAME from dbo.CONSTITUENT where ID=R.CONSTITUENTID),
                    @LARGESTID = R.ID,
                    @LARGESTRECORDID = R.ID,
                    @LARGESTDATE = R.DATE,
                    @LARGESTTYPECODE = R.TRANSACTIONTYPECODE,
                    @LARGESTTYPE = R.TRANSACTIONTYPE,
                    @LARGESTAMOUNT = sum(RS.AMOUNT) - sum(coalesce(IWO.AMOUNT,0)),
                    @LARGESTPLEDGEBALANCE = case R.TRANSACTIONTYPECODE when 1 then dbo.UFN_PLEDGE_GETBALANCE(R.ID) 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.ID)
                                    order by
                                        ID
                                    for xml raw('ITEM'),type,elements,root('LARGESTSPLITS'),BINARY BASE64)
                from dbo.REVENUE R
                    inner join dbo.REVENUESPLIT RS on RS.REVENUEID = R.ID
                    inner join dbo.GROUPMEMBER GM on R.CONSTITUENTID = GM.MEMBERID
                    left join dbo.GROUPMEMBERDATERANGE GMDR on GMDR.GROUPMEMBERID = GM.ID
                    left join
                        (select
                            INSTALLMENTSPLIT.PLEDGEID, 
                            INSTALLMENTSPLIT.DESIGNATIONID, 
                            sum(IWO.AMOUNT) as AMOUNT
                        from dbo.INSTALLMENTSPLIT 
                        inner join dbo.INSTALLMENTSPLITWRITEOFF IWO on IWO.INSTALLMENTSPLITID = INSTALLMENTSPLIT.ID
                        group by INSTALLMENTSPLIT.PLEDGEID, INSTALLMENTSPLIT.DESIGNATIONID) IWO
                        on IWO.PLEDGEID = RS.REVENUEID and IWO.DESIGNATIONID = RS.DESIGNATIONID
                where @GROUPINCLUDESMEMBERGIVING = 1
                    and GM.GROUPID = @ID
                    and (R.TRANSACTIONTYPECODE = 1 or (R.TRANSACTIONTYPECODE = 0 and RS.APPLICATIONCODE in (0, 3))) --Pledge or Payment (Gift or Recurring gift payment)

                    and ((GMDR.DATEFROM is null and (GMDR.DATETO is null or GMDR.DATETO >= R.DATE))
                        or (GMDR.DATETO is null and (GMDR.DATEFROM is null or GMDR.DATEFROM <= R.DATE)) 
                        or (GMDR.DATEFROM <= R.DATE and GMDR.DATETO >= R.DATE))
                    and dbo.UFN_REVENUE_USERHASDESIGNATIONSITEACCESS(R.ID,@CURRENTAPPUSERID) = 1
                group by
                    R.ID, R.TRANSACTIONTYPE, R.TRANSACTIONTYPECODE, R.DATE, R.DATEADDED, R.CONSTITUENTID, IWO.AMOUNT
                order by
                    (sum(RS.AMOUNT) - sum(coalesce(IWO.AMOUNT,0))) desc, R.DATE desc, R.DATEADDED desc;

                -- group largest gift

                select top 1
                    @LARGESTCONSTITUENTNAME = (select NAME from dbo.CONSTITUENT where ID=R.CONSTITUENTID),
                    @LARGESTID = R.ID,
                    @LARGESTRECORDID = R.ID,
                    @LARGESTDATE = R.DATE,
                    @LARGESTTYPECODE = R.TRANSACTIONTYPECODE,
                    @LARGESTTYPE = R.TRANSACTIONTYPE,
                    @LARGESTAMOUNT = sum(RS.AMOUNT) - sum(coalesce(IWO.AMOUNT,0)),
                    @LARGESTPLEDGEBALANCE = case R.TRANSACTIONTYPECODE when 1 then dbo.UFN_PLEDGE_GETBALANCE(R.ID) 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.ID)
                                    order by
                                        ID
                                    for xml raw('ITEM'),type,elements,root('LARGESTSPLITS'),BINARY BASE64)
                from dbo.REVENUE R
                    inner join dbo.REVENUESPLIT RS on RS.REVENUEID = R.ID
                    left join
                        (select
                            INSTALLMENTSPLIT.PLEDGEID, 
                            INSTALLMENTSPLIT.DESIGNATIONID, 
                            sum(IWO.AMOUNT) as AMOUNT
                        from dbo.INSTALLMENTSPLIT 
                        inner join dbo.INSTALLMENTSPLITWRITEOFF IWO on IWO.INSTALLMENTSPLITID = INSTALLMENTSPLIT.ID
                        group by INSTALLMENTSPLIT.PLEDGEID, INSTALLMENTSPLIT.DESIGNATIONID) IWO
                        on IWO.PLEDGEID = RS.REVENUEID and IWO.DESIGNATIONID = RS.DESIGNATIONID
                where R.CONSTITUENTID = @ID
                    and (R.TRANSACTIONTYPECODE = 1 or (R.TRANSACTIONTYPECODE = 0 and RS.APPLICATIONCODE in (0, 3))) --Pledge or Payment (Gift or Recurring gift payment)

                    and dbo.UFN_REVENUE_USERHASDESIGNATIONSITEACCESS(R.ID,@CURRENTAPPUSERID) = 1
                group by
                    R.ID, R.TRANSACTIONTYPE, R.TRANSACTIONTYPECODE, R.DATE, R.DATEADDED, R.CONSTITUENTID, IWO.AMOUNT
                having (@LARGESTAMOUNT is null or (sum(RS.AMOUNT) - sum(coalesce(IWO.AMOUNT, 0))) > @LARGESTAMOUNT)
                order by
                    (sum(RS.AMOUNT) - sum(coalesce(IWO.AMOUNT,0))) desc, R.DATE desc, R.DATEADDED desc;

                -- first group member gift

                select top 1
                    @FIRSTCONSTITUENTNAME = (select NAME from dbo.CONSTITUENT where ID=R.CONSTITUENTID),
                    @FIRSTID = R.ID,
                    @FIRSTRECORDID = R.ID,
                    @FIRSTDATE = R.DATE,
                    @FIRSTTYPECODE = R.TRANSACTIONTYPECODE,
                    @FIRSTTYPE = R.TRANSACTIONTYPE,
                    @FIRSTAMOUNT = sum(RS.AMOUNT) - sum(coalesce(IWO.AMOUNT,0)),
                    @FIRSTPLEDGEBALANCE = case R.TRANSACTIONTYPECODE when 1 then dbo.UFN_PLEDGE_GETBALANCE(R.ID) 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.ID)
                                    order by
                                        ID
                                    for xml raw('ITEM'),type,elements,root('FIRSTSPLITS'),BINARY BASE64)
                from dbo.REVENUE R
                    inner join dbo.REVENUESPLIT RS on RS.REVENUEID = R.ID
                    inner join dbo.GROUPMEMBER GM on R.CONSTITUENTID = GM.MEMBERID
                    left join dbo.GROUPMEMBERDATERANGE GMDR on GMDR.GROUPMEMBERID = GM.ID
                    left join
                        (select
                            INSTALLMENTSPLIT.PLEDGEID, 
                            INSTALLMENTSPLIT.DESIGNATIONID, 
                            sum(IWO.AMOUNT) as AMOUNT
                        from dbo.INSTALLMENTSPLIT 
                        inner join dbo.INSTALLMENTSPLITWRITEOFF IWO on IWO.INSTALLMENTSPLITID = INSTALLMENTSPLIT.ID
                        group by INSTALLMENTSPLIT.PLEDGEID, INSTALLMENTSPLIT.DESIGNATIONID) IWO
                        on IWO.PLEDGEID = RS.REVENUEID and IWO.DESIGNATIONID = RS.DESIGNATIONID
                where @GROUPINCLUDESMEMBERGIVING = 1
                    and GM.GROUPID = @ID
                    and (R.TRANSACTIONTYPECODE = 1 or (R.TRANSACTIONTYPECODE = 0 and RS.APPLICATIONCODE in (0, 3))) --Pledge or Payment (Gift or Recurring gift payment)

                    and ((GMDR.DATEFROM is null and (GMDR.DATETO is null or GMDR.DATETO >= R.DATE))
                        or (GMDR.DATETO is null and (GMDR.DATEFROM is null or GMDR.DATEFROM <= R.DATE)) 
                        or (GMDR.DATEFROM <= R.DATE and GMDR.DATETO >= R.DATE))
                    and dbo.UFN_REVENUE_USERHASDESIGNATIONSITEACCESS(R.ID,@CURRENTAPPUSERID) = 1
                group by
                    R.ID, R.TRANSACTIONTYPE, R.TRANSACTIONTYPECODE, R.DATE, R.DATEADDED, R.CONSTITUENTID, IWO.AMOUNT
                order by
                    R.DATE asc, R.DATEADDED asc;

                -- first group gift

                select top 1
                    @FIRSTCONSTITUENTNAME = (select NAME from dbo.CONSTITUENT where ID=R.CONSTITUENTID),
                    @FIRSTID = R.ID,
                    @FIRSTRECORDID = R.ID,
                    @FIRSTDATE = R.DATE,
                    @FIRSTTYPECODE = R.TRANSACTIONTYPECODE,
                    @FIRSTTYPE = R.TRANSACTIONTYPE,
                    @FIRSTAMOUNT = sum(RS.AMOUNT) - sum(coalesce(IWO.AMOUNT,0)),
                    @FIRSTPLEDGEBALANCE = case R.TRANSACTIONTYPECODE when 1 then dbo.UFN_PLEDGE_GETBALANCE(R.ID) 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.ID)
                                    order by
                                        ID
                                    for xml raw('ITEM'),type,elements,root('FIRSTSPLITS'),BINARY BASE64)
                from dbo.REVENUE R
                    inner join dbo.REVENUESPLIT RS on RS.REVENUEID = R.ID
                    left join
                        (select
                            INSTALLMENTSPLIT.PLEDGEID, 
                            INSTALLMENTSPLIT.DESIGNATIONID, 
                            sum(IWO.AMOUNT) as AMOUNT
                        from dbo.INSTALLMENTSPLIT 
                        inner join dbo.INSTALLMENTSPLITWRITEOFF IWO on IWO.INSTALLMENTSPLITID = INSTALLMENTSPLIT.ID
                        group by INSTALLMENTSPLIT.PLEDGEID, INSTALLMENTSPLIT.DESIGNATIONID) IWO
                        on IWO.PLEDGEID = RS.REVENUEID and IWO.DESIGNATIONID = RS.DESIGNATIONID
                where R.CONSTITUENTID = @ID
                    and (R.TRANSACTIONTYPECODE = 1 or (R.TRANSACTIONTYPECODE = 0 and RS.APPLICATIONCODE in (0, 3))) --Pledge or Payment (Gift or Recurring gift payment)

                    and (@FIRSTDATE is null or R.DATE < @FIRSTDATE)
                    and dbo.UFN_REVENUE_USERHASDESIGNATIONSITEACCESS(R.ID,@CURRENTAPPUSERID) = 1
                group by
                    R.ID, R.TRANSACTIONTYPE, R.TRANSACTIONTYPECODE, R.DATE, R.DATEADDED, R.CONSTITUENTID, IWO.AMOUNT
                order by
                    R.DATE asc, R.DATEADDED asc;

                -- group member latest gift

                select top 1
                    @LATESTCONSTITUENTNAME = (select NAME from dbo.CONSTITUENT where ID=R.CONSTITUENTID),
                    @LATESTID = R.ID,
                    @LATESTRECORDID = R.ID,
                    @LATESTDATE = R.DATE,
                    @LATESTTYPECODE = R.TRANSACTIONTYPECODE,
                    @LATESTTYPE = R.TRANSACTIONTYPE,
                    @LATESTAMOUNT = sum(RS.AMOUNT) - sum(coalesce(IWO.AMOUNT,0)),
                    @LATESTPLEDGEBALANCE = case R.TRANSACTIONTYPECODE when 1 then dbo.UFN_PLEDGE_GETBALANCE(R.ID) 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.ID)
                                    order by
                                        ID
                                    for xml raw('ITEM'),type,elements,root('LATESTSPLITS'),BINARY BASE64)
                from dbo.REVENUE R
                    inner join dbo.REVENUESPLIT RS on RS.REVENUEID = R.ID
                    inner join dbo.GROUPMEMBER GM on R.CONSTITUENTID = GM.MEMBERID
                    left join dbo.GROUPMEMBERDATERANGE GMDR on GMDR.GROUPMEMBERID = GM.ID
                    left join
                        (select
                            INSTALLMENTSPLIT.PLEDGEID, 
                            INSTALLMENTSPLIT.DESIGNATIONID, 
                            sum(IWO.AMOUNT) as AMOUNT
                        from dbo.INSTALLMENTSPLIT 
                        inner join dbo.INSTALLMENTSPLITWRITEOFF IWO on IWO.INSTALLMENTSPLITID = INSTALLMENTSPLIT.ID
                        group by INSTALLMENTSPLIT.PLEDGEID, INSTALLMENTSPLIT.DESIGNATIONID) IWO
                        on IWO.PLEDGEID = RS.REVENUEID and IWO.DESIGNATIONID = RS.DESIGNATIONID
                where @GROUPINCLUDESMEMBERGIVING = 1
                    and GM.GROUPID = @ID
                    and (R.TRANSACTIONTYPECODE = 1 or (R.TRANSACTIONTYPECODE = 0 and RS.APPLICATIONCODE in (0, 3))) --Pledge or Payment (Gift or Recurring gift payment)

                    and ((GMDR.DATEFROM is null and (GMDR.DATETO is null or GMDR.DATETO >= R.DATE))
                        or (GMDR.DATETO is null and (GMDR.DATEFROM is null or GMDR.DATEFROM <= R.DATE)) 
                        or (GMDR.DATEFROM <= R.DATE and GMDR.DATETO >= R.DATE))
                    and dbo.UFN_REVENUE_USERHASDESIGNATIONSITEACCESS(R.ID,@CURRENTAPPUSERID) = 1
                group by
                    R.ID, R.TRANSACTIONTYPE, R.TRANSACTIONTYPECODE, R.DATE, R.DATEADDED, R.CONSTITUENTID, IWO.AMOUNT
                order by
                    R.DATE desc, R.DATEADDED desc;

                -- group latest gift

                select top 1
                    @LATESTCONSTITUENTNAME = (select NAME from dbo.CONSTITUENT where ID=R.CONSTITUENTID),
                    @LATESTID = R.ID,
                    @LATESTRECORDID = R.ID,
                    @LATESTDATE = R.DATE,
                    @LATESTTYPECODE = R.TRANSACTIONTYPECODE,
                    @LATESTTYPE = R.TRANSACTIONTYPE,
                    @LATESTAMOUNT = sum(RS.AMOUNT) - sum(coalesce(IWO.AMOUNT,0)),
                    @LATESTPLEDGEBALANCE = case R.TRANSACTIONTYPECODE when 1 then dbo.UFN_PLEDGE_GETBALANCE(R.ID) 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.ID)
                                    order by
                                        ID
                                    for xml raw('ITEM'),type,elements,root('LATESTSPLITS'),BINARY BASE64)
                from dbo.REVENUE R
                    inner join dbo.REVENUESPLIT RS on RS.REVENUEID = R.ID
                    left join
                        (select
                            INSTALLMENTSPLIT.PLEDGEID, 
                            INSTALLMENTSPLIT.DESIGNATIONID, 
                            sum(IWO.AMOUNT) as AMOUNT
                        from dbo.INSTALLMENTSPLIT 
                        inner join dbo.INSTALLMENTSPLITWRITEOFF IWO on IWO.INSTALLMENTSPLITID = INSTALLMENTSPLIT.ID
                        group by INSTALLMENTSPLIT.PLEDGEID, INSTALLMENTSPLIT.DESIGNATIONID) IWO
                        on IWO.PLEDGEID = RS.REVENUEID and IWO.DESIGNATIONID = RS.DESIGNATIONID
                where R.CONSTITUENTID = @ID
                    and (@LATESTDATE is null or R.DATE > @LATESTDATE)
                    and (R.TRANSACTIONTYPECODE = 1 or (R.TRANSACTIONTYPECODE = 0 and RS.APPLICATIONCODE in (0, 3))) --Pledge or Payment (Gift or Recurring gift payment)

                    and dbo.UFN_REVENUE_USERHASDESIGNATIONSITEACCESS(R.ID,@CURRENTAPPUSERID) = 1
                group by
                    R.ID, R.TRANSACTIONTYPE, R.TRANSACTIONTYPECODE, R.DATE, R.DATEADDED, R.CONSTITUENTID, IWO.AMOUNT
                order by
                    R.DATE desc, R.DATEADDED desc;


                -- Handle recognitions

                exec dbo.USP_GROUP_RECOGNITIONSUMMARYGET
                    @ID,
                    @RECOGNITIONTOTALNUMBER = @RECOGNITIONTOTALNUMBER output,
                    @RECOGNITIONTOTALAMOUNT = @RECOGNITIONTOTALAMOUNT output,
                    @RECOGNITIONTOTALGROUPAMOUNT = @RECOGNITIONTOTALGROUPAMOUNT output,
                    @RECOGNITIONTOTALGROUPMEMBERAMOUNT = @RECOGNITIONTOTALGROUPMEMBERAMOUNT output,
                    @RECOGNITIONAVERAGEAMOUNT = @RECOGNITIONAVERAGEAMOUNT output,
                    @RECOGNITIONMOSTFREQUENTAMOUNT = @RECOGNITIONMOSTFREQUENTAMOUNT output,
                    @RECOGNITIONTOTALYEARS = @RECOGNITIONTOTALYEARS output,
                    @RECOGNITIONCONSECUTIVEYEARS = @RECOGNITIONCONSECUTIVEYEARS output,
                    @RECOGNITIONGIVENSINCEFISCALYEAR = @RECOGNITIONGIVENSINCEFISCALYEAR output,
                    @CURRENTAPPUSERID = @CURRENTAPPUSERID;

                --group member largest gift

                select top 1
                    @RECOGNITIONLARGESTCONSTITUENTNAME = (select NAME from dbo.CONSTITUENT where ID=RR.CONSTITUENTID),
                    @RECOGNITIONLARGESTRECORDID = R.ID,
                    @RECOGNITIONLARGESTDATE = RR.EFFECTIVEDATE,
                    @RECOGNITIONLARGESTTYPECODE = R.TRANSACTIONTYPECODE,
                    @RECOGNITIONLARGESTTYPE = R.TRANSACTIONTYPE,
                    @RECOGNITIONLARGESTAMOUNT = RR.AMOUNT
                from dbo.REVENUERECOGNITION RR
                    inner join dbo.REVENUESPLIT RS on RR.REVENUESPLITID = RS.ID
                    inner join dbo.REVENUE 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
                where @GROUPINCLUDESMEMBERGIVING = 1
                    and GM.GROUPID = @ID
                    and (R.TRANSACTIONTYPECODE = 1 or (R.TRANSACTIONTYPECODE = 0 and RS.APPLICATIONCODE in (0, 3))) --Pledge or Payment (Gift or Recurring gift payment)

                    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 dbo.UFN_REVENUE_USERHASDESIGNATIONSITEACCESS(R.ID,@CURRENTAPPUSERID) = 1
                order by
                    RR.AMOUNT desc, RR.EFFECTIVEDATE desc, RR.DATEADDED desc;

                -- group largest recognition

                select top 1
                    @RECOGNITIONLARGESTCONSTITUENTNAME = (select NAME from dbo.CONSTITUENT where ID=RR.CONSTITUENTID),
                    @RECOGNITIONLARGESTRECORDID = R.ID,
                    @RECOGNITIONLARGESTDATE = RR.EFFECTIVEDATE,
                    @RECOGNITIONLARGESTTYPECODE = R.TRANSACTIONTYPECODE,
                    @RECOGNITIONLARGESTTYPE = R.TRANSACTIONTYPE,
                    @RECOGNITIONLARGESTAMOUNT = RR.AMOUNT
                from dbo.REVENUERECOGNITION RR
                    inner join dbo.REVENUESPLIT RS on RR.REVENUESPLITID = RS.ID
                    inner join dbo.REVENUE R on RS.REVENUEID = R.ID
                where RR.CONSTITUENTID = @ID
                    and (R.TRANSACTIONTYPECODE = 1 or (R.TRANSACTIONTYPECODE = 0 and RS.APPLICATIONCODE in (0, 3))) --Pledge or Payment (Gift or Recurring gift payment)

                    and (@RECOGNITIONLARGESTAMOUNT is null or RR.AMOUNT > @RECOGNITIONLARGESTAMOUNT)
                    and dbo.UFN_REVENUE_USERHASDESIGNATIONSITEACCESS(R.ID,@CURRENTAPPUSERID) = 1
                order by
                    RR.AMOUNT desc, RR.EFFECTIVEDATE desc, RR.DATEADDED desc;

                -- first group member recognition

                select top 1
                    @RECOGNITIONFIRSTCONSTITUENTNAME = (select NAME from dbo.CONSTITUENT where ID=RR.CONSTITUENTID),
                    @RECOGNITIONFIRSTRECORDID = R.ID,
                    @RECOGNITIONFIRSTDATE = RR.EFFECTIVEDATE,
                    @RECOGNITIONFIRSTTYPECODE = R.TRANSACTIONTYPECODE,
                    @RECOGNITIONFIRSTTYPE = R.TRANSACTIONTYPE,
                    @RECOGNITIONFIRSTAMOUNT = RR.AMOUNT
                from dbo.REVENUERECOGNITION RR
                    inner join dbo.REVENUESPLIT RS on RR.REVENUESPLITID = RS.ID
                    inner join dbo.REVENUE 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
                where @GROUPINCLUDESMEMBERGIVING = 1
                    and GM.GROUPID = @ID
                    and (R.TRANSACTIONTYPECODE = 1 or (R.TRANSACTIONTYPECODE = 0 and RS.APPLICATIONCODE in (0, 3))) --Pledge or Payment (Gift or Recurring gift payment)

                    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 dbo.UFN_REVENUE_USERHASDESIGNATIONSITEACCESS(R.ID,@CURRENTAPPUSERID) = 1
                order by
                    RR.EFFECTIVEDATE asc, RR.DATEADDED asc;

                -- first group recognition

                select top 1
                    @RECOGNITIONFIRSTCONSTITUENTNAME = (select NAME from dbo.CONSTITUENT where ID=RR.CONSTITUENTID),
                    @RECOGNITIONFIRSTRECORDID = R.ID,
                    @RECOGNITIONFIRSTDATE = RR.EFFECTIVEDATE,
                    @RECOGNITIONFIRSTTYPECODE = R.TRANSACTIONTYPECODE,
                    @RECOGNITIONFIRSTTYPE = R.TRANSACTIONTYPE,
                    @RECOGNITIONFIRSTAMOUNT = RR.AMOUNT
                from dbo.REVENUERECOGNITION RR
                    inner join dbo.REVENUESPLIT RS on RR.REVENUESPLITID = RS.ID
                    inner join dbo.REVENUE R on RS.REVENUEID = R.ID
                where RR.CONSTITUENTID = @ID
                    and (R.TRANSACTIONTYPECODE = 1 or (R.TRANSACTIONTYPECODE = 0 and RS.APPLICATIONCODE in (0, 3))) --Pledge or Payment (Gift or Recurring gift payment)

                    and (@RECOGNITIONFIRSTDATE is null or RR.EFFECTIVEDATE < @RECOGNITIONFIRSTDATE)
                    and dbo.UFN_REVENUE_USERHASDESIGNATIONSITEACCESS(R.ID,@CURRENTAPPUSERID) = 1
                order by
                    RR.EFFECTIVEDATE asc, RR.DATEADDED asc;

                -- group member latest recognition

                select top 1
                    @RECOGNITIONLATESTCONSTITUENTNAME = (select NAME from dbo.CONSTITUENT where ID=RR.CONSTITUENTID),
                    @RECOGNITIONLATESTRECORDID = R.ID,
                    @RECOGNITIONLATESTDATE = RR.EFFECTIVEDATE,
                    @RECOGNITIONLATESTTYPECODE = R.TRANSACTIONTYPECODE,
                    @RECOGNITIONLATESTTYPE = R.TRANSACTIONTYPE,
                    @RECOGNITIONLATESTAMOUNT = RR.AMOUNT
                from dbo.REVENUERECOGNITION RR
                    inner join dbo.REVENUESPLIT RS on RR.REVENUESPLITID = RS.ID
                    inner join dbo.REVENUE 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
                where @GROUPINCLUDESMEMBERGIVING = 1
                    and GM.GROUPID = @ID
                    and (R.TRANSACTIONTYPECODE = 1 or (R.TRANSACTIONTYPECODE = 0 and RS.APPLICATIONCODE in (0, 3))) --Pledge or Payment (Gift or Recurring gift payment)

                    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 dbo.UFN_REVENUE_USERHASDESIGNATIONSITEACCESS(R.ID,@CURRENTAPPUSERID) = 1
                order by
                    RR.EFFECTIVEDATE desc, RR.DATEADDED desc;

                -- group latest recognition

                select top 1
                    @RECOGNITIONLATESTCONSTITUENTNAME = (select NAME from dbo.CONSTITUENT where ID=RR.CONSTITUENTID),
                    @RECOGNITIONLATESTRECORDID = R.ID,
                    @RECOGNITIONLATESTDATE = RR.EFFECTIVEDATE,
                    @RECOGNITIONLATESTTYPECODE = R.TRANSACTIONTYPECODE,
                    @RECOGNITIONLATESTTYPE = R.TRANSACTIONTYPE,
                    @RECOGNITIONLATESTAMOUNT = RR.AMOUNT
                from dbo.REVENUERECOGNITION RR
                    inner join dbo.REVENUESPLIT RS on RR.REVENUESPLITID = RS.ID
                    inner join dbo.REVENUE R on RS.REVENUEID = R.ID
                where RR.CONSTITUENTID = @ID
                    and (@RECOGNITIONLATESTDATE is null or RR.EFFECTIVEDATE > @RECOGNITIONLATESTDATE)
                    and (R.TRANSACTIONTYPECODE = 1 or (R.TRANSACTIONTYPECODE = 0 and RS.APPLICATIONCODE in (0, 3))) --Pledge or Payment (Gift or Recurring gift payment)

                    and dbo.UFN_REVENUE_USERHASDESIGNATIONSITEACCESS(R.ID,@CURRENTAPPUSERID) = 1
                order by
                    RR.EFFECTIVEDATE desc, RR.DATEADDED desc;

                return 0;