USP_DATALIST_CAMPAIGNOPPORTUNITY

Returns all of the opportunities that reference a given campaign.

Parameters

Parameter	Parameter Type	Mode	Description
@CAMPAIGNID	uniqueidentifier	IN	Input parameter indicating the context ID for the data list.
@STATUSCODE	tinyint	IN	Opportunity status
@OPPORTUNITYTYPECODEID	uniqueidentifier	IN	Opportunity type
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
@SITEFILTERMODE	tinyint	IN	Sites
@SITESSELECTED	xml	IN
@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_CAMPAIGNOPPORTUNITY
                (
                    @CAMPAIGNID uniqueidentifier,
                    @STATUSCODE tinyint = null,
                    @OPPORTUNITYTYPECODEID uniqueidentifier = null,
                    @CURRENTAPPUSERID uniqueidentifier = null,
                    @SITEFILTERMODE tinyint = 0,
                    @SITESSELECTED xml = null,
                    @SECURITYFEATUREID uniqueidentifier = null,
                    @SECURITYFEATURETYPE tinyint = null
                ) 
                as 
                begin                
                    select distinct        
                        OPPORTUNITY.ID,
                        dbo.UFN_CONSTITUENT_BUILDNAME(PROSPECTPLAN.PROSPECTID) as CONSTITUENT,    
                        PROSPECTPLAN.NAME,
                        dbo.UFN_PROSPECTPLANTYPECODE_GETDESCRIPTION(PROSPECTPLAN.PROSPECTPLANTYPECODEID) as PLANTYPE,
                        dbo.UFN_PROSPECTPLAN_GETSITELIST(PROSPECTPLAN.ID) as PROSPECTPLANSITES,
                        OPPORTUNITY.STATUS,
                        dbo.UFN_OPPORTUNITYTYPECODE_GETDESCRIPTION(OPPORTUNITY.OPPORTUNITYTYPECODEID) as OPPORTUNITYTYPE,
                        OPPORTUNITY.AMOUNT as OPPORTUNITYAMOUNT,
                        OPPORTUNITY.BASECURRENCYID
                    from
                        dbo.OPPORTUNITY
                        inner join dbo.OPPORTUNITYCAMPAIGN on OPPORTUNITYCAMPAIGN.OPPORTUNITYID = OPPORTUNITY.ID
                        inner join dbo.PROSPECTPLAN on PROSPECTPLAN.ID = OPPORTUNITY.PROSPECTPLANID 
                    where
                        OPPORTUNITYCAMPAIGN.CAMPAIGNID = @CAMPAIGNID and
                        (@STATUSCODE is null or OPPORTUNITY.STATUSCODE = @STATUSCODE) and
                        (@OPPORTUNITYTYPECODEID is null or OPPORTUNITY.OPPORTUNITYTYPECODEID = @OPPORTUNITYTYPECODEID)
                        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
                        and (
                            @SITEFILTERMODE = 0
                                or PROSPECTPLAN.ID in ( 
                                    select 
                                        PROSPECTPLANSITE.PROSPECTPLANID
                                    from 
                                        dbo.UFN_SITE_BUILDDATALISTSITEFILTER (@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED) as SITEFILTER
                                        inner join dbo.PROSPECTPLANSITE on PROSPECTPLANSITE.SITEID = SITEFILTER.SITEID
                                )
                        )

                end