USP_DATAFORMTEMPLATE_VIEW_PROSPECTWPPAGEDATA

The load procedure used by the view dataform template "Wealth and Ratings 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
@FLAGGED	bit	INOUT	FLAGGED
@HASWEALTHPROFILE	bit	INOUT	HASWEALTHPROFILE
@CONSTITUENTID	uniqueidentifier	INOUT	CONSTITUENTID
@UPDATEPENDING	bit	INOUT	UPDATEPENDING
@LASTWEALTHUPDATE	datetime	INOUT	LASTWEALTHUPDATE
@DISABLEDWEALTHUPDATES	bit	INOUT	DISABLEDWEALTHUPDATES
@ISPROSPECT	bit	INOUT	ISPROSPECT
@VALIDSEARCHCRITERIASTRICT	bit	INOUT	VALIDSEARCHCRITERIASTRICT
@VALIDSEARCHCRITERIARELAXED	bit	INOUT	VALIDSEARCHCRITERIARELAXED
@ISFORMERPROSPECT	bit	INOUT	ISFORMERPROSPECT
@ISFUNDRAISER	bit	INOUT	ISFUNDRAISER
@ISFORMERFUNDRAISER	bit	INOUT	ISFORMERFUNDRAISER
@ISVOLUNTEER	bit	INOUT	ISVOLUNTEER
@ISFORMERVOLUNTEER	bit	INOUT	ISFORMERVOLUNTEER
@ISREGISTRANT	bit	INOUT	ISREGISTRANT
@ISVENDOR	bit	INOUT	ISVENDOR
@ISALUMNUS	bit	INOUT	ISALUMNUS
@ISSTUDENT	bit	INOUT	ISSTUDENT
@ISFORMERSTUDENT	bit	INOUT	ISFORMERSTUDENT
@APPUSERID	uniqueidentifier	INOUT	APPUSERID
@HASCOMMUNITYDATA	bit	INOUT	HASCOMMUNITYDATA
@CONFIRMEDWEALTHCAPACITY	bit	INOUT	CONFIRMEDWEALTHCAPACITY
@WEALTHCAPACITYEXISTS	bit	INOUT	WEALTHCAPACITYEXISTS
@ISINACTIVE	bit	INOUT	ISINACTIVE
@BIOGRAPHICALSUFFIX	nvarchar(13)	INOUT	BIOGRAPHICALSUFFIX
@POLITICALDONATIONSSUFFIX	nvarchar(13)	INOUT	POLITICALDONATIONSSUFFIX
@PRIVATEFOUNDATIONSSUFFIX	nvarchar(13)	INOUT	PRIVATEFOUNDATIONSSUFFIX
@INCOMECOMPENSATIONSUFFIX	nvarchar(13)	INOUT	INCOMECOMPENSATIONSUFFIX
@AFFLUENCEINDICATORSSUFFIX	nvarchar(13)	INOUT	AFFLUENCEINDICATORSSUFFIX
@NONPROFITAFFILIATIONSSUFFIX	nvarchar(13)	INOUT	NONPROFITAFFILIATIONSSUFFIX
@PHILANTHROPICGIFTSSUFFIX	nvarchar(13)	INOUT	PHILANTHROPICGIFTSSUFFIX
@BUSINESSOWNERSHIPSUFFIX	nvarchar(13)	INOUT	BUSINESSOWNERSHIPSUFFIX
@REALESTATESUFFIX	nvarchar(13)	INOUT	REALESTATESUFFIX
@SECURITIESSUFFIX	nvarchar(13)	INOUT	SECURITIESSUFFIX
@OTHERASSETSSUFFIX	nvarchar(13)	INOUT	OTHERASSETSSUFFIX
@NEWBIOGRAPHICAL	bit	INOUT	NEWBIOGRAPHICAL
@NEWPOLITICALDONATIONS	bit	INOUT	NEWPOLITICALDONATIONS
@NEWPRIVATEFOUNDATIONS	bit	INOUT	NEWPRIVATEFOUNDATIONS
@NEWINCOMECOMPENSATION	bit	INOUT	NEWINCOMECOMPENSATION
@NEWAFFLUENCEINDICATORS	bit	INOUT	NEWAFFLUENCEINDICATORS
@NEWNONPROFITAFFILIATIONS	bit	INOUT	NEWNONPROFITAFFILIATIONS
@NEWPHILANTHROPICGIFTS	bit	INOUT	NEWPHILANTHROPICGIFTS
@NEWBUSINESSOWNERSHIP	bit	INOUT	NEWBUSINESSOWNERSHIP
@NEWREALESTATE	bit	INOUT	NEWREALESTATE
@NEWSECURITIES	bit	INOUT	NEWSECURITIES
@NEWOTHERASSETS	bit	INOUT	NEWOTHERASSETS
@SYSTEMCALCULATEDWEALTHCAPACITY	bit	INOUT	SYSTEMCALCULATEDWEALTHCAPACITY
@HASWEALTHPOINTCREDENTIALS	bit	INOUT	HASWEALTHPOINTCREDENTIALS
@LINKEDTORE7	bit	INOUT	LINKEDTORE7
@ISORG	bit	INOUT	ISORG
@ISGROUPMEMBER	bit	INOUT	ISGROUPMEMBER
@ISGROUP	bit	INOUT	ISGROUP
@ISHOUSEHOLD	bit	INOUT	ISHOUSEHOLD
@RE7SECURITYFEATURESGRANTED	bit	INOUT	RE7SECURITYFEATURESGRANTED
@WEALTHPOINTREGISTERED	bit	INOUT	WEALTHPOINTREGISTERED
@WEALTHPOINTACCOUNTTYPECODE	int	INOUT	WEALTHPOINTACCOUNTTYPECODE
@HASSTEWARDSHIPPLAN	bit	INOUT	HASSTEWARDSHIPPLAN
@ISBANK	bit	INOUT	ISBANK
@ISCOMMITTEE	bit	INOUT	ISCOMMITTEE
@ISGRANTOR	bit	INOUT	ISGRANTOR
@ISSPONSOR	bit	INOUT	ISSPONSOR
@ISCOMMITTEEMEMBER	bit	INOUT	ISCOMMITTEEMEMBER
@SPONSORTYPECODE	tinyint	INOUT	SPONSORTYPECODE
@ISPLANNEDGIVER	bit	INOUT	ISPLANNEDGIVER
@ISFACULTY	bit	INOUT	ISFACULTY
@ISFORMERSPONSOR	bit	INOUT	ISFORMERSPONSOR
@CANVIEWBIOGRAPHICAL	bit	INOUT	CANVIEWBIOGRAPHICAL
@CANVIEWCAMPAIGN	bit	INOUT	CANVIEWCAMPAIGN
@CANVIEWFOUNDATION	bit	INOUT	CANVIEWFOUNDATION
@CANVIEWGIFT	bit	INOUT	CANVIEWGIFT
@CANVIEWINCOME	bit	INOUT	CANVIEWINCOME
@CANVIEWAFFLUENCEINDICATORS	bit	INOUT	CANVIEWAFFLUENCEINDICATORS
@CANVIEWNONPROFIT	bit	INOUT	CANVIEWNONPROFIT
@CANVIEWOTHERASSETS	bit	INOUT	CANVIEWOTHERASSETS
@CANVIEWBUSINESS	bit	INOUT	CANVIEWBUSINESS
@CANVIEWREALESTATE	bit	INOUT	CANVIEWREALESTATE
@CANVIEWSECURITIES	bit	INOUT	CANVIEWSECURITIES
@HASRECEIVEDFUNDS	bit	INOUT	HASRECEIVEDFUNDS
@USER_GRANTED_CONSTITPERSONALINFO_EDIT	bit	INOUT
@RE7RECORDID	int	INOUT
@LASTRESEARCHDETAILSUPDATE	datetime	INOUT
@LASTRESEARCHEDBY	nvarchar(100)	INOUT
@CANVIEWGIVINGTOMYORG	bit	INOUT
@HASGIVINGSUMMARY	bit	INOUT
@N_GIFTS	int	INOUT
@USER_GRANTED_ADDRESS_DELETE_FOR_CONSTIT	bit	INOUT
@VIEWAS_ADVOCATECONSTITUENCYTEXT	nvarchar(100)	INOUT
@VIEWAS_BOARDMEMBERCONSTITUENCYTEXT	nvarchar(100)	INOUT
@VIEWAS_COMMITTEECONSTITUENCYTEXT	nvarchar(100)	INOUT
@VIEWAS_STAFFCONSTITUENCYTEXT	nvarchar(100)	INOUT
@VIEWAS_FUNDRAISERCONSTITUENCYTEXT	nvarchar(100)	INOUT
@VIEWAS_PROSPECTCONSTITUENCYTEXT	nvarchar(100)	INOUT
@VIEWAS_VOLUNTEERCONSTITUENCYTEXT	nvarchar(100)	INOUT
@ISADVOCATE	bit	INOUT
@MAPPINGCREDENTIALSEXIST	bit	INOUT
@USER_GRANTED_ADDRESS_GEOCODE	bit	INOUT
@USER_GRANTED_CONSTITUENT_MAPENTITY	bit	INOUT
@ADDRESSATTRIBUTEDEFINED	bit	INOUT
@ISOLDPAGE	bit	INOUT
@HASIND2ORGRELATIONS	bit	INOUT
@ISNATIONALFUNDRAISINGGROUP	bit	INOUT
@ENTERPRISE_FLAG	bit	INOUT
@DECEASED	bit	INOUT
@DECEASINGOPTIONSSET	bit	INOUT
@AGE	int	INOUT
@HASSPOUSE	bit	INOUT
@MARRIAGERULESSET	bit	INOUT
@ISDISSOLVED	bit	INOUT
@SPOUSERELATIONSHIPID	uniqueidentifier	INOUT
@SYSADMIN	bit	INOUT
@HASRESEARCHDETAILSFUNDRAISER	bit	INOUT
@MODELSCOREANDCUSTOMRATINGCOUNT	int	INOUT
@PENDINGSEARCH	bit	INOUT
@REALESTATEASSETS	money	INOUT
@C_REALESTATEASSETS	money	INOUT
@BUSINESSASSETS	money	INOUT
@C_BUSINESSASSETS	money	INOUT
@SECURITIESASSETS	money	INOUT
@C_SECURITIESASSETS	money	INOUT
@INCOME	money	INOUT
@C_INCOME	money	INOUT
@NAFFLUENCEINDICATORS	int	INOUT
@C_NAFFLUENCEINDICATORS	int	INOUT
@OTHERASSETS	money	INOUT
@C_OTHERASSETS	money	INOUT
@TOTALGIVING	money	INOUT
@LARGESTGIFT	money	INOUT
@PHILANTHROPIC	money	INOUT
@C_PHILANTHROPIC	money	INOUT
@POLITICAL	money	INOUT
@C_POLITICAL	money	INOUT
@NONPROFITAFFILIATIONS	int	INOUT
@C_NONPROFITAFFILIATIONS	int	INOUT
@FOUNDATIONAFFILIATIONS	int	INOUT
@C_FOUNDATIONAFFILIATIONS	int	INOUT
@NETWORKCONNECTIONS	int	INOUT
@C_NETWORKCONNECTIONS	int	INOUT
@BIOGRAPHICALNUMBER	int	INOUT
@C_BIOGRAPHICALNUMBER	int	INOUT
@USESUBCAPTION	bit	INOUT
@RE7CANVIEW	bit	INOUT
@RE7WEBCONFIGURED	bit	INOUT
@CANVIEWRE7ADDRESSES	bit	INOUT
@CANVIEWRE7PHONES	bit	INOUT
@LASTSOCIALUPDATE	datetime	INOUT
@HASSOCIALINFORMATION	bit	INOUT
@HIDETEASER	bit	INOUT

Definition

Copy


CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_PROSPECTWPPAGEDATA

    @ID uniqueidentifier,
    @CURRENTAPPUSERID            uniqueidentifier,
    @DATALOADED                    bit =                0 output,
    @NAME                        nvarchar(400) =        null output,
    @FLAGGED                    bit =                null output,
    @HASWEALTHPROFILE            bit =                null output,

    @CONSTITUENTID                uniqueidentifier =    null output,
    @UPDATEPENDING                bit =                null output,
    @LASTWEALTHUPDATE            datetime =            null output,
    @DISABLEDWEALTHUPDATES        bit =                null output,
    @ISPROSPECT                    bit =                null output,
    @VALIDSEARCHCRITERIASTRICT  bit =                null output,
    @VALIDSEARCHCRITERIARELAXED bit =                null output,
    @ISFORMERPROSPECT            bit =                null output,
    @ISFUNDRAISER                bit =                null output,
    @ISFORMERFUNDRAISER            bit =                null output,
    @ISVOLUNTEER                bit =                null output,
    @ISFORMERVOLUNTEER            bit =                null output,                    
    @ISREGISTRANT                bit =                null output,
    @ISVENDOR                    bit =                null output,
    @ISALUMNUS                    bit =                null output,
    @ISSTUDENT                    bit =                null output,
    @ISFORMERSTUDENT            bit =                null output,
    @APPUSERID                    uniqueidentifier =    null output,
    @HASCOMMUNITYDATA bit = null output,
    @CONFIRMEDWEALTHCAPACITY    bit =               null output,
    @WEALTHCAPACITYEXISTS       bit =               null output,

    @ISINACTIVE                  bit =              null output,
    @BIOGRAPHICALSUFFIX          nvarchar(13) = null output,
    @POLITICALDONATIONSSUFFIX    nvarchar(13) = null output,
    @PRIVATEFOUNDATIONSSUFFIX    nvarchar(13) = null output,
    @INCOMECOMPENSATIONSUFFIX    nvarchar(13) = null output,
    @AFFLUENCEINDICATORSSUFFIX   nvarchar(13) = null output,
    @NONPROFITAFFILIATIONSSUFFIX nvarchar(13) = null output,
    @PHILANTHROPICGIFTSSUFFIX    nvarchar(13) = null output,
    @BUSINESSOWNERSHIPSUFFIX     nvarchar(13) = null output,
    @REALESTATESUFFIX            nvarchar(13) = null output,
    @SECURITIESSUFFIX            nvarchar(13) = null output,
    @OTHERASSETSSUFFIX           nvarchar(13) = null output,

    @NEWBIOGRAPHICAL             bit = null output,
    @NEWPOLITICALDONATIONS       bit = null output,
    @NEWPRIVATEFOUNDATIONS       bit = null output,
    @NEWINCOMECOMPENSATION       bit = null output,
    @NEWAFFLUENCEINDICATORS      bit = null output,
    @NEWNONPROFITAFFILIATIONS    bit = null output,
    @NEWPHILANTHROPICGIFTS       bit = null output,
    @NEWBUSINESSOWNERSHIP        bit = null output,
    @NEWREALESTATE               bit = null output,
    @NEWSECURITIES               bit = null output,
    @NEWOTHERASSETS              bit = null output,

    @SYSTEMCALCULATEDWEALTHCAPACITY bit = null output,
    @HASWEALTHPOINTCREDENTIALS     bit = null output,
    @LINKEDTORE7                 bit = null output,
    @ISORG                         bit = null output,
    @ISGROUPMEMBER                 bit =                null output,
    @ISGROUP                     bit =                null output,
    @ISHOUSEHOLD                 bit =                null output,
    @RE7SECURITYFEATURESGRANTED  bit = null output,
    @WEALTHPOINTREGISTERED       bit = null output,
    @WEALTHPOINTACCOUNTTYPECODE  integer = null output,
    @HASSTEWARDSHIPPLAN             bit = null output,
    @ISBANK bit = null output,
    @ISCOMMITTEE bit = null output,
    @ISGRANTOR bit = null output,
    @ISSPONSOR bit = null output,         
    @ISCOMMITTEEMEMBER bit = null output,
    @SPONSORTYPECODE tinyint = null output,
    @ISPLANNEDGIVER bit = null output,
    @ISFACULTY bit = null output,
    @ISFORMERSPONSOR bit = null output,

    @CANVIEWBIOGRAPHICAL bit = null output,
    @CANVIEWCAMPAIGN bit = null output,
    @CANVIEWFOUNDATION bit = null output,
    @CANVIEWGIFT bit = null output,
    @CANVIEWINCOME bit = null output,
    @CANVIEWAFFLUENCEINDICATORS bit = null output,
    @CANVIEWNONPROFIT bit = null output,
    @CANVIEWOTHERASSETS bit = null output,
    @CANVIEWBUSINESS bit = null output,
    @CANVIEWREALESTATE bit = null output,
    @CANVIEWSECURITIES bit = null output,
    @HASRECEIVEDFUNDS bit = null output,
    @USER_GRANTED_CONSTITPERSONALINFO_EDIT bit = null output,
    @RE7RECORDID int = null output,

    @LASTRESEARCHDETAILSUPDATE datetime = null output,
    @LASTRESEARCHEDBY nvarchar(100) = null output,
    @CANVIEWGIVINGTOMYORG bit = null output,
    @HASGIVINGSUMMARY bit = null output,
    @N_GIFTS integer = null output,
    @USER_GRANTED_ADDRESS_DELETE_FOR_CONSTIT bit = null output,
    @VIEWAS_ADVOCATECONSTITUENCYTEXT nvarchar(100) = null output,
    @VIEWAS_BOARDMEMBERCONSTITUENCYTEXT nvarchar(100) = null output,
    @VIEWAS_COMMITTEECONSTITUENCYTEXT nvarchar(100) = null output,
    @VIEWAS_STAFFCONSTITUENCYTEXT nvarchar(100) = null output,
    @VIEWAS_FUNDRAISERCONSTITUENCYTEXT nvarchar(100) = null output,
    @VIEWAS_PROSPECTCONSTITUENCYTEXT nvarchar(100) = null output,
    @VIEWAS_VOLUNTEERCONSTITUENCYTEXT nvarchar(100) = null output,
    @ISADVOCATE bit = null output,
    @MAPPINGCREDENTIALSEXIST bit = null output,
    @USER_GRANTED_ADDRESS_GEOCODE bit = null output,
    @USER_GRANTED_CONSTITUENT_MAPENTITY bit = null output,
    @ADDRESSATTRIBUTEDEFINED bit = null output,
    @ISOLDPAGE bit = null output,
    @HASIND2ORGRELATIONS bit = null output,
    @ISNATIONALFUNDRAISINGGROUP  bit = null output,
    @ENTERPRISE_FLAG bit = null output,
    @DECEASED bit = null output,
    @DECEASINGOPTIONSSET bit = null output,
    @AGE int = null output,
    @HASSPOUSE bit = null output,
    @MARRIAGERULESSET bit = null output,
    @ISDISSOLVED bit = null output,
    @SPOUSERELATIONSHIPID uniqueidentifier = null output,
    @SYSADMIN bit = null output,
    @HASRESEARCHDETAILSFUNDRAISER bit = null output,
    @MODELSCOREANDCUSTOMRATINGCOUNT integer = null output,
    @PENDINGSEARCH bit = null output,

    @REALESTATEASSETS money = null output,
    @C_REALESTATEASSETS money = null output,
    @BUSINESSASSETS money = null output,
    @C_BUSINESSASSETS money = null output,
    @SECURITIESASSETS money = null output,
    @C_SECURITIESASSETS money = null output,
    @INCOME money = null output,
    @C_INCOME money = null output,
    @NAFFLUENCEINDICATORS int = null output,
    @C_NAFFLUENCEINDICATORS int = null output,
    @OTHERASSETS money = null output,
    @C_OTHERASSETS money = null output,

    @TOTALGIVING money = null output,
    @LARGESTGIFT money = null output,
    @PHILANTHROPIC money = null output,
    @C_PHILANTHROPIC money = null output,
    @POLITICAL money = null output,
    @C_POLITICAL money = null output,

    @NONPROFITAFFILIATIONS int = null output,
    @C_NONPROFITAFFILIATIONS int = null output,
    @FOUNDATIONAFFILIATIONS int = null output,
    @C_FOUNDATIONAFFILIATIONS int = null output,
    @NETWORKCONNECTIONS int = null output,
    @C_NETWORKCONNECTIONS int = null output,
    @BIOGRAPHICALNUMBER int = null output,
    @C_BIOGRAPHICALNUMBER int = null output,
    @USESUBCAPTION bit = null output,
    @RE7CANVIEW bit = null output,
  @RE7WEBCONFIGURED bit = null output,
  @CANVIEWRE7ADDRESSES bit = null output,
  @CANVIEWRE7PHONES bit = null output,
    @LASTSOCIALUPDATE datetime = null output,
    @HASSOCIALINFORMATION bit = null output,
  @HIDETEASER bit = null output

 as

    set nocount on;
    set @DATALOADED = 0;    

    Set @ISNATIONALFUNDRAISINGGROUP = dbo.UFN_CONSTITUENT_ISNATIONALFUNDRAISINGGROUP(@ID)          

    select @APPUSERID = ID from dbo.APPUSER where CONSTITUENTID = @ID;

    select @SYSADMIN = ISSYSADMIN from     dbo.APPUSER where APPUSER.ID = @CURRENTAPPUSERID;


    --WEALTHPOINT FIELDS

    set @REALESTATEASSETS = 0;
    set @C_REALESTATEASSETS = 0;
    set @BUSINESSASSETS = 0;
    set @C_BUSINESSASSETS = 0;
    set @SECURITIESASSETS = 0;
    set @C_SECURITIESASSETS = 0;
    set @INCOME = 0;
    set @C_INCOME = 0;
    set @NAFFLUENCEINDICATORS = 0;
    set @C_NAFFLUENCEINDICATORS = 0;
    set @OTHERASSETS = 0;
    set @C_OTHERASSETS = 0;
    set @PENDINGSEARCH = 0;

    -- Giving
    set @TOTALGIVING = 0;
    set @LARGESTGIFT = 0;
    set @PHILANTHROPIC = 0;
    set @C_PHILANTHROPIC = 0;
    set @POLITICAL = 0;
    set @C_POLITICAL = 0;

    -- Affiliation
    set @NONPROFITAFFILIATIONS = 0;
    set @C_NONPROFITAFFILIATIONS = 0;
    set @FOUNDATIONAFFILIATIONS = 0;
    set @C_FOUNDATIONAFFILIATIONS = 0;
    set @NETWORKCONNECTIONS = 0;
    set @C_NETWORKCONNECTIONS = 0;

    --Biographics
    set @BIOGRAPHICALNUMBER = 0;
    set @C_BIOGRAPHICALNUMBER =0;

    set @CONSTITUENTID =  @ID;

    select 
        @LASTWEALTHUPDATE = WEALTH.WEALTHPOINTDATE,
        @LASTSOCIALUPDATE = WEALTH.SOCIALMEDIADATE,

        @HASWEALTHPROFILE = case when WEALTH.WEALTHPROFILEPDF is null then 0 else 1 end,

        @REALESTATEASSETS = WEALTH.REALESTATEASSETS,
        @C_REALESTATEASSETS = WEALTH.REALESTATEASSETSCONFIRMED,
        @BUSINESSASSETS = WEALTH.BUSINESSOWNERSHIPASSETS,
        @C_BUSINESSASSETS = WEALTH.BUSINESSOWNERSHIPASSETSCONFIRMED,
        @SECURITIESASSETS = WEALTH.SECURITIESASSETS,
        @C_SECURITIESASSETS = WEALTH.SECURITIESASSETSCONFIRMED,
        @INCOME = WEALTH.INCOMECOMPENSATIONASSETS,
        @C_INCOME = WEALTH.INCOMECOMPENSATIONASSETSCONFIRMED,
        @NAFFLUENCEINDICATORS = WEALTH.AFFLUENCEINDICATORNUMBER,
        @C_NAFFLUENCEINDICATORS = WEALTH.AFFLUENCEINDICATORNUMBERCONFIRMED,
        @OTHERASSETS = WEALTH.OTHERASSETS,
        @C_OTHERASSETS = WEALTH.OTHERASSETSCONFIRMED,    
        @NONPROFITAFFILIATIONS = WEALTH.NONPROFITAFFILIATIONNUMBER,
        @C_NONPROFITAFFILIATIONS = WEALTH.NONPROFITAFFILIATIONNUMBERCONFIRMED,
        @FOUNDATIONAFFILIATIONS = WEALTH.PRIVATEFOUNDATIONNUMBER,
        @C_FOUNDATIONAFFILIATIONS = WEALTH.PRIVATEFOUNDATIONNUMBERCONFIRMED,   
        @BIOGRAPHICALNUMBER = WEALTH.BIOGRAPHICALNUMBER,
        @C_BIOGRAPHICALNUMBER = WEALTH.BIOGRAPHICALNUMBERCONFIRMED,
        @PENDINGSEARCH = WEALTH.PENDINGSEARCH

    from
        dbo.WEALTH
    where
        WEALTH.ID = @ID;

    --Network connection
    select
    @NETWORKCONNECTIONS = (SELECT COUNT(ID) FROM dbo.UFN_WEALTHPOINT_EXTENDEDRELATIONSHIP(@CONSTITUENTID, null, null)),
        @C_NETWORKCONNECTIONS = (SELECT COUNT(ID) FROM dbo.UFN_WEALTHPOINT_EXTENDEDRELATIONSHIP(@CONSTITUENTID, null, null) WHERE LINKEDCONSTITUENTID IS NOT NULL)

    --Philanthropic Confirmed amount
    select @C_PHILANTHROPIC = ISNULL(SUM(LO),0)
    from WPPHILANTHROPICGIFT 
        where CONFIRMED = 1 and WEALTHID = @ID

    --Philanthropic Total Identified
    select @PHILANTHROPIC = ISNULL(SUM(LO),0)
    from WPPHILANTHROPICGIFT 
        where REJECTED  <> 1 and WEALTHID = @ID    

    --Political Confirmed 
    select @C_POLITICAL = ISNULL(SUM(AMOUNT),0)
    from WPPOLITICALDONATION 
        where CONFIRMED = 1 and WEALTHID = @ID    

    --Political Total Identified
    select @POLITICAL = ISNULL(SUM(AMOUNT),0)
    from WPPOLITICALDONATION 
        where REJECTED  <> 1 and WEALTHID = @ID    

if @HASWEALTHPROFILE is null
    set @HASWEALTHPROFILE=0;


    --WEALTHCAPACITY FIELDS

    select
        @CONFIRMEDWEALTHCAPACITY =          WEALTHCAPACITY.[CONFIRMED],
        @SYSTEMCALCULATEDWEALTHCAPACITY = WEALTHCAPACITY.[SYSTEMCALCULATED]    ,
        @WEALTHCAPACITYEXISTS = 1              
    from
     dbo.[WEALTHCAPACITY] 
    where
        WEALTHCAPACITY.[ID] = @ID;

    if @WEALTHCAPACITYEXISTS is null
        set @WEALTHCAPACITYEXISTS  = 0;


    if @WEALTHCAPACITYEXISTS = 0
        set @SYSTEMCALCULATEDWEALTHCAPACITY = 1;


    set @CONSTITUENTID =  @ID;

    select

        @ISPROSPECT =            dbo.UFN_CONSTITUENT_ISPROSPECT(@ID),
        @ISFORMERPROSPECT =        dbo.UFN_CONSTITUENT_ISFORMERPROSPECT(@ID),
        @ISFUNDRAISER =            dbo.UFN_CONSTITUENT_ISFUNDRAISER(@ID),
        @ISFORMERFUNDRAISER =    dbo.UFN_CONSTITUENT_ISFORMERFUNDRAISER(@ID),
        @ISVOLUNTEER =            dbo.UFN_CONSTITUENT_ISVOLUNTEER(@ID),
        @ISFORMERVOLUNTEER =    dbo.UFN_CONSTITUENT_ISFORMERVOLUNTEER(@ID),
        @ISREGISTRANT =            dbo.UFN_CONSTITUENT_ISREGISTRANT(@ID),
        @ISVENDOR =                dbo.UFN_CONSTITUENT_ISVENDOR(@ID),
        @ISALUMNUS =            dbo.UFN_CONSTITUENT_ISALUMNUS(@ID),
        @ISSTUDENT =            dbo.UFN_CONSTITUENT_ISSTUDENT(@ID),
        @ISFORMERSTUDENT =        dbo.UFN_CONSTITUENT_ISFORMERSTUDENT(@ID),
        @HASCOMMUNITYDATA =     dbo.UFN_CONSTITUENT_HASCOMMUNITYDATA(@ID),
        @ISGROUPMEMBER =        dbo.UFN_CONSTITUENT_HASGROUPMEMBERRECORD(@ID),

        @HASSTEWARDSHIPPLAN =    dbo.UFN_CONSTITUENT_HASSTEWARDSHIPPLAN(@ID),
        @ISBANK = dbo.UFN_CONSTITUENT_ISBANK(@ID),
        @ISCOMMITTEE = dbo.UFN_CONSTITUENT_ISCOMMITTEE(@ID),
        @ISGRANTOR = dbo.UFN_CONSTITUENT_ISGRANTOR(@ID),
        @ISCOMMITTEEMEMBER = dbo.UFN_CONSTITUENT_ISCOMMITTEEMEMBER(@ID),
        @ISPLANNEDGIVER = dbo.UFN_CONSTITUENT_ISPLANNEDGIVER(@ID),
        @ISFACULTY = dbo.UFN_CONSTITUENT_ISFACULTY(@ID),
        @HASRECEIVEDFUNDS = dbo.UFN_CONSTITUENT_HASRECEIVEDFUNDS(@ID),
        @DECEASED = dbo.UFN_CONSTITUENT_ISDECEASED(@ID),
        @HASSPOUSE = case when exists (select ID from dbo.RELATIONSHIP where RELATIONSHIPCONSTITUENTID = @ID and ISSPOUSE = 1) then 1 else 0 end,
        @AGE = dbo.UFN_CONSTITUENT_GETAGE(@ID),
        @ISDISSOLVED = case when exists (select ID from dbo.DISSOLVEDGROUP where ID = @ID) then 1 else 0 end,
        @SPOUSERELATIONSHIPID = (select ID from dbo.RELATIONSHIP where RELATIONSHIPCONSTITUENTID = @ID and ISSPOUSE = 1)

    select @DECEASINGOPTIONSSET = RULESSET
        from dbo.UFN_DECEASINGOPTION_GETRULES();

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


    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;

    IF @FLAGGED is null set @FLAGGED=0;


    exec dbo.USP_SPONSOR_GETINFORMATION
    @ID,
    @ISSPONSOR output,
    @ISFORMERSPONSOR output,
    @SPONSORTYPECODE output

    --Collect common Wealth Details data
    exec dbo.USP_WEALTHDETAILS_PAGEEXPRESSIONS_COMMON
        @ID,                     
        @DATALOADED                        output,
        @NAME                            output,
        @ISINACTIVE                     output,  
        @CONSTITUENTID                     output,  
        @UPDATEPENDING                     output,  
        @DISABLEDWEALTHUPDATES             output,  
        @VALIDSEARCHCRITERIASTRICT         output,  
        @VALIDSEARCHCRITERIARELAXED     output,  

        @BIOGRAPHICALSUFFIX             output,  
        @POLITICALDONATIONSSUFFIX         output,  
        @PRIVATEFOUNDATIONSSUFFIX         output,  
        @INCOMECOMPENSATIONSUFFIX         output,  
        @AFFLUENCEINDICATORSSUFFIX         output,  
        @NONPROFITAFFILIATIONSSUFFIX     output,  
        @PHILANTHROPICGIFTSSUFFIX         output,  
        @BUSINESSOWNERSHIPSUFFIX         output,  
        @REALESTATESUFFIX                 output,  
        @SECURITIESSUFFIX                 output,  
        @OTHERASSETSSUFFIX                 output,  

        @NEWBIOGRAPHICAL                 output,  
        @NEWPOLITICALDONATIONS          output,  
        @NEWPRIVATEFOUNDATIONS             output,  
        @NEWINCOMECOMPENSATION             output,  
        @NEWAFFLUENCEINDICATORS           output,  
        @NEWNONPROFITAFFILIATIONS         output,  
        @NEWPHILANTHROPICGIFTS             output,  
        @NEWBUSINESSOWNERSHIP             output,  
        @NEWREALESTATE                     output,  
        @NEWSECURITIES                    output,  
        @NEWOTHERASSETS                    output,
        @ISORG                            output,
        @ISGROUP                        output,
        @ISHOUSEHOLD                    output,
        @WEALTHPOINTREGISTERED          output,
        @WEALTHPOINTACCOUNTTYPECODE     output;

  select 
    @RE7RECORDID = RE7RECORDID
  from dbo.RE7INTEGRATIONCONSTITUENTMAP where ID=@ID

  if coalesce(@RE7RECORDID, 0) > 0
    begin
        set @LINKEDTORE7 = 1;
    end else begin
        set @LINKEDTORE7 = 0;
    end

  /*Check RE Rights */
  /*Also deny rights if RE7 not configured*/
  --Checking RE7 Credentails stored for current AppUser
  declare @RE7INTEGRATIONCREDENTIALS bit;
  select @RE7INTEGRATIONCREDENTIALS = 1 from RE7INTEGRATIONCREDENTIALS where ID = @CURRENTAPPUSERID

    if exists(select top 1 1 from dbo.RE7INTEGRATIONCONFIGURATION) and
     ( @SYSADMIN = 1 or
         dbo.UFN_SECURITY_APPUSER_GRANTED_FORM_IN_SYSTEMROLE(@CURRENTAPPUSERID,'E1560C48-F5E0-4E4E-B9EF-9EE4CCE644F4')=1
     ) and (@RE7INTEGRATIONCREDENTIALS = 1)
     set @RE7CANVIEW = 1
   else
     set @RE7CANVIEW = 0

   if exists(select top 1 RERPWEBSERVICEURL from dbo.RE7INTEGRATIONCONFIGURATION where RERPWEBSERVICEURL <> '') and @RE7INTEGRATIONCREDENTIALS = 1
     set @RE7WEBCONFIGURED = 1
   else
     set @RE7WEBCONFIGURED = 0

   set @CANVIEWRE7ADDRESSES = 0;
   set @CANVIEWRE7PHONES = 0;

   select @CANVIEWRE7ADDRESSES = 1 from RE7INTEGRATIONSELECTEDFIELD where ID = '4F455172-4C35-43EE-A730-1E3DA4AC9B52';
   select @CANVIEWRE7PHONES = 1 from RE7INTEGRATIONSELECTEDFIELD where ID = 'D1D320F2-E9DF-473D-9015-5DB7B3D60140';

    if exists(select top 1 1 from dbo.RE7INTEGRATIONCONFIGURATION) and
     ( @SYSADMIN = 1 or
         (dbo.UFN_SECURITY_APPUSER_GRANTED_RECORDOPERATION_IN_SYSTEMROLE(@CURRENTAPPUSERID ,'c81183fd-78df-4ebf-8855-b3dcc0b0be33')=1 and
          dbo.UFN_SECURITY_APPUSER_GRANTED_RECORDOPERATION_IN_SYSTEMROLE(@CURRENTAPPUSERID ,'505760bd-3e81-4c4f-8f21-f59edfd5562d')=1 and
          dbo.UFN_SECURITY_APPUSER_GRANTED_RECORDOPERATION_IN_SYSTEMROLE(@CURRENTAPPUSERID ,'49675912-2d9a-415b-8f03-ccf9c564796d')=1 and
          dbo.UFN_SECURITY_APPUSER_GRANTED_DATALIST_IN_SYSTEMROLE(@CURRENTAPPUSERID,'ab0e06c4-dcea-414a-bf29-0b0758a3934b')=1 and
          dbo.UFN_SECURITY_APPUSER_GRANTED_FORM_IN_SYSTEMROLE(@CURRENTAPPUSERID,'8DABE43F-6848-40EB-8638-8E4B6311CE72')=1 and
          dbo.UFN_SECURITY_APPUSER_GRANTED_FORM_IN_SYSTEMROLE(@CURRENTAPPUSERID,'1A5D63F0-4DDD-4DD3-A4E6-FB1E9FE17FE2')=1 and
          dbo.UFN_SECURITY_APPUSER_GRANTED_FORM_IN_SYSTEMROLE(@CURRENTAPPUSERID,'41B32DCD-4CFE-4FAC-83DA-FA9A3F893B71')=1 and
          dbo.UFN_SECURITY_APPUSER_GRANTED_FORM_IN_SYSTEMROLE(@CURRENTAPPUSERID,'437cd7d8-e49c-4591-9574-572f5f15fd3a')=1 and
          dbo.UFN_SECURITY_APPUSER_GRANTED_FORM_IN_SYSTEMROLE(@CURRENTAPPUSERID,'E1560C48-F5E0-4E4E-B9EF-9EE4CCE644F4')=1 and
          dbo.UFN_SECURITY_APPUSER_GRANTED_FORM_IN_SYSTEMROLE(@CURRENTAPPUSERID,'08F40ECE-26B9-4e11-B53D-1FA5BAFC038A')=1 and
          dbo.UFN_SECURITY_APPUSER_GRANTED_FORM_IN_SYSTEMROLE(@CURRENTAPPUSERID,'33EE2022-238B-4400-81F5-F81F19C9F755')=1 and
          dbo.UFN_SECURITY_APPUSER_GRANTED_FORM_IN_SYSTEMROLE(@CURRENTAPPUSERID,'38B7BE1C-6F54-4951-94C5-EE53C688CBBA')=1 and
          dbo.UFN_SECURITY_APPUSER_GRANTED_FORM_IN_SYSTEMROLE(@CURRENTAPPUSERID,'2dbf2cc3-958c-4454-b169-7eb1e656044e')=1 )
     ) and (@RE7INTEGRATIONCREDENTIALS = 1)
        set @RE7SECURITYFEATURESGRANTED = 1;
    else
        set @RE7SECURITYFEATURESGRANTED = 0;      



    if (1=@SYSADMIN) begin
        set @CANVIEWBIOGRAPHICAL = 1;
        set @CANVIEWCAMPAIGN = 1;
        set @CANVIEWFOUNDATION = 1;
        set @CANVIEWGIFT = 1;
        set @CANVIEWINCOME = 1;
        set @CANVIEWAFFLUENCEINDICATORS = 1;
        set @CANVIEWNONPROFIT = 1;
        set @CANVIEWOTHERASSETS = 1;
        set @CANVIEWBUSINESS = 1;
        set @CANVIEWREALESTATE = 1;
        set @CANVIEWSECURITIES = 1;
        set @CANVIEWGIVINGTOMYORG = 1;
        set @USER_GRANTED_ADDRESS_DELETE_FOR_CONSTIT = 1;
        set @USER_GRANTED_CONSTITPERSONALINFO_EDIT = 1;
        set @USER_GRANTED_ADDRESS_GEOCODE = 1;
        set @USER_GRANTED_CONSTITUENT_MAPENTITY = 1;
    end
    else begin
        set @CANVIEWBIOGRAPHICAL =            dbo.UFN_SECURITY_APPUSER_GRANTED_DATALIST_IN_SYSTEMROLE(@CURRENTAPPUSERID,'45F165B4-025E-49B3-AFCC-FD9B741406DB') &
                                            dbo.UFN_SECURITY_APPUSER_GRANTED_PAGEEXPRESSIONFORM_IN_SYSTEMROLE(@CURRENTAPPUSERID, 'ca164256-542b-48fe-b165-d53d0dd75ac4');
        set @CANVIEWCAMPAIGN =                dbo.UFN_SECURITY_APPUSER_GRANTED_DATALIST_IN_SYSTEMROLE(@CURRENTAPPUSERID,'7685BF76-60A9-4C8F-8DCE-C143505F3F9D') &
                                            dbo.UFN_SECURITY_APPUSER_GRANTED_PAGEEXPRESSIONFORM_IN_SYSTEMROLE(@CURRENTAPPUSERID, '5a0edb04-1ce6-49fb-b965-5cb91746c37e');
        set @CANVIEWFOUNDATION =            dbo.UFN_SECURITY_APPUSER_GRANTED_DATALIST_IN_SYSTEMROLE(@CURRENTAPPUSERID,'F4C7D336-DDEF-474A-89E5-291EE2DB2F52') &
                                            dbo.UFN_SECURITY_APPUSER_GRANTED_PAGEEXPRESSIONFORM_IN_SYSTEMROLE(@CURRENTAPPUSERID, 'f9b1f154-844d-4e11-92f5-4c8f5fa0a1cf');
        set @CANVIEWGIFT =                    dbo.UFN_SECURITY_APPUSER_GRANTED_DATALIST_IN_SYSTEMROLE(@CURRENTAPPUSERID,'4B3922A5-2C7B-4FFB-9F64-54F024C40E13') &
                                            dbo.UFN_SECURITY_APPUSER_GRANTED_PAGEEXPRESSIONFORM_IN_SYSTEMROLE(@CURRENTAPPUSERID, '608322a5-0699-42c6-9cc9-07b3ae685f5e');
        set @CANVIEWINCOME =                dbo.UFN_SECURITY_APPUSER_GRANTED_DATALIST_IN_SYSTEMROLE(@CURRENTAPPUSERID,'4CD067F6-DED7-458C-B592-11AAC1186FD9') &
                                            dbo.UFN_SECURITY_APPUSER_GRANTED_PAGEEXPRESSIONFORM_IN_SYSTEMROLE(@CURRENTAPPUSERID, '0e21b37a-ec41-4883-8b4d-f9738688d441');
        set @CANVIEWAFFLUENCEINDICATORS =    dbo.UFN_SECURITY_APPUSER_GRANTED_DATALIST_IN_SYSTEMROLE(@CURRENTAPPUSERID,'8BFA650D-4FC4-4454-94F2-2A38332C0684') &
                                            dbo.UFN_SECURITY_APPUSER_GRANTED_PAGEEXPRESSIONFORM_IN_SYSTEMROLE(@CURRENTAPPUSERID, '791f6e7d-8aa8-4078-a4e1-0392cd0b73f2');
        set @CANVIEWNONPROFIT =                dbo.UFN_SECURITY_APPUSER_GRANTED_DATALIST_IN_SYSTEMROLE(@CURRENTAPPUSERID,'9CA1A80A-3697-4D42-B7A9-CC0CB5B562A9') &
                                            dbo.UFN_SECURITY_APPUSER_GRANTED_PAGEEXPRESSIONFORM_IN_SYSTEMROLE(@CURRENTAPPUSERID, '40f3df01-4ba4-4228-9a3b-18e4bd98fbe3');
        set @CANVIEWOTHERASSETS =            dbo.UFN_SECURITY_APPUSER_GRANTED_DATALIST_IN_SYSTEMROLE(@CURRENTAPPUSERID,'DC27FA3B-7EFE-484C-99CA-FC8C84D7EE0A') &
                                            dbo.UFN_SECURITY_APPUSER_GRANTED_PAGEEXPRESSIONFORM_IN_SYSTEMROLE(@CURRENTAPPUSERID, 'b70edfe9-af03-486a-bc01-352a219cbf01');
        set @CANVIEWBUSINESS =                dbo.UFN_SECURITY_APPUSER_GRANTED_DATALIST_IN_SYSTEMROLE(@CURRENTAPPUSERID,'2B80B727-9E55-433E-9B37-E061645B1263') &
                                            dbo.UFN_SECURITY_APPUSER_GRANTED_PAGEEXPRESSIONFORM_IN_SYSTEMROLE(@CURRENTAPPUSERID, 'd6d56c8f-3f0d-4f5a-b194-e5c5d1399a48');
        set @CANVIEWREALESTATE =            dbo.UFN_SECURITY_APPUSER_GRANTED_DATALIST_IN_SYSTEMROLE(@CURRENTAPPUSERID,'C5659434-1493-4c84-BCB5-2D2DA2932CA0') &
                                            dbo.UFN_SECURITY_APPUSER_GRANTED_PAGEEXPRESSIONFORM_IN_SYSTEMROLE(@CURRENTAPPUSERID, '0a054f34-9246-49a8-87f0-2cf789597080');
        set @CANVIEWSECURITIES =            dbo.UFN_SECURITY_APPUSER_GRANTED_DATALIST_IN_SYSTEMROLE(@CURRENTAPPUSERID,'8623389D-F469-4a34-B3D5-00C642257CDC') &
                                            dbo.UFN_SECURITY_APPUSER_GRANTED_PAGEEXPRESSIONFORM_IN_SYSTEMROLE(@CURRENTAPPUSERID, '90758f12-463a-421b-8433-1883eb1c0aa5');
        set @CANVIEWGIVINGTOMYORG =     dbo.UFN_SECURITY_APPUSER_GRANTED_FORM_IN_SYSTEMROLE(@CURRENTAPPUSERID,'523eee21-4ee1-471b-9f2e-6e67a7d4670c');
        set @USER_GRANTED_ADDRESS_GEOCODE = dbo.UFN_SECURITY_APPUSER_GRANTED_RECORDOPERATION_IN_SYSTEMROLE(@CURRENTAPPUSERID,'1783224D-E771-437F-AAE4-A3981AE84543');
        set @USER_GRANTED_CONSTITUENT_MAPENTITY = dbo.UFN_SECURITY_APPUSER_GRANTED_MAPENTITY_IN_SYSTEMROLE(@CURRENTAPPUSERID,'645E6BE7-459C-402b-A03C-67587CA72B94');

    set @USER_GRANTED_CONSTITPERSONALINFO_EDIT = 
        case 
          when @ISORG = 1 then dbo.UFN_SECURITY_APPUSER_GRANTED_FORM_IN_SYSTEMROLE(@CURRENTAPPUSERID,'a9954902-ea62-48ae-8e6f-4e2ed3c3f4f9')
          when @ISHOUSEHOLD = 1 then dbo.UFN_SECURITY_APPUSER_GRANTED_FORM_IN_SYSTEMROLE(@CURRENTAPPUSERID,'2f419cfd-9e70-406c-8277-ba2c4b7bdfba')
          when @ISGROUP = 1 then dbo.UFN_SECURITY_APPUSER_GRANTED_FORM_IN_SYSTEMROLE(@CURRENTAPPUSERID,'292ee330-a63c-4cc5-98c2-a1168a1b7150')
          else dbo.UFN_SECURITY_APPUSER_GRANTED_FORM_IN_SYSTEMROLE(@CURRENTAPPUSERID,'788AB947-26ED-40C4-865E-8FE29577E593')
        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',@ID) = 1 and
                    (select
                        count(*)
                    from
                        dbo.UFN_SITEID_MAPFROM_CONSTITUENTID(@ID) as SITES
                    where
                        dbo.UFN_SECURITY_APPUSER_GRANTED_RECORDOPERATION_FORSITE(@CURRENTAPPUSERID, '0C2D6552-EC7F-4923-A4AF-AFC53114C32B', SITEID) = 1) > 0
                then 1
                else 0
                end;

    end

  declare @PROSPECTMANAGERFUNDRAISERID uniqueidentifier = null;

  select 
    @LASTRESEARCHDETAILSUPDATE = PROSPECT.LASTUPDATED,
    @LASTRESEARCHEDBY = APPUSER.DISPLAYNAME,
    @PROSPECTMANAGERFUNDRAISERID = PROSPECT.PROSPECTMANAGERFUNDRAISERID
  from dbo.PROSPECT
    inner join  dbo.APPUSER on PROSPECT.LASTRESEARCHEDBYID = APPUSER.ID
  where PROSPECT.ID = @ID;

  set @HASRESEARCHDETAILSFUNDRAISER = 0;
  If (@PROSPECTMANAGERFUNDRAISERID is not null)
    set @HASRESEARCHDETAILSFUNDRAISER = 1;

    set @HASGIVINGSUMMARY = 0;
    select 
        @HASGIVINGSUMMARY = 1,
        @N_GIFTS = TOTALGIFTSGIVEN,
    @TOTALGIVING = TOTALGIFTAMOUNT,
    @LARGESTGIFT = LARGESTGIFTAMOUNT
    from 
        dbo.RE7INTEGRATIONGIVINGSUMMARY
    where
        RE7INTEGRATIONGIVINGSUMMARY.ID = @ID

  select
    @VIEWAS_BOARDMEMBERCONSTITUENCYTEXT = dbo.UFN_CONSTITUENCY_GETDESCRIPTION('F828E957-5F5E-479A-8F23-2FFD6C7C68FF'),
    @VIEWAS_STAFFCONSTITUENCYTEXT = dbo.UFN_CONSTITUENCY_GETDESCRIPTION('6093915E-ADE9-42BE-88AE-304731754467'),
    @VIEWAS_COMMITTEECONSTITUENCYTEXT = dbo.UFN_CONSTITUENCY_GETDESCRIPTION('AC9DB5A4-14E0-416A-9FB2-04038AC66799'),
    @VIEWAS_FUNDRAISERCONSTITUENCYTEXT = dbo.UFN_CONSTITUENCY_GETDESCRIPTION('D2DCA06A-BE6E-40B3-B95D-59A926181923'),
 @VIEWAS_PROSPECTCONSTITUENCYTEXT = dbo.UFN_CONSTITUENCY_GETDESCRIPTION('00E748FB-940D-4A7D-A133-C148B29410A8'),
    @VIEWAS_VOLUNTEERCONSTITUENCYTEXT = dbo.UFN_CONSTITUENCY_GETDESCRIPTION('E7489703-3D63-4017-A2BC-88C092563C5D'),
    @VIEWAS_ADVOCATECONSTITUENCYTEXT = dbo.UFN_CONSTITUENCY_GETDESCRIPTION('F828E957-5F5E-479A-8F23-2FFD6C7C6899')

    Set @ISADVOCATE = dbo.UFN_CONSTITUENT_ISADVOCATE(@ID)

    declare @MAPPINGCREDENTIALSID uniqueidentifier;
    select top 1
        @MAPPINGCREDENTIALSID = ID
    from
        dbo.MAPPINGCREDENTIALS
    where
        len([KEY]) > 0;

    if @MAPPINGCREDENTIALSID is not null
        set @MAPPINGCREDENTIALSEXIST = 1;
    else
        set @MAPPINGCREDENTIALSEXIST = 0;

        set @ADDRESSATTRIBUTEDEFINED = dbo.UFN_ATTRIBUTECATEGORY_DEFINEDFORRECORDTYPE_FORAPPUSER('ADDRESS',@CURRENTAPPUSERID)

        if (@ISGROUP=1 OR @ISORG=1)
            set @ISOLDPAGE = 1
        else
            set @ISOLDPAGE = 0

        set @HASIND2ORGRELATIONS = 0
        select top 1 @HASIND2ORGRELATIONS = 1 
            from dbo.RELATIONSHIP
            inner join dbo.CONSTITUENT
                on RELATIONSHIP.RECIPROCALCONSTITUENTID = CONSTITUENT.ID
            where RELATIONSHIP.RELATIONSHIPCONSTITUENTID = @ID
            and CONSTITUENT.ISORGANIZATION = 1

        set @ENTERPRISE_FLAG = 0;
        select @ENTERPRISE_FLAG = 1 from INSTALLEDPRODUCTLIST where ID='3117D2C8-7F46-42F2-ABEB-B654F2F63046'

        declare @MODELSCORECOUNT integer = 0;
        declare @CUSTOMRATINGCOUNT integer = 0;
        set @MODELSCORECOUNT = (select count (*) from dbo.UFN_MODELSCORESFORCONSTITUENT(@ID));
        exec dbo.USP_CUSTOMRATINGS_COUNTFORCONSTITUENT @CONSTITUENTID = @ID, @APPUSERID = @CURRENTAPPUSERID, @COUNT = @CUSTOMRATINGCOUNT output;
        set @MODELSCOREANDCUSTOMRATINGCOUNT = @MODELSCORECOUNT + @CUSTOMRATINGCOUNT;

        set @USESUBCAPTION = 
          case when exists (select top 1 1 from INSTALLEDPRODUCTLIST where ID='42c15648-749e-4859-a56d-3a6474814cc7') then 0 --Altru
          else 1 end

      set @HASSOCIALINFORMATION = 0;
      select @HASSOCIALINFORMATION = 1 from WPSOCIAL where ID = @ID;

      set @HIDETEASER = 1;
      if @HASSOCIALINFORMATION = 0
      begin
        select @HIDETEASER = SOCIALENABLED from dbo.WEALTHPOINTCONFIGURATION;
      end

    return 0;