USP_DATALIST_STEPSCANCELLEDDECLINED
List of cancelled or declined 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 |
| @TIMEPERIOD | tinyint | IN | Time period |
| @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_STEPSCANCELLEDDECLINED
(
@CURRENTAPPUSERID uniqueidentifier,
@ORGPOSITIONSSELECTIONID uniqueidentifier = null,
@TIMEPERIOD tinyint = 0,
@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;
set @TODAY = dbo.UFN_DATE_GETEARLIESTTIME(getdate());
if @ORGPOSITIONSSELECTIONID is null
begin
if @MAXROWS is not null
set ROWCOUNT @MAXROWS
select
I.ID,
I.STATUS,
I.STATUSCODE,
I.DATE,
NF_PC.NAME,
NF_FC.NAME,
(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,
I.OBJECTIVE,
dbo.UFN_PROSPECTPLANTYPECODE_GETDESCRIPTION(PP.PROSPECTPLANTYPECODEID),
dbo.UFN_PROSPECTPLANSTATUSCODE_GETDESCRIPTION(PP.PROSPECTPLANSTATUSCODEID),
I.ISINTERACTION,
I.DATEADDED,
dbo.UFN_PROSPECTPLAN_GETSITELIST(PP.ID) SITES,
null as ACTUALDATE,
0 as ISSTEWARDSHIPSTEP,
0 as ISINDIVIDUALSTEP
from dbo.INTERACTION I
inner join dbo.PROSPECTPLAN PP on PP.ID=I.PROSPECTPLANID
outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(I.CONSTITUENTID) NF_PC
outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(I.FUNDRAISERID) NF_FC
where
I.STATUSCODE in (4,5)
and (
@TIMEPERIOD = 0
or (
@TIMEPERIOD <> 0
and datediff(day,I.DATE,@TODAY) < @TIMEPERIOD
)
)
and (
select count(*)
from dbo.UFN_SITEID_MAPFROM_PROSPECTPLANID(PP.ID) as PROSPECTPLANSITE
where (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[PROSPECTPLANSITE].[SITEID] or (SITEID is null and [PROSPECTPLANSITE].[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
)
)
union all
select
I.ID as INTERACTIONID,
I.STATUS,
I.STATUSCODE,
I.DATE,
NF_PC.NAME as PROSPECTNAME,
NF_FC.NAME as OWNER,
(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,
I.OBJECTIVE,
dbo.UFN_STEWARDSHIPPLANTYPECODE_GETDESCRIPTION(SP.PLANTYPECODEID) as PLANTYPE,
dbo.UFN_STEWARDSHIPPLANSUBTYPECODE_GETDESCRIPTION(SP.PLANSUBTYPECODEID) as PLANSTAGE,
I.ISINTERACTION,
I.DATEADDED,
dbo.UFN_STEWARDSHIPPLAN_GETSITELIST(SP.ID) SITES,
null as ACTUALDATE,
1 as ISSTEWARDSHIPSTEP,
case
when CONSTITUENT.ISGROUP = 1 then 0
when CONSTITUENT.ISORGANIZATION = 1 then 0
else 1
end as ISINDIVIDUALSTEP
from
dbo.STEWARDSHIPPLANSTEP I
inner join dbo.STEWARDSHIPPLAN SP on SP.ID=I.PLANID
inner join dbo.CONSTITUENT on CONSTITUENT.ID = SP.CONSTITUENTID
outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(SP.CONSTITUENTID) NF_PC
outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(I.CONSTITUENTID) NF_FC
where
I.STATUSCODE in (2,3)
and (
@TIMEPERIOD = 0
or (
@TIMEPERIOD <> 0
and datediff(day,I.DATE,@TODAY) < @TIMEPERIOD
)
)
and (
select count(*)
from dbo.UFN_SITEID_MAPFROM_STEWARDSHIPPLANID(SP.ID) as PROSPECTPLANSITE
where (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[PROSPECTPLANSITE].[SITEID] or (SITEID is null and [PROSPECTPLANSITE].[SITEID] is null)))
) > 0
and (
@SITEFILTERMODE = 0
or SP.ID in (
select STEWARDSHIPPLANSITE.STEWARDSHIPPLANID
from dbo.UFN_SITE_BUILDDATALISTSITEFILTER (@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED) SITEFILTER
inner join dbo.STEWARDSHIPPLANSITE on STEWARDSHIPPLANSITE.SITEID = SITEFILTER.SITEID
)
)
order by
I.DATEADDED, I.ID
set rowcount 0;
end
else
begin
declare @IDS as table(ID uniqueidentifier);
insert into @IDS exec USP_ORGANIZATIONHIERARCHY_IDSET_POSITIONHOLDERS @ORGPOSITIONSSELECTIONID, @CURRENTAPPUSERID;
if @MAXROWS is not null
set ROWCOUNT @MAXROWS
select distinct
DATA.INTERACTIONID,
DATA.STATUS,
DATA.STATUSCODE,
DATA.DATE,
DATA.PROSPECTNAME,
DATA.OWNER,
DATA.FUNDRAISER,
DATA.OBJECTIVE,
DATA.PLANTYPE,
DATA.PLANSTAGE,
DATA.ISINTERACTION,
DATA.DATEADDED,
DATA.SITES,
DATA.ACTUALDATE,
DATA.ISSTEWARDSHIPSTEP,
DATA.ISINDIVIDUALSTEP
from
(select
I.ID as INTERACTIONID,
I.STATUS,
I.STATUSCODE,
I.DATE,
NF_PC.NAME as PROSPECTNAME,
NF_FC.NAME as OWNER,
(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,
I.OBJECTIVE,
dbo.UFN_PROSPECTPLANTYPECODE_GETDESCRIPTION(PP.PROSPECTPLANTYPECODEID) as PLANTYPE,
dbo.UFN_PROSPECTPLANSTATUSCODE_GETDESCRIPTION(PP.PROSPECTPLANSTATUSCODEID) as PLANSTAGE,
I.ISINTERACTION,
I.DATEADDED,
dbo.UFN_PROSPECTPLAN_GETSITELIST(PP.ID) SITES,
I.ACTUALDATE,
0 as ISSTEWARDSHIPSTEP,
0 as ISINDIVIDUALSTEP
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
outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(I.CONSTITUENTID) NF_PC
outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(I.FUNDRAISERID) NF_FC
where
I.STATUSCODE in (4,5)
and (
@TIMEPERIOD = 0
or (
@TIMEPERIOD <> 0
and datediff(day,I.DATE,@TODAY) < @TIMEPERIOD
)
)
and (
select count(*)
from dbo.UFN_SITEID_MAPFROM_PROSPECTPLANID(PP.ID) as PROSPECTPLANSITE
where (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[PROSPECTPLANSITE].[SITEID] or (SITEID is null and [PROSPECTPLANSITE].[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
)
)
union all
select
I.ID as INTERACTIONID,
I.STATUS,
I.STATUSCODE,
I.DATE,
NF_PC.NAME as PROSPECTNAME,
NF_FC.NAME as OWNER,
(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,
I.OBJECTIVE,
dbo.UFN_PROSPECTPLANTYPECODE_GETDESCRIPTION(PP.PROSPECTPLANTYPECODEID) as PLANTYPE,
dbo.UFN_PROSPECTPLANSTATUSCODE_GETDESCRIPTION(PP.PROSPECTPLANSTATUSCODEID) as PLANSTAGE,
I.ISINTERACTION,
I.DATEADDED,
dbo.UFN_PROSPECTPLAN_GETSITELIST(PP.ID) SITES,
I.ACTUALDATE,
0 as ISSTEWARDSHIPSTEP,
0 as ISINDIVIDUALSTEP
from
dbo.INTERACTION I
inner join dbo.INTERACTIONADDITIONALFUNDRAISER IAF on I.ID = IAF.INTERACTIONID
inner join dbo.ORGANIZATIONPOSITIONHOLDER OPH on OPH.CONSTITUENTID = IAF.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
outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(I.CONSTITUENTID) NF_PC
outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(I.FUNDRAISERID) NF_FC
where
@ONLYOWNEDINTERACTIONS = 0
and I.STATUSCODE in (4,5)
and (
@TIMEPERIOD = 0
or (
@TIMEPERIOD <> 0
and datediff(day,I.DATE,@TODAY) < @TIMEPERIOD
)
)
and (
select count(*)
from dbo.UFN_SITEID_MAPFROM_PROSPECTPLANID(PP.ID) as PROSPECTPLANSITE
where (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[PROSPECTPLANSITE].[SITEID] or (SITEID is null and [PROSPECTPLANSITE].[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
)
)
union all
select
I.ID as INTERACTIONID,
I.STATUS,
I.STATUSCODE,
I.DATE,
NF_PC.NAME as PROSPECTNAME,
NF_FC.NAME as OWNER,
(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,
I.OBJECTIVE,
dbo.UFN_STEWARDSHIPPLANTYPECODE_GETDESCRIPTION(SP.PLANTYPECODEID) as PLANTYPE,
dbo.UFN_STEWARDSHIPPLANSUBTYPECODE_GETDESCRIPTION(SP.PLANSUBTYPECODEID) as PLANSTAGE,
I.ISINTERACTION,
I.DATEADDED,
dbo.UFN_STEWARDSHIPPLAN_GETSITELIST(SP.ID) SITES,
I.ACTUALDATE,
1 as ISSTEWARDSHIPSTEP,
case
when CONSTITUENT.ISGROUP = 1 then 0
when CONSTITUENT.ISORGANIZATION = 1 then 0
else 1
end as ISINDIVIDUALSTEP
from
dbo.STEWARDSHIPPLANSTEP I
inner join dbo.ORGANIZATIONPOSITIONHOLDER OPH on OPH.CONSTITUENTID = I.CONSTITUENTID 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.STEWARDSHIPPLAN SP on SP.ID=I.PLANID
inner join dbo.CONSTITUENT on CONSTITUENT.ID = SP.CONSTITUENTID
outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(SP.CONSTITUENTID) NF_PC
outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(I.CONSTITUENTID) NF_FC
where
I.STATUSCODE in (2,3)
and (
@TIMEPERIOD = 0
or (
@TIMEPERIOD <> 0
and datediff(day,I.DATE,@TODAY) < @TIMEPERIOD
)
)
and (
select count(*)
from dbo.UFN_SITEID_MAPFROM_STEWARDSHIPPLANID(SP.ID) as PROSPECTPLANSITE
where (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[PROSPECTPLANSITE].[SITEID] or (SITEID is null and [PROSPECTPLANSITE].[SITEID] is null)))
) > 0
and (
@SITEFILTERMODE = 0
or SP.ID in (
select STEWARDSHIPPLANSITE.STEWARDSHIPPLANID
from dbo.UFN_SITE_BUILDDATALISTSITEFILTER (@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED) SITEFILTER
inner join dbo.STEWARDSHIPPLANSITE on STEWARDSHIPPLANSITE.SITEID = SITEFILTER.SITEID
)
)
) as DATA
order by
DATA.ACTUALDATE, DATA.DATEADDED, DATA.INTERACTIONID
set rowcount 0;
end
end