USP_DATAFORMTEMPLATE_VIEW_RE7INTEGRATIONGIVINGSUMMARY_EXPRESSIONS

The load procedure used by the view dataform template "RE7 Integration Giving Summary 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
@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
@ISINACTIVE	bit	INOUT	ISINACTIVE
@BIOGRAPHICALSUFFIX	nvarchar(8)	INOUT	BIOGRAPHICALSUFFIX
@POLITICALDONATIONSSUFFIX	nvarchar(8)	INOUT	POLITICALDONATIONSSUFFIX
@PRIVATEFOUNDATIONSSUFFIX	nvarchar(8)	INOUT	PRIVATEFOUNDATIONSSUFFIX
@INCOMECOMPENSATIONSUFFIX	nvarchar(8)	INOUT	INCOMECOMPENSATIONSUFFIX
@AFFLUENCEINDICATORSSUFFIX	nvarchar(8)	INOUT	AFFLUENCEINDICATORSSUFFIX
@NONPROFITAFFILIATIONSSUFFIX	nvarchar(8)	INOUT	NONPROFITAFFILIATIONSSUFFIX
@PHILANTHROPICGIFTSSUFFIX	nvarchar(8)	INOUT	PHILANTHROPICGIFTSSUFFIX
@BUSINESSOWNERSHIPSUFFIX	nvarchar(8)	INOUT	BUSINESSOWNERSHIPSUFFIX
@REALESTATESUFFIX	nvarchar(8)	INOUT	REALESTATESUFFIX
@SECURITIESSUFFIX	nvarchar(8)	INOUT	SECURITIESSUFFIX
@OTHERASSETSSUFFIX	nvarchar(8)	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
@LINKEDTORE7	bit	INOUT	LINKEDTORE7
@ISORG	bit	INOUT	ISORG
@HASGIVINGSUMMARY	bit	INOUT	HASGIVINGSUMMARY
@ISGROUP	bit	INOUT	ISGROUP
@ISHOUSEHOLD	bit	INOUT	ISHOUSEHOLD
@ISFACULTY	bit	INOUT	ISFACULTY
@HASRECEIVEDFUNDS	bit	INOUT	HASRECEIVEDFUNDS
@ISGROUPMEMBER	bit	INOUT	ISGROUPMEMBER
@USER_GRANTED_CONSTITPERSONALINFO_EDIT	bit	INOUT
@RE7CONFIGUREDFORUSER	bit	INOUT

Definition

Copy


                CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_RE7INTEGRATIONGIVINGSUMMARY_EXPRESSIONS
                (     
                    @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,
                    @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,

                    @ISINACTIVE                  bit =              null output,
                    @BIOGRAPHICALSUFFIX          nvarchar(8) = null output,
                    @POLITICALDONATIONSSUFFIX    nvarchar(8) = null output,
                    @PRIVATEFOUNDATIONSSUFFIX    nvarchar(8) = null output,
                    @INCOMECOMPENSATIONSUFFIX    nvarchar(8) = null output,
                    @AFFLUENCEINDICATORSSUFFIX   nvarchar(8) = null output,
                    @NONPROFITAFFILIATIONSSUFFIX nvarchar(8) = null output,
                    @PHILANTHROPICGIFTSSUFFIX    nvarchar(8) = null output,
                    @BUSINESSOWNERSHIPSUFFIX     nvarchar(8) = null output,
                    @REALESTATESUFFIX            nvarchar(8) = null output,
                    @SECURITIESSUFFIX            nvarchar(8) = null output,
                    @OTHERASSETSSUFFIX           nvarchar(8) = 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,

                    @LINKEDTORE7                 bit = null output,
                    @ISORG                         bit = null output,
                    @HASGIVINGSUMMARY             bit = null output,
                    @ISGROUP                     bit = null output,
                    @ISHOUSEHOLD                 bit = null output,
                    @ISFACULTY                     bit = null output,
                    @HASRECEIVEDFUNDS             bit = null output,
                    @ISGROUPMEMBER                 bit = null output,
          @USER_GRANTED_CONSTITPERSONALINFO_EDIT bit = null output,
          @RE7CONFIGUREDFORUSER bit = null output
                )
                as
                    set nocount on;
                    set @DATALOADED = 0;    

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

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

                    if @ISORG = 0
                    begin
                        select
                            @DATALOADED = 1,
                            @NAME = common.NAME,
                            @FLAGGED = case when FP.ID is null then 0 else 1 end,
                            @HASWEALTHPROFILE = case when W.WEALTHPROFILEPDF is null then 0 else 1 end,

                            @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),
                            @ISGROUP =              dbo.UFN_CONSTITUENT_ISGROUP(@ID),
                            @ISHOUSEHOLD =          dbo.UFN_CONSTITUENT_ISHOUSEHOLD(@ID),
                            @ISFACULTY =            dbo.UFN_CONSTITUENT_ISFACULTY(@ID),
                            @CONSTITUENTID = @ID,
                            @ISGROUPMEMBER = dbo.UFN_CONSTITUENT_HASGROUPMEMBERRECORD(@ID)
                        from
                            dbo.CONSTITUENT AS common
                            left outer join dbo.WEALTH W on W.ID=common.ID
                            left outer join dbo.PROSPECT P on P.ID=common.ID
                            left outer join dbo.FLAGGEDPROSPECT FP on FP.APPUSERID=@CURRENTAPPUSERID and FP.PROSPECTID=common.ID
                        where
                            common.ID = @ID;

                        if exists(select 1 from dbo.RE7INTEGRATIONGIVINGSUMMARY where ID = @ID and @ISORG = 0)
                        begin
                            set @HASGIVINGSUMMARY = 1
                        end else begin
                            set @HASGIVINGSUMMARY = 0
                        end

                        if exists(select 1 from dbo.RE7INTEGRATIONCONSTITUENTMAP where ID=@ID and @ISORG = 0) 
                        begin
                            set @LINKEDTORE7 = 1;
                        end else begin
                            set @LINKEDTORE7 = 0;
                        end      

                        set @HASRECEIVEDFUNDS = dbo.UFN_CONSTITUENT_HASRECEIVEDFUNDS(@ID)

              if dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 
            begin
              set @USER_GRANTED_CONSTITPERSONALINFO_EDIT = 1;
              end
              else 
            begin
              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;
              end

            set @RE7CONFIGUREDFORUSER = 0;

           select top 1 @RE7CONFIGUREDFORUSER = 1
           from dbo.RE7INTEGRATIONCREDENTIALS
           where ID = @CURRENTAPPUSERID;


                    end    else begin
                        set @DATALOADED = 0;
                    end

                    return 0;