UFN_DATALIST_CONSTITUENT_RECOGNITIONHISTORY_GROUP
Returns data for the ConstituentGroupRecognitionHistory datalist for a group or household constituent.
Return
| Return Type |
|---|
| table |
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @CONSTITUENTID | uniqueidentifier | IN | |
| @NUMBERTOSHOWCODE | smallint | IN | |
| @CURRENTAPPUSERID | uniqueidentifier | IN | |
| @SITEFILTERMODE | tinyint | IN | |
| @SITESSELECTED | xml | IN | |
| @SECURITYFEATUREID | uniqueidentifier | IN | |
| @SECURITYFEATURETYPE | tinyint | IN |
Definition
Copy
CREATE function dbo.UFN_DATALIST_CONSTITUENT_RECOGNITIONHISTORY_GROUP
(
@CONSTITUENTID uniqueidentifier,
@NUMBERTOSHOWCODE smallint = 2,
@CURRENTAPPUSERID uniqueidentifier,
@SITEFILTERMODE tinyint = 0,
@SITESSELECTED xml = null,
@SECURITYFEATUREID uniqueidentifier = null,
@SECURITYFEATURETYPE tinyint = null
)
returns @R table
(
RECOGNITIONID uniqueidentifier,
EFFECTIVEDATE datetime,
TYPE nvarchar(100),
AMOUNT money,
GROSSAMOUNT money,
GIFTAMOUNT money,
DONOR nvarchar(200),
REVENUETYPE nvarchar(100),
RECORDID uniqueidentifier,
DATEADDED datetime,
CAMPAIGNS nvarchar(max),
SITES nvarchar(max),
REVENUESPLITID uniqueidentifier
)
as
begin
declare @CURRENTDATE datetime;
set @CURRENTDATE = getdate();
if @NUMBERTOSHOWCODE in (0,3,4,5) begin
declare @STARTDATE datetime;
if @NUMBERTOSHOWCODE = 3 --Last 30 Days
set @STARTDATE = dbo.UFN_DATE_GETEARLIESTTIME(dateadd(day,-29,@CURRENTDATE));
else if @NUMBERTOSHOWCODE = 4 --Last 90 Days
set @STARTDATE = dbo.UFN_DATE_GETEARLIESTTIME(dateadd(day,-89,@CURRENTDATE));
else if @NUMBERTOSHOWCODE = 5 --Last Year
set @STARTDATE = dbo.UFN_DATE_GETEARLIESTTIME(dateadd(day,1,dateadd(year,-1,@CURRENTDATE)))
else
set @STARTDATE = @CURRENTDATE;
declare @ENDDATE datetime;
set @ENDDATE = dbo.UFN_DATE_GETLATESTTIME(@CURRENTDATE);
insert into @R
select
[RECOGNITIONS].RECOGNITIONID,
[RECOGNITIONS].RECOGNITIONDATE as [EFFECTIVEDATE],
REVENUERECOGNITIONTYPECODE.DESCRIPTION as [TYPE],
[RECOGNITIONS].RECOGNITIONAMOUNT as [AMOUNT],
ROUND (
(
case
when REVENUESPLIT.AMOUNT > 0 then
case REVENUE.TRANSACTIONTYPECODE
when 0
then case
when [RECOGNITIONS].RECOGNITIONAMOUNT > REVENUESPLIT.AMOUNT
then dbo.UFN_GIFTAIDREVENUESPLIT_GETSPLITTAXCLAIMAMOUNT(REVENUESPLIT.ID, 1) + [RECOGNITIONS].RECOGNITIONAMOUNT
else
[RECOGNITIONS].RECOGNITIONAMOUNT/REVENUESPLIT.AMOUNT * dbo.UFN_GIFTAIDREVENUESPLIT_GETSPLITTAXCLAIMAMOUNT(REVENUESPLIT.ID, 1) + [RECOGNITIONS].RECOGNITIONAMOUNT
end
when 1
then case
when [RECOGNITIONS].RECOGNITIONAMOUNT > REVENUESPLIT.AMOUNT
then dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATEPLEDGESPLITGROSSAMOUNT(REVENUESPLIT.ID) - REVENUESPLIT.AMOUNT + [RECOGNITIONS].RECOGNITIONAMOUNT
else
[RECOGNITIONS].RECOGNITIONAMOUNT/REVENUESPLIT.AMOUNT * (dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATEPLEDGESPLITGROSSAMOUNT(REVENUESPLIT.ID) - REVENUESPLIT.AMOUNT) + [RECOGNITIONS].RECOGNITIONAMOUNT
end
else
[RECOGNITIONS].RECOGNITIONAMOUNT
end
else REVENUESPLIT.AMOUNT end
), 2
) as GROSSAMOUNT,
REVENUESPLIT.AMOUNT as [GIFTAMOUNT],
(select CONSTITUENT.NAME from dbo.CONSTITUENT where CONSTITUENT.ID = [RECOGNITIONS].REVENUECONSTITUENTID) as [DONOR],
REVENUE.TRANSACTIONTYPE as REVENUETYPE,
REVENUE.ID as [RECORDID],
[RECOGNITIONS].RECOGNITIONDATEADDED as [DATEADDED],
(
select
dbo.UDA_BUILDLIST(distinct CAMPAIGN.NAME)
from
dbo.CAMPAIGN
inner join
dbo.REVENUESPLITCAMPAIGN on CAMPAIGN.ID = REVENUESPLITCAMPAIGN.CAMPAIGNID
where
REVENUESPLITCAMPAIGN.REVENUESPLITID = REVENUESPLIT.ID
) as CAMPAIGNS,
dbo.UFN_REVENUESPLIT_BUILDSITELIST(REVENUESPLIT.ID) SITES,
REVENUESPLIT.ID as [REVENUESPLITID]
from dbo.UFN_GROUP_RECOGNITIONCREDITS(@CONSTITUENTID,null,null,null,null,1,null,0,null, null, null) as [RECOGNITIONS]
inner join dbo.REVENUE on REVENUE.ID = RECOGNITIONS.REVENUEID
inner join dbo.REVENUESPLIT on REVENUESPLIT.ID = RECOGNITIONS.REVENUESPLITID
left join dbo.REVENUERECOGNITIONTYPECODE on REVENUERECOGNITIONTYPECODE.ID = [RECOGNITIONS].REVENUERECOGNITIONTYPECODEID
left join dbo.PLANNEDGIFTREVENUE on PLANNEDGIFTREVENUE.ID = REVENUE.ID
left join dbo.PLANNEDGIFT on PLANNEDGIFT.ID = PLANNEDGIFTREVENUE.ID
where
(
REVENUE.TRANSACTIONTYPECODE = 1 --Pledge
or REVENUE.TRANSACTIONTYPECODE = 7 --Auction donation
or (REVENUE.TRANSACTIONTYPECODE = 4 and PLANNEDGIFT.VEHICLECODE in (0,1,2,5,6,7,8,9)) --Planned Gift
or (REVENUE.TRANSACTIONTYPECODE = 0 and (REVENUESPLIT.APPLICATIONCODE in (0,3) or (REVENUESPLIT.APPLICATIONCODE = 1 and REVENUESPLIT.TYPECODE = 0))) --Payment (Gift and Recurring Gift Payment)
)
and (@NUMBERTOSHOWCODE = 0 or ([RECOGNITIONS].RECOGNITIONDATE >= @STARTDATE and [RECOGNITIONS].RECOGNITIONDATE <= @ENDDATE))
order by [RECOGNITIONS].RECOGNITIONDATE desc, [RECOGNITIONS].RECOGNITIONDATEADDED desc, [RECOGNITIONS].RECOGNITIONAMOUNT desc
end
else
begin
declare @NUMBERTOSHOW int;
if @NUMBERTOSHOWCODE = 1
set @NUMBERTOSHOW = 5;
else if @NUMBERTOSHOWCODE = 2
set @NUMBERTOSHOW = 10;
else
set @NUMBERTOSHOW = 0;
insert into @R
select top(@NUMBERTOSHOW)
[RECOGNITIONS].RECOGNITIONID,
[RECOGNITIONS].RECOGNITIONDATE as [EFFECTIVEDATE],
REVENUERECOGNITIONTYPECODE.DESCRIPTION as [TYPE],
[RECOGNITIONS].RECOGNITIONAMOUNT as [AMOUNT],
ROUND (
(
case
when REVENUESPLIT.AMOUNT > 0 then
case REVENUE.TRANSACTIONTYPECODE
when 0
then case
when [RECOGNITIONS].RECOGNITIONAMOUNT > REVENUESPLIT.AMOUNT
then dbo.UFN_GIFTAIDREVENUESPLIT_GETSPLITTAXCLAIMAMOUNT(REVENUESPLIT.ID, 1) + [RECOGNITIONS].RECOGNITIONAMOUNT
else
[RECOGNITIONS].RECOGNITIONAMOUNT/REVENUESPLIT.AMOUNT * dbo.UFN_GIFTAIDREVENUESPLIT_GETSPLITTAXCLAIMAMOUNT(REVENUESPLIT.ID, 1) + [RECOGNITIONS].RECOGNITIONAMOUNT
end
when 1
then case
when [RECOGNITIONS].RECOGNITIONAMOUNT > REVENUESPLIT.AMOUNT
then dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATEPLEDGESPLITGROSSAMOUNT(REVENUESPLIT.ID) - REVENUESPLIT.AMOUNT + [RECOGNITIONS].RECOGNITIONAMOUNT
else
[RECOGNITIONS].RECOGNITIONAMOUNT/REVENUESPLIT.AMOUNT * (dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATEPLEDGESPLITGROSSAMOUNT(REVENUESPLIT.ID) - REVENUESPLIT.AMOUNT) + [RECOGNITIONS].RECOGNITIONAMOUNT
end
else
[RECOGNITIONS].RECOGNITIONAMOUNT
end
else REVENUESPLIT.AMOUNT end
), 2
) as GROSSAMOUNT,
REVENUESPLIT.AMOUNT as [GIFTAMOUNT],
(select CONSTITUENT.NAME from dbo.CONSTITUENT where CONSTITUENT.ID = [RECOGNITIONS].REVENUECONSTITUENTID) as [DONOR],
REVENUE.TRANSACTIONTYPE as REVENUETYPE,
REVENUE.ID as [RECORDID],
[RECOGNITIONS].RECOGNITIONDATEADDED as [DATEADDED],
(
select
dbo.UDA_BUILDLIST(distinct CAMPAIGN.NAME)
from
dbo.CAMPAIGN
inner join
dbo.REVENUESPLITCAMPAIGN on CAMPAIGN.ID = REVENUESPLITCAMPAIGN.CAMPAIGNID
where
REVENUESPLITCAMPAIGN.REVENUESPLITID = REVENUESPLIT.ID
) as CAMPAIGNS,
dbo.UFN_REVENUESPLIT_BUILDSITELIST(REVENUESPLIT.ID) SITES,
REVENUESPLIT.ID as [REVENUESPLITID]
from dbo.UFN_GROUP_RECOGNITIONCREDITS(@CONSTITUENTID,null,null,null,null,1,null,0,null,null,null) as [RECOGNITIONS]
inner join dbo.REVENUE on REVENUE.ID = RECOGNITIONS.REVENUEID
inner join dbo.REVENUESPLIT on REVENUESPLIT.ID = RECOGNITIONS.REVENUESPLITID
left join dbo.REVENUERECOGNITIONTYPECODE on REVENUERECOGNITIONTYPECODE.ID = [RECOGNITIONS].REVENUERECOGNITIONTYPECODEID
left join dbo.PLANNEDGIFTREVENUE on PLANNEDGIFTREVENUE.ID = REVENUE.ID
left join dbo.PLANNEDGIFT on PLANNEDGIFT.ID = PLANNEDGIFTREVENUE.ID
where
(
REVENUE.TRANSACTIONTYPECODE = 1 --Pledge
or REVENUE.TRANSACTIONTYPECODE = 7 --Auction donation
or (REVENUE.TRANSACTIONTYPECODE = 4 and PLANNEDGIFT.VEHICLECODE in (0,1,2,5,6,7,8,9)) --Planned Gift
or (REVENUE.TRANSACTIONTYPECODE = 0 and (REVENUESPLIT.APPLICATIONCODE in (0,3) or (REVENUESPLIT.APPLICATIONCODE = 1 and REVENUESPLIT.TYPECODE = 0))) --Payment (Gift and Recurring Gift Payment)
)
order by [RECOGNITIONS].RECOGNITIONDATE desc, [RECOGNITIONS].RECOGNITIONDATEADDED desc, [RECOGNITIONS].RECOGNITIONAMOUNT desc
end
return;
end