USP_DATAFORMTEMPLATE_VIEW_FACULTY

The load procedure used by the view dataform template "Faculty View Form"

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	IN	The input ID parameter used to load the fields defined on the form.
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
@DATALOADED	bit	INOUT	Output parameter indicating whether or not data was actually loaded.
@NAME	nvarchar(400)	INOUT	Name
@ISINACTIVE	bit	INOUT	ISINACTIVE
@DECEASED	bit	INOUT	DECEASED
@ISORG	bit	INOUT	ISORG
@ISGROUP	bit	INOUT	ISGROUP
@DECEASINGOPTIONSSET	bit	INOUT	DECEASINGOPTIONSSET
@DECEASEDDATE	UDT_FUZZYDATE	INOUT	DECEASEDDATE
@ADDRESS	nvarchar(300)	INOUT	ADDRESS
@PHONENUMBER	nvarchar(100)	INOUT	PHONENUMBER
@PHONETYPE	nvarchar(100)	INOUT	PHONETYPE
@EMAILADDRESS	UDT_EMAILADDRESS	INOUT	EMAILADDRESS
@DONOTMAIL	bit	INOUT	DONOTMAIL
@DONOTEMAIL	bit	INOUT	DONOTEMAIL
@DONOTPHONE	bit	INOUT	DONOTPHONE
@WEBADDRESS	UDT_WEBADDRESS	INOUT	WEBADDRESS
@PICTURE	varbinary	INOUT	PICTURE
@LOOKUPID	nvarchar(100)	INOUT	Lookup ID
@CONSTITUENTID	uniqueidentifier	INOUT	CONSTITUENTID
@ISSTUDENT	bit	INOUT	ISSTUDENT
@ATTRIBUTEDEFINED	bit	INOUT	ATTRIBUTEDEFINED
@STUDENTCONSTITUENCYTEXT	nvarchar(100)	INOUT	STUDENTCONSTITUENCYTEXT
@PRIMARYEDUCATION	nvarchar(100)	INOUT	Primary education
@PRIMARYEDUCATIONID	uniqueidentifier	INOUT	PRIMARYEDUCATIONID
@CURRENTSCHOOL	nvarchar(100)	INOUT	School
@CURRENTENROLLMENTID	uniqueidentifier	INOUT	CURRENTENROLLMENTID
@CURRENTSCHOOL2	nvarchar(100)	INOUT	CURRENTSCHOOL2
@CURRENTENROLLMENTID2	uniqueidentifier	INOUT	CURRENTENROLLMENTID2
@CURRENTSCHOOL3	nvarchar(100)	INOUT	CURRENTSCHOOL3
@CURRENTENROLLMENTID3	uniqueidentifier	INOUT	CURRENTENROLLMENTID3
@RELATEDCONSTITUENT	nvarchar(154)	INOUT	Spouse
@RELATEDCONSTITUENTID	uniqueidentifier	INOUT	RELATEDCONSTITUENTID
@PRIMARYBUSINESS	nvarchar(100)	INOUT	Primary business
@PRIMARYBUSINESSID	uniqueidentifier	INOUT	PRIMARYBUSINESSID
@ISSPOUSEDECEASED	bit	INOUT	Is spouse deceased
@CONSTITUENTINACTIVITYREASON	nvarchar(63)	INOUT	Inactive reason
@PHONEISCONFIDENTIAL	bit	INOUT	PHONEISCONFIDENTIAL
@ADDRESSISCONFIDENTIAL	bit	INOUT	ADDRESSISCONFIDENTIAL
@ADDRESSID	uniqueidentifier	INOUT	ADDRESSID
@PHONENUMBERID	uniqueidentifier	INOUT	PHONENUMBERID
@EMAILADDRESSID	uniqueidentifier	INOUT	EMAILADDRESSID
@HASRECEIVEDFUNDS	bit	INOUT	HASRECEIVEDFUNDS

Definition

Copy



CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_FACULTY 
(
    @ID uniqueidentifier,
    @CURRENTAPPUSERID uniqueidentifier,
    @DATALOADED bit = 0 output,
    @NAME nvarchar(400) = null output,
    @ISINACTIVE bit = null output,
    @DECEASED bit = null output,
    @ISORG bit = null output,
    @ISGROUP bit = null output,
    @DECEASINGOPTIONSSET bit = null output,    
    @DECEASEDDATE dbo.UDT_FUZZYDATE = null output,
    @ADDRESS nvarchar(300) = null output,
    @PHONENUMBER nvarchar(100) = null output,
    @PHONETYPE nvarchar(100) = null output,
    @EMAILADDRESS dbo.UDT_EMAILADDRESS = null output,
    @DONOTMAIL bit = null output,
    @DONOTEMAIL bit = null output,
    @DONOTPHONE bit = null output,
    @WEBADDRESS dbo.UDT_WEBADDRESS = null output,
    @PICTURE varbinary(max) = null output,
    @LOOKUPID nvarchar(100) = null output,
    @CONSTITUENTID uniqueidentifier = null output,
    @ISSTUDENT bit = null output,
    @ATTRIBUTEDEFINED bit = null output,
    @STUDENTCONSTITUENCYTEXT nvarchar(100) = null output,
    @PRIMARYEDUCATION nvarchar(100) = null output,
    @PRIMARYEDUCATIONID uniqueidentifier = null output,
    @CURRENTSCHOOL nvarchar(100) = null output,
    @CURRENTENROLLMENTID uniqueidentifier = null output,
    @CURRENTSCHOOL2 nvarchar(100) = null output,
    @CURRENTENROLLMENTID2 uniqueidentifier = null output,
    @CURRENTSCHOOL3 nvarchar(100) = null output,
    @CURRENTENROLLMENTID3 uniqueidentifier = null output,
    @RELATEDCONSTITUENT nvarchar(154) = null output,
    @RELATEDCONSTITUENTID uniqueidentifier = null output,
    @PRIMARYBUSINESS nvarchar(100) = null output,
    @PRIMARYBUSINESSID uniqueidentifier = null output,
    @ISSPOUSEDECEASED bit = null output,
    @CONSTITUENTINACTIVITYREASON nvarchar(63) = null output,
    @PHONEISCONFIDENTIAL bit = null output,
    @ADDRESSISCONFIDENTIAL bit = null output,
    @ADDRESSID uniqueidentifier = null output,
    @PHONENUMBERID uniqueidentifier = null output,
    @EMAILADDRESSID uniqueidentifier = null output,
    @HASRECEIVEDFUNDS bit = null output
)
as
    set nocount on;

    -- be sure to set this, in case the select returns no rows

    set @DATALOADED = 0;

    if exists (select * from dbo.FACULTY where ID = @ID) 
    begin
        -- populate the output parameters, which correspond to fields on the form.  Note that

        -- we set @DATALOADED = 1 to indicate that the load was successful.  Otherwise, the system

        -- will display a "no data loaded" message.

        exec dbo.USP_CONSTITUENTGETEXPRESSIONDATA @ID, @CURRENTAPPUSERID output, @DATALOADED output, @NAME = @NAME output, @ISINACTIVE = @ISINACTIVE output, @DECEASED = @DECEASED output, @ISORG = @ISORG output, @ISGROUP = @ISGROUP output, @DECEASINGOPTIONSSET = @DECEASINGOPTIONSSET output, @CONSTITUENTID = @CONSTITUENTID output, @ISSTUDENT = @ISSTUDENT output, @ATTRIBUTEDEFINED = @ATTRIBUTEDEFINED output;

        if @ISINACTIVE = 1
            select @CONSTITUENTINACTIVITYREASON = dbo.UFN_CONSTITUENTINACTIVITYREASONCODE_TRANSLATE(CONSTITUENTINACTIVEDETAIL.CONSTITUENTINACTIVITYREASONCODEID)
            from dbo.CONSTITUENTINACTIVEDETAIL
            where ID = @ID

        --CONSTIT FIELDS

        select
            @DATALOADED = 1,
            @WEBADDRESS = CONSTITUENT.WEBADDRESS,
            @PICTURE = CONSTITUENT.PICTURETHUMBNAIL,
            @LOOKUPID = CONSTITUENT.LOOKUPID
        from
            dbo.CONSTITUENT
        where
            CONSTITUENT.ID = @ID;

        --EMAIL

        SELECT 
            @EMAILADDRESS = EMAILADDRESS,
            @DONOTEMAIL = EMAILADDRESS.DONOTEMAIL,
            @EMAILADDRESSID = EMAILADDRESS.ID
        FROM dbo.EMAILADDRESS
        WHERE
            EMAILADDRESS.CONSTITUENTID = @ID and
            EMAILADDRESS.ISPRIMARY = 1;

        --PHONE        

        SELECT
            @PHONENUMBER = dbo.UFN_PHONE_GETINTERNATIONALNUMBER(PHONE.COUNTRYID, PHONE.NUMBER),
            @PHONETYPE = (select DESCRIPTION FROM dbo.PHONETYPECODE WHERE ID = PHONE.PHONETYPECODEID),
            @DONOTPHONE = PHONE.DONOTCALL,
            @PHONEISCONFIDENTIAL = PHONE.ISCONFIDENTIAL,
            @PHONENUMBERID = PHONE.ID
        FROM dbo.PHONE
        WHERE 
            PHONE.CONSTITUENTID = @ID and
            PHONE.ISPRIMARY = 1;

        --ADDRESS

        select 
            @ADDRESS = dbo.UFN_BUILDFULLADDRESS(ADDRESS.ID, ADDRESS.ADDRESSBLOCK, ADDRESS.CITY, ADDRESS.STATEID, ADDRESS.POSTCODE, ADDRESS.COUNTRYID),
            @DONOTMAIL = ADDRESS.DONOTMAIL,
            @ADDRESSISCONFIDENTIAL = ADDRESS.ISCONFIDENTIAL,
            @ADDRESSID = ADDRESS.ID       
        from
            dbo.ADDRESS
        where
            ADDRESS.CONSTITUENTID = @ID and
            ADDRESS.ISPRIMARY = 1;

        --DECEASED

        SELECT 
        @DECEASEDDATE=DECEASEDDATE
        FROM
        dbo.DECEASEDCONSTITUENT
        WHERE ID=@ID;

        --STUDENT CONSTITUENCY

        set @STUDENTCONSTITUENCYTEXT=case
                            when dbo.UFN_CONSTITUENT_ISSTUDENT(@ID) = 1 then dbo.UFN_CONSTITUENCY_GETDESCRIPTION('4DB8F4FC-BC43-421D-B592-69BEF109B5FC') --Student

                            else null end;

        --PRIMARY EDUCATION FIELDS

        select
            @PRIMARYEDUCATIONID = EDUCATIONALHISTORY.ID,
            @PRIMARYEDUCATION = EDUCATIONALINSTITUTION.NAME
        from dbo.EDUCATIONALHISTORY 
        inner join dbo.EDUCATIONALINSTITUTION on EDUCATIONALINSTITUTION.ID = EDUCATIONALHISTORY.EDUCATIONALINSTITUTIONID
        where EDUCATIONALHISTORY.ISPRIMARYRECORD = 1 and EDUCATIONALHISTORY.CONSTITUENTID = @ID;

        --CURRENT SCHOOL INFORMATION

        declare @CURR_INFO TABLE
        (
         ENROLLMENTID    uniqueidentifier,
         SCHOOLID   uniqueidentifier,
         SCHOOL nvarchar(100),
         ROW tinyint
        )

        insert into @CURR_INFO 
        select [ENROLLMENTID], [SCHOOLID], [SCHOOL], [ROW]
        from dbo.UFN_STUDENT_CURRENTENROLLMENTINFO(@ID, 1) 

        select
            @CURRENTENROLLMENTID = CURR_INFO.[ENROLLMENTID],
            @CURRENTSCHOOL = CURR_INFO.[SCHOOL]
        from
            @CURR_INFO as CURR_INFO
        where 
            CURR_INFO.[ROW] = 1

        select 
            @CURRENTENROLLMENTID2 = CURR_INFO.[ENROLLMENTID],
            @CURRENTSCHOOL2 = CURR_INFO.[SCHOOL]
        from
            @CURR_INFO as CURR_INFO
        where 
            (CURR_INFO.[ROW] = 2) and (CURR_INFO.[SCHOOLID] <>  @CURRENTENROLLMENTID)

        select 
            @CURRENTENROLLMENTID3 = CURR_INFO.[ENROLLMENTID],
            @CURRENTSCHOOL3 = CURR_INFO.[SCHOOL]
        from
            @CURR_INFO as CURR_INFO
        where 
            (CURR_INFO.[ROW] = 3) and (CURR_INFO.[SCHOOLID] NOT IN (@CURRENTENROLLMENTID, @CURRENTENROLLMENTID2)) 

        select
            @RELATEDCONSTITUENT = SPOUSE.NAME,
            @RELATEDCONSTITUENTID = SPOUSE.ID,
            @ISSPOUSEDECEASED = case when dbo.DECEASEDCONSTITUENT.ID is null then 0 else 1 end
        from
            dbo.RELATIONSHIP
        inner join
            dbo.CONSTITUENT AS SPOUSE on SPOUSE.ID = RELATIONSHIP.RECIPROCALCONSTITUENTID    
        left join
            dbo.DECEASEDCONSTITUENT on SPOUSE.ID = dbo.DECEASEDCONSTITUENT.ID
        where 
            RELATIONSHIP.RELATIONSHIPCONSTITUENTID = @ID and
            RELATIONSHIP.ISSPOUSE = 1    

        -- primary business, if available

        select
            @PRIMARYBUSINESSID = [ORG].ID,
            @PRIMARYBUSINESS = [ORG].KEYNAME
        from
            dbo.RELATIONSHIP
            inner join dbo.CONSTITUENT as [ORG] on [ORG].ID = RELATIONSHIP.RECIPROCALCONSTITUENTID
        where 
            RELATIONSHIP.RELATIONSHIPCONSTITUENTID = @ID
            and    RELATIONSHIP.ISPRIMARYBUSINESS = 1;

        set @HASRECEIVEDFUNDS = dbo.UFN_CONSTITUENT_HASRECEIVEDFUNDS(@ID)

    end        
    return 0;