USP_DATAFORMTEMPLATE_ADD_EDUCATIONALHISTORYUPDATEBATCHTEMPLATE
The save procedure used by the add dataform template "Educational History Update Batch Template Add Form".
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. |
| @VALIDATEONLY | bit | IN | Validate only |
| @CONSTITUENTLOOKUPID | nvarchar(100) | IN | Constituent lookup ID |
| @CONSTITUENTKEYNAME | nvarchar(100) | IN | Constituent last name |
| @CONSTITUENTFIRSTNAME | nvarchar(50) | IN | Constituent first name |
| @EDUCATIONALINSTITUTIONID | uniqueidentifier | IN | Educational institution |
| @EDUCATIONALINSTITUTIONNAME | nvarchar(100) | IN | Educational institution name |
| @EDUCATIONALINSTITUTIONFICECODE | nvarchar(50) | IN | Educational institution FICE code |
| @EDUCATIONALINSTITUTIONCOUNTRYID | uniqueidentifier | IN | Educational institution country |
| @EDUCATIONALINSTITUTIONCITY | nvarchar(150) | IN | Educational institution city |
| @EDUCATIONALINSTITUTIONSTATEID | uniqueidentifier | IN | Educational institution state |
| @ISPRIMARYRECORD | bit | IN | Primary education information |
| @ACADEMICCATALOGPROGRAMID | uniqueidentifier | IN | Academic catalog program |
| @EDUCATIONALPROGRAMCODEID | uniqueidentifier | IN | Educational program |
| @CONSTITUENCYSTATUSCODE | tinyint | IN | Status |
| @EDUCATIONALHISTORYREASONCODEID | uniqueidentifier | IN | Reason |
| @EDUCATIONALHISTORYLEVELCODEID | uniqueidentifier | IN | Level |
| @DATELEFT | UDT_FUZZYDATE | IN | Date attended to |
| @DATEGRADUATED | UDT_FUZZYDATE | IN | Graduation date |
| @ACADEMICCATALOGDEGREEID | uniqueidentifier | IN | Academic catalog degree |
| @EDUCATIONALDEGREECODEID | uniqueidentifier | IN | Educational degree |
| @EDUCATIONALAWARDCODEID | uniqueidentifier | IN | Honor awarded |
| @STARTDATE | UDT_FUZZYDATE | IN | Date attended 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 | Academic catalog additional information |
| @UNAFFILIATEDADDITIONALINFORMATION | xml | IN | Educational additional information |
| @EDUCATIONALINVOLVEMENTS | xml | IN | Educational involvements |
| @EDUCATIONALHISTORYSTATUSID | uniqueidentifier | IN | Status |
Definition
Copy
CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_EDUCATIONALHISTORYUPDATEBATCHTEMPLATE (
@ID uniqueidentifier = null output,
@CHANGEAGENTID uniqueidentifier = null,
@CONSTITUENTID uniqueidentifier,
@VALIDATEONLY bit = 0,
@CONSTITUENTLOOKUPID nvarchar(100) = '',
@CONSTITUENTKEYNAME nvarchar(100) = '',
@CONSTITUENTFIRSTNAME nvarchar(50) = '',
@EDUCATIONALINSTITUTIONID uniqueidentifier = null,
@EDUCATIONALINSTITUTIONNAME nvarchar(100) = '',
@EDUCATIONALINSTITUTIONFICECODE nvarchar(50) = '',
@EDUCATIONALINSTITUTIONCOUNTRYID uniqueidentifier = null,
@EDUCATIONALINSTITUTIONCITY nvarchar(150) = '',
@EDUCATIONALINSTITUTIONSTATEID uniqueidentifier = null,
@ISPRIMARYRECORD bit = 0,
@ACADEMICCATALOGPROGRAMID uniqueidentifier = null,
@EDUCATIONALPROGRAMCODEID uniqueidentifier = null,
@CONSTITUENCYSTATUSCODE tinyint = 0,
@EDUCATIONALHISTORYREASONCODEID uniqueidentifier = null,
@EDUCATIONALHISTORYLEVELCODEID uniqueidentifier = null,
@DATELEFT dbo.UDT_FUZZYDATE = '00000000',
@DATEGRADUATED dbo.UDT_FUZZYDATE = '00000000',
@ACADEMICCATALOGDEGREEID uniqueidentifier = null,
@EDUCATIONALDEGREECODEID uniqueidentifier = null,
@EDUCATIONALAWARDCODEID uniqueidentifier = null,
@STARTDATE dbo.UDT_FUZZYDATE = '00000000',
@CLASSYEAR dbo.UDT_YEAR = '0000',
@PREFERREDCLASSYEAR dbo.UDT_YEAR = '0000',
@EDUCATIONALSOURCECODEID uniqueidentifier = null,
@EDUCATIONALSOURCEDATE dbo.UDT_FUZZYDATE = '00000000',
@COMMENT nvarchar(500) = '',
@AFFILIATEDADDITIONALINFORMATION xml = null,
@UNAFFILIATEDADDITIONALINFORMATION xml = null,
@EDUCATIONALINVOLVEMENTS xml = 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;
begin try
if @ID is null
set @ID = newid();
--==========================================================
-- Academic catalog
--==========================================================
declare @USEACADEMICCATALOG bit;
select top 1
@USEACADEMICCATALOG = EC.[USEACADEMICCATALOG]
from
dbo.[EDUCATIONALCONFIGURATION] EC;
set @USEACADEMICCATALOG = coalesce(@USEACADEMICCATALOG,0);
--==========================================================
-- Educational institution
--==========================================================
if @EDUCATIONALINSTITUTIONID is null
begin
select top 1
@EDUCATIONALINSTITUTIONID = EI.[ID]
from
dbo.[EDUCATIONALINSTITUTION] EI
where
EI.[NAME] = @EDUCATIONALINSTITUTIONNAME;
if @EDUCATIONALINSTITUTIONID is null
begin
set @EDUCATIONALINSTITUTIONID = newid();
set @EDUCATIONALINSTITUTIONFICECODE = coalesce(@EDUCATIONALINSTITUTIONFICECODE, '');
set @EDUCATIONALINSTITUTIONNAME = coalesce(@EDUCATIONALINSTITUTIONNAME, '');
set @EDUCATIONALINSTITUTIONCITY = coalesce(@EDUCATIONALINSTITUTIONCITY, '');
if len(@EDUCATIONALINSTITUTIONNAME)=0
raiserror('BBERR_EDUCATIONALHISTORYUPDATEBATCHTEMPLATEADD_EDUCATIONALINSTITUTIONNAMEISREQUIRED', 13, 1)
exec dbo.USP_DATAFORMTEMPLATE_ADD_EDUCATIONALINSTITUTION_2 @EDUCATIONALINSTITUTIONID, @CHANGEAGENTID, @EDUCATIONALINSTITUTIONFICECODE, @EDUCATIONALINSTITUTIONNAME, 0, @EDUCATIONALINSTITUTIONCOUNTRYID, @EDUCATIONALINSTITUTIONCITY, @EDUCATIONALINSTITUTIONSTATEID;
end
end
declare @EDUCATIONALINSTITUTIONISAFFILIATED bit;
select
@EDUCATIONALINSTITUTIONISAFFILIATED = EI.[ISAFFILIATED]
from
dbo.[EDUCATIONALINSTITUTION] EI
where
EI.[ID] = @EDUCATIONALINSTITUTIONID;
--==========================================================
-- Primary record
--==========================================================
update
dbo.[EDUCATIONALHISTORY]
set
[ISPRIMARYRECORD] = 0,
[CHANGEDBYID] = @CHANGEAGENTID,
[DATECHANGED] = @CURRENTDATE
where
@ISPRIMARYRECORD = 1 and
[ISPRIMARYRECORD] = 1 and
[CONSTITUENTID] = @CONSTITUENTID and
[ID] <> @ID;
--==========================================================
-- Constituent
--==========================================================
declare @MATCHINGCONSTITUENTCOUNT smallint
select @MATCHINGCONSTITUENTCOUNT = count(ID) from dbo.UFN_CONSTITUENT_GETIDSFROMCRITERIA( @CONSTITUENTID, @CONSTITUENTLOOKUPID, @CONSTITUENTKEYNAME, @CONSTITUENTFIRSTNAME, 500)
if @MATCHINGCONSTITUENTCOUNT = 1 begin
select top(1) @CONSTITUENTID = ID from dbo.UFN_CONSTITUENT_GETIDSFROMCRITERIA( @CONSTITUENTID, @CONSTITUENTLOOKUPID, @CONSTITUENTKEYNAME, @CONSTITUENTFIRSTNAME, 500)
end
else if @MATCHINGCONSTITUENTCOUNT > 1 begin
raiserror('The ID for this constituent matches multiple records.',13,1);
end
else begin
raiserror('The ID for this constituent does not match any records.',13,1);
end
--==========================================================
-- Educational history
--==========================================================
if @USEACADEMICCATALOG = 1 and @EDUCATIONALINSTITUTIONISAFFILIATED = 1 begin
insert into dbo.[EDUCATIONALHISTORY] (
[ID],
[CONSTITUENTID],
[EDUCATIONALINSTITUTIONID],
[ISPRIMARYRECORD],
[ACADEMICCATALOGPROGRAMID],
[CONSTITUENCYSTATUSCODE],
[EDUCATIONALHISTORYREASONCODEID],
[EDUCATIONALHISTORYLEVELCODEID],
[DATELEFT],
[DATEGRADUATED],
[ACADEMICCATALOGDEGREEID],
[EDUCATIONALAWARDCODEID],
[STARTDATE],
[CLASSOF],
[PREFERREDCLASSYEAR],
[EDUCATIONALSOURCECODEID],
[EDUCATIONALSOURCEDATE],
[COMMENT],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED]
)
values (
@ID,
@CONSTITUENTID,
@EDUCATIONALINSTITUTIONID,
@ISPRIMARYRECORD,
@ACADEMICCATALOGPROGRAMID,
coalesce(@CONSTITUENCYSTATUSCODE, 0),
@EDUCATIONALHISTORYREASONCODEID,
@EDUCATIONALHISTORYLEVELCODEID,
coalesce(@DATELEFT, '00000000'),
coalesce(@DATEGRADUATED, '00000000'),
@ACADEMICCATALOGDEGREEID,
@EDUCATIONALAWARDCODEID,
coalesce(@STARTDATE, '00000000'),
coalesce(@CLASSYEAR, '0000'),
coalesce(@PREFERREDCLASSYEAR, '0000'),
@EDUCATIONALSOURCECODEID,
coalesce(@EDUCATIONALSOURCEDATE, '00000000'),
coalesce(@COMMENT, ''),
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
);
end
else begin
insert into dbo.[EDUCATIONALHISTORY] (
[ID],
[CONSTITUENTID],
[EDUCATIONALINSTITUTIONID],
[ISPRIMARYRECORD],
[EDUCATIONALPROGRAMCODEID],
[CONSTITUENCYSTATUSCODE],
[EDUCATIONALHISTORYREASONCODEID],
[EDUCATIONALHISTORYLEVELCODEID],
[DATELEFT],
[DATEGRADUATED],
[EDUCATIONALDEGREECODEID],
[EDUCATIONALAWARDCODEID],
[STARTDATE],
[CLASSOF],
[PREFERREDCLASSYEAR],
[EDUCATIONALSOURCECODEID],
[EDUCATIONALSOURCEDATE],
[COMMENT],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED]
)
values (
@ID,
@CONSTITUENTID,
@EDUCATIONALINSTITUTIONID,
@ISPRIMARYRECORD,
@EDUCATIONALPROGRAMCODEID,
coalesce(@CONSTITUENCYSTATUSCODE, 0),
@EDUCATIONALHISTORYREASONCODEID,
@EDUCATIONALHISTORYLEVELCODEID,
coalesce(@DATELEFT, '00000000'),
coalesce(@DATEGRADUATED, '00000000'),
@EDUCATIONALDEGREECODEID,
@EDUCATIONALAWARDCODEID,
coalesce(@STARTDATE, '00000000'),
coalesce(@CLASSYEAR, '0000'),
coalesce(@PREFERREDCLASSYEAR, '0000'),
@EDUCATIONALSOURCECODEID,
coalesce(@EDUCATIONALSOURCEDATE, '00000000'),
coalesce(@COMMENT, ''),
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
);
end
--==========================================================
--STATUS
--==========================================================
if @EDUCATIONALHISTORYSTATUSID is null
begin
set @EDUCATIONALHISTORYSTATUSID = '00000000-0000-0000-0000-000000000001'
end
if (dbo.UFN_EDUCATIONALHISTORY_CURRENTEDUCATIONALHISTORYSTATUS(@ID) != @EDUCATIONALHISTORYSTATUSID)
begin
insert into dbo.[EDUCATIONALHISTORYSTATUSHISTORY]
(
[EDUCATIONALHISTORYID],
[EDUCATIONALHISTORYSTATUSID],
[STATUSDATE],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED]
)
values
(
@ID,
@EDUCATIONALHISTORYSTATUSID,
@CURRENTDATE,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
)
end
--==========================================================
-- Additional information
--==========================================================
if @USEACADEMICCATALOG = 1 and @EDUCATIONALINSTITUTIONISAFFILIATED = 1 begin
insert into dbo.[EDUCATIONADDITIONALINFORMATION] (
[EDUCATIONALHISTORYID],
[ACADEMICCATALOGCOLLEGEID],
[ACADEMICCATALOGDIVISIONID],
[ACADEMICCATALOGDEPARTMENTID],
[ACADEMICCATALOGSUBDEPARTMENTID],
[ACADEMICCATALOGDEGREETYPEID],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED]
)
select
@ID,
AAI.[ACADEMICCATALOGCOLLEGEID],
AAI.[ACADEMICCATALOGDIVISIONID],
AAI.[ACADEMICCATALOGDEPARTMENTID],
AAI.[ACADEMICCATALOGSUBDEPARTMENTID],
AAI.[ACADEMICCATALOGDEGREETYPEID],
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from
dbo.UFN_EDUCATIONALHISTORY_GETAFFILIATEDADDITIONALINFORMATION_FORUPDATEBATCH_FROMITEMLISTXML(@AFFILIATEDADDITIONALINFORMATION) AAI;
end
else begin
insert into dbo.[EDUCATIONADDITIONALINFORMATION] (
[EDUCATIONALHISTORYID],
[EDUCATIONALCOLLEGECODEID],
[EDUCATIONALDIVISIONCODEID],
[EDUCATIONALDEPARTMENTCODEID],
[EDUCATIONALSUBDEPARTMENTCODEID],
[EDUCATIONALDEGREETYPECODEID],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED]
)
select
@ID,
UAI.[EDUCATIONALCOLLEGECODEID],
UAI.[EDUCATIONALDIVISIONCODEID],
UAI.[EDUCATIONALDEPARTMENTCODEID],
UAI.[EDUCATIONALSUBDEPARTMENTCODEID],
UAI.[EDUCATIONALDEGREETYPECODEID],
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from
dbo.UFN_EDUCATIONALHISTORY_GETUNAFFILIATEDADDITIONALINFORMATION_FORUPDATEBATCH_FROMITEMLISTXML(@UNAFFILIATEDADDITIONALINFORMATION) UAI;
end
--==========================================================
-- Educational involvements
--==========================================================
declare @EDUCATIONALINVOLVEMENTSTABLE table (
[EDUCATIONALINVOLVEMENTID] uniqueidentifier,
[EDUCATIONALINVOLVEMENTTYPECODEID] uniqueidentifier,
[EDUCATIONALINVOLVEMENTNAMEID] uniqueidentifier,
[DATEFROM] dbo.UDT_FUZZYDATE,
[DATETO] dbo.UDT_FUZZYDATE,
[EDUCATIONALINVOLVEMENTAWARDCODEID] uniqueidentifier,
[COMMENT] nvarchar(500),
[POSITION] nvarchar(256)
);
insert into @EDUCATIONALINVOLVEMENTSTABLE (
[EDUCATIONALINVOLVEMENTID],
[EDUCATIONALINVOLVEMENTTYPECODEID],
[EDUCATIONALINVOLVEMENTNAMEID],
[DATEFROM],
[DATETO],
[EDUCATIONALINVOLVEMENTAWARDCODEID],
[COMMENT],
[POSITION]
)
select
EI.[EDUCATIONALINVOLVEMENTID],
EI.[EDUCATIONALINVOLVEMENTTYPECODEID],
EI.[EDUCATIONALINVOLVEMENTNAMEID],
EI.[DATEFROM],
EI.[DATETO],
EI.[EDUCATIONALINVOLVEMENTAWARDCODEID],
EI.[COMMENT],
EI.[POSITION]
from
dbo.UFN_CONSTITUENT_GETEDUCATIONALINVOLVEMENTS_FORUPDATEBATCH_FROMITEMLISTXML(@EDUCATIONALINVOLVEMENTS) EI;
update
dbo.[EDUCATIONALINVOLVEMENT]
set
[EDUCATIONALINVOLVEMENTTYPECODEID] = coalesce(EIT.[EDUCATIONALINVOLVEMENTTYPECODEID], EI.[EDUCATIONALINVOLVEMENTTYPECODEID]),
[EDUCATIONALINVOLVEMENTNAMEID] = coalesce(EIT.[EDUCATIONALINVOLVEMENTNAMEID], EI.[EDUCATIONALINVOLVEMENTNAMEID]),
[DATEFROM] = coalesce(nullif(EIT.[DATEFROM], '00000000'), EI.[DATEFROM]),
[DATETO] = coalesce(nullif(EIT.[DATETO], '00000000'), EI.[DATETO]),
[EDUCATIONALINVOLVEMENTAWARDCODEID] = coalesce(EIT.[EDUCATIONALINVOLVEMENTAWARDCODEID], EI.[EDUCATIONALINVOLVEMENTAWARDCODEID]),
[COMMENT] = coalesce(nullif(EIT.[COMMENT], ''), EI.[COMMENT]),
[CHANGEDBYID] = @CHANGEAGENTID,
[DATECHANGED] = @CURRENTDATE,
[POSITION] = coalesce(nullif(EIT.[POSITION], ''), EI.[POSITION])
from
@EDUCATIONALINVOLVEMENTSTABLE EIT
inner join
dbo.[EDUCATIONALINVOLVEMENT] EI
on
EIT.[EDUCATIONALINVOLVEMENTID] = EI.[ID] or
(EIT.[EDUCATIONALINVOLVEMENTID] is null and
EI.[CONSTITUENTID] = @CONSTITUENTID and
(EIT.[EDUCATIONALINVOLVEMENTTYPECODEID] = EI.[EDUCATIONALINVOLVEMENTTYPECODEID] or (EIT.[EDUCATIONALINVOLVEMENTTYPECODEID] is null and EI.[EDUCATIONALINVOLVEMENTTYPECODEID] is null)) and
(EIT.[EDUCATIONALINVOLVEMENTNAMEID] = EI.[EDUCATIONALINVOLVEMENTNAMEID] or (EIT.[EDUCATIONALINVOLVEMENTNAMEID] is null and EI.[EDUCATIONALINVOLVEMENTNAMEID] is null)) and
coalesce(EIT.[DATEFROM], '00000000') = coalesce(EI.[DATEFROM], '00000000') and
coalesce(EIT.[DATETO], '00000000') = coalesce(EI.[DATETO], '00000000') and
(EIT.[EDUCATIONALINVOLVEMENTAWARDCODEID] = EI.[EDUCATIONALINVOLVEMENTAWARDCODEID] or (EIT.[EDUCATIONALINVOLVEMENTAWARDCODEID] is null and EI.[EDUCATIONALINVOLVEMENTAWARDCODEID] is null)));
insert into dbo.[EDUCATIONALINVOLVEMENT] (
[CONSTITUENTID],
[EDUCATIONALINSTITUTIONID],
[EDUCATIONALINVOLVEMENTTYPECODEID],
[EDUCATIONALINVOLVEMENTNAMEID],
[DATEFROM],
[DATETO],
[EDUCATIONALINVOLVEMENTAWARDCODEID],
[COMMENT],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED],
[POSITION]
)
select
@CONSTITUENTID,
@EDUCATIONALINSTITUTIONID,
EIT.[EDUCATIONALINVOLVEMENTTYPECODEID],
EIT.[EDUCATIONALINVOLVEMENTNAMEID],
coalesce(EIT.[DATEFROM], '00000000'),
coalesce(EIT.[DATETO], '00000000'),
EIT.[EDUCATIONALINVOLVEMENTAWARDCODEID],
coalesce(EIT.[COMMENT], ''),
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE,
coalesce(EIT.[POSITION], '')
from
@EDUCATIONALINVOLVEMENTSTABLE EIT
left join
dbo.[EDUCATIONALINVOLVEMENT] EI
on
EIT.[EDUCATIONALINVOLVEMENTID] = EI.[ID] or
(EIT.[EDUCATIONALINVOLVEMENTID] is null and
EI.[CONSTITUENTID] = @CONSTITUENTID and
(EIT.[EDUCATIONALINVOLVEMENTTYPECODEID] = EI.[EDUCATIONALINVOLVEMENTTYPECODEID] or (EIT.[EDUCATIONALINVOLVEMENTTYPECODEID] is null and EI.[EDUCATIONALINVOLVEMENTTYPECODEID] is null)) and
(EIT.[EDUCATIONALINVOLVEMENTNAMEID] = EI.[EDUCATIONALINVOLVEMENTNAMEID] or (EIT.[EDUCATIONALINVOLVEMENTNAMEID] is null and EI.[EDUCATIONALINVOLVEMENTNAMEID] is null)) and
coalesce(EIT.[DATEFROM], '00000000') = coalesce(EI.[DATEFROM], '00000000') and
coalesce(EIT.[DATETO], '00000000') = coalesce(EI.[DATETO], '00000000') and
(EIT.[EDUCATIONALINVOLVEMENTAWARDCODEID] = EI.[EDUCATIONALINVOLVEMENTAWARDCODEID] or (EIT.[EDUCATIONALINVOLVEMENTAWARDCODEID] is null and EI.[EDUCATIONALINVOLVEMENTAWARDCODEID] is null)))
where
EI.[ID] is null;
end try
begin catch
exec dbo.USP_RAISE_ERROR;
return 1;
end catch
return 0;