USP_DATALIST_STEPSOVERDUE
List of overdue prospect plan steps.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @CURRENTAPPUSERID | uniqueidentifier | IN | Input parameter indicating the ID of the current user. |
| @ORGPOSITIONSSELECTIONID | uniqueidentifier | IN | Show for |
| @DAYSOVERDUEFILTER | smallint | IN | Days overdue |
| @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. |
| @ONLYOWNEDINTERACTIONS | bit | IN | Only show steps owned by this fundraiser |
| @MAXROWS | int | IN |
Definition
Copy
CREATE procedure dbo.USP_DATALIST_STEPSOVERDUE
(
@CURRENTAPPUSERID uniqueidentifier,
@ORGPOSITIONSSELECTIONID uniqueidentifier = null,
@DAYSOVERDUEFILTER smallint = -1,
@SITEFILTERMODE tinyint = 0,
@SITESSELECTED xml = null,
@SECURITYFEATUREID uniqueidentifier = null,
@SECURITYFEATURETYPE tinyint = null,
@ONLYOWNEDINTERACTIONS bit = 0,
@MAXROWS int = null
)
with execute as owner
as begin
set nocount on;
declare @TODAY datetime = dbo.UFN_DATE_GETEARLIESTTIME(getdate());
declare @temp table
(
INTERACTIONID uniqueidentifier,
PROSPECTPLANID uniqueidentifier,
PROSPECT nvarchar(154),
EXPECTEDDATE datetime,
DAYSOVERDUE integer,
OWNER nvarchar(154),
OBJECTIVE nvarchar(100),
PLANTYPE nvarchar(100),
PLANSTAGE nvarchar(100),
TIMEFRAME nvarchar(100),
ISSTEWARDSHIPSTEP bit,
STEWARDSHIPFORGROUPORG bit,
SITES nvarchar(1024),
FUNDRAISER nvarchar(1024),
ISFUNDINGREQUESTSTEP bit
);
if @ORGPOSITIONSSELECTIONID is null
begin
insert into @temp
select
I.ID,
PP.ID,
NF_PC.NAME,
I.EXPECTEDDATE,
datediff(day,I.EXPECTEDDATE,@TODAY),
NF_FC.NAME,
I.OBJECTIVE,
dbo.UFN_PROSPECTPLANTYPECODE_GETDESCRIPTION(PP.PROSPECTPLANTYPECODEID),
dbo.UFN_PROSPECTPLANSTATUSCODE_GETDESCRIPTION(PP.PROSPECTPLANSTATUSCODEID),
case
when datediff(day,I.EXPECTEDDATE,@TODAY) <= 30 then
'30 days or less'
when datediff(day,I.EXPECTEDDATE,@TODAY) <= 60 then
'Over 30 days'
when datediff(day,I.EXPECTEDDATE,@TODAY) <= 90 then
'Over 60 days'
else
'Over 90 days'
end,
0,
0,
dbo.UFN_PROSPECTPLAN_GETSITELIST(PP.ID) SITES,
(select dbo.UDA_BUILDLIST(NF.NAME) from dbo.INTERACTIONADDITIONALFUNDRAISER outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(INTERACTIONADDITIONALFUNDRAISER.FUNDRAISERID) NF where INTERACTIONADDITIONALFUNDRAISER.INTERACTIONID = I.ID) as FUNDRAISER,
0
from
dbo.INTERACTION I
inner join dbo.PROSPECTPLAN PP on PP.ID = I.PROSPECTPLANID
cross apply dbo.UFN_CONSTITUENT_DISPLAYNAME(PP.PROSPECTID) NF_PC
outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(I.FUNDRAISERID) NF_FC
where
I.COMPLETED=0 and
I.EXPECTEDDATE<@TODAY
and (
select count(*)
from dbo.UFN_SITEID_MAPFROM_PROSPECTPLANID(PP.ID) as SITEDRECORD
where (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[SITEDRECORD].[SITEID] or (SITEID is null and [SITEDRECORD].[SITEID] is null)))
) > 0
and (
@SITEFILTERMODE = 0
or PP.ID in (
select PROSPECTPLANSITE.PROSPECTPLANID
from dbo.UFN_SITE_BUILDDATALISTSITEFILTER (@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED) SITEFILTER
inner join dbo.PROSPECTPLANSITE on PROSPECTPLANSITE.SITEID = SITEFILTER.SITEID
)
)
and (
@DAYSOVERDUEFILTER = 0
or (
@DAYSOVERDUEFILTER = -1
and datediff(day,I.EXPECTEDDATE,@TODAY) <= 30
)
or (
@DAYSOVERDUEFILTER <> -1
and datediff(day,I.EXPECTEDDATE,@TODAY) > @DAYSOVERDUEFILTER
)
)
insert into @temp
select
STEP.ID,
STEP.PLANID, -- Prospect Plan ID
NF_PROSPECT.NAME, -- Prospect
STEP.TARGETDATE, -- Expected Date
datediff(day,STEP.TARGETDATE,@TODAY), -- Days overdue
NF_OWNER.NAME, -- Fundraiser
STEP.OBJECTIVE, -- Objective
'Stewardship', -- Plan Type
null, -- Plan Stage
case
when datediff(day,STEP.TARGETDATE,@TODAY) <= 30 then
'30 days or less'
when datediff(day,STEP.TARGETDATE,@TODAY) <= 60 then
'Over 30 days'
when datediff(day,STEP.TARGETDATE,@TODAY) <= 90 then
'Over 60 days'
else
'Over 90 days'
end, -- Time Frame
1, -- ISSTEWARDSHIPSTEP
case -- STEWARDSHIPFORGROUPORG
when PROSPECT.ISGROUP = 1 or PROSPECT.ISORGANIZATION = 1 then 1
else 0
end,
dbo.UFN_STEWARDSHIPPLANSTEP_GETSITELIST(STEP.ID) SITES,
'' as FUNDRAISER,
0
from
dbo.STEWARDSHIPPLANSTEP STEP
inner join dbo.STEWARDSHIPPLAN on STEWARDSHIPPLAN.ID = STEP.PLANID
inner join dbo.CONSTITUENT PROSPECT on STEWARDSHIPPLAN.CONSTITUENTID = PROSPECT.ID
left outer join dbo.INTERACTIONTYPECODE ITC on ITC.ID = STEP.CONTACTMETHODCODEID
outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(PROSPECT.ID) NF_PROSPECT
outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(STEP.CONSTITUENTID) NF_OWNER
where
STEP.STATUSCODE = 0
and STEP.TARGETDATE < @TODAY
and
(
@DAYSOVERDUEFILTER = 0
or
(
@DAYSOVERDUEFILTER = -1
and datediff(day, STEP.TARGETDATE, @TODAY) <= 30
)
or
(
@DAYSOVERDUEFILTER <> -1
and datediff(day, STEP.TARGETDATE, @TODAY) > @DAYSOVERDUEFILTER
)
)
and
(
select count(*)
from dbo.UFN_SITEID_MAPFROM_STEWARDSHIPPLANID(STEWARDSHIPPLAN.ID) as SITEDRECORD
where (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[SITEDRECORD].[SITEID] or (SITEID is null and [SITEDRECORD].[SITEID] is null)))
) > 0
and
(
@SITEFILTERMODE = 0
or
STEWARDSHIPPLAN.ID in
(
select STEWARDSHIPPLANSITE.STEWARDSHIPPLANID
from dbo.UFN_SITE_BUILDDATALISTSITEFILTER (@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED) SITEFILTER
inner join dbo.STEWARDSHIPPLANSITE on STEWARDSHIPPLANSITE.SITEID = SITEFILTER.SITEID
)
)
--------
--AdamBu - 11/9/09 - If flagged to, include funding request info
--------
if dbo.UFN_GETINCLUDEGRANTS() = 1
begin
insert into @temp
select
I.ID,
FUNDINGREQUEST.ID,
NF_PC.NAME,
I.EXPECTEDDATE,
datediff(day,I.EXPECTEDDATE,@TODAY),
NF_FC.NAME,
I.OBJECTIVE,
'Funding request',
dbo.UFN_FUNDINGREQUESTSTAGECODE_GETDESCRIPTION(FUNDINGREQUEST.FUNDINGREQUESTSTAGECODEID),
case
when datediff(day,I.EXPECTEDDATE,@TODAY) <= 30 then
'30 days or less'
when datediff(day,I.EXPECTEDDATE,@TODAY) <= 60 then
'Over 30 days'
when datediff(day,I.EXPECTEDDATE,@TODAY) <= 90 then
'Over 60 days'
else
'Over 90 days'
end,
0,
0,
SITE.NAME SITES,
(select dbo.UDA_BUILDLIST(NF.NAME) from dbo.INTERACTIONADDITIONALFUNDRAISER outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(INTERACTIONADDITIONALFUNDRAISER.FUNDRAISERID) NF where INTERACTIONADDITIONALFUNDRAISER.INTERACTIONID = I.ID) as FUNDRAISER,
1
from
dbo.INTERACTION I
inner join dbo.FUNDINGREQUEST on FUNDINGREQUEST.ID = I.FUNDINGREQUESTID
inner join dbo.GRANTS on GRANTS.ID = FUNDINGREQUEST.GRANTSID
inner join dbo.FUNDINGPLAN SITEDRECORD on SITEDRECORD.ID = FUNDINGREQUEST.FUNDINGPLANID
left join dbo.SITE on SITE.ID = SITEDRECORD.SITEID
cross apply dbo.UFN_CONSTITUENT_DISPLAYNAME(GRANTS.GRANTORID) NF_PC
outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(I.FUNDRAISERID) NF_FC
where
I.COMPLETED=0 and
I.EXPECTEDDATE<@TODAY
and (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[SITEDRECORD].[SITEID] or (SITEID is null and [SITEDRECORD].[SITEID] is null)))
and (
@SITEFILTERMODE = 0
or SITE.ID in (
select SITEID from dbo.UFN_SITE_BUILDDATALISTSITEFILTER (@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED)
)
)
and (
@DAYSOVERDUEFILTER = 0
or (
@DAYSOVERDUEFILTER = -1
and datediff(day,I.EXPECTEDDATE,@TODAY) <= 30
)
or (
@DAYSOVERDUEFILTER <> -1
and datediff(day,I.EXPECTEDDATE,@TODAY) > @DAYSOVERDUEFILTER
)
)
end
end
else
begin
declare @IDS as table(ID uniqueidentifier);
insert into @IDS exec USP_ORGANIZATIONHIERARCHY_IDSET_POSITIONHOLDERS @ORGPOSITIONSSELECTIONID, @CURRENTAPPUSERID;
insert into @temp
select distinct
DATA.INTERACTIONID,
DATA.PROSPECTPLANID,
DATA.PROSPECTNAME,
DATA.EXPECTEDDATE,
DATA.DAYSOVERDUE,
DATA.FUNDRAISERNAME,
DATA.OBJECTIVE,
DATA.PLANTYPE,
DATA.PLANSTAGE,
DATA.TIMEFRAME,
DATA.ISSTEWARDSHIPSTEP,
DATA.STEWARDSHIPFORGROUPORG,
DATA.SITES,
DATA.FUNDRAISER,
DATA.ISFUNDINGREQUESTSTEP
from
(
select
I.ID as INTERACTIONID,
PP.ID as PROSPECTPLANID,
NF_PC.NAME as PROSPECTNAME,
I.EXPECTEDDATE,
datediff(day,I.EXPECTEDDATE,@TODAY) as DAYSOVERDUE,
NF_FC.NAME as FUNDRAISERNAME,
I.OBJECTIVE,
dbo.UFN_PROSPECTPLANTYPECODE_GETDESCRIPTION(PP.PROSPECTPLANTYPECODEID) as PLANTYPE,
dbo.UFN_PROSPECTPLANSTATUSCODE_GETDESCRIPTION(PP.PROSPECTPLANSTATUSCODEID) as PLANSTAGE,
case
when datediff(day,I.EXPECTEDDATE,@TODAY) <= 30 then
'30 days or less'
when datediff(day,I.EXPECTEDDATE,@TODAY) <= 60 then
'Over 30 days'
when datediff(day,I.EXPECTEDDATE,@TODAY) <= 90 then
'Over 60 days'
else
'Over 90 days'
end as TIMEFRAME,
0 as ISSTEWARDSHIPSTEP,
0 as STEWARDSHIPFORGROUPORG,
dbo.UFN_PROSPECTPLAN_GETSITELIST(PP.ID) SITES,
(select dbo.UDA_BUILDLIST(NF.NAME) from dbo.INTERACTIONADDITIONALFUNDRAISER outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(INTERACTIONADDITIONALFUNDRAISER.FUNDRAISERID) NF where INTERACTIONADDITIONALFUNDRAISER.INTERACTIONID = I.ID) as FUNDRAISER,
0 as ISFUNDINGREQUESTSTEP
from
dbo.INTERACTION I
inner join dbo.ORGANIZATIONPOSITIONHOLDER OPH on OPH.CONSTITUENTID = I.FUNDRAISERID and I.DATE between OPH.DATEFROM and coalesce(OPH.DATETO, I.DATE)
inner join @IDS as SELECTION on OPH.[ID] = SELECTION.[ID]
inner join dbo.PROSPECTPLAN PP on PP.ID = I.PROSPECTPLANID
cross apply dbo.UFN_CONSTITUENT_DISPLAYNAME(PP.PROSPECTID) NF_PC
outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(I.FUNDRAISERID) NF_FC
where
I.COMPLETED=0 and
I.EXPECTEDDATE<@TODAY
and (
select count(*)
from dbo.UFN_SITEID_MAPFROM_PROSPECTPLANID(PP.ID) as SITEDRECORD
where (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[SITEDRECORD].[SITEID] or (SITEID is null and [SITEDRECORD].[SITEID] is null)))
) > 0
and (
@SITEFILTERMODE = 0
or PP.ID in (
select PROSPECTPLANSITE.PROSPECTPLANID
from dbo.UFN_SITE_BUILDDATALISTSITEFILTER (@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED) SITEFILTER
inner join dbo.PROSPECTPLANSITE on PROSPECTPLANSITE.SITEID = SITEFILTER.SITEID
)
)
and (
@DAYSOVERDUEFILTER = 0
or (
@DAYSOVERDUEFILTER = -1
and datediff(day,I.EXPECTEDDATE,@TODAY) <= 30
)
or (
@DAYSOVERDUEFILTER <> -1
and datediff(day,I.EXPECTEDDATE,@TODAY) > @DAYSOVERDUEFILTER
)
)
union all
select
I.ID as INTERACTIONID,
PP.ID as PROSPECTPLANID,
NF_PC.NAME as PROSPECTNAME,
I.EXPECTEDDATE,
datediff(day,I.EXPECTEDDATE,@TODAY) as DAYSOVERDUE,
NF_FC.NAME as FUNDRAISERNAME,
I.OBJECTIVE,
dbo.UFN_PROSPECTPLANTYPECODE_GETDESCRIPTION(PP.PROSPECTPLANTYPECODEID) as PLANTYPE,
dbo.UFN_PROSPECTPLANSTATUSCODE_GETDESCRIPTION(PP.PROSPECTPLANSTATUSCODEID) as PLANSTAGE,
case
when datediff(day,I.EXPECTEDDATE,@TODAY) <= 30 then
'30 days or less'
when datediff(day,I.EXPECTEDDATE,@TODAY) <= 60 then
'Over 30 days'
when datediff(day,I.EXPECTEDDATE,@TODAY) <= 90 then
'Over 60 days'
else
'Over 90 days'
end as TIMEFRAME,
0 as ISSTEWARDSHIPSTEP,
0 as STEWARDSHIPFORGROUPORG,
dbo.UFN_PROSPECTPLAN_GETSITELIST(PP.ID) SITES,
(select dbo.UDA_BUILDLIST(NF.NAME) from dbo.INTERACTIONADDITIONALFUNDRAISER outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(INTERACTIONADDITIONALFUNDRAISER.FUNDRAISERID) NF where INTERACTIONADDITIONALFUNDRAISER.INTERACTIONID = I.ID) as FUNDRAISER,
0 as ISFUNDINGREQUESTSTEP
from
dbo.INTERACTION I
inner join dbo.INTERACTIONADDITIONALFUNDRAISER on I.ID = INTERACTIONADDITIONALFUNDRAISER.INTERACTIONID
inner join dbo.ORGANIZATIONPOSITIONHOLDER OPH on OPH.CONSTITUENTID = INTERACTIONADDITIONALFUNDRAISER.FUNDRAISERID and I.DATE between OPH.DATEFROM and coalesce(OPH.DATETO, I.DATE)
inner join @IDS as SELECTION on OPH.[ID] = SELECTION.[ID]
inner join dbo.PROSPECTPLAN PP on PP.ID = I.PROSPECTPLANID
cross apply dbo.UFN_CONSTITUENT_DISPLAYNAME(PP.PROSPECTID) NF_PC
outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(I.FUNDRAISERID) NF_FC
where
@ONLYOWNEDINTERACTIONS = 0 and
I.COMPLETED=0 and
I.EXPECTEDDATE<@TODAY
and (
select count(*)
from dbo.UFN_SITEID_MAPFROM_PROSPECTPLANID(PP.ID) as SITEDRECORD
where (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[SITEDRECORD].[SITEID] or (SITEID is null and [SITEDRECORD].[SITEID] is null)))
) > 0
and (
@SITEFILTERMODE = 0
or PP.ID in (
select PROSPECTPLANSITE.PROSPECTPLANID
from dbo.UFN_SITE_BUILDDATALISTSITEFILTER (@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED) SITEFILTER
inner join dbo.PROSPECTPLANSITE on PROSPECTPLANSITE.SITEID = SITEFILTER.SITEID
)
)
and (
@DAYSOVERDUEFILTER = 0
or (
@DAYSOVERDUEFILTER = -1
and datediff(day,I.EXPECTEDDATE,@TODAY) <= 30
)
or (
@DAYSOVERDUEFILTER <> -1
and datediff(day,I.EXPECTEDDATE,@TODAY) > @DAYSOVERDUEFILTER
)
)
) as DATA
insert into @temp
select
STEP.ID,
STEP.PLANID, -- Prospect Plan ID
NF_PROSPECT.NAME, -- Prospect
STEP.TARGETDATE, -- Expected Date
datediff(day,STEP.TARGETDATE,@TODAY), -- Days overdue
NF_OWNER.NAME, -- Fundraiser
STEP.OBJECTIVE, -- Objective
'Stewardship', -- Plan Type
null, -- Plan Stage
case
when datediff(day,STEP.TARGETDATE,@TODAY) <= 30 then
'30 days or less'
when datediff(day,STEP.TARGETDATE,@TODAY) <= 60 then
'Over 30 days'
when datediff(day,STEP.TARGETDATE,@TODAY) <= 90 then
'Over 60 days'
else
'Over 90 days'
end, -- Time Frame
1, -- ISSTEWARDSHIPSTEP
case -- STEWARDSHIPFORGROUPORG
when PROSPECT.ISGROUP = 1 or PROSPECT.ISORGANIZATION = 1 then 1
else 0
end,
dbo.UFN_STEWARDSHIPPLANSTEP_GETSITELIST(STEP.ID) SITES,
'' as FUNDRAISER,
0
from
dbo.STEWARDSHIPPLANSTEP STEP
inner join dbo.ORGANIZATIONPOSITIONHOLDER OPH on OPH.CONSTITUENTID = STEP.CONSTITUENTID and STEP.TARGETDATE between OPH.DATEFROM and coalesce(OPH.DATETO, STEP.TARGETDATE)
inner join @IDS as SELECTION on OPH.[ID] = SELECTION.[ID]
inner join dbo.STEWARDSHIPPLAN on STEWARDSHIPPLAN.ID = STEP.PLANID
inner join dbo.CONSTITUENT PROSPECT on STEWARDSHIPPLAN.CONSTITUENTID = PROSPECT.ID
left outer join dbo.INTERACTIONTYPECODE ITC on ITC.ID = STEP.CONTACTMETHODCODEID
outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(PROSPECT.ID) NF_PROSPECT
outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(STEP.CONSTITUENTID) NF_OWNER
where
STEP.STATUSCODE = 0
and STEP.TARGETDATE < @TODAY
and
(
@DAYSOVERDUEFILTER = 0
or
(
@DAYSOVERDUEFILTER = -1
and datediff(day,STEP.TARGETDATE,@TODAY) <= 30
)
or
(
@DAYSOVERDUEFILTER <> -1
and datediff(day,STEP.TARGETDATE,@TODAY) > @DAYSOVERDUEFILTER
)
)
and
(
select count(*)
from dbo.UFN_SITEID_MAPFROM_STEWARDSHIPPLANID(STEWARDSHIPPLAN.ID) as SITEDRECORD
where (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[SITEDRECORD].[SITEID] or (SITEID is null and [SITEDRECORD].[SITEID] is null)))
) > 0
and
(
@SITEFILTERMODE = 0
or
STEWARDSHIPPLAN.ID in
(
select STEWARDSHIPPLANSITE.STEWARDSHIPPLANID
from dbo.UFN_SITE_BUILDDATALISTSITEFILTER (@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED) SITEFILTER
inner join dbo.STEWARDSHIPPLANSITE on STEWARDSHIPPLANSITE.SITEID = SITEFILTER.SITEID
)
)
--------
--AdamBu - 11/9/09 - If flagged to, include funding request info
--------
if dbo.UFN_GETINCLUDEGRANTS() = 1
begin
insert into @temp
select
I.ID,
FUNDINGREQUEST.ID,
NF_PC.NAME,
I.EXPECTEDDATE,
datediff(day,I.EXPECTEDDATE,@TODAY),
NF_FC.NAME,
I.OBJECTIVE,
'Funding request',
dbo.UFN_FUNDINGREQUESTSTAGECODE_GETDESCRIPTION(FUNDINGREQUEST.FUNDINGREQUESTSTAGECODEID),
case
when datediff(day,I.EXPECTEDDATE,@TODAY) <= 30 then
'30 days or less'
when datediff(day,I.EXPECTEDDATE,@TODAY) <= 60 then
'Over 30 days'
when datediff(day,I.EXPECTEDDATE,@TODAY) <= 90 then
'Over 60 days'
else
'Over 90 days'
end,
0,
0,
SITE.NAME SITES,
(select dbo.UDA_BUILDLIST(NF.NAME) from dbo.INTERACTIONADDITIONALFUNDRAISER outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(INTERACTIONADDITIONALFUNDRAISER.FUNDRAISERID) NF where INTERACTIONADDITIONALFUNDRAISER.INTERACTIONID = I.ID) as FUNDRAISER,
1
from
dbo.INTERACTION I
inner join dbo.ORGANIZATIONPOSITIONHOLDER OPH on OPH.CONSTITUENTID = I.FUNDRAISERID and I.DATE between OPH.DATEFROM and coalesce(OPH.DATETO, I.DATE)
inner join @IDS as SELECTION on OPH.[ID] = SELECTION.[ID]
inner join dbo.FUNDINGREQUEST on FUNDINGREQUEST.ID = I.FUNDINGREQUESTID
inner join dbo.GRANTS on GRANTS.ID = FUNDINGREQUEST.GRANTSID
inner join dbo.FUNDINGPLAN SITEDRECORD on SITEDRECORD.ID = FUNDINGREQUEST.FUNDINGPLANID
left join dbo.SITE on SITE.ID = SITEDRECORD.SITEID
cross apply dbo.UFN_CONSTITUENT_DISPLAYNAME(GRANTS.GRANTORID) NF_PC
outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(I.FUNDRAISERID) NF_FC
where
I.COMPLETED=0 and
I.EXPECTEDDATE<@TODAY
and (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[SITEDRECORD].[SITEID] or (SITEID is null and [SITEDRECORD].[SITEID] is null)))
and (
@SITEFILTERMODE = 0
or SITE.ID in (
select SITEID from dbo.UFN_SITE_BUILDDATALISTSITEFILTER (@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED)
)
)
and (
@DAYSOVERDUEFILTER = 0
or (
@DAYSOVERDUEFILTER = -1
and datediff(day,I.EXPECTEDDATE,@TODAY) <= 30
)
or (
@DAYSOVERDUEFILTER <> -1
and datediff(day,I.EXPECTEDDATE,@TODAY) > @DAYSOVERDUEFILTER
)
)
union all
select
I.ID,
FUNDINGREQUEST.ID,
NF_PC.NAME,
I.EXPECTEDDATE,
datediff(day,I.EXPECTEDDATE,@TODAY),
NF_FC.NAME,
I.OBJECTIVE,
'Funding request',
dbo.UFN_FUNDINGREQUESTSTAGECODE_GETDESCRIPTION(FUNDINGREQUEST.FUNDINGREQUESTSTAGECODEID),
case
when datediff(day,I.EXPECTEDDATE,@TODAY) <= 30 then
'30 days or less'
when datediff(day,I.EXPECTEDDATE,@TODAY) <= 60 then
'Over 30 days'
when datediff(day,I.EXPECTEDDATE,@TODAY) <= 90 then
'Over 60 days'
else
'Over 90 days'
end,
0,
0,
SITE.NAME SITES,
(select dbo.UDA_BUILDLIST(NF.NAME) from dbo.INTERACTIONADDITIONALFUNDRAISER outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(INTERACTIONADDITIONALFUNDRAISER.FUNDRAISERID) NF where INTERACTIONADDITIONALFUNDRAISER.INTERACTIONID = I.ID) as FUNDRAISER,
1
from
dbo.INTERACTION I
inner join dbo.INTERACTIONADDITIONALFUNDRAISER on I.ID = INTERACTIONADDITIONALFUNDRAISER.INTERACTIONID
inner join dbo.ORGANIZATIONPOSITIONHOLDER OPH on OPH.CONSTITUENTID = INTERACTIONADDITIONALFUNDRAISER.FUNDRAISERID and I.DATE between OPH.DATEFROM and coalesce(OPH.DATETO, I.DATE)
inner join @IDS as SELECTION on OPH.[ID] = SELECTION.[ID]
inner join dbo.FUNDINGREQUEST on FUNDINGREQUEST.ID = I.FUNDINGREQUESTID
inner join dbo.GRANTS on GRANTS.ID = FUNDINGREQUEST.GRANTSID
inner join dbo.FUNDINGPLAN SITEDRECORD on SITEDRECORD.ID = FUNDINGREQUEST.FUNDINGPLANID
left join dbo.SITE on SITE.ID = SITEDRECORD.SITEID
cross apply dbo.UFN_CONSTITUENT_DISPLAYNAME(GRANTS.GRANTORID) NF_PC
outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(I.FUNDRAISERID) NF_FC
where
@ONLYOWNEDINTERACTIONS = 0 and
I.COMPLETED=0 and
I.EXPECTEDDATE<@TODAY
and (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[SITEDRECORD].[SITEID] or (SITEID is null and [SITEDRECORD].[SITEID] is null)))
and (
@SITEFILTERMODE = 0
or SITE.ID in (
select SITEID from dbo.UFN_SITE_BUILDDATALISTSITEFILTER (@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED)
)
)
and (
@DAYSOVERDUEFILTER = 0
or (
@DAYSOVERDUEFILTER = -1
and datediff(day,I.EXPECTEDDATE,@TODAY) <= 30
)
or (
@DAYSOVERDUEFILTER <> -1
and datediff(day,I.EXPECTEDDATE,@TODAY) > @DAYSOVERDUEFILTER
)
)
end
end
if @MAXROWS is not null
set rowcount @MAXROWS;
select
INTERACTIONID,
PROSPECTPLANID,
EXPECTEDDATE,
DAYSOVERDUE,
PROSPECT,
OWNER,
OBJECTIVE,
PLANTYPE,
PLANSTAGE,
TIMEFRAME,
ISSTEWARDSHIPSTEP,
STEWARDSHIPFORGROUPORG,
SITES,
FUNDRAISER,
ISFUNDINGREQUESTSTEP
from @temp
order by EXPECTEDDATE, PROSPECT
set rowcount 0;
end