USP_DATAFORMTEMPLATE_ADD_EDUCATIONALHISTORY_2

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

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	INOUT	The output parameter indicating the ID of the record added.
@CHANGEAGENTID	uniqueidentifier	IN	Input parameter indicating the ID of the change agent invoking the procedure.
@CONSTITUENTID	uniqueidentifier	IN	Input parameter indicating the context ID for the record being added.
@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_ADD_EDUCATIONALHISTORY_2
(
    @ID uniqueidentifier = null output,
    @CHANGEAGENTID uniqueidentifier = null,
    @CONSTITUENTID uniqueidentifier,
    @ISPRIMARYRECORD bit = null,
    @EDUCATIONALINSTITUTIONID uniqueidentifier,
    @ACADEMICCATALOGPROGRAMID uniqueidentifier = null,
    @EDUCATIONALPROGRAMCODEID uniqueidentifier = null,
    @CONSTITUENCYSTATUSCODE tinyint = 0,
    @DATEGRADUATED dbo.UDT_FUZZYDATE = null,
    @DATELEFT dbo.UDT_FUZZYDATE = null,
    @ACADEMICCATALOGDEGREEID uniqueidentifier = null,
    @EDUCATIONALDEGREECODEID uniqueidentifier = null,
    @EDUCATIONALAWARDCODEID uniqueidentifier = null,
    @STARTDATE dbo.UDT_FUZZYDATE = null,
    @CLASSYEAR dbo.UDT_YEAR = null,
    @PREFERREDCLASSYEAR dbo.UDT_YEAR = null,
    @EDUCATIONALSOURCECODEID uniqueidentifier = null,
    @EDUCATIONALSOURCEDATE dbo.UDT_FUZZYDATE = null,
    @COMMENT nvarchar(500) = null,
    @AFFILIATEDADDITIONALINFORMATION xml = null,
    @UNAFFILIATEDADDITIONALINFORMATION xml = null,
    @ISAFFILIATED bit = null,
    @EDUCATIONALHISTORYLEVELCODEID uniqueidentifier = null,
    @EDUCATIONALHISTORYREASONCODEID uniqueidentifier = null,
        @EDUCATIONALHISTORYSTATUSID uniqueidentifier = null
)
as
    set nocount on;

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

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

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

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

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

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

        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
            insert into dbo.EDUCATIONALHISTORY
            (
                ID,
                CONSTITUENTID,
                ISPRIMARYRECORD,
                EDUCATIONALINSTITUTIONID,
                ACADEMICCATALOGPROGRAMID,
                CONSTITUENCYSTATUSCODE,
                DATEGRADUATED,
                DATELEFT,
                ACADEMICCATALOGDEGREEID,
                EDUCATIONALAWARDCODEID,
                STARTDATE,
                CLASSOF,
                PREFERREDCLASSYEAR,
                EDUCATIONALSOURCECODEID,
                EDUCATIONALSOURCEDATE,
                COMMENT,
                EDUCATIONALHISTORYLEVELCODEID,
                EDUCATIONALHISTORYREASONCODEID,
                ADDEDBYID, 
                CHANGEDBYID, 
                DATEADDED, 
                DATECHANGED
            )
            values
            (
                @ID,
                @CONSTITUENTID,
                @ISPRIMARYRECORD,
                @EDUCATIONALINSTITUTIONID,
                @ACADEMICCATALOGPROGRAMID,
                @CONSTITUENCYSTATUSCODE,
                @DATEGRADUATED,
                @DATELEFT,
                @ACADEMICCATALOGDEGREEID,
                @EDUCATIONALAWARDCODEID,
                @STARTDATE,
                @CLASSYEAR,
                @PREFERREDCLASSYEAR,
                @EDUCATIONALSOURCECODEID,
                @EDUCATIONALSOURCEDATE,
                @COMMENT,
                @EDUCATIONALHISTORYLEVELCODEID,
                @EDUCATIONALHISTORYREASONCODEID,
                @CHANGEAGENTID,
                @CHANGEAGENTID,
                @CURRENTDATE,
                @CURRENTDATE
            );

            exec dbo.USP_EDUCATIONALHISTORY_GETAFFILIATEDADDITIONALINFORMATION_ADDFROMXML @ID, @AFFILIATEDADDITIONALINFORMATION, @CHANGEAGENTID;
        end
        else
        begin
            insert into dbo.EDUCATIONALHISTORY
            (
                ID,
                CONSTITUENTID,
                ISPRIMARYRECORD,
                EDUCATIONALINSTITUTIONID,
                EDUCATIONALPROGRAMCODEID,
                CONSTITUENCYSTATUSCODE,
                DATEGRADUATED,
                DATELEFT,
                EDUCATIONALDEGREECODEID,
                EDUCATIONALAWARDCODEID,
                STARTDATE,
                CLASSOF,
                PREFERREDCLASSYEAR,
                EDUCATIONALSOURCECODEID,
                EDUCATIONALSOURCEDATE,
                COMMENT,
                EDUCATIONALHISTORYLEVELCODEID,
                EDUCATIONALHISTORYREASONCODEID,
                ADDEDBYID, 
                CHANGEDBYID, 
                DATEADDED, 
                DATECHANGED
            )
            values
            (
                @ID,
                @CONSTITUENTID,
                @ISPRIMARYRECORD,
                @EDUCATIONALINSTITUTIONID,
                @EDUCATIONALPROGRAMCODEID,
                @CONSTITUENCYSTATUSCODE,
                @DATEGRADUATED,
                @DATELEFT,
                @EDUCATIONALDEGREECODEID,
                @EDUCATIONALAWARDCODEID,
                @STARTDATE,
                @CLASSYEAR,
                @PREFERREDCLASSYEAR,
                @EDUCATIONALSOURCECODEID,
                @EDUCATIONALSOURCEDATE,
                @COMMENT,
                @EDUCATIONALHISTORYLEVELCODEID,
                @EDUCATIONALHISTORYREASONCODEID,
                @CHANGEAGENTID,
                @CHANGEAGENTID,
                @CURRENTDATE,
                @CURRENTDATE
            );

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

        if @EDUCATIONALHISTORYSTATUSID is 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 try
    begin catch
        exec dbo.USP_RAISE_ERROR;
        return 1;
    end catch

    return 0;