UFN_CONSTITUENT_CORRESPONDENCE

CREATE function dbo.UFN_CONSTITUENT_CORRESPONDENCE
(
  @CONSTITUENTID uniqueidentifier,
  @CURRENTAPPUSERID uniqueidentifier,
  @SITEFILTERMODE tinyint,
  @SITESSELECTED xml,
  @SECURITYFEATUREID uniqueidentifier,
  @SECURITYFEATURETYPE tinyint,
  @CORRESPONDENCETYPECODE smallint,
  @STARTDATE datetime,
  @ENDDATE datetime,
  @CHANNEL smallint                        -- MKTPACKAGE.CHANNELCODE: 0 - Mail, 1 - Email, 2 - Phone, 3 - Public media...Only mail/email current filter values

)
returns table
as
return

  with CORRESPONDENCE_CTE as (
    -- community emails

    select
      convert(nchar(36),BBNC.[CONSTITUENTID]) ID,
      BBNC.[SENTDATE] DATESENT,
      case 
        when A.ID is null then 11 
        else 10
      end as CORRESPONDENCETYPECODE,
      case
        when A.ID is null then 'Online Confirmation'
        else 'Appeal Mailing' 
      end as CORRESPONDENCETYPE,
      BBNC.[EMAILNAME] as DETAILS,
      CA.[USERNAME] as PROCESSOR,
      '' as COMMENTS,
      '' as RECORDID,
      0 as FINDERNUMBER,
      @CONSTITUENTID as CONSTITUENTID,
      case 
        when BBNC.DONATED > 0 then convert(bit, 1)
        else convert(bit, 0) 
      end as HASRESPONSES,
      'c4479f6a-8b42-49fc-b302-e12215d05c12' as PREVIEWDATAFORM,
      convert(nchar(36),BBNC.[ID]) as PREVIEWDATAFORMRECORDID,
      A.SiteID as SITEID,
      case 
          when A.SITEID is null
              then 2 
          else 1 
      end as SITEENFORCEMENT,
      1 as CHANNEL,
    case when BBNC.BOUNCED = 1 or BBNC.SPAMCOMPLAINT =1 then 1 else 0 end as NOTSENT
  from
      dbo.[UFN_NETCOMMUNITYEMAILJOBRECIPIENT_EMAIL]() AS BBNC
        left join dbo.[BBNCAPPEALIDMAP] BBNCA on BBNCA.[ID] = BBNC.[APPEALID]
        left join dbo.[APPEAL] A on A.[ID] = BBNCA.[APPEALID]
        left join dbo.[CHANGEAGENT] CA on CA.[ID] = BBNC.[ADDEDBYID]
    where
        BBNC.[CONSTITUENTID] = @CONSTITUENTID 
      and [BBNC].[UPDATEDATE] is not null --Newsletter subscriptions can generate incomplete email job recipient rows 

        and ((@CORRESPONDENCETYPECODE = 6 and A.ID is not null) or (@CORRESPONDENCETYPECODE = 11 and A.ID is null) or @CORRESPONDENCETYPECODE = 99)
        and ((BBNC.[SENTDATE] >= @STARTDATE and BBNC.[SENTDATE] <= @ENDDATE) or BBNC.[SENTDATE] is null)
        and (@CHANNEL = 1 or @CHANNEL = 99) and BBNC.EMAILTYPEID not in (15, 9, 16) -- filter out test emails with email type BlastTest = 15 and ProjectAppealSeed = 9, and BBDM = 16 for bug 168851 


  union all

  select
      convert(nchar(36),C.[ID]) ID,
      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,
      @CONSTITUENTID as CONSTITUENTID,
      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' PREVIEWDATAFORM,
      convert(nchar(36),C.[ID]) as PREVIEWDATAFORMRECORDID,
                CC.[SITEID] as SITEID,
      case 
          when CC.ID is null 
              then 0
          else 1 
      end as SITEENFORCEMENT,
      99 as CHANNEL,
    0 as NOTSENT
  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 @CHANNEL = 99

  union all

  select
      convert(nchar(36),R.[ID]),
      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,
      @CONSTITUENTID as CONSTITUENTID,
      convert(bit,0),
      '23c2a927-de76-4faa-b8c2-d4579f36c4fb',
      convert(nchar(36),RL.[ID]) as PREVIEWDATAFORMRECORDID,
                BPI.[SITEID] as SITEID,
      case 
          when BPI.SITEID is null 
              then 2 
          else 1 
      end as SITEENFORCEMENT,
      coalesce(MKTPACKAGE.CHANNELCODE, 99) as CHANNEL,
    0 as NOTSENT
  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
      RL.[ACKNOWLEDGEEID] = @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.CHANNELCODE = @CHANNEL)

  union all

  select
      convert(nchar(36),R.[ID]),
      RECEIPT.[RECEIPTDATE],
      2,
      'Receipt',
      RP.[NAME],
      AU.USERNAME,
      '',
      '',
      0,
      @CONSTITUENTID as CONSTITUENTID,
      convert(bit,0),
      '44E05002-D3C8-491F-AE69-C71594AE8C4C',
      convert(nchar(36),R.[ID]) as PREVIEWDATAFORMRECORDID,
                BPI.[SITEID] as SITEID,
      case 
          when BPI.SITEID is null 
              then 2 
          else 1 
      end as SITEENFORCEMENT,
      99 as CHANNEL,
    0 as NOTSENT
  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

  select
      convert(nchar(36),R.[ID]),
      PRS.[SENTDATE],
      3,
      'Reminder',
      PRP.[NAME],
      case when AU.USERNAME is not null then AU.USERNAME else CA.[USERNAME] end,
      '',
      '',
      0,
      @CONSTITUENTID as CONSTITUENTID,
      convert(bit,0),
      'A5F58EB7-5775-4B6F-9C69-CD91CD5A0103',
      convert(nchar(36),R.[ID]) as PREVIEWDATAFORMRECORDID,
                BPI.[SITEID] as SITEID,
      case 
          when BPI.SITEID is null 
              then 2 
          else 1 
      end as SITEENFORCEMENT,
      coalesce(MKTPACKAGE.CHANNELCODE, 99) as CHANNEL,
    0 as NOTSENT
  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.CHANNELCODE = @CHANNEL)            

  union all

  select
      convert(nchar(36),PG.[ID]),
      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,
      @CONSTITUENTID as CONSTITUENTID,
      convert(bit,0),
      '33afa0f2-b15a-45e3-9c91-dec2c2ef94eb',
      convert(nchar(36),PGL.[ID]) as PREVIEWDATAFORMRECORDID,
                BPI.[SITEID] as SITEID,
      case 
          when BPI.SITEID is null 
              then 2 
          else 1 
      end as SITEENFORCEMENT,
      99 as CHANNEL,
    0 as NOTSENT
  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

  select
      upper(IVH.[PARAMETERSETID]),
      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,
      @CONSTITUENTID as CONSTITUENTID,
      convert(bit,0),
      '0084565a-9858-4670-ade0-7381b11d304b',
      upper(IH.ID) as PREVIEWDATAFORMRECORDID,
                null as SITEID,
      0 as SITEENFORCEMENT,
      coalesce(MKTPACKAGE.CHANNELCODE, -1) as CHANNEL,
    0 as NOTSENT
  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

  -- Appeal and membership mailings

  select
      convert(nchar(36),MS.[ID]),
      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], '') as [DETAILS],
      CHA.[USERNAME],
      CA.[COMMENTS],
      convert(nchar(36),CA.[ID]),
      CA.[FINDERNUMBER],
      @CONSTITUENTID as CONSTITUENTID,
      case when exists(select 1 from dbo.CONSTITUENTAPPEALRESPONSE where CONSTITUENTAPPEALRESPONSE.CONSTITUENTAPPEALID=CA.ID)
      then convert(bit,1) else convert(bit,0) end,
      'F37F65F4-B9BC-4F70-9FF9-F40493EBAE6C',
      convert(nchar(36),CA.[ID]) as PREVIEWDATAFORMRECORDID,
      A.SITEID as SITEID,
      case 
          when A.SITEID is null
              then 2 
          else 1 
      end as SITEENFORCEMENT,
      coalesce(PAC.CHANNELCODE, 99) as CHANNEL,
    0 as NOTSENT
  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.CHANNELCODE = @CHANNEL)

  union all

  select
      convert(nchar(36),R.[ID]),
      RTL.[ACKNOWLEDGEDATE],
      7,
      'Tribute Acknowledgement',
      TAP.[NAME] + case when TAP.[NAME] is not null and TAP.[NAME] <> '' and TLC.[NAME] is not null and TLC.[NAME] <> '' then ' - ' else '' end + TLC.[NAME],
      case when AU.USERNAME is not null then AU.USERNAME else CA.[USERNAME] end,
      '',
      '',
      0,
      @CONSTITUENTID as CONSTITUENTID,
      convert(bit,0),
      'f4dcc1d3-0c5b-478c-aa6d-6ec8ea36a2b2',
      convert(nchar(36),RTL.[ID]) as PREVIEWDATAFORMRECORDID,
                BPI.[SITEID] as SITEID,
      case 
          when BPI.SITEID is null 
              then 2 
          else 1 
      end as SITEENFORCEMENT,
      99 as CHANNEL,
    0 as NOTSENT
  from
      dbo.[REVENUETRIBUTELETTER] RTL
      left join dbo.[REVENUETRIBUTE] RT on RTL.[REVENUETRIBUTEID] = RT.[ID]
      left join dbo.[TRIBUTELETTERCODE] TLC on TLC.[ID] = RTL.[TRIBUTELETTERCODEID]
      left join dbo.[TRIBUTEACKNOWLEDGEMENTPROCESSSTATUS] TAPS on TAPS.[ID] = RTL.[TRIBUTEACKNOWLEDGEMENTPROCESSSTATUSID]
      left join dbo.[BUSINESSPROCESSSTATUS] BPS on BPS.[ID] = RTL.[TRIBUTEACKNOWLEDGEMENTPROCESSSTATUSID]
      left join dbo.[TRIBUTEACKNOWLEDGEMENTPROCESS] TAP on TAP.[ID] = TAPS.[PARAMETERSETID]
      left join dbo.[REVENUE] R on R.[ID] = RT.[REVENUEID]
      left join dbo.[APPUSER] AU on AU.[ID] = BPS.[STARTEDBYUSERID]
      left join dbo.[CHANGEAGENT] CA on CA.[ID] = RTL.[ADDEDBYID]
      left join dbo.BUSINESSPROCESSINSTANCE BPI on TAP.ID = BPI.BUSINESSPROCESSPARAMETERSETID
  where
      RTL.[CONSTITUENTID] = @CONSTITUENTID
      and RTL.[ACKNOWLEDGEDATE] is not null
      and (@CORRESPONDENCETYPECODE = 7 or @CORRESPONDENCETYPECODE = 99)
      and (RTL.[ACKNOWLEDGEDATE] >= @STARTDATE and RTL.[ACKNOWLEDGEDATE] <= @ENDDATE)
      and @CHANNEL = 99

  )
  select
      C.ID,
      C.DATESENT,
      C.CORRESPONDENCETYPECODE,
      C.CORRESPONDENCETYPE,
      C.DETAILS,
      C.PROCESSOR,
      C.COMMENTS,
      C.RECORDID,
      C.FINDERNUMBER,
      C.CONSTITUENTID,
      C.HASRESPONSES,
      C.PREVIEWDATAFORM,
      C.PREVIEWDATAFORMRECORDID,
      case
          when C.DATESENT >= dbo.UFN_DATE_THISMONTH_LASTDAY(getdate(), 1) then 'Future'
          when C.DATESENT >= dbo.UFN_DATE_THISMONTH_FIRSTDAY(getdate(), 0) and C.DATESENT <= dbo.UFN_DATE_THISMONTH_LASTDAY(getdate(), 1) then 'This month'
          when C.DATESENT >= dateadd(month, -1, dbo.UFN_DATE_THISMONTH_FIRSTDAY(getdate(), 0)) then 'Last month'
          when C.DATESENT >= dateadd(month, -2, dbo.UFN_DATE_THISMONTH_FIRSTDAY(getdate(), 0)) then 'Two months ago'
          else 'Older'
      end TIMEFRAMETEXT,
      case
          when C.DATESENT >= dbo.UFN_DATE_THISMONTH_LASTDAY(getdate(), 1) then '0'
          when C.DATESENT >= dbo.UFN_DATE_THISMONTH_FIRSTDAY(getdate(), 0) then '1'
          when C.DATESENT >= dateadd(month, -1, dbo.UFN_DATE_THISMONTH_FIRSTDAY(getdate(), 0)) then '2'
          when C.DATESENT >= dateadd(month, -2, dbo.UFN_DATE_THISMONTH_FIRSTDAY(getdate(), 0)) then '4'
          else '6'
      end TIMEFRAMEGROUPSORT,
                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 all sites

          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 CHANNEL,
    C.NOTSENT
  from CORRESPONDENCE_CTE C
      left join dbo.SITE on SITE.ID = C.SITEID
  where ( C.SITEENFORCEMENT = 0  --Type not site secured

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

          or( --SITEEXTENTION

                      (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 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

          @SITEFILTERMODE = 0
              or C.SITEID in (
                  select SITEID
                  from dbo.UFN_SITE_BUILDDATALISTSITEFILTER (@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED) 
              )
      );