USP_BBNC_COMMITPROFILEUPDATEEDUCATIONALHISTORY
Updates a constituent's educational history information from a Blackbaud Internet Solutions profile transaction to the system from a given batch.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @ID | uniqueidentifier | IN | |
| @ADDNEWRECORD | bit | IN | |
| @EDUCATIONALHISTORYID | uniqueidentifier | INOUT | |
| @EDUCATIONALINSTITUTIONID | uniqueidentifier | IN | |
| @EDUCATIONALHISTORYSTATUSID | uniqueidentifier | IN | |
| @EDUCATIONALPROGRAMCODEID | uniqueidentifier | IN | |
| @EDUCATIONALDEGREECODEID | uniqueidentifier | IN | |
| @EDUCATIONALAWARDCODEID | uniqueidentifier | IN | |
| @COMMENTS | nvarchar(500) | IN | |
| @STARTDATE | UDT_FUZZYDATE | IN | |
| @ENDDATE | UDT_FUZZYDATE | IN | |
| @GRADUATIONDATE | UDT_FUZZYDATE | IN | |
| @CLASSOF | UDT_YEAR | IN | |
| @PREFERREDCLASSOF | UDT_YEAR | IN | |
| @ISPRIMARY | bit | IN | |
| @CHANGEAGENTID | uniqueidentifier | IN | |
| @CHANGEDATE | datetime | IN | |
| @ACADEMICCATALOGPROGRAMID | uniqueidentifier | IN | |
| @ACADEMICCATALOGDEGREEID | uniqueidentifier | IN |
Definition
Copy
CREATE procedure dbo.USP_BBNC_COMMITPROFILEUPDATEEDUCATIONALHISTORY
(
@ID uniqueidentifier = null,
@ADDNEWRECORD bit = 0,
@EDUCATIONALHISTORYID uniqueidentifier = null output,
@EDUCATIONALINSTITUTIONID uniqueidentifier = null,
@EDUCATIONALHISTORYSTATUSID uniqueidentifier = null,
@EDUCATIONALPROGRAMCODEID uniqueidentifier = null,
@EDUCATIONALDEGREECODEID uniqueidentifier = null,
@EDUCATIONALAWARDCODEID uniqueidentifier = null,
@COMMENTS nvarchar(500) = '',
@STARTDATE dbo.UDT_FUZZYDATE = '00000000',
@ENDDATE dbo.UDT_FUZZYDATE = '00000000',
@GRADUATIONDATE dbo.UDT_FUZZYDATE = '00000000',
@CLASSOF dbo.UDT_YEAR = null,
@PREFERREDCLASSOF dbo.UDT_YEAR = null,
@ISPRIMARY bit = 0,
@CHANGEAGENTID uniqueidentifier = null,
@CHANGEDATE datetime = null,
@ACADEMICCATALOGPROGRAMID uniqueidentifier = null,
@ACADEMICCATALOGDEGREEID uniqueidentifier = null
)
as
set nocount on;
declare @PRIMARYID uniqueidentifier;
if @ID is null
begin
raiserror('The constituent 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 @ISPRIMARY = 1
begin
select
@PRIMARYID = ID
from
dbo.EDUCATIONALHISTORY
where
CONSTITUENTID = @ID and ISPRIMARYRECORD = 1;
if @PRIMARYID is not null and @PRIMARYID <> @EDUCATIONALHISTORYID
begin
update
dbo.EDUCATIONALHISTORY
set
ISPRIMARYRECORD = 0
where
ID = @PRIMARYID;
end
end
if @ADDNEWRECORD = 0
begin
if @EDUCATIONALHISTORYID is not null
begin
if not exists(select ID from dbo.EDUCATIONALHISTORY where ID = @EDUCATIONALHISTORYID)
begin
raiserror('BBERR_EDUCATIONALHISTORYIDDOESNOTEXIST',16,1);
return -2;
end
declare @OLDEDUCATIONALINSTITUTIONID uniqueidentifier;
select @OLDEDUCATIONALINSTITUTIONID = EDUCATIONALINSTITUTIONID
from dbo.EDUCATIONALHISTORY
where ID = @EDUCATIONALHISTORYID;
update
dbo.EDUCATIONALHISTORY
set
EDUCATIONALINSTITUTIONID = @EDUCATIONALINSTITUTIONID,
EDUCATIONALPROGRAMCODEID = @EDUCATIONALPROGRAMCODEID,
EDUCATIONALDEGREECODEID = @EDUCATIONALDEGREECODEID,
EDUCATIONALAWARDCODEID = @EDUCATIONALAWARDCODEID,
COMMENT = @COMMENTS,
STARTDATE = @STARTDATE,
DATELEFT = @ENDDATE,
DATEGRADUATED = @GRADUATIONDATE,
CLASSOF = coalesce(@CLASSOF, 0),
PREFERREDCLASSYEAR = coalesce(@PREFERREDCLASSOF, 0),
ISPRIMARYRECORD = @ISPRIMARY,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CHANGEDATE,
ACADEMICCATALOGPROGRAMID = @ACADEMICCATALOGPROGRAMID,
ACADEMICCATALOGDEGREEID = @ACADEMICCATALOGDEGREEID
where
ID = @EDUCATIONALHISTORYID;
--update any involvements for this constituent if the educational institution has changed
if @OLDEDUCATIONALINSTITUTIONID != @EDUCATIONALINSTITUTIONID
update
dbo.EDUCATIONALINVOLVEMENT
set
EDUCATIONALINSTITUTIONID = @EDUCATIONALINSTITUTIONID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CHANGEDATE
where
ID in
(select EI.ID
from dbo.EDUCATIONALINVOLVEMENT EI
left join dbo.EDUCATIONALHISTORY EH on EH.CONSTITUENTID = EI.CONSTITUENTID
where
EH.ID = @EDUCATIONALHISTORYID and EI.EDUCATIONALINSTITUTIONID = @OLDEDUCATIONALINSTITUTIONID);
if (not exists(select ID from dbo.EDUCATIONALHISTORYSTATUSHISTORY where EDUCATIONALHISTORYID=@EDUCATIONALHISTORYID and EDUCATIONALHISTORYSTATUSID=@EDUCATIONALHISTORYSTATUSID)
or (select EDUCATIONALHISTORYSTATUSID from dbo.EDUCATIONALHISTORY where ID=@EDUCATIONALHISTORYID) <> @EDUCATIONALHISTORYSTATUSID)
insert into dbo.EDUCATIONALHISTORYSTATUSHISTORY
(
EDUCATIONALHISTORYID,
EDUCATIONALHISTORYSTATUSID,
STATUSDATE,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
values
(
@EDUCATIONALHISTORYID,
@EDUCATIONALHISTORYSTATUSID,
@CHANGEDATE,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CHANGEDATE,
@CHANGEDATE
);
end
end
else
begin
set @EDUCATIONALHISTORYID = newid();
insert into dbo.EDUCATIONALHISTORY
(
ID,
CONSTITUENTID,
EDUCATIONALINSTITUTIONID,
EDUCATIONALPROGRAMCODEID,
EDUCATIONALDEGREECODEID,
EDUCATIONALAWARDCODEID,
COMMENT,
STARTDATE,
DATELEFT,
DATEGRADUATED,
CLASSOF,
PREFERREDCLASSYEAR,
ISPRIMARYRECORD,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED,
ACADEMICCATALOGPROGRAMID,
ACADEMICCATALOGDEGREEID
)
values
(
@EDUCATIONALHISTORYID,
@ID,
@EDUCATIONALINSTITUTIONID,
@EDUCATIONALPROGRAMCODEID,
@EDUCATIONALDEGREECODEID,
@EDUCATIONALAWARDCODEID,
@COMMENTS,
@STARTDATE,
@ENDDATE,
@GRADUATIONDATE,
coalesce(@CLASSOF, 0),
coalesce(@PREFERREDCLASSOF, 0),
@ISPRIMARY,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CHANGEDATE,
@CHANGEDATE,
@ACADEMICCATALOGPROGRAMID,
@ACADEMICCATALOGDEGREEID
);
insert into dbo.EDUCATIONALHISTORYSTATUSHISTORY
(
EDUCATIONALHISTORYID,
EDUCATIONALHISTORYSTATUSID,
STATUSDATE,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
values
(
@EDUCATIONALHISTORYID,
@EDUCATIONALHISTORYSTATUSID,
@CHANGEDATE,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CHANGEDATE,
@CHANGEDATE
);
end
end try
begin catch
exec dbo.USP_RAISE_ERROR;
return 1;
end catch
return 0;