V_QUERY_EDUCATIONALINVOLVEMENT
Provides the ability to query a constituent's educational involvement.
Fields
| Field | Field Type | Null | Description |
|---|---|---|---|
| ID | uniqueidentifier | System record ID | |
| CONSTITUENTID | uniqueidentifier | Constituent ID | |
| TYPE | nvarchar(100) | yes | Type |
| NAME | nvarchar(150) | yes | Name |
| POSITION | nvarchar(256) | Position | |
| DATEFROM | UDT_FUZZYDATE | Start date | |
| DATETO | UDT_FUZZYDATE | End date | |
| INSTITUTION | nvarchar(100) | yes | Educational institution |
| COMMENT | nvarchar(500) | Notes | |
| AWARD | nvarchar(100) | yes | Award |
| 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 |
| ACADEMICCATALOGPROGRAM | nvarchar(13) | yes | Academic catalog program |
| ACADEMICCATALOGCOLLEGEID | nvarchar(50) | yes | Academic catalog college ID |
| ACADEMICCATALOGCOLLEGE | nvarchar(150) | yes | Academic catalog college |
| ACADEMICCATALOGDIVISIONID | nvarchar(50) | yes | Academic catalog division ID |
| ACADEMICCATALOGDIVISION | nvarchar(150) | yes | Academic catalog division |
| ACADEMICCATALOGDEPARTMENTID | nvarchar(50) | yes | Academic catalog department ID |
| ACADEMICCATALOGDEPARTMENT | nvarchar(150) | yes | Academic catalog department |
| ACADEMICCATALOGSUBDEPARTMENTID | nvarchar(50) | yes | Academic catalog sub department ID |
| ACADEMICCATALOGSUBDEPARTMENT | nvarchar(150) | yes | Academic catalog sub department |
| EDUCATIONALPROGRAMCODE | nvarchar(100) | yes | Educational program |
| EDUCATIONALCOLLEGECODE | nvarchar(100) | yes | Educational college |
| EDUCATIONALDIVISIONCODE | nvarchar(100) | yes | Educational division |
| EDUCATIONALDEPARTMENTCODE | nvarchar(100) | yes | Educational department |
| EDUCATIONALSUBDEPARTMENTCODE | nvarchar(100) | yes | Educational sub department |
| EDUCATIONALDEPARTMENT_SHORTCODE | nvarchar(25) | yes | Educational department short code |
| EDUCATIONALSUBDEPARTMENT_SHORTCODE | nvarchar(25) | yes | Educational sub department short code |
| EDUCATIONALINVOLVEMENTTYPECODEID | uniqueidentifier | ||
| EDUCATIONALINVOLVEMENTAWARDCODEID | uniqueidentifier | yes | |
| EDUCATIONALPROGRAMCODEID | uniqueidentifier | yes | |
| EDUCATIONALCOLLEGECODEID | uniqueidentifier | yes | |
| EDUCATIONALDIVISIONCODEID | uniqueidentifier | yes | |
| EDUCATIONALDEPARTMENTCODEID | uniqueidentifier | yes | |
| EDUCATIONALSUBDEPARTMENTCODEID | uniqueidentifier | yes | |
| EDUCATIONALINVOLVEMENTNAMEID | uniqueidentifier | yes | |
| EDUCATIONALINSTITUTIONID | uniqueidentifier | yes |
Definition
Copy
/*
Generated by Blackbaud AppFx Platform
Date: 9/30/2015 1:01:51 AM
Assembly Version: Blackbaud.AppFx.Platform.SqlClr, Version=4.0.153.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE VIEW dbo.V_QUERY_EDUCATIONALINVOLVEMENT AS
select
INVOLVEMENT.ID,
INVOLVEMENT.CONSTITUENTID,
TYPE.DESCRIPTION as TYPE,
EDUCATIONALINVOLVEMENTNAME.NAME,
INVOLVEMENT.POSITION,
INVOLVEMENT.DATEFROM,
INVOLVEMENT.DATETO,
INSTITUTION.NAME as INSTITUTION,
INVOLVEMENT.COMMENT,
AWARD.DESCRIPTION as AWARD,
coalesce(ADDEDBY.APPLICATIONNAME,'') as ADDEDBY_APPLICATION,
coalesce(ADDEDBY.USERNAME,'') as ADDEDBY_USERNAME,
coalesce(CHANGEDBY.APPLICATIONNAME,'') as CHANGEDBY_APPLICATION,
coalesce(CHANGEDBY.USERNAME,'') as CHANGEDBY_USERNAME,
INVOLVEMENT.DATEADDED,
INVOLVEMENT.DATECHANGED,
INVOLVEMENT.TSLONG,
ACADEMICCATALOGPROGRAM.PROGRAM as ACADEMICCATALOGPROGRAM,
ACADEMICCATALOGCOLLEGE.USERID as ACADEMICCATALOGCOLLEGEID,
ACADEMICCATALOGCOLLEGE.NAME as ACADEMICCATALOGCOLLEGE,
ACADEMICCATALOGDIVISION.USERID as ACADEMICCATALOGDIVISIONID,
ACADEMICCATALOGDIVISION.NAME as ACADEMICCATALOGDIVISION,
ACADEMICCATALOGDEPARTMENT.USERID as ACADEMICCATALOGDEPARTMENTID,
ACADEMICCATALOGDEPARTMENT.NAME as ACADEMICCATALOGDEPARTMENT,
ACADEMICCATALOGSUBDEPARTMENT.USERID as ACADEMICCATALOGSUBDEPARTMENTID,
ACADEMICCATALOGSUBDEPARTMENT.NAME as ACADEMICCATALOGSUBDEPARTMENT,
EDUCATIONALPROGRAMCODE.DESCRIPTION as EDUCATIONALPROGRAMCODE,
EDUCATIONALCOLLEGECODE.DESCRIPTION as EDUCATIONALCOLLEGECODE,
EDUCATIONALDIVISIONCODE.DESCRIPTION as EDUCATIONALDIVISIONCODE,
EDUCATIONALDEPARTMENTCODE.DESCRIPTION as EDUCATIONALDEPARTMENTCODE,
EDUCATIONALSUBDEPARTMENTCODE.DESCRIPTION as EDUCATIONALSUBDEPARTMENTCODE,
EDUCATIONALDEPARTMENTCODEEXTENSION.SHORTCODE as EDUCATIONALDEPARTMENT_SHORTCODE,
EDUCATIONALSUBDEPARTMENTCODEEXTENSION.SHORTCODE as EDUCATIONALSUBDEPARTMENT_SHORTCODE,
[INVOLVEMENT].[EDUCATIONALINVOLVEMENTTYPECODEID],
[INVOLVEMENT].[EDUCATIONALINVOLVEMENTAWARDCODEID],
[INVOLVEMENT].[EDUCATIONALPROGRAMCODEID],
[INVOLVEMENT].[EDUCATIONALCOLLEGECODEID],
[INVOLVEMENT].[EDUCATIONALDIVISIONCODEID],
[INVOLVEMENT].[EDUCATIONALDEPARTMENTCODEID],
[INVOLVEMENT].[EDUCATIONALSUBDEPARTMENTCODEID],
[INVOLVEMENT].[EDUCATIONALINVOLVEMENTNAMEID],
[INVOLVEMENT].[EDUCATIONALINSTITUTIONID]
/*#EXTENSION*/
from
dbo.EDUCATIONALINVOLVEMENT as INVOLVEMENT
left join dbo.EDUCATIONALINVOLVEMENTTYPECODE as TYPE on INVOLVEMENT.EDUCATIONALINVOLVEMENTTYPECODEID = TYPE.ID
left join dbo.EDUCATIONALINSTITUTION as INSTITUTION on INVOLVEMENT.EDUCATIONALINSTITUTIONID = INSTITUTION.ID
left join dbo.EDUCATIONINVOLVEMENTAWARDCODE as AWARD on INVOLVEMENT.EDUCATIONALINVOLVEMENTAWARDCODEID = AWARD.ID
left join dbo.CHANGEAGENT as ADDEDBY on ADDEDBY.ID = INVOLVEMENT.ADDEDBYID
left join dbo.CHANGEAGENT as CHANGEDBY on CHANGEDBY.ID = INVOLVEMENT.CHANGEDBYID
left join dbo.EDUCATIONALINVOLVEMENTNAME on INVOLVEMENT.EDUCATIONALINVOLVEMENTNAMEID = EDUCATIONALINVOLVEMENTNAME.ID
left join dbo.ACADEMICCATALOGPROGRAM on INVOLVEMENT.ACADEMICCATALOGPROGRAMID = ACADEMICCATALOGPROGRAM.ID
left join dbo.ACADEMICCATALOGCOLLEGE on INVOLVEMENT.ACADEMICCATALOGCOLLEGEID = ACADEMICCATALOGCOLLEGE.ID
left join dbo.ACADEMICCATALOGDIVISION on INVOLVEMENT.ACADEMICCATALOGDIVISIONID = ACADEMICCATALOGDIVISION.ID
left join dbo.ACADEMICCATALOGDEPARTMENT on INVOLVEMENT.ACADEMICCATALOGDEPARTMENTID = ACADEMICCATALOGDEPARTMENT.ID
left join dbo.ACADEMICCATALOGSUBDEPARTMENT on INVOLVEMENT.ACADEMICCATALOGSUBDEPARTMENTID = ACADEMICCATALOGSUBDEPARTMENT.ID
left join dbo.EDUCATIONALDIVISIONCODE on INVOLVEMENT.EDUCATIONALDIVISIONCODEID = EDUCATIONALDIVISIONCODE.ID
left join dbo.EDUCATIONALDEPARTMENTCODE on INVOLVEMENT.EDUCATIONALDEPARTMENTCODEID = EDUCATIONALDEPARTMENTCODE.ID
left join dbo.EDUCATIONALSUBDEPARTMENTCODE on INVOLVEMENT.EDUCATIONALSUBDEPARTMENTCODEID = EDUCATIONALSUBDEPARTMENTCODE.ID
left join dbo.EDUCATIONALPROGRAMCODE on INVOLVEMENT.EDUCATIONALPROGRAMCODEID = EDUCATIONALPROGRAMCODE.ID
left join dbo.EDUCATIONALCOLLEGECODE on INVOLVEMENT.EDUCATIONALCOLLEGECODEID = EDUCATIONALCOLLEGECODE.ID
left join dbo.EDUCATIONALDEPARTMENTCODEEXTENSION on EDUCATIONALDEPARTMENTCODE.ID = EDUCATIONALDEPARTMENTCODEEXTENSION.ID
left join dbo.EDUCATIONALSUBDEPARTMENTCODEEXTENSION on EDUCATIONALSUBDEPARTMENTCODE.ID = EDUCATIONALSUBDEPARTMENTCODEEXTENSION.ID