USP_DATALIST_PROSPECTOPPORTUNITY_CAMPAIGN

A datalist of campaigns for a given opportunity.

Parameters

Parameter	Parameter Type	Mode	Description
@PROSPECTID	uniqueidentifier	IN	Input parameter indicating the context ID for the data list.
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
@TYPEID	uniqueidentifier	IN	Opportunity type
@STATUS	tinyint	IN	Opportunity status
@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_PROSPECTOPPORTUNITY_CAMPAIGN
                (
                    @PROSPECTID uniqueidentifier,
                    @CURRENTAPPUSERID uniqueidentifier,
                    @TYPEID uniqueidentifier = null,
                    @STATUS tinyint = null,
                    @SITEFILTERMODE tinyint = 0,
                    @SITESSELECTED xml = null,
                    @SECURITYFEATUREID uniqueidentifier = null,
                    @SECURITYFEATURETYPE tinyint = null
                )
                as begin

                    select 
                        OPPORTUNITYCAMPAIGN.ID,
                        CAMPAIGN.ID as CAMPAIGNID,
                        CAMPAIGN.USERID as CAMPAIGNNAME,
                        CAMPAIGN.ENDDATE as CAMPAIGNENDDATE,
                        PROSPECTPLAN.NAME as PLANNAME,
                        dbo.UFN_PROSPECTPLANSTATUSCODE_GETDESCRIPTION(PROSPECTPLAN.PROSPECTPLANSTATUSCODEID) as PLANSTAGE,
                        dbo.UFN_PROSPECTPLAN_GETSITELIST(PROSPECTPLAN.ID) as PLANSITES,
                        OPPORTUNITY.ID as OPPORTUNITYID,
                        OPPORTUNITY.STATUS as OPPORTUNITYSTATUS,
                        OPPORTUNITYTYPECODE.DESCRIPTION as OPPORTUNITYTYPE,
                        OPPORTUNITY.AMOUNT as OPPORTUNITYAMOUNT,
                        OPPORTUNITY.BASECURRENCYID
                    from
                        dbo.OPPORTUNITYCAMPAIGN
                        inner join dbo.CAMPAIGN on CAMPAIGN.ID = OPPORTUNITYCAMPAIGN.CAMPAIGNID
                        inner join dbo.OPPORTUNITY on OPPORTUNITY.ID = OPPORTUNITYCAMPAIGN.OPPORTUNITYID
                        inner join dbo.PROSPECTPLAN on PROSPECTPLAN.ID = OPPORTUNITY.PROSPECTPLANID
                        left join dbo.OPPORTUNITYTYPECODE on OPPORTUNITYTYPECODE.ID = OPPORTUNITY.OPPORTUNITYTYPECODEID
                    where
                        PROSPECTPLAN.PROSPECTID=@PROSPECTID
                        and (@STATUS is null or @STATUS=OPPORTUNITY.STATUSCODE)
                        and (@TYPEID is null or OPPORTUNITYTYPECODE.ID=@TYPEID)
                        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) SITEFILTER
                                        inner join dbo.PROSPECTPLANSITE on PROSPECTPLANSITE.SITEID = SITEFILTER.SITEID
                                )
                        )
                    order by
                        CAMPAIGN.USERID,
                        PROSPECTPLAN.NAME,dbo.UFN_PROSPECTPLANSTATUSCODE_GETDESCRIPTION(PROSPECTPLAN.PROSPECTPLANSTATUSCODEID),
                        OPPORTUNITY.STATUS,OPPORTUNITYTYPECODE.DESCRIPTION,OPPORTUNITY.ASKAMOUNT

                end