USP_DATAFORMTEMPLATE_ADD_STUDENT
The save procedure used by the add dataform template "Student Add Form".
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @ID | uniqueidentifier | INOUT | The output parameter indicating the ID of the record added. |
| @CURRENTAPPUSERID | uniqueidentifier | IN | Input parameter indicating the ID of the current user. |
| @CHANGEAGENTID | uniqueidentifier | IN | Input parameter indicating the ID of the change agent invoking the procedure. |
| @LASTNAME | nvarchar(100) | IN | Last name |
| @FIRSTNAME | nvarchar(50) | IN | First name |
| @MIDDLENAME | nvarchar(50) | IN | Middle name |
| @NICKNAME | nvarchar(50) | IN | Nickname |
| @SUFFIXCODEID | uniqueidentifier | IN | Suffix |
| @SCHOOLID | uniqueidentifier | IN | School |
| @ACADEMICYEARID | uniqueidentifier | IN | Academic year |
| @SCHOOLGRADELEVELID | uniqueidentifier | IN | Grade level |
| @DATEENROLLED | UDT_FUZZYDATE | IN | Date enrolled |
| @ADDRESS_ADDRESSTYPECODEID | uniqueidentifier | IN | Address type |
| @ADDRESS_DONOTMAIL | bit | IN | Do not send mail to this address |
| @ADDRESS_COUNTRYID | uniqueidentifier | IN | Country |
| @ADDRESS_STATEID | uniqueidentifier | IN | State |
| @ADDRESS_ADDRESSBLOCK | nvarchar(150) | IN | Address |
| @ADDRESS_CITY | nvarchar(50) | IN | City |
| @ADDRESS_POSTCODE | nvarchar(12) | IN | ZIP |
| @PHONE_PHONETYPECODEID | uniqueidentifier | IN | Phone type |
| @PHONE_NUMBER | nvarchar(100) | IN | Phone number |
| @EMAILADDRESS_EMAILADDRESSTYPECODEID | uniqueidentifier | IN | Email type |
| @EMAILADDRESS_EMAILADDRESS | UDT_EMAILADDRESS | IN | Email address |
| @SKIP_ADDING_SECURITYGROUPS | bit | IN | Skip adding security groups |
| @ADDRESS_OMITFROMVALIDATION | bit | IN | Omit this address from validation |
| @ADDRESS_CART | nvarchar(10) | IN | |
| @ADDRESS_DPC | nvarchar(8) | IN | |
| @ADDRESS_LOT | nvarchar(5) | IN | |
| @ADDRESS_COUNTYCODEID | uniqueidentifier | IN | |
| @ADDRESS_CONGRESSIONALDISTRICTCODEID | uniqueidentifier | IN | |
| @ADDRESS_LASTVALIDATIONATTEMPTDATE | datetime | IN | |
| @ADDRESS_VALIDATIONMESSAGE | nvarchar(100) | IN | |
| @ADDRESS_CERTIFICATIONDATA | int | IN | |
| @SKIP_ADDING_SITES | bit | IN | Skip adding sites |
| @ADDRESS_DONOTMAILREASONCODEID | uniqueidentifier | IN | Reason |
| @GENDERCODE | tinyint | IN | Gender |
| @BIRTHDATE | UDT_FUZZYDATE | IN | Birth date |
| @ADVISORID | uniqueidentifier | IN | Advisor |
| @STUDENTENROLLMENTSTATUS | uniqueidentifier | IN | Current status |
| @RELATION_CONSTITUENTID | uniqueidentifier | IN | Full name |
| @RELATION_LASTNAME | nvarchar(100) | IN | Last name |
| @RELATION_FIRSTNAME | nvarchar(50) | IN | First name |
| @RELATION_MIDDLENAME | nvarchar(50) | IN | Middle name |
| @RELATION_TITLECODEID | uniqueidentifier | IN | Title |
| @RELATION_TITLE2CODEID | uniqueidentifier | IN | Title 2 |
| @RELATION_SUFFIXCODEID | uniqueidentifier | IN | Suffix |
| @RELATION_SUFFIX2CODEID | uniqueidentifier | IN | Suffix 2 |
| @RELATION_NICKNAME | nvarchar(50) | IN | Nickname |
| @RELATION_MAIDENNAME | nvarchar(50) | IN | Maiden name |
| @RELATION_MARITALSTATUSCODEID | uniqueidentifier | IN | Marital status |
| @RELATION_GENDERCODE | tinyint | IN | Gender |
| @RELATION_BIRTHDATE | UDT_FUZZYDATE | IN | Birth date |
| @RELATION_PHONE_PHONETYPECODEID | uniqueidentifier | IN | Phone type |
| @RELATION_PHONE_NUMBER | nvarchar(100) | IN | Phone number |
| @RELATION_PHONE_PHONETYPECODEID_2 | uniqueidentifier | IN | Phone type 2 |
| @RELATION_PHONE_NUMBER_2 | nvarchar(100) | IN | Phone number 2 |
| @RELATION_EMAILADDRESS_EMAILADDRESSTYPECODEID | uniqueidentifier | IN | Email type |
| @RELATION_EMAILADDRESS_EMAILADDRESS | UDT_EMAILADDRESS | IN | Email address |
| @RELATION_RELATIONSHIPTYPECODEID | uniqueidentifier | IN | Relationship type |
| @RELATION_RECIPROCALTYPECODEID | uniqueidentifier | IN | Reciprocal relationship type |
| @RELATION_COPYPRIMARYINFORMATION | bit | IN | Copy primary information |
| @ADDITIONALRELATION_CONSTITUENTID | uniqueidentifier | IN | Full name |
| @ADDITIONALRELATION_LASTNAME | nvarchar(100) | IN | Last name |
| @ADDITIONALRELATION_FIRSTNAME | nvarchar(50) | IN | First name |
| @ADDITIONALRELATION_MIDDLENAME | nvarchar(50) | IN | Middle name |
| @ADDITIONALRELATION_TITLECODEID | uniqueidentifier | IN | Title |
| @ADDITIONALRELATION_TITLE2CODEID | uniqueidentifier | IN | Title 2 |
| @ADDITIONALRELATION_SUFFIXCODEID | uniqueidentifier | IN | Suffix |
| @ADDITIONALRELATION_SUFFIX2CODEID | uniqueidentifier | IN | Suffix 2 |
| @ADDITIONALRELATION_NICKNAME | nvarchar(50) | IN | Nickname |
| @ADDITIONALRELATION_MAIDENNAME | nvarchar(50) | IN | Maiden name |
| @ADDITIONALRELATION_MARITALSTATUSCODEID | uniqueidentifier | IN | Marital status |
| @ADDITIONALRELATION_GENDERCODE | tinyint | IN | Gender |
| @ADDITIONALRELATION_BIRTHDATE | UDT_FUZZYDATE | IN | Birth date |
| @ADDITIONALRELATION_PHONE_PHONETYPECODEID | uniqueidentifier | IN | Phone type |
| @ADDITIONALRELATION_PHONE_NUMBER | nvarchar(100) | IN | Phone number |
| @ADDITIONALRELATION_EMAILADDRESS_EMAILADDRESSTYPECODEID | uniqueidentifier | IN | Email type |
| @ADDITIONALRELATION_EMAILADDRESS_EMAILADDRESS | UDT_EMAILADDRESS | IN | Email address |
| @ADDITIONALRELATION_RELATIONSHIPTYPECODEID | uniqueidentifier | IN | Relationship type |
| @ADDITIONALRELATION_RECIPROCALTYPECODEID | uniqueidentifier | IN | Reciprocal relationship type |
| @ADDITIONALRELATION_COPYPRIMARYINFORMATION | bit | IN | Copy primary information |
| @ADDITIONALRELATION_SPOUSE_RELATIONSHIPTYPECODEID | uniqueidentifier | IN | Relationship type |
| @ADDITIONALRELATION_SPOUSE_RECIPROCALTYPECODEID | uniqueidentifier | IN | Reciprocal relationship type |
| @ADDITIONALRELATION_ISSPOUSEOFRELATION | bit | IN | Additional relation is spouse of relation |
| @TITLECODEID | uniqueidentifier | IN | Title |
| @RELATION_ISEMERGENCYCONTACT | bit | IN | Is emergency contact |
| @ADDITIONALRELATION_ISEMERGENCYCONTACT | bit | IN | Is emergency contact |
| @PHONE2_PHONETYPECODEID | uniqueidentifier | IN | Phone type 2 |
| @PHONE2_NUMBER | nvarchar(100) | IN | Phone number 2 |
Definition
Copy
CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_STUDENT
(
@ID uniqueidentifier = null output,
@CURRENTAPPUSERID uniqueidentifier,
@CHANGEAGENTID uniqueidentifier = null,
@LASTNAME nvarchar(100),
@FIRSTNAME nvarchar(50) = '',
@MIDDLENAME nvarchar(50) = '',
@NICKNAME nvarchar(50) = '',
@SUFFIXCODEID uniqueidentifier = null,
@SCHOOLID uniqueidentifier = null,
@ACADEMICYEARID uniqueidentifier = null,
@SCHOOLGRADELEVELID uniqueidentifier = null,
@DATEENROLLED dbo.UDT_FUZZYDATE = null,
@ADDRESS_ADDRESSTYPECODEID uniqueidentifier = null,
@ADDRESS_DONOTMAIL bit = 0,
@ADDRESS_COUNTRYID uniqueidentifier = null,
@ADDRESS_STATEID uniqueidentifier = null,
@ADDRESS_ADDRESSBLOCK nvarchar(150) = '',
@ADDRESS_CITY nvarchar(50) = '',
@ADDRESS_POSTCODE nvarchar(12) = '',
@PHONE_PHONETYPECODEID uniqueidentifier = null,
@PHONE_NUMBER nvarchar(100) = '',
@EMAILADDRESS_EMAILADDRESSTYPECODEID uniqueidentifier = null,
@EMAILADDRESS_EMAILADDRESS dbo.UDT_EMAILADDRESS = '',
@SKIP_ADDING_SECURITYGROUPS bit=0,
@ADDRESS_OMITFROMVALIDATION bit = 0,
@ADDRESS_CART nvarchar(10) = '',
@ADDRESS_DPC nvarchar(8) = '',
@ADDRESS_LOT nvarchar(5) = '',
@ADDRESS_COUNTYCODEID uniqueidentifier = null,
@ADDRESS_CONGRESSIONALDISTRICTCODEID uniqueidentifier = null,
@ADDRESS_LASTVALIDATIONATTEMPTDATE datetime = null,
@ADDRESS_VALIDATIONMESSAGE nvarchar(100) = '',
@ADDRESS_CERTIFICATIONDATA integer = 0,
@SKIP_ADDING_SITES bit = 0,
@ADDRESS_DONOTMAILREASONCODEID uniqueidentifier = null,
@GENDERCODE tinyint = 0,
@BIRTHDATE dbo.UDT_FUZZYDATE = '00000000',
@ADVISORID uniqueidentifier = null,
@STUDENTENROLLMENTSTATUS uniqueidentifier,
@RELATION_CONSTITUENTID uniqueidentifier = null,
@RELATION_LASTNAME nvarchar(100) = '',
@RELATION_FIRSTNAME nvarchar(50) = '',
@RELATION_MIDDLENAME nvarchar(50) = '',
@RELATION_TITLECODEID uniqueidentifier = null,
@RELATION_TITLE2CODEID uniqueidentifier = null,
@RELATION_SUFFIXCODEID uniqueidentifier = null,
@RELATION_SUFFIX2CODEID uniqueidentifier = null,
@RELATION_NICKNAME nvarchar(50) = '',
@RELATION_MAIDENNAME nvarchar(50) = '',
@RELATION_MARITALSTATUSCODEID uniqueidentifier = null,
@RELATION_GENDERCODE tinyint = 0,
@RELATION_BIRTHDATE dbo.UDT_FUZZYDATE = '00000000',
@RELATION_PHONE_PHONETYPECODEID uniqueidentifier = null,
@RELATION_PHONE_NUMBER nvarchar(100) = '',
@RELATION_PHONE_PHONETYPECODEID_2 uniqueidentifier = null,
@RELATION_PHONE_NUMBER_2 nvarchar(100) = '',
@RELATION_EMAILADDRESS_EMAILADDRESSTYPECODEID uniqueidentifier = null,
@RELATION_EMAILADDRESS_EMAILADDRESS dbo.UDT_EMAILADDRESS = '',
@RELATION_RELATIONSHIPTYPECODEID uniqueidentifier = null,
@RELATION_RECIPROCALTYPECODEID uniqueidentifier = null,
@RELATION_COPYPRIMARYINFORMATION bit = 1,
@ADDITIONALRELATION_CONSTITUENTID uniqueidentifier = null,
@ADDITIONALRELATION_LASTNAME nvarchar(100) = '',
@ADDITIONALRELATION_FIRSTNAME nvarchar(50) = '',
@ADDITIONALRELATION_MIDDLENAME nvarchar(50) = '',
@ADDITIONALRELATION_TITLECODEID uniqueidentifier = null,
@ADDITIONALRELATION_TITLE2CODEID uniqueidentifier = null,
@ADDITIONALRELATION_SUFFIXCODEID uniqueidentifier = null,
@ADDITIONALRELATION_SUFFIX2CODEID uniqueidentifier = null,
@ADDITIONALRELATION_NICKNAME nvarchar(50) = '',
@ADDITIONALRELATION_MAIDENNAME nvarchar(50) = '',
@ADDITIONALRELATION_MARITALSTATUSCODEID uniqueidentifier = null,
@ADDITIONALRELATION_GENDERCODE tinyint = 0,
@ADDITIONALRELATION_BIRTHDATE dbo.UDT_FUZZYDATE = '00000000',
@ADDITIONALRELATION_PHONE_PHONETYPECODEID uniqueidentifier = null,
@ADDITIONALRELATION_PHONE_NUMBER nvarchar(100) = '',
@ADDITIONALRELATION_EMAILADDRESS_EMAILADDRESSTYPECODEID uniqueidentifier = null,
@ADDITIONALRELATION_EMAILADDRESS_EMAILADDRESS dbo.UDT_EMAILADDRESS = '',
@ADDITIONALRELATION_RELATIONSHIPTYPECODEID uniqueidentifier = null,
@ADDITIONALRELATION_RECIPROCALTYPECODEID uniqueidentifier = null,
@ADDITIONALRELATION_COPYPRIMARYINFORMATION bit = 1,
@ADDITIONALRELATION_SPOUSE_RELATIONSHIPTYPECODEID uniqueidentifier = null,
@ADDITIONALRELATION_SPOUSE_RECIPROCALTYPECODEID uniqueidentifier = null,
@ADDITIONALRELATION_ISSPOUSEOFRELATION bit = 0,
@TITLECODEID uniqueidentifier = null,
@RELATION_ISEMERGENCYCONTACT bit = 0,
@ADDITIONALRELATION_ISEMERGENCYCONTACT bit = 0,
@PHONE2_PHONETYPECODEID uniqueidentifier = null,
@PHONE2_NUMBER nvarchar(100) = ''
)
as
set nocount on;
if @ID is null
set @ID = newid();
if @CHANGEAGENTID is null
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output
if @ADDRESS_DONOTMAIL = 0
set @ADDRESS_DONOTMAILREASONCODEID = null
declare @CURRENTDATE datetime
set @CURRENTDATE = getdate()
declare @STARTDATE date, @ENDDATE date
select @STARTDATE = dbo.ACADEMICYEAR.[STARTDATE], @ENDDATE = dbo.ACADEMICYEAR.[ENDDATE]
from dbo.ACADEMICYEAR
where dbo.ACADEMICYEAR.[ID] = @ACADEMICYEARID
declare @ENROLLMENTID uniqueidentifier, @STUDENTPROGRESSIONID uniqueidentifier
set @ENROLLMENTID = newid()
set @STUDENTPROGRESSIONID = newid()
begin try
-- handle inserting the data
insert into dbo.CONSTITUENT
(
[ID],
[ISORGANIZATION],
[KEYNAME],
[FIRSTNAME],
[MIDDLENAME],
[NICKNAME],
[TITLECODEID],
[SUFFIXCODEID],
[GENDERCODE],
[BIRTHDATE],
[ISCONSTITUENT],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED])
values
(@ID,
0,
@LASTNAME,
@FIRSTNAME,
@MIDDLENAME,
@NICKNAME,
@TITLECODEID,
@SUFFIXCODEID,
@GENDERCODE,
@BIRTHDATE,
case when dbo.UFN_STUDENTCONSTITUENCYCRITERIA_GETISCONSTITUENTWHEN() = 0 then 1 else 0 end,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE);
insert into dbo.STUDENT
(ID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
values
(@ID, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE);
if
(
(@ADDRESS_STATEID is not null)
or (coalesce(@ADDRESS_ADDRESSBLOCK,'') <> '')
or (coalesce(@ADDRESS_CITY,'') <> '')
or (coalesce(@ADDRESS_POSTCODE,'') <> '')
or (@ADDRESS_ADDRESSTYPECODEID is not null)
or (@ADDRESS_COUNTRYID is not null)
or (@ADDRESS_DONOTMAIL = 1)
or (@ADDRESS_DONOTMAILREASONCODEID is not null)
)
begin
declare @ADDRESSID uniqueidentifier;
set @ADDRESSID = newID();
insert into dbo.[ADDRESS]
(
[ID],
[CONSTITUENTID],
[ADDRESSTYPECODEID],
[ISPRIMARY],
[DONOTMAIL],
[COUNTRYID],
[STATEID],
[ADDRESSBLOCK],
[CITY],
[POSTCODE],
[CART],
[DPC],
[LOT],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED],
[DONOTMAILREASONCODEID]
)
values
(
@ADDRESSID,
@ID,
@ADDRESS_ADDRESSTYPECODEID,
1,
@ADDRESS_DONOTMAIL,
@ADDRESS_COUNTRYID,
@ADDRESS_STATEID,
@ADDRESS_ADDRESSBLOCK,
@ADDRESS_CITY,
@ADDRESS_POSTCODE,
@ADDRESS_CART,
@ADDRESS_DPC,
@ADDRESS_LOT,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE,
@ADDRESS_DONOTMAILREASONCODEID
);
insert into dbo.ADDRESSVALIDATIONUPDATE
(
[ID],
[COUNTYCODEID],
[CONGRESSIONALDISTRICTCODEID],
[LASTVALIDATIONATTEMPTDATE],
[VALIDATIONMESSAGE],
[CERTIFICATIONDATA],
[OMITFROMVALIDATION],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED]
)
values
(
@ADDRESSID,
@ADDRESS_COUNTYCODEID,
@ADDRESS_CONGRESSIONALDISTRICTCODEID,
@CURRENTDATE,
@ADDRESS_VALIDATIONMESSAGE,
@ADDRESS_CERTIFICATIONDATA,
@ADDRESS_OMITFROMVALIDATION,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
);
end
/*Start Primary Phone */
if (@PHONE_PHONETYPECODEID is not null) or (coalesce(@PHONE_NUMBER,'') <> '')
begin
insert into dbo.[PHONE]
(
[CONSTITUENTID],
[PHONETYPECODEID],
[NUMBER],
[ISPRIMARY],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED],
[COUNTRYID]
)
values
(
@ID,
@PHONE_PHONETYPECODEID,
@PHONE_NUMBER,
1,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE,
@ADDRESS_COUNTRYID
);
end
/*End Primary Phone */
/*Start Second Phone */
declare @PHONE2_ISPRIMARY int
set @PHONE2_ISPRIMARY = case when coalesce(@PHONE_NUMBER,'') = '' then 1 else 0 end
if (@PHONE2_PHONETYPECODEID is not null) or (coalesce(@PHONE2_NUMBER,'') <> '')
begin
insert into dbo.[PHONE]
(
[CONSTITUENTID],
[PHONETYPECODEID],
[NUMBER],
[ISPRIMARY],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED],
[COUNTRYID]
)
values
(
@ID,
@PHONE2_PHONETYPECODEID,
@PHONE2_NUMBER,
@PHONE2_ISPRIMARY,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE,
@ADDRESS_COUNTRYID
);
end
/*End Second Phone */
/*Start Primary Email Address */
if (@EMAILADDRESS_EMAILADDRESSTYPECODEID is not null) or (coalesce(@EMAILADDRESS_EMAILADDRESS,'') <> '')
begin
insert into dbo.[EMAILADDRESS]
(
[CONSTITUENTID],
[EMAILADDRESSTYPECODEID],
[EMAILADDRESS],
[ISPRIMARY],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED]
)
values
(
@ID,
@EMAILADDRESS_EMAILADDRESSTYPECODEID,
@EMAILADDRESS_EMAILADDRESS,
1,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
);
end
/*End Primary Email Address */
/*Start security groups*/
if COALESCE(@SKIP_ADDING_SECURITYGROUPS,0) = 0
BEGIN
exec dbo.USP_CONSTITUENT_ASSIGNSECURITYGROUPS_FORNEWRECORD
@APPUSERID =@CURRENTAPPUSERID,
@DATAFORMTEMPLATEID='B4A8C2CB-FE50-4c50-A587-DC3C409B0E6A',
@CONSTITUENTID =@ID,
@DATEADDEDTOUSE =@CURRENTDATE,
@CHANGEAGENTID = @CHANGEAGENTID;
END
/*end security groups*/
if coalesce(@SKIP_ADDING_SITES,0) = 0
exec dbo.USP_CONSTITUENT_ASSIGNSITES_FORNEWRECORD
@APPUSERID = @CURRENTAPPUSERID,
@DATAFORMTEMPLATEID = 'B4A8C2CB-FE50-4c50-A587-DC3C409B0E6A',
@CONSTITUENTID = @ID,
@DATEADDEDTOUSE = @CURRENTDATE,
@CHANGEAGENTID = @CHANGEAGENTID;
/*Start name format defaults*/
INSERT INTO [dbo].[NAMEFORMAT]
([CONSTITUENTID]
,[NAMEFORMATTYPECODEID]
,[NAMEFORMATFUNCTIONID]
,[ADDEDBYID]
,[CHANGEDBYID]
,[DATEADDED]
,[DATECHANGED]
,[PRIMARYADDRESSEE]
,[PRIMARYSALUTATION]
,[SEQUENCE])
SELECT
@ID
,NFD.NAMEFORMATTYPECODEID
,NFD.NAMEFORMATFUNCTIONID
,@CHANGEAGENTID
,@CHANGEAGENTID
,@CURRENTDATE
,@CURRENTDATE
,NFD.PRIMARYADDRESSEE
,NFD.PRIMARYSALUTATION
,(select count(ID)
from dbo.NAMEFORMATDEFAULT as SUBNFD
where NFD.ID > SUBNFD.ID
and NFD.APPLYTOCODE = SUBNFD.APPLYTOCODE)
FROM dbo.NAMEFORMATDEFAULT as NFD
WHERE NFD.APPLYTOCODE = 0
/*End name format defaults*/
/*Start student enrollment*/
declare @SOURCEID uniqueidentifier
exec dbo.USP_EDUCATIONALHISTORY_RO_EDUCATIONALSOURCEID @SOURCEID output
INSERT INTO [dbo].[EDUCATIONALHISTORY]
(
[ID],
[CONSTITUENTID],
[EDUCATIONALINSTITUTIONID],
[STARTDATE],
[EDUCATIONALSOURCECODEID],
[EDUCATIONALSOURCEDATE],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED]
)
values
(
@ENROLLMENTID,
@ID,
@SCHOOLID,
@DATEENROLLED,
@SOURCEID,
convert(varchar(8), getdate(), 112),
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
)
/*End student enrollment*/
/*Start student enrollment status history*/
INSERT INTO [dbo].[EDUCATIONALHISTORYSTATUSHISTORY]
(
[EDUCATIONALHISTORYID],
[EDUCATIONALHISTORYSTATUSID],
[STATUSDATE],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED]
)
values
(
@ENROLLMENTID,
@STUDENTENROLLMENTSTATUS,
@DATEENROLLED,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
)
/*End student enrollment status history*/
/*Start Student Progression*/
INSERT INTO [dbo].[STUDENTPROGRESSION]
(
[ID],
[ENROLLMENTID],
[SCHOOLGRADELEVELID],
[STARTDATE],
[ENDDATE],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED]
)
values
(
@STUDENTPROGRESSIONID,
@ENROLLMENTID,
@SCHOOLGRADELEVELID,
@STARTDATE,
@ENDDATE,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
);
/*Start Student Advisor*/
if @ADVISORID is not null
begin
INSERT INTO [dbo].[STUDENTADVISOR]
(
[STUDENTPROGRESSIONID],
[FACULTYID],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED]
)
values
(
@STUDENTPROGRESSIONID,
@ADVISORID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
)
end
/*ADD RELATION*/
--Add relationship if relationship types or the relation are specified
if (@RELATION_CONSTITUENTID is not null) or (len(@RELATION_LASTNAME) > 0) or (@RELATION_RECIPROCALTYPECODEID is not null) or (@RELATION_RELATIONSHIPTYPECODEID is not null)
begin
exec dbo.USP_STUDENT_ADDRELATION @ID, @CURRENTAPPUSERID, @CHANGEAGENTID, @CURRENTDATE, @SKIP_ADDING_SITES, @SKIP_ADDING_SECURITYGROUPS, @RELATION_CONSTITUENTID output, @RELATION_LASTNAME, @RELATION_FIRSTNAME, @RELATION_MIDDLENAME, @RELATION_TITLECODEID, @RELATION_TITLE2CODEID, @RELATION_SUFFIXCODEID, @RELATION_SUFFIX2CODEID, @RELATION_NICKNAME, @RELATION_MAIDENNAME, @RELATION_MARITALSTATUSCODEID, @RELATION_GENDERCODE, @RELATION_BIRTHDATE, @RELATION_PHONE_PHONETYPECODEID, @RELATION_PHONE_NUMBER, @RELATION_PHONE_PHONETYPECODEID_2, @RELATION_PHONE_NUMBER_2, @RELATION_EMAILADDRESS_EMAILADDRESSTYPECODEID, @RELATION_EMAILADDRESS_EMAILADDRESS, @RELATION_RELATIONSHIPTYPECODEID, @RELATION_RECIPROCALTYPECODEID, @RELATION_COPYPRIMARYINFORMATION, @RELATION_ADDRESSCOUNTRYID=@ADDRESS_COUNTRYID
update dbo.RELATIONSHIP
set ISEMERGENCYCONTACT = @RELATION_ISEMERGENCYCONTACT
where RECIPROCALCONSTITUENTID = @ID and
RELATIONSHIPCONSTITUENTID = @RELATION_CONSTITUENTID and
RELATIONSHIPTYPECODEID = @RELATION_RECIPROCALTYPECODEID and
RECIPROCALTYPECODEID = @RELATION_RELATIONSHIPTYPECODEID
end
/*ADD ADDITIONAL RELATION*/
--Add relationship if relationship types or the relation are specified
if (@ADDITIONALRELATION_CONSTITUENTID is not null) or (len(@ADDITIONALRELATION_LASTNAME) > 0) or (@ADDITIONALRELATION_RECIPROCALTYPECODEID is not null) or (@ADDITIONALRELATION_RELATIONSHIPTYPECODEID is not null)
begin
exec dbo.USP_STUDENT_ADDRELATION @ID, @CURRENTAPPUSERID, @CHANGEAGENTID, @CURRENTDATE, @SKIP_ADDING_SITES, @SKIP_ADDING_SECURITYGROUPS, @ADDITIONALRELATION_CONSTITUENTID output, @ADDITIONALRELATION_LASTNAME, @ADDITIONALRELATION_FIRSTNAME, @ADDITIONALRELATION_MIDDLENAME, @ADDITIONALRELATION_TITLECODEID, @ADDITIONALRELATION_TITLE2CODEID, @ADDITIONALRELATION_SUFFIXCODEID, @ADDITIONALRELATION_SUFFIX2CODEID, @ADDITIONALRELATION_NICKNAME, @ADDITIONALRELATION_MAIDENNAME, @ADDITIONALRELATION_MARITALSTATUSCODEID, @ADDITIONALRELATION_GENDERCODE, @ADDITIONALRELATION_BIRTHDATE, @ADDITIONALRELATION_PHONE_PHONETYPECODEID, @ADDITIONALRELATION_PHONE_NUMBER, @RELATION_EMAILADDRESS_EMAILADDRESSTYPECODEID = @ADDITIONALRELATION_EMAILADDRESS_EMAILADDRESSTYPECODEID, @RELATION_EMAILADDRESS_EMAILADDRESS = @ADDITIONALRELATION_EMAILADDRESS_EMAILADDRESS, @RELATION_RELATIONSHIPTYPECODEID = @ADDITIONALRELATION_RELATIONSHIPTYPECODEID, @RELATION_RECIPROCALTYPECODEID = @ADDITIONALRELATION_RECIPROCALTYPECODEID, @RELATION_COPYPRIMARYINFORMATION = @ADDITIONALRELATION_COPYPRIMARYINFORMATION, @RELATION_ADDRESSCOUNTRYID=@ADDRESS_COUNTRYID
update dbo.RELATIONSHIP
set ISEMERGENCYCONTACT = @ADDITIONALRELATION_ISEMERGENCYCONTACT
where RECIPROCALCONSTITUENTID = @ID and
RELATIONSHIPCONSTITUENTID = @ADDITIONALRELATION_CONSTITUENTID and
RELATIONSHIPTYPECODEID = @ADDITIONALRELATION_RECIPROCALTYPECODEID and
RECIPROCALTYPECODEID = @ADDITIONALRELATION_RELATIONSHIPTYPECODEID
end
/*ADD SPOUSE RELATIONSHIP BETWEEN RELATION AND ADDITIONAL RELATION*/
if (@ADDITIONALRELATION_SPOUSE_RELATIONSHIPTYPECODEID is not null) or (@ADDITIONALRELATION_SPOUSE_RECIPROCALTYPECODEID is not null)
begin
-- throw an error if @Relation_ConstituentID has not been set
if @RELATION_CONSTITUENTID is null
begin
raiserror('ERR_RELATION_NOTSPECIFIED', 13, 1)
return 0
end
exec dbo.USP_STUDENT_ADDRELATION @RELATION_CONSTITUENTID, @CURRENTAPPUSERID, @CHANGEAGENTID, @CURRENTDATE, @SKIP_ADDING_SITES, @SKIP_ADDING_SECURITYGROUPS, @ADDITIONALRELATION_CONSTITUENTID, @RELATION_RELATIONSHIPTYPECODEID = @ADDITIONALRELATION_SPOUSE_RELATIONSHIPTYPECODEID, @RELATION_RECIPROCALTYPECODEID = @ADDITIONALRELATION_SPOUSE_RECIPROCALTYPECODEID, @RELATION_COPYPRIMARYINFORMATION = 0, @RELATION_ISSPOUSE = @ADDITIONALRELATION_ISSPOUSEOFRELATION
end
end try
begin catch
exec dbo.USP_RAISE_ERROR
return 1
end catch
return 0