V_QUERY_CONSTITUENTCORRESPONDENCEALL
Provides the ability to query all constituent correspondence.
Fields
| Field | Field Type | Null | Description |
|---|---|---|---|
| CONSTITUENTID | uniqueidentifier | yes | Constituent ID |
| ID | uniqueidentifier | yes | System record ID |
| DATESENT | datetime | yes | Date sent |
| CORRESPONDENCETYPE | varchar(28) | Communication | |
| PROCESSOR | nvarchar(128) | yes | Processor |
| ADDEDBY_APPLICATION | nvarchar(200) | yes | Added by application |
| ADDEDBY_USERNAME | nvarchar(128) | yes | Added by user name |
| CHANGEDBY_APPLICATION | nvarchar(200) | yes | Changed by application |
| CHANGEDBY_USERNAME | nvarchar(128) | yes | Changed by user name |
| DATEADDED | datetime | yes | Date added |
| DATECHANGED | datetime | yes | Date changed |
| TSLONG | bigint | yes | Timestamp value |
| CHANNEL | varchar(5) | yes | Channel |
Definition
Copy
/*
Generated by Blackbaud AppFx Platform
Date: 3/19/2013 1:44:09 AM
Assembly Version: Blackbaud.AppFx.Platform.SqlClr, Version=3.0.504.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE VIEW dbo.V_QUERY_CONSTITUENTCORRESPONDENCEALL AS
select
BBNC.CONSTITUENTID as [CONSTITUENTID],
BBNC.ID as [ID],
BBNC.SENTDATE as [DATESENT],
'Appeal mailing' as [CORRESPONDENCETYPE],
CHANGEDBY.USERNAME as [PROCESSOR],
ADDEDBY.APPLICATIONNAME as [ADDEDBY_APPLICATION],
ADDEDBY.USERNAME as [ADDEDBY_USERNAME],
CHANGEDBY.APPLICATIONNAME as [CHANGEDBY_APPLICATION],
CHANGEDBY.USERNAME as [CHANGEDBY_USERNAME],
BBNC.DATEADDED as [DATEADDED],
BBNC.DATECHANGED as [DATECHANGED],
BBNC.TSLONG as [TSLONG],
'Email' as [CHANNEL]
/*#EXTENSION*/
from
dbo.[NETCOMMUNITYEMAILJOBRECIPIENT] BBNC with (nolock)
left join dbo.CHANGEAGENT as [ADDEDBY] with (nolock) on ADDEDBY.ID = BBNC.ADDEDBYID
left join dbo.CHANGEAGENT as [CHANGEDBY] with (nolock) on CHANGEDBY.ID = BBNC.CHANGEDBYID
union all
select
C.CONSTITUENTID as [CONSTITUENTID],
C.ID as [ID],
C.DATESENT as [DATESENT],
'General correspondence' as [CORRESPONDENCETYPE],
CHANGEDBY.USERNAME as [PROCESSOR],
ADDEDBY.APPLICATIONNAME as [ADDEDBY_APPLICATION],
ADDEDBY.USERNAME as [ADDEDBY_USERNAME],
CHANGEDBY.APPLICATIONNAME as [CHANGEDBY_APPLICATION],
CHANGEDBY.USERNAME as [CHANGEDBY_USERNAME],
C.DATEADDED as [DATEADDED],
C.DATECHANGED as [DATECHANGED],
C.TSLONG as [TSLONG],
'Mail' as [CHANNEL]
/*#EXTENSION*/
from
dbo.CONSTITUENTCORRESPONDENCE as [C] with (nolock)
left join dbo.CORRESPONDENCECODE as [CC] with (nolock) on CC.ID = C.CORRESPONDENCECODEID
left join dbo.CHANGEAGENT as [ADDEDBY] with (nolock) on ADDEDBY.ID = C.ADDEDBYID
left join dbo.CHANGEAGENT as [CHANGEDBY] with (nolock) on CHANGEDBY.ID = C.CHANGEDBYID
union all
select
R.CONSTITUENTID,
AP.ID,
RL.ACKNOWLEDGEDATE,
'Acknowledgement',
case when AU.USERNAME is not null then AU.USERNAME else CHANGEDBY.USERNAME end,
ADDEDBY.APPLICATIONNAME,
ADDEDBY.USERNAME,
CHANGEDBY.APPLICATIONNAME,
CHANGEDBY.USERNAME,
RL.DATEADDED,
RL.DATECHANGED,
RL.TSLONG,
case coalesce(MKTPACKAGE.CHANNELCODE, 0)
when 0 then 'Mail'
when 1 then 'Email'
end as [CHANNEL]
/*#EXTENSION*/
from
dbo.REVENUELETTER as [RL] with (nolock)
left join dbo.ACKNOWLEDGEMENTPROCESSSTATUS as [APS] with (nolock) on RL.ACKNOWLEDGEMENTPROCESSSTATUSID = APS.ID
left join dbo.ACKNOWLEDGEMENTPROCESS as [AP] with (nolock)on AP.ID = APS.PARAMETERSETID
left join dbo.BUSINESSPROCESSSTATUS as [BPS] with (nolock) on BPS.ID = RL.ACKNOWLEDGEMENTPROCESSSTATUSID
left join dbo.REVENUE as [R] with (nolock) on R.ID = RL.REVENUEID
left join dbo.APPUSER as [AU] with (nolock) on AU.ID = BPS.STARTEDBYUSERID
left join dbo.CHANGEAGENT as [ADDEDBY] with (nolock) on ADDEDBY.ID = RL.ADDEDBYID
left join dbo.CHANGEAGENT as [CHANGEDBY] with (nolock) on CHANGEDBY.ID = RL.CHANGEDBYID
left join dbo.MKTPACKAGE on RL.MKTPACKAGEID = MKTPACKAGE.ID
union all
select
R.CONSTITUENTID,
RECEIPT.ID,
RECEIPT.RECEIPTDATE,
'Receipt',
AU.USERNAME,
ADDEDBY.APPLICATIONNAME,
ADDEDBY.USERNAME,
CHANGEDBY.APPLICATIONNAME,
CHANGEDBY.USERNAME,
R.DATEADDED,
R.DATECHANGED,
R.TSLONG,
'Mail' as [CHANNEL]
/*#EXTENSION*/
from
dbo.REVENUERECEIPT as [RECEIPT] with (nolock)
left join dbo.REVENUE as [R] with (nolock) on R.ID = RECEIPT.REVENUEID
left join dbo.RECEIPTINGPROCESSSTATUS as [RPS] with (nolock) on RPS.ID = RECEIPT.RECEIPTINGPROCESSSTATUSID
left join dbo.BUSINESSPROCESSSTATUS as [BPS] with (nolock) on BPS.ID = RECEIPT.RECEIPTINGPROCESSSTATUSID
left join dbo.APPUSER as [AU] with (nolock) on AU.ID = BPS.STARTEDBYUSERID
left join dbo.CHANGEAGENT as [ADDEDBY] with (nolock) on ADDEDBY.ID = R.ADDEDBYID
left join dbo.CHANGEAGENT as [CHANGEDBY] with (nolock) on CHANGEDBY.ID = R.CHANGEDBYID
union all
select
R.CONSTITUENTID,
PRS.ID,
PRS.SENTDATE,
'Pledge reminder',
case when AU.USERNAME is not null then AU.USERNAME else CHANGEDBY.USERNAME end,
ADDEDBY.APPLICATIONNAME,
ADDEDBY.USERNAME,
CHANGEDBY.APPLICATIONNAME,
CHANGEDBY.USERNAME,
BPS.DATEADDED,
BPS.DATECHANGED,
BPS.TSLONG,
case coalesce(MKTPACKAGE.CHANNELCODE, 0)
when 0 then 'Mail'
when 1 then 'Email'
end as [CHANNEL]
/*#EXTENSION*/
from
dbo.PLEDGEREMINDERSENT as [PRS] with (nolock)
left join dbo.PLEDGEREMINDERPROCESSSTATUS as [PRPS] with (nolock) on PRPS.ID = PRS.PLEDGEREMINDERPROCESSSTATUSID
left join dbo.BUSINESSPROCESSSTATUS as [BPS] with (nolock) on BPS.ID = PRS.PLEDGEREMINDERPROCESSSTATUSID
left join dbo.PLEDGEREMINDERPROCESS as [PRP] with (nolock) on PRP.ID = PRPS.PARAMETERSETID
left join dbo.REVENUE as [R] with (nolock) on R.ID = PRS.REVENUEID
left join dbo.APPUSER as [AU] with (nolock) on AU.ID = BPS.STARTEDBYUSERID
left join dbo.CHANGEAGENT as [ADDEDBY] with (nolock) on ADDEDBY.ID = BPS.ADDEDBYID
left join dbo.CHANGEAGENT as [CHANGEDBY] with (nolock) on CHANGEDBY.ID = BPS.CHANGEDBYID
left join dbo.MKTPACKAGE on PRS.PACKAGEID = MKTPACKAGE.ID
union all
select
PG.CONSTITUENTID,
PGAP.ID,
PGL.ACKNOWLEDGEDATE,
'Planned gift acknowledgement',
case when AU.USERNAME is not null then AU.USERNAME else CHANGEDBY.USERNAME end,
ADDEDBY.APPLICATIONNAME,
ADDEDBY.USERNAME,
CHANGEDBY.APPLICATIONNAME,
CHANGEDBY.USERNAME,
PGL.DATEADDED,
PGL.DATECHANGED,
PGL.TSLONG,
'Mail' as [CHANNEL]
/*#EXTENSION*/
from
dbo.PLANNEDGIFTLETTER as [PGL] with (nolock)
left join dbo.PLANNEDGIFTLETTERCODE as [PGLC] with (nolock) on PGLC.ID = PGL.PLANNEDGIFTLETTERCODEID
left join dbo.PLANNEDGIFTACKNOWLEDGEMENTPROCESSSTATUS as [PGAPS] with (nolock) on PGAPS.ID = PGL.PLANNEDGIFTACKNOWLEDGEMENTPROCESSSTATUSID
left join dbo.BUSINESSPROCESSSTATUS as [BPS] with (nolock) on BPS.ID = PGL.PLANNEDGIFTACKNOWLEDGEMENTPROCESSSTATUSID
left join dbo.PLANNEDGIFTACKNOWLEDGEMENTPROCESS as [PGAP] with (nolock) on PGAP.ID = PGAPS.PARAMETERSETID
left join dbo.PLANNEDGIFT as [PG] with (nolock) on PG.ID = PGL.PLANNEDGIFTID
left join dbo.APPUSER as [AU] with (nolock) on AU.ID = BPS.STARTEDBYUSERID
left join dbo.CHANGEAGENT as [ADDEDBY] with (nolock) on ADDEDBY.ID = PGL.ADDEDBYID
left join dbo.CHANGEAGENT as [CHANGEDBY] with (nolock) on CHANGEDBY.ID = PGL.CHANGEDBYID
union all
select
IH.CONSTITUENTID,
IVH.ID,
isnull(IVH.MAILDATE, IVH.DATECHANGED),
'Event invitation',
case when AU.USERNAME is not null then AU.USERNAME else CHANGEDBY.USERNAME end,
ADDEDBY.APPLICATIONNAME,
ADDEDBY.USERNAME,
CHANGEDBY.APPLICATIONNAME,
CHANGEDBY.USERNAME,
IH.DATEADDED,
IH.DATECHANGED,
IH.TSLONG,
case coalesce(MKTPACKAGE.CHANNELCODE, 0)
when 0 then 'Mail'
when 1 then 'Email'
end as [CHANNEL]
/*#EXTENSION*/
from
dbo.INVITEEHISTORY as [IH] with (nolock)
left join dbo.INVITEE as [IV] with (nolock) on IV.CONSTITUENTID = IH.CONSTITUENTID
left join dbo.INVITATIONHISTORY as [IVH] with (nolock) on IVH.ID = IH.INVITATIONHISTORYID
left join dbo.BUSINESSPROCESSSTATUS as [BPS] with (nolock) on BPS.ID = IVH.BUSINESSPROCESSSTATUSID
left join dbo.EVENT as [E] with (nolock) on E.ID = IH.EVENTID
left join dbo.APPUSER as [AU] with (nolock) on AU.ID = BPS.STARTEDBYUSERID
left join dbo.CHANGEAGENT as [ADDEDBY] with (nolock) on ADDEDBY.ID = IH.ADDEDBYID
left join dbo.CHANGEAGENT as [CHANGEDBY] with (nolock) on CHANGEDBY.ID = IH.CHANGEDBYID
left join dbo.MKTPACKAGE on IH.MKTPACKAGEID = MKTPACKAGE.ID
union all
select
CA.CONSTITUENTID,
CA.ID,
CA.DATESENT,
'Appeal mailing',
CHANGEDBY.USERNAME,
ADDEDBY.APPLICATIONNAME,
ADDEDBY.USERNAME,
CHANGEDBY.APPLICATIONNAME,
CHANGEDBY.USERNAME,
CA.DATEADDED,
CA.DATECHANGED,
CA.TSLONG,
case coalesce(MKTPACKAGE.CHANNELCODE, 0)
when 0 then 'Mail'
when 1 then 'Email'
end as [CHANNEL]
/*#EXTENSION*/
from
dbo.CONSTITUENTAPPEAL as [CA] with (nolock)
left join dbo.APPEAL as [A] with (nolock) on A.ID = CA.APPEALID
left join dbo.MKTSEGMENTATION as [MS] with (nolock) on MS.ID = CA.MKTSEGMENTATIONID
left join dbo.CHANGEAGENT as [ADDEDBY] with (nolock) on ADDEDBY.ID = CA.ADDEDBYID
left join dbo.CHANGEAGENT as [CHANGEDBY] with (nolock) on CHANGEDBY.ID = CA.CHANGEDBYID
left join dbo.MKTPACKAGE on MKTPACKAGE.ID = CA.MKTPACKAGEID
where [MS].MAILINGTYPECODE <> 2
union all
select
CA.CONSTITUENTID,
MS.ID,
CA.DATESENT,
'Membership renewal',
CHANGEDBY.USERNAME,
ADDEDBY.APPLICATIONNAME,
ADDEDBY.USERNAME,
CHANGEDBY.APPLICATIONNAME,
CHANGEDBY.USERNAME,
CA.DATEADDED,
CA.DATECHANGED,
CA.TSLONG,
case coalesce(MKTPACKAGE.CHANNELCODE, 0)
when 0 then 'Mail'
when 1 then 'Email'
end as [CHANNEL]
/*#EXTENSION*/
from
dbo.CONSTITUENTAPPEAL as [CA] with (nolock)
left join dbo.APPEAL as [A] with (nolock) on A.ID = CA.APPEALID
left join dbo.MKTSEGMENTATION as [MS] with (nolock) on MS.ID = CA.MKTSEGMENTATIONID
left join dbo.CHANGEAGENT as [ADDEDBY] with (nolock) on ADDEDBY.ID = CA.ADDEDBYID
left join dbo.CHANGEAGENT as [CHANGEDBY] with (nolock) on CHANGEDBY.ID = CA.CHANGEDBYID
left join dbo.MKTPACKAGE on MKTPACKAGE.ID = CA.MKTPACKAGEID
where [MS].MAILINGTYPECODE = 2
union all
select
RTL.CONSTITUENTID,
TAP.ID,
RTL.ACKNOWLEDGEDATE,
'Tribute Acknowledgement',
case when AU.USERNAME is not null then AU.USERNAME else CHANGEDBY.USERNAME end,
ADDEDBY.APPLICATIONNAME,
ADDEDBY.USERNAME,
CHANGEDBY.APPLICATIONNAME,
CHANGEDBY.USERNAME,
RTL.DATEADDED,
RTL.DATECHANGED,
RTL.TSLONG,
'Mail' as [CHANNEL]
/*#EXTENSION*/
from
dbo.TRIBUTEACKNOWLEDGEMENTPROCESS as [TAP] with (nolock)
left join dbo.TRIBUTEACKNOWLEDGEMENTPROCESSSTATUS as [TAPS] with (nolock) on TAPS.PARAMETERSETID = TAP.ID
left join dbo.REVENUETRIBUTELETTER as [RTL] with (nolock) on RTL.TRIBUTEACKNOWLEDGEMENTPROCESSSTATUSID = TAPS.ID
left join dbo.BUSINESSPROCESSSTATUS as [BPS] with (nolock) on BPS.ID = RTL.TRIBUTEACKNOWLEDGEMENTPROCESSSTATUSID
--left join dbo.REVENUETRIBUTE as [RT] with (nolock) on RT.ID = RTL.REVENUETRIBUTEID
left join dbo.APPUSER as [AU] with (nolock) on AU.ID = BPS.STARTEDBYUSERID
left join dbo.CHANGEAGENT as [ADDEDBY] with (nolock) on ADDEDBY.ID = RTL.ADDEDBYID
left join dbo.CHANGEAGENT as [CHANGEDBY] with (nolock) on CHANGEDBY.ID = RTL.CHANGEDBYID