V_QUERY_FACULTY
Provides support for querying on Faculty information.
Fields
| Field | Field Type | Null | Description |
|---|---|---|---|
| ID | uniqueidentifier | System record ID | |
| CONSTITUENTID | uniqueidentifier | Constituent ID | |
| KEYNAME | nvarchar(100) | Last name | |
| FIRSTNAME | nvarchar(50) | First name | |
| MIDDLENAME | nvarchar(50) | Middle name | |
| NICKNAME | nvarchar(50) | Nickname | |
| LOOKUPID | nvarchar(100) | yes | Lookup ID |
| SUFFIXCODEID_TRANSLATION | nvarchar(100) | yes | Suffix |
| GENDER | nvarchar(7) | yes | Gender |
| BIRTHDATE | UDT_FUZZYDATE | Birth date | |
| DECEASEDDATE | UDT_FUZZYDATE | yes | Deceased date |
| ISINACTIVE | bit | Inactive | |
| DECEASED | bit | yes | Deceased |
| AGE | int | yes | Age |
| SUFFIX2CODEID_TRANSLATION | nvarchar(100) | yes | Suffix 2 |
| TITLECODEID_TRANSLATION | nvarchar(100) | yes | Title |
| TITLE2CODEID_TRANSLATION | nvarchar(100) | yes | Title 2 |
| NAME | nvarchar(154) | yes | Name |
| WEBADDRESS | UDT_WEBADDRESS | Web address | |
| DONOTMAIL | bit | Do not mail | |
| DONOTEMAIL | bit | Do not email | |
| DONOTPHONE | bit | Do not phone | |
| PICTURE | varbinary | yes | Picture |
| FACULTYSTATUSCODEID_TRANSLATION | nvarchar(100) | yes | Employment status |
| PRIMARYBUSINESS_ID | uniqueidentifier | yes | Primary business ID |
| HIREDONDATE | date | yes | Hire date |
| TERMINATED | bit | Terminated | |
| TERMINATEDONDATE | date | yes | Terminated date |
| 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 |
| YEARSEMPLOYED | int | yes | Years employed |
Definition
Copy
/*
Generated by Blackbaud AppFx Platform
Date: 11/30/2010 11:35:39 PM
Assembly Version: Blackbaud.AppFx.Platform.SqlClr, Version=2.8.2022.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE VIEW dbo.V_QUERY_FACULTY AS
select
FACULTY.ID,
CONSTITUENT.ID as [CONSTITUENTID],
CONSTITUENT.KEYNAME,
CONSTITUENT.FIRSTNAME,
CONSTITUENT.MIDDLENAME,
CONSTITUENT.NICKNAME,
CONSTITUENT.LOOKUPID,
(select SUFFIXCODE.DESCRIPTION from dbo.SUFFIXCODE where SUFFIXCODE.ID = CONSTITUENT.SUFFIXCODEID) as [SUFFIXCODEID_TRANSLATION],
CONSTITUENT.GENDER,
CONSTITUENT.BIRTHDATE,
DECEASEDCONSTITUENT.DECEASEDDATE,
CONSTITUENT.ISINACTIVE,
convert(bit, case when DECEASEDCONSTITUENT.ID is null then 0 else 1 end) as DECEASED,
dbo.UFN_CONSTITUENT_GETAGE(CONSTITUENT.ID) as AGE,
(select SUFFIXCODE.DESCRIPTION from dbo.SUFFIXCODE where SUFFIXCODE.ID = CONSTITUENT.SUFFIX2CODEID) as [SUFFIX2CODEID_TRANSLATION],
(select TITLECODE.DESCRIPTION from dbo.TITLECODE where TITLECODE.ID = CONSTITUENT.TITLECODEID) as [TITLECODEID_TRANSLATION],
(select TITLECODE.DESCRIPTION from dbo.TITLECODE where TITLECODE.ID = CONSTITUENT.TITLE2CODEID) as [TITLE2CODEID_TRANSLATION],
CONSTITUENT.NAME,
CONSTITUENT.WEBADDRESS,
CONSTITUENT.DONOTMAIL,
CONSTITUENT.DONOTEMAIL,
CONSTITUENT.DONOTPHONE,
CONSTITUENT.PICTURE,
(select FACULTYSTATUSCODE.DESCRIPTION from dbo.FACULTYSTATUSCODE where FACULTYSTATUSCODE.ID = FACULTY.FACULTYSTATUSCODEID) as [FACULTYSTATUSCODEID_TRANSLATION],
(select PRIMARYBUSINESS.RECIPROCALCONSTITUENTID from dbo.RELATIONSHIP as PRIMARYBUSINESS where PRIMARYBUSINESS.RELATIONSHIPCONSTITUENTID = CONSTITUENT.ID and PRIMARYBUSINESS.ISPRIMARYBUSINESS = 1 and CONSTITUENT.ISORGANIZATION = 0) as [PRIMARYBUSINESS_ID],
FACULTY.HIREDONDATE,
FACULTY.TERMINATED,
FACULTY.TERMINATEDONDATE,
ADDEDBY.APPLICATIONNAME as [ADDEDBY_APPLICATION],
ADDEDBY.USERNAME as [ADDEDBY_USERNAME],
CHANGEDBY.APPLICATIONNAME as [CHANGEDBY_APPLICATION],
CHANGEDBY.USERNAME as [CHANGEDBY_USERNAME],
FACULTY.DATEADDED,
FACULTY.DATECHANGED,
FACULTY.TSLONG,
dbo.UFN_AGEFROMFUZZYDATE(convert(varchar(8), hiredondate, 112), getdate()) + 1 as YEARSEMPLOYED
from
dbo.FACULTY
inner join dbo.CONSTITUENT on CONSTITUENT.ID = FACULTY.ID
left join dbo.DECEASEDCONSTITUENT on DECEASEDCONSTITUENT.ID = CONSTITUENT.ID
left join dbo.CHANGEAGENT as [ADDEDBY] on [ADDEDBY].ID = FACULTY.ADDEDBYID
left join dbo.CHANGEAGENT as [CHANGEDBY] on [CHANGEDBY].ID = FACULTY.CHANGEDBYID