USP_BBNC_COMMITPROFILEUPDATEPRIMARYALUMNI
Updates a constituent's primary alumni information from a Blackbaud Internet Solutions profile transaction to the system from a given batch.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @ID | uniqueidentifier | IN | |
| @CLASSOF | UDT_YEAR | IN | |
| @STARTDATE | UDT_FUZZYDATE | IN | |
| @ENDDATE | UDT_FUZZYDATE | IN | |
| @EDUCATIONALDEGREECODEID | uniqueidentifier | IN | |
| @FRATERNITY | nvarchar(50) | IN | |
| @GPA | decimal(3, 2) | IN | |
| @KNOWNNAME | nvarchar(50) | IN | |
| @EDUCATIONALMAJORCODEID | uniqueidentifier | IN | |
| @EDUCATIONALMINORCODEID | uniqueidentifier | IN | |
| @EDUCATIONALINSTITUTIONID | uniqueidentifier | IN | |
| @EDUCATIONALHISTORYTYPECODEID | uniqueidentifier | IN | |
| @EDUCATIONALHISTORYSTATUSCODEID | uniqueidentifier | IN | |
| @CHANGEAGENTID | uniqueidentifier | IN | |
| @CHANGEDATE | datetime | IN |
Definition
Copy
CREATE procedure dbo.USP_BBNC_COMMITPROFILEUPDATEPRIMARYALUMNI
(
@ID uniqueidentifier = null,
@CLASSOF dbo.UDT_YEAR = null,
@STARTDATE dbo.UDT_FUZZYDATE = '00000000',
@ENDDATE dbo.UDT_FUZZYDATE = '00000000',
@EDUCATIONALDEGREECODEID uniqueidentifier = null,
@FRATERNITY nvarchar(50) = '',
@GPA decimal(3,2) = 0,
@KNOWNNAME nvarchar(50) = '',
@EDUCATIONALMAJORCODEID uniqueidentifier = null,
@EDUCATIONALMINORCODEID uniqueidentifier = null,
@EDUCATIONALINSTITUTIONID uniqueidentifier = null,
@EDUCATIONALHISTORYTYPECODEID uniqueidentifier = null,
@EDUCATIONALHISTORYSTATUSCODEID uniqueidentifier = null,
@CHANGEAGENTID uniqueidentifier = null,
@CHANGEDATE datetime = null
) as
set nocount on;
declare @EDUCATIONALHISTORYID 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();
select
@EDUCATIONALHISTORYID = [ID]
from
dbo.EDUCATIONALHISTORY
where
[CONSTITUENTID] = @ID and [ISPRIMARYRECORD] = 1;
begin try
begin
if @EDUCATIONALHISTORYID is not null
begin
update dbo.EDUCATIONALHISTORY
set
[CLASSOF] = coalesce(@CLASSOF, 0),
[STARTDATE] = @STARTDATE,
[ENDDATE] = @ENDDATE,
[EDUCATIONALDEGREECODEID] = @EDUCATIONALDEGREECODEID,
[FRATERNITY] = @FRATERNITY,
[GPA] = coalesce(@GPA, 0),
[KNOWNNAME] = @KNOWNNAME,
[EDUCATIONALINSTITUTIONID] = @EDUCATIONALINSTITUTIONID,
[EDUCATIONALHISTORYTYPECODEID] = @EDUCATIONALHISTORYTYPECODEID,
[EDUCATIONALHISTORYSTATUSCODEID] = @EDUCATIONALHISTORYSTATUSCODEID,
[CHANGEDBYID] = @CHANGEAGENTID,
[DATECHANGED] = @CHANGEDATE
where
[ID] = @EDUCATIONALHISTORYID;
if @EDUCATIONALMAJORCODEID is not null
begin
if exists (select [ID] from dbo.EDUCATIONALMAJOR where [EDUCATIONALHISTORYID] = @EDUCATIONALHISTORYID)
update dbo.EDUCATIONALMAJOR set
[EDUCATIONALMAJORCODEID] = @EDUCATIONALMAJORCODEID,
[CHANGEDBYID] = @CHANGEAGENTID,
[DATECHANGED] = @CHANGEDATE
where
[ID] = (select top 1 ID from dbo.EDUCATIONALMAJOR where EDUCATIONALHISTORYID = @EDUCATIONALHISTORYID);
else
insert into dbo.EDUCATIONALMAJOR
(
[EDUCATIONALHISTORYID],
[EDUCATIONALMAJORCODEID],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED]
)
values
(
@EDUCATIONALHISTORYID,
@EDUCATIONALMAJORCODEID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CHANGEDATE,
@CHANGEDATE
);
end
if @EDUCATIONALMINORCODEID is not null
begin
if exists (select [ID] from dbo.EDUCATIONALMINOR where [EDUCATIONALHISTORYID] = @EDUCATIONALHISTORYID)
update dbo.EDUCATIONALMINOR set
[EDUCATIONALMAJORCODEID] = @EDUCATIONALMINORCODEID,
[CHANGEDBYID] = @CHANGEAGENTID,
[DATECHANGED] = @CHANGEDATE
where
[ID] = (select top 1 ID from dbo.EDUCATIONALMINOR where EDUCATIONALHISTORYID = @EDUCATIONALHISTORYID);
else
insert into dbo.EDUCATIONALMINOR
(
[EDUCATIONALHISTORYID],
[EDUCATIONALMAJORCODEID],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED]
)
values
(
@EDUCATIONALHISTORYID,
@EDUCATIONALMINORCODEID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CHANGEDATE,
@CHANGEDATE
);
end
end
else
begin
set @EDUCATIONALHISTORYID = newid();
insert into dbo.EDUCATIONALHISTORY
(
[ID],
[CONSTITUENTID],
[ISPRIMARYRECORD],
[CLASSOF],
[STARTDATE],
[ENDDATE],
[EDUCATIONALDEGREECODEID],
[FRATERNITY],
[GPA],
[KNOWNNAME],
[EDUCATIONALINSTITUTIONID],
[EDUCATIONALHISTORYTYPECODEID],
[EDUCATIONALHISTORYSTATUSCODEID],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED]
)
values
(
@EDUCATIONALHISTORYID,
@ID,
1,
coalesce(@CLASSOF, 0),
@STARTDATE,
@ENDDATE,
@EDUCATIONALDEGREECODEID,
@FRATERNITY,
coalesce(@GPA, 0),
@KNOWNNAME,
@EDUCATIONALINSTITUTIONID,
@EDUCATIONALHISTORYTYPECODEID,
@EDUCATIONALHISTORYSTATUSCODEID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CHANGEDATE,
@CHANGEDATE
);
if @EDUCATIONALMAJORCODEID is not null
insert into dbo.EDUCATIONALMAJOR
(
[EDUCATIONALHISTORYID],
[EDUCATIONALMAJORCODEID],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED]
)
values
(
@EDUCATIONALHISTORYID,
@EDUCATIONALMAJORCODEID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CHANGEDATE,
@CHANGEDATE
);
if @EDUCATIONALMINORCODEID is not null
insert into dbo.EDUCATIONALMINOR
(
[EDUCATIONALHISTORYID],
[EDUCATIONALMAJORCODEID],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED]
)
values
(
@EDUCATIONALHISTORYID,
@EDUCATIONALMINORCODEID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CHANGEDATE,
@CHANGEDATE
);
end
end
end try
begin catch
exec dbo.USP_RAISE_ERROR;
return 1;
end catch
return 0;