USP_GROUP_RECOGNITIONCREDITS

CREATE procedure dbo.USP_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,
    @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;

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

    create table #TMP_CONSTITUENTFILTER_GROUP_RECOGNITIONCREDITS 
        (ID uniqueidentifier, 
        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('(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 @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  

    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

    --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_RECOGNITIONCREDITS(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 CONSTITUENT.ISINACTIVE=0
      and @GROUPID is null or CONSTITUENT.ID = @GROUPID;  

    create index IX_TMP_CONSTITUENTFILTER_GROUP_RECOGNITIONCREDITS_ID on #TMP_CONSTITUENTFILTER_GROUP_RECOGNITIONCREDITS (ID)

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

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

    if @SITES is null
    begin
        insert into #TMP_MEMBERS_GROUP_RECOGNITIONCREDITS
        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 #TMP_CONSTITUENTFILTER_GROUP_RECOGNITIONCREDITS 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 #TMP_CONSTITUENTFILTER_GROUP_RECOGNITIONCREDITS as [FILTER]  
         inner join dbo.CONSTITUENT on CONSTITUENT.ID = [FILTER].ID  
         inner join dbo.GROUPDATA on GROUPDATA.ID = CONSTITUENT.ID
    end
    else
    begin
        insert into #TMP_MEMBERS_GROUP_RECOGNITIONCREDITS
        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 #TMP_CONSTITUENTFILTER_GROUP_RECOGNITIONCREDITS 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 #CONSTITUENTSITEFILTER as CONSTITUENTSITEFILTER on CONSTITUENTSITEFILTER.ID = GROUPMEMBER.MEMBERID
         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 #TMP_CONSTITUENTFILTER_GROUP_RECOGNITIONCREDITS as [FILTER]  
         inner join dbo.CONSTITUENT on CONSTITUENT.ID = [FILTER].ID  
         inner join #CONSTITUENTSITEFILTER as CONSTITUENTSITEFILTER on CONSTITUENTSITEFILTER.ID = CONSTITUENT.ID
         inner join dbo.GROUPDATA on GROUPDATA.ID = CONSTITUENT.ID
    end

    drop table #CONSTITUENTSITEFILTER;

    create index IX_TMP_MEMBERS_GROUP_RECOGNITIONCREDITS_CONSTITUENTID on #TMP_MEMBERS_GROUP_RECOGNITIONCREDITS (CONSTITUENTID);

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

    create table #TMP_REVENUE_GROUP_RECOGNITIONCREDITS
        (REVENUEID uniqueidentifier, 
        REVENUEDATE datetime, 
        REVENUECONSTITUENTID uniqueidentifier, 
        REVENUESPLITID uniqueidentifier,  
        RECOGNITIONID uniqueidentifier, 
        DATEADDED datetime, 
        EFFECTIVEDATE datetime, 
        RECOGNITIONCONSTITUENTID uniqueidentifier, 
        REVENUERECOGNITIONTYPECODEID uniqueidentifier, 
        AMOUNT money, 
        REVENUEDATEADDED datetime,
        TSLONG bigint)

    declare @declaressql as nvarchar(max) = ''
    set @declaressql = 'declare @TODAY datetime;  
    set @TODAY = getdate();  
    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);
      declare @RECOGNITIONTYPECODESFILTER table (RECOGNITIONTYPECODEID uniqueidentifier primary key);      
      declare @TYPECODES xml = ' + coalesce('''' + cast(@TYPECODES as nvarchar(max)) + '''', 'null') + '

      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

      declare @APPLICATIONTYPECODES xml = ' + coalesce('''' + cast(@APPLICATIONTYPECODES as nvarchar(max)) + '''', 'null') + '
      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

      declare @DESIGNATIONS xml = ' + coalesce('''' + cast(@DESIGNATIONS as nvarchar(max)) + '''', 'null') + '
      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

      declare @CAMPAIGNS xml = ' + coalesce('''' + cast(@CAMPAIGNS as nvarchar(max)) + '''', 'null') + '
      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

      declare @RECOGNITIONTYPECODES xml = ' + coalesce('''' + cast(@RECOGNITIONTYPECODES as nvarchar(max)) + '''', 'null') + '
      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  

    declare @REVENUESELECTIONID uniqueidentifier = ' + coalesce('''' + cast(@REVENUESELECTIONID as nvarchar(36)) + '''', 'null')

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

    create table #TMP_REVENUE_SELECTION
        (ID uniqueidentifier)

    if @REVENUESELECTIONID is not null or @REVENUESELECTIONID <> '00000000-0000-0000-0000-000000000000'  
    begin
        declare @ISSTATIC bit = 0
        declare @DBOBJECTTYPE tinyint = 0
        declare @DBOBJECTNAME nvarchar(128)
        declare @ADHOCQUERYID uniqueidentifier

        select @DBOBJECTNAME = IDSETREGISTER.DBOBJECTNAME, @DBOBJECTTYPE = IDSETREGISTER.OBJECTTYPE, @ISSTATIC = IDSETREGISTER.STATIC, @ADHOCQUERYID=IDSETREGISTERADHOCQUERY.ADHOCQUERYID from dbo.IDSETREGISTER 
            left join dbo.IDSETREGISTERADHOCQUERY on IDSETREGISTER.ID = IDSETREGISTERADHOCQUERY.IDSETREGISTERID
            where IDSETREGISTER.ID = @REVENUESELECTIONID

        if  @DBOBJECTTYPE > 0
        begin
            if @DBOBJECTTYPE = 1 and @ISSTATIC = 1 and @ADHOCQUERYID is not null 
                select @DBOBJECTNAME = dbo.UFN_ADHOCQUERY_MAKEIDSETSTATICTABLENAME(@ADHOCQUERYID)
            else
                set @DBOBJECTNAME = 'UFN_IDSETREADER_GETRESULTS_GUID(''' + cast(@REVENUESELECTIONID as nvarchar(36)) + ''')'
            DECLARE @sqlCommand varchar(1000)
            SET @sqlCommand = 'insert #TMP_REVENUE_SELECTION select distinct * from  dbo.' + @DBOBJECTNAME
            EXEC (@sqlCommand)
        end
    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
            REVENUERECOGNITION.ID
        from
            REVENUERECOGNITION
        inner join    
            REVENUESPLIT on REVENUESPLIT.ID = REVENUERECOGNITION.REVENUESPLITID
        cross apply 
            dbo.UFN_SITEID_MAPFROM_REVENUESPLITID(REVENUESPLIT.ID) as SITES
        inner join
            @SITESFILTER as SITESFILTER on SITESFILTER.ID = SITES.SITEID
    end

    declare @recognitionsql nvarchar(max) = ''
    set @recognitionsql = ' 
      select 
        [REVENUERECOGNITIONAMOUNTINCURRENCY].[REVENUEID],
        [REVENUERECOGNITIONAMOUNTINCURRENCY].[DATE] as [REVENUEDATE], 
        [REVENUERECOGNITIONAMOUNTINCURRENCY].[CONSTITUENTID] as [REVENUECONSTITUENTID], 
        [REVENUERECOGNITIONAMOUNTINCURRENCY].[REVENUESPLITID],
        [REVENUERECOGNITIONAMOUNTINCURRENCY].[ID] as [RECOGNITIONID], 
        [REVENUERECOGNITIONAMOUNTINCURRENCY].[DATEADDED], 
        [REVENUERECOGNITIONAMOUNTINCURRENCY].[EFFECTIVEDATE], 
        [REVENUERECOGNITIONAMOUNTINCURRENCY].[CONSTITUENTID] as [RECOGNITIONCONSTITUENTID], 
        [REVENUERECOGNITIONAMOUNTINCURRENCY].[REVENUERECOGNITIONTYPECODEID], 
        [REVENUERECOGNITIONAMOUNTINCURRENCY].[AMOUNTINCURRENCY],
        [REVENUERECOGNITIONAMOUNTINCURRENCY].[REVENUEDATEADDED],
        [REVENUERECOGNITIONAMOUNTINCURRENCY].[REVENUESPLITTSLONG] as [TSLONG]  
from dbo.[UFN_REVENUERECOGNITION_GETAMOUNTINCURRENCY_BULK](@CURRENCYID, @ORGANIZATIONCURRENCYID, @DECIMALDIGITS, @ROUNDINGTYPECODE) as [REVENUERECOGNITIONAMOUNTINCURRENCY] '

    if @SITES is not null
    begin
        set @recognitionsql = @recognitionsql + ' inner join #TMP_REVENUESPLITSITEFILTER as [REVENUESITEFILTER] on [REVENUESITEFILTER].[ID] = [REVENUERECOGNITIONAMOUNTINCURRENCY].[ID]'
    end

    if @TYPECODES is not null
        set @recognitionsql = @recognitionsql +  ' inner join @SPLITTYPECODESFILTER  [SPLITTYPECODESFILTER] on [SPLITTYPECODESFILTER].TYPECODE = [REVENUERECOGNITIONAMOUNTINCURRENCY].[REVENUESPLITTYPECODE]'

    if @APPLICATIONTYPECODES is not null
        set @recognitionsql = @recognitionsql + ' inner join @APPLICATIONTYPECODESFILTER  [FILTER] on [FILTER].TRANSACTIONTYPECODE = [REVENUERECOGNITIONAMOUNTINCURRENCY].[TRANSACTIONTYPECODE] and [FILTER].APPLICATIONCODE = [REVENUERECOGNITIONAMOUNTINCURRENCY].[APPLICATIONCODE]'

    if @RECOGNITIONTYPECODES is not null
        set @recognitionsql = @recognitionsql + ' inner join @RECOGNITIONTYPECODESFILTER as [RFILTER] on [RFILTER].RECOGNITIONTYPECODEID = [REVENUERECOGNITIONAMOUNTINCURRENCY].[REVENUERECOGNITIONTYPECODEID] '

    if @REVENUESELECTIONID is not null or @REVENUESELECTIONID <> '00000000-0000-0000-0000-000000000000'  
        set @recognitionsql = @recognitionsql + ' inner join #TMP_REVENUE_SELECTION ADH on ADH.ID = [REVENUERECOGNITIONAMOUNTINCURRENCY].[REVENUESPLITID] '

    declare @wheresql nvarchar(max) = ''

    if @DESIGNATIONS is not null
        begin  
            if LEN(@wheresql) > 0 set @wheresql = @wheresql + ' and '
            set @wheresql = @wheresql + ' ([REVENUERECOGNITIONAMOUNTINCURRENCY].[DESIGNATIONID] in (select [DESIGNATIONSFILTER].DESIGNATIONID from @DESIGNATIONSFILTER as [DESIGNATIONSFILTER])) '                
        end

    if @CAMPAIGNS is not null
        begin  
            if LEN(@wheresql) > 0 set @wheresql = @wheresql + ' and '
            set @wheresql = @wheresql + ' ([REVENUERECOGNITIONAMOUNTINCURRENCY].[REVENUESPLITID] in (select REVENUESPLITCAMPAIGN.REVENUESPLITID from dbo.REVENUESPLITCAMPAIGN inner join @CAMPAIGNSFILTER as [CAMPAIGNSFILTER] on [CAMPAIGNSFILTER].CAMPAIGNID = REVENUESPLITCAMPAIGN.CAMPAIGNID))'
        end


    if LEN(@wheresql) > 0 set @wheresql = ' where ' + @wheresql         

    set @recognitionsql = @recognitionsql + @wheresql

    set @recognitionsql = @declaressql + @recognitionsql

    insert into #TMP_REVENUE_GROUP_RECOGNITIONCREDITS
    exec sp_executesql @recognitionsql, N'@CURRENCYID uniqueidentifier, @ORGANIZATIONCURRENCYID uniqueidentifier, @DECIMALDIGITS tinyint, @ROUNDINGTYPECODE tinyint',
      @CURRENCYID = @CURRENCYID, 
      @ORGANIZATIONCURRENCYID = @ORGANIZATIONCURRENCYID, 
      @DECIMALDIGITS = @DECIMALDIGITS, 
      @ROUNDINGTYPECODE = @ROUNDINGTYPECODE;

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

    create index IX_TMP_REVENUE_GROUP_RECOGNITIONCREDITS_RECOGNITIONCONSTITUENTID on #TMP_REVENUE_GROUP_RECOGNITIONCREDITS (RECOGNITIONCONSTITUENTID)

    insert into #TMP_GROUP_RECOGNITIONCREDITS  
        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].TSLONG as [TSLONG]
        from #TMP_REVENUE_GROUP_RECOGNITIONCREDITS [CTE_RECOGNITION]  
        inner join #TMP_MEMBERS_GROUP_RECOGNITIONCREDITS [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].TSLONG as [TSLONG]
        from #TMP_REVENUE_GROUP_RECOGNITIONCREDITS [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 #TMP_MEMBERS_GROUP_RECOGNITIONCREDITS where #TMP_MEMBERS_GROUP_RECOGNITIONCREDITS.ISHISTORICAL = 0)
        and @GROUPID is null;

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


   end