USP_DATAFORMTEMPLATE_ADD_BATCHEDUCATIONALHISTORYUPDATEBATCHROW

The save procedure used by the add dataform template "Educational History Update Batch Row Add Form".

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	INOUT	The output parameter indicating the ID of the record added.
@BATCHID	uniqueidentifier	IN	Input parameter indicating the context ID for the record being added.
@CHANGEAGENTID	uniqueidentifier	IN	Input parameter indicating the ID of the change agent invoking the procedure.
@SEQUENCE	int	IN	Sequence
@PRIMARYRECORDID	uniqueidentifier	IN	Educational history
@PRIMARYCONTEXTRECORDID	uniqueidentifier	IN	Constituent
@CONSTITUENTLOOKUPID	nvarchar(100)	IN	Lookup ID
@CONSTITUENTKEYNAME	nvarchar(100)	IN	Last name
@CONSTITUENTFIRSTNAME	nvarchar(50)	IN	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_BATCHEDUCATIONALHISTORYUPDATEBATCHROW(
    @ID uniqueidentifier = null output,
    @BATCHID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier,
    @SEQUENCE int,
    @PRIMARYRECORDID uniqueidentifier = null,
    @PRIMARYCONTEXTRECORDID uniqueidentifier = null,
    @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 = 0,
    @PREFERREDCLASSYEAR dbo.UDT_YEAR = 0,
    @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;

if @ID is null
    set @ID = newid();

if @CHANGEAGENTID is null
    exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

set @CURRENTDATE = getdate();

if exists (select ID from dbo.EDUCATIONALHISTORY where ID = @PRIMARYRECORDID)
    select
        @PRIMARYCONTEXTRECORDID = EDUCATIONALHISTORY.CONSTITUENTID,
        @CONSTITUENTLOOKUPID = CONSTITUENT.LOOKUPID,
        @CONSTITUENTKEYNAME = CONSTITUENT.KEYNAME,
        @CONSTITUENTFIRSTNAME = CONSTITUENT.FIRSTNAME,
    @EDUCATIONALINSTITUTIONID = isnull(@EDUCATIONALINSTITUTIONID, EDUCATIONALHISTORY.EDUCATIONALINSTITUTIONID),
    @EDUCATIONALPROGRAMCODEID = isnull(@EDUCATIONALPROGRAMCODEID, EDUCATIONALHISTORY.EDUCATIONALPROGRAMCODEID),
    @EDUCATIONALDEGREECODEID = isnull(@EDUCATIONALDEGREECODEID, EDUCATIONALHISTORY.EDUCATIONALDEGREECODEID)
    from
        dbo.EDUCATIONALHISTORY
        inner join dbo.CONSTITUENT on CONSTITUENT.ID = EDUCATIONALHISTORY.CONSTITUENTID
    inner join dbo.EDUCATIONALINSTITUTION as INSTITUTION on EDUCATIONALHISTORY.EDUCATIONALINSTITUTIONID = INSTITUTION.ID
  where
        EDUCATIONALHISTORY.ID = @PRIMARYRECORDID


declare @MATCHINGCONSTITUENTCOUNT smallint
select @MATCHINGCONSTITUENTCOUNT = count(ID) from dbo.UFN_CONSTITUENT_GETIDSFROMCRITERIA( @PRIMARYCONTEXTRECORDID, @CONSTITUENTLOOKUPID, @CONSTITUENTKEYNAME, @CONSTITUENTFIRSTNAME, 500)

if @MATCHINGCONSTITUENTCOUNT = 1 begin
    select top(1) @PRIMARYCONTEXTRECORDID = ID from dbo.UFN_CONSTITUENT_GETIDSFROMCRITERIA( @PRIMARYCONTEXTRECORDID, @CONSTITUENTLOOKUPID, @CONSTITUENTKEYNAME, @CONSTITUENTFIRSTNAME, 500)
end

begin try
insert into dbo.BATCHEDUCATIONALHISTORYUPDATE(
    [ID],
    [BATCHID],
    [SEQUENCE],
    [ADDEDBYID],
    [CHANGEDBYID],
    [DATEADDED],
    [DATECHANGED],
    [PRIMARYRECORDID],
    [PRIMARYCONTEXTRECORDID],
    [CONSTITUENTLOOKUPID],
    [CONSTITUENTKEYNAME],
    [CONSTITUENTFIRSTNAME],
    [EDUCATIONALINSTITUTIONID],
    [EDUCATIONALINSTITUTIONNAME],
    [EDUCATIONALINSTITUTIONFICECODE],
    [EDUCATIONALINSTITUTIONCOUNTRYID],
    [EDUCATIONALINSTITUTIONCITY],
    [EDUCATIONALINSTITUTIONSTATEID],
    [ISPRIMARYRECORD],
    [ACADEMICCATALOGPROGRAMID],
    [EDUCATIONALPROGRAMCODEID],
    [CONSTITUENCYSTATUSCODE],
    [EDUCATIONALHISTORYREASONCODEID],
    [EDUCATIONALHISTORYLEVELCODEID],
    [DATELEFT],
    [DATEGRADUATED],
    [ACADEMICCATALOGDEGREEID],
    [EDUCATIONALDEGREECODEID],
    [EDUCATIONALAWARDCODEID],
    [STARTDATE],
    [CLASSYEAR],
    [PREFERREDCLASSYEAR],
    [EDUCATIONALSOURCECODEID],
    [EDUCATIONALSOURCEDATE],
    [COMMENT],
    [EDUCATIONALHISTORYSTATUSID]) values (
    @ID,
    @BATCHID,
    @SEQUENCE,
    @CHANGEAGENTID,
    @CHANGEAGENTID,
    @CURRENTDATE,
    @CURRENTDATE,
    @PRIMARYRECORDID,
    @PRIMARYCONTEXTRECORDID,
    coalesce(@CONSTITUENTLOOKUPID, ''),
    coalesce(@CONSTITUENTKEYNAME, ''),
    coalesce(@CONSTITUENTFIRSTNAME, ''),
    @EDUCATIONALINSTITUTIONID,
    coalesce(@EDUCATIONALINSTITUTIONNAME, ''),
    coalesce(@EDUCATIONALINSTITUTIONFICECODE, ''),
    @EDUCATIONALINSTITUTIONCOUNTRYID,
    coalesce(@EDUCATIONALINSTITUTIONCITY, ''),
    @EDUCATIONALINSTITUTIONSTATEID,
    coalesce(@ISPRIMARYRECORD, 0),
    @ACADEMICCATALOGPROGRAMID,
    @EDUCATIONALPROGRAMCODEID,
    coalesce(@CONSTITUENCYSTATUSCODE, 0),
    @EDUCATIONALHISTORYREASONCODEID,
    @EDUCATIONALHISTORYLEVELCODEID,
    coalesce(@DATELEFT, '00000000'),
    coalesce(@DATEGRADUATED, '00000000'),
    @ACADEMICCATALOGDEGREEID,
    @EDUCATIONALDEGREECODEID,
    @EDUCATIONALAWARDCODEID,
    coalesce(@STARTDATE, '00000000'),
    coalesce(@CLASSYEAR, 0),
    coalesce(@PREFERREDCLASSYEAR, 0),
    @EDUCATIONALSOURCECODEID,
    coalesce(@EDUCATIONALSOURCEDATE, '00000000'),
    coalesce(@COMMENT, ''),
    coalesce(@EDUCATIONALHISTORYSTATUSID,'00000000-0000-0000-0000-000000000001')
);

exec dbo.USP_BATCHEDUCATIONALHISTORYUPDATE_GETAFFILIATEDADDITIONALINFORMATION_ADDFROMXML @ID, @AFFILIATEDADDITIONALINFORMATION, @CHANGEAGENTID


exec dbo.USP_BATCHEDUCATIONALHISTORYUPDATE_GETUNAFFILIATEDADDITIONALINFORMATION_ADDFROMXML @ID, @UNAFFILIATEDADDITIONALINFORMATION, @CHANGEAGENTID


insert into dbo.BATCHEDUCATIONALHISTORYUPDATEEDUCATIONALINVOLVEMENTS(
    BATCHEDUCATIONALHISTORYUPDATEID,
    EDUCATIONALINVOLVEMENTID,
    EDUCATIONALINVOLVEMENTTYPECODEID,
    EDUCATIONALINVOLVEMENTNAMEID,
    DATEFROM,
    DATETO,
    EDUCATIONALINVOLVEMENTAWARDCODEID,
    COMMENT,
    ADDEDBYID,
    CHANGEDBYID,
    DATEADDED,
    DATECHANGED,
    POSITION
)
select
    @ID,
    EDUCATIONALINVOLVEMENTID,
    EDUCATIONALINVOLVEMENTTYPECODEID,
    EDUCATIONALINVOLVEMENTNAMEID,
    coalesce(DATEFROM, '00000000'),
    coalesce(DATETO, '00000000'),
    EDUCATIONALINVOLVEMENTAWARDCODEID,
    coalesce(COMMENT, ''),
    @CHANGEAGENTID,
    @CHANGEAGENTID,
    @CURRENTDATE,
    @CURRENTDATE,
    POSITION
from
    dbo.UFN_BATCHEDUCATIONALHISTORYUPDATE_GETEDUCATIONALINVOLVEMENTS_FROMITEMLISTXML(@EDUCATIONALINVOLVEMENTS)

if @MATCHINGCONSTITUENTCOUNT > 1 begin
    insert into dbo.BATCHEDUCATIONALHISTORYUPDATEBATCHSYSTEMMESSAGE
    (
        ID, MESSAGETEXT, MESSAGETYPECODE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
    )
    values
    (
        @ID, 'The ID for this constituent matches multiple records.', 0, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
    )
end
else if @MATCHINGCONSTITUENTCOUNT = 0 begin
    insert into dbo.BATCHEDUCATIONALHISTORYUPDATEBATCHSYSTEMMESSAGE
    (
        ID, MESSAGETEXT, MESSAGETYPECODE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
    )
    values
    (
        @ID, 'The ID for this constituent does not match any records.', 0, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
    )
end

end try
begin catch
    exec.dbo.USP_RAISE_ERROR;
    return 1;
end catch

return 0;