USP_DATALIST_UNASSIGNEDPROSPECTS
List of prospects without a primary manager assigned.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @CURRENTAPPUSERID | uniqueidentifier | IN | Input parameter indicating the ID of the current user. |
| @ORGPOSITIONSSELECTIONID | uniqueidentifier | IN | Show for |
| @PROSPECTSTATUSCODEID | uniqueidentifier | IN | Prospect status |
| @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_UNASSIGNEDPROSPECTS
(
@CURRENTAPPUSERID uniqueidentifier,
@ORGPOSITIONSSELECTIONID uniqueidentifier = null,
@PROSPECTSTATUSCODEID uniqueidentifier = null,
@SECURITYFEATUREID uniqueidentifier = null,
@SECURITYFEATURETYPE tinyint = null
) as begin
set nocount on;
declare @TODAY datetime;
set @TODAY = dbo.UFN_DATE_GETEARLIESTTIME(getdate());
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);
declare @ISSYSADMIN bit;
set @ISSYSADMIN = dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID);
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,
dbo.UFN_PROSPECTSTATUSCODE_GETDESCRIPTION(P.PROSPECTSTATUSCODEID),
(case when PP.SECONDARYMANAGERFUNDRAISERID is null then 0 else 1 end) HASSECONDARYMANAGER
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
where
PP.PRIMARYMANAGERFUNDRAISERID is null
and (
@PROSPECTSTATUSCODEID is null
or @PROSPECTSTATUSCODEID = P.PROSPECTSTATUSCODEID
)
and (
@ISSYSADMIN = 1
or (
dbo.UFN_SECURITY_APPUSER_GRANTED_DATALIST_FORCONSTIT(@CURRENTAPPUSERID, 'B47A172B-3DF8-4a04-9B34-696112F65B04', PC.ID) = 1
and
(
(
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
or
(
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
)
)
)
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,
dbo.UFN_PROSPECTSTATUSCODE_GETDESCRIPTION(P.PROSPECTSTATUSCODEID),
(case when PP.SECONDARYMANAGERFUNDRAISERID is null then 0 else 1 end) HASSECONDARYMANAGER
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)
inner 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)
inner join dbo.ORGANIZATIONPOSITIONHOLDER OPH
on OPH.CONSTITUENTID = P.PROSPECTMANAGERFUNDRAISERID and @TODAY between OPH.DATEFROM and coalesce(OPH.DATETO, @TODAY)
inner join @IDS as SELECTION on OPH.[ID] = SELECTION.[ID]
outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(P.PROSPECTMANAGERFUNDRAISERID) PROMGR_NF
where
PP.PRIMARYMANAGERFUNDRAISERID is null
and (
@PROSPECTSTATUSCODEID is null
or @PROSPECTSTATUSCODEID = P.PROSPECTSTATUSCODEID
)
and (
@ISSYSADMIN = 1
or (
dbo.UFN_SECURITY_APPUSER_GRANTED_DATALIST_FORCONSTIT(@CURRENTAPPUSERID, 'B47A172B-3DF8-4a04-9B34-696112F65B04', PC.ID) = 1
and
(
(
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
or
(
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
)
)
)
order by
PC.KEYNAME, PC_NF.NAME
end
end