USP_DATAFORMTEMPLATE_VIEW_CONSTITUENTPAGEDATA

The load procedure used by the view dataform template "Constituent Page Expression 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
@ISSELF	bit	INOUT	ISSELF
@ISORG	bit	INOUT	ISORG
@ISPROSPECT	bit	INOUT	ISPROSPECT
@ISFORMERPROSPECT	bit	INOUT	ISFORMERPROSPECT
@ISFUNDRAISER	bit	INOUT	ISFUNDRAISER
@ISFORMERFUNDRAISER	bit	INOUT	ISFORMERFUNDRAISER
@ISVOLUNTEER	bit	INOUT	ISVOLUNTEER
@ISFORMERVOLUNTEER	bit	INOUT	ISFORMERVOLUNTEER
@ISCOMMUNITYMEMBER	bit	INOUT	ISCOMMUNITYMEMBER
@ISREGISTRANT	bit	INOUT	ISREGISTRANT
@ISVENDOR	bit	INOUT	ISVENDOR
@APPUSERID	uniqueidentifier	INOUT	APPUSERID
@ATTRIBUTEDEFINED	bit	INOUT	ATTRIBUTEDEFINED
@SMARTFIELDDEFINED	bit	INOUT	SMARTFIELDDEFINED
@UPDATEPENDING	bit	INOUT	UPDATEPENDING
@CONSTITUENTID	uniqueidentifier	INOUT	CONSTITUENTID
@HASSPOUSE	bit	INOUT	HASSPOUSE
@AGE	int	INOUT	AGE
@ISALUMNUS	bit	INOUT	ISALUMNUS
@ISSTUDENT	bit	INOUT	ISSTUDENT
@ISFORMERSTUDENT	bit	INOUT	ISFORMERSTUDENT
@VALIDSEARCHCRITERIASTRICT	bit	INOUT	VALIDSEARCHCRITERIASTRICT
@VALIDSEARCHCRITERIARELAXED	bit	INOUT	VALIDSEARCHCRITERIARELAXED
@EDUCATIONATTRIBUTEDEFINED	bit	INOUT	EDUCATIONATTRIBUTEDEFINED
@ISGROUPMEMBER	bit	INOUT	ISGROUPMEMBER
@ISGROUP	bit	INOUT	ISGROUP
@GROUPCANBEDONOR	bit	INOUT	GROUPCANBEDONOR
@CURRENTMEMBERCOUNT	int	INOUT	CURRENTMEMBERCOUNT
@PREVIOUSMEMBERCOUNT	int	INOUT	PREVIOUSMEMBERCOUNT
@HASCOMMUNITYDATA	bit	INOUT	HASCOMMUNITYDATA
@ISHOUSEHOLD	bit	INOUT	ISHOUSEHOLD
@HOUSEHOLDID	uniqueidentifier	INOUT	HOUSEHOLDID
@MAPPINGCREDENTIALSEXIST	bit	INOUT	MAPPINGCREDENTIALSEXIST
@HASSTEWARDSHIPPLAN	bit	INOUT	HASSTEWARDSHIPPLAN
@DECEASINGOPTIONSSET	bit	INOUT	DECEASINGOPTIONSSET
@ISDISSOLVED	bit	INOUT	ISDISSOLVED
@SPOUSERELATIONSHIPID	uniqueidentifier	INOUT	SPOUSERELATIONSHIPID
@ISPLANNEDGIVER	bit	INOUT	ISPLANNEDGIVER
@ISPATRON	bit	INOUT	ISPATRON
@ISBANK	bit	INOUT	ISBANK
@HASIND2ORGRELATIONS	bit	INOUT	HASIND2ORGRELATIONS
@ISCOMMITTEE	bit	INOUT	ISCOMMITTEE
@ISGRANTOR	bit	INOUT	ISGRANTOR
@ISSPONSOR	bit	INOUT	ISSPONSOR
@SPONSORTYPECODE	tinyint	INOUT	SPONSORTYPECODE
@ISCOMMITTEEMEMBER	bit	INOUT	ISCOMMITTEEMEMBER
@HASMULTIPLEMGCONDITIONS	bit	INOUT	HASMULTIPLEMGCONDITIONS
@ISFACULTY	bit	INOUT	ISFACULTY
@ISCONSTITUENT	bit	INOUT	ISCONSTITUENT
@CANBEDONOR	bit	INOUT	CANBEDONOR
@ISFORMERSPONSOR	bit	INOUT	ISFORMERSPONSOR
@USER_GRANTED_IND_TO_IND_REL_ADD_FOR_CONSTIT	bit	INOUT	USER_GRANTED_IND_TO_IND_REL_ADD_FOR_CONSTIT
@USER_GRANTED_CONSTITNOTENOTIFICATION_ADD	bit	INOUT	USER_GRANTED_CONSTITNOTENOTIFICATION_ADD
@USER_GRANTED_CONSTITNOTENOTIFICATION_EDIT	bit	INOUT	USER_GRANTED_CONSTITNOTENOTIFICATION_EDIT
@USER_GRANTED_CONSTITNOTE_ADD	bit	INOUT	USER_GRANTED_CONSTITNOTE_ADD
@USER_GRANTED_CONSTITMEDIALINK_ADD	bit	INOUT	USER_GRANTED_CONSTITMEDIALINK_ADD
@USER_GRANTED_CONSTITATTACHMENT_ADD	bit	INOUT	USER_GRANTED_CONSTITATTACHMENT_ADD
@USER_GRANTED_CONSTITNOTE_EDIT_2	bit	INOUT	USER_GRANTED_CONSTITNOTE_EDIT_2
@USER_GRANTED_CONSTITMEDIALINK_EDIT	bit	INOUT	USER_GRANTED_CONSTITMEDIALINK_EDIT
@USER_GRANTED_CONSTITATTACHMENT_EDIT	bit	INOUT	USER_GRANTED_CONSTITATTACHMENT_EDIT
@USER_GRANTED_CONSTITATTACHMENT_VIEW	bit	INOUT	USER_GRANTED_CONSTITATTACHMENT_VIEW
@USER_GRANTED_ADDRESS_GEOCODE	bit	INOUT	USER_GRANTED_ADDRESS_GEOCODE
@USER_GRANTED_CONSTITNOTENOTIFICATION_DELETE	bit	INOUT	USER_GRANTED_CONSTITNOTENOTIFICATION_DELETE
@USER_GRANTED_CONSTITNOTE_DELETE	bit	INOUT	USER_GRANTED_CONSTITNOTE_DELETE
@USER_GRANTED_CONSTITMEDIALINK_DELETE	bit	INOUT	USER_GRANTED_CONSTITMEDIALINK_DELETE
@USER_GRANTED_CONSTITATTACHMENT_DELETE	bit	INOUT	USER_GRANTED_CONSTITATTACHMENT_DELETE
@USER_GRANTED_ADDRESS_DELETE_FOR_CONSTIT	bit	INOUT	USER_GRANTED_ADDRESS_DELETE_FOR_CONSTIT
@USER_GRANTED_CONSTITUENT_MAPENTITY	bit	INOUT	USER_GRANTED_CONSTITUENT_MAPENTITY
@MARRIAGERULESSET	bit	INOUT	MARRIAGERULESSET
@ISADVOCATE	bit	INOUT	ISADVOCATE
@HASRECEIVEDFUNDS	bit	INOUT	HASRECEIVEDFUNDS
@ADDRESSATTRIBUTEDEFINED	bit	INOUT	ADDRESSATTRIBUTEDEFINED
@ISFAFREGISTRANT	bit	INOUT	ISFAFREGISTRANT
@VIEWREVENUETEXTLINKTEXT	varchar(25)	INOUT	VIEWREVENUETEXTLINKTEXT
@USER_GRANTED_CONSTITPERSONALINFO_EDIT	bit	INOUT	USER_GRANTED_CONSTITPERSONALINFO_EDIT
@MEMBERSHIPID	uniqueidentifier	INOUT
@ISNATIONALFUNDRAISINGGROUP	bit	INOUT
@ISFUNDRAISINGGROUP	bit	INOUT
@ALLOWINTERACTIONS	bit	INOUT
@ISHOUSEHOLDMEMBER	bit	INOUT
@USER_GRANTED_REVENUESUMMARY_VIEW	bit	INOUT
@USER_GRANTED_HOUSEHOLDSUMMARY_VIEW	bit	INOUT
@USER_GRANTED_REVENUESUMMARYBRKDWN_VIEW	bit	INOUT
@USER_GRANTED_REVENUESUMMARYPARAMETER_VIEW	bit	INOUT
@HASREFUNDS	bit	INOUT
@SINGLESPONSORSHIP	bit	INOUT
@ROOTID	uniqueidentifier	INOUT
@ISHISTORICALCOMMITTEEMEMBER	bit	INOUT
@CANCOORDINATEEVENTS	bit	INOUT
@CANSOLICITREVENUE	bit	INOUT
@HASFUNDRAISINGGOALS	bit	INOUT
@HASCAMPAIGNS	bit	INOUT
@HASCOMMITTEEGOALS	bit	INOUT
@CANSETCOMMITTEEGOALS	bit	INOUT
@HASEVENTS	bit	INOUT
@HASPROSPECTMANAGER	bit	INOUT
@FLAGGED	bit	INOUT
@ALERTSENABLED	bit	INOUT
@NETCOMMUNITYCLIENTUSERID	uniqueidentifier	INOUT
@NETCOMMUNITYEMAILINFOEXISTS	bit	INOUT
@USER_GRANTED_ADDRESS_EDIT_FOR_CONSTIT	bit	INOUT
@USER_GRANTED_PHONENUMBER_EDIT_FOR_CONSTIT	bit	INOUT
@USER_GRANTED_PHONENUMBER_DELETE_FOR_CONSTIT	bit	INOUT
@USER_GRANTED_EMAILADDRESS_EDIT_FOR_CONSTIT	bit	INOUT
@USER_GRANTED_EMAILADDRESS_DELETE_FOR_CONSTIT	bit	INOUT
@USER_GRANTED_SOCIALMEDIAACCOUNT_EDIT_FOR_CONSTIT	bit	INOUT
@USER_GRANTED_SOCIALMEDIAACCOUNT_DELETE_FOR_CONSTIT	bit	INOUT
@HASCONSENTSOLICITCODE	bit	INOUT

Definition

Copy



                CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_CONSTITUENTPAGEDATA
                (
                    @ID uniqueidentifier,
                    @CURRENTAPPUSERID uniqueidentifier,
                    @DATALOADED bit = 0 output,
                    @NAME nvarchar(400) = null output,
                    @ISINACTIVE bit = null output,
                    @DECEASED bit = null output,
                    @ISSELF bit = null output,
                    @ISORG bit = null output,
                    @ISPROSPECT bit = null output,
                    @ISFORMERPROSPECT bit = null output,
                    @ISFUNDRAISER bit = null output,
                    @ISFORMERFUNDRAISER bit = null output,
                    @ISVOLUNTEER bit = null output,
                    @ISFORMERVOLUNTEER bit = null output,
                    @ISCOMMUNITYMEMBER bit = null output,
                    @ISREGISTRANT bit = null output,
                    @ISVENDOR bit = null output,
                    @APPUSERID uniqueidentifier = null output,
                    @ATTRIBUTEDEFINED bit = null output,
                    @SMARTFIELDDEFINED bit = null output,
                    @UPDATEPENDING bit = null output,
                    @CONSTITUENTID uniqueidentifier = null output,
                    @HASSPOUSE bit = null output,
                    @AGE int = null output,
                    @ISALUMNUS bit = null output,
                    @ISSTUDENT bit = null output,
                    @ISFORMERSTUDENT bit = null output,
                    @VALIDSEARCHCRITERIASTRICT bit = null output,
                    @VALIDSEARCHCRITERIARELAXED bit = null output,
                    @EDUCATIONATTRIBUTEDEFINED bit = null output,
                    @ISGROUPMEMBER bit = null output,
                    @ISGROUP bit = null output,
                    @GROUPCANBEDONOR bit = null output,
                    @CURRENTMEMBERCOUNT integer = null output,
                    @PREVIOUSMEMBERCOUNT integer = null output,
                    @HASCOMMUNITYDATA bit = null output,
                    @ISHOUSEHOLD bit = null output,
                    @HOUSEHOLDID uniqueidentifier = null output,
                    @MAPPINGCREDENTIALSEXIST bit = null output,
                    @HASSTEWARDSHIPPLAN bit = null output,
                    @DECEASINGOPTIONSSET bit = null output,
                    @ISDISSOLVED bit = null output,
                    @SPOUSERELATIONSHIPID uniqueidentifier = null output,
                    @ISPLANNEDGIVER bit = null output,
                    @ISPATRON bit = null output,
                    @ISBANK bit = null output,
                    @HASIND2ORGRELATIONS bit = null output,
                    @ISCOMMITTEE bit = null output,
                    @ISGRANTOR bit = null output,
                    @ISSPONSOR bit = null output,
                    @SPONSORTYPECODE tinyint = null output,
                    @ISCOMMITTEEMEMBER bit = null output,
                    @HASMULTIPLEMGCONDITIONS bit = null output,
                    @ISFACULTY bit = null output,
                    @ISCONSTITUENT bit = null output,
                    @CANBEDONOR bit = null output,
                    @ISFORMERSPONSOR bit = null output,
                    @USER_GRANTED_IND_TO_IND_REL_ADD_FOR_CONSTIT bit = null output,
                    @USER_GRANTED_CONSTITNOTENOTIFICATION_ADD bit = null output,
                    @USER_GRANTED_CONSTITNOTENOTIFICATION_EDIT bit = null output,
                    @USER_GRANTED_CONSTITNOTE_ADD bit = null output,
                    @USER_GRANTED_CONSTITMEDIALINK_ADD bit = null output,
                    @USER_GRANTED_CONSTITATTACHMENT_ADD bit = null output,
                    @USER_GRANTED_CONSTITNOTE_EDIT_2 bit = null output,
                    @USER_GRANTED_CONSTITMEDIALINK_EDIT bit = null output,
                    @USER_GRANTED_CONSTITATTACHMENT_EDIT bit = null output,
                    @USER_GRANTED_CONSTITATTACHMENT_VIEW bit = null output,
                    @USER_GRANTED_ADDRESS_GEOCODE bit = null output,
                    @USER_GRANTED_CONSTITNOTENOTIFICATION_DELETE bit = null output,
                    @USER_GRANTED_CONSTITNOTE_DELETE bit = null output,
                    @USER_GRANTED_CONSTITMEDIALINK_DELETE bit = null output,
                    @USER_GRANTED_CONSTITATTACHMENT_DELETE bit = null output,
                    @USER_GRANTED_ADDRESS_DELETE_FOR_CONSTIT bit = null output,
                    @USER_GRANTED_CONSTITUENT_MAPENTITY bit = null output,
                    @MARRIAGERULESSET bit = null output,
                    @ISADVOCATE  bit = null output,
                    @HASRECEIVEDFUNDS bit = null output,
                    @ADDRESSATTRIBUTEDEFINED bit = null output,
                    @ISFAFREGISTRANT bit = null output,
                    @VIEWREVENUETEXTLINKTEXT varchar(25) = null output,
                    @USER_GRANTED_CONSTITPERSONALINFO_EDIT bit = null output,
                    @MEMBERSHIPID uniqueidentifier = null output,
                    @ISNATIONALFUNDRAISINGGROUP  bit = null output,
                    @ISFUNDRAISINGGROUP bit = null output,
                    @ALLOWINTERACTIONS bit = null output,
                    @ISHOUSEHOLDMEMBER bit = null output,
                    @USER_GRANTED_REVENUESUMMARY_VIEW bit = null output,
                    @USER_GRANTED_HOUSEHOLDSUMMARY_VIEW bit = null output,
                    @USER_GRANTED_REVENUESUMMARYBRKDWN_VIEW bit = null output,
                    @USER_GRANTED_REVENUESUMMARYPARAMETER_VIEW bit = null output,
                    @HASREFUNDS bit = null output,
                    @SINGLESPONSORSHIP bit = null output,
                    @ROOTID uniqueidentifier = null output,
                    @ISHISTORICALCOMMITTEEMEMBER bit = null output,
                    @CANCOORDINATEEVENTS bit = null output,
                    @CANSOLICITREVENUE bit = null output,
                    @HASFUNDRAISINGGOALS bit = null output,
                    @HASCAMPAIGNS bit = null output,
                    @HASCOMMITTEEGOALS bit = null output,
                    @CANSETCOMMITTEEGOALS bit = null output,
                    @HASEVENTS bit = null output,
                    @HASPROSPECTMANAGER bit = null output,
                    @FLAGGED bit = null output,
                    @ALERTSENABLED bit = null output,
                    @NETCOMMUNITYCLIENTUSERID uniqueidentifier = null output,
                    @NETCOMMUNITYEMAILINFOEXISTS bit = null output,
                    @USER_GRANTED_ADDRESS_EDIT_FOR_CONSTIT bit = null output,
                    @USER_GRANTED_PHONENUMBER_EDIT_FOR_CONSTIT bit = null output,
                    @USER_GRANTED_PHONENUMBER_DELETE_FOR_CONSTIT bit = null output,
                    @USER_GRANTED_EMAILADDRESS_EDIT_FOR_CONSTIT bit = null output,
                    @USER_GRANTED_EMAILADDRESS_DELETE_FOR_CONSTIT bit = null output,
                    @USER_GRANTED_SOCIALMEDIAACCOUNT_EDIT_FOR_CONSTIT bit = null output,
                    @USER_GRANTED_SOCIALMEDIAACCOUNT_DELETE_FOR_CONSTIT bit = null output,
                    @HASCONSENTSOLICITCODE bit = null output
                )
                as
                    set nocount on;

                    set @DATALOADED = 0;

                    exec dbo.USP_CONSTITUENTGETEXPRESSIONDATA 
                        @ID, 
                        @CURRENTAPPUSERID output, 
                        @DATALOADED output, 
                        @NAME output, 
                        @ISINACTIVE output, 
                        @DECEASED output, 
                        @ISSELF output, 
                        @ISORG output, 
                        @ISPROSPECT output, 
                        @ISFORMERPROSPECT output, 
                        @ISFUNDRAISER output, 
                        @ISFORMERFUNDRAISER output, 
                        @ISVOLUNTEER output, 
                        @ISFORMERVOLUNTEER output, 
                        @ISCOMMUNITYMEMBER output, 
                        @ISREGISTRANT output, 
                        @ISVENDOR output, 
                        @APPUSERID output, 
                        @ATTRIBUTEDEFINED output, 
                        @SMARTFIELDDEFINED output, 
                        @UPDATEPENDING output, 
                        @CONSTITUENTID output, 
                        @HASSPOUSE output, 
                        @AGE output, 
                        @ISALUMNUS output, 
                        @ISSTUDENT output, 
                        @ISFORMERSTUDENT output, 
                        @VALIDSEARCHCRITERIASTRICT output, 
                        @VALIDSEARCHCRITERIARELAXED output, 
                        @EDUCATIONATTRIBUTEDEFINED output, 
                        @HASCOMMUNITYDATA output, 
                        @ISGROUPMEMBER output, 
                        @ISGROUP output, 
                        @GROUPCANBEDONOR output, 
                        @CURRENTMEMBERCOUNT output, 
                        @PREVIOUSMEMBERCOUNT output, 
                        @ISHOUSEHOLD output, 
                        @HOUSEHOLDID output, 
                        @MAPPINGCREDENTIALSEXIST output, 
                        @HASSTEWARDSHIPPLAN output, 
                        @DECEASINGOPTIONSSET output, 
                        @ISDISSOLVED output, 
                        @SPOUSERELATIONSHIPID output, 
                        @ISPLANNEDGIVER output, 
                        @ISPATRON output, 
                        @ISBANK output, 
                        default, 
                        @HASIND2ORGRELATIONS output, 
                        @ISCOMMITTEE output, 
                        @ISGRANTOR = @ISGRANTOR output, 
                        @ISSPONSOR = @ISSPONSOR output, 
                        @SPONSORTYPECODE = @SPONSORTYPECODE output, 
                        @ISCOMMITTEEMEMBER = @ISCOMMITTEEMEMBER output, 
                        @HASMULTIPLEMGCONDITIONS = @HASMULTIPLEMGCONDITIONS output, 
                        @ISFACULTY = @ISFACULTY output, 
                        @ISCONSTITUENT=@ISCONSTITUENT output, 
                        @CANBEDONOR = @CANBEDONOR output, 
                        @ISFORMERSPONSOR = @ISFORMERSPONSOR output, 
                        @HASRECEIVEDFUNDS = @HASRECEIVEDFUNDS output, 
                        @ADDRESSATTRIBUTEDEFINED = @ADDRESSATTRIBUTEDEFINED output, 
                        @USER_GRANTED_CONSTITPERSONALINFO_EDIT = @USER_GRANTED_CONSTITPERSONALINFO_EDIT output

                    select @MARRIAGERULESSET = RULESSET from dbo.UFN_MARRIAGEOPTION_GETRULES();

                    select    @HASCONSENTSOLICITCODE = 
                            case 
                                when COUNT(CONSTITUENTSOLICITCODE.ID) = 0 then 0
                                when COUNT(CONSTITUENTSOLICITCODE.ID) > 0 then 1
                            end
                    from    CONSTITUENTSOLICITCODE
                    join    dbo.SOLICITCODE ON SOLICITCODE.ID = CONSTITUENTSOLICITCODE.SOLICITCODEID
                    where    CONSTITUENTSOLICITCODE.CONSTITUENTID = @ID
                    and        (SOLICITCODE.CONSENTCODE = 1
                           or
                           SOLICITCODE.CONSENTCODE = 2);

                    Set @ISADVOCATE = dbo.UFN_CONSTITUENT_ISADVOCATE(@ID)

                    Set @ISNATIONALFUNDRAISINGGROUP = dbo.UFN_CONSTITUENT_ISNATIONALFUNDRAISINGGROUP(@ID)
                    Set @ISFUNDRAISINGGROUP = dbo.UFN_CONSTITUENT_ISFUNDRAISINGGROUP(@ID)

                    --check if FAF registrants

                    Set @ISFAFREGISTRANT = 0
                    Set @VIEWREVENUETEXTLINKTEXT = 'Revenue and recognition'

                    Set @ISFAFREGISTRANT = dbo.UFN_FAF_ISPARTICIPANTS(@ID)
                    if @ISFAFREGISTRANT = 1
                    Begin
                    Set @VIEWREVENUETEXTLINKTEXT = 'Gifts & registrations'
                    End

                    declare @MEMBERSHIPCOUNT int = (select count(MEMBERSHIP.ID) from dbo.MEMBERSHIP 
                                 inner join dbo.MEMBER on MEMBER.MEMBERSHIPID = MEMBERSHIP.ID
                                                 inner join dbo.MEMBER PRIMARYMEMBER on PRIMARYMEMBER.MEMBERSHIPID = MEMBERSHIP.ID and PRIMARYMEMBER.ISPRIMARY = 1
                                                 where MEMBER.CONSTITUENTID = @ID)
                    if @MEMBERSHIPCOUNT = 1
                    set @MEMBERSHIPID = (select MEMBERSHIP.ID from dbo.MEMBERSHIP 
                                 inner join dbo.MEMBER on MEMBER.MEMBERSHIPID = MEMBERSHIP.ID
                                                 inner join dbo.MEMBER PRIMARYMEMBER on PRIMARYMEMBER.MEMBERSHIPID = MEMBERSHIP.ID and PRIMARYMEMBER.ISPRIMARY = 1
                                                 where MEMBER.CONSTITUENTID = @ID)

                    set @ISHOUSEHOLDMEMBER = case when 
                        (
                            select 
                                count(*) 
                            from 
                                dbo.GROUPDATA 
                                inner join dbo.GROUPMEMBER on GROUPDATA.ID = GROUPMEMBER.GROUPID 
                            where 
                                GROUPDATA.GROUPTYPECODE = 0 
                                and GROUPMEMBER.MEMBERID = @ID 
                                and dbo.UFN_GROUPMEMBER_ISCURRENTMEMBER(GROUPMEMBER.ID) = 1
                        ) > 0 
                        then 1 
                        else 0 
                    end;

                    --TWG 09/28/2010

                    --Adding permission checks for features that are conditionally shown on the page. Not including in the common procedure because these will differ from page to page

                    if dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 begin
                        set @USER_GRANTED_IND_TO_IND_REL_ADD_FOR_CONSTIT = 1;
                        set @USER_GRANTED_CONSTITNOTENOTIFICATION_ADD = 1;
                        set @USER_GRANTED_CONSTITNOTENOTIFICATION_EDIT = 1;
                        set @USER_GRANTED_CONSTITNOTE_ADD = 1;
                        set @USER_GRANTED_CONSTITMEDIALINK_ADD = 1;
                        set @USER_GRANTED_CONSTITATTACHMENT_ADD = 1;
                        set @USER_GRANTED_CONSTITNOTE_EDIT_2 = 1;
                        set @USER_GRANTED_CONSTITMEDIALINK_EDIT = 1;
                        set @USER_GRANTED_CONSTITATTACHMENT_EDIT = 1;
                        set @USER_GRANTED_CONSTITATTACHMENT_VIEW = 1;
                        set @USER_GRANTED_ADDRESS_GEOCODE = 1;
                        set @USER_GRANTED_CONSTITNOTENOTIFICATION_DELETE = 1;
                        set @USER_GRANTED_CONSTITNOTE_DELETE = 1;
                        set @USER_GRANTED_CONSTITMEDIALINK_DELETE = 1;
                        set @USER_GRANTED_CONSTITATTACHMENT_DELETE = 1;
                        set @USER_GRANTED_ADDRESS_DELETE_FOR_CONSTIT = 1;
                        set @USER_GRANTED_CONSTITUENT_MAPENTITY = 1;
                        set @USER_GRANTED_REVENUESUMMARY_VIEW = 1;
                        set @USER_GRANTED_HOUSEHOLDSUMMARY_VIEW = 1;
                        set @USER_GRANTED_REVENUESUMMARYBRKDWN_VIEW = 1;
                        set @USER_GRANTED_REVENUESUMMARYPARAMETER_VIEW = 1;
            set @USER_GRANTED_ADDRESS_EDIT_FOR_CONSTIT = 1;
          set @USER_GRANTED_PHONENUMBER_EDIT_FOR_CONSTIT = 1;
            set @USER_GRANTED_PHONENUMBER_DELETE_FOR_CONSTIT = 1;
            set @USER_GRANTED_EMAILADDRESS_EDIT_FOR_CONSTIT = 1;
            set @USER_GRANTED_EMAILADDRESS_DELETE_FOR_CONSTIT = 1;
            set @USER_GRANTED_SOCIALMEDIAACCOUNT_EDIT_FOR_CONSTIT = 1;
            set @USER_GRANTED_SOCIALMEDIAACCOUNT_DELETE_FOR_CONSTIT = 1;
                    end
                    else begin
                        set @USER_GRANTED_IND_TO_IND_REL_ADD_FOR_CONSTIT = case
                            when
                                dbo.UFN_SECURITY_APPUSER_GRANTED_FORM_IN_SYSTEMROLE(@CURRENTAPPUSERID,'50D61839-6DB9-4c33-8080-69FAD56B533D') = 1 and
                                dbo.UFN_SECURITY_APPUSER_GRANTED_FORM_FORCONSTIT(@CURRENTAPPUSERID,'50D61839-6DB9-4c33-8080-69FAD56B533D',@CONSTITUENTID) = 1 and
                                (select
                                    count(*)
                                from
                                    dbo.UFN_SITEID_MAPFROM_CONSTITUENTID(@CONSTITUENTID) as SITES
                                where
                                    dbo.UFN_SECURITY_APPUSER_GRANTED_FORM_FORSITE(@CURRENTAPPUSERID, '50D61839-6DB9-4c33-8080-69FAD56B533D', SITEID) = 1) > 0
                            then 1
                            else 0
                            end;
                        set @USER_GRANTED_CONSTITNOTENOTIFICATION_ADD = dbo.UFN_SECURITY_APPUSER_GRANTED_FORM_IN_SYSTEMROLE(@CURRENTAPPUSERID,'8e43e74b-cd0a-4e4e-b02b-f130639b1ae6');
                        set @USER_GRANTED_CONSTITNOTENOTIFICATION_EDIT = dbo.UFN_SECURITY_APPUSER_GRANTED_FORM_IN_SYSTEMROLE(@CURRENTAPPUSERID,'0a91f5b8-15c9-41fc-b76b-f1004586e95a');
                        set @USER_GRANTED_CONSTITNOTE_ADD = dbo.UFN_SECURITY_APPUSER_GRANTED_FORM_IN_SYSTEMROLE(@CURRENTAPPUSERID,'AC0ECD28-AF8A-469b-A5F0-03034D9D9A70');
                        set @USER_GRANTED_CONSTITMEDIALINK_ADD = dbo.UFN_SECURITY_APPUSER_GRANTED_FORM_IN_SYSTEMROLE(@CURRENTAPPUSERID,'E9843191-4CDF-4018-974F-4ECD61201A58');
                        set @USER_GRANTED_CONSTITATTACHMENT_ADD = dbo.UFN_SECURITY_APPUSER_GRANTED_FORM_IN_SYSTEMROLE(@CURRENTAPPUSERID,'5F7F892C-74FC-4783-A327-25EA0D23E89F');
                        set @USER_GRANTED_CONSTITNOTE_EDIT_2 = dbo.UFN_SECURITY_APPUSER_GRANTED_FORM_IN_SYSTEMROLE(@CURRENTAPPUSERID,'6F6980CD-9189-4590-BF55-12BC2AB99FFD');
                        set @USER_GRANTED_CONSTITMEDIALINK_EDIT = dbo.UFN_SECURITY_APPUSER_GRANTED_FORM_IN_SYSTEMROLE(@CURRENTAPPUSERID,'23E6F3CB-2EF6-4CBE-BEF5-28D099627E94');
                        set @USER_GRANTED_CONSTITATTACHMENT_EDIT = dbo.UFN_SECURITY_APPUSER_GRANTED_FORM_IN_SYSTEMROLE(@CURRENTAPPUSERID,'4D478F30-74E0-418A-893F-2622BC89767F');
                        set @USER_GRANTED_CONSTITATTACHMENT_VIEW = dbo.UFN_SECURITY_APPUSER_GRANTED_FORM_IN_SYSTEMROLE(@CURRENTAPPUSERID,'7126725A-12F2-4A37-A992-10891CEEA336');
                        set @USER_GRANTED_ADDRESS_GEOCODE = dbo.UFN_SECURITY_APPUSER_GRANTED_RECORDOPERATION_IN_SYSTEMROLE(@CURRENTAPPUSERID,'1783224D-E771-437F-AAE4-A3981AE84543');
                        set @USER_GRANTED_CONSTITNOTENOTIFICATION_DELETE = dbo.UFN_SECURITY_APPUSER_GRANTED_RECORDOPERATION_IN_SYSTEMROLE(@CURRENTAPPUSERID,'ad442665-44f3-4d73-a5e1-722757ee69e8');
                        set @USER_GRANTED_CONSTITNOTE_DELETE = dbo.UFN_SECURITY_APPUSER_GRANTED_RECORDOPERATION_IN_SYSTEMROLE(@CURRENTAPPUSERID,'E135A349-2F3C-44ba-9C14-557E1AD02A4D');
                        set @USER_GRANTED_CONSTITMEDIALINK_DELETE = dbo.UFN_SECURITY_APPUSER_GRANTED_RECORDOPERATION_IN_SYSTEMROLE(@CURRENTAPPUSERID,'DBA025EF-6507-455B-887F-F1D66B2A13DC');
                        set @USER_GRANTED_CONSTITATTACHMENT_DELETE = dbo.UFN_SECURITY_APPUSER_GRANTED_RECORDOPERATION_IN_SYSTEMROLE(@CURRENTAPPUSERID,'3E7278FB-46D4-401E-8E7E-ADF794C54D4F');
            set @USER_GRANTED_ADDRESS_EDIT_FOR_CONSTIT = case
                            when
                                dbo.UFN_SECURITY_APPUSER_GRANTED_FORM_IN_SYSTEMROLE(@CURRENTAPPUSERID,'9BEF22AB-496F-48FD-98FE-5BE72270EF30') = 1 and
                                dbo.UFN_SECURITY_APPUSER_GRANTED_FORM_FORCONSTIT(@CURRENTAPPUSERID,'9BEF22AB-496F-48FD-98FE-5BE72270EF30',@CONSTITUENTID) = 1 and
                                (select
                                    count(*)
                                from
                                    dbo.UFN_SITEID_MAPFROM_CONSTITUENTID(@CONSTITUENTID) as SITES
                                where
                                    dbo.UFN_SECURITY_APPUSER_GRANTED_FORM_FORSITE(@CURRENTAPPUSERID, '9BEF22AB-496F-48FD-98FE-5BE72270EF30', SITEID) = 1) > 0
                            then 1
                            else 0
                            end;
                        set @USER_GRANTED_ADDRESS_DELETE_FOR_CONSTIT = case
                            when
                                dbo.UFN_SECURITY_APPUSER_GRANTED_RECORDOPERATION_IN_SYSTEMROLE(@CURRENTAPPUSERID,'0C2D6552-EC7F-4923-A4AF-AFC53114C32B') = 1 and
                                dbo.UFN_SECURITY_APPUSER_GRANTED_RECORDOPERATION_FORCONSTIT(@CURRENTAPPUSERID,'0C2D6552-EC7F-4923-A4AF-AFC53114C32B',@CONSTITUENTID) = 1 and
                                (select
                                    count(*)
                                from
                                    dbo.UFN_SITEID_MAPFROM_CONSTITUENTID(@CONSTITUENTID) as SITES
                                where
                                    dbo.UFN_SECURITY_APPUSER_GRANTED_RECORDOPERATION_FORSITE(@CURRENTAPPUSERID, '0C2D6552-EC7F-4923-A4AF-AFC53114C32B', SITEID) = 1) > 0
                            then 1
                            else 0
                            end;
            set @USER_GRANTED_PHONENUMBER_EDIT_FOR_CONSTIT = case
                            when
                                dbo.UFN_SECURITY_APPUSER_GRANTED_FORM_IN_SYSTEMROLE(@CURRENTAPPUSERID,'DEBC671C-7089-4D3C-81E8-A49E32D3ED54') = 1 and
                                dbo.UFN_SECURITY_APPUSER_GRANTED_FORM_FORCONSTIT(@CURRENTAPPUSERID,'DEBC671C-7089-4D3C-81E8-A49E32D3ED54',@CONSTITUENTID) = 1 and
                                (select
                                    count(*)
                                from
                                    dbo.UFN_SITEID_MAPFROM_CONSTITUENTID(@CONSTITUENTID) as SITES
                                where
                                    dbo.UFN_SECURITY_APPUSER_GRANTED_FORM_FORSITE(@CURRENTAPPUSERID, 'DEBC671C-7089-4D3C-81E8-A49E32D3ED54', SITEID) = 1) > 0
                            then 1
                            else 0
                            end;
                        set @USER_GRANTED_PHONENUMBER_DELETE_FOR_CONSTIT = case
                            when
                                dbo.UFN_SECURITY_APPUSER_GRANTED_RECORDOPERATION_IN_SYSTEMROLE(@CURRENTAPPUSERID,'9607C710-462B-42a1-BF62-9B435786B3F6') = 1 and
                                dbo.UFN_SECURITY_APPUSER_GRANTED_RECORDOPERATION_FORCONSTIT(@CURRENTAPPUSERID,'9607C710-462B-42a1-BF62-9B435786B3F6',@CONSTITUENTID) = 1 and
                                (select
                                    count(*)
                                from
                                    dbo.UFN_SITEID_MAPFROM_CONSTITUENTID(@CONSTITUENTID) as SITES
                                where
                                    dbo.UFN_SECURITY_APPUSER_GRANTED_RECORDOPERATION_FORSITE(@CURRENTAPPUSERID, '9607C710-462B-42a1-BF62-9B435786B3F6', SITEID) = 1) > 0
                            then 1
                            else 0
                            end;
            set @USER_GRANTED_EMAILADDRESS_EDIT_FOR_CONSTIT = case
                            when
                                dbo.UFN_SECURITY_APPUSER_GRANTED_FORM_IN_SYSTEMROLE(@CURRENTAPPUSERID,'14c40226-2dab-49fe-b92f-c5200f0b59a9') = 1 and
                                dbo.UFN_SECURITY_APPUSER_GRANTED_FORM_FORCONSTIT(@CURRENTAPPUSERID,'14c40226-2dab-49fe-b92f-c5200f0b59a9',@CONSTITUENTID) = 1 and
                                (select
                                    count(*)
                                from
                                    dbo.UFN_SITEID_MAPFROM_CONSTITUENTID(@CONSTITUENTID) as SITES
                                where
                                    dbo.UFN_SECURITY_APPUSER_GRANTED_FORM_FORSITE(@CURRENTAPPUSERID, '14c40226-2dab-49fe-b92f-c5200f0b59a9', SITEID) = 1) > 0
                            then 1
                            else 0
                            end;
                        set @USER_GRANTED_EMAILADDRESS_DELETE_FOR_CONSTIT = case
                            when
                                dbo.UFN_SECURITY_APPUSER_GRANTED_RECORDOPERATION_IN_SYSTEMROLE(@CURRENTAPPUSERID,'2A7DE962-CEAE-44A2-9F5D-C064096433BE') = 1 and
                                dbo.UFN_SECURITY_APPUSER_GRANTED_RECORDOPERATION_FORCONSTIT(@CURRENTAPPUSERID,'2A7DE962-CEAE-44A2-9F5D-C064096433BE',@CONSTITUENTID) = 1 and
                                (select
                                    count(*)
                                from
                                    dbo.UFN_SITEID_MAPFROM_CONSTITUENTID(@CONSTITUENTID) as SITES
                                where
                                    dbo.UFN_SECURITY_APPUSER_GRANTED_RECORDOPERATION_FORSITE(@CURRENTAPPUSERID, '2A7DE962-CEAE-44A2-9F5D-C064096433BE', SITEID) = 1) > 0
                            then 1
                            else 0
                            end;
            set @USER_GRANTED_SOCIALMEDIAACCOUNT_EDIT_FOR_CONSTIT = case
                            when
                                dbo.UFN_SECURITY_APPUSER_GRANTED_FORM_IN_SYSTEMROLE(@CURRENTAPPUSERID,'9c2a473f-4c66-44b8-a36f-6b1fc907c9e4') = 1 and
                                dbo.UFN_SECURITY_APPUSER_GRANTED_FORM_FORCONSTIT(@CURRENTAPPUSERID,'9c2a473f-4c66-44b8-a36f-6b1fc907c9e4',@CONSTITUENTID) = 1 and
                                (select
                                    count(*)
                                from
                                    dbo.UFN_SITEID_MAPFROM_CONSTITUENTID(@CONSTITUENTID) as SITES
                                where
                                    dbo.UFN_SECURITY_APPUSER_GRANTED_FORM_FORSITE(@CURRENTAPPUSERID, '9c2a473f-4c66-44b8-a36f-6b1fc907c9e4', SITEID) = 1) > 0
                            then 1
                            else 0
                            end;
                        set @USER_GRANTED_SOCIALMEDIAACCOUNT_DELETE_FOR_CONSTIT = case
                            when
                                dbo.UFN_SECURITY_APPUSER_GRANTED_RECORDOPERATION_IN_SYSTEMROLE(@CURRENTAPPUSERID,'3F964292-65BA-4595-946D-BA42D683BA89') = 1 and
                                dbo.UFN_SECURITY_APPUSER_GRANTED_RECORDOPERATION_FORCONSTIT(@CURRENTAPPUSERID,'3F964292-65BA-4595-946D-BA42D683BA89',@CONSTITUENTID) = 1 and
                                (select
                                    count(*)
                                from
                                    dbo.UFN_SITEID_MAPFROM_CONSTITUENTID(@CONSTITUENTID) as SITES
                                where
                                    dbo.UFN_SECURITY_APPUSER_GRANTED_RECORDOPERATION_FORSITE(@CURRENTAPPUSERID, '3F964292-65BA-4595-946D-BA42D683BA89', SITEID) = 1) > 0
                            then 1
                            else 0
                            end;

                        /*If ResearchPoint use RP mapping*/
                        if (dbo.UFN_INSTALLEDPRODUCTS_PRODUCTIS('6f77d512-d0d1-444f-9b46-b8603a6fe5f1') = 1)
                              set @USER_GRANTED_CONSTITUENT_MAPENTITY = dbo.UFN_SECURITY_APPUSER_GRANTED_MAPENTITY_IN_SYSTEMROLE(@CURRENTAPPUSERID,'78102CE5-F217-4429-9175-D99A6B55A4EE');
                        else
                              set @USER_GRANTED_CONSTITUENT_MAPENTITY = dbo.UFN_SECURITY_APPUSER_GRANTED_MAPENTITY_IN_SYSTEMROLE(@CURRENTAPPUSERID,'645E6BE7-459C-402b-A03C-67587CA72B94');

                        set @USER_GRANTED_REVENUESUMMARY_VIEW = dbo.UFN_SECURITY_APPUSER_GRANTED_DATALIST_IN_SYSTEMROLE(@CURRENTAPPUSERID,'422f6423-a449-4db9-9877-140094ab77ce');
                        set @USER_GRANTED_HOUSEHOLDSUMMARY_VIEW = dbo.UFN_SECURITY_APPUSER_GRANTED_DATALIST_IN_SYSTEMROLE(@CURRENTAPPUSERID,'deae7918-1537-449f-a2b5-2e37a1230232');
                        set @USER_GRANTED_REVENUESUMMARYBRKDWN_VIEW = dbo.UFN_SECURITY_APPUSER_GRANTED_DATALIST_IN_SYSTEMROLE(@CURRENTAPPUSERID,'0F09502E-A278-4902-BF47-EC5D9A18F4E4');

                        set @USER_GRANTED_REVENUESUMMARYPARAMETER_VIEW = 
                            case
                                when @USER_GRANTED_REVENUESUMMARY_VIEW = 1 then 1
                                when @ISHOUSEHOLDMEMBER = 1 and @USER_GRANTED_HOUSEHOLDSUMMARY_VIEW = 1 then 1
                                when @USER_GRANTED_REVENUESUMMARYBRKDWN_VIEW = 1 then 1
                                else 0
                            end;
                    end

                    set @ALLOWINTERACTIONS = 
                    case when dbo.UFN_INSTALLEDPRODUCTS_PRODUCTIS('3117d2c8-7f46-42f2-abeb-b654f2f63046') = 1
                        or dbo.UFN_INSTALLEDPRODUCTS_PRODUCTIS('42c15648-749e-4859-a56d-3a6474814cc7') = 1
                        or dbo.UFN_INSTALLEDPRODUCTS_PRODUCTIS('f5ac53c4-d0ce-4e20-bca6-aacdfc01b302') = 1
                    then 1
                    else 0
                    end;

                    set @HASREFUNDS = 0;

                    if exists 
                    (
                        select 
                            [CREDIT].[ID] 
                        from 
                            dbo.[CREDIT]
                        where 
                            [CREDIT].[CONSTITUENTID] = @ID 
                            and [CREDIT].[TYPECODE] = 0
                    )
                        set @HASREFUNDS = 1;

                    declare @RECORDSCOUNT int;
                    select @RECORDSCOUNT = count(ID) from dbo.SPONSORSHIPCOMMITMENT where CONSTITUENTID = @ID;
                    set @SINGLESPONSORSHIP = 0;
                    if @RECORDSCOUNT = 1
                        set @SINGLESPONSORSHIP = 1;

                    set @ROOTID = dbo.UFN_CORPORATESTRUCTURE_GETROOT(@ID);

                    select
                        @ISHISTORICALCOMMITTEEMEMBER = 
                            case
                                when count(1) >= 1 then 1
                                else 0
                            end
                    from
                        dbo.GROUPMEMBER
                    where
                        GROUPMEMBER.MEMBERID = @ID
                        and dbo.UFN_CONSTITUENT_ISCOMMITTEE(GROUPMEMBER.GROUPID) = 1

                    select @CANCOORDINATEEVENTS = CANCOORDINATEEVENTS,
                    @CANSOLICITREVENUE = CANSOLICITREVENUE,
                    @CANSETCOMMITTEEGOALS = CANSETCOMMITTEEGOALS
                    from dbo.COMMITTEE where ID = @ID;

                    if exists(select top 1 ID from dbo.EVENTCOORDINATOR where CONSTITUENTID = @ID)
                        set @HASEVENTS = 1;
                    else
                        set @HASEVENTS = 0;

                    if exists(select top 1 ID from dbo.UFN_GROUPGOAL_DATALIST(@ID,null) where ISFUNDRAISINGGOAL = 1)
                        set @HASFUNDRAISINGGOALS = 1;
                    else
                        set @HASFUNDRAISINGGOALS = 0;

                    if exists(select top 1 ID from dbo.CAMPAIGNFUNDRAISER where CONSTITUENTID =@ID)
                        set @HASCAMPAIGNS = 1;
                    else
                        set @HASCAMPAIGNS = 0;

                    if exists(select top 1 ID from dbo.UFN_GROUPGOAL_DATALIST(@ID,null) where ISFUNDRAISINGGOAL = 0)
                        set @HASCOMMITTEEGOALS = 1;
                    else
                        set @HASCOMMITTEEGOALS = 0;

                    declare @CURRENTDATEEARLIESTTIME datetime;
                    set @CURRENTDATEEARLIESTTIME = dbo.UFN_DATE_GETEARLIESTTIME(getdate());

                    select
                        @HASPROSPECTMANAGER =
                            case
                                when PROSPECTMANAGERFUNDRAISERID is null or PROSPECTMANAGERENDDATE < @CURRENTDATEEARLIESTTIME then 0
                                else 1
                            end
                    from
                        dbo.PROSPECT
                    where
                        ID = @ID;

                    select
                        @FLAGGED =
                            case
                                when FP.ID is null then 0
                                else 1
                            end
                    from
                        dbo.FLAGGEDPROSPECT FP
                    where
                        FP.APPUSERID=@CURRENTAPPUSERID
                        and FP.PROSPECTID=@ID;

                    select @ALERTSENABLED = ENABLED from dbo.DATABASEMAILSETTINGS;

                    select top (1)
                        @NETCOMMUNITYCLIENTUSERID = [NETCOMMUNITYCLIENTUSER].[ID],
                        @NETCOMMUNITYEMAILINFOEXISTS =
                            case
                                when [NETCOMMUNITYEMAILINFO].[ID] is null then 0
                                else 1
                            end
                    from
                        dbo.NETCOMMUNITYCLIENTUSER
                        left join dbo.NETCOMMUNITYEMAILINFO on [NETCOMMUNITYCLIENTUSER].[ID] = [NETCOMMUNITYEMAILINFO].[NETCOMMUNITYCLIENTUSERID]
                    where
                        [NETCOMMUNITYCLIENTUSER].[CONSTITUENTID] = @ID
                        and [NETCOMMUNITYCLIENTUSER].[ACTIVE] = 1
                        and [NETCOMMUNITYCLIENTUSER].[DELETED] = 0
                    order by
                        [NETCOMMUNITYCLIENTUSER].[USERUPDATEDATE] desc;

                    return 0;