USP_DATALIST_RECEIPTEMAILOUTPUT
Returns a list of records from the receipt process in receipt email 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_RECEIPTEMAILOUTPUT
(
@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;
select distinct top (@MAXROWS)
STANDARDVIEW.ID,
STANDARDVIEW.CONSTITUENTNAME,
STANDARDVIEW.CONSTITUENTLOOKUPID,
STANDARDVIEW.ADDRESSEE,
STANDARDVIEW.SALUTATION,
STANDARDVIEW.CONTACT,
STANDARDVIEW.POSITION,
STANDARDVIEW.EMAILADDRESS,
STANDARDVIEW.DATE,
STANDARDVIEW.PAYMENTAMOUNT,
STANDARDVIEW.RECEIPTAMOUNT,
STANDARDVIEW.TOTALBENEFITAMOUNT,
STANDARDVIEW.PAYMENTMETHOD,
STANDARDVIEW.ISSUER,
STANDARDVIEW.SYMBOL,
STANDARDVIEW.NUMBEROFUNITS,
STANDARDVIEW.MEDIANPRICE,
STANDARDVIEW.PROPERTYGIKSUBTYPE,
STANDARDVIEW.GIVENANONYMOUSLY,
STANDARDVIEW.PORTIONSUBJECTTOVAT,
STANDARDVIEW.VATTAXRATEDESCRIPTION,
STANDARDVIEW.VATTAXRATE,
STANDARDVIEW.VATAMOUNT,
STANDARDVIEW.GIFTINKINDITEMNAME,
STANDARDVIEW.GIFTINKINDDISPOSITION,
STANDARDVIEW.GIFTINKINDNUMBEROFUNITS,
STANDARDVIEW.GIFTINKINDFAIRMARKETVALUE,
STANDARDVIEW.BENEFITSWAIVED,
STANDARDVIEW.TRANSACTIONCURRENCYID,
STANDARDVIEW.TRANSACTIONCURRENCY
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
inner join
dbo.V_QUERY_RECEIPTEMAILOUTPUT STANDARDVIEW on REVENUE.ID = STANDARDVIEW.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)
and REVENUE.RECEIPTTYPECODE = 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)))
)
order by
STANDARDVIEW.CONSTITUENTNAME;
end
else
begin
set nocount on;
select distinct top (@MAXROWS)
STANDARDVIEW.ID,
STANDARDVIEW.CONSTITUENTNAME,
STANDARDVIEW.CONSTITUENTLOOKUPID,
STANDARDVIEW.ADDRESSEE,
STANDARDVIEW.SALUTATION,
STANDARDVIEW.CONTACT,
STANDARDVIEW.POSITION,
STANDARDVIEW.EMAILADDRESS,
STANDARDVIEW.DATE,
STANDARDVIEW.PAYMENTAMOUNT,
STANDARDVIEW.RECEIPTAMOUNT,
STANDARDVIEW.TOTALBENEFITAMOUNT,
STANDARDVIEW.PAYMENTMETHOD,
STANDARDVIEW.ISSUER,
STANDARDVIEW.SYMBOL,
STANDARDVIEW.NUMBEROFUNITS,
STANDARDVIEW.MEDIANPRICE,
STANDARDVIEW.PROPERTYGIKSUBTYPE,
STANDARDVIEW.GIVENANONYMOUSLY,
STANDARDVIEW.PORTIONSUBJECTTOVAT,
STANDARDVIEW.VATTAXRATEDESCRIPTION,
STANDARDVIEW.VATTAXRATE,
STANDARDVIEW.VATAMOUNT,
STANDARDVIEW.GIFTINKINDITEMNAME,
STANDARDVIEW.GIFTINKINDDISPOSITION,
STANDARDVIEW.GIFTINKINDNUMBEROFUNITS,
STANDARDVIEW.GIFTINKINDFAIRMARKETVALUE,
STANDARDVIEW.BENEFITSWAIVED,
STANDARDVIEW.TRANSACTIONCURRENCYID,
STANDARDVIEW.TRANSACTIONCURRENCY
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
inner join
dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITIDS_FORBUSINESSPROCESS(@OWNERID, @BPID) as CONSTIT_RACS on REVENUE.CONSTITUENTID = CONSTIT_RACS.ID
inner join
dbo.V_QUERY_RECEIPTEMAILOUTPUT STANDARDVIEW on REVENUE.ID = STANDARDVIEW.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)
and REVENUE.RECEIPTTYPECODE = 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)))
)
order by
STANDARDVIEW.CONSTITUENTNAME;
end
end
else
begin
if @SELECTIONID is null
begin
set nocount on;
select distinct top (@MAXROWS)
STANDARDVIEW.ID,
STANDARDVIEW.CONSTITUENTNAME,
STANDARDVIEW.CONSTITUENTLOOKUPID,
STANDARDVIEW.ADDRESSEE,
STANDARDVIEW.SALUTATION,
STANDARDVIEW.CONTACT,
STANDARDVIEW.POSITION,
STANDARDVIEW.EMAILADDRESS,
STANDARDVIEW.DATE,
STANDARDVIEW.PAYMENTAMOUNT,
STANDARDVIEW.RECEIPTAMOUNT,
STANDARDVIEW.TOTALBENEFITAMOUNT,
STANDARDVIEW.PAYMENTMETHOD,
STANDARDVIEW.ISSUER,
STANDARDVIEW.SYMBOL,
STANDARDVIEW.NUMBEROFUNITS,
STANDARDVIEW.MEDIANPRICE,
STANDARDVIEW.PROPERTYGIKSUBTYPE,
STANDARDVIEW.GIVENANONYMOUSLY,
STANDARDVIEW.PORTIONSUBJECTTOVAT,
STANDARDVIEW.VATTAXRATEDESCRIPTION,
STANDARDVIEW.VATTAXRATE,
STANDARDVIEW.VATAMOUNT,
STANDARDVIEW.GIFTINKINDITEMNAME,
STANDARDVIEW.GIFTINKINDDISPOSITION,
STANDARDVIEW.GIFTINKINDNUMBEROFUNITS,
STANDARDVIEW.GIFTINKINDFAIRMARKETVALUE,
STANDARDVIEW.BENEFITSWAIVED,
STANDARDVIEW.TRANSACTIONCURRENCYID,
STANDARDVIEW.TRANSACTIONCURRENCY
from
dbo.REVENUE with (nolock)
inner join
dbo.UFN_BUSINESSPROCESSCOMMPREF_GETINCLUDEDCONSTITUENT(@STARTDATE, @EXCLUDEDECEASED, @EXCLUDEINACTIVE, @INCLUSIONS, @EXCLUSIONS) AS EXC ON REVENUE.CONSTITUENTID = EXC.ID
inner join
dbo.V_QUERY_RECEIPTEMAILOUTPUT STANDARDVIEW on REVENUE.ID = STANDARDVIEW.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)
and REVENUE.RECEIPTTYPECODE = 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)))
)
order by
STANDARDVIEW.CONSTITUENTNAME;
end
else
begin
set nocount on;
select distinct top (@MAXROWS)
STANDARDVIEW.ID,
STANDARDVIEW.CONSTITUENTNAME,
STANDARDVIEW.CONSTITUENTLOOKUPID,
STANDARDVIEW.ADDRESSEE,
STANDARDVIEW.SALUTATION,
STANDARDVIEW.CONTACT,
STANDARDVIEW.POSITION,
STANDARDVIEW.EMAILADDRESS,
STANDARDVIEW.DATE,
STANDARDVIEW.PAYMENTAMOUNT,
STANDARDVIEW.RECEIPTAMOUNT,
STANDARDVIEW.TOTALBENEFITAMOUNT,
STANDARDVIEW.PAYMENTMETHOD,
STANDARDVIEW.ISSUER,
STANDARDVIEW.SYMBOL,
STANDARDVIEW.NUMBEROFUNITS,
STANDARDVIEW.MEDIANPRICE,
STANDARDVIEW.PROPERTYGIKSUBTYPE,
STANDARDVIEW.GIVENANONYMOUSLY,
STANDARDVIEW.PORTIONSUBJECTTOVAT,
STANDARDVIEW.VATTAXRATEDESCRIPTION,
STANDARDVIEW.VATTAXRATE,
STANDARDVIEW.VATAMOUNT,
STANDARDVIEW.GIFTINKINDITEMNAME,
STANDARDVIEW.GIFTINKINDDISPOSITION,
STANDARDVIEW.GIFTINKINDNUMBEROFUNITS,
STANDARDVIEW.GIFTINKINDFAIRMARKETVALUE,
STANDARDVIEW.BENEFITSWAIVED,
STANDARDVIEW.TRANSACTIONCURRENCYID,
STANDARDVIEW.TRANSACTIONCURRENCY
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
inner join
dbo.V_QUERY_RECEIPTEMAILOUTPUT STANDARDVIEW on REVENUE.ID = STANDARDVIEW.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)
and REVENUE.RECEIPTTYPECODE = 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)))
)
order by
STANDARDVIEW.CONSTITUENTNAME;
end
end