USP_REPORT_CONSTITUENTTRIBUTE
Returns constituents and associated tributes.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @DATETYPE | tinyint | IN | |
| @DATERANGEDISPLAY | nvarchar(100) | IN | |
| @STARTDATE | datetime | IN | |
| @ENDDATE | datetime | IN | |
| @CONSTITUENTQUERY | uniqueidentifier | IN | |
| @CONSTITUENTID | uniqueidentifier | IN | |
| @TRIBUTETYPECODEID | uniqueidentifier | IN | |
| @ISUKINSTALLED | tinyint | IN | |
| @REPORTUSERID | nvarchar(128) | IN | |
| @CURRENCYCODE | tinyint | IN | |
| @ALTREPORTUSERID | nvarchar(128) | IN |
Definition
Copy
CREATE procedure dbo.USP_REPORT_CONSTITUENTTRIBUTE
(
@DATETYPE tinyint = null,
@DATERANGEDISPLAY nvarchar(100) = '',
@STARTDATE datetime = null,
@ENDDATE datetime = null,
@CONSTITUENTQUERY uniqueidentifier = null,
@CONSTITUENTID uniqueidentifier = null,
@TRIBUTETYPECODEID uniqueidentifier = null,
@ISUKINSTALLED tinyint = null,
@REPORTUSERID nvarchar(128) = null,
@CURRENCYCODE tinyint = null, --3 = My base, (null, 1) = Organization
@ALTREPORTUSERID nvarchar(128) = null
)
with execute as owner
as
set nocount on;
declare @CURRENTAPPUSERID uniqueidentifier;
declare @ISADMIN bit;
declare @APPUSER_IN_NONRACROLE bit;
declare @APPUSER_IN_NOSECGROUPROLE bit;
declare @APPUSER_IN_NONSITEROLE bit;
declare @APPUSER_IN_NOSITEROLE bit;
set @CURRENTAPPUSERID = dbo.UFN_APPUSER_GETREPORTAPPUSERID(@REPORTUSERID, @ALTREPORTUSERID);
set @ISADMIN = dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID);
set @APPUSER_IN_NONRACROLE = dbo.UFN_SECURITY_APPUSER_IN_NONRACROLE(@CURRENTAPPUSERID);
set @APPUSER_IN_NOSECGROUPROLE = dbo.UFN_SECURITY_APPUSER_IN_NO_SECURITY_GROUP_ROLE(@CURRENTAPPUSERID);
set @APPUSER_IN_NONSITEROLE = dbo.UFN_SECURITY_APPUSER_IN_NONSITEROLE(@CURRENTAPPUSERID);
set @APPUSER_IN_NOSITEROLE = dbo.UFN_SECURITY_APPUSER_IN_NO_SITE_ROLE(@CURRENTAPPUSERID);
declare @ORGANIZATIONCURRENCYID uniqueidentifier = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();
declare @SELECTEDCURRENCYID uniqueidentifier;
if @CURRENCYCODE = 3
begin
if dbo.UFN_CURRENCYSET_GETAPPUSERCURRENCYSET(@CURRENTAPPUSERID) is not null
begin
select @SELECTEDCURRENCYID = CURRENCYSET.BASECURRENCYID
from dbo.CURRENCYSET
where
CURRENCYSET.ID = dbo.UFN_CURRENCYSET_GETAPPUSERCURRENCYSET(@CURRENTAPPUSERID);
end
else
begin
select @SELECTEDCURRENCYID = CURRENCYSET.BASECURRENCYID
from dbo.CURRENCYSET
where
CURRENCYSET.ID = dbo.UFN_CURRENCYSET_GETAPPUSERDEFAULTCURRENCYSET();
end
end
else
set @SELECTEDCURRENCYID = @ORGANIZATIONCURRENCYID;
declare @SELECTEDCURRENCYISO nvarchar(3);
declare @SELECTEDCURRENCYDECIMALDIGITS tinyint;
declare @SELECTEDCURRENCYSYMBOL nvarchar(5);
declare @SELECTEDCURRENCYSYMBOLDISPLAYSETTINGCODE tinyint;
declare @SELECTEDCURRENCYROUNDINGTYPECODE tinyint;
select
@SELECTEDCURRENCYISO = CURRENCYPROPERTIES.ISO4217,
@SELECTEDCURRENCYDECIMALDIGITS = CURRENCYPROPERTIES.DECIMALDIGITS,
@SELECTEDCURRENCYSYMBOL = CURRENCYPROPERTIES.CURRENCYSYMBOL,
@SELECTEDCURRENCYSYMBOLDISPLAYSETTINGCODE = CURRENCYPROPERTIES.SYMBOLDISPLAYSETTINGCODE,
@SELECTEDCURRENCYROUNDINGTYPECODE = CURRENCYPROPERTIES.ROUNDINGTYPECODE
from
dbo.UFN_CURRENCY_GETPROPERTIES(@SELECTEDCURRENCYID) CURRENCYPROPERTIES
declare @SQLTOEXEC nvarchar(max);
declare @DBOBJECTNAME nvarchar(128);
declare @DBOBJECTTYPE smallint;
if @CONSTITUENTQUERY is not null begin
if not exists(select ID from dbo.IDSETREGISTER where ID = @CONSTITUENTQUERY)
raiserror('ID set does not exist in the database.', 15, 1);
select @DBOBJECTNAME = DBOBJECTNAME, @DBOBJECTTYPE = OBJECTTYPE from dbo.IDSETREGISTER where ID = @CONSTITUENTQUERY;
if @DBOBJECTTYPE = 1
set @DBOBJECTNAME = @DBOBJECTNAME + '()';
else if @DBOBJECTTYPE = 2
set @DBOBJECTNAME = @DBOBJECTNAME + '(''' + convert(nvarchar(36), @CONSTITUENTQUERY) + ''')';
end
set @SQLTOEXEC =
'select distinct
''http://www.blackbaud.com/CONSTITUENTID?CONSTITUENTID='' + CONVERT(nvarchar(36), C.ID) as CONSTITUENTID,
''http://www.blackbaud.com/TRIBUTEID?TRIBUTEID='' + CONVERT(nvarchar(36), TRIBUTE.ID) as TRIBUTEID,
C.ID,
NF.NAME,
C.KEYNAME,
C.FIRSTNAME,
case when exists (select DC.ID from dbo.DECEASEDCONSTITUENT DC where DC.ID = C.ID) then ''Yes'' else ''No'' end as ISDECEASED,
case when C.ISINACTIVE = 1 then ''No'' else ''Yes'' end as ISACTIVE,
dbo.UFN_BUILDFULLADDRESS(A.ID, A.ADDRESSBLOCK, A.CITY, A.STATEID, A.POSTCODE, A.COUNTRYID) [ADDRESS],
TRIBUTE.TRIBUTETEXT,
case when TRIBUTE.ISACTIVE = 1 then ''Yes'' else ''No'' end as ISTRIBUTEACTIVE,
''Yes'' as ISTRIBUTEE,
case
when exists
(select T.TRIBUTEEID from dbo.TRIBUTE T inner join dbo.TRIBUTEACKNOWLEDGEE A on T.ID = A.TRIBUTEID where T.ID = TRIBUTE.ID and A.CONSTITUENTID = C.ID)
then ''Yes'' else ''No'' end as ISACKNOWLEDGEE,
(select isnull(sum(RT.AMOUNTINCURRENCY), 0)
from
dbo.UFN_REVENUETRIBUTE_GETAMOUNTSINCURRENCY_BULK(@SELECTEDCURRENCYID, @ORGANIZATIONCURRENCYID, @SELECTEDCURRENCYDECIMALDIGITS, @SELECTEDCURRENCYROUNDINGTYPECODE) RT
inner join dbo.REVENUE R with (nolock) on RT.REVENUEID = R.ID
where RT.TRIBUTEID = TRIBUTE.ID
and (@ISADMIN = 1 or
(
(@APPUSER_IN_NONRACROLE = 1 or
dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT(@CURRENTAPPUSERID, R.CONSTITUENTID, @APPUSER_IN_NOSECGROUPROLE) = 1)
and
(@APPUSER_IN_NONSITEROLE = 1 or
dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT_BYSITE(@CURRENTAPPUSERID, C.ID, @APPUSER_IN_NOSITEROLE) = 1)
)
)
and exists(
select ID
from dbo.REVENUESPLIT
where REVENUEID=R.ID
and dbo.UFN_DESIGNATION_USERHASSITEACCESS(REVENUESPLIT.DESIGNATIONID,@CURRENTAPPUSERID) = 1
)
) as REVENUETRIBUTEAMOUNT,
(select
isnull(sum(RT.AMOUNTINCURRENCY + isnull(RTTCA.AMOUNTINCURRENCY, 0)), 0)
from
dbo.UFN_REVENUETRIBUTE_GETAMOUNTSINCURRENCY_BULK(@SELECTEDCURRENCYID, @ORGANIZATIONCURRENCYID, @SELECTEDCURRENCYDECIMALDIGITS, @SELECTEDCURRENCYROUNDINGTYPECODE) RT
inner join dbo.REVENUE R with (nolock) on RT.REVENUEID = R.ID
left join dbo.UFN_REVENUETRIBUTETAXCLAIMAMOUNT_GETAMOUNTSINCURRENCY_BULK(@SELECTEDCURRENCYID, @ORGANIZATIONCURRENCYID, @SELECTEDCURRENCYDECIMALDIGITS, @SELECTEDCURRENCYROUNDINGTYPECODE) RTTCA on RT.ID = RTTCA.ID
where RT.TRIBUTEID = TRIBUTE.ID
and (@ISADMIN = 1 or
(
(@APPUSER_IN_NONRACROLE = 1 or
dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT(@CURRENTAPPUSERID, R.CONSTITUENTID, @APPUSER_IN_NOSECGROUPROLE) = 1)
and
(@APPUSER_IN_NONSITEROLE = 1 or
dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT_BYSITE(@CURRENTAPPUSERID, C.ID, @APPUSER_IN_NOSITEROLE) = 1)
)
)
and exists(
select ID
from dbo.REVENUESPLIT
where REVENUEID=R.ID
and dbo.UFN_DESIGNATION_USERHASSITEACCESS(REVENUESPLIT.DESIGNATIONID,@CURRENTAPPUSERID) = 1
)
) as GROSSAMOUNT,
TRIBUTE.DATEADDED as DATECREATED,' + NCHAR(13)
--Adding a non nvarchar(max) in the previous line truncates the concatenation to 4000 chars, breaking the line here to add more text
set @SQLTOEXEC = @SQLTOEXEC +
'
@SELECTEDCURRENCYID SELECTEDCURRENCYID,
@SELECTEDCURRENCYISO as SELECTEDCURRENCYISO,
@SELECTEDCURRENCYDECIMALDIGITS as SELECTEDCURRENCYDECIMALDIGITS,
@SELECTEDCURRENCYSYMBOL as SELECTEDCURRENCYSYMBOL,
@SELECTEDCURRENCYSYMBOLDISPLAYSETTINGCODE as SELECTEDCURRENCYSYMBOLDISPLAYSETTINGCODE
from dbo.CONSTITUENT C with (nolock)
inner join dbo.TRIBUTE on C.ID = TRIBUTE.TRIBUTEEID
left join dbo.ADDRESS A on C.ID = A.CONSTITUENTID and ISPRIMARY = 1
inner join dbo.REVENUETRIBUTE OUTERRT on TRIBUTE.ID = OUTERRT.TRIBUTEID
outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(C.ID) NF' + nchar(13)
if @CONSTITUENTQUERY is not null
set @SQLTOEXEC = @SQLTOEXEC + 'inner join dbo.' + @DBOBJECTNAME + ' as SELECTION on C.ID = SELECTION.[ID]' + nchar(13);
set @SQLTOEXEC = @SQLTOEXEC +
'where
(@STARTDATE is null or TRIBUTE.DATEADDED >= dbo.UFN_DATE_GETEARLIESTTIME(@STARTDATE))
and (@ENDDATE is null or TRIBUTE.DATEADDED <= dbo.UFN_DATE_GETLATESTTIME(@ENDDATE))
and (@TRIBUTETYPECODEID is null or TRIBUTE.TRIBUTETYPECODEID = @TRIBUTETYPECODEID)
and (@CONSTITUENTID is null or C.ID = @CONSTITUENTID)
and (@ISADMIN = 1 or
(
(@APPUSER_IN_NONRACROLE = 1
or dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT(@CURRENTAPPUSERID, C.ID, @APPUSER_IN_NOSECGROUPROLE) = 1)
and
(@APPUSER_IN_NONSITEROLE = 1 or
dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT_BYSITE(@CURRENTAPPUSERID, C.ID, @APPUSER_IN_NOSITEROLE) = 1)
)
)
' + nchar(13)
set @SQLTOEXEC = @SQLTOEXEC +
'union all
select distinct
''http://www.blackbaud.com/CONSTITUENTID?CONSTITUENTID='' + CONVERT(nvarchar(36), C.ID) as CONSTITUENTID,
''http://www.blackbaud.com/TRIBUTEID?TRIBUTEID='' + CONVERT(nvarchar(36), TRIBUTE.ID) as TRIBUTEID,
C.ID,
NF.NAME,
C.KEYNAME,
C.FIRSTNAME,
case when exists (select DC.ID from dbo.DECEASEDCONSTITUENT DC where DC.ID = C.ID) then ''Yes'' else ''No'' end as ISDECEASED,
case when C.ISINACTIVE = 1 then ''No'' else ''Yes'' end as ISACTIVE,
dbo.UFN_BUILDFULLADDRESS(A.ID, A.ADDRESSBLOCK, A.CITY, A.STATEID, A.POSTCODE, A.COUNTRYID) [ADDRESS],
TRIBUTE.TRIBUTETEXT,
case when TRIBUTE.ISACTIVE = 1 then ''Yes'' else ''No'' end as ISTRIBUTEACTIVE,
''No'' as ISTRIBUTEE,
''Yes'' as ISACKNOWLEDGEE,
(select isnull(sum(RT.AMOUNTINCURRENCY), 0)
from
dbo.UFN_REVENUETRIBUTE_GETAMOUNTSINCURRENCY_BULK(@SELECTEDCURRENCYID, @ORGANIZATIONCURRENCYID, @SELECTEDCURRENCYDECIMALDIGITS, @SELECTEDCURRENCYROUNDINGTYPECODE) RT
join dbo.REVENUE R on RT.REVENUEID = R.ID
where RT.TRIBUTEID = TRIBUTE.ID
and (@ISADMIN = 1 or
(
(@APPUSER_IN_NONRACROLE = 1 or
dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT(@CURRENTAPPUSERID, R.CONSTITUENTID, @APPUSER_IN_NOSECGROUPROLE) = 1)
and
(@APPUSER_IN_NONSITEROLE = 1 or
dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT_BYSITE(@CURRENTAPPUSERID, C.ID, @APPUSER_IN_NOSITEROLE) = 1)
)
)
and exists(
select ID
from dbo.REVENUESPLIT
where REVENUEID=R.ID
and dbo.UFN_DESIGNATION_USERHASSITEACCESS(REVENUESPLIT.DESIGNATIONID,@CURRENTAPPUSERID) = 1
)
) as REVENUETRIBUTEAMOUNT,
(select
isnull(sum(RT.AMOUNTINCURRENCY + isnull(RTTCA.AMOUNTINCURRENCY, 0)), 0)
from
dbo.UFN_REVENUETRIBUTE_GETAMOUNTSINCURRENCY_BULK(@SELECTEDCURRENCYID, @ORGANIZATIONCURRENCYID, @SELECTEDCURRENCYDECIMALDIGITS, @SELECTEDCURRENCYROUNDINGTYPECODE) RT
inner join dbo.REVENUE R with (nolock) on RT.REVENUEID = R.ID
left join dbo.UFN_REVENUETRIBUTETAXCLAIMAMOUNT_GETAMOUNTSINCURRENCY_BULK(@SELECTEDCURRENCYID, @ORGANIZATIONCURRENCYID, @SELECTEDCURRENCYDECIMALDIGITS, @SELECTEDCURRENCYROUNDINGTYPECODE) RTTCA on RT.ID = RTTCA.ID
where RT.TRIBUTEID = TRIBUTE.ID
and (@ISADMIN = 1 or
(
(@APPUSER_IN_NONRACROLE = 1 or
dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT(@CURRENTAPPUSERID, R.CONSTITUENTID, @APPUSER_IN_NOSECGROUPROLE) = 1)
and
(@APPUSER_IN_NONSITEROLE = 1 or
dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT_BYSITE(@CURRENTAPPUSERID, C.ID, @APPUSER_IN_NOSITEROLE) = 1)
)
)
and exists(
select ID
from dbo.REVENUESPLIT
where REVENUEID=R.ID
and dbo.UFN_DESIGNATION_USERHASSITEACCESS(REVENUESPLIT.DESIGNATIONID,@CURRENTAPPUSERID) = 1
)
) as GROSSAMOUNT,
TRIBUTE.DATEADDED as DATECREATED,' + NCHAR(13)
--Adding a non nvarchar(max) in the previous line truncates the concatenation to 4000 chars, breaking the line here to add more text
set @SQLTOEXEC = @SQLTOEXEC +
'
@SELECTEDCURRENCYID SELECTEDCURRENCYID,
@SELECTEDCURRENCYISO as SELECTEDCURRENCYISO,
@SELECTEDCURRENCYDECIMALDIGITS as SELECTEDCURRENCYDECIMALDIGITS,
@SELECTEDCURRENCYSYMBOL as SELECTEDCURRENCYSYMBOL,
@SELECTEDCURRENCYSYMBOLDISPLAYSETTINGCODE as SELECTEDCURRENCYSYMBOLDISPLAYSETTINGCODE
from dbo.CONSTITUENT C with (nolock)
inner join dbo.TRIBUTEACKNOWLEDGEE TA on C.ID = TA.CONSTITUENTID
inner join dbo.TRIBUTE on TA.TRIBUTEID = TRIBUTE.ID
left join dbo.ADDRESS A on C.ID = A.CONSTITUENTID and ISPRIMARY = 1
inner join dbo.REVENUETRIBUTE OUTERRT on TRIBUTE.ID = OUTERRT.TRIBUTEID
outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(C.ID) NF' + nchar(13)
if @CONSTITUENTQUERY is not null
set @SQLTOEXEC = @SQLTOEXEC + 'inner join dbo.' + @DBOBJECTNAME + ' as SELECTION on C.ID = SELECTION.[ID]' + nchar(13);
set @SQLTOEXEC = @SQLTOEXEC +
'where
((TRIBUTE.TRIBUTEEID is null) or (TA.CONSTITUENTID <> TRIBUTE.TRIBUTEEID))
and (@STARTDATE is null or TRIBUTE.DATEADDED >= dbo.UFN_DATE_GETEARLIESTTIME(@STARTDATE))
and (@ENDDATE is null or TRIBUTE.DATEADDED <= dbo.UFN_DATE_GETLATESTTIME(@ENDDATE))
and (@TRIBUTETYPECODEID is null or TRIBUTE.TRIBUTETYPECODEID = @TRIBUTETYPECODEID)
and (@CONSTITUENTID is null or C.ID = @CONSTITUENTID)
and (@ISADMIN = 1 or
(
(@APPUSER_IN_NONRACROLE = 1
or dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT(@CURRENTAPPUSERID, C.ID, @APPUSER_IN_NOSECGROUPROLE) = 1)
and
(@APPUSER_IN_NONSITEROLE = 1 or
dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT_BYSITE(@CURRENTAPPUSERID, C.ID, @APPUSER_IN_NOSITEROLE) = 1)
)
)
' + nchar(13)
set @SQLTOEXEC = @SQLTOEXEC +
'
order by KEYNAME, FIRSTNAME, TRIBUTETEXT, DATECREATED
'
exec sp_executesql @SQLTOEXEC,
N'@STARTDATE datetime,
@ENDDATE datetime,
@CONSTITUENTID uniqueidentifier,
@TRIBUTETYPECODEID uniqueidentifier,
@ISADMIN bit,
@APPUSER_IN_NONRACROLE bit,
@APPUSER_IN_NOSECGROUPROLE bit,
@CURRENTAPPUSERID uniqueidentifier,
@APPUSER_IN_NOSITEROLE bit,
@APPUSER_IN_NONSITEROLE bit,
@CURRENCYCODE tinyint,
@SELECTEDCURRENCYID uniqueidentifier,
@ORGANIZATIONCURRENCYID uniqueidentifier,
@SELECTEDCURRENCYISO nvarchar(3),
@SELECTEDCURRENCYDECIMALDIGITS tinyint,
@SELECTEDCURRENCYSYMBOL nvarchar(5),
@SELECTEDCURRENCYSYMBOLDISPLAYSETTINGCODE tinyint,
@SELECTEDCURRENCYROUNDINGTYPECODE tinyint',
@STARTDATE=@STARTDATE,
@ENDDATE=@ENDDATE,
@CONSTITUENTID=@CONSTITUENTID,
@TRIBUTETYPECODEID=@TRIBUTETYPECODEID,
@ISADMIN=@ISADMIN,
@APPUSER_IN_NONRACROLE=@APPUSER_IN_NONRACROLE,
@APPUSER_IN_NOSECGROUPROLE=@APPUSER_IN_NOSECGROUPROLE,
@CURRENTAPPUSERID=@CURRENTAPPUSERID,
@APPUSER_IN_NOSITEROLE=@APPUSER_IN_NOSITEROLE,
@APPUSER_IN_NONSITEROLE=@APPUSER_IN_NONSITEROLE,
@CURRENCYCODE=@CURRENCYCODE,
@SELECTEDCURRENCYID=@SELECTEDCURRENCYID,
@ORGANIZATIONCURRENCYID=@ORGANIZATIONCURRENCYID,
@SELECTEDCURRENCYISO=@SELECTEDCURRENCYISO,
@SELECTEDCURRENCYDECIMALDIGITS=@SELECTEDCURRENCYDECIMALDIGITS,
@SELECTEDCURRENCYSYMBOL=@SELECTEDCURRENCYSYMBOL,
@SELECTEDCURRENCYSYMBOLDISPLAYSETTINGCODE=@SELECTEDCURRENCYSYMBOLDISPLAYSETTINGCODE,
@SELECTEDCURRENCYROUNDINGTYPECODE=@SELECTEDCURRENCYROUNDINGTYPECODE