V_QUERY_ADVOCACYLOG
Provides support for querying advocacy information
Fields
| Field | Field Type | Null | Description |
|---|---|---|---|
| ID | uniqueidentifier | System record ID | |
| ADVOCACYLOGID | int | yes | Advocacylogid |
| CONSTITUENTID | uniqueidentifier | Constituent | |
| TYPE | nvarchar(7) | yes | Delivery type |
| ACTIONITEMTYPE | nvarchar(8) | yes | Action item type |
| ACTIONCLASSIFICATION | nvarchar(15) | yes | Action classification |
| FROMADDRESS | nvarchar(255) | yes | From address |
| TOADDRESS | nvarchar(2000) | yes | To address |
| STATUS | nvarchar(10) | yes | Message status |
| SALUTATION | nvarchar(100) | yes | Salutation |
| MESSAGESIGNATURE | nvarchar(255) | yes | Message signature |
| SUBJECTLINE | nvarchar(255) | yes | Subject line |
| MESSAGETEXT | nvarchar(max) | yes | Message text |
| TARGETNAME | nvarchar(500) | yes | Target name |
| FEDERALDISTRICTCODE | nvarchar(4) | yes | Federal district code |
| UPPERSTATEDISTRICTCODE | nvarchar(5) | yes | Upper state district code |
| LOWERSTATEDISTRICTCODE | nvarchar(5) | yes | Lower state district code |
| ISMESSAGETEXTCHANGED | bit | yes | Is message text changed |
| DISTRICTNAME | nvarchar(100) | yes | District name |
| DISTRICT | nvarchar(13) | yes | District |
| ADDRESSLINE1 | nvarchar(100) | yes | Address line 1 |
| ADDRESSLINE2 | nvarchar(100) | yes | Address line 2 |
| CITY | nvarchar(50) | yes | City |
| STATE | nvarchar(100) | yes | State |
| COUNTRY | nvarchar(5) | yes | Country |
| ZIP | nvarchar(12) | yes | Zip |
| ISSUBJECTCHANGED | bit | yes | Is subject changed |
| 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 | Action item date | |
| DATECHANGED | datetime | Date changed | |
| TSLONG | bigint | yes | Timestamp value |
| ADVOCACYACTIVITYID | uniqueidentifier | yes | AdvocacyActionID |
| ActionCenterName | nvarchar(256) | yes | Action item center |
| SCHEDULEDSTARTDATE | datetime | yes | Initiation date |
| SCHEDULEDENDDATE | datetime | yes | Expiration date |
| ACTIONITEMNAME | nvarchar(250) | Action item name | |
| ACTIONITEMSTATUS | nvarchar(8) | yes | Action item status |
| SIGNATURENUMBER | bigint | yes | Signature number |
| ACTIONITEMCOUNT | int | yes | Action item count |
Definition
Copy
/*
Generated by Blackbaud AppFx Platform
Date: 11/30/2010 11:42:22 PM
Assembly Version: Blackbaud.AppFx.Platform.SqlClr, Version=2.8.2022.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE VIEW dbo.V_QUERY_ADVOCACYLOG AS
SELECT
ADVOCACYLOG.ID,
ADVOCACYLOG.[ADVOCACYLOGID],
-- ADVOCACYLOG.[SITECONTENTID],
-- ADVOCACYLOG.[ACTIONITEMID],
ADVOCACYLOG.[CONSTITUENTID],
-- ADVOCACYLOG.[LEGISID],
ADVOCACYLOG.[TYPE],
AI.TYPE AS [ACTIONITEMTYPE],
AI.TOPIC AS [ACTIONCLASSIFICATION],
-- ADVOCACYLOG.[FAILUREREASONID],
-- M.[FAILUREDESCRIPTION] AS [FAILUREREASON],
ADVOCACYLOG.[FROMADDRESS],
ADVOCACYLOG.[TOADDRESS],
ADVOCACYLOG.[STATUS],
-- ADVOCACYLOG.[TESTFLAG],
ADVOCACYLOG.[SALUTATION],
-- ADVOCACYLOG.[MESSAGEHEADER],
-- ADVOCACYLOG.[MESSAGESALUTATION],
ADVOCACYLOG.[MESSAGESIGNATURE],
ADVOCACYLOG.[SUBJECTLINE],
ADVOCACYLOG.[MESSAGETEXT],
ADVOCACYLOG.[TARGETNAME],
ADVOCACYLOG.[FEDERALDISTRICTCODE],
ADVOCACYLOG.[UPPERSTATEDISTRICTCODE],
ADVOCACYLOG.[LOWERSTATEDISTRICTCODE],
ADVOCACYLOG.[ISMESSAGETEXTCHANGED],
ADVOCACYLOG.[DISTRICTNAME],
ADVOCACYLOG.[DISTRICT],
ADVOCACYLOG.[ADDRESSLINE1],
ADVOCACYLOG.[ADDRESSLINE2],
ADVOCACYLOG.[CITY],
ADVOCACYLOG.[STATE],
ADVOCACYLOG.[COUNTRY],
ADVOCACYLOG.[ZIP],
ADVOCACYLOG.[ISSUBJECTCHANGED],
ADDEDBY.APPLICATIONNAME as [ADDEDBY_APPLICATION],
ADDEDBY.USERNAME as [ADDEDBY_USERNAME],
CHANGEDBY.APPLICATIONNAME as [CHANGEDBY_APPLICATION],
CHANGEDBY.USERNAME as [CHANGEDBY_USERNAME],
ADVOCACYLOG.DATEADDED,
ADVOCACYLOG.DATECHANGED,
ADVOCACYLOG.TSLONG,
AA.ID as [ADVOCACYACTIVITYID],
SC.title as [ActionCenterName],
AI.[STARTDATE] as [SCHEDULEDSTARTDATE],
AI.[ENDDATE] as [SCHEDULEDENDDATE],
AI.NAME as [ACTIONITEMNAME],
AI.STATUS as [ACTIONITEMSTATUS],
NULL AS [SIGNATURENUMBER],
iCount.ActionItemCount As ACTIONITEMCOUNT
FROM
dbo.ADVOCACYLOG
INNER JOIN dbo.SITECONTENT SC
ON ADVOCACYLOG.SITECONTENTID = SC.ID
INNER JOIN dbo.ACTION_ITEM AI
ON ADVOCACYLOG.ACTIONITEMID = AI.ID
left join dbo.ADVOCACYACTIVITY AA
ON ADVOCACYLOG.SITECONTENTID = AA.SITECONTENTID
AND ADVOCACYLOG.ACTIONITEMID = AA.ACTIONITEMID
AND ADVOCACYLOG.CONSTITUENTID = AA.CONSTITUENTID
left join dbo.MESSAGEFAILUREREASON M ON ADVOCACYLOG.FAILUREREASONID = M.FAILUREREASONID
left join dbo.CHANGEAGENT as [ADDEDBY] on [ADDEDBY].ID = ADVOCACYLOG.ADDEDBYID
left join dbo.CHANGEAGENT as [CHANGEDBY] on [CHANGEDBY].ID = ADVOCACYLOG.CHANGEDBYID
Left join (Select AA.ConstituentID As ID, Count(AA.ID) As ActionItemCount
From ADVOCACYACTIVITY AA
Group By AA.ConstituentID) As iCount
On iCount.ID = AA.CONSTITUENTID
UNION
SELECT
ID,
[ADVOCACYLOGID],
-- [SITECONTENTID],
-- [ACTIONITEMID],
[CONSTITUENTID],
-- [LEGISID],
[TYPE],
[ACTIONITEMTYPE],
[ACTIONCLASSIFICATION],
-- ADVOCACYLOG.[FAILUREREASONID],
-- [FAILUREREASON],
[FROMADDRESS],
[TOADDRESS],
[STATUS],
-- ADVOCACYLOG.[TESTFLAG],
[SALUTATION],
-- [MESSAGEHEADER],
-- [MESSAGESALUTATION],
[MESSAGESIGNATURE],
[SUBJECTLINE],
[MESSAGETEXT],
[TARGETNAME],
[FEDERALDISTRICTCODE],
[UPPERSTATEDISTRICTCODE],
[LOWERSTATEDISTRICTCODE],
[ISMESSAGETEXTCHANGED],
[DISTRICTNAME],
[DISTRICT],
[ADDRESSLINE1],
[ADDRESSLINE2],
[CITY],
[STATE],
[COUNTRY],
[ZIP],
[ISSUBJECTCHANGED],
[ADDEDBY_APPLICATION],
[ADDEDBY_USERNAME],
[CHANGEDBY_APPLICATION],
[CHANGEDBY_USERNAME],
DATEADDED,
DATECHANGED,
TSLONG,
[ADVOCACYACTIVITYID],
[ActionCenterName],
[SCHEDULEDSTARTDATE],
[SCHEDULEDENDDATE],
[ACTIONITEMNAME],
[ACTIONITEMSTATUS],
SIGNATURENUMBER,
ACTIONITEMCOUNT
FROM
(
SELECT
AP.ID,
NULL as [ADVOCACYLOGID],
-- AP.[SITECONTENTID],
-- AP.[ACTIONITEMID],
AP.[CONSTITUENTID],
-- NULL as [LEGISID],
NULL AS [TYPE],
AI.TYPE AS [ACTIONITEMTYPE],
NULL AS [ACTIONCLASSIFICATION],
NULL AS [FAILUREREASON],
NULL AS [FROMADDRESS],
NULL AS [TOADDRESS],
NULL AS [STATUS],
NULL AS [SALUTATION],
NULL AS [MESSAGEHEADER],
NULL AS [MESSAGESALUTATION],
NULL AS [MESSAGESIGNATURE],
NULL AS [SUBJECTLINE],
NULL AS [MESSAGETEXT],
NULL AS [TARGETNAME],
NULL AS [FEDERALDISTRICTCODE],
NULL AS [UPPERSTATEDISTRICTCODE],
NULL AS [LOWERSTATEDISTRICTCODE],
NULL AS [ISMESSAGETEXTCHANGED],
NULL AS [DISTRICTNAME],
NULL AS [DISTRICT],
NULL AS [ADDRESSLINE1],
NULL AS [ADDRESSLINE2],
NULL AS [CITY],
NULL AS [STATE],
NULL AS [COUNTRY],
NULL AS [ZIP],
NULL AS [ISSUBJECTCHANGED],
ADDEDBY.APPLICATIONNAME as [ADDEDBY_APPLICATION],
ADDEDBY.USERNAME as [ADDEDBY_USERNAME],
CHANGEDBY.APPLICATIONNAME as [CHANGEDBY_APPLICATION],
CHANGEDBY.USERNAME as [CHANGEDBY_USERNAME],
AP.DATEADDED,
AP.DATECHANGED,
AP.TSLONG,
AA.ID as [ADVOCACYACTIVITYID],
SC.title as [ActionCenterName],
AI.[STARTDATE] as [SCHEDULEDSTARTDATE],
AI.[ENDDATE] as [SCHEDULEDENDDATE],
AI.NAME as [ACTIONITEMNAME],
AI.STATUS as [ACTIONITEMSTATUS],
ROW_NUMBER() OVER( PARTITION BY AP.[ACTIONITEMID] ORDER BY AP.DATEADDED ASC ) AS SIGNATURENUMBER,
iCount.ActionItemCount As ACTIONITEMCOUNT
FROM
dbo.ADVOCACYPETITION AP
INNER JOIN dbo.SITECONTENT SC
ON AP.SITECONTENTID = SC.ID
INNER JOIN dbo.ACTION_ITEM AI
ON AP.ACTIONITEMID = AI.ID
left join dbo.ADVOCACYACTIVITY AA
ON AP.SITECONTENTID = AA.SITECONTENTID
AND AP.ACTIONITEMID = AA.ACTIONITEMID
AND AP.CONSTITUENTID = AA.CONSTITUENTID
left join dbo.CHANGEAGENT as [ADDEDBY] on [ADDEDBY].ID = AP.ADDEDBYID
left join dbo.CHANGEAGENT as [CHANGEDBY] on [CHANGEDBY].ID = AP.CHANGEDBYID
Left join (Select AA.ConstituentID As ID, Count(AA.ID) As ActionItemCount
From ADVOCACYACTIVITY AA
Group By AA.ConstituentID) As iCount
On iCount.ID = AA.CONSTITUENTID
)AS A