USP_EDUCATIONALHISTORY_VIEWLIST

Gets the educational history list.

Parameters

Parameter	Parameter Type	Mode
@ID	uniqueidentifier	IN
@ISPRIMARYRECORD	bit	INOUT
@EDUCATIONALINSTITUTION	nvarchar(150)	INOUT
@ACADEMICCATALOGPROGRAM	nvarchar(150)	INOUT
@EDUCATIONALPROGRAM	nvarchar(150)	INOUT
@CONSTITUENCYSTATUSCODE	tinyint	INOUT
@CONSTITUENCYSTATUS	nvarchar(150)	INOUT
@DATEGRADUATED	UDT_FUZZYDATE	INOUT
@DATELEFT	UDT_FUZZYDATE	INOUT
@ACADEMICCATALOGDEGREE	nvarchar(150)	INOUT
@EDUCATIONALDEGREE	nvarchar(150)	INOUT
@EDUCATIONALAWARD	nvarchar(150)	INOUT
@STARTDATE	UDT_FUZZYDATE	INOUT
@CLASSYEAR	UDT_YEAR	INOUT
@PREFERREDCLASSYEAR	UDT_YEAR	INOUT
@EDUCATIONALSOURCE	nvarchar(150)	INOUT
@EDUCATIONALSOURCEDATE	UDT_FUZZYDATE	INOUT
@COMMENT	nvarchar(500)	INOUT
@ISAFFILIATED	bit	INOUT
@AWARD	nvarchar(150)	INOUT
@ADDITIONALINFORMATION	xml	INOUT
@EDUCATIONALHISTORYLEVELCODE	nvarchar(150)	INOUT
@USEACADEMICCATALOG	bit	INOUT
@REASONCODE	uniqueidentifier	INOUT
@EDUCATIONALHISTORYSTATUSID	uniqueidentifier	INOUT
@EDUCATIONALHISTORYSTATUS	nvarchar(100)	INOUT
@REASONCODENAME	nvarchar(100)	INOUT

Definition

Copy



            CREATE procedure dbo.USP_EDUCATIONALHISTORY_VIEWLIST
            (
                @ID uniqueidentifier,
                @ISPRIMARYRECORD bit = null output,
                @EDUCATIONALINSTITUTION nvarchar(150) = null output,
                @ACADEMICCATALOGPROGRAM nvarchar(150) = null output,
                @EDUCATIONALPROGRAM nvarchar(150) = null output,
                @CONSTITUENCYSTATUSCODE tinyint = null output,
                @CONSTITUENCYSTATUS nvarchar(150) = null output,
                @DATEGRADUATED dbo.UDT_FUZZYDATE = null output,
                @DATELEFT dbo.UDT_FUZZYDATE = null output,
                @ACADEMICCATALOGDEGREE nvarchar(150) = null output,
                @EDUCATIONALDEGREE nvarchar(150) = null output,
                @EDUCATIONALAWARD nvarchar(150) = null output,
                @STARTDATE dbo.UDT_FUZZYDATE = null output,
                @CLASSYEAR dbo.UDT_YEAR = null output,
                @PREFERREDCLASSYEAR dbo.UDT_YEAR = null output,
                @EDUCATIONALSOURCE nvarchar(150) = null output,
                @EDUCATIONALSOURCEDATE dbo.UDT_FUZZYDATE = null output,
                @COMMENT nvarchar(500) = null output,
                @ISAFFILIATED bit = null output,
                @AWARD nvarchar(150) = null output,
                @ADDITIONALINFORMATION xml = null output,
                @EDUCATIONALHISTORYLEVELCODE nvarchar(150) = null output,
                @USEACADEMICCATALOG bit = null output,
                @REASONCODE uniqueidentifier = null output,
                @EDUCATIONALHISTORYSTATUSID uniqueidentifier = null output,
                @EDUCATIONALHISTORYSTATUS nvarchar(100) = null output,
                @REASONCODENAME nvarchar(100) = null output
            )
            as
                set nocount on;

                select
                    @ISPRIMARYRECORD = EDUCATION.ISPRIMARYRECORD,
                    @EDUCATIONALINSTITUTION = INSTITUTION.NAME,
                    @ACADEMICCATALOGPROGRAM = PROGRAM.PROGRAM,
                    @EDUCATIONALPROGRAM = dbo.UFN_EDUCATIONALPROGRAMCODE_GETDESCRIPTION(EDUCATION.EDUCATIONALPROGRAMCODEID),
                    @CONSTITUENCYSTATUSCODE = EDUCATION.CONSTITUENCYSTATUSCODE,
                    @CONSTITUENCYSTATUS = EDUCATION.CONSTITUENCYSTATUS,
                    @DATEGRADUATED = DATEGRADUATED,
                    @DATELEFT = DATELEFT,
                    @ACADEMICCATALOGDEGREE = DEGREE.NAME,
                    @EDUCATIONALDEGREE = dbo.UFN_EDUCATIONALDEGREECODE_GETDESCRIPTION(EDUCATION.EDUCATIONALDEGREECODEID),
                    @EDUCATIONALAWARD = dbo.UFN_EDUCATIONALAWARDCODE_GETDESCRIPTION(EDUCATION.EDUCATIONALAWARDCODEID),
                    @STARTDATE = EDUCATION.STARTDATE,
                    @CLASSYEAR = EDUCATION.CLASSOF,
                    @PREFERREDCLASSYEAR = EDUCATION.PREFERREDCLASSYEAR,
                    @EDUCATIONALSOURCE = dbo.UFN_EDUCATIONALSOURCECODE_GETDESCRIPTION(EDUCATION.EDUCATIONALSOURCECODEID),
                    @EDUCATIONALSOURCEDATE = EDUCATION.EDUCATIONALSOURCEDATE,
                    @COMMENT = EDUCATION.COMMENT,
                    @ISAFFILIATED = INSTITUTION.ISAFFILIATED,
                    @AWARD = dbo.UFN_EDUCATIONALAWARDCODE_GETDESCRIPTION(EDUCATION.EDUCATIONALAWARDCODEID),
                    @ADDITIONALINFORMATION = dbo.UFN_EDUCATIONALHISTORY_GETADDITIONALINFORMATION_TOITEMLISTXML(@ID),
                    @EDUCATIONALHISTORYLEVELCODE = dbo.UFN_EDUCATIONALHISTORYLEVELCODE_GETDESCRIPTION(EDUCATION.EDUCATIONALHISTORYLEVELCODEID),
                    @REASONCODE = EDUCATION.EDUCATIONALHISTORYREASONCODEID,
                    @EDUCATIONALHISTORYSTATUSID = [STATUS].ID,
                    @EDUCATIONALHISTORYSTATUS = [STATUS].[DESCRIPTION],
                    @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;

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

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