UFN_GROUP_REVENUESPLITS_FORHOUSEHOLDSMARTFIELD
Return
| Return Type |
|---|
| table |
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @GROUPID | uniqueidentifier | IN | |
| @TYPECODES | xml | IN | |
| @DESIGNATIONS | xml | IN | |
| @CAMPAIGNS | xml | IN | |
| @FILTERUNCERTAINGIFTSFORHISTORICALMEMBERS | bit | IN | |
| @ASOF | datetime | IN | |
| @INCLUDEGROUPSWITHNOREVENUE | bit | IN | |
| @GROUPTYPECODE | tinyint | IN | |
| @APPLICATIONTYPECODES | xml | IN | |
| @REVENUESELECTIONID | uniqueidentifier | IN |
Definition
Copy
CREATE function dbo.UFN_GROUP_REVENUESPLITS_FORHOUSEHOLDSMARTFIELD
(
@GROUPID uniqueidentifier,
@TYPECODES xml,
@DESIGNATIONS xml,
@CAMPAIGNS xml,
@FILTERUNCERTAINGIFTSFORHISTORICALMEMBERS bit,
@ASOF datetime,
@INCLUDEGROUPSWITHNOREVENUE bit,
@GROUPTYPECODE tinyint,
@APPLICATIONTYPECODES xml,
@REVENUESELECTIONID uniqueidentifier
)
returns @R table
(
GROUPID uniqueidentifier,
GROUPTYPECODE tinyint,
GROUPMEMBERID uniqueidentifier,
ISGROUP bit,
GROUPMEMBERSHIPSTARTDATE datetime,
GROUPMEMBERSHIPENDDATE datetime,
GROUPMEMBERSHIPISHISTORICAL bit,
REVENUEID uniqueidentifier,
REVENUESPLITID uniqueidentifier,
REVENUEDATE datetime,
REVENUESPLITAMOUNT money,
REVENUEDATEADDED datetime
)
as
begin
declare @TODAY datetime;
set @TODAY = getdate();
declare @CONSTITUENTFILTER table(ID uniqueidentifier primary key, INCLUDESMEMBERGIVING bit not null);
declare @SPLITTYPECODESFILTER table (TYPECODE tinyint primary key);
declare @DESIGNATIONSFILTER table (DESIGNATIONID uniqueidentifier primary key);
declare @CAMPAIGNSFILTER table (CAMPAIGNID uniqueidentifier primary key);
declare @APPLICATIONTYPECODESFILTER table (APPLICATIONCODE tinyint, TRANSACTIONTYPECODE tinyint);
if @TYPECODES is not null
begin
insert into @SPLITTYPECODESFILTER(TYPECODE)
select distinct T.c.value('(TYPECODEID)[1]','tinyint')
from @TYPECODES.nodes('/TYPECODES/ITEM') T(c);
if not exists (select 1 from @SPLITTYPECODESFILTER)
begin
insert into @SPLITTYPECODESFILTER(TYPECODE)
select distinct T.c.value('(REVENUETYPECODES)[1]','tinyint')
from @TYPECODES.nodes('/REVENUETYPECODES/ITEM') T(c);
end
if not exists (select 1 from @SPLITTYPECODESFILTER)
set @TYPECODES = null;
end
if @APPLICATIONTYPECODES is not null
begin
insert into @APPLICATIONTYPECODESFILTER(APPLICATIONCODE, TRANSACTIONTYPECODE)
select
T.c.value('(APPLICATION)[1]','tinyint'),
T.c.value('(TRANSACTIONTYPE)[1]','tinyint')
from @APPLICATIONTYPECODES.nodes('/SELECTIONS/ITEM') T(c)
where T.c.value('(TRANSACTIONTYPE)[1]','tinyint') is not null;
if not exists (select 1 from @APPLICATIONTYPECODESFILTER)
set @APPLICATIONTYPECODES = null;
end
if @DESIGNATIONS is not null
begin
insert into @DESIGNATIONSFILTER(DESIGNATIONID)
select T.c.value('(ID)[1]','uniqueidentifier')
from @DESIGNATIONS.nodes('/DESIGNATIONS/ITEM') T(c);
if not exists (select 1 from @DESIGNATIONSFILTER)
set @DESIGNATIONS = null;
end
if @CAMPAIGNS is not null
begin
insert into @CAMPAIGNSFILTER(CAMPAIGNID)
select T.c.value('(ID)[1]','uniqueidentifier')
from @CAMPAIGNS.nodes('/CAMPAIGNS/ITEM') T(c);
if not exists (select 1 from @CAMPAIGNSFILTER)
set @CAMPAIGNS = null;
end
--JamesWill WI60704. There are too many complexities to use the @ASOF filter, so this will simply return all gifts
--(Complexities include: want to return the same gifts for all household members; want to return households without revenue;
--want to return individuals without households; etc. I was running into issues where some gifts were not getting counted
--because of the @ASOF and things were inconsistent. This will be a little slower, but it will also be correct.
insert into @CONSTITUENTFILTER(ID, INCLUDESMEMBERGIVING)
select CONSTITUENT.ID,
case when GROUPDATA.GROUPTYPECODE = 0 then 1 else coalesce(GROUPTYPE.INCLUDEMEMBERGIVING, 0) end
from dbo.CONSTITUENT
inner join dbo.GROUPDATA on GROUPDATA.ID = CONSTITUENT.ID and (@GROUPTYPECODE is null or GROUPDATA.GROUPTYPECODE = @GROUPTYPECODE)
left join dbo.GROUPTYPE on GROUPTYPE.ID = GROUPDATA.GROUPTYPEID
where CONSTITUENT.ISGROUP = 1
and @GROUPID is null or CONSTITUENT.ID = @GROUPID;
with [CTE_MEMBERS] as
(
select
CONSTITUENT.ID as [GROUPID],
GROUPMEMBER.MEMBERID as [CONSTITUENTID],
0 as [ISGROUP],
GROUPMEMBERDATERANGE.DATEFROM,
GROUPMEMBERDATERANGE.DATETO,
case when GROUPMEMBERDATERANGE.DATETO is not null and GROUPMEMBERDATERANGE.DATETO < @TODAY then 1 else 0 end as [ISHISTORICAL],
GROUPDATA.GROUPTYPECODE
from @CONSTITUENTFILTER as [FILTER]
inner join dbo.CONSTITUENT on CONSTITUENT.ID = [FILTER].ID
inner join dbo.GROUPMEMBER on GROUPMEMBER.GROUPID = CONSTITUENT.ID
inner join dbo.GROUPDATA on GROUPDATA.ID = GROUPMEMBER.GROUPID
left join dbo.GROUPMEMBERDATERANGE on GROUPMEMBERDATERANGE.GROUPMEMBERID = GROUPMEMBER.ID
where @GROUPID is null or CONSTITUENT.ID = @GROUPID
union /*Also include giving by the household itself*/
select
CONSTITUENT.ID as [GROUPID],
CONSTITUENT.ID as [CONSTITUENTID],
1 as [ISGROUP],
null as [DATEFROM],
null as [DATETO],
0 as [ISHISTORICAL],
GROUPDATA.GROUPTYPECODE
from @CONSTITUENTFILTER as [FILTER]
inner join dbo.CONSTITUENT on CONSTITUENT.ID = [FILTER].ID
inner join dbo.GROUPDATA on GROUPDATA.ID = CONSTITUENT.ID
where @GROUPID is null or CONSTITUENT.ID = @GROUPID
),
[CTE_REVENUE] as
(
select
FINANCIALTRANSACTION.ID as [REVENUEID],
FINANCIALTRANSACTION.DATE as [REVENUEDATE],
FINANCIALTRANSACTION.CONSTITUENTID as [REVENUECONSTITUENTID],
FINANCIALTRANSACTIONLINEITEM.ID as [REVENUESPLITID],
case when FINANCIALTRANSACTION.TYPECODE in (1, 3) then
FINANCIALTRANSACTIONLINEITEM.BASEAMOUNT - coalesce((
select sum(WRITEOFFSPLIT.AMOUNT) as [WRITEOFFAMOUNT]
from dbo.WRITEOFF
inner join dbo.WRITEOFFSPLIT on WRITEOFF.ID = WRITEOFFSPLIT.WRITEOFFID
where WRITEOFF.REVENUEID = FINANCIALTRANSACTION.ID), 0)
else
FINANCIALTRANSACTIONLINEITEM.BASEAMOUNT
end as [REVENUESPLITAMOUNT],
FINANCIALTRANSACTION.DATEADDED as [REVENUEDATEADDED]
from dbo.FINANCIALTRANSACTION
inner join dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = FINANCIALTRANSACTION.ID
inner join dbo.REVENUESPLIT_EXT on REVENUESPLIT_EXT.ID = FINANCIALTRANSACTIONLINEITEM.ID
where
(@TYPECODES is null or REVENUESPLIT_EXT.TYPECODE in (select [SPLITTYPECODESFILTER].TYPECODE from @SPLITTYPECODESFILTER as [SPLITTYPECODESFILTER]))
and (
@APPLICATIONTYPECODES is null or FINANCIALTRANSACTIONLINEITEM.ID in (
select [FILTERSPLIT].ID
from dbo.FINANCIALTRANSACTIONLINEITEM as [FILTERSPLIT]
inner join dbo.REVENUESPLIT_EXT on FILTERSPLIT.ID = REVENUESPLIT_EXT.ID
inner join dbo.FINANCIALTRANSACTION as [FILTERREVENUE] on [FILTERREVENUE].ID = [FILTERSPLIT].FINANCIALTRANSACTIONID
inner join @APPLICATIONTYPECODESFILTER as [FILTER] on [FILTER].APPLICATIONCODE = [REVENUESPLIT_EXT].APPLICATIONCODE
and [FILTER].TRANSACTIONTYPECODE = [FILTERREVENUE].TYPECODE
)
)
and (@DESIGNATIONS is null or REVENUESPLIT_EXT.DESIGNATIONID in (select [DESIGNATIONSFILTER].DESIGNATIONID from @DESIGNATIONSFILTER as [DESIGNATIONSFILTER]))
and (@CAMPAIGNS is null or FINANCIALTRANSACTIONLINEITEM.ID in (select REVENUESPLITCAMPAIGN.REVENUESPLITID from dbo.REVENUESPLITCAMPAIGN inner join @CAMPAIGNSFILTER as [CAMPAIGNSFILTER] on [CAMPAIGNSFILTER].CAMPAIGNID = REVENUESPLITCAMPAIGN.CAMPAIGNID))
and (@REVENUESELECTIONID is null or FINANCIALTRANSACTIONLINEITEM.ID in (select ID from dbo.UFN_IDSETREADER_GETRESULTS_GUID(@REVENUESELECTIONID)))
and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null and FINANCIALTRANSACTIONLINEITEM.TYPECODE != 1
and FINANCIALTRANSACTION.DELETEDON is null
)
insert into @R
select
[CTE_MEMBERS].GROUPID,
[CTE_MEMBERS].GROUPTYPECODE,
[CTE_MEMBERS].CONSTITUENTID as [GROUPMEMBERID],
[CTE_MEMBERS].ISGROUP,
[CTE_MEMBERS].DATEFROM as [GROUPMEMBERSHIPSTARTDATE],
[CTE_MEMBERS].DATETO as [GROUPMEMBERSHIPENDDATE],
[CTE_MEMBERS].ISHISTORICAL as [GROUPMEMBERSHIPISHISTORICAL],
[CTE_REVENUE].REVENUEID,
[CTE_REVENUE].REVENUESPLITID,
[CTE_REVENUE].REVENUEDATE,
[CTE_REVENUE].REVENUESPLITAMOUNT as [REVENUESPLITAMOUNT],
[CTE_REVENUE].REVENUEDATEADDED
from [CTE_REVENUE]
inner join [CTE_MEMBERS] on [CTE_MEMBERS].CONSTITUENTID = [CTE_REVENUE].REVENUECONSTITUENTID
where
[CTE_MEMBERS].ISGROUP = 1
or
[CTE_MEMBERS].ISHISTORICAL = 0
or
([CTE_MEMBERS].ISHISTORICAL = 1 and (@FILTERUNCERTAINGIFTSFORHISTORICALMEMBERS = 0 or ([CTE_REVENUE].REVENUEDATE <= [CTE_MEMBERS].DATETO)))
union --Include individuals who are not in the selected groups
select
null,
null,
CONSTITUENT.ID,
0,
null,
null,
0,
[CTE_REVENUE].REVENUEID,
[CTE_REVENUE].REVENUESPLITID,
[CTE_REVENUE].REVENUEDATE,
[CTE_REVENUE].REVENUESPLITAMOUNT as [REVENUESPLITAMOUNT],
[CTE_REVENUE].REVENUEDATEADDED
from [CTE_REVENUE]
inner join dbo.CONSTITUENT on CONSTITUENT.ID = [CTE_REVENUE].REVENUECONSTITUENTID
where CONSTITUENT.ID not in (select ID from dbo.GROUPDATA)
and CONSTITUENT.ID not in (select CONSTITUENTID from [CTE_MEMBERS])
and @GROUPID is null
if @INCLUDEGROUPSWITHNOREVENUE = 1
insert into @R
select
CONSTITUENT.ID as [GROUPID],
GROUPDATA.GROUPTYPECODE,
CONSTITUENT.ID as [GROUPMEMBERID],
1 as [ISGROUP],
null as [GROUPMEMBERSHIPSTARTDATE],
null as [GROUPMEMBERSHIPENDDATE],
0 as [GROUPMEMBERSHIPISHISTORICAL],
null as [REVENUEID],
null as [REVENUESPLITID],
null as [REVENUEDATE],
null as [REVENUESPLITAMOUNT],
null as [REVENUEDATEADDED]
from dbo.CONSTITUENT
inner join dbo.GROUPDATA on GROUPDATA.ID = CONSTITUENT.ID
left join @R as [RESULT] on [RESULT].GROUPID = CONSTITUENT.ID
where [RESULT].GROUPID is null and (CONSTITUENT.ID = @GROUPID or @GROUPID is null)
return;
end