USP_DATAFORMTEMPLATE_VIEW_INDIVIDUALPROFILE

The load procedure used by the view dataform template "Constituent Summary Profile 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.
@ISCONSTITUENTPROFILE	bit	INOUT	ISCONSTITUENTPROFILE
@ISORGANIZATION	bit	INOUT	ISORGANIZATION
@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
@RELATEDCONSTITUENT	nvarchar(154)	INOUT	RELATEDCONSTITUENT
@RELATEDCONSTITUENTID	uniqueidentifier	INOUT	RELATEDCONSTITUENTID
@BOARDMEMBERCONSTITUENCYTEXT	nvarchar(100)	INOUT	BOARDMEMBERCONSTITUENCYTEXT
@RELATIONCONSTITUENCYTEXT	nvarchar(100)	INOUT	RELATIONCONSTITUENCYTEXT
@STAFFCONSTITUENCYTEXT	nvarchar(100)	INOUT	STAFFCONSTITUENCYTEXT
@DONORCONSTITUENCYTEXT	nvarchar(100)	INOUT	DONORCONSTITUENCYTEXT
@FUNDRAISERCONSTITUENCYTEXT	nvarchar(100)	INOUT	FUNDRAISERCONSTITUENCYTEXT
@PROSPECTCONSTITUENCYTEXT	nvarchar(100)	INOUT	PROSPECTCONSTITUENCYTEXT
@VOLUNTEERCONSTITUENCYTEXT	nvarchar(100)	INOUT	VOLUNTEERCONSTITUENCYTEXT
@COMMUNITYMEMBERCONSTITUENCYTEXT	nvarchar(100)	INOUT	COMMUNITYMEMBERCONSTITUENCYTEXT
@USERDEFINEDCONSTITUENCYTEXT	nvarchar(4000)	INOUT	USERDEFINEDCONSTITUENCYTEXT
@ALUMNUSSTATUSTEXT	nvarchar(100)	INOUT	ALUMNUSSTATUSTEXT
@REGISTRANTSTATUSTEXT	nvarchar(100)	INOUT	REGISTRANTSTATUSTEXT
@VENDORSTATUSTEXT	nvarchar(100)	INOUT	VENDORSTATUSTEXT
@ISINACTIVE	bit	INOUT	ISINACTIVE
@DECEASEDDATE	UDT_FUZZYDATE	INOUT	DECEASEDDATE
@PICTURE	varbinary	INOUT	PICTURE
@PRIMARYEDUCATION	nvarchar(100)	INOUT	Primary education
@PRIMARYBUSINESS	nvarchar(100)	INOUT	Primary business
@PRIMARYBUSINESSID	uniqueidentifier	INOUT	PRIMARYBUSINESSID
@LOOKUPID	nvarchar(100)	INOUT	Lookup ID
@WEALTHPOINTUPDATEPENDING	bit	INOUT	WEALTHPOINTUPDATEPENDING
@PRIMARYEDUCATIONID	uniqueidentifier	INOUT	PRIMARYEDUCATIONID
@EDUCATIONATTRIBUTEDEFINED	bit	INOUT	EDUCATIONATTRIBUTEDEFINED
@MATCHFINDERCONSTITUENCYTEXT	nvarchar(100)	INOUT	MATCHFINDERCONSTITUENCYTEXT
@MATCHFINDERONLINERECORDID	int	INOUT	MATCHFINDERONLINERECORDID
@SOLICITCODECOUNT	int	INOUT	SOLICITCODECOUNT
@HOUSEHOLDTEXT	nvarchar(154)	INOUT	Household
@HOUSEHOLDID	uniqueidentifier	INOUT	HOUSEHOLDID
@ISGROUP	bit	INOUT	ISGROUP
@GROUPTYPE	nvarchar(300)	INOUT	Group type
@GROUPMEMBERCOUNT	int	INOUT	No. of members
@ISHOUSEHOLD	bit	INOUT	Is Household
@GIVESANONYMOUSLY	bit	INOUT	Gives anonymously
@MEMBERCONSTITUENCYTEXT	nvarchar(100)	INOUT	MEMBERCONSTITUENCYTEXT
@RECOGNITIONCONSTITUENCYTEXT	nvarchar(100)	INOUT	RECOGNITIONCONSTITUENCYTEXT
@ISSPOUSEDECEASED	bit	INOUT	Is spouse deceased
@ISDISSOLVED	bit	INOUT	Is group dissolved
@PLANNEDGIVERCONSTITUENCYTEXT	nvarchar(100)	INOUT	PLANNEDGIVERCONSTITUENCYTEXT
@BANKCONSTITUENCYTEXT	nvarchar(100)	INOUT	BANKCONSTITUENCYTEXT
@DECLARATIONSONFILE	bit	INOUT	DECLARATIONSONFILE
@PATRONCONSTITUENCYTEXT	nvarchar(100)	INOUT	PATRONCONSTITUENCYTEXT
@COMMITTEECONSTITUENCYTEXT	nvarchar(100)	INOUT	COMMITTEECONSTITUENCYTEXT
@ISCOMMITTEEMEMBER	bit	INOUT	ISCOMMITTEEMEMBER
@GRANTORCONSTITUENCYTEXT	nvarchar(100)	INOUT	GRANTORCONSTITUENCYTEXT
@SPONSORCONSTITUENCYTEXT	nvarchar(100)	INOUT	SPONSORCONSTITUENCYTEXT
@CONSTITUENTINACTIVITYREASON	nvarchar(63)	INOUT	Inactive reason
@COMMITTEEMEMBERCONSTITUENCYTEXT	nvarchar(100)	INOUT	COMMITTEEMEMBERCONSTITUENCYTEXT
@STUDENTCONSTITUENCYTEXT	nvarchar(100)	INOUT	STUDENTCONSTITUENCYTEXT
@FACULTYCONSTITUENCYTEXT	nvarchar(100)	INOUT	FACULTYCONSTITUENCYTEXT
@ISDECEASED	bit	INOUT	ISDECEASED
@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
@STUDENTENROLLMENTID	uniqueidentifier	INOUT	STUDENTENROLLMENTID
@ALUMNUSCONSTITUENCYTEXT	nvarchar(100)	INOUT	ALUMNUSCONSTITUENCYTEXT
@ALUMNUSENROLLMENTID	uniqueidentifier	INOUT	ALUMNUSENROLLMENTID
@LOYALDONORCONSTITUENCYTEXT	nvarchar(100)	INOUT	LOYALDONORCONSTITUENCYTEXT
@MAJORDONORCONSTITUENCYTEXT	nvarchar(100)	INOUT	MAJORDONORCONSTITUENCYTEXT
@STUDENTRELATIONCONSTITUENCYTEXT	nvarchar(4000)	INOUT	STUDENTRELATIONCONSTITUENCYTEXT
@PHONEISCONFIDENTIAL	bit	INOUT	PHONEISCONFIDENTIAL
@ADDRESSISCONFIDENTIAL	bit	INOUT	ADDRESSISCONFIDENTIAL
@CONSTITUENCIESDISPLAYORDER	xml	INOUT	CONSTITUENCIESDISPLAYORDER
@SPONSORTYPECODE	tinyint	INOUT	SPONSORTYPECODE
@LIFECYCLESTAGE	nvarchar(1000)	INOUT	Donor lifecycle
@LIFECYCLESTAGEASOF	datetime	INOUT	as of
@PLANNEDGIVERSTAGE	nvarchar(15)	INOUT	PLANNEDGIVERSTAGE
@PLANNEDGIVERSTAGEASOF	datetime	INOUT	as of
@DONORSTATECODE	tinyint	INOUT	DONORSTATECODE
@DONORSTATE	nvarchar(250)	INOUT	Donor state
@LASTREVENUEDATE	datetime	INOUT	LASTREVENUEDATE
@ADDRESSID	uniqueidentifier	INOUT	ADDRESSID
@PHONENUMBERID	uniqueidentifier	INOUT	PHONENUMBERID
@EMAILADDRESSID	uniqueidentifier	INOUT	EMAILADDRESSID
@ADVOCATECONSTITUENCYTEXT	nvarchar(100)	INOUT	ADVOCATECONSTITUENCYTEXT
@TOPPARENT	nvarchar(154)	INOUT	Top parent organization
@TOPPARENTID	uniqueidentifier	INOUT	TOPPARENTID
@USERDEFINEDCONSTITUENCIES	xml	INOUT	USERDEFINEDCONSTITUENCIES
@STUDENTRELATIONCONSTITUENCIES	xml	INOUT	STUDENTRELATIONCONSTITUENCIES
@USER_GRANTED_CONSTITPERSONALINFO_EDIT	bit	INOUT	USER_GRANTED_CONSTITPERSONALINFO_EDIT
@NAME	nvarchar(255)	INOUT
@NFGCONSTITUENCYTEXT	nvarchar(100)	INOUT
@FUNDRAISINGGROUPCONSTITUENCYTEXT	nvarchar(100)	INOUT
@SOCIALMEDIAACCOUNTS	xml	INOUT

Definition

Copy



            CREATE  procedure [dbo].[USP_DATAFORMTEMPLATE_VIEW_INDIVIDUALPROFILE]

                @ID uniqueidentifier,
                @CURRENTAPPUSERID uniqueidentifier=null,
                @DATALOADED bit = 0 output,
                @ISCONSTITUENTPROFILE bit = null output,
                @ISORGANIZATION bit = 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,
                @RELATEDCONSTITUENT nvarchar(154) = null output,
                @RELATEDCONSTITUENTID uniqueidentifier = null output,
                @BOARDMEMBERCONSTITUENCYTEXT nvarchar(100) = null output,
                @RELATIONCONSTITUENCYTEXT nvarchar(100) = null output,
                @STAFFCONSTITUENCYTEXT nvarchar(100) = null output,
                @DONORCONSTITUENCYTEXT nvarchar(100) = null output,
                @FUNDRAISERCONSTITUENCYTEXT nvarchar(100) = null output,
                @PROSPECTCONSTITUENCYTEXT nvarchar(100) = null output,
                @VOLUNTEERCONSTITUENCYTEXT nvarchar(100) = null output,
                @COMMUNITYMEMBERCONSTITUENCYTEXT nvarchar(100) = null output,
                @USERDEFINEDCONSTITUENCYTEXT nvarchar(4000) = null output,
                @ALUMNUSSTATUSTEXT nvarchar(100) = null output,
                @REGISTRANTSTATUSTEXT nvarchar(100) = null output,
                @VENDORSTATUSTEXT nvarchar(100) = null output,
                @ISINACTIVE bit = null output,
                @DECEASEDDATE dbo.UDT_FUZZYDATE = null output,
                @PICTURE varbinary(max) = null output,
                @PRIMARYEDUCATION nvarchar(100) = null output,
                @PRIMARYBUSINESS nvarchar(100) = null output,
                @PRIMARYBUSINESSID uniqueidentifier = null output,
                @LOOKUPID nvarchar(100) = null output,
                @WEALTHPOINTUPDATEPENDING bit = null output,
                @PRIMARYEDUCATIONID uniqueidentifier = null output,
                @EDUCATIONATTRIBUTEDEFINED bit = null output,
                @MATCHFINDERCONSTITUENCYTEXT nvarchar(100) = null output,
                @MATCHFINDERONLINERECORDID int = null output,
                @SOLICITCODECOUNT int = null output,
                @HOUSEHOLDTEXT nvarchar(154) = null output,
                @HOUSEHOLDID uniqueidentifier = null output,
                @ISGROUP bit = null output,
                @GROUPTYPE nvarchar(300) = null output,
                @GROUPMEMBERCOUNT integer = null output,
                @ISHOUSEHOLD bit = null output,
                @GIVESANONYMOUSLY bit = null output,
                @MEMBERCONSTITUENCYTEXT nvarchar(100) = null output,
                @RECOGNITIONCONSTITUENCYTEXT nvarchar(100) = null output,
                @ISSPOUSEDECEASED bit = null output,
                @ISDISSOLVED bit = null output,
                @PLANNEDGIVERCONSTITUENCYTEXT nvarchar(100)=null output,
                @BANKCONSTITUENCYTEXT nvarchar(100) = null output,
                @DECLARATIONSONFILE bit = null output,
                @PATRONCONSTITUENCYTEXT nvarchar(100) = null output,
                @COMMITTEECONSTITUENCYTEXT nvarchar(100) = null output,
                @ISCOMMITTEEMEMBER bit = null output,
                @GRANTORCONSTITUENCYTEXT nvarchar(100) = null output,
                @SPONSORCONSTITUENCYTEXT nvarchar(100) = null output,
                @CONSTITUENTINACTIVITYREASON nvarchar(63) = null output,
                @COMMITTEEMEMBERCONSTITUENCYTEXT nvarchar(100) = null output,
                @STUDENTCONSTITUENCYTEXT nvarchar(100) = null output,
                @FACULTYCONSTITUENCYTEXT nvarchar(100) = null output,
                @ISDECEASED bit = 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,
                @STUDENTENROLLMENTID uniqueidentifier = null output,
                @ALUMNUSCONSTITUENCYTEXT nvarchar(100) = null output,
                @ALUMNUSENROLLMENTID uniqueidentifier = null output,
                @LOYALDONORCONSTITUENCYTEXT nvarchar(100) = null output,
                @MAJORDONORCONSTITUENCYTEXT nvarchar(100) = null output,
                @STUDENTRELATIONCONSTITUENCYTEXT nvarchar(4000) = null output,
                @PHONEISCONFIDENTIAL bit = null output,
                @ADDRESSISCONFIDENTIAL bit = null output,
                @CONSTITUENCIESDISPLAYORDER xml = null output,
                @SPONSORTYPECODE tinyint = null output,
                @LIFECYCLESTAGE nvarchar(1000) = null output,
                @LIFECYCLESTAGEASOF datetime = null output,
                @PLANNEDGIVERSTAGE nvarchar(15) = null output,
                @PLANNEDGIVERSTAGEASOF datetime = null output,
                @DONORSTATECODE tinyint = null output,
                @DONORSTATE nvarchar(250) = null output,
                @LASTREVENUEDATE datetime = null output,
                @ADDRESSID uniqueidentifier = null output,
                @PHONENUMBERID uniqueidentifier = null output,
                @EMAILADDRESSID uniqueidentifier = null output,
                @ADVOCATECONSTITUENCYTEXT nvarchar(100) = null output,
                @TOPPARENT nvarchar(154) = null output,
                @TOPPARENTID uniqueidentifier = null output,
                @USERDEFINEDCONSTITUENCIES xml = null output,
                @STUDENTRELATIONCONSTITUENCIES xml = null output,
                @USER_GRANTED_CONSTITPERSONALINFO_EDIT bit = null output,
                @NAME nvarchar(255) = null output,
                @NFGCONSTITUENCYTEXT nvarchar(100) = null output,
                @FUNDRAISINGGROUPCONSTITUENCYTEXT nvarchar(100) = null output,
                @SOCIALMEDIAACCOUNTS xml = null output
            as
                set nocount on;

                set @DATALOADED = 0;
                set @MATCHFINDERONLINERECORDID = 0;


                --CONSTIT FIELDS

                select
                    @DATALOADED = 1,
                    @ISORGANIZATION = CONSTITUENT.ISORGANIZATION,
                    @ISGROUP = CONSTITUENT.ISGROUP,
                    @WEBADDRESS = CONSTITUENT.WEBADDRESS,
                    @PICTURE = CONSTITUENT.PICTURETHUMBNAIL,
                    @ISINACTIVE = CONSTITUENT.ISINACTIVE,
                    @LOOKUPID = CONSTITUENT.LOOKUPID,
                    @ISCONSTITUENTPROFILE = ISCONSTITUENT,
                    @GIVESANONYMOUSLY = CONSTITUENT.GIVESANONYMOUSLY,
                    @NAME = dbo.UFN_CONSTITUENT_BUILDNAME(@ID)
                from
                    dbo.CONSTITUENT
                where
                    CONSTITUENT.ID = @ID;

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

                if @ISCONSTITUENTPROFILE <> 0 
                    set @CONSTITUENCIESDISPLAYORDER = 
                        (select 
                            ID,
                            DESCRIPTION,
                            SEQUENCE,
      ISSYSTEM
                        from 
                            dbo.V_CONSTITUENCYDISPLAYORDER
                        order by 
                            SEQUENCE, DESCRIPTION
                        for xml raw('ITEM'),type,elements,root('CONSTITUENCIESDISPLAYORDER'),BINARY BASE64)


      --CONSTIT GROUP FIELDS

      select 
        @GROUPTYPE = coalesce( (select GT.NAME from dbo.GROUPTYPE GT where GT.ID = G.GROUPTYPEID), 'Household'),
        @ISHOUSEHOLD = case when G.GROUPTYPECODE = 0 then 1 else 0 end,
        @ISDISSOLVED = case when DG.ID is null then 0 else 1 end
      from dbo.GROUPDATA G
      left join dbo.DISSOLVEDGROUP DG
      on DG.ID = G.ID
      where G.ID = @ID;

      declare @CURRENTDATE date;
      set @CURRENTDATE = dbo.UFN_DATE_GETEARLIESTTIME(getdate());

      select 
        @GROUPMEMBERCOUNT = count(GM.ID)
      from
        dbo.GROUPMEMBER GM
      left join dbo.GROUPMEMBERDATERANGE as GMDR on GM.ID = GMDR.GROUPMEMBERID
      where GM.GROUPID = @ID
      and ((GMDR.DATEFROM is null and (GMDR.DATETO is null or GMDR.DATETO > @CURRENTDATE))
          or (GMDR.DATETO is null and (GMDR.DATEFROM is null or GMDR.DATEFROM <= @CURRENTDATE)) 
          or (GMDR.DATEFROM <= @CURRENTDATE and GMDR.DATETO > @CURRENTDATE))

            --PRIMARY BUSINESS RELATIONSHIP FIELDS

                if @ISORGANIZATION = 0 and @ISGROUP = 0 -- JohnLu 2007/08/20 Make sure the constituent is not a group

                    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 ;

            --PRIMARY EDUCATION FIELDS

                if @ISORGANIZATION = 0 and @ISGROUP = 0 -- JohnLu 2007/08/20 Make sure the constituent is not a group

                    select
                        @PRIMARYEDUCATIONID = EDUCATIONALHISTORY.ID,
                        @PRIMARYEDUCATION = EDUCATIONALINSTITUTION.NAME,
                        @EDUCATIONATTRIBUTEDEFINED = dbo.UFN_ATTRIBUTECATEGORY_DEFINEDFORRECORDTYPE_FORAPPUSER('EDUCATIONAL HISTORY',@CURRENTAPPUSERID)
                    from dbo.EDUCATIONALHISTORY 
                    inner join dbo.EDUCATIONALINSTITUTION on EDUCATIONALINSTITUTION.ID = EDUCATIONALHISTORY.EDUCATIONALINSTITUTIONID
                    where EDUCATIONALHISTORY.ISPRIMARYRECORD = 1 and EDUCATIONALHISTORY.CONSTITUENTID = @ID;

            --RELATIONSHIP FIELDS FOR RELATED CONSTIT LABEL

                if @ISORGANIZATION =1
                    BEGIN

                        SELECT 
                        @RELATEDCONSTITUENT =PARENTORG.[NAME],
                        @RELATEDCONSTITUENTID=PARENTORG.ID
                        FROM 
                        dbo.ORGANIZATIONDATA AS OD
                        inner join
                        dbo.CONSTITUENT AS PARENTORG
                        on
                        PARENTORG.ID = OD.PARENTCORPID
                        WHERE OD.ID=@ID;

                    END
                ELSE
                    BEGIN
                        if @ISGROUP = 0 -- JohnLu 2007/08/20 Make sure the constituent is not a group

                        begin
                            SELECT
                            @RELATEDCONSTITUENT = NF.[NAME] ,
                            @RELATEDCONSTITUENTID = RELATIONSHIP.RECIPROCALCONSTITUENTID,
              @ISSPOUSEDECEASED = case when dbo.DECEASEDCONSTITUENT.ID is null then 0 else 1 end
                            FROM dbo.RELATIONSHIP
                                outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(RELATIONSHIP.RECIPROCALCONSTITUENTID) NF
              LEFT JOIN
              dbo.DECEASEDCONSTITUENT
              ON
              RELATIONSHIP.RECIPROCALCONSTITUENTID = dbo.DECEASEDCONSTITUENT.ID
                            WHERE 
                            RELATIONSHIP.RELATIONSHIPCONSTITUENTID = @ID and
                            RELATIONSHIP.ISSPOUSE = 1;
                        end
                    END

                --DECEASED

                SET @ISDECEASED = 0;

                SELECT 
                @DECEASEDDATE=DECEASEDDATE,
                @ISDECEASED = 1
                FROM
                dbo.DECEASEDCONSTITUENT
                WHERE ID=@ID;

                --EMAIL

                SELECT 
                  @EMAILADDRESS = EMAILADDRESS,
                  @DONOTEMAIL = DONOTEMAIL,
                  @EMAILADDRESSID = 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),
                  @PHONEISCONFIDENTIAL = PHONE.ISCONFIDENTIAL,
                  @DONOTPHONE = PHONE.DONOTCALL,
                  @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),
                    @ADDRESSISCONFIDENTIAL = ADDRESS.ISCONFIDENTIAL,
                    @DONOTMAIL = ADDRESS.DONOTMAIL,
                    @ADDRESSID = ADDRESS.ID
                from
                    dbo.ADDRESS
                where
                    ADDRESS.CONSTITUENTID = @ID and
                    ADDRESS.ISPRIMARY = 1;

                select top 1
                    @MATCHFINDERONLINERECORDID = MATCHFINDERRECORDID 
                from dbo.MATCHFINDERCONSTITUENT
                where ID = @ID;

                --SOCIAL MEDIA ACCOUNTS

                select @SOCIALMEDIAACCOUNTS = dbo.UFN_CONSTITUENT_GETSOCIALMEDIAACCOUNTS_TOITEMLISTXML(@ID);

                --Constituencies


                set @RELATIONCONSTITUENCYTEXT = null;
                select top 1 @RELATIONCONSTITUENCYTEXT = DESCRIPTION 
                from dbo.UFN_CONSTITUENT_GETALLCONSTITUENCIES(@ID)
                where CONSTITUENCYCODEID = '8B6ECCFD-6829-4FBC-B092-58CA083F9322';


                set @PLANNEDGIVERCONSTITUENCYTEXT = null;
                if (select top 1 ISACTIVE from dbo.CONSTITUENCYDEFINITION where ID = 'CEE46FE7-3FBB-4DFE-97EB-BA67DD33C634') = 1
                begin
                    if dbo.UFN_CONSTITUENT_ISPLANNEDGIVER(@ID) = 1
                    begin
                        set @PLANNEDGIVERCONSTITUENCYTEXT = dbo.UFN_CONSTITUENCY_GETDESCRIPTION('CEE46FE7-3FBB-4DFE-97EB-BA67DD33C634')
                    end
                end

                set @RECOGNITIONCONSTITUENCYTEXT = null;
                if (select top 1 ISACTIVE from dbo.CONSTITUENCYDEFINITION where ID = '3dfac92e-78bd-4051-abdc-02c675deb8f6') = 1
                begin
                    if dbo.UFN_CONSTITUENT_ISRECOGNITION(@ID,@CURRENTAPPUSERID) = 1
                    begin
                        set @RECOGNITIONCONSTITUENCYTEXT = dbo.UFN_CONSTITUENCY_GETDESCRIPTION('3dfac92e-78bd-4051-abdc-02c675deb8f6')  --Recognition

                    end
                end

                set @STUDENTCONSTITUENCYTEXT = null;
                if (select top 1 ISACTIVE from dbo.CONSTITUENCYDEFINITION where ID = '4DB8F4FC-BC43-421D-B592-69BEF109B5FC') = 1
                begin
                    if dbo.UFN_CONSTITUENT_ISSTUDENT(@ID) = 1
                    begin
                        set @STUDENTCONSTITUENCYTEXT = dbo.UFN_CONSTITUENCY_GETDESCRIPTION('4DB8F4FC-BC43-421D-B592-69BEF109B5FC') --Student

                    end
                end

                set @ALUMNUSCONSTITUENCYTEXT = null;
                if (select top 1 ISACTIVE from dbo.CONSTITUENCYDEFINITION where ID = '46EC3424-BA54-4431-A7DC-C6CEBB3B4592') = 1
                begin
                    if dbo.UFN_CONSTITUENT_ISALUMNUS(@ID) = 1
                    begin
                        set @ALUMNUSCONSTITUENCYTEXT = dbo.UFN_CONSTITUENCY_GETDESCRIPTION('46EC3424-BA54-4431-A7DC-C6CEBB3B4592') --Alumnus

                    end
                end             

                set @FACULTYCONSTITUENCYTEXT = null;
                if (select top 1 ISACTIVE from dbo.CONSTITUENCYDEFINITION where ID = '08D55D6A-10C8-4a72-92A0-EF87033AD7B6') = 1
                begin
                    if dbo.UFN_CONSTITUENT_ISFACULTY(@ID) = 1
                    begin
                        set @FACULTYCONSTITUENCYTEXT = dbo.UFN_CONSTITUENCY_GETDESCRIPTION('08D55D6A-10C8-4a72-92A0-EF87033AD7B6') --Faculty

                    end
                end                         

                set @MEMBERCONSTITUENCYTEXT = null;
                if (select top 1 ISACTIVE from dbo.CONSTITUENCYDEFINITION where ID = '2d11326e-8f3b-4322-9797-57c1aacfa5df') = 1
                begin
                    if dbo.UFN_CONSTITUENT_ISMEMBER(@ID,@CURRENTAPPUSERID) = 1
                    begin
                        set @MEMBERCONSTITUENCYTEXT = dbo.UFN_CONSTITUENCY_GETDESCRIPTION('2d11326e-8f3b-4322-9797-57c1aacfa5df')  --Member

                    end
                end           

                set @BOARDMEMBERCONSTITUENCYTEXT = null;
                if (select top 1 ISACTIVE from dbo.CONSTITUENCYDEFINITION where ID = 'F828E957-5F5E-479A-8F23-2FFD6C7C68FF') = 1
                begin
                    if dbo.UFN_CONSTITUENT_ISBOARDMEMBER(@ID) = 1
                    begin
                        set @BOARDMEMBERCONSTITUENCYTEXT = dbo.UFN_CONSTITUENCY_GETDESCRIPTION('F828E957-5F5E-479A-8F23-2FFD6C7C68FF') --Board member

                    end
                end   

                set @ADVOCATECONSTITUENCYTEXT = null;
                if (select top 1 ISACTIVE from dbo.CONSTITUENCYDEFINITION where ID = 'F828E957-5F5E-479A-8F23-2FFD6C7C6899') = 1
                begin
                    if dbo.UFN_CONSTITUENT_ISADVOCATE(@ID) = 1
                    begin
                        set @ADVOCATECONSTITUENCYTEXT = dbo.UFN_CONSTITUENCY_GETDESCRIPTION('F828E957-5F5E-479A-8F23-2FFD6C7C6899') --Advocate

                    end
                end 

                set @STAFFCONSTITUENCYTEXT = null;
                if (select top 1 ISACTIVE from dbo.CONSTITUENCYDEFINITION where ID = '6093915E-ADE9-42BE-88AE-304731754467') = 1
                begin
                    if dbo.UFN_CONSTITUENT_ISSTAFF(@ID) = 1
                    begin
                        set @STAFFCONSTITUENCYTEXT = dbo.UFN_CONSTITUENCY_GETDESCRIPTION('6093915E-ADE9-42BE-88AE-304731754467') --Staff

                    end
                end 

                set @SPONSORTYPECODE = dbo.UFN_CONSTITUENT_SPONSORTYPECODE(@ID);
                set @SPONSORCONSTITUENCYTEXT = null;

                if @SPONSORTYPECODE = 0
                begin
                    if (select top 1 ISACTIVE from dbo.CONSTITUENCYDEFINITION where ID = '4D746A03-A0AB-45F3-A30B-1AD4F304E622') = 1
                    begin
                        set @SPONSORCONSTITUENCYTEXT = dbo.UFN_CONSTITUENCY_GETDESCRIPTION('4D746A03-A0AB-45F3-A30B-1AD4F304E622') --Sponsor

                    end 
                end

                if @SPONSORTYPECODE = 1
                begin
                    if (select top 1 ISACTIVE from dbo.CONSTITUENCYDEFINITION where ID = 'F89E03BC-E724-4e5d-943B-72D4D1E1E916') = 1
                    begin
                        set @SPONSORCONSTITUENCYTEXT = dbo.UFN_CONSTITUENCY_GETDESCRIPTION('F89E03BC-E724-4e5d-943B-72D4D1E1E916') --Sponsorship recipient

                    end

                end

                if @SPONSORTYPECODE = 2
                begin
                    if (select top 1 ISACTIVE from dbo.CONSTITUENCYDEFINITION where ID = '908E521C-B0A5-4832-B664-7D7B079D77C2') = 1
                    begin                        
                        set @SPONSORCONSTITUENCYTEXT = dbo.UFN_CONSTITUENCY_GETDESCRIPTION('908E521C-B0A5-4832-B664-7D7B079D77C2') --Sponsorship donor                        

                    end 
                end                                           

                set @DONORCONSTITUENCYTEXT = null;
                if (select top 1 ISACTIVE from dbo.CONSTITUENCYDEFINITION where ID = '70165682-4324-46EC-9439-83FC0CC67E7F') = 1
                begin
                    if dbo.UFN_CONSTITUENT_ISDONOR(@ID) = 1
                    begin
                        set @DONORCONSTITUENCYTEXT = dbo.UFN_CONSTITUENCY_GETDESCRIPTION('70165682-4324-46EC-9439-83FC0CC67E7F') --Donor

                    end
                end 

                set @LOYALDONORCONSTITUENCYTEXT = null;
                if (select top 1 ISACTIVE from dbo.CONSTITUENCYDEFINITION where ID = 'E5A0EA42-65BA-4B25-AFE2-9B709F99E72B') = 1
                begin
                    if dbo.UFN_CONSTITUENT_ISLOYALDONOR(@ID) = 1
                    begin
                        set @LOYALDONORCONSTITUENCYTEXT = dbo.UFN_CONSTITUENCY_GETDESCRIPTION('E5A0EA42-65BA-4B25-AFE2-9B709F99E72B') --Loyal donor

                    end
                end 


                set @MAJORDONORCONSTITUENCYTEXT = null;
                if (select top 1 ISACTIVE from dbo.CONSTITUENCYDEFINITION where ID = '1A9BFE80-604D-4B5B-8065-E751DDF6EF39') = 1
                begin
                    if dbo.UFN_CONSTITUENT_ISMAJORDONOR(@ID) = 1
                    begin
                        set @MAJORDONORCONSTITUENCYTEXT = dbo.UFN_CONSTITUENCY_GETDESCRIPTION('1A9BFE80-604D-4B5B-8065-E751DDF6EF39') --Major donor

                    end
                end 


                if len(@LOYALDONORCONSTITUENCYTEXT)>0 or len(@MAJORDONORCONSTITUENCYTEXT)>0 
                    set @DONORCONSTITUENCYTEXT = null;


                set @FUNDRAISERCONSTITUENCYTEXT = null;
                if (select top 1 ISACTIVE from dbo.CONSTITUENCYDEFINITION where ID = 'D2DCA06A-BE6E-40B3-B95D-59A926181923') = 1
                begin
                    if dbo.UFN_CONSTITUENT_ISFUNDRAISER(@ID) = 1
                    begin
                        set @FUNDRAISERCONSTITUENCYTEXT = dbo.UFN_CONSTITUENCY_GETDESCRIPTION('D2DCA06A-BE6E-40B3-B95D-59A926181923') --Fundraiser

                    end
                end

                set @PROSPECTCONSTITUENCYTEXT = null;
                if (select top 1 ISACTIVE from dbo.CONSTITUENCYDEFINITION where ID = '00E748FB-940D-4A7D-A133-C148B29410A8') = 1
                begin
                    if dbo.UFN_CONSTITUENT_ISPROSPECT(@ID) = 1
                    begin
                        set @PROSPECTCONSTITUENCYTEXT = dbo.UFN_CONSTITUENCY_GETDESCRIPTION('00E748FB-940D-4A7D-A133-C148B29410A8') --Major giving prospect

                    end
                end                            

                set @VOLUNTEERCONSTITUENCYTEXT = null;
                if (select top 1 ISACTIVE from dbo.CONSTITUENCYDEFINITION where ID = 'E7489703-3D63-4017-A2BC-88C092563C5D') = 1
                begin
                    if dbo.UFN_CONSTITUENT_ISVOLUNTEER(@ID) = 1
                    begin
                        set @VOLUNTEERCONSTITUENCYTEXT = dbo.UFN_CONSTITUENCY_GETDESCRIPTION('E7489703-3D63-4017-A2BC-88C092563C5D') --Volunteer

                    end
                end  


                -- no longer return 'Community member' as a constituency

                --set @COMMUNITYMEMBERCONSTITUENCYTEXT = case

                --                                      when dbo.UFN_CONSTITUENT_ISCOMMUNITYMEMBER(@ID) = 1 then dbo.UFN_CONSTITUENCY_GETDESCRIPTION('55FE8E7C-2B68-44C8-B35C-818AD1944C03') --Community member

                --                                      else null end;  

                set @PATRONCONSTITUENCYTEXT = null;
                if (select top 1 ISACTIVE from dbo.CONSTITUENCYDEFINITION where ID = 'A843B859-4C6B-445B-97F3-179582E270A5') = 1
                begin
                    if dbo.UFN_CONSTITUENT_ISPATRON(@ID) = 1
                    begin
                        set @PATRONCONSTITUENCYTEXT = dbo.UFN_CONSTITUENCY_GETDESCRIPTION('A843B859-4C6B-445B-97F3-179582E270A5') --Patron

                    end
                end



                set @ALUMNUSSTATUSTEXT = dbo.UFN_CONSTITUENT_GETALUMNUSSTATUSTEXT(@ID);

                set @REGISTRANTSTATUSTEXT = null;
                if (select top 1 ISACTIVE from dbo.CONSTITUENCYDEFINITION where ID = 'C49D4B46-72A7-4206-91AA-BEABA2323E3C') = 1
                begin
                    if dbo.UFN_CONSTITUENT_ISREGISTRANT(@ID) = 1
                    begin
                        set @REGISTRANTSTATUSTEXT = dbo.UFN_CONSTITUENCY_GETDESCRIPTION('C49D4B46-72A7-4206-91AA-BEABA2323E3C') --Event registrant

                    end
                end


                set @VENDORSTATUSTEXT = null;
                if (select top 1 ISACTIVE from dbo.CONSTITUENCYDEFINITION where ID = '5435C96D-8617-46C3-9A62-5AFF08451A53') = 1
                begin
                    if dbo.UFN_CONSTITUENT_ISVENDOR(@ID) = 1
                    begin
                        set @VENDORSTATUSTEXT = dbo.UFN_CONSTITUENCY_GETDESCRIPTION('5435C96D-8617-46C3-9A62-5AFF08451A53') --Vendor

                    end
                end

                set @MATCHFINDERCONSTITUENCYTEXT = null;
                if (select top 1 ISACTIVE from dbo.CONSTITUENCYDEFINITION where ID = '2D04A9C5-27D0-4646-BF0F-6826E4C12632') = 1
                begin
                    if not @MATCHFINDERONLINERECORDID is null and @MATCHFINDERONLINERECORDID <> 0
                    begin
                        set @MATCHFINDERCONSTITUENCYTEXT = dbo.UFN_CONSTITUENCY_GETDESCRIPTION('2D04A9C5-27D0-4646-BF0F-6826E4C12632') --Matching gift organization

                    end
                end

                set @USERDEFINEDCONSTITUENCYTEXT = dbo.UFN_CONSTITUENCIES_USERDEFINED(@ID);

                set @USERDEFINEDCONSTITUENCIES = dbo.UFN_CONSTITUENT_GETCONSTITUENCIES_USERDEFINED_TOITEMLISTXML(@ID, @CURRENTDATE);


                set @COMMITTEECONSTITUENCYTEXT = null;
                if (select top 1 ISACTIVE from dbo.CONSTITUENCYDEFINITION where ID = 'AC9DB5A4-14E0-416A-9FB2-04038AC66799') = 1
                begin
                    if dbo.UFN_CONSTITUENT_ISCOMMITTEE(@ID) = 1
                    begin
                        set @COMMITTEECONSTITUENCYTEXT = dbo.UFN_CONSTITUENCY_GETDESCRIPTION('AC9DB5A4-14E0-416A-9FB2-04038AC66799') --Committee

                    end
                end

                set @GRANTORCONSTITUENCYTEXT = null;
                if (select top 1 ISACTIVE from dbo.CONSTITUENCYDEFINITION where ID = 'D9982C99-15C1-4C90-873E-56FD4B164056') = 1
                begin
                    if dbo.UFN_CONSTITUENT_ISGRANTOR(@ID) = 1
                    begin
                        set @GRANTORCONSTITUENCYTEXT = dbo.UFN_CONSTITUENCY_GETDESCRIPTION('D9982C99-15C1-4C90-873E-56FD4B164056') --Grantor

                    end
                end


                set @ISCOMMITTEEMEMBER = dbo.UFN_CONSTITUENT_ISCOMMITTEEMEMBER(@ID)

                set @COMMITTEEMEMBERCONSTITUENCYTEXT = null;
                if (select top 1 ISACTIVE from dbo.CONSTITUENCYDEFINITION where ID = '093A3D4F-2974-447F-AD92-870EB4A04593') = 1
                begin
                    if @ISCOMMITTEEMEMBER = 1
                    begin
                        set @COMMITTEEMEMBERCONSTITUENCYTEXT = dbo.UFN_CONSTITUENCY_GETDESCRIPTION('093A3D4F-2974-447F-AD92-870EB4A04593') /* Committee member */
                    end
                end

                set @BANKCONSTITUENCYTEXT = null;
                if (select top 1 ISACTIVE from dbo.CONSTITUENCYDEFINITION where ID = '171AB3CD-C4E1-4825-B693-10F524A7A594') = 1
                begin
                    if dbo.UFN_CONSTITUENT_ISBANK(@ID) = 1
                    begin
                        set @BANKCONSTITUENCYTEXT = dbo.UFN_CONSTITUENCY_GETDESCRIPTION('171AB3CD-C4E1-4825-B693-10F524A7A594')
                    end
                end


                set @STUDENTRELATIONCONSTITUENCYTEXT = (select dbo.UDA_BUILDLIST(distinct CONSTITUENCYCODEDESCRIPTION) from dbo.UFN_CONSTITUENT_STUDENTRELATIONCONSTITUENCIES() where CONSTITUENTID = @ID)

                set @STUDENTRELATIONCONSTITUENCIES = (
                    select distinct
                        CONSTITUENCYCODEDESCRIPTION as DESCRIPTION,
                        CONSTITUENCYCODESEQUENCE as SEQUENCE
                    from
                        dbo.UFN_CONSTITUENT_STUDENTRELATIONCONSTITUENCIES()
                    where
                        CONSTITUENTID = @ID
                    order by
                        CONSTITUENCYCODESEQUENCE
                    for xml raw('ITEM'), type, elements, root('STUDENTRELATIONCONSTITUENCIES'), binary base64
                )


            set @NFGCONSTITUENCYTEXT = null;
                if (select top 1 ISACTIVE from dbo.CONSTITUENCYDEFINITION where ID = 'A499228A-7619-40DF-AE75-BBA1EE20FB4E') = 1
                begin
                    if dbo.UFN_CONSTITUENT_ISNATIONALFUNDRAISINGGROUP(@ID) = 1
                    begin
                        set @NFGCONSTITUENCYTEXT = dbo.UFN_CONSTITUENCY_GETDESCRIPTION('A499228A-7619-40DF-AE75-BBA1EE20FB4E') --NFG

                    end
                end

                set @FUNDRAISINGGROUPCONSTITUENCYTEXT = null;
                if (select top 1 ISACTIVE from dbo.CONSTITUENCYDEFINITION where ID = '264818E9-3F59-44C1-9BBF-1BB1F9C78CA5') = 1
                begin
                    if dbo.UFN_CONSTITUENT_ISFUNDRAISINGGROUP(@ID) = 1
                    begin
                        set @FUNDRAISINGGROUPCONSTITUENCYTEXT = dbo.UFN_CONSTITUENCY_GETDESCRIPTION('264818E9-3F59-44C1-9BBF-1BB1F9C78CA5') --Fundraising Group

                    end
                end


                if exists(select 1 from dbo.WEALTH where ID=@ID and PENDINGSEARCH=1) begin
                    set @WEALTHPOINTUPDATEPENDING = 1;
                end else begin
                    set @WEALTHPOINTUPDATEPENDING = 0;
                end


                --Solicit Codes

                select @SOLICITCODECOUNT=count(ID)
                from CONSTITUENTSOLICITCODE
                where CONSTITUENTID=@ID

                -- HOUSEHOLD

                -- constituents are only allowed to be part of one household, only individuals can be in households

                if @ISORGANIZATION = 0 and @ISGROUP = 0
                    select top(1)
                        @HOUSEHOLDID = CG.ID,
                        @HOUSEHOLDTEXT = CG.NAME
                    from dbo.GROUPMEMBER as GM
                    join dbo.CONSTITUENT as CG on GM.GROUPID = CG.ID
                    join dbo.GROUPDATA as GD on GD.ID = CG.ID
                    where GM.MEMBERID = @ID
                        and dbo.UFN_GROUPMEMBER_ISCURRENTMEMBER(GM.ID) = 1
                        and GD.GROUPTYPECODE = 0;

                --Gift Aid declarations indicator

                select 
                    @DECLARATIONSONFILE = case  
                        when count(*) > 0 then 1
                        else 0
                    end
                from 
                    dbo.TAXDECLARATION
                where 
                    TAXDECLARATION.CONSTITUENTID=@ID and 
                    (
                        (DECLARATIONSTARTS <= getdate() and DECLARATIONENDS >= getdate())
                        or (DECLARATIONSTARTS <= getdate() and DECLARATIONENDS is null)
                    ) and 
                    exists (select PERMISSIONGRANTED from dbo.UFN_APPUSER_HASPERMISSIONFORDECLARATION(@CURRENTAPPUSERID, TAXDECLARATION.CHARITYCLAIMREFERENCENUMBERID));


                --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)) 

                --STUDENT ENROLLMENT

                if @STUDENTCONSTITUENCYTEXT is not null
                 begin
             set @STUDENTENROLLMENTID = 
                    (select 
                        top 1 (EDUCATIONALHISTORY.ID) 
                    from dbo.EDUCATIONALHISTORY 
                        inner join dbo.EDUCATIONALINSTITUTION on EDUCATIONALHISTORY.EDUCATIONALINSTITUTIONID = EDUCATIONALINSTITUTION.ID
                        inner join EDUCATIONALHISTORYSTATUS on EDUCATIONALHISTORY.EDUCATIONALHISTORYSTATUSID = EDUCATIONALHISTORYSTATUS.ID
                    where (EDUCATIONALINSTITUTION.ISAFFILIATED = 1) and
                          (EDUCATIONALHISTORYSTATUS.CONSTITUENCYIMPLIEDCODE = 0) and
                          (EDUCATIONALHISTORY.CONSTITUENTID = @ID)
                    order by dbo.EDUCATIONALHISTORY.DATEADDED)
                end

                --ALUMNUS ENROLLMENT

                if @ALUMNUSCONSTITUENCYTEXT is not null 
                 begin
                    set @ALUMNUSENROLLMENTID =
                        (select top 1 (EDUCATIONALHISTORY.ID) 
                        from dbo.EDUCATIONALHISTORY 
                            inner join dbo.EDUCATIONALINSTITUTION on EDUCATIONALHISTORY.EDUCATIONALINSTITUTIONID = EDUCATIONALINSTITUTION.ID
                            inner join EDUCATIONALHISTORYSTATUS on EDUCATIONALHISTORY.EDUCATIONALHISTORYSTATUSID = EDUCATIONALHISTORYSTATUS.ID
                        where (EDUCATIONALINSTITUTION.ISAFFILIATED = 1) and
                              (EDUCATIONALHISTORYSTATUS.CONSTITUENCYIMPLIEDCODE = 1) and
                              (EDUCATIONALHISTORY.CONSTITUENTID = @ID)
                        order by dbo.EDUCATIONALHISTORY.DATEADDED)
                 end

                 select 
                    @LIFECYCLESTAGE = LIFECYCLETYPE.NAME, 
                    @LIFECYCLESTAGEASOF = REVENUELIFECYCLE.STARTDATE,
                    @DONORSTATECODE = REVENUELIFECYCLE.DONORSTATECODE,
                    @LASTREVENUEDATE = REVENUELIFECYCLE.LASTREVENUEDATE,
                    @DONORSTATE = REVENUELIFECYCLE.DONORSTATE
                 from
                    dbo.REVENUELIFECYCLE
                    inner join dbo. LIFECYCLETYPE on LIFECYCLETYPE.ID = REVENUELIFECYCLE.LIFECYCLETYPEID
                 where
                    REVENUELIFECYCLE.CONSTITUENTID = @ID and
                    LIFECYCLETYPE.IMPORTANCE <> 0;

                 select 
                    @PLANNEDGIVERSTAGE = LIFECYCLETYPE.NAME, 
                    @PLANNEDGIVERSTAGEASOF = REVENUELIFECYCLE.STARTDATE
                 from
                    dbo.REVENUELIFECYCLE
                    inner join dbo. LIFECYCLETYPE on LIFECYCLETYPE.ID = REVENUELIFECYCLE.LIFECYCLETYPEID
                 where
                    REVENUELIFECYCLE.CONSTITUENTID = @ID and
                    LIFECYCLETYPE.NAME = 'Planned giver';

          declare @TOPPARENTCODE uniqueidentifier;
          declare @TOPSUBCODE uniqueidentifier;

          select @TOPPARENTCODE = TOPPARENTORGID,
                   @TOPSUBCODE = TOPSUBSIDIARYORGID 
          from dbo.RELATIONSHIPCONFIGURATIONCORPORATE 

        select @TOPPARENT=TOPPARENT,
               @TOPPARENTID=TOPPARENTID
        from dbo.UFN_CONSTITUENT_GETPARENTORGNAME(@ID,@TOPPARENTCODE,@TOPSUBCODE)

        if @TOPPARENTID = @RELATEDCONSTITUENTID or @TOPPARENTID = @ID
          set @TOPPARENT = ''

        if dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1
          set @USER_GRANTED_CONSTITPERSONALINFO_EDIT = 1;
        else
          set @USER_GRANTED_CONSTITPERSONALINFO_EDIT = dbo.UFN_SECURITY_APPUSER_GRANTED_FORM_IN_SYSTEMROLE(@CURRENTAPPUSERID,'788AB947-26ED-40C4-865E-8FE29577E593');


                return 0;