USP_DATAFORMTEMPLATE_EDIT_EDUCATIONALHISTORYUPDATEBATCHTEMPLATE
The save procedure used by the edit dataform template "Educational History Update Batch Template Edit Form".
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @ID | uniqueidentifier | IN | The input ID parameter indicating the ID of the record being edited. |
| @CHANGEAGENTID | uniqueidentifier | IN | Input parameter indicating the ID of the change agent invoking the procedure. |
| @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_EDIT_EDUCATIONALHISTORYUPDATEBATCHTEMPLATE (
@ID uniqueidentifier,
@CHANGEAGENTID uniqueidentifier = null,
@VALIDATEONLY bit,
@CONSTITUENTLOOKUPID nvarchar(100),
@CONSTITUENTKEYNAME nvarchar(100),
@CONSTITUENTFIRSTNAME nvarchar(50),
@EDUCATIONALINSTITUTIONID uniqueidentifier,
@EDUCATIONALINSTITUTIONNAME nvarchar(100),
@EDUCATIONALINSTITUTIONFICECODE nvarchar(50),
@EDUCATIONALINSTITUTIONCOUNTRYID uniqueidentifier,
@EDUCATIONALINSTITUTIONCITY nvarchar(150),
@EDUCATIONALINSTITUTIONSTATEID uniqueidentifier,
@ISPRIMARYRECORD bit,
@ACADEMICCATALOGPROGRAMID uniqueidentifier,
@EDUCATIONALPROGRAMCODEID uniqueidentifier,
@CONSTITUENCYSTATUSCODE tinyint,
@EDUCATIONALHISTORYREASONCODEID uniqueidentifier,
@EDUCATIONALHISTORYLEVELCODEID uniqueidentifier,
@DATELEFT dbo.UDT_FUZZYDATE,
@DATEGRADUATED dbo.UDT_FUZZYDATE,
@ACADEMICCATALOGDEGREEID uniqueidentifier,
@EDUCATIONALDEGREECODEID uniqueidentifier,
@EDUCATIONALAWARDCODEID uniqueidentifier,
@STARTDATE dbo.UDT_FUZZYDATE,
@CLASSYEAR dbo.UDT_YEAR,
@PREFERREDCLASSYEAR dbo.UDT_YEAR,
@EDUCATIONALSOURCECODEID uniqueidentifier,
@EDUCATIONALSOURCEDATE dbo.UDT_FUZZYDATE,
@COMMENT nvarchar(500),
@AFFILIATEDADDITIONALINFORMATION xml,
@UNAFFILIATEDADDITIONALINFORMATION xml,
@EDUCATIONALINVOLVEMENTS xml,
@EDUCATIONALHISTORYSTATUSID uniqueidentifier
) as
set nocount on;
declare @CURRENTDATE datetime;
set @CURRENTDATE = getdate();
if @CHANGEAGENTID is null
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;
begin try
--==========================================================
-- Constituent
--==========================================================
declare @CONSTITUENTID uniqueidentifier;
select
@CONSTITUENTID = EH.[CONSTITUENTID]
from
dbo.[EDUCATIONALHISTORY] EH
where
EH.[ID] = @ID;
--==========================================================
-- 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 and nullif(@EDUCATIONALINSTITUTIONNAME, '') is null begin
select top 1
@EDUCATIONALINSTITUTIONID = EH.[EDUCATIONALINSTITUTIONID]
from
dbo.[EDUCATIONALHISTORY] EH
where
EH.[ID] = @ID;
end
if @EDUCATIONALINSTITUTIONID is null
begin
set @EDUCATIONALINSTITUTIONID = newid();
set @EDUCATIONALINSTITUTIONFICECODE = coalesce(@EDUCATIONALINSTITUTIONFICECODE, '');
set @EDUCATIONALINSTITUTIONNAME = coalesce(@EDUCATIONALINSTITUTIONNAME, '');
set @EDUCATIONALINSTITUTIONCITY = coalesce(@EDUCATIONALINSTITUTIONCITY, '');
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;
--==========================================================
-- Educational history
--==========================================================
if @USEACADEMICCATALOG = 1 and @EDUCATIONALINSTITUTIONISAFFILIATED = 1
begin
update
dbo.[EDUCATIONALHISTORY]
set
[EDUCATIONALPROGRAMCODEID] = null,
[EDUCATIONALDEGREECODEID] = null,
[EDUCATIONALINSTITUTIONID] = coalesce(nullif(@EDUCATIONALINSTITUTIONID, '00000000-0000-0000-0000-000000000000'), EH.[EDUCATIONALINSTITUTIONID]),
[ISPRIMARYRECORD] = coalesce(@ISPRIMARYRECORD, EH.[ISPRIMARYRECORD]),
[ACADEMICCATALOGPROGRAMID] = coalesce(nullif(@ACADEMICCATALOGPROGRAMID, '00000000-0000-0000-0000-000000000000'), EH.[ACADEMICCATALOGPROGRAMID]),
[CONSTITUENCYSTATUSCODE] =
case
when nullif(@EDUCATIONALHISTORYREASONCODEID, '00000000-0000-0000-0000-000000000000') is null then
coalesce(nullif(@CONSTITUENCYSTATUSCODE, 0), EH.[CONSTITUENCYSTATUSCODE])
else coalesce(@CONSTITUENCYSTATUSCODE, 0)
end,
[EDUCATIONALHISTORYREASONCODEID] = coalesce(nullif(@EDUCATIONALHISTORYREASONCODEID, '00000000-0000-0000-0000-000000000000'), EH.[EDUCATIONALHISTORYREASONCODEID]),
[EDUCATIONALHISTORYLEVELCODEID] = coalesce(nullif(@EDUCATIONALHISTORYLEVELCODEID, '00000000-0000-0000-0000-000000000000'), EH.[EDUCATIONALHISTORYLEVELCODEID]),
[DATELEFT] = coalesce(nullif(@DATELEFT, '00000000'), EH.[DATELEFT]),
[DATEGRADUATED] = coalesce(nullif(@DATEGRADUATED, '00000000'), EH.[DATEGRADUATED]),
[ACADEMICCATALOGDEGREEID] =
case
when exists(
select
ACD.ID
from
dbo.ACADEMICCATALOGDEGREE ACD
where
ACD.ACADEMICCATALOGPROGRAMID = coalesce(nullif(@ACADEMICCATALOGPROGRAMID, '00000000-0000-0000-0000-000000000000'), EH.[ACADEMICCATALOGPROGRAMID])
and ACD.ID = coalesce(nullif(@ACADEMICCATALOGDEGREEID, '00000000-0000-0000-0000-000000000000'), EH.[ACADEMICCATALOGDEGREEID])
) then coalesce(nullif(@ACADEMICCATALOGDEGREEID, '00000000-0000-0000-0000-000000000000'), EH.[ACADEMICCATALOGDEGREEID])
else null
end,
[EDUCATIONALAWARDCODEID] = coalesce(@EDUCATIONALAWARDCODEID, EH.[EDUCATIONALAWARDCODEID]),
[STARTDATE] = coalesce(nullif(@STARTDATE, '00000000'), EH.[STARTDATE]),
[CLASSOF] = coalesce(nullif(@CLASSYEAR, '0000'), EH.[CLASSOF]),
[PREFERREDCLASSYEAR] = coalesce(nullif(@PREFERREDCLASSYEAR, '0000'), EH.[PREFERREDCLASSYEAR]),
[EDUCATIONALSOURCECODEID] = coalesce(nullif(@EDUCATIONALSOURCECODEID, '00000000-0000-0000-0000-000000000000'), EH.[EDUCATIONALSOURCECODEID]),
[EDUCATIONALSOURCEDATE] = coalesce(nullif(@EDUCATIONALSOURCEDATE, '00000000'), EH.[EDUCATIONALSOURCEDATE]),
[COMMENT] = coalesce(nullif(@COMMENT, ''), EH.[COMMENT]),
[CHANGEDBYID] = @CHANGEAGENTID,
[DATECHANGED] = @CURRENTDATE
from
dbo.[EDUCATIONALHISTORY] EH
where
EH.[ID] = @ID;
end
else
begin
update
dbo.[EDUCATIONALHISTORY]
set
[ACADEMICCATALOGPROGRAMID] = null,
[ACADEMICCATALOGDEGREEID] = null,
[EDUCATIONALINSTITUTIONID] = coalesce(nullif(@EDUCATIONALINSTITUTIONID, '00000000-0000-0000-0000-000000000000'), EH.[EDUCATIONALINSTITUTIONID]),
[ISPRIMARYRECORD] = coalesce(@ISPRIMARYRECORD, EH.[ISPRIMARYRECORD]),
[EDUCATIONALPROGRAMCODEID] = coalesce(nullif(@EDUCATIONALPROGRAMCODEID, '00000000-0000-0000-0000-000000000000'), EH.[EDUCATIONALPROGRAMCODEID]),
[CONSTITUENCYSTATUSCODE] =
case
when nullif(@EDUCATIONALHISTORYREASONCODEID, '00000000-0000-0000-0000-000000000000') is null then
coalesce(nullif(@CONSTITUENCYSTATUSCODE, 0), EH.[CONSTITUENCYSTATUSCODE])
else coalesce(@CONSTITUENCYSTATUSCODE, 0)
end,
[EDUCATIONALHISTORYREASONCODEID] = coalesce(nullif(@EDUCATIONALHISTORYREASONCODEID, '00000000-0000-0000-0000-000000000000'), EH.[EDUCATIONALHISTORYREASONCODEID]),
[EDUCATIONALHISTORYLEVELCODEID] = coalesce(nullif(@EDUCATIONALHISTORYLEVELCODEID, '00000000-0000-0000-0000-000000000000'), EH.[EDUCATIONALHISTORYLEVELCODEID]),
[DATELEFT] = coalesce(nullif(@DATELEFT, '00000000'), EH.[DATELEFT]),
[DATEGRADUATED] = coalesce(nullif(@DATEGRADUATED, '00000000'), EH.[DATEGRADUATED]),
[EDUCATIONALDEGREECODEID] = coalesce(nullif(@EDUCATIONALDEGREECODEID, '00000000-0000-0000-0000-000000000000'), EH.[EDUCATIONALDEGREECODEID]),
[EDUCATIONALAWARDCODEID] = coalesce(nullif(@EDUCATIONALAWARDCODEID, '00000000-0000-0000-0000-000000000000'), EH.[EDUCATIONALAWARDCODEID]),
[STARTDATE] = coalesce(nullif(@STARTDATE, '00000000'), EH.[STARTDATE]),
[CLASSOF] = coalesce(nullif(@CLASSYEAR, '0000'), EH.[CLASSOF]),
[PREFERREDCLASSYEAR] = coalesce(nullif(@PREFERREDCLASSYEAR, '0000'), EH.[PREFERREDCLASSYEAR]),
[EDUCATIONALSOURCECODEID] = coalesce(nullif(@EDUCATIONALSOURCECODEID, '00000000-0000-0000-0000-000000000000'), EH.[EDUCATIONALSOURCECODEID]),
[EDUCATIONALSOURCEDATE] = coalesce(nullif(@EDUCATIONALSOURCEDATE, '00000000'), EH.[EDUCATIONALSOURCEDATE]),
[COMMENT] = coalesce(nullif(@COMMENT, ''), EH.[COMMENT]),
[CHANGEDBYID] = @CHANGEAGENTID,
[DATECHANGED] = @CURRENTDATE
from
dbo.[EDUCATIONALHISTORY] EH
where
EH.[ID] = @ID;
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
declare @AFFILIATEDADDITIONALINFORMATIONTABLE table (
[EDUCATIONADDITIONALINFORMATIONID] uniqueidentifier,
[ACADEMICCATALOGCOLLEGEID] uniqueidentifier,
[ACADEMICCATALOGDIVISIONID] uniqueidentifier,
[ACADEMICCATALOGDEPARTMENTID] uniqueidentifier,
[ACADEMICCATALOGSUBDEPARTMENTID] uniqueidentifier,
[ACADEMICCATALOGDEGREETYPEID] uniqueidentifier
);
insert into @AFFILIATEDADDITIONALINFORMATIONTABLE (
[EDUCATIONADDITIONALINFORMATIONID],
[ACADEMICCATALOGCOLLEGEID],
[ACADEMICCATALOGDIVISIONID],
[ACADEMICCATALOGDEPARTMENTID],
[ACADEMICCATALOGSUBDEPARTMENTID],
[ACADEMICCATALOGDEGREETYPEID]
)
select
AAI.[EDUCATIONADDITIONALINFORMATIONID],
AAI.[ACADEMICCATALOGCOLLEGEID],
AAI.[ACADEMICCATALOGDIVISIONID],
AAI.[ACADEMICCATALOGDEPARTMENTID],
AAI.[ACADEMICCATALOGSUBDEPARTMENTID],
AAI.[ACADEMICCATALOGDEGREETYPEID]
from
dbo.UFN_EDUCATIONALHISTORY_GETAFFILIATEDADDITIONALINFORMATION_FORUPDATEBATCH_FROMITEMLISTXML(@AFFILIATEDADDITIONALINFORMATION) AAI;
update
dbo.[EDUCATIONADDITIONALINFORMATION]
set
[ACADEMICCATALOGCOLLEGEID] =
case
when exists(
select
ACC.ID
from
dbo.ACADEMICCATALOGCOLLEGE ACC
inner join
dbo.ACADEMICCATALOGDEGREE ACD on ACC.ACADEMICCATALOGDEGREEID = ACD.ID
where
ACD.ACADEMICCATALOGPROGRAMID = EH.[ACADEMICCATALOGPROGRAMID]
and ACC.ID = coalesce(nullif(AAIT.ACADEMICCATALOGCOLLEGEID, '00000000-0000-0000-0000-000000000000'), EAI.[ACADEMICCATALOGCOLLEGEID])
) then coalesce(nullif(AAIT.ACADEMICCATALOGCOLLEGEID, '00000000-0000-0000-0000-000000000000'), EAI.[ACADEMICCATALOGCOLLEGEID])
else null
end,
[ACADEMICCATALOGDIVISIONID] =
case
when exists(
select
DIV.ID
from
dbo.ACADEMICCATALOGDIVISION DIV
where
DIV.ACADEMICCATALOGCOLLEGEID =
(
select
ACC.ID
from
dbo.ACADEMICCATALOGCOLLEGE ACC
inner join
dbo.ACADEMICCATALOGDEGREE ACD on ACC.ACADEMICCATALOGDEGREEID = ACD.ID
where
ACD.ACADEMICCATALOGPROGRAMID = EH.[ACADEMICCATALOGPROGRAMID]
and ACC.ID = coalesce(nullif(AAIT.ACADEMICCATALOGCOLLEGEID, '00000000-0000-0000-0000-000000000000'), EAI.[ACADEMICCATALOGCOLLEGEID]))
and DIV.ID = coalesce(nullif(AAIT.ACADEMICCATALOGDIVISIONID, '00000000-0000-0000-0000-000000000000'), EAI.[ACADEMICCATALOGDIVISIONID])
) then coalesce(nullif(AAIT.ACADEMICCATALOGDIVISIONID, '00000000-0000-0000-0000-000000000000'), EAI.[ACADEMICCATALOGDIVISIONID])
else null
end,
[ACADEMICCATALOGDEPARTMENTID] =
case
when exists(
select
DEPT.ID
from
dbo.ACADEMICCATALOGDEPARTMENT DEPT
where
DEPT.ID = coalesce(nullif(AAIT.ACADEMICCATALOGDEPARTMENTID, '00000000-0000-0000-0000-000000000000'), EAI.[ACADEMICCATALOGDEPARTMENTID])
and (DEPT.ACADEMICCATALOGCOLLEGEID =
(
select
ACC.ID
from
dbo.ACADEMICCATALOGCOLLEGE ACC
inner join
dbo.ACADEMICCATALOGDEGREE ACD on ACC.ACADEMICCATALOGDEGREEID = ACD.ID
where
ACD.ACADEMICCATALOGPROGRAMID = EH.[ACADEMICCATALOGPROGRAMID]
and ACC.ID = coalesce(nullif(AAIT.ACADEMICCATALOGCOLLEGEID, '00000000-0000-0000-0000-000000000000'), EAI.[ACADEMICCATALOGCOLLEGEID])
)
or DEPT.ACADEMICCATALOGDIVISIONID =
(
select
DIV.ID
from
dbo.ACADEMICCATALOGDIVISION DIV
inner join
dbo.ACADEMICCATALOGCOLLEGE ACC on ACC.ID = DIV.ACADEMICCATALOGCOLLEGEID
inner join
dbo.ACADEMICCATALOGDEGREE ACD on ACC.ACADEMICCATALOGDEGREEID = ACD.ID
where
ACD.ACADEMICCATALOGPROGRAMID = EH.[ACADEMICCATALOGPROGRAMID]
and ACC.ID = coalesce(nullif(AAIT.ACADEMICCATALOGCOLLEGEID, '00000000-0000-0000-0000-000000000000'), EAI.[ACADEMICCATALOGCOLLEGEID])
and DIV.ID = coalesce(nullif(AAIT.ACADEMICCATALOGDIVISIONID, '00000000-0000-0000-0000-000000000000'), EAI.[ACADEMICCATALOGDIVISIONID])
))
) then coalesce(nullif(AAIT.ACADEMICCATALOGDEPARTMENTID, '00000000-0000-0000-0000-000000000000'), EAI.[ACADEMICCATALOGDEPARTMENTID])
else null
end,
[ACADEMICCATALOGSUBDEPARTMENTID] =
case
when exists(
select
SUB.ID
from
dbo.ACADEMICCATALOGSUBDEPARTMENT SUB
inner join
dbo.ACADEMICCATALOGDEPARTMENT DEPT on DEPT.ID = SUB.ACADEMICCATALOGDEPARTMENTID
where
DEPT.ID = coalesce(nullif(AAIT.ACADEMICCATALOGDEPARTMENTID, '00000000-0000-0000-0000-000000000000'), EAI.[ACADEMICCATALOGDEPARTMENTID])
and (DEPT.ACADEMICCATALOGCOLLEGEID =
(
select
ACC.ID
from
dbo.ACADEMICCATALOGCOLLEGE ACC
inner join
dbo.ACADEMICCATALOGDEGREE ACD on ACC.ACADEMICCATALOGDEGREEID = ACD.ID
where
ACD.ACADEMICCATALOGPROGRAMID = EH.[ACADEMICCATALOGPROGRAMID]
and ACC.ID = coalesce(nullif(AAIT.ACADEMICCATALOGCOLLEGEID, '00000000-0000-0000-0000-000000000000'), EAI.[ACADEMICCATALOGCOLLEGEID])
)
or DEPT.ACADEMICCATALOGDIVISIONID =
(
select
DIV.ID
from
dbo.ACADEMICCATALOGDIVISION DIV
inner join
dbo.ACADEMICCATALOGCOLLEGE ACC on ACC.ID = DIV.ACADEMICCATALOGCOLLEGEID
inner join
dbo.ACADEMICCATALOGDEGREE ACD on ACC.ACADEMICCATALOGDEGREEID = ACD.ID
where
ACD.ACADEMICCATALOGPROGRAMID = EH.[ACADEMICCATALOGPROGRAMID]
and ACC.ID = coalesce(nullif(AAIT.ACADEMICCATALOGCOLLEGEID, '00000000-0000-0000-0000-000000000000'), EAI.[ACADEMICCATALOGCOLLEGEID])
and DIV.ID = coalesce(nullif(AAIT.ACADEMICCATALOGDIVISIONID, '00000000-0000-0000-0000-000000000000'), EAI.[ACADEMICCATALOGDIVISIONID])
))
) then coalesce(nullif(AAIT.ACADEMICCATALOGSUBDEPARTMENTID, '00000000-0000-0000-0000-000000000000'), EAI.[ACADEMICCATALOGSUBDEPARTMENTID])
else null
end,
[ACADEMICCATALOGDEGREETYPEID] =
case
when exists(
select
DT.ID
from
dbo.ACADEMICCATALOGDEGREETYPE DT
where
DT.ID = coalesce(nullif(AAIT.ACADEMICCATALOGDEGREETYPEID, '00000000-0000-0000-0000-000000000000'), EAI.[ACADEMICCATALOGDEGREETYPEID])
and (DT.ACADEMICCATALOGDEPARTMENTID =
(
select
DEPT.ID
from
dbo.ACADEMICCATALOGDEPARTMENT DEPT
where
DEPT.ID = coalesce(nullif(AAIT.ACADEMICCATALOGDEPARTMENTID, '00000000-0000-0000-0000-000000000000'), EAI.[ACADEMICCATALOGDEPARTMENTID])
and (DEPT.ACADEMICCATALOGCOLLEGEID =
(
select
ACC.ID
from
dbo.ACADEMICCATALOGCOLLEGE ACC
inner join
dbo.ACADEMICCATALOGDEGREE ACD on ACC.ACADEMICCATALOGDEGREEID = ACD.ID
where
ACD.ACADEMICCATALOGPROGRAMID = EH.[ACADEMICCATALOGPROGRAMID]
and ACC.ID = coalesce(nullif(AAIT.ACADEMICCATALOGCOLLEGEID, '00000000-0000-0000-0000-000000000000'), EAI.[ACADEMICCATALOGCOLLEGEID])
)
or DEPT.ACADEMICCATALOGDIVISIONID =
(
select
DIV.ID
from
dbo.ACADEMICCATALOGDIVISION DIV
inner join
dbo.ACADEMICCATALOGCOLLEGE ACC on ACC.ID = DIV.ACADEMICCATALOGCOLLEGEID
inner join
dbo.ACADEMICCATALOGDEGREE ACD on ACC.ACADEMICCATALOGDEGREEID = ACD.ID
where
ACD.ACADEMICCATALOGPROGRAMID = EH.[ACADEMICCATALOGPROGRAMID]
and ACC.ID = coalesce(nullif(AAIT.ACADEMICCATALOGCOLLEGEID, '00000000-0000-0000-0000-000000000000'), EAI.[ACADEMICCATALOGCOLLEGEID])
and DIV.ID = coalesce(nullif(AAIT.ACADEMICCATALOGDIVISIONID, '00000000-0000-0000-0000-000000000000'), EAI.[ACADEMICCATALOGDIVISIONID])
))
)
or DT.ACADEMICCATALOGSUBDEPARTMENTID =
(
select
SUB.ID
from
dbo.ACADEMICCATALOGSUBDEPARTMENT SUB
inner join
dbo.ACADEMICCATALOGDEPARTMENT DEPT on DEPT.ID = SUB.ACADEMICCATALOGDEPARTMENTID
where
DEPT.ID = coalesce(nullif(AAIT.ACADEMICCATALOGDEPARTMENTID, '00000000-0000-0000-0000-000000000000'), EAI.[ACADEMICCATALOGDEPARTMENTID])
and SUB.ID = coalesce(nullif(AAIT.ACADEMICCATALOGSUBDEPARTMENTID, '00000000-0000-0000-0000-000000000000'), EAI.[ACADEMICCATALOGSUBDEPARTMENTID])
and (DEPT.ACADEMICCATALOGCOLLEGEID =
(
select
ACC.ID
from
dbo.ACADEMICCATALOGCOLLEGE ACC
inner join
dbo.ACADEMICCATALOGDEGREE ACD on ACC.ACADEMICCATALOGDEGREEID = ACD.ID
where
ACD.ACADEMICCATALOGPROGRAMID = EH.[ACADEMICCATALOGPROGRAMID]
and ACC.ID = coalesce(nullif(AAIT.ACADEMICCATALOGCOLLEGEID, '00000000-0000-0000-0000-000000000000'), EAI.[ACADEMICCATALOGCOLLEGEID])
)
or DEPT.ACADEMICCATALOGDIVISIONID =
(
select
DIV.ID
from
dbo.ACADEMICCATALOGDIVISION DIV
inner join
dbo.ACADEMICCATALOGCOLLEGE ACC on ACC.ID = DIV.ACADEMICCATALOGCOLLEGEID
inner join
dbo.ACADEMICCATALOGDEGREE ACD on ACC.ACADEMICCATALOGDEGREEID = ACD.ID
where
ACD.ACADEMICCATALOGPROGRAMID = EH.[ACADEMICCATALOGPROGRAMID]
and ACC.ID = coalesce(nullif(AAIT.ACADEMICCATALOGCOLLEGEID, '00000000-0000-0000-0000-000000000000'), EAI.[ACADEMICCATALOGCOLLEGEID])
and DIV.ID = coalesce(nullif(AAIT.ACADEMICCATALOGDIVISIONID, '00000000-0000-0000-0000-000000000000'), EAI.[ACADEMICCATALOGDIVISIONID])
))
))
) then coalesce(nullif(AAIT.ACADEMICCATALOGDEGREETYPEID, '00000000-0000-0000-0000-000000000000'), EAI.[ACADEMICCATALOGDEGREETYPEID])
else null
end,
[EDUCATIONALCOLLEGECODEID] = null,
[EDUCATIONALDIVISIONCODEID] = null,
[EDUCATIONALDEPARTMENTCODEID] = null,
[EDUCATIONALSUBDEPARTMENTCODEID] = null,
[EDUCATIONALDEGREETYPECODEID] = null,
[CHANGEDBYID] = @CHANGEAGENTID,
[DATECHANGED] = @CURRENTDATE
from
@AFFILIATEDADDITIONALINFORMATIONTABLE AAIT
inner join
dbo.[EDUCATIONADDITIONALINFORMATION] EAI
on
AAIT.[EDUCATIONADDITIONALINFORMATIONID] = EAI.[ID] or
(AAIT.[EDUCATIONADDITIONALINFORMATIONID] is null and
EAI.EDUCATIONALHISTORYID = @ID and
(AAIT.[ACADEMICCATALOGCOLLEGEID] = EAI.[ACADEMICCATALOGCOLLEGEID] or (AAIT.[ACADEMICCATALOGCOLLEGEID] is null and EAI.[ACADEMICCATALOGCOLLEGEID] is null)) and
(AAIT.[ACADEMICCATALOGDIVISIONID] = EAI.[ACADEMICCATALOGDIVISIONID] or (AAIT.[ACADEMICCATALOGDIVISIONID] is null and EAI.[ACADEMICCATALOGDIVISIONID] is null)) and
(AAIT.[ACADEMICCATALOGDEPARTMENTID] = EAI.[ACADEMICCATALOGDEPARTMENTID] or (AAIT.[ACADEMICCATALOGDEPARTMENTID] is null and EAI.[ACADEMICCATALOGDEPARTMENTID] is null)) and
(AAIT.[ACADEMICCATALOGSUBDEPARTMENTID] = EAI.[ACADEMICCATALOGSUBDEPARTMENTID] or (AAIT.[ACADEMICCATALOGSUBDEPARTMENTID] is null and EAI.[ACADEMICCATALOGSUBDEPARTMENTID] is null)) and
(AAIT.[ACADEMICCATALOGDEGREETYPEID] = EAI.[ACADEMICCATALOGDEGREETYPEID] or (AAIT.[ACADEMICCATALOGDEGREETYPEID] is null and EAI.[ACADEMICCATALOGDEGREETYPEID] is null)))
inner join
dbo.[EDUCATIONALHISTORY] EH on EH.ID = EAI.EDUCATIONALHISTORYID;
insert into dbo.[EDUCATIONADDITIONALINFORMATION] (
[EDUCATIONALHISTORYID],
[ACADEMICCATALOGCOLLEGEID],
[ACADEMICCATALOGDIVISIONID],
[ACADEMICCATALOGDEPARTMENTID],
[ACADEMICCATALOGSUBDEPARTMENTID],
[ACADEMICCATALOGDEGREETYPEID],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED]
)
select
@ID,
AAIT.[ACADEMICCATALOGCOLLEGEID],
AAIT.[ACADEMICCATALOGDIVISIONID],
AAIT.[ACADEMICCATALOGDEPARTMENTID],
AAIT.[ACADEMICCATALOGSUBDEPARTMENTID],
AAIT.[ACADEMICCATALOGDEGREETYPEID],
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from
@AFFILIATEDADDITIONALINFORMATIONTABLE AAIT
left join
dbo.[EDUCATIONADDITIONALINFORMATION] EAI
on
AAIT.[EDUCATIONADDITIONALINFORMATIONID] = EAI.[ID] or
(AAIT.[EDUCATIONADDITIONALINFORMATIONID] is null and
EAI.EDUCATIONALHISTORYID = @ID and
(AAIT.[ACADEMICCATALOGCOLLEGEID] = EAI.[ACADEMICCATALOGCOLLEGEID] or (AAIT.[ACADEMICCATALOGCOLLEGEID] is null and EAI.[ACADEMICCATALOGCOLLEGEID] is null)) and
(AAIT.[ACADEMICCATALOGDEPARTMENTID] = EAI.[ACADEMICCATALOGDEPARTMENTID] or (AAIT.[ACADEMICCATALOGDEPARTMENTID] is null and EAI.[ACADEMICCATALOGDEPARTMENTID] is null)) and
(AAIT.[ACADEMICCATALOGDIVISIONID] = EAI.[ACADEMICCATALOGDIVISIONID] or (AAIT.[ACADEMICCATALOGDIVISIONID] is null and EAI.[ACADEMICCATALOGDIVISIONID] is null)) and
(AAIT.[ACADEMICCATALOGSUBDEPARTMENTID] = EAI.[ACADEMICCATALOGSUBDEPARTMENTID] or (AAIT.[ACADEMICCATALOGSUBDEPARTMENTID] is null and EAI.[ACADEMICCATALOGSUBDEPARTMENTID] is null)) and
(AAIT.[ACADEMICCATALOGDEGREETYPEID] = EAI.[ACADEMICCATALOGDEGREETYPEID] or (AAIT.[ACADEMICCATALOGDEGREETYPEID] is null and EAI.[ACADEMICCATALOGDEGREETYPEID] is null)))
where
EAI.[ID] is null;
end
else begin
declare @UNAFFILIATEDADDITIONALINFORMATIONTABLE table (
[EDUCATIONADDITIONALINFORMATIONID] uniqueidentifier,
[EDUCATIONALCOLLEGECODEID] uniqueidentifier,
[EDUCATIONALDIVISIONCODEID] uniqueidentifier,
[EDUCATIONALDEPARTMENTCODEID] uniqueidentifier,
[EDUCATIONALSUBDEPARTMENTCODEID] uniqueidentifier,
[EDUCATIONALDEGREETYPECODEID] uniqueidentifier
);
insert into @UNAFFILIATEDADDITIONALINFORMATIONTABLE (
[EDUCATIONADDITIONALINFORMATIONID],
[EDUCATIONALCOLLEGECODEID],
[EDUCATIONALDIVISIONCODEID],
[EDUCATIONALDEPARTMENTCODEID],
[EDUCATIONALSUBDEPARTMENTCODEID],
[EDUCATIONALDEGREETYPECODEID]
)
select
UAI.[EDUCATIONADDITIONALINFORMATIONID],
UAI.[EDUCATIONALCOLLEGECODEID],
UAI.[EDUCATIONALDIVISIONCODEID],
UAI.[EDUCATIONALDEPARTMENTCODEID],
UAI.[EDUCATIONALSUBDEPARTMENTCODEID],
UAI.[EDUCATIONALDEGREETYPECODEID]
from
dbo.UFN_EDUCATIONALHISTORY_GETUNAFFILIATEDADDITIONALINFORMATION_FORUPDATEBATCH_FROMITEMLISTXML(@UNAFFILIATEDADDITIONALINFORMATION) UAI;
update
dbo.[EDUCATIONADDITIONALINFORMATION]
set
[EDUCATIONALCOLLEGECODEID] = coalesce(nullif(UAIT.[EDUCATIONALCOLLEGECODEID], '00000000-0000-0000-0000-000000000000'), EAI.EDUCATIONALCOLLEGECODEID),
[EDUCATIONALDIVISIONCODEID] = coalesce(nullif(UAIT.[EDUCATIONALDIVISIONCODEID], '00000000-0000-0000-0000-000000000000'), EAI.EDUCATIONALDIVISIONCODEID),
[EDUCATIONALDEPARTMENTCODEID] = coalesce(nullif(UAIT.[EDUCATIONALDEPARTMENTCODEID], '00000000-0000-0000-0000-000000000000'), EAI.EDUCATIONALDEPARTMENTCODEID),
[EDUCATIONALSUBDEPARTMENTCODEID] = coalesce(nullif(UAIT.[EDUCATIONALSUBDEPARTMENTCODEID], '00000000-0000-0000-0000-000000000000'), EAI.EDUCATIONALSUBDEPARTMENTCODEID),
[EDUCATIONALDEGREETYPECODEID] = coalesce(nullif(UAIT.[EDUCATIONALDEGREETYPECODEID], '00000000-0000-0000-0000-000000000000'), EAI.EDUCATIONALDEGREETYPECODEID),
[ACADEMICCATALOGCOLLEGEID] = null,
[ACADEMICCATALOGDEPARTMENTID] = null,
[ACADEMICCATALOGDEGREETYPEID] = null,
[CHANGEDBYID] = @CHANGEAGENTID,
[DATECHANGED] = @CURRENTDATE
from
@UNAFFILIATEDADDITIONALINFORMATIONTABLE UAIT
inner join
dbo.[EDUCATIONADDITIONALINFORMATION] EAI
on
UAIT.[EDUCATIONADDITIONALINFORMATIONID] = EAI.[ID] or
(UAIT.[EDUCATIONADDITIONALINFORMATIONID] is null and
EAI.EDUCATIONALHISTORYID = @ID and
(UAIT.[EDUCATIONALCOLLEGECODEID] = EAI.[EDUCATIONALCOLLEGECODEID] or (UAIT.[EDUCATIONALCOLLEGECODEID] is null and EAI.[EDUCATIONALCOLLEGECODEID] is null)) and
(UAIT.[EDUCATIONALDIVISIONCODEID] = EAI.[EDUCATIONALDIVISIONCODEID] or (UAIT.[EDUCATIONALDIVISIONCODEID] is null and EAI.[EDUCATIONALDIVISIONCODEID] is null)) and
(UAIT.[EDUCATIONALDEPARTMENTCODEID] = EAI.[EDUCATIONALDEPARTMENTCODEID] or (UAIT.[EDUCATIONALDEPARTMENTCODEID] is null and EAI.[EDUCATIONALDEPARTMENTCODEID] is null)) and
(UAIT.[EDUCATIONALSUBDEPARTMENTCODEID] = EAI.[EDUCATIONALSUBDEPARTMENTCODEID] or (UAIT.[EDUCATIONALSUBDEPARTMENTCODEID] is null and EAI.[EDUCATIONALSUBDEPARTMENTCODEID] is null)) and
(UAIT.[EDUCATIONALDEGREETYPECODEID] = EAI.[EDUCATIONALDEGREETYPECODEID] or (UAIT.[EDUCATIONALDEGREETYPECODEID] is null and EAI.[EDUCATIONALDEGREETYPECODEID] is null)));
insert into dbo.[EDUCATIONADDITIONALINFORMATION] (
[EDUCATIONALHISTORYID],
[EDUCATIONALCOLLEGECODEID],
[EDUCATIONALDIVISIONCODEID],
[EDUCATIONALDEPARTMENTCODEID],
[EDUCATIONALSUBDEPARTMENTCODEID],
[EDUCATIONALDEGREETYPECODEID],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED]
)
select
@ID,
UAIT.[EDUCATIONALCOLLEGECODEID],
UAIT.[EDUCATIONALDIVISIONCODEID],
UAIT.[EDUCATIONALDEPARTMENTCODEID],
UAIT.[EDUCATIONALSUBDEPARTMENTCODEID],
UAIT.[EDUCATIONALDEGREETYPECODEID],
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from
@UNAFFILIATEDADDITIONALINFORMATIONTABLE UAIT
left join
dbo.[EDUCATIONADDITIONALINFORMATION] EAI
on
UAIT.[EDUCATIONADDITIONALINFORMATIONID] = EAI.[ID] or
(UAIT.[EDUCATIONADDITIONALINFORMATIONID] is null and
EAI.EDUCATIONALHISTORYID = @ID and
(UAIT.[EDUCATIONALCOLLEGECODEID] = EAI.[EDUCATIONALCOLLEGECODEID] or (UAIT.[EDUCATIONALCOLLEGECODEID] is null and EAI.[EDUCATIONALCOLLEGECODEID] is null)) and
(UAIT.[EDUCATIONALDIVISIONCODEID] = EAI.[EDUCATIONALDIVISIONCODEID] or (UAIT.[EDUCATIONALDIVISIONCODEID] is null and EAI.[EDUCATIONALDIVISIONCODEID] is null)) and
(UAIT.[EDUCATIONALDEPARTMENTCODEID] = EAI.[EDUCATIONALDEPARTMENTCODEID] or (UAIT.[EDUCATIONALDEPARTMENTCODEID] is null and EAI.[EDUCATIONALDEPARTMENTCODEID] is null)) and
(UAIT.[EDUCATIONALSUBDEPARTMENTCODEID] = EAI.[EDUCATIONALSUBDEPARTMENTCODEID] or (UAIT.[EDUCATIONALSUBDEPARTMENTCODEID] is null and EAI.[EDUCATIONALSUBDEPARTMENTCODEID] is null)) and
(UAIT.[EDUCATIONALDEGREETYPECODEID] = EAI.[EDUCATIONALDEGREETYPECODEID] or (UAIT.[EDUCATIONALDEGREETYPECODEID] is null and EAI.[EDUCATIONALDEGREETYPECODEID] is null)))
where
EAI.[ID] is null;
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(nullif(EIT.[EDUCATIONALINVOLVEMENTTYPECODEID], '00000000-0000-0000-0000-000000000000'), EI.[EDUCATIONALINVOLVEMENTTYPECODEID]),
[EDUCATIONALINVOLVEMENTNAMEID] = coalesce(nullif(EIT.[EDUCATIONALINVOLVEMENTNAMEID], '00000000-0000-0000-0000-000000000000'), EI.[EDUCATIONALINVOLVEMENTNAMEID]),
[DATEFROM] = coalesce(nullif(EIT.[DATEFROM], '00000000'), EI.[DATEFROM]),
[DATETO] = coalesce(nullif(EIT.[DATETO], '00000000'), EI.[DATETO]),
[EDUCATIONALINVOLVEMENTAWARDCODEID] = coalesce(nullif(EIT.[EDUCATIONALINVOLVEMENTAWARDCODEID], '00000000-0000-0000-0000-000000000000'), 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;