USP_BBNC_COMMITPROFILEUPDATEEDUCATIONADDITIONALINFO

Updates a constituent's education additional information from a Blackbaud Internet Solutions profile transaction to the system from a given batch.
Parameters

Parameter	Parameter Type	Mode
@EDUCATIONALHISTORYID	uniqueidentifier	IN
@ADDNEWRECORD	bit	IN
@EDUCATIONADDITIONALINFORMATIONID	uniqueidentifier	IN
@EDUCATIONALCOLLEGECODEID	uniqueidentifier	IN
@EDUCATIONALDEPARTMENTCODEID	uniqueidentifier	IN
@EDUCATIONALDEGREETYPECODEID	uniqueidentifier	IN
@EDUCATIONALDIVISIONCODEID	uniqueidentifier	IN
@EDUCATIONALSUBDEPARTMENTCODEID	uniqueidentifier	IN
@CHANGEAGENTID	uniqueidentifier	IN
@CHANGEDATE	datetime	IN
@ACADEMICCATALOGCOLLEGEID	uniqueidentifier	IN
@ACADEMICCATALOGDIVISIONID	uniqueidentifier	IN
@ACADEMICCATALOGDEPARTMENTID	uniqueidentifier	IN
@ACADEMICCATALOGSUBDEPARTMENTID	uniqueidentifier	IN
@ACADEMICCATALOGDEGREETYPEID	uniqueidentifier	IN
Definition

Copy


        CREATE procedure dbo.USP_BBNC_COMMITPROFILEUPDATEEDUCATIONADDITIONALINFO
        (
            @EDUCATIONALHISTORYID uniqueidentifier = null,
            @ADDNEWRECORD bit = 0,
            @EDUCATIONADDITIONALINFORMATIONID uniqueidentifier = null,
            @EDUCATIONALCOLLEGECODEID uniqueidentifier = null,
            @EDUCATIONALDEPARTMENTCODEID uniqueidentifier = null,
            @EDUCATIONALDEGREETYPECODEID uniqueidentifier = null,
            @EDUCATIONALDIVISIONCODEID uniqueidentifier = null,
            @EDUCATIONALSUBDEPARTMENTCODEID uniqueidentifier = null,
            @CHANGEAGENTID uniqueidentifier = null,
            @CHANGEDATE datetime = null,
            @ACADEMICCATALOGCOLLEGEID uniqueidentifier = null,
            @ACADEMICCATALOGDIVISIONID uniqueidentifier = null,
            @ACADEMICCATALOGDEPARTMENTID uniqueidentifier = null,
            @ACADEMICCATALOGSUBDEPARTMENTID uniqueidentifier = null,
            @ACADEMICCATALOGDEGREETYPEID uniqueidentifier = null
        )
        as

            set nocount on;

            if @EDUCATIONALHISTORYID is null
                begin
                    raiserror('The educational history ID is required',16,1);
                    return -2;
                end

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

            if @CHANGEDATE is null
                set @CHANGEDATE = getdate();

            begin try
                if @ADDNEWRECORD = 0
                    begin
                        if @EDUCATIONADDITIONALINFORMATIONID is not null
                            begin                                           
                                if (@EDUCATIONALCOLLEGECODEID is null 
                                    and @EDUCATIONALDEPARTMENTCODEID is null
                                    and @EDUCATIONALDEGREETYPECODEID is null
                                    and @EDUCATIONALDIVISIONCODEID is null
                                    and @EDUCATIONALSUBDEPARTMENTCODEID is null
                                    and @ACADEMICCATALOGCOLLEGEID is null
                                    and @ACADEMICCATALOGDIVISIONID is null
                                    and @ACADEMICCATALOGDEPARTMENTID is null
                                    and @ACADEMICCATALOGSUBDEPARTMENTID is null
                                    and @ACADEMICCATALOGDEGREETYPEID is null)
                                begin
                                    exec dbo.USP_EDUCATIONADDITIONALINFORMATION_DELETEBYID_WITHCHANGEAGENTID @EDUCATIONADDITIONALINFORMATIONID, @CHANGEAGENTID;
                                end
                                else
                                begin
                                    if not exists(select ID from dbo.EDUCATIONADDITIONALINFORMATION where ID = @EDUCATIONADDITIONALINFORMATIONID)
                                    begin
                                        raiserror('BBERR_EDUCATIONADDITIONALINFORMATIONIDDOESNOTEXIST',16,1);
                                        return -2;
                                    end
                                    else
                                    begin
                                        update 
                                            dbo.EDUCATIONADDITIONALINFORMATION
                                        set
                                            EDUCATIONALCOLLEGECODEID = @EDUCATIONALCOLLEGECODEID,
                                            EDUCATIONALDEPARTMENTCODEID = @EDUCATIONALDEPARTMENTCODEID,
                                            EDUCATIONALDEGREETYPECODEID = @EDUCATIONALDEGREETYPECODEID,
                                            EDUCATIONALDIVISIONCODEID = @EDUCATIONALDIVISIONCODEID,
                                            EDUCATIONALSUBDEPARTMENTCODEID = @EDUCATIONALSUBDEPARTMENTCODEID,
                                            CHANGEDBYID = @CHANGEAGENTID, 
                                            DATECHANGED = @CHANGEDATE,
                                            ACADEMICCATALOGCOLLEGEID =@ACADEMICCATALOGCOLLEGEID,
                                            ACADEMICCATALOGDIVISIONID = @ACADEMICCATALOGDIVISIONID,
                                            ACADEMICCATALOGDEPARTMENTID  = @ACADEMICCATALOGDEPARTMENTID,
                                            ACADEMICCATALOGSUBDEPARTMENTID = @ACADEMICCATALOGSUBDEPARTMENTID,
                                            ACADEMICCATALOGDEGREETYPEID =@ACADEMICCATALOGDEGREETYPEID
                                        where
                                            ID = @EDUCATIONADDITIONALINFORMATIONID;
                                    end
                                end
                            end
                    end
                else
                    begin
                        if not exists(select ID from dbo.EDUCATIONALHISTORY where ID = @EDUCATIONALHISTORYID)
                        begin
                            raiserror('BBERR_EDUCATIONALHISTORYIDDOESNOTEXIST',16,1);
                            return -2;
                        end

                        set @EDUCATIONADDITIONALINFORMATIONID = newid();
                        insert into dbo.EDUCATIONADDITIONALINFORMATION
                        (
                            ID,
                            EDUCATIONALHISTORYID,
                            EDUCATIONALCOLLEGECODEID,
                            EDUCATIONALDEPARTMENTCODEID,
                            EDUCATIONALDEGREETYPECODEID,
                            EDUCATIONALDIVISIONCODEID,
                            EDUCATIONALSUBDEPARTMENTCODEID,
                            ADDEDBYID, 
                            CHANGEDBYID, 
                            DATEADDED, 
                            DATECHANGED,
                            ACADEMICCATALOGCOLLEGEID,
                            ACADEMICCATALOGDIVISIONID,
                            ACADEMICCATALOGDEPARTMENTID,
                            ACADEMICCATALOGSUBDEPARTMENTID,
                            ACADEMICCATALOGDEGREETYPEID
                        )
                        values
                        (
                            @EDUCATIONADDITIONALINFORMATIONID,
                            @EDUCATIONALHISTORYID,
                            @EDUCATIONALCOLLEGECODEID,
                            @EDUCATIONALDEPARTMENTCODEID,
                            @EDUCATIONALDEGREETYPECODEID,
                            @EDUCATIONALDIVISIONCODEID,
                            @EDUCATIONALSUBDEPARTMENTCODEID,
                            @CHANGEAGENTID,
                            @CHANGEAGENTID,
                            @CHANGEDATE,
                            @CHANGEDATE,
                            @ACADEMICCATALOGCOLLEGEID,
                            @ACADEMICCATALOGDIVISIONID,
                            @ACADEMICCATALOGDEPARTMENTID,
                            @ACADEMICCATALOGSUBDEPARTMENTID,
                            @ACADEMICCATALOGDEGREETYPEID
                        );
                    end
            end try
            begin catch
                exec dbo.USP_RAISE_ERROR;
                return 1;
            end catch

            return 0;