USP_DATALIST_CORRESPONDENCEEMAILOUTPUT
Returns a list of records from the correspondence process in constituent mail form.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @SELECTIONID | uniqueidentifier | IN | SelectionID |
| @MAXROWS | int | IN | Input parameter indicating the maximum number of rows to return. |
| @EXCLUDEMAIL | bit | IN | Exclude mail |
| @EXCLUDEPHONE | bit | IN | Exclude phone |
| @EXCLUDEEMAIL | bit | IN | Exclude email |
| @OWNERID | uniqueidentifier | IN | |
| @STARTDATE | datetime | IN | |
| @EXCLUDEDECEASED | bit | IN | |
| @EXCLUDEINACTIVE | bit | IN | |
| @INCLUSIONS | xml | IN | |
| @EXCLUSIONS | xml | IN | |
| @PARAMETERSETID | uniqueidentifier | IN | Parameter set ID |
| @CURRENTAPPUSERID | uniqueidentifier | IN | Input parameter indicating the ID of the current user. |
| @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_CORRESPONDENCEEMAILOUTPUT
(
@SELECTIONID uniqueidentifier = null,
@MAXROWS int,
@EXCLUDEMAIL bit ,
@EXCLUDEPHONE bit,
@EXCLUDEEMAIL bit,
@OWNERID uniqueidentifier = null,
@STARTDATE datetime = null,
@EXCLUDEDECEASED bit = null,
@EXCLUDEINACTIVE bit = null,
@INCLUSIONS xml = null,
@EXCLUSIONS xml = null,
@PARAMETERSETID uniqueidentifier = null,
--@OVERRIDEREQUIREDEXCLUSIONS bit = 0,
--@REQUIREDEXCLUSIONS xml = null
@CURRENTAPPUSERID uniqueidentifier = null,
@SECURITYFEATUREID uniqueidentifier = null,
@SECURITYFEATURETYPE tinyint = null
)
as
declare @BYPASSSECURITY bit;
declare @BPID uniqueidentifier;
declare @BYPASSSITESECURITY bit;
set @BPID = '52776E74-DF72-451C-82D5-519549DAE34B';
set @BYPASSSECURITY = 0;
set @CURRENTAPPUSERID = @OWNERID;
set @BYPASSSITESECURITY = 0;
if @OWNERID is not null
begin
select @BYPASSSECURITY = dbo.UFN_APPUSER_ISSYSADMIN(@OWNERID);
if @BYPASSSECURITY = 0
begin
select @BYPASSSECURITY = dbo.UFN_SECURITY_APPUSER_GRANTED_BUSINESSPROCESS_IN_NONRACROLE(@OWNERID, @BPID);
select @BYPASSSITESECURITY = dbo.UFN_SECURITY_APPUSER_GRANTED_BUSINESSPROCESS_IN_NONSITEROLE(@OWNERID, @BPID);
end
else
set @BYPASSSITESECURITY = 1;
end
else
begin
set @BYPASSSECURITY = 1;
set @BYPASSSITESECURITY = 1;
end
if @BYPASSSECURITY = 0 or @BYPASSSITESECURITY = 0
begin
if @SELECTIONID is null
begin
set nocount on;
select top (@MAXROWS) STANDARDVIEW.*
from
dbo.UFN_QUERY_CORRESPONDENCEEMAILOUTPUT(@PARAMETERSETID) STANDARDVIEW
inner join
dbo.CONSTITUENT on STANDARDVIEW.CONSTITUENTID = CONSTITUENT.ID
inner join
dbo.UFN_BUSINESSPROCESSCOMMPREF_GETINCLUDEDCONSTITUENT(@STARTDATE, @EXCLUDEDECEASED, @EXCLUDEINACTIVE, @INCLUSIONS, @EXCLUSIONS) AS EXC ON CONSTITUENT.ID=EXC.ID
left join
dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITIDS_FORBUSINESSPROCESS(@OWNERID, @BPID) as CONSTIT_RACS on CONSTITUENT.ID = CONSTIT_RACS.ID
where
((CONSTITUENT.DONOTMAIL = case @EXCLUDEMAIL when 1 then 0 end) or (CONSTITUENT.DONOTMAIL = case @EXCLUDEMAIL when 0 then 1 end or CONSTITUENT.DONOTMAIL = 0))
and ((CONSTITUENT.DONOTPHONE = case @EXCLUDEPHONE when 1 then 0 end) or (CONSTITUENT.DONOTPHONE = case @EXCLUDEPHONE when 0 then 1 end or CONSTITUENT.DONOTPHONE = 0))
and ((CONSTITUENT.DONOTEMAIL = case @EXCLUDEEMAIL when 1 then 0 end) or (CONSTITUENT.DONOTEMAIL = case @EXCLUDEEMAIL when 0 then 1 end or CONSTITUENT.DONOTEMAIL = 0))
and (@BYPASSSECURITY = 1 or CONSTIT_RACS.ID is not null)
and (@BYPASSSITESECURITY = 1 or
exists(
select 1 from dbo.CONSTITUENT C
left join dbo.CONSTITUENTSITE on CONSTITUENTSITE.CONSTITUENTID = C.ID
where
C.ID = CONSTITUENT.ID
and
(dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[CONSTITUENTSITE].[SITEID] or (SITEID is null and [CONSTITUENTSITE].[SITEID] is null)))
)
)
order by
STANDARDVIEW.CONSTITUENTNAME;
end
else
begin
set nocount on;
with MAIL_CTE as (
select top (@MAXROWS)
SELECTION.ID
from
dbo.UFN_IDSETREADER_GETRESULTS_GUID(@SELECTIONID) SELECTION
inner join
dbo.CONSTITUENT on SELECTION.ID = CONSTITUENT.ID
left join
dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITIDS_FORBUSINESSPROCESS(@OWNERID, @BPID) as CONSTIT_RACS on CONSTITUENT.ID = CONSTIT_RACS.ID
inner join
dbo.UFN_BUSINESSPROCESSCOMMPREF_GETINCLUDEDCONSTITUENT(@STARTDATE, @EXCLUDEDECEASED, @EXCLUDEINACTIVE, @INCLUSIONS, @EXCLUSIONS) AS EXC ON CONSTITUENT.ID=EXC.ID
where
((CONSTITUENT.DONOTMAIL = case @EXCLUDEMAIL when 1 then 0 end) or (CONSTITUENT.DONOTMAIL = case @EXCLUDEMAIL when 0 then 1 end or CONSTITUENT.DONOTMAIL = 0))
and ((CONSTITUENT.DONOTPHONE = case @EXCLUDEPHONE when 1 then 0 end) or (CONSTITUENT.DONOTPHONE = case @EXCLUDEPHONE when 0 then 1 end or CONSTITUENT.DONOTPHONE = 0))
and ((CONSTITUENT.DONOTEMAIL = case @EXCLUDEEMAIL when 1 then 0 end) or (CONSTITUENT.DONOTEMAIL = case @EXCLUDEEMAIL when 0 then 1 end or CONSTITUENT.DONOTEMAIL = 0))
and (@BYPASSSECURITY = 1 or CONSTIT_RACS.ID is not null)
and (@BYPASSSITESECURITY = 1 or
exists(
select 1 from dbo.CONSTITUENT C
left join dbo.CONSTITUENTSITE on CONSTITUENTSITE.CONSTITUENTID = C.ID
where
C.ID = CONSTITUENT.ID
and
(dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[CONSTITUENTSITE].[SITEID] or (SITEID is null and [CONSTITUENTSITE].[SITEID] is null)))
)
)
)
select top (@MAXROWS)
STANDARDVIEW.*
from
MAIL_CTE MAIL
inner join
dbo.UFN_QUERY_CORRESPONDENCEEMAILOUTPUT(@PARAMETERSETID) STANDARDVIEW on MAIL.ID = STANDARDVIEW.CONSTITUENTID
order by
STANDARDVIEW.CONSTITUENTNAME;
end
end
else
if @SELECTIONID is null
begin
set nocount on;
select top (@MAXROWS) STANDARDVIEW.*
from
dbo.UFN_QUERY_CORRESPONDENCEEMAILOUTPUT(@PARAMETERSETID) STANDARDVIEW
inner join
dbo.CONSTITUENT on STANDARDVIEW.CONSTITUENTID = CONSTITUENT.ID
inner join
dbo.UFN_BUSINESSPROCESSCOMMPREF_GETINCLUDEDCONSTITUENT(@STARTDATE, @EXCLUDEDECEASED, @EXCLUDEINACTIVE, @INCLUSIONS, @EXCLUSIONS) AS EXC ON CONSTITUENT.ID=EXC.ID
where
((CONSTITUENT.DONOTMAIL = case @EXCLUDEMAIL when 1 then 0 end) or (CONSTITUENT.DONOTMAIL = case @EXCLUDEMAIL when 0 then 1 end or CONSTITUENT.DONOTMAIL = 0))
and ((CONSTITUENT.DONOTPHONE = case @EXCLUDEPHONE when 1 then 0 end) or (CONSTITUENT.DONOTPHONE = case @EXCLUDEPHONE when 0 then 1 end or CONSTITUENT.DONOTPHONE = 0))
and ((CONSTITUENT.DONOTEMAIL = case @EXCLUDEEMAIL when 1 then 0 end) or (CONSTITUENT.DONOTEMAIL = case @EXCLUDEEMAIL when 0 then 1 end or CONSTITUENT.DONOTEMAIL = 0))
order by
STANDARDVIEW.CONSTITUENTNAME;
end
else
begin
set nocount on;
with MAIL_CTE as (
select top (@MAXROWS)
SELECTION.ID
from
dbo.UFN_IDSETREADER_GETRESULTS_GUID(@SELECTIONID) SELECTION
inner join
dbo.CONSTITUENT on SELECTION.ID = CONSTITUENT.ID
inner join
dbo.UFN_BUSINESSPROCESSCOMMPREF_GETINCLUDEDCONSTITUENT(@STARTDATE, @EXCLUDEDECEASED, @EXCLUDEINACTIVE, @INCLUSIONS, @EXCLUSIONS) AS EXC ON CONSTITUENT.ID=EXC.ID
where
((CONSTITUENT.DONOTMAIL = case @EXCLUDEMAIL when 1 then 0 end) or (CONSTITUENT.DONOTMAIL = case @EXCLUDEMAIL when 0 then 1 end or CONSTITUENT.DONOTMAIL = 0))
and ((CONSTITUENT.DONOTPHONE = case @EXCLUDEPHONE when 1 then 0 end) or (CONSTITUENT.DONOTPHONE = case @EXCLUDEPHONE when 0 then 1 end or CONSTITUENT.DONOTPHONE = 0))
and ((CONSTITUENT.DONOTEMAIL = case @EXCLUDEEMAIL when 1 then 0 end) or (CONSTITUENT.DONOTEMAIL = case @EXCLUDEEMAIL when 0 then 1 end or CONSTITUENT.DONOTEMAIL = 0))
)
select top (@MAXROWS)
STANDARDVIEW.*
from
MAIL_CTE MAIL
inner join
dbo.UFN_QUERY_CORRESPONDENCEEMAILOUTPUT(@PARAMETERSETID) STANDARDVIEW on MAIL.ID = STANDARDVIEW.CONSTITUENTID
order by
STANDARDVIEW.CONSTITUENTNAME;
end