USP_GROUP_REVENUESPLITS

CREATE procedure dbo.USP_GROUP_REVENUESPLITS
(
    @GROUPID uniqueidentifier,
    @TYPECODES xml,
    @DESIGNATIONS xml,
    @CAMPAIGNS xml,
    @FILTERUNCERTAINGIFTSFORHISTORICALMEMBERS bit,
    @ASOF datetime,
    @INCLUDEGROUPSWITHNOREVENUE bit,
    @GROUPTYPECODE tinyint,
    @APPLICATIONTYPECODES xml,
    @REVENUESELECTIONID uniqueidentifier,
    @CURRENCYID uniqueidentifier = null,
    @SITES xml = null
)
with execute as owner
as
begin
    declare @TODAY datetime;
    set @TODAY = getdate(); 

    declare @DECIMALDIGITS tinyint;
    declare @ROUNDINGTYPECODE tinyint;
    declare @ORGANIZATIONCURRENCYID uniqueidentifier = dbo.[UFN_CURRENCY_GETORGANIZATIONCURRENCY]();

    if @CURRENCYID is null
        set @CURRENCYID = @ORGANIZATIONCURRENCYID;

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

    declare @SITESFILTER table(ID uniqueidentifier primary key);
    insert into 
        @SITESFILTER(ID) 
    select distinct
        T.c.value('(SITEID)[1]','uniqueidentifier')
    FROM
        @SITES.nodes('/SITES/ITEM') T(c)

    create table #CONSTITUENTSITEFILTER (ID uniqueidentifier primary key);
    insert into
        #CONSTITUENTSITEFILTER(ID)
    select distinct
        CONSTITUENTSITE.CONSTITUENTID
    from
        CONSTITUENTSITE
    inner join
        @SITESFILTER as SITES on SITES.ID = CONSTITUENTSITE.SITEID

    if object_id('tempdb..#TMP_CONSTITUENTFILTER_GROUP_REVENUESPLITS') is not null
        drop table #TMP_CONSTITUENTFILTER_GROUP_REVENUESPLITS

    create table #TMP_CONSTITUENTFILTER_GROUP_REVENUESPLITS (ID uniqueidentifier primary key, INCLUDESMEMBERGIVING bit not null);

    --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 #TMP_CONSTITUENTFILTER_GROUP_REVENUESPLITS(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;

    if object_id('tempdb..#TMP_MEMBERS_GROUP_REVENUESPLITS') is not null
        drop table #TMP_MEMBERS_GROUP_REVENUESPLITS

    create table #TMP_MEMBERS_GROUP_REVENUESPLITS
    (
        GROUPID uniqueidentifier,
        CONSTITUENTID uniqueidentifier,
        ISGROUP bit,
        DATEFROM datetime,
        DATETO datetime,
        ISHISTORICAL bit,
        GROUPTYPECODE tinyint
    );

    if @SITES is null
    begin
        -- No site filtering.

        insert into #TMP_MEMBERS_GROUP_REVENUESPLITS(GROUPID, CONSTITUENTID, ISGROUP, DATEFROM, DATETO, ISHISTORICAL, GROUPTYPECODE)
        select 
            FILTER.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 #TMP_CONSTITUENTFILTER_GROUP_REVENUESPLITS as [FILTER]
            inner join dbo.GROUPMEMBER on GROUPMEMBER.GROUPID = FILTER.ID
            inner join dbo.GROUPDATA on GROUPDATA.ID = GROUPMEMBER.GROUPID
            left join dbo.GROUPMEMBERDATERANGE on GROUPMEMBERDATERANGE.GROUPMEMBERID = GROUPMEMBER.ID 
        where @GROUPID is null or FILTER.ID = @GROUPID
        union /*Also include giving by the household itself*/
        select
            FILTER.ID as [GROUPID],
            FILTER.ID as [CONSTITUENTID],
            1 as [ISGROUP],
            null as [DATEFROM],
            null as [DATETO],
            0 as [ISHISTORICAL],
            GROUPDATA.GROUPTYPECODE
        from #TMP_CONSTITUENTFILTER_GROUP_REVENUESPLITS as [FILTER]
            inner join dbo.GROUPDATA on GROUPDATA.ID = FILTER.ID
        where @GROUPID is null or FILTER.ID = @GROUPID;                        
    end
    else
    begin
        -- Filter out constituent records not available by sites.

        insert into #TMP_MEMBERS_GROUP_REVENUESPLITS(GROUPID, CONSTITUENTID, ISGROUP, DATEFROM, DATETO, ISHISTORICAL, GROUPTYPECODE)
        select 
            FILTER.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 #TMP_CONSTITUENTFILTER_GROUP_REVENUESPLITS as [FILTER]
            inner join dbo.GROUPMEMBER on GROUPMEMBER.GROUPID = FILTER.ID
            inner join dbo.GROUPDATA on GROUPDATA.ID = GROUPMEMBER.GROUPID
            left join dbo.GROUPMEMBERDATERANGE on GROUPMEMBERDATERANGE.GROUPMEMBERID = GROUPMEMBER.ID 
            inner join #CONSTITUENTSITEFILTER as CONSTITUENTSITEFILTER on CONSTITUENTSITEFILTER.ID = GROUPMEMBER.MEMBERID
        where @GROUPID is null or FILTER.ID = @GROUPID
        union /*Also include giving by the household itself*/
        select
            FILTER.ID as [GROUPID],
            FILTER.ID as [CONSTITUENTID],
            1 as [ISGROUP],
            null as [DATEFROM],
            null as [DATETO],
            0 as [ISHISTORICAL],
            GROUPDATA.GROUPTYPECODE
        from #TMP_CONSTITUENTFILTER_GROUP_REVENUESPLITS as [FILTER]
            inner join dbo.GROUPDATA on GROUPDATA.ID = FILTER.ID
            inner join #CONSTITUENTSITEFILTER as CONSTITUENTSITEFILTER on CONSTITUENTSITEFILTER.ID = FILTER.ID
        where @GROUPID is null or FILTER.ID = @GROUPID;  

    end

    drop table #CONSTITUENTSITEFILTER;

    create index IX_TMP_MEMBERS_GROUP_REVENUESPLITS_GROUPID on #TMP_MEMBERS_GROUP_REVENUESPLITS (GROUPID, ISGROUP, ISHISTORICAL)
    create index IX_TMP_MEMBERS_GROUP_REVENUESPLITS_CONSTITUENTID on #TMP_MEMBERS_GROUP_REVENUESPLITS (CONSTITUENTID, ISGROUP, ISHISTORICAL)

    if object_id('tempdb..#TMP_REVENUE_GROUP_REVENUESPLITS') is not null
        drop table #TMP_REVENUE_GROUP_REVENUESPLITS

    create table #TMP_REVENUE_GROUP_REVENUESPLITS
    (
        REVENUEID uniqueidentifier,
        REVENUEDATE datetime,
        REVENUECONSTITUENTID uniqueidentifier,
        REVENUESPLITID uniqueidentifier primary key,
        REVENUESPLITAMOUNT money,
        REVENUEDATEADDED datetime,
        TSLONG bigint
    );    

    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('(REVENUETYPES)[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 object_id('tempdb..#TMP_REVENUESPLITSITEFILTER') is not null
        drop table #TMP_REVENUESPLITSITEFILTER

    create table #TMP_REVENUESPLITSITEFILTER(ID uniqueidentifier);

    if @SITES is not null
    begin
        insert into #TMP_REVENUESPLITSITEFILTER
        select distinct
            REVENUESPLIT.ID
        from
            REVENUESPLIT
        cross apply 
            dbo.UFN_SITEID_MAPFROM_REVENUESPLITID(REVENUESPLIT.ID) as SITES
        inner join
            @SITESFILTER as SITESFILTER on SITESFILTER.ID = SITES.SITEID
    end

/*================================================================================================================================================================================*/
/***Replacing TBF with a CTE*/
/*================================================================================================================================================================================*/
    ;WITH REVENUESPLITAMOUNTINCURRENCY AS (
    select
        FTLI.ID,
        RES.APPLICATIONCODE,
        RES.[APPLICATION],
        FTLI.FINANCIALTRANSACTIONID REVENUEID,
        FT.CONSTITUENTID,
        RE.APPEALID,
        FT.TYPECODE as TRANSACTIONTYPECODE,
        FT.[TYPE] as TRANSACTIONTYPE,
        case when (@CURRENCYID is not null) 
                    and (@CURRENCYID <> @ORGANIZATIONCURRENCYID)
                    and @CURRENCYID <> FTB.BASECURRENCYID                        
        then
            case
                when [LATESTORGANIZATIONEXCHANGERATE].RATE is not null
                    then dbo.UFN_CURRENCY_ROUND(dbo.UFN_CURRENCY_APPLYRATE(FTLI.ORGAMOUNT, [LATESTORGANIZATIONEXCHANGERATE].RATE), @DECIMALDIGITS, @ROUNDINGTYPECODE)

                when [LATESTINVERSEORGANIZATIONEXCHANGERATE].RATE is not null
                    then dbo.UFN_CURRENCY_ROUND(dbo.UFN_CURRENCY_APPLYRATE(FTLI.ORGAMOUNT, cast((1 / [LATESTINVERSEORGANIZATIONEXCHANGERATE].RATE) as decimal(20,8))), @DECIMALDIGITS, @ROUNDINGTYPECODE)
                else
                    0
            end 
        else                    
            case
                when (@CURRENCYID is null) or (@CURRENCYID = @ORGANIZATIONCURRENCYID)
                    then FTLI.ORGAMOUNT
                else
                    FTLI.BASEAMOUNT    
            end 

        end [AMOUNTINCURRENCY],
        cast(FT.[DATE] as datetime) [DATE],
        RES.DESIGNATIONID,
        RES.TYPECODE,
        FTLI.[TYPE],
        FT.DATEADDED [REVENUEDATEADDED],
        FTLI.TSLONG,
        RE.CHANNELCODEID,
        FT.TRANSACTIONCURRENCYID,
        FTLI.DESCRIPTION, 
        FTLI.POSTDATE
    from
        dbo.FINANCIALTRANSACTIONLINEITEM as FTLI with (nolock)
        inner join dbo.FINANCIALTRANSACTION as FT with (nolock) on FTLI.FINANCIALTRANSACTIONID = FT.ID
        inner join dbo.V_BASECURRENCYFORFINANCIALTRANSACTION_I as FTB with (nolock) on FT.ID = FTB.FINANCIALTRANSACTIONID
        inner join dbo.REVENUE_EXT as RE on FT.ID = RE.ID
        inner join dbo.REVENUESPLIT_EXT as RES on FTLI.ID = RES.ID
        left outer join dbo.CURRENCYEXCHANGERATE LATESTORGANIZATIONEXCHANGERATE  with (nolock)
            on 
                @ORGANIZATIONCURRENCYID = LATESTORGANIZATIONEXCHANGERATE.FROMCURRENCYID
                and @CURRENCYID = LATESTORGANIZATIONEXCHANGERATE.TOCURRENCYID
                and LATESTORGANIZATIONEXCHANGERATE.TYPECODE in (0,1)
                and LATESTORGANIZATIONEXCHANGERATE.ISLATESTRATEFORASOFDATE = 1
                and dateadd(ms, 86399996, FT.[DATE]) >= LATESTORGANIZATIONEXCHANGERATE.ASOFDATESDTZ
    and dateadd(ms, 86399996, FT.[DATE]) <= LATESTORGANIZATIONEXCHANGERATE.NEXTRATEASOFDATEORMAXSDTZ
        left outer join dbo.CURRENCYEXCHANGERATE LATESTINVERSEORGANIZATIONEXCHANGERATE with (nolock)
            on
            @CURRENCYID = LATESTINVERSEORGANIZATIONEXCHANGERATE.FROMCURRENCYID
                and @ORGANIZATIONCURRENCYID= LATESTINVERSEORGANIZATIONEXCHANGERATE.TOCURRENCYID
                and LATESTINVERSEORGANIZATIONEXCHANGERATE.TYPECODE in (0,1)
                and LATESTINVERSEORGANIZATIONEXCHANGERATE.ISLATESTRATEFORASOFDATE = 1
                and dateadd(ms, 86399996, FT.[DATE]) >= LATESTINVERSEORGANIZATIONEXCHANGERATE.ASOFDATESDTZ
    and dateadd(ms, 86399996, FT.[DATE]) <= LATESTINVERSEORGANIZATIONEXCHANGERATE.NEXTRATEASOFDATEORMAXSDTZ where FTLI.DELETEDON is null)
,WRITEOFFSPLITAMOUNTINCURRENCY AS (
  select
    [WRITEOFFSPLIT].[ID],
    [WRITEOFFSPLIT].FINANCIALTRANSACTIONID [WRITEOFFID],
    [REVENUESPLIT_EXT].[DESIGNATIONID],
    case
      when (@CURRENCYID is null) or (@CURRENCYID = @ORGANIZATIONCURRENCYID) then [WRITEOFFSPLIT].[ORGAMOUNT]
      else [WRITEOFFSPLIT].[BASEAMOUNT]
    end as [AMOUNTINCURRENCY],
    [WRITEOFF].[PARENTID] as [REVENUEID]
  from dbo.FINANCIALTRANSACTION [WRITEOFF] with (nolock)
  inner join dbo.FINANCIALTRANSACTIONLINEITEM [WRITEOFFSPLIT] with (nolock) on [WRITEOFF].[ID] = [WRITEOFFSPLIT].[FINANCIALTRANSACTIONID]
  inner join dbo.REVENUESPLIT_EXT on WRITEOFFSPLIT.ID = REVENUESPLIT_EXT.ID
  inner join dbo.FINANCIALTRANSACTION [REVENUE] with (nolock) on [REVENUE].[ID] = [WRITEOFF].[PARENTID]
  inner join dbo.V_BASECURRENCYFORFINANCIALTRANSACTION_I V with (noexpand) on WRITEOFF.ID = V.FINANCIALTRANSACTIONID
  where 
    WRITEOFF.TYPECODE = 20 and WRITEOFF.DELETEDON is null and (
    (@CURRENCYID is null) or
    (@CURRENCYID = @ORGANIZATIONCURRENCYID) or
    (@CURRENCYID = V.BASECURRENCYID))

  union all

  select
    [WRITEOFFSPLIT].[ID],
    [WRITEOFFSPLIT].FINANCIALTRANSACTIONID [WRITEOFFID],
    [REVENUESPLIT_EXT].[DESIGNATIONID],
    case
      when [LATESTORGANIZATIONEXCHANGERATE].[RATE] is not null
        then dbo.[UFN_CURRENCY_ROUND](dbo.[UFN_CURRENCY_APPLYRATE]([WRITEOFFSPLIT].[ORGAMOUNT], [LATESTORGANIZATIONEXCHANGERATE].[RATE]), @DECIMALDIGITS, @ROUNDINGTYPECODE)

      when [LATESTINVERSEORGANIZATIONEXCHANGERATE].[RATE] is not null
        then dbo.[UFN_CURRENCY_ROUND](dbo.[UFN_CURRENCY_APPLYRATE]([WRITEOFFSPLIT].[ORGAMOUNT], cast((1 / [LATESTINVERSEORGANIZATIONEXCHANGERATE].[RATE]) as decimal(20,8))), @DECIMALDIGITS, @ROUNDINGTYPECODE)
      else
        0
    end [AMOUNTINCURRENCY],
    [WRITEOFF].[PARENTID] as [REVENUEID]
  from dbo.FINANCIALTRANSACTION [WRITEOFF] with (nolock)
  inner join dbo.FINANCIALTRANSACTIONLINEITEM [WRITEOFFSPLIT] with (nolock) on [WRITEOFF].[ID] = [WRITEOFFSPLIT].[FINANCIALTRANSACTIONID]
  inner join dbo.REVENUESPLIT_EXT on WRITEOFFSPLIT.ID = REVENUESPLIT_EXT.ID
  inner join dbo.FINANCIALTRANSACTION [REVENUE] with (nolock) on [REVENUE].[ID] = [WRITEOFF].[PARENTID]
  inner join dbo.V_BASECURRENCYFORFINANCIALTRANSACTION_I V with (noexpand) on WRITEOFF.ID = V.FINANCIALTRANSACTIONID
    outer apply
    (
      select
        [RATE]
      from dbo.[CURRENCYEXCHANGERATE]
      where
        @ORGANIZATIONCURRENCYID = [CURRENCYEXCHANGERATE].[FROMCURRENCYID] and
        @CURRENCYID = [CURRENCYEXCHANGERATE].[TOCURRENCYID] and
        [CURRENCYEXCHANGERATE].[TYPECODE] in (0,1) and
        [CURRENCYEXCHANGERATE].[ISLATESTRATEFORASOFDATE] = 1 and
        dateadd(ms, 86399996, cast(REVENUE.DATE as datetime)) >= CURRENCYEXCHANGERATE.ASOFDATESDTZ and
        dateadd(ms, 86399996, cast(REVENUE.DATE as datetime)) <= CURRENCYEXCHANGERATE.NEXTRATEASOFDATEORMAXSDTZ
    ) [LATESTORGANIZATIONEXCHANGERATE]
    outer apply
    (
      select
        [RATE]
      from dbo.[CURRENCYEXCHANGERATE]
      where
        @CURRENCYID = [CURRENCYEXCHANGERATE].[FROMCURRENCYID] and
        @ORGANIZATIONCURRENCYID= [CURRENCYEXCHANGERATE].[TOCURRENCYID] and
        [CURRENCYEXCHANGERATE].[TYPECODE] in (0,1) and
        [CURRENCYEXCHANGERATE].[ISLATESTRATEFORASOFDATE] = 1 and
        dateadd(ms, 86399996, cast(REVENUE.DATE as datetime)) >= CURRENCYEXCHANGERATE.ASOFDATESDTZ and
        dateadd(ms, 86399996, cast(REVENUE.DATE as datetime)) <= CURRENCYEXCHANGERATE.NEXTRATEASOFDATEORMAXSDTZ
    ) [LATESTINVERSEORGANIZATIONEXCHANGERATE]
    where 
      WRITEOFF.TYPECODE = 20 and WRITEOFF.DELETEDON is null and (
      (@CURRENCYID is not null) and
      (@CURRENCYID <> @ORGANIZATIONCURRENCYID) and
      (@CURRENCYID <> V.BASECURRENCYID))
      )

/*================================================================================================================================================================================*/    
/*================================================================================================================================================================================*/


    insert into #TMP_REVENUE_GROUP_REVENUESPLITS(REVENUEID, REVENUEDATE, REVENUECONSTITUENTID, REVENUESPLITID, REVENUESPLITAMOUNT, REVENUEDATEADDED, TSLONG)
    select 
        [REVENUESPLITAMOUNTINCURRENCY].[REVENUEID] as [REVENUEID], 
        [REVENUESPLITAMOUNTINCURRENCY].[DATE] as [REVENUEDATE], 
        [REVENUESPLITAMOUNTINCURRENCY].[CONSTITUENTID] as [REVENUECONSTITUENTID], 
        [REVENUESPLITAMOUNTINCURRENCY].[ID] as [REVENUESPLITID], 
        [REVENUESPLITAMOUNTINCURRENCY].[AMOUNTINCURRENCY] - coalesce(sum([WRITEOFFSPLITAMOUNTINCURRENCY].[AMOUNTINCURRENCY]),0) [REVENUESPLITAMOUNT],
        [REVENUESPLITAMOUNTINCURRENCY].[REVENUEDATEADDED] as [REVENUEDATEADDED],
        [REVENUESPLITAMOUNTINCURRENCY].[TSLONG]
    from REVENUESPLITAMOUNTINCURRENCY
        left join WRITEOFFSPLITAMOUNTINCURRENCY on [WRITEOFFSPLITAMOUNTINCURRENCY].[REVENUEID] = [REVENUESPLITAMOUNTINCURRENCY].[REVENUEID]
                                                and [WRITEOFFSPLITAMOUNTINCURRENCY].[DESIGNATIONID] = [REVENUESPLITAMOUNTINCURRENCY].[DESIGNATIONID]
        left join #TMP_REVENUESPLITSITEFILTER REVENUESPLITSITEFILTER on REVENUESPLITSITEFILTER.ID = REVENUESPLITAMOUNTINCURRENCY.ID
    where
        [REVENUESPLITAMOUNTINCURRENCY].[CONSTITUENTID] is not null and
        (@TYPECODES is null 
            or(
                [REVENUESPLITAMOUNTINCURRENCY].[TYPECODE] in (
                    select TYPECODE
                    from @SPLITTYPECODESFILTER
                )
            )
        )
        and (@APPLICATIONTYPECODES is null 
            or exists (
                select 1 
                from @APPLICATIONTYPECODESFILTER
                where TRANSACTIONTYPECODE is not null
                    and TRANSACTIONTYPECODE = [REVENUESPLITAMOUNTINCURRENCY].[TRANSACTIONTYPECODE]
                    and APPLICATIONCODE = [REVENUESPLITAMOUNTINCURRENCY].[APPLICATIONCODE]
            )
        )
        and (@DESIGNATIONS is null 
            or (
                [REVENUESPLITAMOUNTINCURRENCY].[DESIGNATIONID] in (
                    select DESIGNATIONID
                    from @DESIGNATIONSFILTER
                )
            )
        )
        and (@CAMPAIGNS is null 
            or (
                [REVENUESPLITAMOUNTINCURRENCY].[ID] in (
                    select [REVENUESPLITCAMPAIGN].[REVENUESPLITID] 
                    from dbo.[REVENUESPLITCAMPAIGN] 
                        inner join @CAMPAIGNSFILTER as [CAMPAIGNSFILTER] on [CAMPAIGNSFILTER].[CAMPAIGNID] = [REVENUESPLITCAMPAIGN].[CAMPAIGNID]
                )
            )
        )
        and (@SITES is null
            or ( [REVENUESPLITSITEFILTER].[ID] is not null )
        )
    group by
        [REVENUESPLITAMOUNTINCURRENCY].[REVENUEID], 
        [REVENUESPLITAMOUNTINCURRENCY].[DATE], 
        [REVENUESPLITAMOUNTINCURRENCY].[CONSTITUENTID], 
        [REVENUESPLITAMOUNTINCURRENCY].[ID], 
        [REVENUESPLITAMOUNTINCURRENCY].[AMOUNTINCURRENCY],
        [REVENUESPLITAMOUNTINCURRENCY].[REVENUEDATEADDED],
        [REVENUESPLITAMOUNTINCURRENCY].[TSLONG]

    if @REVENUESELECTIONID is not null or @REVENUESELECTIONID <> '00000000-0000-0000-0000-000000000000'
    begin
        declare @DBOBJECTNAME nvarchar(max);  
        declare @DBOBJECTTYPE smallint;

        select
            @DBOBJECTNAME = [DBOBJECTNAME],  
            @DBOBJECTTYPE = [OBJECTTYPE]  
        from dbo.[IDSETREGISTER]  
        where [ID] = @REVENUESELECTIONID;

        if left(@DBOBJECTNAME, 1) <> '['  
            set @DBOBJECTNAME = '[' + @DBOBJECTNAME + ']';  

        if @DBOBJECTTYPE = 1  
        begin  
            set @DBOBJECTNAME = @DBOBJECTNAME + '(';  
            set @DBOBJECTNAME = @DBOBJECTNAME + ')';  
        end  
        else  
        begin  
            if @DBOBJECTTYPE = 2  
                set @DBOBJECTNAME = @DBOBJECTNAME + '(''' + convert(nvarchar(36), @REVENUESELECTIONID) + ''')';
        end

        declare @SELECTIONSQL nvarchar(max) = '
            delete #TMP_REVENUE_GROUP_REVENUESPLITS
            where REVENUESPLITID not in (select [ID] from dbo.' + @DBOBJECTNAME + ')';

        exec sp_executesql @SELECTIONSQL;
    end

    insert into #TMP_GROUP_REVENUESPLITS
    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,
        [CTE_REVENUE].TSLONG
    from #TMP_MEMBERS_GROUP_REVENUESPLITS [CTE_MEMBERS]
        inner join #TMP_REVENUE_GROUP_REVENUESPLITS [CTE_REVENUE] 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 all --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,
        [CTE_REVENUE].TSLONG
    from #TMP_REVENUE_GROUP_REVENUESPLITS [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 #TMP_MEMBERS_GROUP_REVENUESPLITS where #TMP_MEMBERS_GROUP_REVENUESPLITS.ISHISTORICAL = 0)
        and @GROUPID is null

    if @INCLUDEGROUPSWITHNOREVENUE = 1
        insert into #TMP_GROUP_REVENUESPLITS
        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],
            0 as [TSLONG]
        from dbo.CONSTITUENT
            inner join dbo.GROUPDATA on GROUPDATA.ID = CONSTITUENT.ID
            left join #TMP_GROUP_REVENUESPLITS as [RESULT] on [RESULT].GROUPID = CONSTITUENT.ID
        where [RESULT].GROUPID is null and (CONSTITUENT.ID = @GROUPID or @GROUPID is null)

    create index IX_TMP_GROUP_REVENUESPLITS_GROUPID on #TMP_GROUP_REVENUESPLITS (GROUPID)
    create index IX_TMP_GROUP_REVENUESPLITS_GROUPMEMBERID on #TMP_GROUP_REVENUESPLITS (GROUPMEMBERID)
    create index IX_TMP_GROUP_REVENUESPLITS_REVENUEDATE on #TMP_GROUP_REVENUESPLITS (REVENUEDATE)

end