USP_DATAFORMTEMPLATE_ADD_EDUCATIONALHISTORY

The save procedure used by the add dataform template "Educational History Add Form".
Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	INOUT	The output parameter indicating the ID of the record added.
@CONSTITUENTID	uniqueidentifier	IN	Input parameter indicating the context ID for the record being added.
@CHANGEAGENTID	uniqueidentifier	IN	Input parameter indicating the ID of the change agent invoking the procedure.
@EDUCATIONALINSTITUTIONID	uniqueidentifier	IN	Educational institution
@ISPRIMARYRECORD	bit	IN	Primary alumni information
@KNOWNNAME	nvarchar(50)	IN	Known name
@EDUCATIONALHISTORYTYPECODEID	uniqueidentifier	IN	Type
@EDUCATIONALHISTORYSTATUSCODEID	uniqueidentifier	IN	Detail
@CONSTITUENCYSTATUSCODE	tinyint	IN	Status
@EDUCATIONALDEGREECODEID	uniqueidentifier	IN	Degree
@STARTDATE	UDT_FUZZYDATE	IN	Start date
@ENDDATE	UDT_FUZZYDATE	IN	End date
@EDUCATIONALMAJOR	xml	IN	Major
@EDUCATIONALMINOR	xml	IN	Minor
@GPA	decimal(3, 2)	IN	GPA
@CLASSOF	UDT_YEAR	IN	Class of
@FRATERNITY	nvarchar(50)	IN	Greek affiliation
Definition

Copy


                    CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_EDUCATIONALHISTORY
                    (
                        @ID uniqueidentifier = null output,
                        @CONSTITUENTID uniqueidentifier,
                        @CHANGEAGENTID uniqueidentifier = null,    
                        @EDUCATIONALINSTITUTIONID uniqueidentifier,
                        @ISPRIMARYRECORD bit = 0,
                        @KNOWNNAME nvarchar(50) = '',
                        @EDUCATIONALHISTORYTYPECODEID uniqueidentifier = null,
                        @EDUCATIONALHISTORYSTATUSCODEID uniqueidentifier = null,
                        @CONSTITUENCYSTATUSCODE tinyint = 0,
                        @EDUCATIONALDEGREECODEID uniqueidentifier = null,
                        @STARTDATE dbo.UDT_FUZZYDATE = null,
                        @ENDDATE dbo.UDT_FUZZYDATE = null,
                        @EDUCATIONALMAJOR xml = null,
                        @EDUCATIONALMINOR xml = null,
                        @GPA decimal(3,2) = null,
                        @CLASSOF dbo.UDT_YEAR = null,
                        @FRATERNITY nvarchar(50) = ''
                    )
                    as
                        set nocount on;

                        declare @CURRENTDATE datetime;
                        declare @ISAFFILIATED bit;
                        declare @STATUSREASON nvarchar(100); 
                        declare @EDUCATIONALHISTORYSTATUSID uniqueidentifier;

                        begin try
                            if @ID is null
                                set @ID = newid();

                            if @CHANGEAGENTID is null  
                                exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

                            set @CURRENTDATE = getdate();

                            --reset Primary Record Flag for existing records

                            update dbo.EDUCATIONALHISTORY
                                set ISPRIMARYRECORD = 0,
                                    DATECHANGED = getdate(),
                                    CHANGEDBYID = @CHANGEAGENTID
                                where 
                                    CONSTITUENTID = @CONSTITUENTID
                                    and ISPRIMARYRECORD = 1
                                    and @ISPRIMARYRECORD = 1;


                            insert into dbo.EDUCATIONALHISTORY
                            (
                                ID, 
                                CONSTITUENTID,
                                EDUCATIONALINSTITUTIONID,
                                ISPRIMARYRECORD,
                                KNOWNNAME,
                                EDUCATIONALHISTORYTYPECODEID,
                                EDUCATIONALHISTORYSTATUSCODEID,
                                CONSTITUENCYSTATUSCODE,
                                EDUCATIONALDEGREECODEID,
                                STARTDATE,
                                ENDDATE,
                                GPA,
                                CLASSOF,
                                FRATERNITY,
                                ADDEDBYID, 
                                CHANGEDBYID, 
                                DATEADDED, 
                                DATECHANGED
                            )
                            values
                            (
                                @ID, 
                                @CONSTITUENTID,
                                @EDUCATIONALINSTITUTIONID,
                                @ISPRIMARYRECORD,
                                @KNOWNNAME,
                                @EDUCATIONALHISTORYTYPECODEID,
                                @EDUCATIONALHISTORYSTATUSCODEID,
                                @CONSTITUENCYSTATUSCODE,
                                @EDUCATIONALDEGREECODEID,
                                @STARTDATE,
                                @ENDDATE,
                                coalesce(@GPA, 0),
                                @CLASSOF,
                                @FRATERNITY,
                                @CHANGEAGENTID, 
                                @CHANGEAGENTID, 
                                @CURRENTDATE, 
                                @CURRENTDATE
                            );

                            exec dbo.USP_EDUCATIONALHISTORY_GETMAJOR_ADDFROMXML @ID, @EDUCATIONALMAJOR, @CHANGEAGENTID;
                            exec dbo.USP_EdUCATIONALHISTORY_GETMINOR_ADDFROMXML @ID, @EDUCATIONALMINOR, @CHANGEAGENTID;

                            select @ISAFFILIATED=ISAFFILIATED from dbo.EDUCATIONALINSTITUTION where ID=@EDUCATIONALINSTITUTIONID

                            if @ISAFFILIATED = 1
                            begin
                                if @CONSTITUENCYSTATUSCODE=1 or @CONSTITUENCYSTATUSCODE=3
                                begin
                                    set @EDUCATIONALHISTORYSTATUSID=dbo.UFN_EDUCATIONALHISTORYSTATUS_FROMCONSTITUENCYSTATUSCODE(@CONSTITUENCYSTATUSCODE)
                                    select @STATUSREASON=DESCRIPTION from dbo.EDUCATIONALHISTORYSTATUS where ID=@EDUCATIONALHISTORYSTATUSID;
                                    INSERT INTO [dbo].[EDUCATIONALHISTORYSTATUSHISTORY]
                                        (
                                            [EDUCATIONALHISTORYID],
                                            [EDUCATIONALHISTORYSTATUSID],
                                            [STATUSDATE],
                                            [STATUSREASON],
                                            [ADDEDBYID],
                                            [CHANGEDBYID],
                                            [DATEADDED],
                                            [DATECHANGED]
                                            )
                                            values
                                            (
                                            @ID,
                                            @EDUCATIONALHISTORYSTATUSID,
                                            @CURRENTDATE,
                                            @STATUSREASON,
                                            @CHANGEAGENTID,
                                            @CHANGEAGENTID,
                                            @CURRENTDATE,
                                            @CURRENTDATE
                                        );
                                end
                            end
                        end try
                        begin catch
                            exec dbo.USP_RAISE_ERROR;
                            return 1;
                        end catch

                        return 0;