UFN_GROUPCORRESPONDENCELIST_QUERY

CREATE function dbo.UFN_GROUPCORRESPONDENCELIST_QUERY
(
    @CONSTITUENTID uniqueidentifier,
    @DATEFILTER tinyint = null,
    @CORRESPONDENCETYPECODE smallint = null,
    @CURRENTAPPUSERID uniqueidentifier,
    @SITEFILTERMODE tinyint = 0,
    @SITESSELECTED xml = null,
    @SECURITYFEATUREID uniqueidentifier = null,
    @SECURITYFEATURETYPE tinyint = null,
    @CHANNEL smallint = 99
)
returns @CORRESPONDENCES table
(
    ID uniqueidentifier,
    CONSTITUENTID uniqueidentifier,
    DATESENT datetime,
    NAME nvarchar(200),
    CORRESPONDENCETYPECODE smallint,
    CORRESPONDENCETYPE nvarchar(200),
    DETAILS nvarchar(200),
    PROCESSOR nvarchar(200),
    COMMENTS nvarchar(255),
    RECORDID nvarchar(200),
    FINDERNUMBER bigint,
    TIMEFRAMETEXT nvarchar(200),
    TIMEFRAMEGROUPSORT nvarchar(200),
    HASPERMISSIONS bit,
    HASRESPONSES bit,
    PREVIEWDATAFORM uniqueidentifier,
    PREVIEWDATAFORMRECORDID nvarchar(200),
    SITE nvarchar(200),
    CHANNEL nvarchar(200)
)
as
begin

declare @ISSYSADMIN bit = 0;
select @ISSYSADMIN = ISSYSADMIN from dbo.APPUSER where ID = @CURRENTAPPUSERID;

declare @QUERYVIEWID uniqueidentifier;

set @QUERYVIEWID = 'ab0db3a0-c3df-4973-a314-65f201d7248b';

declare @USERGRANTEDALLCONSTITUENTS bit = 0;
set @USERGRANTEDALLCONSTITUENTS = dbo.UFN_SECURITY_APPUSER_GRANTED_QUERYVIEW_IN_NONRACROLE(@CURRENTAPPUSERID, @QUERYVIEWID);

declare @ENTERPRISEINSTALLED bit = dbo.[UFN_INSTALLEDPRODUCTS_PRODUCTIS]('3117D2C8-7F46-42F2-ABEB-B654F2F63046');

if @DATEFILTER is null
begin
    set @DATEFILTER = 6; -- current month;

end

if @CORRESPONDENCETYPECODE is null
begin
    set @CORRESPONDENCETYPECODE = 99; -- all;

end

declare @STARTDATE datetime;
declare @ENDDATE datetime;

declare @ASOFDATE datetime = getdate();

set @STARTDATE = @ASOFDATE;
set @ENDDATE = @STARTDATE;

-- @DATEFILTER=1 is for today only


if @DATEFILTER=2 begin
    -- Next 7 days

    set @ENDDATE = dateadd(day,7,@ENDDATE);
end else if @DATEFILTER=3 begin
    -- Last 7 days

    set @STARTDATE = dateadd(day,-7,@STARTDATE);
end else if @DATEFILTER=4 begin
    -- Next 30 days

    set @ENDDATE = dateadd(day,30,@ENDDATE);
end else if @DATEFILTER=5 begin
    -- Last 30 days

    set @STARTDATE = dateadd(day,-30,@STARTDATE);
end else if @DATEFILTER=6 begin
    -- Current month

    set @STARTDATE = dbo.UFN_DATE_THISMONTH_FIRSTDAY(@STARTDATE, 0);
    set @ENDDATE = dbo.UFN_DATE_THISMONTH_LASTDAY(@ENDDATE, 1);
end else if @DATEFILTER=7 begin
    -- Previous month

    set @STARTDATE = dbo.UFN_DATE_LASTMONTH_FIRSTDAY(@STARTDATE, 0);
    set @ENDDATE = dbo.UFN_DATE_LASTMONTH_LASTDAY(@ENDDATE, 1);
end else if @DATEFILTER=8 begin
    -- Next 12 months

    set @ENDDATE = dateadd(year,1,@ENDDATE);
end else if @DATEFILTER=9 begin
    -- Last 12 months

    set @STARTDATE = dateadd(year,-1,@STARTDATE);
end else if @DATEFILTER=10 begin
    -- All dates

    set @STARTDATE = '17530102';
    set @ENDDATE = '99991230';
end else if @DATEFILTER=11 begin
    -- Current fiscal year

    set @STARTDATE = dbo.UFN_DATE_THISFISCALYEAR_FIRSTDAY(@STARTDATE, 0);
    set @ENDDATE = dbo.UFN_DATE_THISFISCALYEAR_LASTDAY(@ENDDATE, 1);
end else if @DATEFILTER=12 begin
    -- Previous fiscal year

    set @STARTDATE = dbo.UFN_DATE_THISFISCALYEAR_FIRSTDAY(dateadd(year,-1,@STARTDATE), 0);
    set @ENDDATE = dbo.UFN_DATE_THISFISCALYEAR_LASTDAY(dateadd(year,-1,@ENDDATE), 1);
end else if @DATEFILTER=13 begin
    -- Next fiscal year

    set @STARTDATE = dbo.UFN_DATE_THISFISCALYEAR_FIRSTDAY(dateadd(year,1,@STARTDATE), 0);
    set @ENDDATE = dbo.UFN_DATE_THISFISCALYEAR_LASTDAY(dateadd(year,1,@ENDDATE), 1);
end else if @DATEFILTER=14 begin
    -- Within 7 days

    set @STARTDATE = dateadd(day,-7,@STARTDATE);
    set @ENDDATE = dateadd(day,7,@ENDDATE);
end else if @DATEFILTER=15 begin
    -- Within 30 days

    set @STARTDATE = dateadd(day,-30,@STARTDATE);
    set @ENDDATE = dateadd(day,30,@ENDDATE);
end else if @DATEFILTER=16 begin
    -- This calendar year

    set @STARTDATE = dbo.UFN_DATE_THISCALENDARYEAR_FIRSTDAY(@STARTDATE, 0);
    set @ENDDATE = dbo.UFN_DATE_THISCALENDARYEAR_LASTDAY(@ENDDATE, 1);
end else if @DATEFILTER=17 begin
    -- Last calendar year

    set @STARTDATE = dbo.UFN_DATE_LASTCALENDARYEAR_FIRSTDAY(@STARTDATE, 0);
    set @ENDDATE = dbo.UFN_DATE_LASTCALENDARYEAR_LASTDAY(@ENDDATE, 1);
end else if @DATEFILTER=18 begin
    -- Next calendar year

    set @STARTDATE = dbo.UFN_DATE_NEXTCALENDARYEAR_FIRSTDAY(@STARTDATE, 0);
    set @ENDDATE = dbo.UFN_DATE_NEXTCALENDARYEAR_LASTDAY(@ENDDATE, 1);
end else if @DATEFILTER=19 begin
    -- This week

    set @STARTDATE = dbo.UFN_DATE_THISWEEK_FIRSTDAY(@STARTDATE, 0);
    set @ENDDATE = dbo.UFN_DATE_THISWEEK_LASTDAY(@ENDDATE, 1);
end else if @DATEFILTER=20 begin
    -- Last week

    set @STARTDATE = dbo.UFN_DATE_LASTWEEK_FIRSTDAY(@STARTDATE, 0);
    set @ENDDATE = dbo.UFN_DATE_LASTWEEK_LASTDAY(@ENDDATE, 1);
end else if @DATEFILTER=21 begin
    -- Next week

    set @STARTDATE = dbo.UFN_DATE_NEXTWEEK_FIRSTDAY(@STARTDATE, 0);
    set @ENDDATE = dbo.UFN_DATE_NEXTWEEK_LASTDAY(@ENDDATE, 1);
end else if @DATEFILTER=22 begin
    -- Next month

    set @STARTDATE = dbo.UFN_DATE_NEXTMONTH_FIRSTDAY(@STARTDATE, 0);
    set @ENDDATE = dbo.UFN_DATE_NEXTMONTH_LASTDAY(@ENDDATE, 1);
end else if @DATEFILTER=23 begin
    -- This quarter

    set @STARTDATE = dbo.UFN_DATE_THISQUARTER_FIRSTDAY(@STARTDATE, 0);
    set @ENDDATE = dbo.UFN_DATE_THISQUARTER_LASTDAY(@ENDDATE, 1);
end else if @DATEFILTER=24 begin
    -- Last quarter

    set @STARTDATE = dbo.UFN_DATE_LASTQUARTER_FIRSTDAY(@STARTDATE, 0);
    set @ENDDATE = dbo.UFN_DATE_LASTQUARTER_LASTDAY(@ENDDATE, 1);
end else if @DATEFILTER=25 begin
    -- Next quarter

    set @STARTDATE = dbo.UFN_DATE_NEXTQUARTER_FIRSTDAY(@STARTDATE, 0);
    set @ENDDATE = dbo.UFN_DATE_NEXTQUARTER_LASTDAY(@ENDDATE, 1);
end else if @DATEFILTER=26 begin
    --No date assigned

    set @STARTDATE = null
    set @ENDDATE = null
end else if @DATEFILTER=27 begin
    --Remainder of the month

    set @ENDDATE = dbo.UFN_DATE_THISMONTH_LASTDAY(@ENDDATE,1);
end else if @DATEFILTER=28 begin
    --Remainder of this fiscal year

    set @ENDDATE = dbo.UFN_DATE_THISFISCALYEAR_LASTDAY(@ENDDATE,1);
end else if @DATEFILTER=29 begin
    -- Last 6 months

    set @STARTDATE = dateadd(month,-6,@STARTDATE);
end else if @DATEFILTER=30 begin
    -- Last 24 months

    set @STARTDATE = dateadd(year,-2,@STARTDATE);
end

set @STARTDATE = dbo.UFN_DATE_GETEARLIESTTIME(@STARTDATE);
set @ENDDATE = dbo.UFN_DATE_GETLATESTTIME(@ENDDATE);

declare @CURRENTDATE datetime = getdate();
declare @THISMONTHSTART datetime = dbo.UFN_DATE_THISMONTH_FIRSTDAY(@CURRENTDATE, 0);
declare @LASTMONTHSTART datetime = dateadd(month, -1, @THISMONTHSTART);
declare @TWOMONTHSAGOSTART datetime = dateadd(month, -2, @THISMONTHSTART);


with INTERACTIONS
as (
    -- group member general correspondence

    select convert(nchar(36), C.[ID]) ID,
        C.CONSTITUENTID CONSTITUENTID,
        C.[DATESENT] DATESENT,
        0 CORRESPONDENCETYPECODE,
        'General Correspondence' CORRESPONDENCETYPE,
        CC.NAME DETAILS,
        CA.[USERNAME] PROCESSOR,
        C.[COMMENTS] COMMENTS,
        convert(nchar(36), CORRESPONDENCEPROCESSSTATUS.PARAMETERSETID) RECORDID,
        0 FINDERNUMBER,
        case 
            when exists (
                    select 1
                    from dbo.CONSTITUENTCORRESPONDENCERESPONSE
                    where CONSTITUENTCORRESPONDENCERESPONSE.CONSTITUENTCORRESPONDENCEID = C.ID
                    )
                then convert(bit, 1)
            else convert(bit, 0)
            end HASRESPONSES,
        'E91F460E-43C0-40DF-BDA9-1D5346E6B535' as PREVIEWDATAFORM,
        convert(nchar(36), C.[ID]) as PREVIEWDATAFORMRECORDID,
        CC.[SITEID] as SITEID,
        case --If correspondence doesn't have a code, the record is unsecured

            when CC.ID is null
                then 0
            else 1
            end as SITEENFORCEMENT,
        99 as CHANNEL
    from dbo.[CONSTITUENTCORRESPONDENCE] C
    left join dbo.[CORRESPONDENCECODE] CC
        on CC.[ID] = C.[CORRESPONDENCECODEID]
    left join dbo.[CORRESPONDENCEPROCESSRUNHISTORY]
        on C.CORRESPONDENCEPROCESSRUNHISTORYID = CORRESPONDENCEPROCESSRUNHISTORY.ID
    left join dbo.[CORRESPONDENCEPROCESSSTATUS]
        on CORRESPONDENCEPROCESSSTATUS.ID = CORRESPONDENCEPROCESSRUNHISTORY.CORRESPONDENCEPROCESSSTATUSID
    left join dbo.[CHANGEAGENT] CA
        on CA.[ID] = C.[ADDEDBYID]
    inner join dbo.GROUPMEMBER GM
        on C.CONSTITUENTID = GM.MEMBERID
    where GM.GROUPID = @CONSTITUENTID
        and (
            @CORRESPONDENCETYPECODE = 0
            or @CORRESPONDENCETYPECODE = 99
            )
        and (
            C.[DATESENT] >= @STARTDATE
            and C.[DATESENT] <= @ENDDATE
            )
        and @ENTERPRISEINSTALLED = 1
        and @CHANNEL = 99

    union all

    -- group general correspondence

    select convert(nchar(36), C.[ID]) ID,
        C.CONSTITUENTID CONSTITUENTID,
        C.[DATESENT] DATESENT,
        0 CORRESPONDENCETYPECODE,
        'General Correspondence' CORRESPONDENCETYPE,
        CC.NAME DETAILS,
        CA.[USERNAME] PROCESSOR,
        C.[COMMENTS] COMMENTS,
        convert(nchar(36), CORRESPONDENCEPROCESSSTATUS.PARAMETERSETID) RECORDID,
        0 FINDERNUMBER,
        case 
            when exists (
                    select 1
                    from dbo.CONSTITUENTCORRESPONDENCERESPONSE
                    where CONSTITUENTCORRESPONDENCERESPONSE.CONSTITUENTCORRESPONDENCEID = C.ID
                    )
                then convert(bit, 1)
            else convert(bit, 0)
            end HASRESPONSES,
        'E91F460E-43C0-40DF-BDA9-1D5346E6B535' as PREVIEWDATAFORM,
        convert(nchar(36), C.[ID]) as PREVIEWDATAFORMRECORDID,
        CC.[SITEID] as SITEID,
        case --If correspondence doesn't have a code, the record is unsecured

            when CC.ID is null
                then 0
            else 1
            end as SITEENFORCEMENT,
        99 as CHANNEL
    from dbo.[CONSTITUENTCORRESPONDENCE] C
    left join dbo.[CORRESPONDENCECODE] CC
        on CC.[ID] = C.[CORRESPONDENCECODEID]
    left join dbo.[CORRESPONDENCEPROCESSRUNHISTORY]
        on C.CORRESPONDENCEPROCESSRUNHISTORYID = CORRESPONDENCEPROCESSRUNHISTORY.ID
    left join dbo.[CORRESPONDENCEPROCESSSTATUS]
        on CORRESPONDENCEPROCESSSTATUS.ID = CORRESPONDENCEPROCESSRUNHISTORY.CORRESPONDENCEPROCESSSTATUSID
    left join dbo.[CHANGEAGENT] CA
        on CA.[ID] = C.[ADDEDBYID]
    where C.CONSTITUENTID = @CONSTITUENTID
        and (
            @CORRESPONDENCETYPECODE = 0
            or @CORRESPONDENCETYPECODE = 99
            )
        and (
            C.[DATESENT] >= @STARTDATE
            and C.[DATESENT] <= @ENDDATE
            )
        and @ENTERPRISEINSTALLED = 1
        and @CHANNEL = 99

    union all

    -- group member acknowledgements

    select convert(nchar(36), R.[ID]),
        R.CONSTITUENTID,
        RL.[ACKNOWLEDGEDATE],
        1,
        'Acknowledgement',
        case 
            when RL.MKTPACKAGEID is not null
                then (
                        select COMMUNICATIONLETTER.NAME
                        from dbo.COMMUNICATIONLETTER
                        where COMMUNICATIONLETTER.EMAILPACKAGEID = RL.MKTPACKAGEID
                            or COMMUNICATIONLETTER.MAILPACKAGEID = RL.MKTPACKAGEID
                        )
            else AP.[NAME] + case 
                    when AP.[NAME] is not null
                        and AP.[NAME] <> ''
                        and LC.[NAME] is not null
                        and LC.[NAME] <> ''
                        then ' - '
                  else ''
                    end + LC.[NAME]
            end,
        case 
            when AU.USERNAME is not null
                then AU.USERNAME
            else CA.[USERNAME]
            end,
        '',
        '',
        0,
        convert(bit, 0),
        '7C0968B7-61C7-44EA-8F8D-96C139A42AD0',
        convert(nchar(36), R.[ID]) as PREVIEWDATAFORMRECORDID,
        BPI.[SITEID] as SITEID,
        2 as SITEENFORCEMENT,
        coalesce(MKTPACKAGE.CHANNELCODE, 0) as CHANNEL
    from dbo.[REVENUELETTER] RL
    left join dbo.[LETTERCODE] LC
        on LC.[ID] = RL.[LETTERCODEID]
    left join dbo.[ACKNOWLEDGEMENTPROCESSSTATUS] APS
        on APS.[ID] = RL.[ACKNOWLEDGEMENTPROCESSSTATUSID]
    left join dbo.[BUSINESSPROCESSSTATUS] BPS
        on BPS.[ID] = RL.[ACKNOWLEDGEMENTPROCESSSTATUSID]
    left join dbo.[ACKNOWLEDGEMENTPROCESS] AP
        on AP.[ID] = APS.[PARAMETERSETID]
    left join dbo.[REVENUE] R
        on R.[ID] = RL.[REVENUEID]
    left join dbo.[APPUSER] AU
        on AU.[ID] = BPS.[STARTEDBYUSERID]
    left join dbo.[CHANGEAGENT] CA
        on CA.[ID] = RL.[ADDEDBYID]
    inner join dbo.GROUPMEMBER GM
        on R.CONSTITUENTID = GM.MEMBERID
    left join dbo.BUSINESSPROCESSINSTANCE BPI
        on AP.ID = BPI.BUSINESSPROCESSPARAMETERSETID
    left join dbo.MKTPACKAGE
        on RL.MKTPACKAGEID = MKTPACKAGE.ID
    where GM.GROUPID = @CONSTITUENTID
        and RL.[ACKNOWLEDGEDATE] is not null
        and (
            @CORRESPONDENCETYPECODE = 1
            or @CORRESPONDENCETYPECODE = 99
            )
        and (
            RL.[ACKNOWLEDGEDATE] >= @STARTDATE
            and RL.[ACKNOWLEDGEDATE] <= @ENDDATE
            )
        and (
            @CHANNEL = 99
            or MKTPACKAGE.ID is null
            or MKTPACKAGE.CHANNELCODE = @CHANNEL
            )

    union all

    -- group acknowledgements

    select convert(nchar(36), R.[ID]),
        R.CONSTITUENTID,
        RL.[ACKNOWLEDGEDATE],
        1,
        'Acknowledgement',
        case 
            when RL.MKTPACKAGEID is not null
                then (
                        select COMMUNICATIONLETTER.NAME
                        from dbo.COMMUNICATIONLETTER
                        where COMMUNICATIONLETTER.EMAILPACKAGEID = RL.MKTPACKAGEID
                            or COMMUNICATIONLETTER.MAILPACKAGEID = RL.MKTPACKAGEID
                        )
            else AP.[NAME] + case 
                    when AP.[NAME] is not null
                        and AP.[NAME] <> ''
                        and LC.[NAME] is not null
                        and LC.[NAME] <> ''
                        then ' - '
                    else ''
                    end + LC.[NAME]
            end,
        case 
            when AU.USERNAME is not null
                then AU.USERNAME
            else CA.[USERNAME]
            end,
        '',
        '',
        0,
        convert(bit, 0),
        '7C0968B7-61C7-44EA-8F8D-96C139A42AD0',
        convert(nchar(36), R.[ID]) as PREVIEWDATAFORMRECORDID,
        BPI.[SITEID] as SITEID,
        2 as SITEENFORCEMENT,
        coalesce(MKTPACKAGE.CHANNELCODE, 0) as CHANNEL
    from dbo.[REVENUELETTER] RL
    left join dbo.[LETTERCODE] LC
        on LC.[ID] = RL.[LETTERCODEID]
    left join dbo.[ACKNOWLEDGEMENTPROCESSSTATUS] APS
        on APS.[ID] = RL.[ACKNOWLEDGEMENTPROCESSSTATUSID]
    left join dbo.[BUSINESSPROCESSSTATUS] BPS
        on BPS.[ID] = RL.[ACKNOWLEDGEMENTPROCESSSTATUSID]
    left join dbo.[ACKNOWLEDGEMENTPROCESS] AP
        on AP.[ID] = APS.[PARAMETERSETID]
    left join dbo.[REVENUE] R
        on R.[ID] = RL.[REVENUEID]
    left join dbo.[APPUSER] AU
        on AU.[ID] = BPS.[STARTEDBYUSERID]
    left join dbo.[CHANGEAGENT] CA
        on CA.[ID] = RL.[ADDEDBYID]
    left join dbo.BUSINESSPROCESSINSTANCE BPI
        on AP.ID = BPI.BUSINESSPROCESSPARAMETERSETID
    left join dbo.MKTPACKAGE
        on RL.MKTPACKAGEID = MKTPACKAGE.ID
    where R.CONSTITUENTID = @CONSTITUENTID
        and RL.[ACKNOWLEDGEDATE] is not null
        and (
            @CORRESPONDENCETYPECODE = 1
            or @CORRESPONDENCETYPECODE = 99
            )
        and (
            RL.[ACKNOWLEDGEDATE] >= @STARTDATE
            and RL.[ACKNOWLEDGEDATE] <= @ENDDATE
            )
        and (
            @CHANNEL = 99
            or MKTPACKAGE.ID is null
            or MKTPACKAGE.CHANNELCODE = @CHANNEL
            )

    union all

    -- group member receipts

    select convert(nchar(36), R.[ID]),
        R.CONSTITUENTID,
        RECEIPT.[RECEIPTDATE],
        2,
        'Receipt',
        RP.[NAME],
        AU.USERNAME,
        '',
        '',
        0,
        convert(bit, 0),
        '44E05002-D3C8-491F-AE69-C71594AE8C4C',
        convert(nchar(36), R.[ID]) as PREVIEWDATAFORMRECORDID,
        BPI.[SITEID] as SITEID,
        2 as SITEENFORCEMENT,
        99 as CHANNEL
    from dbo.[REVENUE] R
    inner join dbo.[REVENUERECEIPT] RECEIPT
        on R.ID = RECEIPT.REVENUEID
    left join dbo.[RECEIPTINGPROCESSSTATUS] RPS
        on RPS.[ID] = RECEIPT.[RECEIPTINGPROCESSSTATUSID]
    left join dbo.[BUSINESSPROCESSSTATUS] BPS
        on BPS.[ID] = RECEIPT.[RECEIPTINGPROCESSSTATUSID]
    left join dbo.[APPUSER] AU
        on AU.[ID] = BPS.[STARTEDBYUSERID]
    left join dbo.[RECEIPTINGPROCESS] RP
        on RP.[ID] = RPS.[PARAMETERSETID]
    inner join dbo.GROUPMEMBER GM
        on R.CONSTITUENTID = GM.MEMBERID
    left join dbo.BUSINESSPROCESSINSTANCE BPI
        on RP.ID = BPI.BUSINESSPROCESSPARAMETERSETID
    where GM.GROUPID = @CONSTITUENTID
        and RECEIPT.[RECEIPTDATE] is not null
        and (
            @CORRESPONDENCETYPECODE = 2
            or @CORRESPONDENCETYPECODE = 99
            )
        and (
            RECEIPT.[RECEIPTDATE] >= @STARTDATE
            and RECEIPT.[RECEIPTDATE] <= @ENDDATE
            )
        and @CHANNEL = 99

    union all

    -- group receipts

    select convert(nchar(36), R.[ID]),
        R.CONSTITUENTID,
        RECEIPT.[RECEIPTDATE],
        2,
        'Receipt',
        RP.[NAME],
        AU.USERNAME,
        '',
        '',
        0,
        convert(bit, 0),
        '44E05002-D3C8-491F-AE69-C71594AE8C4C',
        convert(nchar(36), R.[ID]) as PREVIEWDATAFORMRECORDID,
        BPI.[SITEID] as SITEID,
        2 as SITEENFORCEMENT,
        99 as CHANNEL
    from dbo.[REVENUE] R
    inner join dbo.[REVENUERECEIPT] RECEIPT
        on R.ID = RECEIPT.REVENUEID
    left join dbo.[RECEIPTINGPROCESSSTATUS] RPS
        on RPS.[ID] = RECEIPT.[RECEIPTINGPROCESSSTATUSID]
    left join dbo.[BUSINESSPROCESSSTATUS] BPS
        on BPS.[ID] = RECEIPT.[RECEIPTINGPROCESSSTATUSID]
    left join dbo.[APPUSER] AU
        on AU.[ID] = BPS.[STARTEDBYUSERID]
    left join dbo.[RECEIPTINGPROCESS] RP
        on RP.[ID] = RPS.[PARAMETERSETID]
    left join dbo.BUSINESSPROCESSINSTANCE BPI
        on RP.ID = BPI.BUSINESSPROCESSPARAMETERSETID
    where R.CONSTITUENTID = @CONSTITUENTID
        and RECEIPT.[RECEIPTDATE] is not null
        and (
            @CORRESPONDENCETYPECODE = 2
            or @CORRESPONDENCETYPECODE = 99
            )
        and (
            RECEIPT.[RECEIPTDATE] >= @STARTDATE
            and RECEIPT.[RECEIPTDATE] <= @ENDDATE
            )
        and @CHANNEL = 99

    union all

    -- group member reminders

    select convert(nchar(36), R.[ID]),
        R.CONSTITUENTID,
        PRS.[SENTDATE],
        3,
        'Reminder',
        PRP.[NAME],
        case 
            when AU.USERNAME is not null
                then AU.USERNAME
            else CA.[USERNAME]
            end,
        '',
        '',
        0,
        convert(bit, 0),
        'A5F58EB7-5775-4B6F-9C69-CD91CD5A0103',
        convert(nchar(36), R.[ID]) as PREVIEWDATAFORMRECORDID,
        BPI.[SITEID] as SITEID,
        2 as SITEENFORCEMENT,
        coalesce(MKTPACKAGE.CHANNELCODE, 0) as CHANNEL
    from dbo.[PLEDGEREMINDERSENT] PRS
    left join dbo.[PLEDGEREMINDERPROCESSSTATUS] PRPS
        on PRPS.[ID] = PRS.[PLEDGEREMINDERPROCESSSTATUSID]
    left join dbo.[BUSINESSPROCESSSTATUS] BPS
        on BPS.[ID] = PRS.[PLEDGEREMINDERPROCESSSTATUSID]
    left join dbo.[PLEDGEREMINDERPROCESS] PRP
        on PRP.[ID] = PRPS.[PARAMETERSETID]
    left join dbo.[REVENUE] R
        on R.[ID] = PRS.[REVENUEID]
    left join dbo.[APPUSER] AU
        on AU.[ID] = BPS.[STARTEDBYUSERID]
    left join dbo.[CHANGEAGENT] CA
        on CA.[ID] = PRS.[ADDEDBYID]
    inner join dbo.GROUPMEMBER GM
        on R.CONSTITUENTID = GM.MEMBERID
    left join dbo.BUSINESSPROCESSINSTANCE BPI
        on PRP.ID = BPI.BUSINESSPROCESSPARAMETERSETID
    left join dbo.MKTPACKAGE
        on PRS.PACKAGEID = MKTPACKAGE.ID
    where GM.GROUPID = @CONSTITUENTID
        and PRS.[SENTDATE] is not null
        and (
            @CORRESPONDENCETYPECODE = 3
            or @CORRESPONDENCETYPECODE = 99
            )
        and (
            PRS.[SENTDATE] >= @STARTDATE
            and PRS.[SENTDATE] <= @ENDDATE
            )
        and (
            @CHANNEL = 99
            or MKTPACKAGE.ID is null
            or MKTPACKAGE.CHANNELCODE = @CHANNEL
            )

    union all

    -- group reminders

    select convert(nchar(36), R.[ID]),
        R.CONSTITUENTID,
        PRS.[SENTDATE],
        3,
        'Reminder',
        PRP.[NAME],
        case 
            when AU.USERNAME is not null
                then AU.USERNAME
            else CA.[USERNAME]
            end,
        '',
        '',
        0,
        convert(bit, 0),
        'A5F58EB7-5775-4B6F-9C69-CD91CD5A0103',
        convert(nchar(36), R.[ID]) as PREVIEWDATAFORMRECORDID,
        BPI.[SITEID] as SITEID,
        2 as SITEENFORCEMENT,
        coalesce(MKTPACKAGE.CHANNELCODE, 0) as CHANNEL
    from dbo.[PLEDGEREMINDERSENT] PRS
    left join dbo.[PLEDGEREMINDERPROCESSSTATUS] PRPS
        on PRPS.[ID] = PRS.[PLEDGEREMINDERPROCESSSTATUSID]
    left join dbo.[BUSINESSPROCESSSTATUS] BPS
        on BPS.[ID] = PRS.[PLEDGEREMINDERPROCESSSTATUSID]
    left join dbo.[PLEDGEREMINDERPROCESS] PRP
        on PRP.[ID] = PRPS.[PARAMETERSETID]
    left join dbo.[REVENUE] R
        on R.[ID] = PRS.[REVENUEID]
    left join dbo.[APPUSER] AU
        on AU.[ID] = BPS.[STARTEDBYUSERID]
    left join dbo.[CHANGEAGENT] CA
        on CA.[ID] = PRS.[ADDEDBYID]
    left join dbo.BUSINESSPROCESSINSTANCE BPI
        on PRP.ID = BPI.BUSINESSPROCESSPARAMETERSETID
    left join dbo.MKTPACKAGE
        on PRS.PACKAGEID = MKTPACKAGE.ID
    where R.CONSTITUENTID = @CONSTITUENTID
        and PRS.[SENTDATE] is not null
        and (
            @CORRESPONDENCETYPECODE = 3
            or @CORRESPONDENCETYPECODE = 99
            )
        and (
            PRS.[SENTDATE] >= @STARTDATE
            and PRS.[SENTDATE] <= @ENDDATE
            )
        and (
            @CHANNEL = 99
            or MKTPACKAGE.ID is null
            or MKTPACKAGE.CHANNELCODE = @CHANNEL
            )

    union all

    -- group member planned gift acknowledgements

    select convert(nchar(36), PG.[ID]),
        PG.CONSTITUENTID,
        PGL.[ACKNOWLEDGEDATE],
        4,
        'Planned Gift Acknowledgement',
        PGAP.[NAME] + case 
            when PGAP.[NAME] is not null
                and PGAP.[NAME] <> ''
                and PGLC.[NAME] is not null
                and PGLC.[NAME] <> ''
                then ' - '
            else ''
            end + PGLC.[NAME],
        case 
            when AU.USERNAME is not null
                then AU.USERNAME
            else CA.[USERNAME]
            end,
        '',
        '',
        0,
        convert(bit, 0),
        'F0E64049-598A-4900-8404-343C63DE7405',
     convert(nchar(36), PG.[ID]) as PREVIEWDATAFORMRECORDID,
        BPI.[SITEID] as SITEID,
        2 as SITEENFORCEMENT,
        99 as CHANNEL
    from dbo.[PLANNEDGIFTLETTER] PGL
    left join dbo.[PLANNEDGIFTLETTERCODE] PGLC
        on PGLC.[ID] = PGL.[PLANNEDGIFTLETTERCODEID]
    left join dbo.[PLANNEDGIFTACKNOWLEDGEMENTPROCESSSTATUS] PGAPS
        on PGAPS.[ID] = PGL.[PLANNEDGIFTACKNOWLEDGEMENTPROCESSSTATUSID]
    left join dbo.[BUSINESSPROCESSSTATUS] BPS
        on BPS.[ID] = PGL.[PLANNEDGIFTACKNOWLEDGEMENTPROCESSSTATUSID]
    left join dbo.[PLANNEDGIFTACKNOWLEDGEMENTPROCESS] PGAP
        on PGAP.[ID] = PGAPS.[PARAMETERSETID]
    left join dbo.[PLANNEDGIFT] PG
        on PG.[ID] = PGL.[PLANNEDGIFTID]
    left join dbo.[APPUSER] AU
        on AU.[ID] = BPS.[STARTEDBYUSERID]
    left join dbo.[CHANGEAGENT] CA
        on CA.[ID] = PGL.[ADDEDBYID]
    inner join dbo.GROUPMEMBER GM
        on PG.CONSTITUENTID = GM.MEMBERID
    left join dbo.BUSINESSPROCESSINSTANCE BPI
        on PGAP.ID = BPI.BUSINESSPROCESSPARAMETERSETID
    where GM.GROUPID = @CONSTITUENTID
        and PGL.[ACKNOWLEDGEDATE] is not null
        and (
            @CORRESPONDENCETYPECODE = 4
            or @CORRESPONDENCETYPECODE = 99
            )
        and (
            PGL.[ACKNOWLEDGEDATE] >= @STARTDATE
            and PGL.[ACKNOWLEDGEDATE] <= @ENDDATE
            )
        and @CHANNEL = 99

    union all

    -- group planned gift acknowledgements

    select convert(nchar(36), PG.[ID]),
        PG.CONSTITUENTID,
        PGL.[ACKNOWLEDGEDATE],
        4,
        'Planned Gift Acknowledgement',
        PGAP.[NAME] + case 
            when PGAP.[NAME] is not null
                and PGAP.[NAME] <> ''
                and PGLC.[NAME] is not null
                and PGLC.[NAME] <> ''
                then ' - '
            else ''
            end + PGLC.[NAME],
        case 
            when AU.USERNAME is not null
                then AU.USERNAME
            else CA.[USERNAME]
            end,
        '',
        '',
        0,
        convert(bit, 0),
        'F0E64049-598A-4900-8404-343C63DE7405',
        convert(nchar(36), PG.[ID]) as PREVIEWDATAFORMRECORDID,
        BPI.[SITEID] as SITEID,
        2 as SITEENFORCEMENT,
        99 as CHANNEL
    from dbo.[PLANNEDGIFTLETTER] PGL
    left join dbo.[PLANNEDGIFTLETTERCODE] PGLC
        on PGLC.[ID] = PGL.[PLANNEDGIFTLETTERCODEID]
    left join dbo.[PLANNEDGIFTACKNOWLEDGEMENTPROCESSSTATUS] PGAPS
        on PGAPS.[ID] = PGL.[PLANNEDGIFTACKNOWLEDGEMENTPROCESSSTATUSID]
    left join dbo.[BUSINESSPROCESSSTATUS] BPS
        on BPS.[ID] = PGL.[PLANNEDGIFTACKNOWLEDGEMENTPROCESSSTATUSID]
    left join dbo.[PLANNEDGIFTACKNOWLEDGEMENTPROCESS] PGAP
        on PGAP.[ID] = PGAPS.[PARAMETERSETID]
    left join dbo.[PLANNEDGIFT] PG
        on PG.[ID] = PGL.[PLANNEDGIFTID]
    left join dbo.[APPUSER] AU
        on AU.[ID] = BPS.[STARTEDBYUSERID]
    left join dbo.[CHANGEAGENT] CA
        on CA.[ID] = PGL.[ADDEDBYID]
    left join dbo.BUSINESSPROCESSINSTANCE BPI
        on PGAP.ID = BPI.BUSINESSPROCESSPARAMETERSETID
    where PG.CONSTITUENTID = @CONSTITUENTID
        and PGL.[ACKNOWLEDGEDATE] is not null
        and (
            @CORRESPONDENCETYPECODE = 4
            or @CORRESPONDENCETYPECODE = 99
            )
        and (
            PGL.[ACKNOWLEDGEDATE] >= @STARTDATE
            and PGL.[ACKNOWLEDGEDATE] <= @ENDDATE
            )
        and @CHANNEL = 99

    union all

    -- group member event invitations

    select upper(IVH.[PARAMETERSETID]) + convert(nchar(36), IVH.[EVENTID]),
        IH.CONSTITUENTID,
        isnull(IVH.MAILDATE, IVH.[DATECHANGED]),
        5,
        'Event Invitation',
        IVH.[NAME] + case 
            when IVH.[NAME] is not null
                and IVH.[NAME] <> ''
                and E.[NAME] is not null
                and E.[NAME] <> ''
                then ' - '
            else ''
            end + E.[NAME],
        case 
            when AU.USERNAME is not null
                then AU.USERNAME
            else CA.[USERNAME]
            end,
        '',
        '',
        0,
        convert(bit, 0),
        'D11CEB65-90F4-4152-9066-F9631835FD8D',
        upper(IVH.[PARAMETERSETID]) + convert(nchar(36), IVH.[EVENTID]) as PREVIEWDATAFORMRECORDID,
        null as SITEID,
        0 as SITEENFORCEMENT,
        coalesce(MKTPACKAGE.CHANNELCODE, - 1) as CHANNEL
    from dbo.[INVITEEHISTORY] IH
    left join dbo.[INVITATIONHISTORY] IVH
        on IVH.[ID] = IH.[INVITATIONHISTORYID]
    left join dbo.[BUSINESSPROCESSSTATUS] BPS
        on BPS.[ID] = IVH.[BUSINESSPROCESSSTATUSID]
    left join dbo.[EVENT] E
        on E.[ID] = IH.[EVENTID]
    left join dbo.[APPUSER] AU
        on AU.[ID] = BPS.[STARTEDBYUSERID]
    left join dbo.[CHANGEAGENT] CA
        on CA.[ID] = IH.[ADDEDBYID]
    inner join dbo.GROUPMEMBER GM
        on IH.CONSTITUENTID = GM.MEMBERID
    left join dbo.MKTPACKAGE
        on IH.MKTPACKAGEID = MKTPACKAGE.ID
    where GM.GROUPID = @CONSTITUENTID
        and (
            @CORRESPONDENCETYPECODE = 5
            or @CORRESPONDENCETYPECODE = 99
            )
        and (
            IVH.[DATECHANGED] >= @STARTDATE
            and IVH.[DATECHANGED] <= @ENDDATE
            )
        and (
            @CHANNEL = 99
            or MKTPACKAGE.CHANNELCODE = @CHANNEL
            )

    union all

    -- group event invitations

    select upper(IVH.[PARAMETERSETID]) + convert(nchar(36), IVH.[EVENTID]),
        IH.CONSTITUENTID,
        isnull(IVH.MAILDATE, IVH.[DATECHANGED]),
        5,
        'Event Invitation',
        IVH.[NAME] + case 
            when IVH.[NAME] is not null
                and IVH.[NAME] <> ''
                and E.[NAME] is not null
                and E.[NAME] <> ''
                then ' - '
            else ''
            end + E.[NAME],
        case 
            when AU.USERNAME is not null
                then AU.USERNAME
            else CA.[USERNAME]
            end,
        '',
        '',
        0,
        convert(bit, 0),
        'D11CEB65-90F4-4152-9066-F9631835FD8D',
        upper(IVH.[PARAMETERSETID]) + convert(nchar(36), IVH.[EVENTID]) as PREVIEWDATAFORMRECORDID,
        null as SITEID,
        0 as SITEENFORCEMENT,
        coalesce(MKTPACKAGE.CHANNELCODE, - 1) as CHANNEL
    from dbo.[INVITEEHISTORY] IH
    left join dbo.[INVITATIONHISTORY] IVH
        on IVH.[ID] = IH.[INVITATIONHISTORYID]
    left join dbo.[BUSINESSPROCESSSTATUS] BPS
        on BPS.[ID] = IVH.[BUSINESSPROCESSSTATUSID]
    left join dbo.[EVENT] E
        on E.[ID] = IH.[EVENTID]
    left join dbo.[APPUSER] AU
        on AU.[ID] = BPS.[STARTEDBYUSERID]
    left join dbo.[CHANGEAGENT] CA
        on CA.[ID] = IH.[ADDEDBYID]
    left join dbo.MKTPACKAGE
        on IH.MKTPACKAGEID = MKTPACKAGE.ID
    where IH.CONSTITUENTID = @CONSTITUENTID
        and (
            @CORRESPONDENCETYPECODE = 5
            or @CORRESPONDENCETYPECODE = 99
            )
        and (
            IVH.[DATECHANGED] >= @STARTDATE
            and IVH.[DATECHANGED] <= @ENDDATE
            )
        and (
            @CHANNEL = 99
            or MKTPACKAGE.CHANNELCODE = @CHANNEL
            )

    union all

    -- group member appeal mailings

    select convert(nchar(36), MS.[ID]),
        CA.CONSTITUENTID,
        CA.[DATESENT],
        case 
            when MS.MAILINGTYPECODE = 2
                then 8
            when MS.MAILINGTYPECODE = 0
                and APPEALMAILING.ID is not null
                then 9
            else 6
            end as [CORRESPONDANCETYPECODE],
        case 
            when MS.MAILINGTYPECODE = 2
                then 'Membership Renewal'
            else 'Appeal Mailing'
            end as [CORRESPONDANCETYPE],
        A.[NAME] + case 
            when A.[NAME] is not null
                and A.[NAME] <> ''
                and MS.[NAME] is not null
                and MS.[NAME] <> ''
                then ' - '
            else ''
            end + coalesce(MS.[NAME], ''),
        CHA.[USERNAME],
        CA.[COMMENTS],
        convert(nchar(36), CA.[ID]),
        CA.[FINDERNUMBER],
        case 
            when exists (
                    select 1
                    from dbo.CONSTITUENTAPPEALRESPONSE
                    where CONSTITUENTAPPEALRESPONSE.CONSTITUENTAPPEALID = CA.ID
                    )
                then convert(bit, 1)
            else convert(bit, 0)
            end HASRESPONSES,
        'F37F65F4-B9BC-4F70-9FF9-F40493EBAE6C',
        convert(nchar(36), CA.[ID]) as PREVIEWDATAFORMRECORDID,
        A.[SITEID] as SITEID,
        1 as SITEENFORCEMENT,
        coalesce(PAC.CHANNELCODE, 0) as CHANNEL
    from dbo.[CONSTITUENTAPPEAL] CA
    left join dbo.[APPEAL] A
        on A.[ID] = CA.[APPEALID]
    left join dbo.[MKTSEGMENTATION] MS
        on MS.[ID] = CA.[MKTSEGMENTATIONID]
    left join dbo.[CHANGEAGENT] CHA
        on CHA.[ID] = CA.[ADDEDBYID]
    inner join dbo.GROUPMEMBER GM
        on CA.CONSTITUENTID = GM.MEMBERID
    left join dbo.APPEALMAILING
        on APPEALMAILING.ID = MS.ID
    left join dbo.MKTPACKAGE PAC
        on PAC.ID = CA.MKTPACKAGEID
    where GM.GROUPID = @CONSTITUENTID
        and (
            (
                @CORRESPONDENCETYPECODE = 6
                and isnull(MS.MAILINGTYPECODE, 0) = 0
                )
            or (
                @CORRESPONDENCETYPECODE = 8
                and MS.MAILINGTYPECODE = 2
                )
            or @CORRESPONDENCETYPECODE = 99
            )
        and (
            (
                CA.[DATESENT] >= @STARTDATE
                and CA.[DATESENT] <= @ENDDATE
                )
            or CA.[DATESENT] is null
            )
        and (
            @CHANNEL = 99
            or PAC.ID is null
            or PAC.CHANNELCODE = @CHANNEL
            )

    union all

    -- group appeal mailings

    select convert(nchar(36), MS.[ID]),
        CA.CONSTITUENTID,
        CA.[DATESENT],
        case 
            when MS.MAILINGTYPECODE = 2
                then 8
            when MS.MAILINGTYPECODE = 0
                and APPEALMAILING.ID is not null
                then 9
            else 6
            end as [CORRESPONDANCETYPECODE],
        case 
            when MS.MAILINGTYPECODE = 2
                then 'Membership Renewal'
            else 'Appeal Mailing'
            end as [CORRESPONDANCETYPE],
        A.[NAME] + case 
            when A.[NAME] is not null
                and A.[NAME] <> ''
                and MS.[NAME] is not null
                and MS.[NAME] <> ''
                then ' - '
            else ''
            end + coalesce(MS.[NAME], ''),
        CHA.[USERNAME],
        CA.[COMMENTS],
        convert(nchar(36), CA.[ID]),
        CA.[FINDERNUMBER],
        case 
            when exists (
                    select 1
                    from dbo.CONSTITUENTAPPEALRESPONSE
                    where CONSTITUENTAPPEALRESPONSE.CONSTITUENTAPPEALID = CA.ID
                    )
                then convert(bit, 1)
            else convert(bit, 0)
            end HASRESPONSES,
        'F37F65F4-B9BC-4F70-9FF9-F40493EBAE6C',
        convert(nchar(36), CA.[ID]) as PREVIEWDATAFORMRECORDID,
        A.[SITEID] as SITEID,
        1 as SITEENFORCEMENT,
        coalesce(PAC.CHANNELCODE, 0) as CHANNEL
    from dbo.[CONSTITUENTAPPEAL] CA
    left join dbo.[APPEAL] A
        on A.[ID] = CA.[APPEALID]
    left join dbo.[MKTSEGMENTATION] MS
        on MS.[ID] = CA.[MKTSEGMENTATIONID]
    left join dbo.[CHANGEAGENT] CHA
        on CHA.[ID] = CA.[ADDEDBYID]
    left join dbo.APPEALMAILING
        on APPEALMAILING.ID = MS.ID
    left join dbo.MKTPACKAGE PAC
        on PAC.ID = CA.MKTPACKAGEID
    where CA.CONSTITUENTID = @CONSTITUENTID
        and (
            (
                @CORRESPONDENCETYPECODE = 6
                and isnull(MS.MAILINGTYPECODE, 0) = 0
                )
            or (
                @CORRESPONDENCETYPECODE = 8
                and MS.MAILINGTYPECODE = 2
                )
            or @CORRESPONDENCETYPECODE = 99
            )
        and (
            (
                CA.[DATESENT] >= @STARTDATE
                and CA.[DATESENT] <= @ENDDATE
                )
            or CA.[DATESENT] is null
            )
        and (
            @CHANNEL = 99
            or PAC.ID is null
            or PAC.CHANNELCODE = @CHANNEL
            )

    union all

    --Group member tribute acknowledgement

    select convert(nchar(36), [REVENUETRIBUTELETTER].[ID]) as ID,
        [REVENUETRIBUTELETTER].[CONSTITUENTID] as CONSTITUENTID,
        [REVENUETRIBUTELETTER].[ACKNOWLEDGEDATE] as DATESENT,
        7 as CORRESPONDENCETYPECODE,
        'Tribute Acknowledgement' as CORRESPONDENCETYPE,
        [TRIBUTEACKNOWLEDGEMENTPROCESS].[NAME] + case 
            when [TRIBUTEACKNOWLEDGEMENTPROCESS].[NAME] is not null
                and [TRIBUTEACKNOWLEDGEMENTPROCESS].[NAME] <> ''
                and [TRIBUTELETTERCODE].[NAME] is not null
                and [TRIBUTELETTERCODE].[NAME] <> ''
                then ' - '
            else ''
            end + [TRIBUTELETTERCODE].[NAME] as DETAILS,
        case 
            when [APPUSER].[USERNAME] is not null
                then [APPUSER].[USERNAME]
            else [CHANGEAGENT].[USERNAME]
            end as PROCESSOR,
        '' as COMMENTS,
        '' as RECORDID,
        0 as FINDERNUMBER,
        convert(bit, 0) as HASRESPONSES,
        '22A891A1-BF2D-4AA8-9580-BCFA066D4546' as PREVIEWDATAFORM,
        CONVERT(nchar(36), [REVENUE].[ID]) as PREVIEWDATAFORMRECORDID,
        [BUSINESSPROCESSINSTANCE].[SITEID] as SITEID,
        2 as SITEENFORCEMENT,
        99 as CHANNEL
    from dbo.[REVENUETRIBUTELETTER]
    left join dbo.[REVENUETRIBUTE]
        on [REVENUETRIBUTELETTER].[REVENUETRIBUTEID] = [REVENUETRIBUTE].[ID]
    left join dbo.[TRIBUTELETTERCODE]
        on [TRIBUTELETTERCODE].[ID] = [REVENUETRIBUTELETTER].[TRIBUTELETTERCODEID]
    left join dbo.[TRIBUTEACKNOWLEDGEMENTPROCESSSTATUS]
        on [TRIBUTEACKNOWLEDGEMENTPROCESSSTATUS].[ID] = [REVENUETRIBUTELETTER].[TRIBUTEACKNOWLEDGEMENTPROCESSSTATUSID]
    left join dbo.[BUSINESSPROCESSSTATUS]
        on [BUSINESSPROCESSSTATUS].[ID] = [REVENUETRIBUTELETTER].[TRIBUTEACKNOWLEDGEMENTPROCESSSTATUSID]
    left join dbo.[TRIBUTEACKNOWLEDGEMENTPROCESS]
        on [TRIBUTEACKNOWLEDGEMENTPROCESS].[ID] = [TRIBUTEACKNOWLEDGEMENTPROCESSSTATUS].[PARAMETERSETID]
    left join dbo.[REVENUE]
        on [REVENUE].[ID] = [REVENUETRIBUTE].[REVENUEID]
    left join dbo.[APPUSER]
        on [APPUSER].[ID] = [BUSINESSPROCESSSTATUS].[STARTEDBYUSERID]
    left join dbo.[CHANGEAGENT]
        on [CHANGEAGENT].[ID] = [REVENUETRIBUTELETTER].[ADDEDBYID]
    left join dbo.[BUSINESSPROCESSINSTANCE]
        on [TRIBUTEACKNOWLEDGEMENTPROCESS].[ID] = [BUSINESSPROCESSINSTANCE].[BUSINESSPROCESSPARAMETERSETID]
    inner join dbo.[GROUPMEMBER]
        on [REVENUETRIBUTELETTER].[CONSTITUENTID] = [GROUPMEMBER].[MEMBERID]
    where [GROUPMEMBER].[GROUPID] = @CONSTITUENTID
        and [REVENUETRIBUTELETTER].[ACKNOWLEDGEDATE] is not null
        and (
            @CORRESPONDENCETYPECODE = 7
            or @CORRESPONDENCETYPECODE = 99
            )
        and (
            [REVENUETRIBUTELETTER].[ACKNOWLEDGEDATE] >= @STARTDATE
            and [REVENUETRIBUTELETTER].[ACKNOWLEDGEDATE] <= @ENDDATE
            )
        and @CHANNEL = 99

    union all

    --group tribute acknowledgement

    select convert(nchar(36), [REVENUETRIBUTELETTER].[ID]) as ID,
        [REVENUETRIBUTELETTER].[CONSTITUENTID] as CONSTITUENTID,
        [REVENUETRIBUTELETTER].[ACKNOWLEDGEDATE] as DATESENT,
        7 as CORRESPONDENCETYPECODE,
        'Tribute Acknowledgement' as CORRESPONDENCETYPE,
        [TRIBUTEACKNOWLEDGEMENTPROCESS].[NAME] + case 
            when [TRIBUTEACKNOWLEDGEMENTPROCESS].[NAME] is not null
                and [TRIBUTEACKNOWLEDGEMENTPROCESS].[NAME] <> ''
                and [TRIBUTELETTERCODE].[NAME] is not null
                and [TRIBUTELETTERCODE].[NAME] <> ''
                then ' - '
            else ''
            end + [TRIBUTELETTERCODE].[NAME] as DETAILS,
        case 
            when [APPUSER].USERNAME is not null
                then [APPUSER].USERNAME
            else [CHANGEAGENT].[USERNAME]
            end as PROCESSOR,
        '' as COMMENTS,
        '' as RECORDID,
        0 as FINDERNUMBER,
        convert(bit, 0) as HASRESPONSES,
        '22A891A1-BF2D-4AA8-9580-BCFA066D4546' as PREVIEWDATAFORM,
        CONVERT(nchar(36), [REVENUE].[ID]) as PREVIEWDATAFORMRECORDID,
        [BUSINESSPROCESSINSTANCE].SITEID as [SITEID],
        2 as SITEENFORCEMENT,
        99 as CHANNEL
    from dbo.[REVENUETRIBUTELETTER]
    left join dbo.[REVENUETRIBUTE]
        on [REVENUETRIBUTELETTER].[REVENUETRIBUTEID] = [REVENUETRIBUTE].[ID]
    left join dbo.[TRIBUTELETTERCODE]
        on [TRIBUTELETTERCODE].[ID] = [REVENUETRIBUTELETTER].[TRIBUTELETTERCODEID]
    left join dbo.[TRIBUTEACKNOWLEDGEMENTPROCESSSTATUS] TAPS
        on TAPS.[ID] = [REVENUETRIBUTELETTER].[TRIBUTEACKNOWLEDGEMENTPROCESSSTATUSID]
    left join dbo.[BUSINESSPROCESSSTATUS]
        on [BUSINESSPROCESSSTATUS].[ID] = [REVENUETRIBUTELETTER].[TRIBUTEACKNOWLEDGEMENTPROCESSSTATUSID]
    left join dbo.[TRIBUTEACKNOWLEDGEMENTPROCESS]
        on [TRIBUTEACKNOWLEDGEMENTPROCESS].[ID] = TAPS.[PARAMETERSETID]
    left join dbo.[REVENUE]
        on [REVENUE].[ID] = [REVENUETRIBUTE].[REVENUEID]
    left join dbo.[APPUSER]
        on [APPUSER].[ID] = [BUSINESSPROCESSSTATUS].[STARTEDBYUSERID]
    left join dbo.[CHANGEAGENT]
        on [CHANGEAGENT].[ID] = [REVENUETRIBUTELETTER].[ADDEDBYID]
    left join dbo.[BUSINESSPROCESSINSTANCE]
        on [TRIBUTEACKNOWLEDGEMENTPROCESS].[ID] = [BUSINESSPROCESSINSTANCE].[BUSINESSPROCESSPARAMETERSETID]
    where [REVENUETRIBUTELETTER].[CONSTITUENTID] = @CONSTITUENTID
        and [REVENUETRIBUTELETTER].[ACKNOWLEDGEDATE] is not null
        and (
            @CORRESPONDENCETYPECODE = 7
            or @CORRESPONDENCETYPECODE = 99
            )
        and (
            [REVENUETRIBUTELETTER].[ACKNOWLEDGEDATE] >= @STARTDATE
            and [REVENUETRIBUTELETTER].[ACKNOWLEDGEDATE] <= @ENDDATE
            )
        and @CHANNEL = 99
    )
insert into @CORRESPONDENCES
select C.ID,
    C.CONSTITUENTID,
    C.DATESENT,
    NF.NAME CONSTITUENTNAME,
    C.CORRESPONDENCETYPECODE,
    C.CORRESPONDENCETYPE,
    C.DETAILS,
    C.PROCESSOR,
    C.COMMENTS,
    C.RECORDID,
    C.FINDERNUMBER,
    case 
        when C.DATESENT >= @THISMONTHSTART
            then 'This month'
        when C.DATESENT >= @LASTMONTHSTART
            then 'Last month'
        when C.DATESENT >= @TWOMONTHSAGOSTART
            then 'Two months ago'
        else 'Older'
    end TIMEFRAMETEXT,
    case 
        when C.DATESENT >= @THISMONTHSTART
            then '0'
        when C.DATESENT >= @LASTMONTHSTART
            then '2'
        when C.DATESENT >= @TWOMONTHSAGOSTART
            then '4'
        else '6'
    end TIMEFRAMEGROUPSORT,
    case 
        when @ISSYSADMIN = 1
            or @USERGRANTEDALLCONSTITUENTS = 1
            or CONSTITSECURITY.ID is not null
            then 1
        else 0
    end as 'HASPERMISSIONS',
    HASRESPONSES,
    PREVIEWDATAFORM,
    PREVIEWDATAFORMRECORDID,
    case C.SITEENFORCEMENT
        when 0
            then 'N/A' --Type not secured

        when 2
            then coalesce(SITE.NAME, 'All sites') --Type is site secured, but null site means record is unsecured

        else coalesce(SITE.NAME, '') --Normal site security

    end SITE,
    case C.CHANNEL
        when 0
            then 'Mail'
        when 1
            then 'Email'
        when 2
            then 'Phone'
        when -1
            then 'Unknown'
    end as CHANNEL
from INTERACTIONS C
-- Need to include the filter on GROUPID in the join predicate; otherwise, if we are viewing the correspondence of a household that is a member of multiple groups,

-- this would include the household's GROUPMEMBER records in the join, leading to multiple rows for each of the household's correspondence.

left join dbo.GROUPMEMBER GM
    on C.CONSTITUENTID = GM.MEMBERID
        and GM.GROUPID = @CONSTITUENTID
left join dbo.GROUPMEMBERDATERANGE GMDR
    on GM.ID = GMDR.GROUPMEMBERID
left join dbo.SITE
    on SITE.ID = C.SITEID
left join dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITIDS_FORQUERYVIEW(@CURRENTAPPUSERID, @QUERYVIEWID) CONSTITSECURITY
    on CONSTITSECURITY.ID = C.CONSTITUENTID
outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(C.CONSTITUENTID) NF
where (
        (
            GM.GROUPID = @CONSTITUENTID
            and (
                (
                    GMDR.DATEFROM is null
                    and (
                        GMDR.DATETO is null
                        or GMDR.DATETO >= C.DATESENT
                        )
                    )
                or (
                    GMDR.DATETO is null
                    and (
                        GMDR.DATEFROM is null
                        or GMDR.DATEFROM <= C.DATESENT
                        )
                    )
                or (
                    GMDR.DATEFROM <= C.DATESENT
                    and GMDR.DATETO >= C.DATESENT
                    )
                )
            )
        or (C.CONSTITUENTID = @CONSTITUENTID)
    )
    and (
        C.SITEENFORCEMENT = 0 --Type not site secured

        or (
            C.SITEENFORCEMENT = 2
            and C.SITEID is null
            ) --Type is site secured, but null site means record is unsecured

        or (
            @ISSYSADMIN = 1
            or exists
            (
              select 1 
              from dbo.[UFN_SITESFORUSERONFEATURE](@CURRENTAPPUSERID, @SECURITYFEATUREID, @SECURITYFEATURETYPE)
              where [SITEID]=[C].[SITEID] or ([SITEID] is null and [C].[SITEID] is null)
            )
        ) --Normal site security

    )
    and (
        --Apply site filter

        (@ISSYSADMIN = 1 and @SITEFILTERMODE <> 3)
        or @SITEFILTERMODE = 0
        or C.SITEID in (
            select SITEID
            from dbo.UFN_SITE_BUILDDATALISTSITEFILTER(@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED)
            )
    )
order by TIMEFRAMEGROUPSORT asc,
    DATESENT desc,
    CORRESPONDENCETYPECODE;

return;
end