USP_DATALIST_RECEIPTINGPROCESS_EMAILGIFTDETAILOUTPUT

Returns a list of records from the receipting process in email gift detail output form.
Parameters

Parameter	Parameter Type	Mode	Description
@SELECTIONID	uniqueidentifier	IN	SelectionID
@MAXROWS	int	IN	Input parameter indicating the maximum number of rows to return.
@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_RECEIPTINGPROCESS_EMAILGIFTDETAILOUTPUT
                (
                    @SELECTIONID uniqueidentifier = null,
                    @MAXROWS int,
                    @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;
                    set @BPID = '83048DED-208E-45c9-852C-E7D5C7317882';
                    set @BYPASSSECURITY = 0;
                    set @CURRENTAPPUSERID = @OWNERID;

                    if @OWNERID is not null
                    begin
                        select @BYPASSSECURITY = dbo.UFN_APPUSER_ISSYSADMIN(@OWNERID);

                        if @BYPASSSECURITY = 0 
                            select @BYPASSSECURITY = dbo.UFN_SECURITY_APPUSER_GRANTED_BUSINESSPROCESS_IN_NONRACROLE(@OWNERID, @BPID);
                    end
                    else
                        set @BYPASSSECURITY = 1;

                    if @BYPASSSECURITY = 0
                    begin
                        if @SELECTIONID is null 
                        begin
                            set nocount on;

                            with CONSTIT_CTE as (
                                select top (@MAXROWS)
                                    REVENUE.CONSTITUENTID
                                from 
                                    dbo.REVENUE with (nolock)
                                inner join
                                    dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITIDS_FORBUSINESSPROCESS(@OWNERID, @BPID) as CONSTIT_RACS on REVENUE.CONSTITUENTID = CONSTIT_RACS.ID                                        
                                inner join 
                                    dbo.UFN_BUSINESSPROCESSCOMMPREF_GETINCLUDEDCONSTITUENT(@STARTDATE, @EXCLUDEDECEASED, @EXCLUDEINACTIVE, @INCLUSIONS, @EXCLUSIONS) AS EXC ON REVENUE.CONSTITUENTID = EXC.ID
                                where
                                    REVENUE.DONOTRECEIPT = 0
                                    and REVENUE.TRANSACTIONTYPECODE in (0,4,7)
                                    and not exists(select RR.ID from REVENUERECEIPT RR where RR.REVENUEID = REVENUE.ID and REVENUE.NEEDSRERECEIPT = 0)
                                group by 
                                    REVENUE.CONSTITUENTID
                            )

                            select top (@MAXROWS)
                                STANDARDVIEW.*
                            from 
                                CONSTIT_CTE CTE 
                            inner join
                                dbo.V_QUERY_RECEIPTING_EMAILGIFTDETAILOUTPUT STANDARDVIEW with (nolock) on CTE.CONSTITUENTID = STANDARDVIEW.CONSTITUENTID
                            where exists
                            (
                                select top 1 RSSUB.ID from dbo.REVENUESPLIT RSSUB
                                cross apply dbo.UFN_SITEID_MAPFROM_REVENUESPLITID(RSSUB.ID) REVSITES
                                where RSSUB.REVENUEID = STANDARDVIEW.REVENUEID
                                and (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[REVSITES].[SITEID] or (SITEID is null and [REVSITES].[SITEID] is null)))
                            )
                            order by    
                                STANDARDVIEW.CONSTITUENTNAME;
                        end
                        else
                        begin
                            set nocount on;

                            with CONSTIT_CTE as (
                                select top (@MAXROWS)
                                    REVENUE.CONSTITUENTID
                                from 
                                    dbo.REVENUE 
                                inner join
                                    dbo.UFN_IDSETREADER_GETRESULTS_GUID(@SELECTIONID) SELECTION on REVENUE.ID = SELECTION.ID
                                inner join 
                                    dbo.UFN_BUSINESSPROCESSCOMMPREF_GETINCLUDEDCONSTITUENT(@STARTDATE, @EXCLUDEDECEASED, @EXCLUDEINACTIVE, @INCLUSIONS, @EXCLUSIONS) AS EXC ON REVENUE.CONSTITUENTID = EXC.ID
                                inner join
                                    dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITIDS_FORBUSINESSPROCESS(@OWNERID, @BPID) as CONSTIT_RACS on REVENUE.CONSTITUENTID = CONSTIT_RACS.ID
                                where
                                    REVENUE.DONOTRECEIPT = 0
                                    and REVENUE.TRANSACTIONTYPECODE in (0,4,7)
                                    and not exists(select RR.ID from REVENUERECEIPT RR where RR.REVENUEID = REVENUE.ID and REVENUE.NEEDSRERECEIPT = 0)
                                group by 
                                    REVENUE.CONSTITUENTID
                            )

                            select top (@MAXROWS)
                                STANDARDVIEW.*
                            from 
                                CONSTIT_CTE CTE 
                            inner join
                                dbo.V_QUERY_RECEIPTING_EMAILGIFTDETAILOUTPUT STANDARDVIEW with (nolock) on CTE.CONSTITUENTID = STANDARDVIEW.CONSTITUENTID
                            where exists
                            (
                                select top 1 RSSUB.ID from dbo.REVENUESPLIT RSSUB
                                cross apply dbo.UFN_SITEID_MAPFROM_REVENUESPLITID(RSSUB.ID) REVSITES
                                where RSSUB.REVENUEID = STANDARDVIEW.REVENUEID
                                and (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[REVSITES].[SITEID] or (SITEID is null and [REVSITES].[SITEID] is null)))
                            )
                            order by    
                                STANDARDVIEW.CONSTITUENTNAME;
                        end
                    end
                    else
                        begin
                            if @SELECTIONID is null 
                            begin
                                set nocount on;

                                with CONSTIT_CTE as (
                                    select top (@MAXROWS)
                                        REVENUE.CONSTITUENTID
                                    from 
                                        dbo.REVENUE with (nolock)
                                    inner join 
                                        dbo.UFN_BUSINESSPROCESSCOMMPREF_GETINCLUDEDCONSTITUENT(@STARTDATE, @EXCLUDEDECEASED, @EXCLUDEINACTIVE, @INCLUSIONS, @EXCLUSIONS) AS EXC ON REVENUE.CONSTITUENTID = EXC.ID
                                    where
                                        REVENUE.DONOTRECEIPT = 0
                                        and REVENUE.TRANSACTIONTYPECODE in (0,4,7)
                                        and not exists(select RR.ID from REVENUERECEIPT RR where RR.REVENUEID = REVENUE.ID and REVENUE.NEEDSRERECEIPT = 0)
                                    group by 
                                        REVENUE.CONSTITUENTID
                                )

                                select distinct top (@MAXROWS)
                                    STANDARDVIEW.*
                                from 
                                    CONSTIT_CTE CTE 
                                inner join
                                    dbo.V_QUERY_RECEIPTING_EMAILGIFTDETAILOUTPUT STANDARDVIEW with (nolock) on CTE.CONSTITUENTID = STANDARDVIEW.CONSTITUENTID
                                where exists
                                (
                                    select top 1 RSSUB.ID from dbo.REVENUESPLIT RSSUB
                                    cross apply dbo.UFN_SITEID_MAPFROM_REVENUESPLITID(RSSUB.ID) REVSITES
                                    where RSSUB.REVENUEID = STANDARDVIEW.REVENUEID
                                    and (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[REVSITES].[SITEID] or (SITEID is null and [REVSITES].[SITEID] is null)))
                                )
                                order by    
                                    STANDARDVIEW.CONSTITUENTNAME;
                            end
                            else
                            begin
                                set nocount on;

                                with CONSTIT_CTE as (
                                    select top (@MAXROWS)
                                        REVENUE.CONSTITUENTID
                                    from 
                                        dbo.REVENUE with (nolock)
                                    inner join
                                        dbo.UFN_IDSETREADER_GETRESULTS_GUID(@SELECTIONID) SELECTION on REVENUE.ID = SELECTION.ID
                                    inner join 
                                        dbo.UFN_BUSINESSPROCESSCOMMPREF_GETINCLUDEDCONSTITUENT(@STARTDATE, @EXCLUDEDECEASED, @EXCLUDEINACTIVE, @INCLUSIONS, @EXCLUSIONS) AS EXC ON REVENUE.CONSTITUENTID = EXC.ID
                                    where
                                        REVENUE.DONOTRECEIPT = 0
                                        and REVENUE.TRANSACTIONTYPECODE in (0,4,7)
                                        and not exists(select RR.ID from REVENUERECEIPT RR where RR.REVENUEID = REVENUE.ID and REVENUE.NEEDSRERECEIPT = 0)
                                    group by 
                                        REVENUE.CONSTITUENTID
                                )

                                select top (@MAXROWS)
                                    STANDARDVIEW.*
                                from 
                                    CONSTIT_CTE CTE
                                inner join
                                    dbo.V_QUERY_RECEIPTING_EMAILGIFTDETAILOUTPUT STANDARDVIEW with (nolock) on CTE.CONSTITUENTID = STANDARDVIEW.CONSTITUENTID
                                where exists
                                (
                                    select top 1 RSSUB.ID from dbo.REVENUESPLIT RSSUB
                                    cross apply dbo.UFN_SITEID_MAPFROM_REVENUESPLITID(RSSUB.ID) REVSITES
                                    where RSSUB.REVENUEID = STANDARDVIEW.REVENUEID
                                    and (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[REVSITES].[SITEID] or (SITEID is null and [REVSITES].[SITEID] is null)))
                                )
                                order by    
                                    STANDARDVIEW.CONSTITUENTNAME;
                            end                    
                        end