USP_DATALIST_OPPORTUNITIESBYLIKELIHOOD_DATE_AMOUNT
A datalist of opportunities filtered by likelihood, date range and amount range.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @DATEFILTER | tinyint | IN | |
| @CURRENTAPPUSERID | uniqueidentifier | IN | Input parameter indicating the ID of the current user. |
| @ORGPOSITIONSSELECTIONID | uniqueidentifier | IN | |
| @PROSPECTPLANTYPECODEID | uniqueidentifier | IN | Plan type |
| @RANGELOWERLIMIT | varchar(100) | IN | |
| @INCLUDEUNQUALIFIED | bit | IN | |
| @INCLUDEQUALIFIED | bit | IN | |
| @INCLUDERESPONSEPENDING | bit | IN | |
| @INCLUDEACCEPTED | bit | IN | |
| @LIKELIHOODID | uniqueidentifier | IN | |
| @NOLIKELIHOODSONLY | bit | IN | |
| @LIKELIHOODLIST | xml | IN | |
| @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. |
| @AMOUNTRANGEID | uniqueidentifier | IN | Amount range ID |
Definition
Copy
CREATE procedure dbo.USP_DATALIST_OPPORTUNITIESBYLIKELIHOOD_DATE_AMOUNT(
@DATEFILTER tinyint = 2,
@CURRENTAPPUSERID uniqueidentifier = null,
@ORGPOSITIONSSELECTIONID uniqueidentifier = null,
@PROSPECTPLANTYPECODEID uniqueidentifier = null,
@RANGELOWERLIMIT varchar(100) = '',
@INCLUDEUNQUALIFIED bit = 0,
@INCLUDEQUALIFIED bit = 0,
@INCLUDERESPONSEPENDING bit = 0,
@INCLUDEACCEPTED bit = 0,
@LIKELIHOODID uniqueidentifier = null,
@NOLIKELIHOODSONLY bit = 0,
@LIKELIHOODLIST as xml = null,
@SITEFILTERMODE tinyint = 0,
@SITESSELECTED xml = null,
@SECURITYFEATUREID uniqueidentifier = null,
@SECURITYFEATURETYPE tinyint = null,
@AMOUNTRANGEID uniqueidentifier = null
) as
set nocount on;
declare @STARTDATE datetime;
declare @ENDDATE datetime;
exec dbo.USP_RESOLVEDATEFILTER @DATEFILTER, @STARTDATE output, @ENDDATE output;
--If the lower limit is set to 0.00 and an amount range ID is given, do a sanity check to ensure that this is truly the lower limit.
if (@RANGELOWERLIMIT = '0.00' or @RANGELOWERLIMIT= '') and @AMOUNTRANGEID is not null
begin
select @RANGELOWERLIMIT = cast(LOWERLIMIT as varchar(100))
from dbo.OPPORTUNITYAMOUNTBRACKET
where ID = @AMOUNTRANGEID
end
declare @LIKELIHOODIDS table(TYPECODE uniqueidentifier);
if @LIKELIHOODID is null
begin
if @LIKELIHOODLIST is not null
begin
insert into @LIKELIHOODIDS(TYPECODE)
select
T.c.value('(LIKELIHOODID)[1]','uniqueidentifier') AS 'TYPECODE'
from @LIKELIHOODLIST.nodes('/LIKELIHOODID/ITEM') T(c)
end
else --@LIKELIHOODLIST is null
begin
insert into @LIKELIHOODIDS(TYPECODE)
select ID from LIKELIHOODTYPECODE;
insert into @LIKELIHOODIDS(TYPECODE) values(null);
end
end
else --@LIKELIHOODID is not null
begin
insert into @LIKELIHOODIDS(TYPECODE) values(@LIKELIHOODID)
end
declare @IDS as table(ID uniqueidentifier);
if @ORGPOSITIONSSELECTIONID is not null
begin
insert into @IDS exec USP_ORGANIZATIONHIERARCHY_IDSET_POSITIONHOLDERS @ORGPOSITIONSSELECTIONID, @CURRENTAPPUSERID;
end
if @NOLIKELIHOODSONLY=0
begin
select
OPPORTUNITY.ID,
PROSPECTPLAN.ID,
PROSPECTPLAN.PROSPECTID,
NF.NAME,
PROSPECTPLANTYPECODE.DESCRIPTION,
dbo.UFN_OPPORTUNITY_AMOUNTINCURRENCY(OPPORTUNITY.ID, OPPORTUNITYAMOUNTRANGES.BASECURRENCYID),
OPPORTUNITYAMOUNTRANGES.NAME,
coalesce(OPPORTUNITY.ASKDATE,OPPORTUNITY.EXPECTEDASKDATE),
dbo.UFN_PROSPECTPLAN_GETSITELIST(PROSPECTPLAN.ID) SITES,
OPPORTUNITYAMOUNTRANGES.BASECURRENCYID,
OPPORTUNITY.STATUS
from
dbo.OPPORTUNITY
inner join dbo.PROSPECTPLAN on PROSPECTPLAN.ID=OPPORTUNITY.PROSPECTPLANID
inner join dbo.PROSPECTPLANTYPECODE on PROSPECTPLANTYPECODE.ID=PROSPECTPLAN.PROSPECTPLANTYPECODEID
inner join dbo.UFN_OPPORTUNITYAMOUNTRANGES_BYGROUPANDUSER(@CURRENTAPPUSERID) OPPORTUNITYAMOUNTRANGES on dbo.UFN_OPPORTUNITY_AMOUNTINCURRENCY(OPPORTUNITY.ID, OPPORTUNITYAMOUNTRANGES.BASECURRENCYID) between OPPORTUNITYAMOUNTRANGES.LOWERLIMIT and OPPORTUNITYAMOUNTRANGES.UPPERLIMIT
cross apply dbo.UFN_CONSTITUENT_DISPLAYNAME(PROSPECTPLAN.PROSPECTID) NF
where
(@INCLUDEUNQUALIFIED = 1 or OPPORTUNITY.STATUSCODE <> 0)
and (@INCLUDEQUALIFIED = 1 or OPPORTUNITY.STATUSCODE <> 1)
and (@INCLUDERESPONSEPENDING = 1 or OPPORTUNITY.STATUSCODE <> 2)
and (@INCLUDEACCEPTED = 1 or OPPORTUNITY.STATUSCODE <> 3)
and OPPORTUNITY.STATUSCODE not in (4,5)
and (OPPORTUNITY.ASKDATE between @STARTDATE and @ENDDATE
or OPPORTUNITY.ASKDATE is null and OPPORTUNITY.EXPECTEDASKDATE between @STARTDATE and @ENDDATE)
and (@PROSPECTPLANTYPECODEID is null or PROSPECTPLAN.PROSPECTPLANTYPECODEID = @PROSPECTPLANTYPECODEID)
and (@RANGELOWERLIMIT = '' or CAST(@RANGELOWERLIMIT as money) = OPPORTUNITYAMOUNTRANGES.LOWERLIMIT)
and ((OPPORTUNITY.LIKELIHOODTYPECODEID is null
and (exists (select TYPECODE from @LIKELIHOODIDS where TYPECODE is null)))
or (OPPORTUNITY.LIKELIHOODTYPECODEID in (select TYPECODE from @LIKELIHOODIDS)))
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
)
)
and (@ORGPOSITIONSSELECTIONID is null or exists (
select SELECTION.ID
from @IDS as SELECTION
inner join dbo.ORGANIZATIONPOSITIONHOLDER as OPH on OPH.ID = SELECTION.ID
where
(OPH.CONSTITUENTID = PROSPECTPLAN.PRIMARYMANAGERFUNDRAISERID) or (OPH.CONSTITUENTID = PROSPECTPLAN.SECONDARYMANAGERFUNDRAISERID) or ((select count(SF.ID) from dbo.SECONDARYFUNDRAISER SF where SF.PROSPECTPLANID = PROSPECTPLAN.ID and SF.FUNDRAISERID = OPH.CONSTITUENTID) > 0)
and(
coalesce(OPPORTUNITY.RESPONSEDATE, OPPORTUNITY.ASKDATE, OPPORTUNITY.EXPECTEDASKDATE) between OPH.DATEFROM and coalesce(OPH.DATETO, convert(nvarchar(8), getdate(), 112))
or coalesce(OPPORTUNITY.RESPONSEDATE, OPPORTUNITY.ASKDATE, OPPORTUNITY.EXPECTEDASKDATE) is null
)
))
order by
OPPORTUNITY.AMOUNT desc;
end
else --@NOLIKELIHOODSONLY=1
begin
select
OPPORTUNITY.ID,
PROSPECTPLAN.ID,
PROSPECTPLAN.PROSPECTID,
NF.NAME,
PROSPECTPLANTYPECODE.DESCRIPTION,
dbo.UFN_OPPORTUNITY_AMOUNTINCURRENCY(OPPORTUNITY.ID, OPPORTUNITYAMOUNTRANGES.BASECURRENCYID),
OPPORTUNITYAMOUNTRANGES.NAME,
OPPORTUNITY.ASKDATE,
dbo.UFN_PROSPECTPLAN_GETSITELIST(PROSPECTPLAN.ID) SITES,
OPPORTUNITYAMOUNTRANGES.BASECURRENCYID,
OPPORTUNITY.STATUS
from
dbo.OPPORTUNITY
inner join dbo.PROSPECTPLAN on PROSPECTPLAN.ID=OPPORTUNITY.PROSPECTPLANID
inner join dbo.PROSPECTPLANTYPECODE on PROSPECTPLANTYPECODE.ID=PROSPECTPLAN.PROSPECTPLANTYPECODEID
inner join dbo.UFN_OPPORTUNITYAMOUNTRANGES_BYGROUPANDUSER(@CURRENTAPPUSERID) OPPORTUNITYAMOUNTRANGES on dbo.UFN_OPPORTUNITY_AMOUNTINCURRENCY(OPPORTUNITY.ID, OPPORTUNITYAMOUNTRANGES.BASECURRENCYID) between OPPORTUNITYAMOUNTRANGES.LOWERLIMIT and OPPORTUNITYAMOUNTRANGES.UPPERLIMIT
cross apply dbo.UFN_CONSTITUENT_DISPLAYNAME(PROSPECTPLAN.PROSPECTID) NF
where
(@INCLUDEUNQUALIFIED = 1 or OPPORTUNITY.STATUSCODE <> 0)
and (@INCLUDEQUALIFIED = 1 or OPPORTUNITY.STATUSCODE <> 1)
and (@INCLUDERESPONSEPENDING = 1 or OPPORTUNITY.STATUSCODE <> 2)
and (@INCLUDEACCEPTED = 1 or OPPORTUNITY.STATUSCODE <> 3)
and OPPORTUNITY.STATUSCODE not in (4,5)
and (OPPORTUNITY.ASKDATE between @STARTDATE and @ENDDATE
or OPPORTUNITY.ASKDATE is null and OPPORTUNITY.EXPECTEDASKDATE between @STARTDATE and @ENDDATE)
and (@PROSPECTPLANTYPECODEID is null or PROSPECTPLAN.PROSPECTPLANTYPECODEID = @PROSPECTPLANTYPECODEID)
and (@RANGELOWERLIMIT = '' or @RANGELOWERLIMIT = OPPORTUNITYAMOUNTRANGES.LOWERLIMIT)
and OPPORTUNITY.LIKELIHOODTYPECODEID is null
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
)
)
and (@ORGPOSITIONSSELECTIONID is null or exists (
select SELECTION.ID
from @IDS as SELECTION
inner join dbo.ORGANIZATIONPOSITIONHOLDER as OPH on OPH.ID = SELECTION.ID
where
(OPH.CONSTITUENTID = PROSPECTPLAN.PRIMARYMANAGERFUNDRAISERID) or (OPH.CONSTITUENTID = PROSPECTPLAN.SECONDARYMANAGERFUNDRAISERID) or ((select count(SF.ID) from dbo.SECONDARYFUNDRAISER SF where SF.PROSPECTPLANID = PROSPECTPLAN.ID and SF.FUNDRAISERID = OPH.CONSTITUENTID) > 0)
and(
coalesce(OPPORTUNITY.RESPONSEDATE, OPPORTUNITY.ASKDATE, OPPORTUNITY.EXPECTEDASKDATE) between OPH.DATEFROM and coalesce(OPH.DATETO, convert(nvarchar(8), getdate(), 112))
or coalesce(OPPORTUNITY.RESPONSEDATE, OPPORTUNITY.ASKDATE, OPPORTUNITY.EXPECTEDASKDATE) is null
)
))
order by
OPPORTUNITY.AMOUNT desc;
end