USP_DATAFORMTEMPLATE_EDITLOAD_EDUCATIONALHISTORYUPDATEBATCHTEMPLATE

The load 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 used to load the fields defined on the form.
@DATALOADED	bit	INOUT	Output parameter indicating whether or not data was actually loaded.
@TSLONG	bigint	INOUT	Output parameter indicating the TSLONG value of the record being edited. This is used to manage multi-user concurrency issues when multiple users access the same record.
@VALIDATEONLY	bit	INOUT	Validate only
@CONSTITUENTLOOKUPID	nvarchar(100)	INOUT	Constituent lookup ID
@CONSTITUENTKEYNAME	nvarchar(100)	INOUT	Constituent last name
@CONSTITUENTFIRSTNAME	nvarchar(50)	INOUT	Constituent first name
@EDUCATIONALINSTITUTIONID	uniqueidentifier	INOUT	Educational institution
@EDUCATIONALINSTITUTIONFICECODE	nvarchar(50)	INOUT	Educational institution FICE code
@EDUCATIONALINSTITUTIONNAME	nvarchar(100)	INOUT	Educational institution name
@EDUCATIONALINSTITUTIONCOUNTRYID	uniqueidentifier	INOUT	Educational institution country
@EDUCATIONALINSTITUTIONCITY	nvarchar(150)	INOUT	Educational institution city
@EDUCATIONALINSTITUTIONSTATEID	uniqueidentifier	INOUT	Educational institution state
@ISPRIMARYRECORD	bit	INOUT	Primary education information
@ACADEMICCATALOGPROGRAMID	uniqueidentifier	INOUT	Academic catalog program
@EDUCATIONALPROGRAMCODEID	uniqueidentifier	INOUT	Educational program
@CONSTITUENCYSTATUSCODE	tinyint	INOUT	Status
@EDUCATIONALHISTORYREASONCODEID	uniqueidentifier	INOUT	Reason
@EDUCATIONALHISTORYLEVELCODEID	uniqueidentifier	INOUT	Level
@DATELEFT	UDT_FUZZYDATE	INOUT	Date attended to
@DATEGRADUATED	UDT_FUZZYDATE	INOUT	Graduation date
@ACADEMICCATALOGDEGREEID	uniqueidentifier	INOUT	Academic catalog degree
@EDUCATIONALDEGREECODEID	uniqueidentifier	INOUT	Educational degree
@EDUCATIONALAWARDCODEID	uniqueidentifier	INOUT	Honor awarded
@STARTDATE	UDT_FUZZYDATE	INOUT	Date attended from
@CLASSYEAR	UDT_YEAR	INOUT	Class of
@PREFERREDCLASSYEAR	UDT_YEAR	INOUT	Preferred class of
@EDUCATIONALSOURCECODEID	uniqueidentifier	INOUT	Information source
@EDUCATIONALSOURCEDATE	UDT_FUZZYDATE	INOUT	Source date
@COMMENT	nvarchar(500)	INOUT	Comments
@AFFILIATEDADDITIONALINFORMATION	xml	INOUT	Academic catalog additional information
@UNAFFILIATEDADDITIONALINFORMATION	xml	INOUT	Educational additional information
@EDUCATIONALINVOLVEMENTS	xml	INOUT	Educational involvements
@USEACADEMICCATALOG	bit	INOUT	Use academic catalog
@EDUCATIONALHISTORYSTATUSID	uniqueidentifier	INOUT	Status

Definition

Copy


CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDITLOAD_EDUCATIONALHISTORYUPDATEBATCHTEMPLATE (
    @ID uniqueidentifier,
    @DATALOADED bit = 0 output,
    @TSLONG bigint = 0 output,
    @VALIDATEONLY bit = null output,
    @CONSTITUENTLOOKUPID nvarchar(100) = null output,
    @CONSTITUENTKEYNAME nvarchar(100) = null output,
    @CONSTITUENTFIRSTNAME nvarchar(50) = null output,
    @EDUCATIONALINSTITUTIONID uniqueidentifier = null output,
    @EDUCATIONALINSTITUTIONFICECODE nvarchar(50) = null output,
    @EDUCATIONALINSTITUTIONNAME nvarchar(100) = null output,
    @EDUCATIONALINSTITUTIONCOUNTRYID uniqueidentifier = null output,
    @EDUCATIONALINSTITUTIONCITY nvarchar(150) = null output,
    @EDUCATIONALINSTITUTIONSTATEID uniqueidentifier = null output,
    @ISPRIMARYRECORD bit = null output,
    @ACADEMICCATALOGPROGRAMID uniqueidentifier = null output,
    @EDUCATIONALPROGRAMCODEID uniqueidentifier = null output,
    @CONSTITUENCYSTATUSCODE tinyint = null output,
    @EDUCATIONALHISTORYREASONCODEID uniqueidentifier = null output,
    @EDUCATIONALHISTORYLEVELCODEID uniqueidentifier = null output,
    @DATELEFT dbo.UDT_FUZZYDATE = null output,
    @DATEGRADUATED dbo.UDT_FUZZYDATE = null output,
    @ACADEMICCATALOGDEGREEID uniqueidentifier = null output,
    @EDUCATIONALDEGREECODEID uniqueidentifier = null output,
    @EDUCATIONALAWARDCODEID uniqueidentifier = null output,
    @STARTDATE dbo.UDT_FUZZYDATE = null output,
    @CLASSYEAR dbo.UDT_YEAR = null output,
    @PREFERREDCLASSYEAR dbo.UDT_YEAR = null output,
    @EDUCATIONALSOURCECODEID uniqueidentifier = null output,
    @EDUCATIONALSOURCEDATE dbo.UDT_FUZZYDATE = null output,
    @COMMENT nvarchar(500) = null output,
    @AFFILIATEDADDITIONALINFORMATION xml = null output,
    @UNAFFILIATEDADDITIONALINFORMATION xml = null output,
    @EDUCATIONALINVOLVEMENTS xml = null output,
    @USEACADEMICCATALOG bit = null output,
    @EDUCATIONALHISTORYSTATUSID uniqueidentifier = null output
) as
    set nocount on;

    declare @CONSTITUENTID uniqueidentifier;

    select
        @DATALOADED = 1,
        @CONSTITUENTID = C.[ID],
        @CONSTITUENTLOOKUPID = C.[LOOKUPID],
        @CONSTITUENTKEYNAME = C.[KEYNAME],
        @CONSTITUENTFIRSTNAME = C.[FIRSTNAME],
        @EDUCATIONALINSTITUTIONID = EI.[ID],
        @EDUCATIONALINSTITUTIONNAME = EI.[NAME],
        @EDUCATIONALINSTITUTIONFICECODE = EI.[FICECODE],
        @EDUCATIONALINSTITUTIONCOUNTRYID = EI.[COUNTRYID],
        @EDUCATIONALINSTITUTIONCITY = EI.[CITY],
        @EDUCATIONALINSTITUTIONSTATEID = EI.[STATEID],
        @ISPRIMARYRECORD = EH.[ISPRIMARYRECORD],
        @ACADEMICCATALOGPROGRAMID = EH.[ACADEMICCATALOGPROGRAMID],
        @EDUCATIONALPROGRAMCODEID = EH.[EDUCATIONALPROGRAMCODEID],
        @CONSTITUENCYSTATUSCODE = EH.[CONSTITUENCYSTATUSCODE],
        @EDUCATIONALHISTORYREASONCODEID = EH.[EDUCATIONALHISTORYREASONCODEID],
        @EDUCATIONALHISTORYLEVELCODEID = EH.[EDUCATIONALHISTORYLEVELCODEID],
        @DATELEFT = EH.[DATELEFT],
        @DATEGRADUATED = EH.[DATEGRADUATED],
        @ACADEMICCATALOGDEGREEID = EH.[ACADEMICCATALOGDEGREEID],
        @EDUCATIONALDEGREECODEID = EH.[EDUCATIONALDEGREECODEID],
        @EDUCATIONALAWARDCODEID = EH.[EDUCATIONALAWARDCODEID],
        @STARTDATE = EH.[STARTDATE],
        @CLASSYEAR = EH.[CLASSOF],
        @PREFERREDCLASSYEAR = EH.[PREFERREDCLASSYEAR],
        @EDUCATIONALSOURCECODEID = EH.[EDUCATIONALSOURCECODEID],
        @EDUCATIONALSOURCEDATE = EH.[EDUCATIONALSOURCEDATE],
        @COMMENT = EH.[COMMENT],
        @TSLONG = EH.[TSLONG],
        @EDUCATIONALHISTORYSTATUSID = EH.[EDUCATIONALHISTORYSTATUSID]
    from
        dbo.[EDUCATIONALHISTORY] EH
    left join
        dbo.[EDUCATIONALINSTITUTION] EI on EI.[ID] = EH.[EDUCATIONALINSTITUTIONID]
    left join
        dbo.[CONSTITUENT] C on C.[ID] = EH.[CONSTITUENTID]
    where
        EH.ID = @ID;

    set @AFFILIATEDADDITIONALINFORMATION = dbo.UFN_EDUCATIONALHISTORY_GETAFFILIATEDADDITIONALINFORMATION_FORUPDATEBATCH_TOITEMLISTXML(@ID);
    set @UNAFFILIATEDADDITIONALINFORMATION = dbo.UFN_EDUCATIONALHISTORY_GETUNAFFILIATEDADDITIONALINFORMATION_FORUPDATEBATCH_TOITEMLISTXML(@ID);    
    set @EDUCATIONALINVOLVEMENTS = dbo.UFN_CONSTITUENT_GETEDUCATIONALINVOLVEMENTS_FORUPDATEBATCH_TOITEMLISTXML(@CONSTITUENTID, @EDUCATIONALINSTITUTIONID);

    select top 1
        @USEACADEMICCATALOG = EC.USEACADEMICCATALOG
    from
        dbo.EDUCATIONALCONFIGURATION EC;

    set @USEACADEMICCATALOG = coalesce(@USEACADEMICCATALOG,0);

    return 0;