USP_DATAFORMTEMPLATE_VIEW_RE7INTEGRATIONTORE7DATA

The load procedure used by the view dataform template "RE7 Integration To The Raiser's Edge Data 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.
@ESTIMATEDWEALTH	nvarchar(100)	INOUT	Estimated Wealth
@MAJORGIVINGCAPACITY	nvarchar(100)	INOUT	Major Giving Capacity
@MAJORGIVINGLIKELIHOOD	nvarchar(100)	INOUT	Major Giving Likelihood
@ANNUALGIFTLIKELIHOOD	nvarchar(100)	INOUT	Annual Gift Likelihood
@PLANNEDGIFTLIKELIHOOD	nvarchar(100)	INOUT	Planned Gift Likelihood
@TARGETGIFTRANGE	nvarchar(100)	INOUT	Target Gift Range
@OVERALLRATING	nvarchar(100)	INOUT	Overall Rating
@TOTALREALESTATEASSETS	nvarchar(100)	INOUT	Total Real Estate Assets
@TOTALSECURITIES	nvarchar(100)	INOUT	Total Securities
@TOTALOTHERASSETS	nvarchar(100)	INOUT	Total Other Assets
@CONTRIBUTIONS	xml	INOUT	Contributions
@FROMRE7	bit	INOUT
@FROMRE7SELECTEDFIELDS	xml	INOUT
@TORE7	bit	INOUT
@TORE7SELECTEDFIELDS	xml	INOUT
@TOTALBUSINESSASSETS	nvarchar(100)	INOUT
@TOTALINCOMECOMPENSATION	nvarchar(100)	INOUT
@TOTALREALESTATEASSETSCOUNT	int	INOUT
@TOTALSECURITIESCOUNT	int	INOUT
@TOTALOTHERASSETSCOUNT	int	INOUT
@TOTALBUSINESSASSETSCOUNT	int	INOUT
@TOTALINCOMECOMPENSATIONCOUNT	int	INOUT
@REALESTATEDETAILS	xml	INOUT
@SECURITIESDETAILS	xml	INOUT
@OTHERASSETSDETAILS	xml	INOUT
@BUSINESSESDETAILS	xml	INOUT
@INCOMECOMPENSATIONDETAILS	xml	INOUT
@ESTIMATEDWEALTHVALUE	money	INOUT
@MAJORGIVINGCAPACITYVALUE	money	INOUT
@SOCIALMEDIACOMPANY	nvarchar(4000)	INOUT
@SOCIALMEDIAOCCUPATION	nvarchar(2048)	INOUT
@SOCIALMEDIARATING	int	INOUT
@INFLUENCERATING	int	INOUT
@OUTREACHRATING	int	INOUT
@PROFILEPHOTOS	xml	INOUT
@SOCIALMEDIAACCOUNTURLS	xml	INOUT

Definition

Copy


                CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_RE7INTEGRATIONTORE7DATA (
                    @ID uniqueidentifier,
                    @DATALOADED bit = 0 output,
                    @ESTIMATEDWEALTH nvarchar(100) = null output,
                    @MAJORGIVINGCAPACITY nvarchar(100) = null output,
                    @MAJORGIVINGLIKELIHOOD nvarchar(100) = null output,
                    @ANNUALGIFTLIKELIHOOD nvarchar(100) = null output,
                    @PLANNEDGIFTLIKELIHOOD nvarchar(100) = null output,
                    @TARGETGIFTRANGE nvarchar(100) = null output,
                    @OVERALLRATING nvarchar(100) = null output,
                    @TOTALREALESTATEASSETS nvarchar(100) = null output,
                    @TOTALSECURITIES nvarchar(100) = null output,
                    @TOTALOTHERASSETS nvarchar(100) = null output,
                    @CONTRIBUTIONS xml = null output,
                    @FROMRE7 bit = null output,
                    @FROMRE7SELECTEDFIELDS xml = null output,
                    @TORE7 bit = null output,
                    @TORE7SELECTEDFIELDS xml = null output,
          @TOTALBUSINESSASSETS nvarchar(100) = null output,
          @TOTALINCOMECOMPENSATION nvarchar(100) = null output,
          @TOTALREALESTATEASSETSCOUNT int = null output,
          @TOTALSECURITIESCOUNT int = null output,
          @TOTALOTHERASSETSCOUNT int = null output,
          @TOTALBUSINESSASSETSCOUNT int = null output,
          @TOTALINCOMECOMPENSATIONCOUNT int = null output,
          @REALESTATEDETAILS xml = null output,
          @SECURITIESDETAILS xml = null output,
          @OTHERASSETSDETAILS xml = null output,
          @BUSINESSESDETAILS xml = null output,
          @INCOMECOMPENSATIONDETAILS xml = null output,
          @ESTIMATEDWEALTHVALUE money = null output,
          @MAJORGIVINGCAPACITYVALUE money = null output,
          @SOCIALMEDIACOMPANY nvarchar(4000) = null output,
          @SOCIALMEDIAOCCUPATION nvarchar(2048) = null output,
          @SOCIALMEDIARATING int = null output,
          @INFLUENCERATING int = null output,
          @OUTREACHRATING int = null output,
          @PROFILEPHOTOS xml = null output,
          @SOCIALMEDIAACCOUNTURLS xml = null output
                ) as
                    set nocount on;

                    select
                        @DATALOADED = 1
                    from dbo.CONSTITUENT C
                    where @ID = C.ID;

                    select 
                        @MAJORGIVINGLIKELIHOOD = nullif(MP.MAJORGIVINGLIKELIHOOD, -1),
                        @ANNUALGIFTLIKELIHOOD = nullif(MP.ANNUALGIFTLIKELIHOOD, -1),
                        @PLANNEDGIFTLIKELIHOOD = nullif(MP.PLANNEDGIFTLIKELIHOOD, -1),
                        @TARGETGIFTRANGE = TGR.RANGE
                    from
                        dbo.MODELINGANDPROPENSITY MP
                    left join dbo.TARGETGIFTRANGE TGR on TGR.ID = MP.TARGETGIFTRANGEID
                    where
                        MP.ID = @ID;

                    select
                        @ESTIMATEDWEALTH = coalesce(EWFR.RANGE, '$0 and up'),
                        @MAJORGIVINGCAPACITY = coalesce(MGCFR.RANGE, '$0 and up'),
                        @OVERALLRATING = ORC.DESCRIPTION,
            @ESTIMATEDWEALTHVALUE = coalesce(ESTIMATEDWEALTHVALUE, 0),
            @MAJORGIVINGCAPACITYVALUE = coalesce(MAJORGIVINGCAPACITYVALUE, 0)
                    from
                        dbo.WEALTHCAPACITY WC    
                    left join dbo.OVERALLRATINGCODE ORC on ORC.ID = WC.OVERALLRATINGCODEID
                    left join dbo.UFN_ESTIMATEDWEALTH_GETFORMATTEDRANGES() EWFR on EWFR.ID = WC.ESTIMATEDWEALTHID
                    left join dbo.UFN_MAJORGIVINGCAPACITY_GETFORMATTEDRANGES() MGCFR on MGCFR.ID = WC.MAJORGIVINGCAPACITYID
                    where
                        WC.ID = @ID;

          if @ESTIMATEDWEALTHVALUE < 0 
                            set @ESTIMATEDWEALTHVALUE = 0;

          if @MAJORGIVINGCAPACITYVALUE < 0 
                            set @MAJORGIVINGCAPACITYVALUE = 0;

          select @TOTALREALESTATEASSETS = REALESTATEASSETSCONFIRMED,
                 @TOTALREALESTATEASSETSCOUNT = REALESTATENUMBERCONFIRMED,
                 @TOTALSECURITIES = SECURITIESASSETSCONFIRMED,
                 @TOTALSECURITIESCOUNT = SECURITIESNUMBERCONFIRMED,
                 @TOTALOTHERASSETS = OTHERASSETSCONFIRMED,
                 @TOTALOTHERASSETSCOUNT = OTHERASSETSNUMBERCONFIRMED,
                 @TOTALBUSINESSASSETS = BUSINESSOWNERSHIPASSETSCONFIRMED,
                 @TOTALBUSINESSASSETSCOUNT = BUSINESSOWNERSHIPNUMBERCONFIRMED,
                 @TOTALINCOMECOMPENSATION = INCOMECOMPENSATIONASSETSCONFIRMED,
                 @TOTALINCOMECOMPENSATIONCOUNT = INCOMECOMPENSATIONNUMBERCONFIRMED
          from dbo.WEALTH
          where ID = @ID;


                    set @CONTRIBUTIONS = dbo.UFN_RE7INTEGRATION_GETCONTRIBUTIONS_TOITEMLISTXML(@ID);
          set @REALESTATEDETAILS = dbo.UFN_RE7INTEGRATION_GETREALESTATE_TOITEMLISTXML(@ID, 3);
          set @SECURITIESDETAILS = dbo.UFN_RE7INTEGRATION_GETSECURITIES_TOITEMLISTXML(@ID, 3);
          set @OTHERASSETSDETAILS = dbo.UFN_RE7INTEGRATION_GETOTHERASSETS_TOITEMLISTXML(@ID, 3);
          set @BUSINESSESDETAILS = dbo.UFN_RE7INTEGRATION_GETBUSINESSES_TOITEMLISTXML(@ID, 3);
          set @INCOMECOMPENSATIONDETAILS = dbo.UFN_RE7INTEGRATION_GETINCOMECOMPENSATION_TOITEMLISTXML(@ID, 3);

          select
            @SOCIALMEDIACOMPANY = COMPANY,
            @SOCIALMEDIAOCCUPATION = OCCUPATION,
            @SOCIALMEDIARATING = SOCIALMEDIARATING,
            @INFLUENCERATING = INFLUENCERATING,
            @OUTREACHRATING = OUTREACHRATING
          from
            dbo.WPSOCIAL
          where
            ID = @ID

          set @PROFILEPHOTOS = dbo.UFN_RE7INTEGRATION_GETPROFILEPHOTOS_TOITEMLISTXML(@ID);
          set @SOCIALMEDIAACCOUNTURLS = dbo.UFN_RE7INTEGRATION_GETSOCIALMEDIAACCOUNTURLS_TOITEMLISTXML(@ID);

          /*Grab integration fields to consolidate calls to the DB */
                    select
                        @FROMRE7 = RE7INTEGRATIONCONFIGURATION.FROMRE7,
                        @TORE7 = RE7INTEGRATIONCONFIGURATION.TORE7
                    from
                        dbo.RE7INTEGRATIONCONFIGURATION;

                    set @FROMRE7SELECTEDFIELDS = replace(convert(nvarchar(max),dbo.UFN_RE7INTEGRATION_GETSELECTEDTORE7FIELDS_TOITEMLISTXML(0)),'RE7INTEGRATIONSELECTEDFIELDS','FROMRE7SELECTEDFIELDS');
                    set @TORE7SELECTEDFIELDS = replace(convert(nvarchar(max),dbo.UFN_RE7INTEGRATION_GETSELECTEDTORE7FIELDS_TOITEMLISTXML(1)),'RE7INTEGRATIONSELECTEDFIELDS','TORE7SELECTEDFIELDS');
          /* end integration fields */

                    return 0;