V_QUERY_CONSTITUENTAPPEAL
Query view for Constituent Appeal records.
Fields
| Field | Field Type | Null | Description |
|---|---|---|---|
| ID | uniqueidentifier | System record ID | |
| CONSTITUENTID | uniqueidentifier | yes | Constituent |
| APPEALID | uniqueidentifier | Appeal | |
| MKTSEGMENTATIONID | uniqueidentifier | yes | Mailing system record ID |
| MKTSEGMENTATION_DESCRIPTION | nvarchar(100) | yes | Mailing |
| SOURCECODE | nvarchar(50) | yes | Source code |
| FINDERNUMBER | bigint | Finder number | |
| DATESENT | datetime | yes | Date sent |
| COMMENTS | nvarchar(255) | yes | Comments |
| MKTPACKAGEID | uniqueidentifier | yes | Package system record ID |
| MKTPACKAGE_DESCRIPTION | nvarchar(100) | yes | Package |
| SEGMENTID | uniqueidentifier | yes | Segment system record ID |
| MKTSEGMENTATIONSEGMENT_DESCRIPTION | nvarchar(100) | yes | Segment |
| TESTSEGMENTID | uniqueidentifier | yes | Test segment system record ID |
| MKTSEGMENTATIONTESTSEGMENT_DESCRIPTION | nvarchar(203) | yes | Test segment |
| DATEADDED | datetime | yes | Date added |
| DATECHANGED | datetime | yes | Date changed |
| TSLONG | bigint | yes | Timestamp value |
| 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 |
| CHANNEL | nvarchar(12) | yes | Channel |
| LETTERID | uniqueidentifier | yes | Letter system record ID |
| LETTERNAME | nvarchar(100) | yes | Letter |
Definition
Copy
/*
Generated by Blackbaud AppFx Platform
Date: 9/30/2015 1:01:30 AM
Assembly Version: Blackbaud.AppFx.Platform.SqlClr, Version=4.0.153.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE VIEW dbo.V_QUERY_CONSTITUENTAPPEAL AS
select
A.ID,
A.CONSTITUENTID,
A.APPEALID,
A.MKTSEGMENTATIONID,
MKTSEGMENTATION.NAME as MKTSEGMENTATION_DESCRIPTION,
A.SOURCECODE,
A.FINDERNUMBER,
A.DATESENT,
A.COMMENTS,
A.MKTPACKAGEID,
MKTPACKAGE.NAME as MKTPACKAGE_DESCRIPTION,
MKTSEGMENTATIONSEGMENT.SEGMENTID,
MKTSEGMENT.NAME as MKTSEGMENTATIONSEGMENT_DESCRIPTION,
TESTSEGMENT.SEGMENTID as TESTSEGMENTID,
dbo.UFN_MKTSEGMENTATIONTESTSEGMENT_GETNAME(A.MKTSEGMENTATIONTESTSEGMENTID) as MKTSEGMENTATIONTESTSEGMENT_DESCRIPTION,
A.DATEADDED,
A.DATECHANGED,
A.TSLONG,
ADDEDBY.APPLICATIONNAME as ADDEDBY_APPLICATION,
ADDEDBY.USERNAME as ADDEDBY_USERNAME,
CHANGEDBY.APPLICATIONNAME as CHANGEDBY_APPLICATION,
CHANGEDBY.USERNAME as CHANGEDBY_USERNAME,
MKTPACKAGE.CHANNEL,
coalesce(MAILLETTER.ID, EMAILLETTER.ID) as LETTERID,
coalesce(MAILLETTER.NAME, EMAILLETTER.NAME) as LETTERNAME
/*#EXTENSION*/
from dbo.CONSTITUENTAPPEAL A with (nolock)
left join dbo.MKTSEGMENTATION on MKTSEGMENTATION.ID = A.MKTSEGMENTATIONID
left join dbo.MKTPACKAGE on MKTPACKAGE.ID = A.MKTPACKAGEID
left join dbo.MKTSEGMENTATIONSEGMENT on MKTSEGMENTATIONSEGMENT.ID = A.MKTSEGMENTATIONSEGMENTID
left join dbo.MKTSEGMENT on MKTSEGMENT.ID = MKTSEGMENTATIONSEGMENT.SEGMENTID
left join dbo.MKTSEGMENTATIONTESTSEGMENT on MKTSEGMENTATIONTESTSEGMENT.ID = A.MKTSEGMENTATIONTESTSEGMENTID
left join dbo.MKTSEGMENTATIONSEGMENT TESTSEGMENT on MKTSEGMENTATIONTESTSEGMENT.SEGMENTID = TESTSEGMENT.ID
left join dbo.COMMUNICATIONLETTER MAILLETTER on MKTPACKAGE.ID = MAILLETTER.MAILPACKAGEID
left join dbo.COMMUNICATIONLETTER EMAILLETTER on MKTPACKAGE.ID = EMAILLETTER.EMAILPACKAGEID
left join dbo.CHANGEAGENT ADDEDBY with (nolock) on ADDEDBY.ID = A.ADDEDBYID
left join dbo.CHANGEAGENT CHANGEDBY with (nolock) on CHANGEDBY.ID = A.CHANGEDBYID
-- SHL BBIS Bug 334678; Union CONSTITUENTAPPEAL with NETCOMMUNITYEMAILJOBRECIPIENT so that appeal emails sent via BBIS are recognized
union
select
BBNCE.ID,
BBNCE.CONSTITUENTID,
A.ID,
null,
null,
null,
0,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null
from UFN_NETCOMMUNITYEMAILJOBRECIPIENT_EMAIL() AS BBNCE
inner join BBNCAPPEALIDMAP BBNCAM on BBNCAM.ID = BBNCE.APPEALID
inner join APPEAL A on BBNCAM.APPEALID = A.ID