USP_DATALIST_CONSTITUENTGROUPCORRESPONDENCE
This datalist returns all correspondence for a constituent group and its members.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @CONSTITUENTID | uniqueidentifier | IN | Input parameter indicating the context ID for the data list. |
| @DATEFILTER | tinyint | IN | Date range |
| @CORRESPONDENCETYPECODE | smallint | IN | Communication |
| @CURRENTAPPUSERID | uniqueidentifier | IN | Input parameter indicating the ID of the current user. |
| @SITEFILTERMODE | tinyint | IN | Sites |
| @SITESSELECTED | xml | IN | |
| @SECURITYFEATUREID | uniqueidentifier | IN | Input parameter indicating the ID of the feature to use for site security checking. |
| @SECURITYFEATURETYPE | tinyint | IN | Input parameter indicating the type of the feature to use for site security checking. |
| @CHANNEL | smallint | IN | Channel |
Definition
Copy
CREATE procedure dbo.USP_DATALIST_CONSTITUENTGROUPCORRESPONDENCE
(
@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
) as
set nocount on;
declare @DATALISTID uniqueidentifier;
set @DATALISTID = 'b3c90e29-5619-4909-9d1b-516d2623d10a';
declare @ENTERPRISEINSTALLED bit = dbo.[UFN_INSTALLEDPRODUCTS_PRODUCTIS]('3117D2C8-7F46-42F2-ABEB-B654F2F63046');
if @DATEFILTER is null begin
set @DATEFILTER = 9; -- last 12 months;
end
if @CORRESPONDENCETYPECODE is null begin
set @CORRESPONDENCETYPECODE = 99; -- all;
end
declare @STARTDATE datetime;
declare @ENDDATE datetime;
exec dbo.USP_RESOLVEDATEFILTER @DATEFILTER=@DATEFILTER, @STARTDATE=@STARTDATE output, @ENDDATE=@ENDDATE output;
with CORRESPONDENCE_CTE 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,
null as SITEID,
0 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,
null as SITEID,
0 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
)
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 >= dbo.UFN_DATE_THISMONTH_FIRSTDAY(getdate(), 0) 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_FIRSTDAY(getdate(), 0) then '0'
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
when dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 then 1
else dbo.UFN_SECURITY_APPUSER_GRANTED_DATALIST_FORCONSTIT(@CURRENTAPPUSERID, @DATALISTID, C.CONSTITUENTID)
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 CORRESPONDENCE_CTE 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
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( (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)
)
)
order by
TIMEFRAMEGROUPSORT asc, DATESENT desc, CORRESPONDENCETYPECODE;