USP_DATALIST_FUNDRAISERSTEPSPENDING
List of pending steps for a given fundraiser.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @CURRENTAPPUSERID | uniqueidentifier | IN | Input parameter indicating the ID of the current user. |
| @FUNDRAISERID | uniqueidentifier | IN | Input parameter indicating the context ID for the data list. |
| @ONLYOWNEDINTERACTIONS | bit | IN | Only show steps owned by this fundraiser |
| @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. |
| @INCLUDEGENERALINTERACTIONS | bit | IN |
Definition
Copy
CREATE procedure dbo.USP_DATALIST_FUNDRAISERSTEPSPENDING
(
@CURRENTAPPUSERID uniqueidentifier,
@FUNDRAISERID uniqueidentifier,
@ONLYOWNEDINTERACTIONS bit = 0,
@SITEFILTERMODE tinyint = 0,
@SITESSELECTED xml = null,
@SECURITYFEATUREID uniqueidentifier = null,
@SECURITYFEATURETYPE tinyint = null,
@INCLUDEGENERALINTERACTIONS bit = null
) as begin
set nocount on;
declare @TODAY datetime;
set @TODAY = dbo.UFN_DATE_GETEARLIESTTIME(getdate());
declare @temp table(
INTERACTIONID uniqueidentifier,
PROSPECTPLANID uniqueidentifier,
PROSPECT nvarchar(154),
EXPECTEDDATE datetime,
OBJECTIVE nvarchar(100),
PLANTYPE nvarchar(100),
PLANSTAGE nvarchar(100),
TIMEFRAME nvarchar(100),
ISINTERACTION bit,
OWNER nvarchar(154),
CONTACTMETHOD nvarchar(100),
HASDOCUMENTATION bit,
HASADDITIONALFUNDRAISERS bit,
ISSTEWARDSHIPSTEP bit,
STEWARDSHIPFORGROUPORG bit,
SITES nvarchar(1024),
ISFUNDINGREQUESTSTEP bit,
ISGENERALINTERACTION bit,
PROSPECTID uniqueidentifier
);
insert into @temp
(INTERACTIONID, PROSPECTPLANID, PROSPECT, EXPECTEDDATE, OBJECTIVE, PLANTYPE, PLANSTAGE, TIMEFRAME, ISINTERACTION, OWNER, CONTACTMETHOD, HASDOCUMENTATION, HASADDITIONALFUNDRAISERS, ISSTEWARDSHIPSTEP, STEWARDSHIPFORGROUPORG,SITES,ISFUNDINGREQUESTSTEP, ISGENERALINTERACTION, PROSPECTID)
select
I.ID,
PP.ID,
NF_PC.NAME,
I.EXPECTEDDATE,
I.OBJECTIVE,
dbo.UFN_PROSPECTPLANTYPECODE_GETDESCRIPTION(PP.PROSPECTPLANTYPECODEID),
dbo.UFN_PROSPECTPLANSTATUSCODE_GETDESCRIPTION(PP.PROSPECTPLANSTATUSCODEID),
dbo.UFN_DATE_TIMEFRAME(datediff(day, @TODAY, I.EXPECTEDDATE)),
I.ISINTERACTION,
NF_FC.NAME as OWNER,
ITC.DESCRIPTION as CONTACTMETHOD,
case when exists(select 1 from dbo.INTERACTIONNOTE where INTERACTIONNOTE.INTERACTIONID=I.ID)
or exists(select 1 from dbo.INTERACTIONATTACHMENT where INTERACTIONATTACHMENT.INTERACTIONID=I.ID)
or exists(select 1 from dbo.INTERACTIONMEDIALINK where INTERACTIONMEDIALINK.INTERACTIONID=I.ID)
then convert(bit,1) else convert(bit,0) end,
case
when exists (select ID from INTERACTIONADDITIONALFUNDRAISER where INTERACTIONID = I.ID) then convert(bit, 1)
else convert(bit, 0)
end HASADDITIONALFUNDRAISERS,
0,
0,
dbo.UFN_PROSPECTPLAN_GETSITELIST(PP.ID) SITES,
0,
0,
I.CONSTITUENTID
from
dbo.INTERACTION I
inner join dbo.PROSPECTPLAN PP
on PP.ID=I.PROSPECTPLANID
left join dbo.INTERACTIONTYPECODE ITC
on I.INTERACTIONTYPECODEID = ITC.ID
cross apply dbo.UFN_CONSTITUENT_DISPLAYNAME(PP.PROSPECTID) NF_PC
outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(I.FUNDRAISERID) NF_FC
where
I.STATUSCODE=1
and (
I.FUNDRAISERID = @FUNDRAISERID
or (@ONLYOWNEDINTERACTIONS = 0 and exists(select ID from dbo.INTERACTIONADDITIONALFUNDRAISER where INTERACTIONID = I.ID and FUNDRAISERID = @FUNDRAISERID))
)
and (
select count(*)
from dbo.UFN_SITEID_MAPFROM_PROSPECTPLANID(PP.ID) as SECUREDRECORD
where (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[SECUREDRECORD].[SITEID] or (SITEID is null and [SECUREDRECORD].[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
)
);
--AdamBu - 10/21/09 - Funding request steps
insert into @temp
(INTERACTIONID, PROSPECTPLANID, PROSPECT, EXPECTEDDATE, OBJECTIVE, PLANTYPE, PLANSTAGE, TIMEFRAME, ISINTERACTION, OWNER, CONTACTMETHOD, HASDOCUMENTATION, HASADDITIONALFUNDRAISERS, ISSTEWARDSHIPSTEP, STEWARDSHIPFORGROUPORG,SITES,ISFUNDINGREQUESTSTEP, ISGENERALINTERACTION, PROSPECTID)
select
I.ID,
FUNDINGREQUEST.ID,
NF_PC.NAME,
I.EXPECTEDDATE,
I.OBJECTIVE,
'Funding request',
dbo.UFN_FUNDINGREQUESTSTAGECODE_GETDESCRIPTION(FUNDINGREQUEST.FUNDINGREQUESTSTAGECODEID),
dbo.UFN_DATE_TIMEFRAME(datediff(day, @TODAY, I.EXPECTEDDATE)),
I.ISINTERACTION,
NF_FC.NAME as OWNER,
ITC.DESCRIPTION as CONTACTMETHOD,
case when exists(select 1 from dbo.INTERACTIONNOTE where INTERACTIONNOTE.INTERACTIONID=I.ID)
or exists(select 1 from dbo.INTERACTIONATTACHMENT where INTERACTIONATTACHMENT.INTERACTIONID=I.ID)
or exists(select 1 from dbo.INTERACTIONMEDIALINK where INTERACTIONMEDIALINK.INTERACTIONID=I.ID)
then convert(bit,1) else convert(bit,0) end,
case
when exists (select ID from INTERACTIONADDITIONALFUNDRAISER where INTERACTIONID = I.ID) then convert(bit, 1)
else convert(bit, 0)
end HASADDITIONALFUNDRAISERS,
0,
0,
SITE.NAME SITES,
1,
0,
I.CONSTITUENTID
from
dbo.INTERACTION I
inner join dbo.FUNDINGREQUEST
on FUNDINGREQUEST.ID=I.FUNDINGREQUESTID
inner join dbo.FUNDINGPLAN SECUREDRECORD
on SECUREDRECORD.ID = FUNDINGREQUEST.FUNDINGPLANID
inner join dbo.GRANTS
on GRANTS.ID = FUNDINGREQUEST.GRANTSID
left join dbo.INTERACTIONTYPECODE ITC
on I.INTERACTIONTYPECODEID = ITC.ID
left join dbo.SITE
on SITE.ID = SECUREDRECORD.SITEID
cross apply dbo.UFN_CONSTITUENT_DISPLAYNAME(GRANTS.GRANTORID) NF_PC
outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(I.FUNDRAISERID) NF_FC
where
I.STATUSCODE=1
and (
I.FUNDRAISERID = @FUNDRAISERID
or (@ONLYOWNEDINTERACTIONS = 0 and exists(select ID from dbo.INTERACTIONADDITIONALFUNDRAISER where INTERACTIONID = I.ID and FUNDRAISERID = @FUNDRAISERID))
)
and (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[SECUREDRECORD].[SITEID] or (SITEID is null and [SECUREDRECORD].[SITEID] is null)))
and (
@SITEFILTERMODE = 0
or SITE.ID in (
select SITEID from dbo.UFN_SITE_BUILDDATALISTSITEFILTER (@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED)
)
);
insert into @temp
(INTERACTIONID, PROSPECTPLANID, PROSPECT, EXPECTEDDATE, OBJECTIVE, PLANTYPE, PLANSTAGE, TIMEFRAME, ISINTERACTION, OWNER, CONTACTMETHOD, HASDOCUMENTATION, HASADDITIONALFUNDRAISERS, ISSTEWARDSHIPSTEP, STEWARDSHIPFORGROUPORG, SITES,ISFUNDINGREQUESTSTEP, ISGENERALINTERACTION, PROSPECTID)
select
STEP.ID,
STEP.PLANID, -- Prospect Plan ID
PROSPECT_NF.NAME, -- Prospect
STEP.TARGETDATE, -- Expected Date
STEP.OBJECTIVE, -- Objective
'Stewardship', -- Plan Type
null, -- Plan Stage
dbo.UFN_DATE_TIMEFRAME(datediff(day, @TODAY, STEP.TARGETDATE)), -- Time Frame
0, -- Is Interaction
OWNER_NF.NAME, -- Owner
ITC.DESCRIPTION, -- Contact Method
case when exists(select 1 from dbo.STEWARDSHIPNOTE where STEWARDSHIPNOTE.STEPID = STEP.ID)
or exists(select 1 from dbo.STEWARDSHIPATTACHMENT where STEWARDSHIPATTACHMENT.STEPID = STEP.ID)
or exists(select 1 from dbo.STEWARDSHIPMEDIALINK where STEWARDSHIPMEDIALINK.STEPID = STEP.ID)
then convert(bit,1)
else convert(bit,0)
end, -- HASDOCUMENTATION
0, -- HASADDITIONALFUNDRAISERS
1, -- ISSTEWARDSHIPSTEP
case
when PROSPECT.ISGROUP = 1 or PROSPECT.ISORGANIZATION = 1 then 1
else 0
end , -- STEWARDSHIPFORGROUPORG
dbo.UFN_STEWARDSHIPPLAN_GETSITELIST([PLAN].ID) SITES,
0,
0,
PROSPECT.ID
from dbo.STEWARDSHIPPLANSTEP STEP
inner join dbo.STEWARDSHIPPLAN [PLAN]
on STEP.PLANID = [PLAN].ID
inner join dbo.CONSTITUENT PROSPECT
on [PLAN].CONSTITUENTID = PROSPECT.ID
left outer join dbo.INTERACTIONTYPECODE ITC
on ITC.ID = STEP.CONTACTMETHODCODEID
cross apply dbo.UFN_CONSTITUENT_DISPLAYNAME(PROSPECT.ID) PROSPECT_NF
outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(STEP.CONSTITUENTID) OWNER_NF
where
STEP.CONSTITUENTID = @FUNDRAISERID
and STEP.STATUSCODE = 0
and exists(
select STEWARDSHIPPLAN.ID
from dbo.STEWARDSHIPPLAN
where
STEWARDSHIPPLAN.ID = STEP.PLANID and
(
select count(*)
from dbo.UFN_SITEID_MAPFROM_STEWARDSHIPPLANID(STEWARDSHIPPLAN.ID) as SECUREDRECORD
where (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[SECUREDRECORD].[SITEID] or (SITEID is null and [SECUREDRECORD].[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
)
)
)
insert into @temp
(INTERACTIONID, PROSPECTPLANID, PROSPECT, EXPECTEDDATE, OBJECTIVE, PLANTYPE, PLANSTAGE, TIMEFRAME, ISINTERACTION, OWNER, CONTACTMETHOD, HASDOCUMENTATION, HASADDITIONALFUNDRAISERS, ISSTEWARDSHIPSTEP, STEWARDSHIPFORGROUPORG, SITES,ISFUNDINGREQUESTSTEP, ISGENERALINTERACTION, PROSPECTID)
select
I.ID,
I.PROSPECTPLANID,
NF_C.NAME,
I.EXPECTEDDATE,
null,
null,
null,
dbo.UFN_DATE_TIMEFRAME(datediff(day, @TODAY, I.EXPECTEDDATE)),
I.ISINTERACTION,
NF_F.NAME,
ITC.DESCRIPTION as CONTACTMETHOD,
case
when exists(select 1 from dbo.INTERACTIONNOTE where INTERACTIONNOTE.INTERACTIONID=I.ID)
or exists(select 1 from dbo.INTERACTIONATTACHMENT where INTERACTIONATTACHMENT.INTERACTIONID=I.ID)
or exists(select 1 from dbo.INTERACTIONMEDIALINK where INTERACTIONMEDIALINK.INTERACTIONID=I.ID)
then convert(bit,1)
else convert(bit,0)
end,
case when exists (select ID from INTERACTIONADDITIONALFUNDRAISER where INTERACTIONID = I.ID)
then convert(bit, 1)
else convert(bit, 0)
end HASADDITIONALFUNDRAISERS,
0,
0,
dbo.UFN_CONSTITUENTINTERACTION_GETSITELIST(I.ID),
0,
1,
I.CONSTITUENTID
from
dbo.INTERACTION I
left join dbo.INTERACTIONTYPECODE ITC on I.INTERACTIONTYPECODEID = ITC.ID
outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(CONSTITUENTID) NF_C
outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(FUNDRAISERID) NF_F
where
@INCLUDEGENERALINTERACTIONS = 1 and
I.STATUSCODE = 1 and
I.FUNDRAISERID = @FUNDRAISERID and
I.PROSPECTPLANID is null and
exists
(
select top 1 INTERACTIONSITE.SITEID from dbo.UFN_SITEID_MAPFROM_INTERACTIONID(I.ID) INTERACTIONSITE
where (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[INTERACTIONSITE].[SITEID] or (SITEID is null and [INTERACTIONSITE].[SITEID] is null)))
) and
(
@SITEFILTERMODE = 0 or
(I.ID in
(
select INTERACTIONSITE.INTERACTIONID
from dbo.UFN_SITE_BUILDDATALISTSITEFILTER (@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED) SITEFILTER
inner join dbo.INTERACTIONSITE on INTERACTIONSITE.SITEID = SITEFILTER.SITEID
)
)
)
select *
from @temp
order by
EXPECTEDDATE, PROSPECT;
end;