V_QUERY_CONSTITUENTSOLICITCODE
Provides the ability to query for solicit code information.
Fields
| Field | Field Type | Null | Description |
|---|---|---|---|
| ID | uniqueidentifier | System record ID | |
| CONSTITUENTID | uniqueidentifier | Constituent ID | |
| DESCRIPTION | nvarchar(353) | yes | Solicit code |
| STARTDATE | datetime | yes | Start date |
| ENDDATE | datetime | yes | End date |
| COMMENTS | nvarchar(100) | Comments | |
| 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 | Date added | |
| DATECHANGED | datetime | Date changed | |
| TSLONG | bigint | yes | Timestamp value |
| SOLICITCODEID | uniqueidentifier | Solicit code record | |
| SOLICITCODEDESCRIPTION | nvarchar(100) | Name | |
| SITEID | uniqueidentifier | yes | Site ID |
| CONSENTPREFERENCE | nvarchar(11) | yes | |
| SOURCEEVIDENCECODEID | uniqueidentifier | yes | |
| SOURCEEVIDENCECODE | nvarchar(100) | yes | |
| SOURCEFILE | nvarchar(260) | ||
| PRIVACYPOLICY | nvarchar(260) | ||
| SUPPORTINGINFORMATION | nvarchar(max) | ||
| CONSENTSTATEMENT | nvarchar(max) |
Definition
Copy
/*
Generated by Blackbaud AppFx Platform
Date: 4/21/2018 10:21:06 AM
Assembly Version: Blackbaud.AppFx.Platform.SqlClr, Version=4.0.177.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE VIEW dbo.V_QUERY_CONSTITUENTSOLICITCODE AS
select
CONSTITUENTSOLICITCODE.ID,
CONSTITUENTSOLICITCODE.CONSTITUENTID,
case
when SOLICITCODE.SITEID is null then SOLICITCODE.[DESCRIPTION]
else SOLICITCODE.[DESCRIPTION] + ' - ' + SITE.NAME
end as DESCRIPTION,
CONSTITUENTSOLICITCODE.STARTDATE,
CONSTITUENTSOLICITCODE.ENDDATE,
CONSTITUENTSOLICITCODE.COMMENTS,
[ADDEDBY].APPLICATIONNAME as [ADDEDBY_APPLICATION],
[ADDEDBY].USERNAME as [ADDEDBY_USERNAME],
[CHANGEDBY].APPLICATIONNAME as [CHANGEDBY_APPLICATION],
[CHANGEDBY].USERNAME as [CHANGEDBY_USERNAME],
CONSTITUENTSOLICITCODE.DATEADDED,
CONSTITUENTSOLICITCODE.DATECHANGED,
CONSTITUENTSOLICITCODE.TSLONG,
SOLICITCODE.ID SOLICITCODEID,
SOLICITCODE.DESCRIPTION SOLICITCODEDESCRIPTION,
SITE.ID as SITEID,
case SOLICITCODE.CONSENTCODE
when 1 then CONSTITUENTSOLICITCODE.CONSENTPREFERENCE -- GDPR
when 2 then CONSTITUENTSOLICITCODE.CONSENTPREFERENCE -- Advanced consent
else null
end as CONSENTPREFERENCE,
CONSTITUENTSOLICITCODE.SOURCECODEID as SOURCEEVIDENCECODEID,
DATAPROTECTIONEVIDENCESOURCECODE.DESCRIPTION as SOURCEEVIDENCECODE,
CONSTITUENTSOLICITCODE.SOURCEFILEPATH as SOURCEFILE,
CONSTITUENTSOLICITCODE.PRIVACYPOLICYFILEPATH as PRIVACYPOLICY,
CONSTITUENTSOLICITCODE.SUPPORTINGINFORMATION,
CONSTITUENTSOLICITCODE.CONSENTSTATEMENT
from dbo.CONSTITUENTSOLICITCODE
inner join dbo.SOLICITCODE on CONSTITUENTSOLICITCODE.SOLICITCODEID = SOLICITCODE.ID
left join dbo.DATAPROTECTIONEVIDENCESOURCECODE on CONSTITUENTSOLICITCODE.SOURCECODEID = DATAPROTECTIONEVIDENCESOURCECODE.ID
left join dbo.SITE on SITE.ID = SOLICITCODE.SITEID
left join dbo.CHANGEAGENT as [ADDEDBY] on ADDEDBY.ID = CONSTITUENTSOLICITCODE.ADDEDBYID
left join dbo.CHANGEAGENT as [CHANGEDBY] on CHANGEDBY.ID = CONSTITUENTSOLICITCODE.CHANGEDBYID