V_QUERY_INTERACTIONALL
Provides the ability to query all interactions including plan steps and constituent interactions.
Fields
| Field | Field Type | Null | Description |
|---|---|---|---|
| ID | uniqueidentifier | System record ID | |
| CONSTITUENTID | uniqueidentifier | Constituent ID | |
| TYPE | nvarchar(100) | yes | Type |
| OBJECTIVE | nvarchar(100) | Summary | |
| OWNERID | uniqueidentifier | yes | Owner ID |
| DATE | datetime | yes | Date |
| EXPECTEDDATE | datetime | Expected date | |
| ACTUALDATE | datetime | yes | Actual date |
| COMPLETED | int | Completed | |
| DATEADDED | datetime | Date added | |
| DATECHANGED | datetime | 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 |
| COMMENT | nvarchar(max) | Comment | |
| EVENTID | uniqueidentifier | yes | Event |
| EVENTNAME | nvarchar(100) | yes | Event name |
| CATEGORY_TRANSLATION | nvarchar(100) | yes | Category |
| SUBCATEGORY_TRANSLATION | nvarchar(100) | yes | Subcategory |
| LOOKUPID | nvarchar(100) | yes | Interaction lookup ID |
| PROSPECTPLANID | uniqueidentifier | yes | Prospect plan ID |
| FUNDINGREQUESTID | uniqueidentifier | yes | Funding request ID |
| PLANTYPE | varchar(13) | yes | Plan type |
| PROSPECTPLANSTEPSTAGE | nvarchar(100) | yes | Prospect plan step stage |
| FUNDINGREQUESTSTEPSTAGE | nvarchar(100) | yes | Funding request step stage |
| ISCONTACTREPORT | int | Is contact report | |
| ISINTERACTION | int | Is interaction | |
| STATUS | nvarchar(12) | yes | Status |
| CONTACTMETHOD | nvarchar(100) | yes | Contact method |
| INTERACTIONTYPECODEID | uniqueidentifier | yes | |
| PROSPECTPLANSTATUSCODEID | uniqueidentifier | yes | |
| FUNDINGREQUESTSTAGECODEID | uniqueidentifier | yes | |
| INTERACTIONCATEGORYID | uniqueidentifier | yes | |
| INTERACTIONSUBCATEGORYID | uniqueidentifier | yes | |
| LOCATION | nvarchar(300) |
Definition
Copy
/*
Generated by Blackbaud AppFx Platform
Date: 12/15/2016 8:40:58 PM
Assembly Version: Blackbaud.AppFx.Platform.SqlClr, Version=4.0.165.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE VIEW dbo.V_QUERY_INTERACTIONALL AS
select
INTERACTION.ID,
INTERACTION.CONSTITUENTID, --From Interaction.Query.xml only
INTERACTIONTYPECODE.DESCRIPTION as TYPE,
INTERACTION.OBJECTIVE,
INTERACTION.FUNDRAISERID OWNERID,
INTERACTION.DATE,
INTERACTION.EXPECTEDDATE,
INTERACTION.ACTUALDATE,
INTERACTION.COMPLETED, --From Interaction.Query.xml only
INTERACTION.DATEADDED,
INTERACTION.DATECHANGED,
INTERACTION.TSLONG,
ADDEDBY.APPLICATIONNAME as ADDEDBY_APPLICATION,
ADDEDBY.USERNAME as ADDEDBY_USERNAME,
CHANGEDBY.APPLICATIONNAME as CHANGEDBY_APPLICATION,
CHANGEDBY.USERNAME as CHANGEDBY_USERNAME,
INTERACTION.COMMENT, --From Interaction.Query.xml and FundingRequestStep.Query.xml
INTERACTION.EVENTID, --From Interaction.Query.xml only
dbo.UFN_EVENT_GETNAME(INTERACTION.EVENTID) as EVENTNAME, --From Interaction.Query.xml only
INTERACTIONCATEGORY.NAME as [CATEGORY_TRANSLATION], --From Interaction.Query.xml and FundingRequestStep.Query.xml
INTERACTIONSUBCATEGORY.NAME as [SUBCATEGORY_TRANSLATION], --From Interaction.Query.xml and FundingRequestStep.Query.xml
INTERACTION.LOOKUPID, --From Interaction.Query.xml only
INTERACTION.PROSPECTPLANID, --From Step.Query.xml only
INTERACTION.FUNDINGREQUESTID, --From FundingRequestStep.Query.xml only
case
when INTERACTION.PROSPECTPLANID is not null then 'Prospect plan'
when INTERACTION.FUNDINGREQUESTID is not null then 'Funding plan'
else null
end [PLANTYPE], --Not on the other step queries
PROSPECTPLANSTATUSCODE.DESCRIPTION as PROSPECTPLANSTEPSTAGE, --From Step.Query.xml only
FUNDINGREQUESTSTAGECODE.DESCRIPTION as FUNDINGREQUESTSTEPSTAGE, --From FundingRequestStep.Query.xml only
INTERACTION.ISCONTACTREPORT, --From Step.Query.xml only
INTERACTION.ISINTERACTION, --From Step.Query.xml and FundingRequestStep.Query.xml
INTERACTION.STATUS, --From Step.Query.xml and FundingRequestStep.Query.xml
INTERACTIONTYPECODE.DESCRIPTION as CONTACTMETHOD,
[INTERACTION].[INTERACTIONTYPECODEID],
[INTERACTION].[PROSPECTPLANSTATUSCODEID],
[INTERACTION].[FUNDINGREQUESTSTAGECODEID],
[INTERACTIONCATEGORY].[ID] as [INTERACTIONCATEGORYID],
[INTERACTIONSUBCATEGORY].[ID] as [INTERACTIONSUBCATEGORYID],
INTERACTION.LOCATION
/*#EXTENSION*/
from dbo.INTERACTION
left join dbo.CHANGEAGENT ADDEDBY on ADDEDBY.ID = INTERACTION.ADDEDBYID
left join dbo.CHANGEAGENT CHANGEDBY on CHANGEDBY.ID = INTERACTION.CHANGEDBYID
left outer join dbo.INTERACTIONTYPECODE on INTERACTIONTYPECODE.ID = INTERACTION.INTERACTIONTYPECODEID
left outer join dbo.INTERACTIONSUBCATEGORY on INTERACTIONSUBCATEGORY.ID = INTERACTION.INTERACTIONSUBCATEGORYID
left outer join dbo.INTERACTIONCATEGORY on INTERACTIONCATEGORY.ID = INTERACTIONSUBCATEGORY.INTERACTIONCATEGORYID
left outer join dbo.PROSPECTPLANSTATUSCODE on PROSPECTPLANSTATUSCODE.ID = INTERACTION.PROSPECTPLANSTATUSCODEID
left outer join dbo.FUNDINGREQUESTSTAGECODE FUNDINGREQUESTSTAGECODE on FUNDINGREQUESTSTAGECODE.ID = INTERACTION.FUNDINGREQUESTSTAGECODEID