USP_DATAFORMTEMPLATE_VIEW_EDUCATIONALHISTORYAFFILIATED_3

The load procedure used by the view dataform template "Educational History Affiliated View Form 3"

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	IN	The input ID parameter used to load the fields defined on the form.
@DATALOADED	bit	INOUT	Output parameter indicating whether or not data was actually loaded.
@CONSTITUENTID	uniqueidentifier	INOUT	Constituent
@EDUCATIONALINSTITUTION	nvarchar(150)	INOUT	Institution
@ACADEMICCATALOGPROGRAM	nvarchar(150)	INOUT	Program
@ALUMNUSTEXT	nvarchar(14)	INOUT	Status
@ACADEMICCATALOGDEGREE	nvarchar(150)	INOUT	Degree
@CLASSYEAR	UDT_YEAR	INOUT	Class of
@PREFERREDCLASSYEAR	UDT_YEAR	INOUT	Preferred class of
@STARTDATE	UDT_FUZZYDATE	INOUT	Start date
@DATEGRADUATED	UDT_FUZZYDATE	INOUT	Graduation date
@DATELEFT	UDT_FUZZYDATE	INOUT	Date left
@ADDITIONALINFORMATION	xml	INOUT	Additional information
@CONSTITUENCYSTATUSCODE	tinyint	INOUT	Status
@DEGREETYPETEXT	nvarchar(150)	INOUT	Degree type text
@DEPARTMENTTEXT	nvarchar(150)	INOUT	Department text
@COLLEGETEXT	nvarchar(150)	INOUT	College text
@COMMENT	nvarchar(500)	INOUT	Comments
@EDUCATIONALHISTORYLEVELCODE	nvarchar(150)	INOUT	Level
@USEACADEMICCATALOG	bit	INOUT	Use academic catalog
@REASONCODE	uniqueidentifier	INOUT	Reason
@EDUCATIONALHISTORYSTATUS	nvarchar(100)	INOUT	Status
@EDUCATIONALPROGRAM	nvarchar(150)	INOUT	Program
@EDUCATIONALDEGREE	nvarchar(150)	INOUT	Degree
@EDUCATIONALAWARD	nvarchar(150)	INOUT	Honor awarded
@REASONCODENAME	nvarchar(100)	INOUT	Reason

Definition

Copy



CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_EDUCATIONALHISTORYAFFILIATED_3
(
    @ID uniqueidentifier,
    @DATALOADED bit = 0 output,
    @CONSTITUENTID uniqueidentifier = null output,
    @EDUCATIONALINSTITUTION nvarchar(150) = null output,
    @ACADEMICCATALOGPROGRAM nvarchar(150) = null output,
    @ALUMNUSTEXT nvarchar(14) = null output,
    @ACADEMICCATALOGDEGREE nvarchar(150) = null output,
    @CLASSYEAR dbo.UDT_YEAR = null output,
    @PREFERREDCLASSYEAR dbo.UDT_YEAR = null output,
    @STARTDATE dbo.UDT_FUZZYDATE = null output,
    @DATEGRADUATED dbo.UDT_FUZZYDATE = null output,
    @DATELEFT dbo.UDT_FUZZYDATE = null output,
    @ADDITIONALINFORMATION xml = null output,
    @CONSTITUENCYSTATUSCODE tinyint = null output,
    @DEGREETYPETEXT nvarchar(150) = null output,
    @DEPARTMENTTEXT nvarchar(150) = null output,
    @COLLEGETEXT nvarchar(150) = null output,
    @COMMENT nvarchar(500) = null output,
    @EDUCATIONALHISTORYLEVELCODE nvarchar(150) = null output,
    @USEACADEMICCATALOG bit = null output,
    @REASONCODE uniqueidentifier = null output,
    @EDUCATIONALHISTORYSTATUS nvarchar(100) = null output,
    @EDUCATIONALPROGRAM nvarchar(150) = null output,
    @EDUCATIONALDEGREE nvarchar(150) = null output,
    @EDUCATIONALAWARD nvarchar(150) = null output,
    @REASONCODENAME nvarchar(100) = null output
)
as
    set nocount on;

    set @DATALOADED = 0;

    select
        @DATALOADED = 1,
        @CONSTITUENTID = EDUCATION.CONSTITUENTID,
        @EDUCATIONALINSTITUTION = INSTITUTION.NAME,
        @ACADEMICCATALOGPROGRAM = PROGRAM.PROGRAM,
        @ALUMNUSTEXT = dbo.UFN_EDUCATIONALHISTORY_GETALUMNUSSTATUSTEXT(EDUCATION.ID),
        @ACADEMICCATALOGDEGREE = DEGREE.NAME,
        @CLASSYEAR = CLASSOF,
        @PREFERREDCLASSYEAR = PREFERREDCLASSYEAR,
        @STARTDATE = STARTDATE,
        @DATEGRADUATED = DATEGRADUATED,
        @DATELEFT = DATELEFT,
        @ADDITIONALINFORMATION = dbo.UFN_EDUCATIONALHISTORY_GETADDITIONALINFORMATION_TOITEMLISTXML(@ID),
        @CONSTITUENCYSTATUSCODE = CONSTITUENCYSTATUSCODE,
        @DEGREETYPETEXT = (select top(1) DESCRIPTION from dbo.ACADEMICCATALOGDEGREETYPECODE where len(DESCRIPTION) = (select max(len(DESCRIPTION)) from dbo.ACADEMICCATALOGDEGREETYPECODE)),
        @DEPARTMENTTEXT = (select top(1) NAME from dbo.ACADEMICCATALOGDEPARTMENT where len(NAME) = (select max(len(NAME)) from dbo.ACADEMICCATALOGDEPARTMENT)),
        @COLLEGETEXT = (select top(1) NAME from dbo.ACADEMICCATALOGCOLLEGE where len(NAME) = (select max(len(NAME)) from dbo.ACADEMICCATALOGCOLLEGE)),
        @COMMENT = EDUCATION.COMMENT,
        @EDUCATIONALHISTORYLEVELCODE = dbo.UFN_EDUCATIONALHISTORYLEVELCODE_GETDESCRIPTION(EDUCATION.EDUCATIONALHISTORYLEVELCODEID),
        @REASONCODE = EDUCATION.EDUCATIONALHISTORYREASONCODEID,
        @EDUCATIONALHISTORYSTATUS = [STATUS].[DESCRIPTION],
        @EDUCATIONALPROGRAM = dbo.UFN_EDUCATIONALPROGRAMCODE_GETDESCRIPTION(EDUCATION.EDUCATIONALPROGRAMCODEID),
        @EDUCATIONALDEGREE = dbo.UFN_EDUCATIONALDEGREECODE_GETDESCRIPTION(EDUCATION.EDUCATIONALDEGREECODEID),
        @EDUCATIONALAWARD = dbo.UFN_EDUCATIONALAWARDCODE_GETDESCRIPTION(EDUCATION.EDUCATIONALAWARDCODEID),
        @REASONCODENAME = dbo.EDUCATIONALHISTORYREASONCODE.DESCRIPTION
    from
        dbo.EDUCATIONALHISTORY as EDUCATION
    inner join dbo.EDUCATIONALINSTITUTION as INSTITUTION on EDUCATION.EDUCATIONALINSTITUTIONID = INSTITUTION.ID
    inner join dbo.EDUCATIONALHISTORYSTATUS as [STATUS] on EDUCATION.EDUCATIONALHISTORYSTATUSID = [STATUS].ID
    left join dbo.ACADEMICCATALOGPROGRAM as PROGRAM on EDUCATION.ACADEMICCATALOGPROGRAMID = PROGRAM.ID
    left join dbo.ACADEMICCATALOGDEGREE as DEGREE on EDUCATION.ACADEMICCATALOGDEGREEID = DEGREE.ID
    left join dbo.EDUCATIONALHISTORYREASONCODE on EDUCATION.EDUCATIONALHISTORYREASONCODEID = dbo.EDUCATIONALHISTORYREASONCODE.ID
    where
        EDUCATION.ID = @ID
    order by EDUCATION.ENDDATE desc;

    select top 1
        @USEACADEMICCATALOG = USEACADEMICCATALOG
    from
        dbo.EDUCATIONALCONFIGURATION;

    set @USEACADEMICCATALOG = coalesce(@USEACADEMICCATALOG,0);

    return 0;