USP_DATAFORMTEMPLATE_EDIT_EDUCATIONALHISTORY

The save procedure used by the edit dataform template "Educational History Edit Form".
Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	IN	The input ID parameter indicating the ID of the record being edited.
@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_EDIT_EDUCATIONALHISTORY
                    (
                        @ID uniqueidentifier,
                        @CHANGEAGENTID uniqueidentifier = null,
                        @EDUCATIONALINSTITUTIONID uniqueidentifier,
                        @ISPRIMARYRECORD bit,
                        @KNOWNNAME nvarchar(50),    
                        @EDUCATIONALHISTORYTYPECODEID uniqueidentifier,
                        @EDUCATIONALHISTORYSTATUSCODEID uniqueidentifier,
                        @CONSTITUENCYSTATUSCODE tinyint,
                        @EDUCATIONALDEGREECODEID uniqueidentifier,
                        @STARTDATE dbo.UDT_FUZZYDATE,
                        @ENDDATE dbo.UDT_FUZZYDATE,
                        @EDUCATIONALMAJOR xml,
                        @EDUCATIONALMINOR xml,
                        @GPA decimal(3,2),
                        @CLASSOF dbo.UDT_YEAR,
                        @FRATERNITY nvarchar(50)
                    )
                    as
                        set nocount on;

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

                        begin try                            
                            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 = @CURRENTDATE,
                                    CHANGEDBYID = @CHANGEAGENTID
                                where 
                                    CONSTITUENTID in 
                                            (select CONSTITUENTID 
                                                from dbo.CONSTITUENT 
                                                inner join dbo.EDUCATIONALHISTORY on CONSTITUENT.ID = EDUCATIONALHISTORY.CONSTITUENTID
                                                where EDUCATIONALHISTORY.ID = @ID)
                                    and ISPRIMARYRECORD = 1
                                    and @ISPRIMARYRECORD = 1;                            

                            update dbo.EDUCATIONALHISTORY
                                set EDUCATIONALINSTITUTIONID = @EDUCATIONALINSTITUTIONID,
                                EDUCATIONALHISTORYTYPECODEID = @EDUCATIONALHISTORYTYPECODEID,
                                ISPRIMARYRECORD = @ISPRIMARYRECORD,
                                KNOWNNAME = @KNOWNNAME,
                                EDUCATIONALHISTORYSTATUSCODEID = @EDUCATIONALHISTORYSTATUSCODEID,
                                CONSTITUENCYSTATUSCODE = @CONSTITUENCYSTATUSCODE,
                                EDUCATIONALDEGREECODEID = @EDUCATIONALDEGREECODEID,
                                STARTDATE = @STARTDATE,
                                ENDDATE = @ENDDATE,
                                GPA = coalesce(@GPA, 0),
                                CLASSOF = @CLASSOF,
                                FRATERNITY = @FRATERNITY,
                                DATECHANGED = @CURRENTDATE,
                                CHANGEDBYID = @CHANGEAGENTID
                            where ID = @ID;

                            exec dbo.USP_EDUCATIONALHISTORY_GETMAJOR_UPDATEFROMXML @ID, @EDUCATIONALMAJOR, @CHANGEAGENTID;
                            exec dbo.USP_EDUCATIONALHISTORY_GETMINOR_UPDATEFROMXML @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)
                                    if not exists(select ID from dbo.EDUCATIONALHISTORYSTATUSHISTORY where EDUCATIONALHISTORYID=@ID AND EDUCATIONALHISTORYSTATUSID=@EDUCATIONALHISTORYSTATUSID)
                                    begin
                                        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
                                    else
                                        UPDATE [dbo].[EDUCATIONALHISTORYSTATUSHISTORY] SET STATUSDATE=@CURRENTDATE,DATECHANGED=@CURRENTDATE,CHANGEDBYID=@CHANGEAGENTID WHERE EDUCATIONALHISTORYID=@ID AND EDUCATIONALHISTORYSTATUSID=@EDUCATIONALHISTORYSTATUSID;
                                end
                            end
                        end try
                        begin catch
                            exec dbo.USP_RAISE_ERROR;
                            return 1;
                        end catch

                        return 0;