USP_DATALIST_CORRESPONDENCEMAILOUTPUT

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
@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_CORRESPONDENCEMAILOUTPUT
                (
                    @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,
                    --@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.V_QUERY_CONSTITUENTMAIL 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.V_QUERY_CONSTITUENTMAIL 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.V_QUERY_CONSTITUENTMAIL 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.V_QUERY_CONSTITUENTMAIL STANDARDVIEW on MAIL.ID = STANDARDVIEW.CONSTITUENTID
                            order by
                                STANDARDVIEW.CONSTITUENTNAME;
                        end