USP_DATALIST_REVENUEOPPORTUNITYCANDIDATE

Datalist of outstanding opportunities associated with a constituent and related household members.
Parameters

Parameter	Parameter Type	Mode	Description
@CONSTITUENTID	uniqueidentifier	IN	Input parameter indicating the context ID for the data list.
@OPPORTUNITYID	uniqueidentifier	IN
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
@SECURITYFEATUREID	uniqueidentifier	IN	Input parameter indicating the ID of the feature to use for site security checking.
@SECURITYFEATURETYPE	tinyint	IN	Input parameter indicating the type of the feature to use for site security checking.
Definition

Copy


                CREATE procedure dbo.USP_DATALIST_REVENUEOPPORTUNITYCANDIDATE
                (
                    @CONSTITUENTID uniqueidentifier,
                    @OPPORTUNITYID uniqueidentifier = null,
                    @CURRENTAPPUSERID uniqueidentifier,
                    @SECURITYFEATUREID uniqueidentifier = null,
                    @SECURITYFEATURETYPE tinyint = null
                ) as
                    set nocount on;

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

                    -- the record if it's a household, or the household an individual belongs to    

                    declare @HOUSEHOLDID uniqueidentifier; 
                    if dbo.UFN_CONSTITUENT_ISHOUSEHOLD(@CONSTITUENTID) = 1
                        set @HOUSEHOLDID = @CONSTITUENTID;
                    else
                        select
                            @HOUSEHOLDID = GM.GROUPID
                        from
                            dbo.GROUPMEMBER GM
                        left outer join
                            dbo.GROUPMEMBERDATERANGE GMDR on GMDR.GROUPMEMBERID = GM.ID
                        left outer join
                            dbo.GROUPDATA GD on GD.ID = GM.GROUPID
                        where
                            GM.MEMBERID = @CONSTITUENTID
                        and
                            GD.GROUPTYPECODE = 0
                        and ((GMDR.DATEFROM is null and (GMDR.DATETO is null or GMDR.DATETO > @CURRENTDATE))
                            or (GMDR.DATETO is null and (GMDR.DATEFROM is null or GMDR.DATEFROM <= @CURRENTDATE)) 
                            or (GMDR.DATEFROM <= @CURRENTDATE and GMDR.DATETO > @CURRENTDATE));

                    with HOUSEHOLDANDMEMBERS(ID) as
                    (
                        select
                            GM.MEMBERID as ID
                        from
                            dbo.GROUPMEMBER GM
                        left outer join
                            dbo.GROUPMEMBERDATERANGE GMDR on GMDR.GROUPMEMBERID = GM.ID
                        where
                            GM.GROUPID = @HOUSEHOLDID
                        and ((GMDR.DATEFROM is null and (GMDR.DATETO is null or GMDR.DATETO > @CURRENTDATE))
                            or (GMDR.DATETO is null and (GMDR.DATEFROM is null or GMDR.DATEFROM <= @CURRENTDATE)) 
                            or (GMDR.DATEFROM <= @CURRENTDATE and GMDR.DATETO > @CURRENTDATE))
                        union
                        select @HOUSEHOLDID
                        union
                        select @CONSTITUENTID
                    )
                    select
                        OPPORTUNITY.ID,
                        NF.NAME,
                        OPPORTUNITY.TRANSACTIONASKAMOUNT,
                        OPPORTUNITY.ASKDATE,
                        OPPORTUNITY.STATUS,
                                    OPPORTUNITY.TRANSACTIONCURRENCYID
                    from
                        dbo.OPPORTUNITY
                        inner join dbo.PROSPECTPLAN on OPPORTUNITY.PROSPECTPLANID = PROSPECTPLAN.ID
                        outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(PROSPECTPLAN.PROSPECTID) NF
                    where 
                        (
                            OPPORTUNITY.ID = @OPPORTUNITYID 
                            or
                            PROSPECTPLAN.PROSPECTID in (select ID from HOUSEHOLDANDMEMBERS)
                        ) and 
                        OPPORTUNITY.STATUSCODE in (1,2,3)
                        and (
                            select count(*) 
                            from dbo.UFN_SITEID_MAPFROM_PROSPECTPLANID(PROSPECTPLAN.ID) as PROSPECTPLANSITE 
                            where (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[PROSPECTPLANSITE].[SITEID] or (SITEID is null and [PROSPECTPLANSITE].[SITEID] is null)))
                        ) > 0
                    order by
                        OPPORTUNITY.ASKDATE desc