USP_DATAFORMTEMPLATE_VIEW_COMMUNITYMEMBERPROFILE

The load procedure used by the view dataform template "Community Member 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.
@DATALOADED	bit	INOUT	Output parameter indicating whether or not data was actually loaded.
@ISCOMMUNITYMEMBERPROFILE	bit	INOUT	ISCOMMUNITYMEMBERPROFILE
@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
@COMMUNITYMEMBERUSERNAME	nvarchar(50)	INOUT	Username
@COMMUNITYMEMBERUSERADDEDDATE	datetime	INOUT	Date registered
@COMMUNITYMEMBERLASTLOGINDATE	datetime	INOUT	Last login
@PRIMARYEDUCATIONID	uniqueidentifier	INOUT	PRIMARYEDUCATIONID
@EDUCATIONATTRIBUTEDEFINED	bit	INOUT	EDUCATIONATTRIBUTEDEFINED
@MATCHFINDERCONSTITUENCYTEXT	nvarchar(100)	INOUT	MATCHFINDERCONSTITUENCYTEXT
@MATCHFINDERONLINERECORDID	int	INOUT	MATCHFINDERONLINERECORDID
@SOLICITCODECOUNT	int	INOUT	SOLICITCODECOUNT
@GIVESANONYMOUSLY	bit	INOUT	Gives anonymously
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
@MEMBERCONSTITUENCYTEXT	nvarchar(100)	INOUT	MEMBERCONSTITUENCYTEXT
@RECOGNITIONCONSTITUENCYTEXT	nvarchar(100)	INOUT	RECOGNITIONCONSTITUENCYTEXT
@PLANNEDGIVERCONSTITUENCYTEXT	nvarchar(100)	INOUT	PLANNEDGIVERCONSTITUENCYTEXT
@BANKCONSTITUENCYTEXT	nvarchar(100)	INOUT	BANKCONSTITUENCYTEXT
@PATRONCONSTITUENCYTEXT	nvarchar(100)	INOUT	PATRONCONSTITUENCYTEXT
@GRANTORCONSTITUENCYTEXT	nvarchar(100)	INOUT	GRANTORCONSTITUENCYTEXT
@SPONSORCONSTITUENCYTEXT	nvarchar(100)	INOUT	SPONSORCONSTITUENCYTEXT
@ISCOMMITTEEMEMBER	bit	INOUT	ISCOMMITTEEMEMBER
@COMMITTEEMEMBERCONSTITUENCYTEXT	nvarchar(100)	INOUT	COMMITTEEMEMBERCONSTITUENCYTEXT
@LOYALDONORCONSTITUENCYTEXT	nvarchar(100)	INOUT	LOYALDONORCONSTITUENCYTEXT
@MAJORDONORCONSTITUENCYTEXT	nvarchar(100)	INOUT	MAJORDONORCONSTITUENCYTEXT
@ISDECEASED	bit	INOUT	ISDECEASED
@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
@HOUSEHOLDTEXT	nvarchar(154)	INOUT	Household
@HOUSEHOLDID	uniqueidentifier	INOUT	HOUSEHOLDID
@EMAILINFORMATIONLOADED	bit	INOUT	Email information loaded
@OPTEDIN	bit	INOUT	Has requested email
@FORWARDINGENABLED	bit	INOUT	Forwarding enabled
@INCOMINGEMAILADDRESS	nvarchar(510)	INOUT	Incoming address
@FORWARDINGEMAILADDRESS	nvarchar(510)	INOUT	Forwarding address

Definition

Copy


                CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_COMMUNITYMEMBERPROFILE
                (
                    @ID uniqueidentifier,
                    @DATALOADED bit = 0 output,
                    @ISCOMMUNITYMEMBERPROFILE bit = null 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,
                    @COMMUNITYMEMBERUSERNAME nvarchar(50) = null output,
                    @COMMUNITYMEMBERUSERADDEDDATE datetime = null output,
                    @COMMUNITYMEMBERLASTLOGINDATE datetime = null output,
                    @PRIMARYEDUCATIONID uniqueidentifier = null output,
                    @EDUCATIONATTRIBUTEDEFINED bit = null output,
                    @MATCHFINDERCONSTITUENCYTEXT nvarchar(100) = null output,
                    @MATCHFINDERONLINERECORDID int = null output,
                    @SOLICITCODECOUNT int = null output,
                    @GIVESANONYMOUSLY bit = null output,
                    @CURRENTAPPUSERID uniqueidentifier = null,
                    @MEMBERCONSTITUENCYTEXT nvarchar(100)=null output,
                    @RECOGNITIONCONSTITUENCYTEXT nvarchar(100)=null output,
                    @PLANNEDGIVERCONSTITUENCYTEXT nvarchar(100)=null output,
                    @BANKCONSTITUENCYTEXT nvarchar(100) = null output,
                    @PATRONCONSTITUENCYTEXT nvarchar(100) = null output,
                    @GRANTORCONSTITUENCYTEXT nvarchar(100) = null output,
                    @SPONSORCONSTITUENCYTEXT nvarchar(100) = null output,
                    @ISCOMMITTEEMEMBER bit = null output,
                    @COMMITTEEMEMBERCONSTITUENCYTEXT nvarchar(100) = null output,
                    @LOYALDONORCONSTITUENCYTEXT nvarchar(100) = null output,
                    @MAJORDONORCONSTITUENCYTEXT nvarchar(100) = null output,
                    @ISDECEASED bit = 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,
                    @HOUSEHOLDTEXT nvarchar(154) = null output,
                    @HOUSEHOLDID uniqueidentifier = null output,
                    @EMAILINFORMATIONLOADED bit = null output,
                    @OPTEDIN bit = null output,
                    @FORWARDINGENABLED bit = null output,
                    @INCOMINGEMAILADDRESS nvarchar(510) = null output,
                    @FORWARDINGEMAILADDRESS nvarchar(510) = null output
                )
                as

                    set nocount on;

                    set @DATALOADED = 0;

                    declare @ISGROUP bit;

                    --CONSTIT FIELDS
                    select
                        @DATALOADED = 1,
                        @ISORGANIZATION = CONSTITUENT.ISORGANIZATION,
                        @ISGROUP = CONSTITUENT.ISGROUP,
                        @WEBADDRESS = CONSTITUENT.WEBADDRESS,
                        @PICTURE = CONSTITUENT.PICTURETHUMBNAIL,
                        @ISINACTIVE = CONSTITUENT.ISINACTIVE,
                        @LOOKUPID = CONSTITUENT.LOOKUPID,
                        @ISCONSTITUENTPROFILE = 1,
                        @ISCOMMUNITYMEMBERPROFILE = 1,
                        @GIVESANONYMOUSLY = CONSTITUENT.GIVESANONYMOUSLY
                    from
                        dbo.CONSTITUENT
                    where
                        CONSTITUENT.ID = @ID;

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

                --PRIMARY BUSINESS RELATIONSHIP FIELDS
                    if @ISORGANIZATION = 0 and @ISGROUP = 0
                        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
                        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 
                            begin
                                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;
                            end
                        end

                    --DECEASED
                    SET @ISDECEASED = 0;

                    SELECT 
                    @DECEASEDDATE=DECEASEDDATE,
                    @ISDECEASED = 1
                    FROM
                    dbo.DECEASEDCONSTITUENT
                    WHERE 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 = 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;

                    select top 1
                        @MATCHFINDERONLINERECORDID = MATCHFINDERRECORDID 
                    from dbo.MATCHFINDERCONSTITUENT
                    where ID = @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;

                    --Constituencies
                    set @PLANNEDGIVERCONSTITUENCYTEXT=case 
                          when dbo.UFN_CONSTITUENT_ISPLANNEDGIVER(@ID) = 1 then dbo.UFN_CONSTITUENCY_GETDESCRIPTION('CEE46FE7-3FBB-4DFE-97EB-BA67DD33C634')  --Planned giver
                          else null end;

                    set @RECOGNITIONCONSTITUENCYTEXT=case 
                             when dbo.UFN_CONSTITUENT_ISRECOGNITION(@ID,@CURRENTAPPUSERID) = 1 then dbo.UFN_CONSTITUENCY_GETDESCRIPTION('3dfac92e-78bd-4051-abdc-02c675deb8f6')  --Recognition
                             else null end;

                    set @MEMBERCONSTITUENCYTEXT=case 
                          when dbo.UFN_CONSTITUENT_ISMEMBER(@ID,@CURRENTAPPUSERID) = 1 then dbo.UFN_CONSTITUENCY_GETDESCRIPTION('2d11326e-8f3b-4322-9797-57c1aacfa5df')  --Member
                          else null end;

                    set    @BOARDMEMBERCONSTITUENCYTEXT = case
                                                        when dbo.UFN_CONSTITUENT_ISBOARDMEMBER(@ID) = 1 then dbo.UFN_CONSTITUENCY_GETDESCRIPTION('F828E957-5F5E-479A-8F23-2FFD6C7C68FF') --Board member
                                                        else null end;

                    set    @RELATIONCONSTITUENCYTEXT = case
                                                        when dbo.UFN_CONSTITUENT_ISRELATION(@ID) = 1 then dbo.UFN_CONSTITUENCY_GETDESCRIPTION('8B6ECCFD-6829-4FBC-B092-58CA083F9322') --Relation only
                                                        else null end;
                    set @STAFFCONSTITUENCYTEXT = case
                                                    when dbo.UFN_CONSTITUENT_ISSTAFF(@ID) = 1 then dbo.UFN_CONSTITUENCY_GETDESCRIPTION('6093915E-ADE9-42BE-88AE-304731754467') --Staff
                                                    else null end;
                    set @DONORCONSTITUENCYTEXT = case
                                                    when dbo.UFN_CONSTITUENT_ISDONOR(@ID) = 1 then dbo.UFN_CONSTITUENCY_GETDESCRIPTION('70165682-4324-46EC-9439-83FC0CC67E7F') --Donor
                                                    else null end;

                    set @LOYALDONORCONSTITUENCYTEXT = case 
                                                    when dbo.UFN_CONSTITUENT_ISLOYALDONOR(@ID) = 1 then dbo.UFN_CONSTITUENCY_GETDESCRIPTION('E5A0EA42-65BA-4B25-AFE2-9B709F99E72B') --Loyal donor
                                                    else null end;

                    set @MAJORDONORCONSTITUENCYTEXT = case 
                                                    when dbo.UFN_CONSTITUENT_ISMAJORDONOR(@ID) = 1 then dbo.UFN_CONSTITUENCY_GETDESCRIPTION('1A9BFE80-604D-4B5B-8065-E751DDF6EF39') --Major donor
                                                    else null end;

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

                    set @FUNDRAISERCONSTITUENCYTEXT = case
                                                        when dbo.UFN_CONSTITUENT_ISFUNDRAISER(@ID) = 1 then dbo.UFN_CONSTITUENCY_GETDESCRIPTION('D2DCA06A-BE6E-40B3-B95D-59A926181923') --Fundraiser
                                                        else null end;
                    set @PROSPECTCONSTITUENCYTEXT = case
                                                        when dbo.UFN_CONSTITUENT_ISPROSPECT(@ID) = 1 then dbo.UFN_CONSTITUENCY_GETDESCRIPTION('00E748FB-940D-4A7D-A133-C148B29410A8') --Major giving prospect
                                                        else null end;
                    set @VOLUNTEERCONSTITUENCYTEXT = case
                                                        when dbo.UFN_CONSTITUENT_ISVOLUNTEER(@ID) = 1 then dbo.UFN_CONSTITUENCY_GETDESCRIPTION('E7489703-3D63-4017-A2BC-88C092563C5D') --Volunteer
                                                        else null end;
                    --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 @ALUMNUSSTATUSTEXT = dbo.UFN_CONSTITUENT_GETALUMNUSSTATUSTEXT(@ID);
                    set @REGISTRANTSTATUSTEXT = case
                                                    when dbo.UFN_CONSTITUENT_ISREGISTRANT(@ID) = 1 then dbo.UFN_CONSTITUENCY_GETDESCRIPTION('C49D4B46-72A7-4206-91AA-BEABA2323E3C') --Event registrant
                                                    else null end;
                    set @VENDORSTATUSTEXT = case
                                                when dbo.UFN_CONSTITUENT_ISVENDOR(@ID) = 1 then dbo.UFN_CONSTITUENCY_GETDESCRIPTION('5435C96D-8617-46C3-9A62-5AFF08451A53') --Vendor
                                                else null end;
                    set @MATCHFINDERCONSTITUENCYTEXT = case
                                                when not @MATCHFINDERONLINERECORDID is null and @MATCHFINDERONLINERECORDID <> 0 then dbo.UFN_CONSTITUENCY_GETDESCRIPTION('2D04A9C5-27D0-4646-BF0F-6826E4C12632') --Matching gift organization
                                                else null end;
                    set @USERDEFINEDCONSTITUENCYTEXT = dbo.UFN_CONSTITUENCIES_USERDEFINED(@ID);
                    set @BANKCONSTITUENCYTEXT = case
                                    when dbo.UFN_CONSTITUENT_ISBANK(@ID) = 1 then dbo.UFN_CONSTITUENCY_GETDESCRIPTION('171AB3CD-C4E1-4825-B693-10F524A7A594') --Bank
                                    else null end;
                    set @PATRONCONSTITUENCYTEXT = case
                        when dbo.UFN_CONSTITUENT_ISPATRON(@ID) = 1 then dbo.UFN_CONSTITUENCY_GETDESCRIPTION('A843B859-4C6B-445B-97F3-179582E270A5') --Patron
                        else null end;                                                        

                    set @GRANTORCONSTITUENCYTEXT = case when dbo.UFN_CONSTITUENT_ISGRANTOR(@ID) = 1 THEN dbo.UFN_CONSTITUENCY_GETDESCRIPTION('D9982C99-15C1-4C90-873E-56FD4B164056') --Grantor
                                                else null end;

                    set @SPONSORCONSTITUENCYTEXT = case dbo.UFN_CONSTITUENT_SPONSORTYPECODE(@ID)
                                                     when 0 then dbo.UFN_CONSTITUENCY_GETDESCRIPTION('4D746A03-A0AB-45F3-A30B-1AD4F304E622') --Sponsor
                                                     when 1 then dbo.UFN_CONSTITUENCY_GETDESCRIPTION('F89E03BC-E724-4e5d-943B-72D4D1E1E916') --Sponsorship recipient
                                                     when 2 then dbo.UFN_CONSTITUENCY_GETDESCRIPTION('908E521C-B0A5-4832-B664-7D7B079D77C2') --Sponsorship donor
                                                    else null end;

                    set @ISCOMMITTEEMEMBER = dbo.UFN_CONSTITUENT_ISCOMMITTEEMEMBER(@ID);
                    set @COMMITTEEMEMBERCONSTITUENCYTEXT = case when @ISCOMMITTEEMEMBER = 1 then dbo.UFN_CONSTITUENCY_GETDESCRIPTION('093A3D4F-2974-447F-AD92-870EB4A04593') /* Committee member */ else null 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

                    --Community Member
                    select top (1)
                        @COMMUNITYMEMBERUSERNAME = [USERNAME],
                        @COMMUNITYMEMBERUSERADDEDDATE = [USERADDEDDATE],
                        @COMMUNITYMEMBERLASTLOGINDATE = [LASTLOGINDATE]
                    from
                        dbo.NETCOMMUNITYCLIENTUSER
                    where
                        [CONSTITUENTID] = @ID
                        and [ACTIVE] = 1
                        and [DELETED] = 0
                    order by
                        [USERUPDATEDATE] desc;

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

                    -- email forwarding info
                    set @EMAILINFORMATIONLOADED = 0;

                    select top (1)
                        @EMAILINFORMATIONLOADED =
                            case
                                when [NETCOMMUNITYEMAILINFO].[ID] is null then 0
                                else 1
                            end,
                        @OPTEDIN = [NETCOMMUNITYEMAILINFO].[OPTEDIN],
                        @FORWARDINGENABLED = [NETCOMMUNITYEMAILINFO].[FORWARDINGENABLED],
                        @INCOMINGEMAILADDRESS = [NETCOMMUNITYEMAILINFO].[INCOMINGEMAILADDRESS],
                        @FORWARDINGEMAILADDRESS = [NETCOMMUNITYEMAILINFO].[FORWARDINGEMAILADDRESS]
                    from
                        dbo.NETCOMMUNITYCLIENTUSER
                        left join dbo.NETCOMMUNITYEMAILINFO on [NETCOMMUNITYCLIENTUSER].[ID] = [NETCOMMUNITYEMAILINFO].[NETCOMMUNITYCLIENTUSERID]
                    where
                        [NETCOMMUNITYCLIENTUSER].[CONSTITUENTID] = @ID
                    order by
                        [NETCOMMUNITYEMAILINFO].[UPDATEDATE] desc;

                    return 0;