USP_BBNC_COMMITPROFILEUPDATEEDUCATIONALINVOLVEMENT
Updates a constituent's educational involvement 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 | |
| @EDUCATIONALINVOLVEMENTID | uniqueidentifier | IN | |
| @EDUCATIONALINSTITUTIONID | uniqueidentifier | IN | |
| @EDUCATIONALINVOLVEMENTTYPECODEID | uniqueidentifier | IN | |
| @DATEFROM | UDT_FUZZYDATE | IN | |
| @DATETO | UDT_FUZZYDATE | IN | |
| @EDUCATIONALINVOLVEMENTNAMEID | uniqueidentifier | IN | |
| @COMMENT | nvarchar(500) | IN | |
| @EDUCATIONALPROGRAMCODEID | uniqueidentifier | IN | |
| @EDUCATIONALCOLLEGECODEID | uniqueidentifier | IN | |
| @EDUCATIONALDIVISIONCODEID | uniqueidentifier | IN | |
| @EDUCATIONALDEPARTMENTCODEID | uniqueidentifier | IN | |
| @EDUCATIONALSUBDEPARTMENTCODEID | uniqueidentifier | IN | |
| @CHANGEAGENTID | uniqueidentifier | IN | |
| @CHANGEDATE | datetime | IN | |
| @EDUCATIONALAWARDCODEID | uniqueidentifier | IN | |
| @EDUCATIONALPOSITION | nvarchar(512) | IN | |
| @ACADEMICCATALOGPROGRAMID | uniqueidentifier | IN | |
| @ACADEMICCATALOGCOLLEGEID | uniqueidentifier | IN | |
| @ACADEMICCATALOGDIVISIONID | uniqueidentifier | IN | |
| @ACADEMICCATALOGDEPARTMENTID | uniqueidentifier | IN | |
| @ACADEMICCATALOGSUBDEPARTMENTID | uniqueidentifier | IN |
Definition
Copy
CREATE procedure dbo.USP_BBNC_COMMITPROFILEUPDATEEDUCATIONALINVOLVEMENT
(
@ID uniqueidentifier = null,
@ADDNEWRECORD bit = 0,
@EDUCATIONALINVOLVEMENTID uniqueidentifier = null,
@EDUCATIONALINSTITUTIONID uniqueidentifier = null,
@EDUCATIONALINVOLVEMENTTYPECODEID uniqueidentifier = null,
@DATEFROM dbo.UDT_FUZZYDATE = '00000000',
@DATETO dbo.UDT_FUZZYDATE = '00000000',
@EDUCATIONALINVOLVEMENTNAMEID uniqueidentifier = null,
@COMMENT nvarchar(500) = '',
@EDUCATIONALPROGRAMCODEID uniqueidentifier = null,
@EDUCATIONALCOLLEGECODEID uniqueidentifier = null,
@EDUCATIONALDIVISIONCODEID uniqueidentifier = null,
@EDUCATIONALDEPARTMENTCODEID uniqueidentifier = null,
@EDUCATIONALSUBDEPARTMENTCODEID uniqueidentifier = null,
@CHANGEAGENTID uniqueidentifier = null,
@CHANGEDATE datetime = null,
@EDUCATIONALAWARDCODEID uniqueidentifier = null,
@EDUCATIONALPOSITION nvarchar(512) = '',
@ACADEMICCATALOGPROGRAMID uniqueidentifier = null,
@ACADEMICCATALOGCOLLEGEID uniqueidentifier = null,
@ACADEMICCATALOGDIVISIONID uniqueidentifier = null,
@ACADEMICCATALOGDEPARTMENTID uniqueidentifier = null,
@ACADEMICCATALOGSUBDEPARTMENTID uniqueidentifier = null
)
as
set nocount on;
if @ID is null
begin
raiserror('The constituent ID is required',16,1);
return -2;
end
if @EDUCATIONALINVOLVEMENTTYPECODEID is null
begin
raiserror('BBERR_EDUCATIONALINVOLVEMENTTYPEMISSING',16,1);
return -2;
end
if @EDUCATIONALINVOLVEMENTNAMEID is null
begin
raiserror('BBERR_EDUCATIONALINVOLVEMENTNAMEMISSING',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 @EDUCATIONALINVOLVEMENTID is not null
begin
if not exists(select ID from dbo.EDUCATIONALINVOLVEMENT where ID = @EDUCATIONALINVOLVEMENTID)
begin
raiserror('BBERR_EDUCATIONALINVOLVEMENTIDDOESNOTEXIST',16,1);
return -2;
end
update
dbo.EDUCATIONALINVOLVEMENT
set
EDUCATIONALINSTITUTIONID = @EDUCATIONALINSTITUTIONID,
EDUCATIONALINVOLVEMENTTYPECODEID = @EDUCATIONALINVOLVEMENTTYPECODEID,
DATEFROM = @DATEFROM,
DATETO = @DATETO,
EDUCATIONALINVOLVEMENTNAMEID = @EDUCATIONALINVOLVEMENTNAMEID,
COMMENT = @COMMENT,
EDUCATIONALPROGRAMCODEID = @EDUCATIONALPROGRAMCODEID,
EDUCATIONALCOLLEGECODEID = @EDUCATIONALCOLLEGECODEID,
EDUCATIONALDIVISIONCODEID = @EDUCATIONALDIVISIONCODEID,
EDUCATIONALDEPARTMENTCODEID = @EDUCATIONALDEPARTMENTCODEID,
EDUCATIONALSUBDEPARTMENTCODEID = @EDUCATIONALSUBDEPARTMENTCODEID,
EDUCATIONALINVOLVEMENTAWARDCODEID = @EDUCATIONALAWARDCODEID,
POSITION = @EDUCATIONALPOSITION,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CHANGEDATE,
ACADEMICCATALOGPROGRAMID = @ACADEMICCATALOGPROGRAMID,
ACADEMICCATALOGCOLLEGEID = @ACADEMICCATALOGCOLLEGEID,
ACADEMICCATALOGDIVISIONID = @ACADEMICCATALOGDIVISIONID,
ACADEMICCATALOGDEPARTMENTID = @ACADEMICCATALOGDEPARTMENTID,
ACADEMICCATALOGSUBDEPARTMENTID = @ACADEMICCATALOGSUBDEPARTMENTID
where
ID = @EDUCATIONALINVOLVEMENTID;
end
end
else
begin
set @EDUCATIONALINVOLVEMENTID = newid();
insert into dbo.EDUCATIONALINVOLVEMENT
(
ID,
CONSTITUENTID,
EDUCATIONALINSTITUTIONID,
EDUCATIONALINVOLVEMENTTYPECODEID,
DATEFROM,
DATETO,
EDUCATIONALINVOLVEMENTNAMEID,
COMMENT,
EDUCATIONALPROGRAMCODEID,
EDUCATIONALCOLLEGECODEID,
EDUCATIONALDIVISIONCODEID,
EDUCATIONALDEPARTMENTCODEID,
EDUCATIONALSUBDEPARTMENTCODEID,
EDUCATIONALINVOLVEMENTAWARDCODEID,
POSITION,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED,
ACADEMICCATALOGPROGRAMID,
ACADEMICCATALOGCOLLEGEID,
ACADEMICCATALOGDIVISIONID,
ACADEMICCATALOGDEPARTMENTID,
ACADEMICCATALOGSUBDEPARTMENTID
)
values
(
@EDUCATIONALINVOLVEMENTID,
@ID,
@EDUCATIONALINSTITUTIONID,
@EDUCATIONALINVOLVEMENTTYPECODEID,
@DATEFROM,
@DATETO,
@EDUCATIONALINVOLVEMENTNAMEID,
@COMMENT,
@EDUCATIONALPROGRAMCODEID,
@EDUCATIONALCOLLEGECODEID,
@EDUCATIONALDIVISIONCODEID,
@EDUCATIONALDEPARTMENTCODEID,
@EDUCATIONALSUBDEPARTMENTCODEID,
@EDUCATIONALAWARDCODEID,
@EDUCATIONALPOSITION,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CHANGEDATE,
@CHANGEDATE,
@ACADEMICCATALOGPROGRAMID,
@ACADEMICCATALOGCOLLEGEID,
@ACADEMICCATALOGDIVISIONID,
@ACADEMICCATALOGDEPARTMENTID,
@ACADEMICCATALOGSUBDEPARTMENTID
);
end
end try
begin catch
exec dbo.USP_RAISE_ERROR;
return 1;
end catch
return 0;