V_QUERY_EDUCATIONALHISTORYUPDATEBATCHEXCEPTIONREPORT
View used for output for educational history update batch exception report.
Fields
| Field | Field Type | Null | Description |
|---|---|---|---|
| ID | uniqueidentifier | System record ID | |
| CONSTITUENTNAME | nvarchar(700) | yes | Constituent name |
| LOOKUPID | nvarchar(100) | Lookup ID | |
| INSTITUTION | nvarchar(100) | yes | Institution |
| PROGRAM | nvarchar(100) | yes | Program |
| DEGREE | nvarchar(150) | yes | Degree |
| COLLEGE | nvarchar(150) | yes | College/school |
| DIVISION | nvarchar(150) | yes | Division |
| DEPARTMENT | nvarchar(150) | yes | Department |
| SUBDEPARTMENT | nvarchar(150) | yes | Sub department |
| DEGREETYPE | nvarchar(100) | yes | Degree type |
Definition
Copy
/*
Generated by Blackbaud AppFx Platform
Date: 8/17/2011 2:35:46 PM
Assembly Version: Blackbaud.AppFx.Platform.SqlClr, Version=2.91.1535.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE VIEW dbo.V_QUERY_EDUCATIONALHISTORYUPDATEBATCHEXCEPTIONREPORT AS
select
BEHU.[ID],
coalesce(nullif(dbo.UFN_NAMEFORMATFUNCTION_SIMPLE(C.ID, C.KEYNAME, C.FIRSTNAME, C.MIDDLENAME, (select DESCRIPTION from dbo.TITLECODE where ID = C.TITLECODEID), (select DESCRIPTION from dbo.SUFFIXCODE where ID = C.SUFFIXCODEID),'','','','',''), ''), BEHU.CONSTITUENTFIRSTNAME + ' ' + BEHU.CONSTITUENTKEYNAME) as CONSTITUENTNAME,
BEHU.[CONSTITUENTLOOKUPID] as LOOKUPID,
coalesce(nullif(EI.[NAME], ''), BEHU.EDUCATIONALINSTITUTIONNAME) as INSTITUTION,
case
when ECON.USEACADEMICCATALOG = 1 and EI.ISAFFILIATED = 1 then ACP.PROGRAM
else EPC.[DESCRIPTION]
end as PROGRAM,
case
when ECON.USEACADEMICCATALOG = 1 and EI.ISAFFILIATED = 1 then ACD.NAME
else EDC.[DESCRIPTION]
end as DEGREE,
case
when (ECON.USEACADEMICCATALOG & EI.ISAFFILIATED) = 1 then ACC.NAME
else ECC.DESCRIPTION
end as COLLEGE,
case
when (ECON.USEACADEMICCATALOG & EI.ISAFFILIATED) = 1 then ACDIV.NAME
else EDIV.DESCRIPTION
end as DIVISION,
case
when (ECON.USEACADEMICCATALOG & EI.ISAFFILIATED) = 1 then ACDEPT.NAME
else EDEPT.DESCRIPTION
end as DEPARTMENT,
case
when (ECON.USEACADEMICCATALOG & EI.ISAFFILIATED) = 1 then ACSUBDEPT.NAME
else ESUBDEPT.DESCRIPTION
end as SUBDEPARTMENT,
case
when (ECON.USEACADEMICCATALOG & EI.ISAFFILIATED) = 1 then ACDTC.DESCRIPTION
else EDTC.DESCRIPTION
end as DEGREETYPE
from
dbo.[BATCHEDUCATIONALHISTORYUPDATE] BEHU
left join
dbo.[EDUCATIONALHISTORY] EH on BEHU.[PRIMARYRECORDID] = EH.[ID]
left join
dbo.[EDUCATIONALINSTITUTION] EI on EI.[ID] = BEHU.[EDUCATIONALINSTITUTIONID]
left join
dbo.[EDUCATIONALPROGRAMCODE] EPC on EPC.[ID] = BEHU.[EDUCATIONALPROGRAMCODEID]
left join
dbo.[EDUCATIONALDEGREECODE] EDC on EDC.[ID] = BEHU.[EDUCATIONALDEGREECODEID]
left join
dbo.[ACADEMICCATALOGPROGRAM] ACP on ACP.[ID] = BEHU.[ACADEMICCATALOGPROGRAMID]
left join
dbo.[ACADEMICCATALOGDEGREE] ACD on ACD.[ID] = BEHU.[ACADEMICCATALOGDEGREEID]
left join
dbo.EDUCATIONADDITIONALINFORMATION EAI on EAI.ID = (select top(1) ID from dbo.EDUCATIONADDITIONALINFORMATION where EDUCATIONALHISTORYID = EH.ID order by TSLONG desc)
left join
dbo.EDUCATIONALCOLLEGECODE ECC on ECC.ID = EAI.EDUCATIONALCOLLEGECODEID
left join
dbo.EDUCATIONALDIVISIONCODE EDIV on EDIV.ID = EAI.EDUCATIONALDIVISIONCODEID
left join
dbo.EDUCATIONALDEPARTMENTCODE EDEPT on EDEPT.ID = EAI.EDUCATIONALDEPARTMENTCODEID
left join
dbo.EDUCATIONALSUBDEPARTMENTCODE ESUBDEPT on ESUBDEPT.ID = EAI.EDUCATIONALSUBDEPARTMENTCODEID
left join
dbo.EDUCATIONALDEGREETYPECODE EDTC on EDTC.ID = EAI.EDUCATIONALDEGREETYPECODEID
left join
dbo.ACADEMICCATALOGCOLLEGE ACC on ACC.ID = EAI.ACADEMICCATALOGCOLLEGEID
left join
dbo.ACADEMICCATALOGDIVISION ACDIV on ACDIV.ID = EAI.ACADEMICCATALOGDIVISIONID
left join
dbo.ACADEMICCATALOGDEPARTMENT ACDEPT on ACDEPT.ID = EAI.ACADEMICCATALOGDEPARTMENTID
left join
dbo.ACADEMICCATALOGSUBDEPARTMENT ACSUBDEPT on ACSUBDEPT.ID = EAI.ACADEMICCATALOGSUBDEPARTMENTID
left join
dbo.ACADEMICCATALOGDEGREETYPE ACDT on ACDT.ID = EAI.ACADEMICCATALOGDEGREETYPEID
left join
dbo.ACADEMICCATALOGDEGREETYPECODE ACDTC on ACDTC.ID = ACDT.ACADEMICCATALOGDEGREETYPECODEID
left join
dbo.[EDUCATIONALCONFIGURATION] ECON on 1 = 1
left join
dbo.[CONSTITUENT] C on C.[ID] = EH.[CONSTITUENTID]