USP_DATAFORMTEMPLATE_EDIT_EDUCATIONALINSTITUTION_3
The save procedure used by the edit dataform template "Educational Institution Edit Form 2".
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. |
| @FICECODE | nvarchar(50) | IN | FICE code |
| @NAME | nvarchar(100) | IN | Name |
| @ISAFFILIATED | bit | IN | This institution is affiliated with our organization |
| @COUNTRYID | uniqueidentifier | IN | Country |
| @CITY | nvarchar(50) | IN | City |
| @STATEID | uniqueidentifier | IN | State |
| @ADDRESSBLOCK | nvarchar(150) | IN | Address |
| @POSTCODE | nvarchar(12) | IN | Zip |
| @PHONENUMBER | nvarchar(100) | IN | Phone number |
Definition
Copy
CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_EDUCATIONALINSTITUTION_3
(
@ID uniqueidentifier,
@CHANGEAGENTID uniqueidentifier = null,
@FICECODE nvarchar(50),
@NAME nvarchar(100),
@ISAFFILIATED bit,
@COUNTRYID uniqueidentifier,
@CITY nvarchar(50),
@STATEID uniqueidentifier,
@ADDRESSBLOCK nvarchar(150),
@POSTCODE nvarchar(12),
@PHONENUMBER nvarchar(100)
)
as
set nocount on;
declare @KEYNAME nvarchar(100);
declare @KEYNAMEPREFIX nvarchar(50);
declare @CURRENTDATE datetime;
set @CURRENTDATE = getdate();
if @CHANGEAGENTID is null
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;
begin try
if dbo.UFN_EDUCATIONALINSTITUTION_NAMEISUNIQUE(@ID, @NAME)=1
begin
exec dbo.USP_PARSE_ORGANIZATION_NAME @NAME, @KEYNAME output, @KEYNAMEPREFIX output;
update dbo.CONSTITUENT set
KEYNAME = @KEYNAME,
KEYNAMEPREFIX = @KEYNAMEPREFIX,
DATECHANGED = @CURRENTDATE,
CHANGEDBYID = @CHANGEAGENTID
where ID = @ID;
if @COUNTRYID is null
exec @COUNTRYID = dbo.UFN_COUNTRY_GETDEFAULT;
if exists(select ID from dbo.ADDRESS where (CONSTITUENTID = @ID) and (ISPRIMARY = 1))
begin
update dbo.ADDRESS
set COUNTRYID = @COUNTRYID,
ADDRESSBLOCK = @ADDRESSBLOCK,
CITY = @CITY,
STATEID = @STATEID,
POSTCODE = @POSTCODE,
DATECHANGED = @CURRENTDATE,
CHANGEDBYID = @CHANGEAGENTID
where (CONSTITUENTID = @ID) and
(ISPRIMARY = 1);
end
else
begin
declare @ADDRESSID uniqueidentifier;
set @ADDRESSID = newID();
insert into dbo.ADDRESS
(ID, CONSTITUENTID, ISPRIMARY, COUNTRYID, ADDRESSBLOCK, CITY, STATEID, POSTCODE, DATEADDED, DATECHANGED, ADDEDBYID,CHANGEDBYID)
values
(@ADDRESSID, @ID, 1, @COUNTRYID, @ADDRESSBLOCK, @CITY, @STATEID, @POSTCODE, @CURRENTDATE, @CURRENTDATE, @CHANGEAGENTID, @CHANGEAGENTID);
end
if coalesce(@PHONENUMBER, '') <> ''
begin
if exists(select ID from dbo.PHONE where (CONSTITUENTID = @ID) and (ISPRIMARY = 1))
begin
update dbo.PHONE
set NUMBER = @PHONENUMBER,
DATECHANGED = @CURRENTDATE,
CHANGEDBYID = @CHANGEAGENTID
where (CONSTITUENTID = @ID) and (ISPRIMARY = 1);
end
else
begin
insert into dbo.PHONE
(ID, CONSTITUENTID, ISPRIMARY, NUMBER, SEQUENCE, DATEADDED, DATECHANGED, ADDEDBYID, CHANGEDBYID)
values
(newID(), @ID, 1, @PHONENUMBER, 1, @CURRENTDATE, @CURRENTDATE, @CHANGEAGENTID, @CHANGEAGENTID);
end
end
update dbo.EDUCATIONALINSTITUTION
set
FICECODE = @FICECODE,
DATECHANGED = @CURRENTDATE,
CHANGEDBYID = @CHANGEAGENTID
where
ID = @ID;
-- Might need to create or remove a school here
declare @ISSCHOOL bit;
set @ISSCHOOL = dbo.UFN_CONSTITUENT_ISSCHOOL(@ID);
if ((@ISAFFILIATED = 1) and (@ISSCHOOL = 0))
begin
insert into dbo.SCHOOL
(
ID,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
values
(
@ID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
)
end
else
begin if ((@ISAFFILIATED = 0) and (@ISSCHOOL = 1))
-- Remove the school record
exec dbo.USP_SCHOOL_DELETEBYID_WITHCHANGEAGENTID @ID, @CHANGEAGENTID;
end
end
else
raiserror ('ERR_EDUCATIONALINSTITUTION_NAMEISUNIQUE',13,1);
end try
begin catch
exec dbo.USP_RAISE_ERROR;
return 1;
end catch
return 0;