USP_DATALIST_PROSPECTRESEARCHREQUESTQUEUE
This datalist displays a list of prospect research requests.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @CONTEXTID | uniqueidentifier | IN | Input parameter indicating the context ID for the data list. |
| @CURRENTAPPUSERID | uniqueidentifier | IN | Input parameter indicating the ID of the current user. |
| @ORGPOSITIONSSELECTIONID | uniqueidentifier | IN | Show for |
| @STATUSCODE | tinyint | IN | Status |
| @FUNDRAISERID | uniqueidentifier | IN | Researcher |
| @NUMBERTOSHOWCODE | tinyint | IN | Request date |
| @DUEDATECODE | tinyint | IN | Due date |
| @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_PROSPECTRESEARCHREQUESTQUEUE(
@CONTEXTID uniqueidentifier,
@CURRENTAPPUSERID uniqueidentifier,
@ORGPOSITIONSSELECTIONID uniqueidentifier = null,
@STATUSCODE tinyint = 99,
@FUNDRAISERID uniqueidentifier = null,
@NUMBERTOSHOWCODE tinyint = 0,
@DUEDATECODE tinyint = 0,
@SITEFILTERMODE tinyint = 0,
@SITESSELECTED xml = null,
@SECURITYFEATUREID uniqueidentifier = null,
@SECURITYFEATURETYPE tinyint = null
)
as
set nocount on;
declare @CURRENTDATE datetime;
set @CURRENTDATE = getdate();
--Request date filter
declare @STARTDATE datetime;
if @NUMBERTOSHOWCODE = 1 --Today
set @STARTDATE = dbo.UFN_DATE_GETEARLIESTTIME(@CURRENTDATE);
else if @NUMBERTOSHOWCODE = 2 --Last 7 Days
set @STARTDATE = dbo.UFN_DATE_GETEARLIESTTIME(dateadd(day,-6, @CURRENTDATE));
else if @NUMBERTOSHOWCODE = 3 --Last 30 Days
set @STARTDATE = dbo.UFN_DATE_GETEARLIESTTIME(dateadd(day,-29,@CURRENTDATE));
else if @NUMBERTOSHOWCODE = 4 --Last 90 Days
set @STARTDATE = dbo.UFN_DATE_GETEARLIESTTIME(dateadd(day,-89,@CURRENTDATE));
else if @NUMBERTOSHOWCODE = 5 --Last 6 Months
set @STARTDATE = dbo.UFN_DATE_GETEARLIESTTIME(dateadd(day,1,dateadd(month,-6,@CURRENTDATE)));
else if @NUMBERTOSHOWCODE = 6 --Last Year
set @STARTDATE = dbo.UFN_DATE_GETEARLIESTTIME(dateadd(day,1,dateadd(year,-1,@CURRENTDATE)))
else
set @STARTDATE = @CURRENTDATE;
declare @ENDDATE datetime;
set @ENDDATE = dbo.UFN_DATE_GETLATESTTIME(@CURRENTDATE);
--Due date filter
declare @DUEDATESTART datetime;
set @DUEDATESTART = dbo.UFN_DATE_GETEARLIESTTIME(@CURRENTDATE);
declare @DUEDATEEND datetime;
if @DUEDATECODE = 1 --Today
set @DUEDATEEND = dbo.UFN_DATE_GETLATESTTIME(@CURRENTDATE);
else if @DUEDATECODE = 2 --Tomorrow
set @DUEDATEEND = dbo.UFN_DATE_GETLATESTTIME(dateadd(day,1, @CURRENTDATE));
else if @DUEDATECODE = 3 --Next 7 days
set @DUEDATEEND = dbo.UFN_DATE_GETLATESTTIME(dateadd(day,7,@CURRENTDATE));
else if @DUEDATECODE = 4 --Next 14 days
set @DUEDATEEND = dbo.UFN_DATE_GETLATESTTIME(dateadd(day,14,@CURRENTDATE));
else
set @DUEDATEEND = @CURRENTDATE;
if @ORGPOSITIONSSELECTIONID is null
select PRR.ID,
PRR.REQUESTNO,
PRR.DUEDATE,
PRR.STATUS,
PRR.RECORDTYPE,
(select count(ID) from dbo.PROSPECTRESEARCHREQUESTCONSTITUENT where PROSPECTRESEARCHREQUESTID = PRR.ID and PROSPECTRESEARCHREQUESTCONSTITUENT.STATUSCODE <> 6) as NUMBEROFCONSTITUENTS,
PRRPC.DESCRIPTION as [PRIORITYCODE],
PRRTC.DESCRIPTION as [REQUESTTYPECODE],
NF.NAME as [ASSIGNEDTO],
PRR.STATUSCODE,
PRR.ASSIGNEDTOID,
(
select dbo.UDA_BUILDLIST(SITE.NAME)
from dbo.PROSPECTRESEARCHREQUESTSITE
inner join dbo.SITE on SITE.ID = PROSPECTRESEARCHREQUESTSITE.SITEID
where PROSPECTRESEARCHREQUESTID = PRR.ID
) as [SITES]
from
dbo.PROSPECTRESEARCHREQUEST PRR
left join
dbo.PROSPECTRESEARCHREQUESTPRIORITYCODE PRRPC on PRRPC.ID = PRR.PROSPECTRESEARCHREQUESTPRIORITYCODEID
left join
dbo.PROSPECTRESEARCHREQUESTTYPECODE PRRTC on PRRTC.ID = PRR.PROSPECTRESEARCHREQUESTTYPECODEID
left join
dbo.APPUSER AU on PRR.ASSIGNEDTOID = AU.CONSTITUENTID
left join
dbo.PROSPECTRESEARCHREQUESTAPPUSERPERMISSION PRRAUP on PRRAUP.ID = @CURRENTAPPUSERID
outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(PRR.ASSIGNEDTOID) NF
where
(@NUMBERTOSHOWCODE = 0 or PRR.DATEADDED between @STARTDATE and @ENDDATE) and
(@DUEDATECODE = 0 or PRR.DUEDATE between @DUEDATESTART and @DUEDATEEND) and
((@STATUSCODE = 99 or PRR.STATUSCODE = @STATUSCODE) and PRR.STATUSCODE not in (4, 6)) and
((PRR.ASSIGNEDTOID = @FUNDRAISERID) or @FUNDRAISERID is null) and
(AU.ID = @CURRENTAPPUSERID or PRRAUP.CANVIEWREQUESTSASSIGNEDTOOTHERS = 1 or PRR.ASSIGNEDTOID is null) and
dbo.UFN_PROSPECTRESEARCHREQUEST_USERHASSITEACCESS(PRR.ID, @CURRENTAPPUSERID, @SECURITYFEATUREID, @SECURITYFEATURETYPE) = 1
and
(--Apply user-defined site filtering
@SITEFILTERMODE = 0
or
exists (select ID from dbo.PROSPECTRESEARCHREQUESTSITE PRRS
inner join dbo.UFN_SITE_BUILDDATALISTSITEFILTER(@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED) S on S.SITEID = PRRS.SITEID
where PRRS.PROSPECTRESEARCHREQUESTID = PRR.ID)
)
order by PRR.DATEADDED desc
else
begin
declare @IDS as table(ID uniqueidentifier);
insert into @IDS exec USP_ORGANIZATIONHIERARCHY_IDSET_POSITIONHOLDERS @ORGPOSITIONSSELECTIONID, @CURRENTAPPUSERID;
select PRR.ID,
PRR.REQUESTNO,
PRR.DUEDATE,
PRR.STATUS,
PRR.RECORDTYPE,
(select count(ID) from dbo.PROSPECTRESEARCHREQUESTCONSTITUENT where PROSPECTRESEARCHREQUESTID = PRR.ID and PROSPECTRESEARCHREQUESTCONSTITUENT.STATUSCODE <> 6) as NUMBEROFCONSTITUENTS,
PRRPC.DESCRIPTION as [PRIORITYCODE],
PRRTC.DESCRIPTION as [REQUESTTYPECODE],
NF.NAME as [ASSIGNEDTO],
PRR.STATUSCODE,
PRR.ASSIGNEDTOID,
(
select dbo.UDA_BUILDLIST(SITE.NAME)
from dbo.PROSPECTRESEARCHREQUESTSITE
inner join dbo.SITE on SITE.ID = PROSPECTRESEARCHREQUESTSITE.SITEID
where PROSPECTRESEARCHREQUESTID = PRR.ID
) as [SITES]
from
dbo.PROSPECTRESEARCHREQUEST PRR
left join
dbo.PROSPECTRESEARCHREQUESTPRIORITYCODE PRRPC on PRRPC.ID = PRR.PROSPECTRESEARCHREQUESTPRIORITYCODEID
left join
dbo.PROSPECTRESEARCHREQUESTTYPECODE PRRTC on PRRTC.ID = PRR.PROSPECTRESEARCHREQUESTTYPECODEID
left join
dbo.APPUSER AU on PRR.ASSIGNEDTOID = AU.CONSTITUENTID
left join
dbo.PROSPECTRESEARCHREQUESTAPPUSERPERMISSION PRRAUP on PRRAUP.ID = @CURRENTAPPUSERID
inner join
dbo.ORGANIZATIONPOSITIONHOLDER OPH on OPH.CONSTITUENTID = PRR.ASSIGNEDTOID and @CURRENTDATE between OPH.DATEFROM and coalesce(OPH.DATETO, @CURRENTDATE)
inner join
@IDS as SELECTION on OPH.[ID] = SELECTION.[ID]
outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(PRR.ASSIGNEDTOID) NF
where
(@NUMBERTOSHOWCODE = 0 or PRR.DATEADDED between @STARTDATE and @ENDDATE) and
(@DUEDATECODE = 0 or PRR.DUEDATE between @DUEDATESTART and @DUEDATEEND) and
((@STATUSCODE = 99 or PRR.STATUSCODE = @STATUSCODE) and PRR.STATUSCODE not in (4, 6)) and
((PRR.ASSIGNEDTOID = @FUNDRAISERID) or @FUNDRAISERID is null) and
(AU.ID = @CURRENTAPPUSERID or PRRAUP.CANVIEWREQUESTSASSIGNEDTOOTHERS = 1 or PRR.ASSIGNEDTOID is null) and
dbo.UFN_PROSPECTRESEARCHREQUEST_USERHASSITEACCESS(PRR.ID, @CURRENTAPPUSERID, @SECURITYFEATUREID, @SECURITYFEATURETYPE) = 1
and
(--Apply user-defined site filtering
@SITEFILTERMODE = 0
or
exists (select ID from dbo.PROSPECTRESEARCHREQUESTSITE PRRS
inner join dbo.UFN_SITE_BUILDDATALISTSITEFILTER(@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED) S on S.SITEID = PRRS.SITEID
where PRRS.PROSPECTRESEARCHREQUESTID = PRR.ID)
)
order by PRR.DATEADDED desc
end