USP_DATALIST_PLEDGEREMINDERFORMATTEDOUTPUT
Returns a list of records from the pledge reminder process with selected formatting applied in constituent mail form.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @SELECTIONID | uniqueidentifier | IN | SelectionID |
| @DATE | datetime | IN | Date |
| @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 |
| @NAMEFORMATPARAMETERID | uniqueidentifier | IN | Name format ID |
| @ADDRESSPROCESSINGOPTIONID | uniqueidentifier | IN | Address format |
| @PARAMETERSETID | uniqueidentifier | IN | Parameter set ID |
| @STARTDATE | datetime | IN | |
| @EXCLUDEDECEASED | bit | IN | |
| @EXCLUDEINACTIVE | bit | IN | |
| @INCLUSIONS | xml | IN | |
| @EXCLUSIONS | xml | IN | |
| @OWNERID | uniqueidentifier | 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_PLEDGEREMINDERFORMATTEDOUTPUT
(
@SELECTIONID uniqueidentifier = null,
@DATE datetime,
@MAXROWS int,
@EXCLUDEMAIL bit,
@EXCLUDEPHONE bit,
@EXCLUDEEMAIL bit,
@NAMEFORMATPARAMETERID uniqueidentifier = null,
@ADDRESSPROCESSINGOPTIONID uniqueidentifier = null,
@PARAMETERSETID uniqueidentifier = null,
@STARTDATE datetime = null,
@EXCLUDEDECEASED bit = null,
@EXCLUDEINACTIVE bit = null,
@INCLUSIONS xml = null,
@EXCLUSIONS xml = null,
@OWNERID uniqueidentifier = null,
--@OVERRIDEREQUIREDEXCLUSIONS bit = 0,
--@REQUIREDEXCLUSIONS xml = null
@CURRENTAPPUSERID uniqueidentifier = null,
@SECURITYFEATUREID uniqueidentifier = null,
@SECURITYFEATURETYPE tinyint = null
)
as
/*
This is not really used anymore for preview but the metadata is for the BP
The Stored procedure has become to complex to be loaded anymore.
Ideally we should rewrite
declare @BYPASSSECURITY bit;
declare @BPID uniqueidentifier;
set @BPID = '84414BD2-3196-4697-998A-2493C29A302A';
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 REMINDER_CTE as (
select top (@MAXROWS)
REVENUE.ID as REVENUEID,
REVENUE.TRANSACTIONTYPECODE as TC
from
dbo.REVENUE with (nolock)
inner join
dbo.REVENUESCHEDULE SCHEDULE on REVENUE.ID = SCHEDULE.ID
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
SCHEDULE.SENDPLEDGEREMINDER = 1
and (REVENUE.TRANSACTIONTYPECODE = 1 and dbo.UFN_PLEDGE_PAYMENTDUE(REVENUE.ID, @DATE) > 0)
or (REVENUE.TRANSACTIONTYPECODE = 2 and dbo.UFN_RECURRINGGIFT_PAYMENTDUE(REVENUE.ID, @DATE) > 0)
and exists
(
select top 1 RSSUB.ID from dbo.REVENUESPLIT RSSUB
cross apply dbo.UFN_SITEID_MAPFROM_REVENUESPLITID(RSSUB.ID) REVSITES
where RSSUB.REVENUEID = REVENUE.ID
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)))
)
)
select top (@MAXROWS)
STANDARDVIEW.*,
case when REM.TC = 1 then dbo.UFN_PLEDGE_PAYMENTDUE(REM.REVENUEID, @DATE) else dbo.UFN_RECURRINGGIFT_PAYMENTDUE(REM.REVENUEID, @DATE) end as AMOUNTDUE
from
REMINDER_CTE REM with (nolock)
inner join
dbo.UFN_QUERY_PLEDGEREMINDEROUTPUT(@NAMEFORMATPARAMETERID,@ADDRESSPROCESSINGOPTIONID,@PARAMETERSETID,@STARTDATE) STANDARDVIEW on REM.REVENUEID = STANDARDVIEW.REVENUEID
order by
STANDARDVIEW.CONSTITUENTNAME;
end
else
begin
set nocount on;
with REMINDER_CTE as (
select top (@MAXROWS)
REVENUE.ID as REVENUEID,
REVENUE.TRANSACTIONTYPECODE as TC
from
dbo.UFN_IDSETREADER_GETRESULTS_GUID(@SELECTIONID) SELECTION
inner join
dbo.REVENUE on SELECTION.ID = REVENUE.ID
inner join
dbo.REVENUESCHEDULE SCHEDULE on REVENUE.ID = SCHEDULE.ID
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
SCHEDULE.SENDPLEDGEREMINDER = 1
and (REVENUE.TRANSACTIONTYPECODE = 1 and dbo.UFN_PLEDGE_PAYMENTDUE(REVENUE.ID, @DATE) > 0)
or (REVENUE.TRANSACTIONTYPECODE = 2 and dbo.UFN_RECURRINGGIFT_PAYMENTDUE(REVENUE.ID, @DATE) > 0)
and exists
(
select top 1 RSSUB.ID from dbo.REVENUESPLIT RSSUB
cross apply dbo.UFN_SITEID_MAPFROM_REVENUESPLITID(RSSUB.ID) REVSITES
where RSSUB.REVENUEID = REVENUE.ID
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)))
)
)
select top (@MAXROWS)
STANDARDVIEW.*,
case when REM.TC = 1 then dbo.UFN_PLEDGE_PAYMENTDUE(REM.REVENUEID, @DATE) else dbo.UFN_RECURRINGGIFT_PAYMENTDUE(REM.REVENUEID, @DATE) end as AMOUNTDUE
from
REMINDER_CTE REM
inner join
dbo.UFN_QUERY_PLEDGEREMINDEROUTPUT(@NAMEFORMATPARAMETERID,@ADDRESSPROCESSINGOPTIONID,@PARAMETERSETID,@STARTDATE) STANDARDVIEW on REM.REVENUEID = STANDARDVIEW.REVENUEID
order by
STANDARDVIEW.CONSTITUENTNAME;
end
end
else
begin
if @SELECTIONID is null
begin
set nocount on;
with REMINDER_CTE as (
select top (@MAXROWS)
REVENUE.ID as REVENUEID,
REVENUE.TRANSACTIONTYPECODE as TC
from
dbo.REVENUE with (nolock)
inner join
dbo.REVENUESCHEDULE SCHEDULE on REVENUE.ID = SCHEDULE.ID
inner join
dbo.UFN_BUSINESSPROCESSCOMMPREF_GETINCLUDEDCONSTITUENT(@STARTDATE, @EXCLUDEDECEASED, @EXCLUDEINACTIVE, @INCLUSIONS, @EXCLUSIONS) AS EXC ON REVENUE.CONSTITUENTID = EXC.ID
where
SCHEDULE.SENDPLEDGEREMINDER = 1
and (REVENUE.TRANSACTIONTYPECODE = 1 and dbo.UFN_PLEDGE_PAYMENTDUE(REVENUE.ID, @DATE) > 0)
or (REVENUE.TRANSACTIONTYPECODE = 2 and dbo.UFN_RECURRINGGIFT_PAYMENTDUE(REVENUE.ID, @DATE) > 0)
and exists
(
select top 1 RSSUB.ID from dbo.REVENUESPLIT RSSUB
cross apply dbo.UFN_SITEID_MAPFROM_REVENUESPLITID(RSSUB.ID) REVSITES
where RSSUB.REVENUEID = REVENUE.ID
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)))
)
)
select top (@MAXROWS)
STANDARDVIEW.*,
case when REM.TC = 1 then dbo.UFN_PLEDGE_PAYMENTDUE(REM.REVENUEID, @DATE) else dbo.UFN_RECURRINGGIFT_PAYMENTDUE(REM.REVENUEID, @DATE) end as AMOUNTDUE
from
REMINDER_CTE REM with (nolock)
inner join
dbo.UFN_QUERY_PLEDGEREMINDEROUTPUT(@NAMEFORMATPARAMETERID,@ADDRESSPROCESSINGOPTIONID,@PARAMETERSETID,@STARTDATE) STANDARDVIEW on REM.REVENUEID = STANDARDVIEW.REVENUEID
order by
STANDARDVIEW.CONSTITUENTNAME;
end
else
begin
set nocount on;
with REMINDER_CTE as (
select top (@MAXROWS)
REVENUE.ID as REVENUEID,
REVENUE.TRANSACTIONTYPECODE as TC
from
dbo.UFN_IDSETREADER_GETRESULTS_GUID(@SELECTIONID) SELECTION
inner join
dbo.REVENUE on SELECTION.ID = REVENUE.ID
inner join
dbo.REVENUESCHEDULE SCHEDULE on REVENUE.ID = SCHEDULE.ID
inner join
dbo.UFN_BUSINESSPROCESSCOMMPREF_GETINCLUDEDCONSTITUENT(@STARTDATE, @EXCLUDEDECEASED, @EXCLUDEINACTIVE, @INCLUSIONS, @EXCLUSIONS) AS EXC ON REVENUE.CONSTITUENTID = EXC.ID
where
SCHEDULE.SENDPLEDGEREMINDER = 1
and (REVENUE.TRANSACTIONTYPECODE = 1 and dbo.UFN_PLEDGE_PAYMENTDUE(REVENUE.ID, @DATE) > 0)
or (REVENUE.TRANSACTIONTYPECODE = 2 and dbo.UFN_RECURRINGGIFT_PAYMENTDUE(REVENUE.ID, @DATE) > 0)
and exists
(
select top 1 RSSUB.ID from dbo.REVENUESPLIT RSSUB
cross apply dbo.UFN_SITEID_MAPFROM_REVENUESPLITID(RSSUB.ID) REVSITES
where RSSUB.REVENUEID = REVENUE.ID
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)))
)
)
select top (@MAXROWS)
STANDARDVIEW.*,
case when REM.TC = 1 then dbo.UFN_PLEDGE_PAYMENTDUE(REM.REVENUEID, @DATE) else dbo.UFN_RECURRINGGIFT_PAYMENTDUE(REM.REVENUEID, @DATE) end as AMOUNTDUE
from
REMINDER_CTE REM
inner join
dbo.UFN_QUERY_PLEDGEREMINDEROUTPUT(@NAMEFORMATPARAMETERID,@ADDRESSPROCESSINGOPTIONID,@PARAMETERSETID,@STARTDATE) STANDARDVIEW on REM.REVENUEID = STANDARDVIEW.REVENUEID
order by
STANDARDVIEW.CONSTITUENTNAME;
end
end
*/
with REMINDER_CTE as (
select top (@MAXROWS)
REVENUE.ID as REVENUEID,
REVENUE.TRANSACTIONTYPECODE as TC
from
dbo.UFN_IDSETREADER_GETRESULTS_GUID(@SELECTIONID) SELECTION
inner join dbo.REVENUE on SELECTION.ID = REVENUE.ID and @SELECTIONID is not null
inner join dbo.REVENUESCHEDULE SCHEDULE on REVENUE.ID = SCHEDULE.ID
inner join dbo.UFN_BUSINESSPROCESSCOMMPREF_GETINCLUDEDCONSTITUENT(@STARTDATE, @EXCLUDEDECEASED, @EXCLUDEINACTIVE, @INCLUSIONS, @EXCLUSIONS) AS EXC ON REVENUE.CONSTITUENTID = EXC.ID
where
SCHEDULE.SENDPLEDGEREMINDER = 1
and (REVENUE.TRANSACTIONTYPECODE = 1 and dbo.UFN_PLEDGE_PAYMENTDUE(REVENUE.ID, @DATE) > 0)
or (REVENUE.TRANSACTIONTYPECODE = 2 and dbo.UFN_RECURRINGGIFT_PAYMENTDUE(REVENUE.ID, @DATE) > 0)
and exists
(
select top 1 RSSUB.ID from dbo.REVENUESPLIT RSSUB
cross apply dbo.UFN_SITEID_MAPFROM_REVENUESPLITID(RSSUB.ID) REVSITES
where RSSUB.REVENUEID = REVENUE.ID
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)))
)
)
select top (@MAXROWS)
STANDARDVIEW.*,
case when REM.TC = 1 then dbo.UFN_PLEDGE_PAYMENTDUE(REM.REVENUEID, @DATE) else dbo.UFN_RECURRINGGIFT_PAYMENTDUE(REM.REVENUEID, @DATE) end as AMOUNTDUE
from
REMINDER_CTE REM
inner join dbo.UFN_QUERY_PLEDGEREMINDEROUTPUT(@NAMEFORMATPARAMETERID,@ADDRESSPROCESSINGOPTIONID,@PARAMETERSETID,@STARTDATE) STANDARDVIEW on REM.REVENUEID = STANDARDVIEW.REVENUEID
order by
STANDARDVIEW.CONSTITUENTNAME;