UFN_GROUP_RECOGNITIONCREDITS

            CREATE function dbo.UFN_GROUP_RECOGNITIONCREDITS
            (
                @GROUPID uniqueidentifier,
                @TYPECODES xml,
                @DESIGNATIONS xml,
                @CAMPAIGNS xml,
                @RECOGNITIONTYPECODES xml,
                @FILTERUNCERTAINGIFTSFORHISTORICALMEMBERS bit,
                @ASOF datetime = null,
                @INCLUDEGROUPSWITHNORECOGNITIONS bit,
                @GROUPTYPECODE tinyint,
                @APPLICATIONTYPECODES xml,
                @REVENUESELECTIONID uniqueidentifier
            )
            returns @R table
            (
                GROUPID uniqueidentifier,
                GROUPTYPECODE tinyint,
                GROUPMEMBERID uniqueidentifier,
                ISGROUP bit,
                GROUPMEMBERSHIPSTARTDATE datetime,
                GROUPMEMBERSHIPENDDATE datetime,
                GROUPMEMBERSHIPISHISTORICAL bit,
                RECOGNITIONID uniqueidentifier,
                REVENUEID uniqueidentifier,
                REVENUESPLITID uniqueidentifier,
                REVENUEDATE datetime,
                REVENUEDATEADDED datetime,
                REVENUECONSTITUENTID uniqueidentifier,
                RECOGNITIONDATE datetime,
                REVENUERECOGNITIONTYPECODEID uniqueidentifier,
                RECOGNITIONAMOUNT money,
                RECOGNITIONDATEADDED datetime,
                BASECURRENCYID uniqueidentifier,
                ORGANIZATIONAMOUNT money
            )
            as
            begin
                declare @TODAY datetime;
                set @TODAY = getdate();

                declare @CONSTITUENTFILTER table(ID uniqueidentifier primary key, INCLUDESMEMBERGIVING bit not null);
                declare @SPLITTYPECODESFILTER table (TYPECODE tinyint primary key);
                declare @DESIGNATIONSFILTER table (DESIGNATIONID uniqueidentifier primary key);
                declare @CAMPAIGNSFILTER table (CAMPAIGNID uniqueidentifier primary key);
                declare @RECOGNITIONTYPECODESFILTER table (RECOGNITIONTYPECODEID uniqueidentifier primary key);
                declare @APPLICATIONTYPECODESFILTER table (APPLICATIONCODE tinyint, TRANSACTIONTYPECODE tinyint);

                if @TYPECODES is not null
                begin
                    insert into @SPLITTYPECODESFILTER(TYPECODE)
                        select distinct T.c.value('(REVENUETYPECODES)[1]','tinyint')
                        from @TYPECODES.nodes('/REVENUETYPECODES/ITEM') T(c);

                    if not exists (select 1 from @SPLITTYPECODESFILTER)
                        set @TYPECODES = null;
                end

                if @APPLICATIONTYPECODES is not null
                begin
                    insert into @APPLICATIONTYPECODESFILTER(APPLICATIONCODE, TRANSACTIONTYPECODE)
                        select
                            T.c.value('(APPLICATION)[1]','tinyint'),
                            T.c.value('(TRANSACTIONTYPE)[1]','tinyint')
                    from @APPLICATIONTYPECODES.nodes('/SELECTIONS/ITEM') T(c)
                    where T.c.value('(TRANSACTIONTYPE)[1]','tinyint') is not null;

                    if not exists (select 1 from @APPLICATIONTYPECODESFILTER)
                        set @APPLICATIONTYPECODES = null;
                end

                if @DESIGNATIONS is not null
                begin
                    insert into @DESIGNATIONSFILTER(DESIGNATIONID)
                        select T.c.value('(ID)[1]','uniqueidentifier')
                        from @DESIGNATIONS.nodes('/DESIGNATIONS/ITEM') T(c);

                        if not exists (select 1 from @DESIGNATIONSFILTER)
                        set @DESIGNATIONS = null;
                end

                if @CAMPAIGNS is not null
                begin
                    insert into @CAMPAIGNSFILTER(CAMPAIGNID)
                        select T.c.value('(ID)[1]','uniqueidentifier')
                        from @CAMPAIGNS.nodes('/CAMPAIGNS/ITEM') T(c);

                    if not exists (select 1 from @CAMPAIGNSFILTER)
                        set @CAMPAIGNS = null;
                end

                if @RECOGNITIONTYPECODES is not null
                begin
                    insert into @RECOGNITIONTYPECODESFILTER(RECOGNITIONTYPECODEID)
                        select T.c.value('(RECOGNITIONTYPECODEID)[1]','uniqueidentifier')
                        from @RECOGNITIONTYPECODES.nodes('/RECOGNITIONTYPES/ITEM') T(c);

                    if not exists (select 1 from @RECOGNITIONTYPECODESFILTER)
                        set @RECOGNITIONTYPECODES = null;
                end

                --JamesWill WI60704. There are too many complexities to use the @ASOF filter, so this will simply return all gifts

                --(Complexities include: want to return the same gifts for all household members; want to return households without revenue;

                --want to return individuals without households; etc. I was running into issues where some gifts were not getting counted

                --because of the @ASOF and things were inconsistent. This will be a little slower, but it will also be correct.

                insert into @CONSTITUENTFILTER(ID, INCLUDESMEMBERGIVING) 
                    select    CONSTITUENT.ID,
                            case when GROUPDATA.GROUPTYPECODE = 0 then 1 else coalesce(GROUPTYPE.INCLUDEMEMBERGIVING, 0) end
                    from dbo.CONSTITUENT 
                    inner join dbo.GROUPDATA on GROUPDATA.ID = CONSTITUENT.ID and (@GROUPTYPECODE is null or GROUPDATA.GROUPTYPECODE = @GROUPTYPECODE)
                    left join dbo.GROUPTYPE on GROUPTYPE.ID = GROUPDATA.GROUPTYPEID
                    where CONSTITUENT.ISGROUP = 1
                        and @GROUPID is null or CONSTITUENT.ID = @GROUPID;

                with [CTE_MEMBERS] as
                (
                    select 
                        CONSTITUENT.ID as [GROUPID],
                        GROUPMEMBER.MEMBERID as [CONSTITUENTID],
                        0 as [ISGROUP],
                        GROUPMEMBERDATERANGE.DATEFROM,
                        GROUPMEMBERDATERANGE.DATETO,
                        case when GROUPMEMBERDATERANGE.DATETO is not null and GROUPMEMBERDATERANGE.DATETO < @TODAY then 1 else 0 end as [ISHISTORICAL],
                        GROUPDATA.GROUPTYPECODE
                    from @CONSTITUENTFILTER as [FILTER]
                    inner join dbo.CONSTITUENT on CONSTITUENT.ID = [FILTER].ID
                    inner join dbo.GROUPMEMBER on GROUPMEMBER.GROUPID = CONSTITUENT.ID and [FILTER].INCLUDESMEMBERGIVING = 1
                    inner join dbo.GROUPDATA on GROUPDATA.ID = GROUPMEMBER.GROUPID
                    left join dbo.GROUPMEMBERDATERANGE on GROUPMEMBERDATERANGE.GROUPMEMBERID = GROUPMEMBER.ID 

                    union    /*Also include giving by the group itself*/
                    select
                        CONSTITUENT.ID as [GROUPID],
                        CONSTITUENT.ID as [CONSTITUENTID],
                        1 as [ISGROUP],
                        null as [DATEFROM],
                        null as [DATETO],
                        0 as [ISHISTORICAL],
                        GROUPDATA.GROUPTYPECODE
                    from @CONSTITUENTFILTER as [FILTER]
                    inner join dbo.CONSTITUENT on CONSTITUENT.ID = [FILTER].ID
                    inner join dbo.GROUPDATA on GROUPDATA.ID = CONSTITUENT.ID

                ),
                [CTE_RECOGNITION] as
                (
                    select REVENUE.ID as [REVENUEID], cast(REVENUE.DATE as datetime) as [REVENUEDATE], REVENUE.CONSTITUENTID as [REVENUECONSTITUENTID], REVENUESPLIT.ID as [REVENUESPLITID],
                            REVENUERECOGNITION.ID as [RECOGNITIONID], REVENUERECOGNITION.DATEADDED, REVENUERECOGNITION.EFFECTIVEDATE, 
                            REVENUERECOGNITION.CONSTITUENTID as [RECOGNITIONCONSTITUENTID], REVENUERECOGNITIONTYPECODEID, REVENUERECOGNITION.AMOUNT, 
                            REVENUE.DATEADDED as [REVENUEDATEADDED], REVENUERECOGNITION.BASECURRENCYID, REVENUERECOGNITION.ORGANIZATIONAMOUNT
                    from dbo.FINANCIALTRANSACTION REVENUE 
                    inner join dbo.FINANCIALTRANSACTIONLINEITEM REVENUESPLIT on REVENUESPLIT.FINANCIALTRANSACTIONID = REVENUE.ID
                    inner join dbo.REVENUESPLIT_EXT on REVENUESPLIT_EXT.ID = REVENUESPLIT.ID
                    inner join dbo.UFN_RECOGNITIONCREDIT_GETRECOGNITIONS(1, null, null) REVENUERECOGNITION on REVENUERECOGNITION.REVENUESPLITID = REVENUESPLIT.ID
                    where 
                            (@TYPECODES is null or REVENUESPLIT_EXT.TYPECODE in (select [SPLITTYPECODESFILTER].TYPECODE from @SPLITTYPECODESFILTER as [SPLITTYPECODESFILTER]))
                        and (
                                @APPLICATIONTYPECODES is null or REVENUESPLIT.ID in        (
                                                                                        select [FILTERSPLIT].ID 
                                                                                        from dbo.FINANCIALTRANSACTIONLINEITEM as [FILTERSPLIT]
                                                                                        inner join dbo.REVENUESPLIT_EXT as [FILTERSPLIT2] on [FILTERSPLIT2].ID = [FILTERSPLIT].ID
                                                                                        inner join dbo.FINANCIALTRANSACTION as [FILTERREVENUE] on [FILTERREVENUE].ID = [FILTERSPLIT].FINANCIALTRANSACTIONID
                                                                                        inner join @APPLICATIONTYPECODESFILTER as [FILTER] on [FILTER].APPLICATIONCODE = [FILTERSPLIT2].APPLICATIONCODE
                                                                                                                                            and [FILTER].TRANSACTIONTYPECODE = [FILTERREVENUE].TYPECODE
                                                                                        )
                            )
                        and    (@DESIGNATIONS is null or REVENUERECOGNITION.DESIGNATIONID in (select [DESIGNATIONSFILTER].DESIGNATIONID from @DESIGNATIONSFILTER as [DESIGNATIONSFILTER]))
                        and (@CAMPAIGNS is null or REVENUESPLIT.ID in (select REVENUESPLITCAMPAIGN.REVENUESPLITID from dbo.REVENUESPLITCAMPAIGN inner join @CAMPAIGNSFILTER as [CAMPAIGNSFILTER] on [CAMPAIGNSFILTER].CAMPAIGNID = REVENUESPLITCAMPAIGN.CAMPAIGNID))
                        and (@RECOGNITIONTYPECODES is null or REVENUERECOGNITION.REVENUERECOGNITIONTYPECODEID in (select [FILTER].RECOGNITIONTYPECODEID from @RECOGNITIONTYPECODESFILTER as [FILTER]))
                        and (@REVENUESELECTIONID is null or REVENUESPLIT.ID in (select ID from dbo.UFN_IDSETREADER_GETRESULTS_GUID(@REVENUESELECTIONID)))
                )
                insert into @R
                    select
                        [CTE_MEMBERS].GROUPID,
                        [CTE_MEMBERS].GROUPTYPECODE,
                        [CTE_MEMBERS].CONSTITUENTID as [GROUPMEMBERID],
                        [CTE_MEMBERS].ISGROUP,
                        [CTE_MEMBERS].DATEFROM as [GROUPMEMBERSHIPSTARTDATE],
                        [CTE_MEMBERS].DATETO as [GROUPMEMBERSHIPENDDATE],
                        [CTE_MEMBERS].ISHISTORICAL as [GROUPMEMBERSHIPISHISTORICAL],
                        [CTE_RECOGNITION].RECOGNITIONID,
                        [CTE_RECOGNITION].REVENUEID, 
                        [CTE_RECOGNITION].REVENUESPLITID,
                        [CTE_RECOGNITION].REVENUEDATE,
                        [CTE_RECOGNITION].REVENUEDATEADDED,
                        [CTE_RECOGNITION].REVENUECONSTITUENTID,
                        [CTE_RECOGNITION].EFFECTIVEDATE as [RECOGNITIONDATE],
                        [CTE_RECOGNITION].REVENUERECOGNITIONTYPECODEID,
                        [CTE_RECOGNITION].AMOUNT as [RECOGNITIONAMOUNT],
                        [CTE_RECOGNITION].DATEADDED as [RECOGNITIONDATEADDED],
                        [CTE_RECOGNITION].BASECURRENCYID,
                        [CTE_RECOGNITION].ORGANIZATIONAMOUNT
                    from [CTE_RECOGNITION]
                    inner join [CTE_MEMBERS] on [CTE_MEMBERS].CONSTITUENTID = [CTE_RECOGNITION].RECOGNITIONCONSTITUENTID
                    where 
                        [CTE_MEMBERS].ISGROUP = 1
                        or
                        [CTE_MEMBERS].ISHISTORICAL = 0
                        or 
                        ([CTE_MEMBERS].ISHISTORICAL = 1 and (@FILTERUNCERTAINGIFTSFORHISTORICALMEMBERS = 0 or ([CTE_RECOGNITION].EFFECTIVEDATE <= [CTE_MEMBERS].DATETO)))

                    union --Include individuals who are not in the selected groups

                    select
                        null,
                        null,
                        CONSTITUENT.ID,
                        0,
                        null,
                        null,
                        0,
                        [CTE_RECOGNITION].RECOGNITIONID,
                        [CTE_RECOGNITION].REVENUEID, 
                        [CTE_RECOGNITION].REVENUESPLITID,
                        [CTE_RECOGNITION].REVENUEDATE,
                        [CTE_RECOGNITION].REVENUEDATEADDED,
                        [CTE_RECOGNITION].REVENUECONSTITUENTID,
                        [CTE_RECOGNITION].EFFECTIVEDATE as [RECOGNITIONDATE],
                        [CTE_RECOGNITION].REVENUERECOGNITIONTYPECODEID,
                        [CTE_RECOGNITION].AMOUNT as [RECOGNITIONAMOUNT],
                        [CTE_RECOGNITION].DATEADDED as [RECOGNITIONDATEADDED],
                        [CTE_RECOGNITION].BASECURRENCYID,
                        [CTE_RECOGNITION].ORGANIZATIONAMOUNT            
                    from [CTE_RECOGNITION]
                    inner join dbo.CONSTITUENT on CONSTITUENT.ID = [CTE_RECOGNITION].RECOGNITIONCONSTITUENTID
                    where CONSTITUENT.ID not in (select ID from dbo.GROUPDATA)
                        and CONSTITUENT.ID not in (select CONSTITUENTID from [CTE_MEMBERS])
                        and @GROUPID is null;

                if @INCLUDEGROUPSWITHNORECOGNITIONS = 1
                begin
                    insert into @R
                    select
                        CONSTITUENT.ID as [GROUPID],
                        GROUPDATA.GROUPTYPECODE,
                        CONSTITUENT.ID as [GROUPMEMBERID],
                        1 as [ISGROUP],
                        null as [GROUPMEMBERSHIPSTARTDATE],
                        null as [GROUPMEMBERSHIPENDDATE],
                        0 as [ISHISTORICAL],
                        null as [RECOGNITIONID],
                        null as [REVENUEID],
                        null as [REVENUESPLITID],
                        null as [REVENUEDATE],
                        null as [REVENUEDATEADDED],
                        null as [REVENUECONSTITUENTID],
                        null as [RECOGNITIONDATE],
                        null as [REVENUERECOGNITIONTYPECODEID],
                        null as [RECOGNITIONAMOUNT],
                        null as [RECOGNITIONDATEADDED],
                        null as [BASECURRENCYID],
                        null as [ORGANIZATIONAMOUNT]
                    from dbo.CONSTITUENT
                    inner join dbo.GROUPDATA on GROUPDATA.ID = CONSTITUENT.ID
                    left join @R as [RESULTS] on [RESULTS].GROUPID = CONSTITUENT.ID
                    where [RESULTS].GROUPID is null and (CONSTITUENT.ID = @GROUPID or @GROUPID is null);
                end

                return;
            end