USP_DATALIST_PROSPECTSNORECENTSTEPS
List of prospects with no completed steps in the past 90 days.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @CURRENTAPPUSERID | uniqueidentifier | IN | Input parameter indicating the ID of the current user. |
| @ORGPOSITIONSSELECTIONID | uniqueidentifier | IN | Show for |
| @NUMBEROFDAYS | smallint | IN | No steps for |
| @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_PROSPECTSNORECENTSTEPS
(
@CURRENTAPPUSERID uniqueidentifier,
@ORGPOSITIONSSELECTIONID uniqueidentifier = null,
@NUMBEROFDAYS smallint = 90,
@SECURITYFEATUREID uniqueidentifier = null,
@SECURITYFEATURETYPE tinyint = null
)
as begin
set nocount on;
declare @ISSYSADMIN bit = dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID);
declare @TODAY datetime;
set @TODAY = dbo.UFN_DATE_GETEARLIESTTIME(getdate());
if @NUMBEROFDAYS = 0
set @NUMBEROFDAYS = datediff(day, dateadd(year, -1, @TODAY), @TODAY);
declare @CURRENTDATE datetime;
set @CURRENTDATE = getdate();
declare @UPPERBOUND datetime;
set @UPPERBOUND = dbo.UFN_DATE_GETLATESTTIME(@CURRENTDATE);
declare @LOWERBOUND datetime;
set @LOWERBOUND = dbo.UFN_DATE_GETEARLIESTTIME(@CURRENTDATE);
if @ORGPOSITIONSSELECTIONID is null
select
PC.ID,
PP.ID,
PC_NF.NAME,
case when PP.ID is null then '' else
dbo.UFN_PROSPECTPLANTYPECODE_GETDESCRIPTION(PP.PROSPECTPLANTYPECODEID) + ' - '
+ dbo.UFN_PROSPECTPLANSTATUSCODE_GETDESCRIPTION(PP.PROSPECTPLANSTATUSCODEID)
end,
LCI.OBJECTIVE,
LCI.ACTUALDATE,
NPI.ID,
NPI.OBJECTIVE,
NPI.EXPECTEDDATE,
PROMGR_NF.NAME,
PRIMGR_NF.NAME
from
dbo.CONSTITUENT PC
outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(PC.ID) PC_NF
inner join dbo.PROSPECTDATERANGE PDR on PDR.CONSTITUENTID = PC.ID and
(PDR.DATEFROM <= @UPPERBOUND or PDR.DATEFROM is null) and
(PDR.DATETO >= @LOWERBOUND or PDR.DATETO is null)
left outer join dbo.PROSPECT P on P.ID=PC.ID
left outer join dbo.PROSPECTPLAN PP on PP.PROSPECTID=PC.ID
left outer join dbo.INTERACTION LCI on LCI.ID = dbo.UFN_PROSPECTPLAN_GETLASTSTEP(PP.ID)
left outer join dbo.INTERACTION NPI on NPI.ID = dbo.UFN_PROSPECTPLAN_GETNEXTSTEP(PP.ID)
outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(P.PROSPECTMANAGERFUNDRAISERID) PROMGR_NF
outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(PP.PRIMARYMANAGERFUNDRAISERID) PRIMGR_NF
left outer join dbo.INTERACTION I on I.PROSPECTPLANID = PP.ID
where
@ISSYSADMIN = 1 or
(
(
select count(*) from dbo.UFN_SITEID_MAPFROM_PROSPECTPLANID(PP.ID) as SITE
where (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[SITE].[SITEID] or (SITEID is null and [SITE].[SITEID] is null)))
) > 0
and
(
select count(*) from dbo.UFN_SITEID_MAPFROM_CONSTITUENTID(PC.ID) as SITE
where (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[SITE].[SITEID] or (SITEID is null and [SITE].[SITEID] is null)))
) > 0
and dbo.UFN_SECURITY_APPUSER_GRANTED_DATALIST_FORCONSTIT(@CURRENTAPPUSERID,
'D570639D-3513-4BC0-A279-173A9D3C9EFA', PC.ID) = 1
)
group by
PC.ID,
PP.ID,
PC_NF.NAME,
case when PP.ID is null then '' else
dbo.UFN_PROSPECTPLANTYPECODE_GETDESCRIPTION(PP.PROSPECTPLANTYPECODEID) + ' - '
+ dbo.UFN_PROSPECTPLANSTATUSCODE_GETDESCRIPTION(PP.PROSPECTPLANSTATUSCODEID)
end,
LCI.OBJECTIVE,
LCI.ACTUALDATE,
NPI.ID,
NPI.OBJECTIVE,
NPI.EXPECTEDDATE,
PROMGR_NF.NAME,
PRIMGR_NF.NAME,
PC.KEYNAME
having
datediff(day,max(I.ACTUALDATE),@TODAY) >= @NUMBEROFDAYS
order by
PC.KEYNAME, PC_NF.NAME
else
begin
declare @IDS as table(ID uniqueidentifier);
insert into @IDS exec USP_ORGANIZATIONHIERARCHY_IDSET_POSITIONHOLDERS @ORGPOSITIONSSELECTIONID, @CURRENTAPPUSERID;
select
PC.ID,
PP.ID,
PC_NF.NAME,
case when PP.ID is null then '' else
dbo.UFN_PROSPECTPLANTYPECODE_GETDESCRIPTION(PP.PROSPECTPLANTYPECODEID) + ' - '
+ dbo.UFN_PROSPECTPLANSTATUSCODE_GETDESCRIPTION(PP.PROSPECTPLANSTATUSCODEID)
end,
LCI.OBJECTIVE,
LCI.ACTUALDATE,
NPI.ID,
NPI.OBJECTIVE,
NPI.EXPECTEDDATE,
PROMGR_NF.NAME,
PRIMGR_NF.NAME
from
dbo.CONSTITUENT PC
outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(PC.ID) PC_NF
inner join dbo.PROSPECTDATERANGE PDR on PDR.CONSTITUENTID = PC.ID and
(PDR.DATEFROM <= @UPPERBOUND or PDR.DATEFROM is null) and
(PDR.DATETO >= @LOWERBOUND or PDR.DATETO is null)
left outer join dbo.PROSPECT P on P.ID=PC.ID
left outer join dbo.PROSPECTPLAN PP on PP.PROSPECTID=PC.ID
left outer join dbo.INTERACTION LCI on LCI.ID = dbo.UFN_PROSPECTPLAN_GETLASTSTEP(PP.ID)
left outer join dbo.INTERACTION NPI on NPI.ID = dbo.UFN_PROSPECTPLAN_GETNEXTSTEP(PP.ID)
outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(P.PROSPECTMANAGERFUNDRAISERID) PROMGR_NF
outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(PP.PRIMARYMANAGERFUNDRAISERID) PRIMGR_NF
left outer join dbo.INTERACTION I on I.PROSPECTPLANID = PP.ID
inner join dbo.ORGANIZATIONPOSITIONHOLDER OPH
on OPH.CONSTITUENTID = PP.PRIMARYMANAGERFUNDRAISERID and @TODAY between OPH.DATEFROM and coalesce(OPH.DATETO, @TODAY)
inner join @IDS as SELECTION on OPH.[ID] = SELECTION.[ID]
where
@ISSYSADMIN = 1 or
(
(
select count(*) from dbo.UFN_SITEID_MAPFROM_PROSPECTPLANID(PP.ID) as SITE
where (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[SITE].[SITEID] or (SITEID is null and [SITE].[SITEID] is null)))
) > 0
and
(
select count(*) from dbo.UFN_SITEID_MAPFROM_CONSTITUENTID(PC.ID) as SITE
where (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[SITE].[SITEID] or (SITEID is null and [SITE].[SITEID] is null)))
) > 0
and dbo.UFN_SECURITY_APPUSER_GRANTED_DATALIST_FORCONSTIT(@CURRENTAPPUSERID,
'D570639D-3513-4BC0-A279-173A9D3C9EFA', PC.ID) = 1
)
group by
PC.ID,
PP.ID,
PC_NF.NAME,
case when PP.ID is null then '' else
dbo.UFN_PROSPECTPLANTYPECODE_GETDESCRIPTION(PP.PROSPECTPLANTYPECODEID) + ' - '
+ dbo.UFN_PROSPECTPLANSTATUSCODE_GETDESCRIPTION(PP.PROSPECTPLANSTATUSCODEID)
end,
LCI.OBJECTIVE,
LCI.ACTUALDATE,
NPI.ID,
NPI.OBJECTIVE,
NPI.EXPECTEDDATE,
PROMGR_NF.NAME,
PRIMGR_NF.NAME,
PC.KEYNAME
having
datediff(day,max(I.ACTUALDATE),@TODAY) >= @NUMBEROFDAYS
order by
PC.KEYNAME, PC_NF.NAME
end
end