USP_DATALIST_RE7INTEGRATIONRESEARCHGROUP_SYNC

Returns the constituent information needed to sync a research group to RE7.
Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	IN	Input parameter indicating the context ID for the data list.
Definition

Copy


                CREATE procedure dbo.USP_DATALIST_RE7INTEGRATIONRESEARCHGROUP_SYNC (
                    @ID uniqueidentifier
                ) as
                    set nocount on;

                    select
                        RESEARCHGROUPMEMBER.CONSTITUENTID,
                        RE7INTEGRATIONCONSTITUENTMAP.RE7RECORDID,
                        nullif(MODELINGANDPROPENSITY.MAJORGIVINGLIKELIHOOD, -1) MAJORGIVINGLIKELIHOOD,
                        nullif(MODELINGANDPROPENSITY.ANNUALGIFTLIKELIHOOD, -1) ANNUALGIFTLIKELIHOOD,
                        nullif(MODELINGANDPROPENSITY.PLANNEDGIFTLIKELIHOOD, -1) PLANNEDGIFTLIKELIHOOD,
                        TARGETGIFTRANGE.RANGE TARGETGIFTRANGE,
                        EWFR.RANGE ESTIMATEDWEALTH,
                        MGCFR.RANGE MAJORGIVINGCAPACITY,
                        OVERALLRATINGCODE.DESCRIPTION OVERALLRATING,
                        (select sum(PROPERTYVALUATION) from dbo.WPREALESTATE where WEALTHID=RESEARCHGROUPMEMBER.CONSTITUENTID and CONFIRMED=1 and HISTORICCODE <> 1) TOTALREALESTATEASSETS,
                        (select sum(VALUEDIRECT) from dbo.WPSECURITIES where WEALTHID=RESEARCHGROUPMEMBER.CONSTITUENTID and CONFIRMED=1) TOTALSECURITIES,
                        (select sum(VALUE) from dbo.WPOTHERASSET where WEALTHID=RESEARCHGROUPMEMBER.CONSTITUENTID and CONFIRMED=1) TOTALOTHERASSETS,
                        (select dbo.UFN_RE7INTEGRATION_GETCONTRIBUTIONS_TOITEMLISTXML(RESEARCHGROUPMEMBER.CONSTITUENTID)) CONTRIBUTIONS
                    from
                        dbo.RESEARCHGROUPMEMBER
                    inner join dbo.RE7INTEGRATIONCONSTITUENTMAP    on
                        RE7INTEGRATIONCONSTITUENTMAP.ID = RESEARCHGROUPMEMBER.CONSTITUENTID
                    left join dbo.MODELINGANDPROPENSITY on
                        MODELINGANDPROPENSITY.ID = RESEARCHGROUPMEMBER.CONSTITUENTID
                    left join dbo.TARGETGIFTRANGE on 
                        TARGETGIFTRANGE.ID = MODELINGANDPROPENSITY.TARGETGIFTRANGEID
                    left join dbo.WEALTHCAPACITY on
                        WEALTHCAPACITY.ID = RESEARCHGROUPMEMBER.CONSTITUENTID    
                    left join dbo.OVERALLRATINGCODE on 
                        OVERALLRATINGCODE.ID = WEALTHCAPACITY.OVERALLRATINGCODEID
                    left join dbo.UFN_ESTIMATEDWEALTH_GETFORMATTEDRANGES() EWFR on 
                        EWFR.ID = WEALTHCAPACITY.ESTIMATEDWEALTHID
                    left join dbo.UFN_MAJORGIVINGCAPACITY_GETFORMATTEDRANGES() MGCFR on 
                        MGCFR.ID = WEALTHCAPACITY.MAJORGIVINGCAPACITYID
                    where
                        RESEARCHGROUPMEMBER.RESEARCHGROUPID = @ID