USP_DATAFORMTEMPLATE_EDIT_EDUCATIONALHISTORY_2

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

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.
@ISPRIMARYRECORD	bit	IN	Primary education information
@EDUCATIONALINSTITUTIONID	uniqueidentifier	IN	Educational institution
@ACADEMICCATALOGPROGRAMID	uniqueidentifier	IN	Program
@EDUCATIONALPROGRAMCODEID	uniqueidentifier	IN	Program
@CONSTITUENCYSTATUSCODE	tinyint	IN	Status
@DATEGRADUATED	UDT_FUZZYDATE	IN	Graduation date
@DATELEFT	UDT_FUZZYDATE	IN	To
@ACADEMICCATALOGDEGREEID	uniqueidentifier	IN	Degree
@EDUCATIONALDEGREECODEID	uniqueidentifier	IN	Degree
@EDUCATIONALAWARDCODEID	uniqueidentifier	IN	Honor awarded
@STARTDATE	UDT_FUZZYDATE	IN	From
@CLASSYEAR	UDT_YEAR	IN	Class of
@PREFERREDCLASSYEAR	UDT_YEAR	IN	Preferred class of
@EDUCATIONALSOURCECODEID	uniqueidentifier	IN	Information source
@EDUCATIONALSOURCEDATE	UDT_FUZZYDATE	IN	Source date
@COMMENT	nvarchar(500)	IN	Comments
@AFFILIATEDADDITIONALINFORMATION	xml	IN
@UNAFFILIATEDADDITIONALINFORMATION	xml	IN
@ISAFFILIATED	bit	IN	Affiliated
@EDUCATIONALHISTORYLEVELCODEID	uniqueidentifier	IN	Level
@EDUCATIONALHISTORYREASONCODEID	uniqueidentifier	IN	Reason
@EDUCATIONALHISTORYSTATUSID	uniqueidentifier	IN	Status

Definition

Copy



CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_EDUCATIONALHISTORY_2
(
    @ID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier = null,
    @ISPRIMARYRECORD bit,
    @EDUCATIONALINSTITUTIONID uniqueidentifier,
    @ACADEMICCATALOGPROGRAMID uniqueidentifier,
    @EDUCATIONALPROGRAMCODEID uniqueidentifier,
    @CONSTITUENCYSTATUSCODE tinyint,
    @DATEGRADUATED dbo.UDT_FUZZYDATE,
    @DATELEFT dbo.UDT_FUZZYDATE,
    @ACADEMICCATALOGDEGREEID uniqueidentifier,
    @EDUCATIONALDEGREECODEID uniqueidentifier,
    @EDUCATIONALAWARDCODEID uniqueidentifier,
    @STARTDATE dbo.UDT_FUZZYDATE,
    @CLASSYEAR dbo.UDT_YEAR,
    @PREFERREDCLASSYEAR dbo.UDT_YEAR,
    @EDUCATIONALSOURCECODEID uniqueidentifier,
    @EDUCATIONALSOURCEDATE dbo.UDT_FUZZYDATE,
    @COMMENT nvarchar(500),
    @AFFILIATEDADDITIONALINFORMATION xml,
    @UNAFFILIATEDADDITIONALINFORMATION xml,
    @ISAFFILIATED bit,
    @EDUCATIONALHISTORYLEVELCODEID uniqueidentifier,
    @EDUCATIONALHISTORYREASONCODEID uniqueidentifier,
    @EDUCATIONALHISTORYSTATUSID uniqueidentifier
)
as
    set nocount on;

    declare @CURRENTDATE datetime;
    set @CURRENTDATE = getdate();

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

    if @DATEGRADUATED is null
        set @DATEGRADUATED = '00000000';

    if @DATELEFT is null
        set @DATELEFT = '00000000';

    declare @OLDEDUCATIONALINSTITUTIONID uniqueidentifier;
    select @OLDEDUCATIONALINSTITUTIONID = EDUCATIONALINSTITUTIONID
      from dbo.EDUCATIONALHISTORY
      where ID = @ID;

    --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;    


    begin try
        declare @USEACADEMICCATALOG bit;                        
        select top 1
            @USEACADEMICCATALOG = EC.USEACADEMICCATALOG
        from
            dbo.EDUCATIONALCONFIGURATION EC;

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

        if @USEACADEMICCATALOG = 1 and @ISAFFILIATED = 1
        begin

            --Bug 56849 - AdamBu 1/27/10 - Clear educational program\degree when using an 

            --    academic catalog program/degree

            update dbo.EDUCATIONALHISTORY
            set
                ISPRIMARYRECORD = @ISPRIMARYRECORD,
                EDUCATIONALINSTITUTIONID = @EDUCATIONALINSTITUTIONID,
                ACADEMICCATALOGPROGRAMID = @ACADEMICCATALOGPROGRAMID,
                EDUCATIONALPROGRAMCODEID = null,
                CONSTITUENCYSTATUSCODE = @CONSTITUENCYSTATUSCODE,
                DATEGRADUATED = @DATEGRADUATED,
                DATELEFT = @DATELEFT,
                ACADEMICCATALOGDEGREEID = @ACADEMICCATALOGDEGREEID,
                EDUCATIONALDEGREECODEID = null,
                EDUCATIONALAWARDCODEID = @EDUCATIONALAWARDCODEID,
                STARTDATE = @STARTDATE,
                CLASSOF = @CLASSYEAR,
                PREFERREDCLASSYEAR = @PREFERREDCLASSYEAR,
                EDUCATIONALSOURCECODEID = @EDUCATIONALSOURCECODEID,
                EDUCATIONALSOURCEDATE = @EDUCATIONALSOURCEDATE,
                COMMENT = @COMMENT,
                EDUCATIONALHISTORYLEVELCODEID = @EDUCATIONALHISTORYLEVELCODEID,
                EDUCATIONALHISTORYREASONCODEID = @EDUCATIONALHISTORYREASONCODEID,
                CHANGEDBYID = @CHANGEAGENTID, 
                DATECHANGED = @CURRENTDATE
            where
                ID = @ID;

            exec dbo.USP_EDUCATIONALHISTORY_GETAFFILIATEDADDITIONALINFORMATION_UPDATEFROMXML @ID, @AFFILIATEDADDITIONALINFORMATION, @CHANGEAGENTID;
        end
        else
        begin

            --Bug 56849 - AdamBu 1/27/10 - Clear academic catalog program\degree 

            --    when using an educational program/degree

            update dbo.EDUCATIONALHISTORY
            set
                ISPRIMARYRECORD = @ISPRIMARYRECORD,
                EDUCATIONALINSTITUTIONID = @EDUCATIONALINSTITUTIONID,
                ACADEMICCATALOGPROGRAMID = null,
                EDUCATIONALPROGRAMCODEID = @EDUCATIONALPROGRAMCODEID,
                CONSTITUENCYSTATUSCODE = @CONSTITUENCYSTATUSCODE,
                DATEGRADUATED = @DATEGRADUATED,
                DATELEFT = @DATELEFT,
                ACADEMICCATALOGDEGREEID = null,
                EDUCATIONALDEGREECODEID = @EDUCATIONALDEGREECODEID,
                EDUCATIONALAWARDCODEID = @EDUCATIONALAWARDCODEID,
                STARTDATE = @STARTDATE,
                CLASSOF = @CLASSYEAR,
                PREFERREDCLASSYEAR = @PREFERREDCLASSYEAR,
                EDUCATIONALSOURCECODEID = @EDUCATIONALSOURCECODEID,
                EDUCATIONALSOURCEDATE = @EDUCATIONALSOURCEDATE,
                COMMENT = @COMMENT,
                EDUCATIONALHISTORYLEVELCODEID = @EDUCATIONALHISTORYLEVELCODEID,
                EDUCATIONALHISTORYREASONCODEID = @EDUCATIONALHISTORYREASONCODEID,
                CHANGEDBYID = @CHANGEAGENTID, 
                DATECHANGED = @CURRENTDATE
            where
                ID = @ID;

            exec dbo.USP_EDUCATIONALHISTORY_GETUNAFFILIATEDADDITIONALINFORMATION_UPDATEFROMXML @ID, @UNAFFILIATEDADDITIONALINFORMATION, @CHANGEAGENTID;
        end

        --update any involvements for this constituent if the educational institution has changed

        if @OLDEDUCATIONALINSTITUTIONID != @EDUCATIONALINSTITUTIONID
        begin
        update dbo.EDUCATIONALINVOLVEMENT
        set
          EDUCATIONALINSTITUTIONID = @EDUCATIONALINSTITUTIONID
        where
          ID in 
            (select EI.ID
              from dbo.EDUCATIONALINVOLVEMENT EI
                  left join dbo.EDUCATIONALHISTORY EH on EH.CONSTITUENTID = EI.CONSTITUENTID
              where
                EH.ID = @ID and EI.EDUCATIONALINSTITUTIONID = @OLDEDUCATIONALINSTITUTIONID);
        end

        if (not exists(select ID from dbo.EDUCATIONALHISTORYSTATUSHISTORY where EDUCATIONALHISTORYID=@ID and EDUCATIONALHISTORYSTATUSID=@EDUCATIONALHISTORYSTATUSID)
            or (select EDUCATIONALHISTORYSTATUSID from dbo.EDUCATIONALHISTORY where ID=@ID) <> @EDUCATIONALHISTORYSTATUSID)
        begin

            if @EDUCATIONALHISTORYSTATUSID = null
                set @EDUCATIONALHISTORYSTATUSID=dbo.UFN_EDUCATIONALHISTORYSTATUS_FROMCONSTITUENCYSTATUSCODE(@CONSTITUENCYSTATUSCODE);

            insert into [dbo].[EDUCATIONALHISTORYSTATUSHISTORY]
            (
                [EDUCATIONALHISTORYID],
                [EDUCATIONALHISTORYSTATUSID],
                [STATUSDATE],
                [ADDEDBYID],
                [CHANGEDBYID],
                [DATEADDED],
                [DATECHANGED]
            )
            values
            (
                @ID,
                @EDUCATIONALHISTORYSTATUSID,                                            
                @CURRENTDATE,
                @CHANGEAGENTID,
                @CHANGEAGENTID,
                @CURRENTDATE,
                @CURRENTDATE
            );

        end
    end try

    begin catch
        exec dbo.USP_RAISE_ERROR;
        return 1;
    end catch

    return 0;