USP_EDUCATIONALINSTITUTION_ADD

Adds an educational institution or school to the system with primary address information.

Parameters

Parameter	Parameter Type	Mode
@ID	uniqueidentifier	INOUT
@CHANGEAGENTID	uniqueidentifier	IN
@NAME	nvarchar(100)	IN
@FICECODE	nvarchar(50)	IN
@ISAFFILIATED	bit	IN
@WEBADDRESS	UDT_WEBADDRESS	IN
@PARENTSCHOOLID	uniqueidentifier	IN
@GENDERCODE	tinyint	IN
@SCHOOLGRADELEVELS	xml	IN
@ADDRESS_ADDRESSTYPECODEID	uniqueidentifier	IN
@ADDRESS_DONOTMAIL	bit	IN
@ADDRESS_COUNTRYID	uniqueidentifier	IN
@ADDRESS_STATEID	uniqueidentifier	IN
@ADDRESS_ADDRESSBLOCK	nvarchar(150)	IN
@ADDRESS_CITY	nvarchar(50)	IN
@ADDRESS_POSTCODE	nvarchar(12)	IN
@PHONE_PHONETYPECODEID	uniqueidentifier	IN
@PHONE_NUMBER	nvarchar(100)	IN
@SKIP_ADDING_SECURITYGROUPS	bit	IN
@EMAILADDRESS_EMAILADDRESSTYPECODEID	uniqueidentifier	IN
@EMAILADDRESS_EMAILADDRESS	UDT_EMAILADDRESS	IN
@OMITFROMVALIDATION	bit	IN
@CART	nvarchar(10)	IN
@DPC	nvarchar(8)	IN
@LOT	nvarchar(5)	IN
@COUNTYCODEID	uniqueidentifier	IN
@CONGRESSIONALDISTRICTCODEID	uniqueidentifier	IN
@LASTVALIDATIONATTEMPTDATE	datetime	IN
@VALIDATIONMESSAGE	nvarchar(100)	IN
@CERTIFICATIONDATA	int	IN
@ADDRESS_DONOTMAILREASONCODEID	uniqueidentifier	IN
@ISPRIMARY	bit	IN
@COMMENTCODE	tinyint	IN

Definition

Copy



    CREATE procedure dbo.USP_EDUCATIONALINSTITUTION_ADD
        (
            @ID uniqueidentifier = null output,
            @CHANGEAGENTID uniqueidentifier = null,
            @NAME nvarchar(100),
      @FICECODE nvarchar(50) = '',
      @ISAFFILIATED bit = 0,
            @WEBADDRESS dbo.UDT_WEBADDRESS = '',
            @PARENTSCHOOLID uniqueidentifier,
            @GENDERCODE tinyint = 0,
            @SCHOOLGRADELEVELS xml = 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) = '',
            @SKIP_ADDING_SECURITYGROUPS bit=0,
            @EMAILADDRESS_EMAILADDRESSTYPECODEID uniqueidentifier = null,
            @EMAILADDRESS_EMAILADDRESS dbo.UDT_EMAILADDRESS = '',
            @OMITFROMVALIDATION bit = 0,
            @CART nvarchar(10) = '',
            @DPC nvarchar(8) = '',
            @LOT nvarchar(5) = '',
            @COUNTYCODEID uniqueidentifier = null,
            @CONGRESSIONALDISTRICTCODEID uniqueidentifier = null,
            @LASTVALIDATIONATTEMPTDATE datetime = null,
            @VALIDATIONMESSAGE nvarchar(100) = '',
            @CERTIFICATIONDATA integer = 0,
            @ADDRESS_DONOTMAILREASONCODEID uniqueidentifier = null, 
            @ISPRIMARY bit = 0,
            @COMMENTCODE tinyint = 1
        )
    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();

                exec dbo.USP_DATAFORMTEMPLATE_ADD_ORGANIZATIONRECORD @ID output, null, @CHANGEAGENTID, @NAME, @WEBADDRESS, null, null, null, 0, 0, @PARENTSCHOOLID, @ADDRESS_ADDRESSTYPECODEID, @ADDRESS_DONOTMAIL, @ADDRESS_COUNTRYID, @ADDRESS_STATEID, @ADDRESS_ADDRESSBLOCK, @ADDRESS_CITY, @ADDRESS_POSTCODE, @PHONE_PHONETYPECODEID, @PHONE_NUMBER, @SKIP_ADDING_SECURITYGROUPS, @EMAILADDRESS_EMAILADDRESSTYPECODEID, @EMAILADDRESS_EMAILADDRESS, @OMITFROMVALIDATION, @CART, @DPC, @LOT, @COUNTYCODEID, @CONGRESSIONALDISTRICTCODEID, @LASTVALIDATIONATTEMPTDATE, @VALIDATIONMESSAGE, @CERTIFICATIONDATA, @ADDRESS_DONOTMAILREASONCODEID, @ISPRIMARY;

                update dbo.CONSTITUENT 
                set ISCONSTITUENT = 0,
                    CHANGEDBYID = @CHANGEAGENTID,
                    DATECHANGED = @CURRENTDATE
                where ID = @ID;

                insert into dbo.EDUCATIONALINSTITUTION
                (
                    ID,
                    FICECODE,
                    ADDEDBYID, 
                    CHANGEDBYID, 
                    DATEADDED, 
                    DATECHANGED
                )
                values
                (
                    @ID,
                    coalesce(@FICECODE, ''),
                    @CHANGEAGENTID,
                    @CHANGEAGENTID,
                    @CURRENTDATE,
                    @CURRENTDATE
                );

                if (@ISAFFILIATED = 1)
                begin
                  -- Add the extra school constituency

                  insert into dbo.SCHOOL
                  (
                      ID,
                      GENDERCODE,
                      ADDEDBYID, 
                      CHANGEDBYID, 
                      DATEADDED, 
                      DATECHANGED,
            COMMENTCODE
                  )
                  values
                  (
                      @ID,
                      @GENDERCODE,
                      @CHANGEAGENTID,
                      @CHANGEAGENTID,
                      @CURRENTDATE,
                      @CURRENTDATE,
            @COMMENTCODE
                  );

                  -- Add the school grade levels

          if (not @SCHOOLGRADELEVELS is null)
          begin
                    exec dbo.USP_SCHOOL_GETGRADELEVELS_ADDFROMXML @ID, @SCHOOLGRADELEVELS, @CHANGEAGENTID, @CURRENTDATE;
          end

                end

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

            return 0;