UFN_GROUP_REVENUESPLITS

            CREATE function dbo.UFN_GROUP_REVENUESPLITS
            (
                @GROUPID uniqueidentifier,
                @TYPECODES xml,
                @DESIGNATIONS xml,
                @CAMPAIGNS xml,

                @FILTERUNCERTAINGIFTSFORHISTORICALMEMBERS bit,
                @ASOF datetime,
                @INCLUDEGROUPSWITHNOREVENUE bit,
                @GROUPTYPECODE tinyint,

                @APPLICATIONTYPECODES xml,
                @REVENUESELECTIONID uniqueidentifier
            )
            returns @R table
            (
                GROUPID uniqueidentifier,
                GROUPTYPECODE tinyint,
                GROUPMEMBERID uniqueidentifier,
                ISGROUP bit,
                GROUPMEMBERSHIPSTARTDATE datetime,
                GROUPMEMBERSHIPENDDATE datetime,
                GROUPMEMBERSHIPISHISTORICAL bit,
                REVENUEID uniqueidentifier,
                REVENUESPLITID uniqueidentifier,
                REVENUEDATE datetime,
                REVENUESPLITAMOUNT money,
                REVENUEDATEADDED datetime
            )
            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 @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

                --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
                    inner join dbo.GROUPDATA on GROUPDATA.ID = GROUPMEMBER.GROUPID
                    left join dbo.GROUPMEMBERDATERANGE on GROUPMEMBERDATERANGE.GROUPMEMBERID = GROUPMEMBER.ID 
                    where @GROUPID is null or CONSTITUENT.ID = @GROUPID
                    union    /*Also include giving by the household 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
                    where @GROUPID is null or CONSTITUENT.ID = @GROUPID
                ),
                [CTE_REVENUE] as
                (
                    select 
                        FINANCIALTRANSACTION.ID as [REVENUEID], 
                        FINANCIALTRANSACTION.DATE as [REVENUEDATE], 
                        FINANCIALTRANSACTION.CONSTITUENTID as [REVENUECONSTITUENTID], 
                        FINANCIALTRANSACTIONLINEITEM.ID as [REVENUESPLITID], 
                        case when FINANCIALTRANSACTION.TYPECODE in (1, 3) then
                            FINANCIALTRANSACTIONLINEITEM.BASEAMOUNT - coalesce((
                                                            select sum(WRITEOFFSPLIT.AMOUNT) as [WRITEOFFAMOUNT]
                                                            from dbo.WRITEOFF
                                                            inner join dbo.WRITEOFFSPLIT on WRITEOFF.ID = WRITEOFFSPLIT.WRITEOFFID
                                                            where WRITEOFF.REVENUEID = FINANCIALTRANSACTION.ID), 0)
                            else
                                FINANCIALTRANSACTIONLINEITEM.BASEAMOUNT 
                        end as [REVENUESPLITAMOUNT], 
                        FINANCIALTRANSACTION.DATEADDED as [REVENUEDATEADDED]
                    from dbo.FINANCIALTRANSACTION 
                    inner join dbo.FINANCIALTRANSACTIONLINEITEM  on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = FINANCIALTRANSACTION.ID
          inner join dbo.REVENUESPLIT_EXT on REVENUESPLIT_EXT.ID = FINANCIALTRANSACTIONLINEITEM.ID
                    where 
                            (@TYPECODES is null or REVENUESPLIT_EXT.TYPECODE in (select [SPLITTYPECODESFILTER].TYPECODE from @SPLITTYPECODESFILTER as [SPLITTYPECODESFILTER]))
                        and (
                                @APPLICATIONTYPECODES is null or FINANCIALTRANSACTIONLINEITEM.ID in        (
                                                                                        select [FILTERSPLIT].ID 
                                                                                        from dbo.FINANCIALTRANSACTIONLINEITEM as [FILTERSPLIT] 
                                            inner join dbo.REVENUESPLIT_EXT on FILTERSPLIT.ID = REVENUESPLIT_EXT.ID
                                                                                        inner join dbo.FINANCIALTRANSACTION as [FILTERREVENUE] on [FILTERREVENUE].ID = [FILTERSPLIT].FINANCIALTRANSACTIONID
                                                                                        inner join @APPLICATIONTYPECODESFILTER as [FILTER] on [FILTER].APPLICATIONCODE = [REVENUESPLIT_EXT].APPLICATIONCODE
                                                                                        and [FILTER].TRANSACTIONTYPECODE = [FILTERREVENUE].TYPECODE
                                                                                        )
                            )
                        and    (@DESIGNATIONS is null or REVENUESPLIT_EXT.DESIGNATIONID in (select [DESIGNATIONSFILTER].DESIGNATIONID from @DESIGNATIONSFILTER as [DESIGNATIONSFILTER]))
                        and (@CAMPAIGNS is null or FINANCIALTRANSACTIONLINEITEM.ID in (select REVENUESPLITCAMPAIGN.REVENUESPLITID from dbo.REVENUESPLITCAMPAIGN inner join @CAMPAIGNSFILTER as [CAMPAIGNSFILTER] on [CAMPAIGNSFILTER].CAMPAIGNID = REVENUESPLITCAMPAIGN.CAMPAIGNID))
                        and (@REVENUESELECTIONID is null or FINANCIALTRANSACTIONLINEITEM.ID in (select ID from dbo.UFN_IDSETREADER_GETRESULTS_GUID(@REVENUESELECTIONID)))
            and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null and FINANCIALTRANSACTIONLINEITEM.TYPECODE != 1
            and FINANCIALTRANSACTION.DELETEDON is null
                )
                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_REVENUE].REVENUEID, 
                        [CTE_REVENUE].REVENUESPLITID,
                        [CTE_REVENUE].REVENUEDATE,
                        [CTE_REVENUE].REVENUESPLITAMOUNT as [REVENUESPLITAMOUNT],
                        [CTE_REVENUE].REVENUEDATEADDED
                    from [CTE_REVENUE]
                    inner join [CTE_MEMBERS] on [CTE_MEMBERS].CONSTITUENTID = [CTE_REVENUE].REVENUECONSTITUENTID
                    where 
                        [CTE_MEMBERS].ISGROUP = 1 
                        or
                        [CTE_MEMBERS].ISHISTORICAL = 0
                        or 
                        ([CTE_MEMBERS].ISHISTORICAL = 1 and (@FILTERUNCERTAINGIFTSFORHISTORICALMEMBERS = 0 or ([CTE_REVENUE].REVENUEDATE <= [CTE_MEMBERS].DATETO)))

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

                    select
                        null,
                        null,
                        CONSTITUENT.ID,
                        0,
                        null,
                        null,
                        0,
                        [CTE_REVENUE].REVENUEID, 
                        [CTE_REVENUE].REVENUESPLITID,
                        [CTE_REVENUE].REVENUEDATE,
                        [CTE_REVENUE].REVENUESPLITAMOUNT as [REVENUESPLITAMOUNT],
                        [CTE_REVENUE].REVENUEDATEADDED
                    from [CTE_REVENUE]
                    inner join dbo.CONSTITUENT on CONSTITUENT.ID = [CTE_REVENUE].REVENUECONSTITUENTID
                    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 @INCLUDEGROUPSWITHNOREVENUE = 1
                    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 [GROUPMEMBERSHIPISHISTORICAL],
                            null as [REVENUEID],
                            null as [REVENUESPLITID],
                            null as [REVENUEDATE],
                            null as [REVENUESPLITAMOUNT],
                            null as [REVENUEDATEADDED]
                        from dbo.CONSTITUENT
                        inner join dbo.GROUPDATA on GROUPDATA.ID = CONSTITUENT.ID
                        left join @R as [RESULT] on [RESULT].GROUPID = CONSTITUENT.ID
                        where [RESULT].GROUPID is null and (CONSTITUENT.ID = @GROUPID or @GROUPID is null)

                return;
            end