USP_CONSTITUENTRECOGNITIONPROCESS_PROCESSPROGRAM
Processes revenue for the supplied recognition program.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @PROGRAMID | uniqueidentifier | IN | |
| @PROCESSDATE | datetime | IN | |
| @CHANGEAGENTID | uniqueidentifier | IN | |
| @INITIALSTATUSCODE | tinyint | IN | |
| @NUMPROCESSED | int | INOUT |
Definition
Copy
CREATE procedure dbo.USP_CONSTITUENTRECOGNITIONPROCESS_PROCESSPROGRAM
(
@PROGRAMID uniqueidentifier,
@PROCESSDATE datetime = null,
@CHANGEAGENTID uniqueidentifier = null,
@INITIALSTATUSCODE tinyint = null,
@NUMPROCESSED int = 0 output
) with execute as owner
as begin
set nocount on;
declare @STARTDATE datetime;
declare @RECOGNITIONTYPE tinyint;
declare @PLANNEDGIFTCODE tinyint;
declare @EXPIRESONCODE tinyint;
declare @LASTRUNON datetime;
declare @MAXAMOUNT money;
declare @GIFTTYPESFILTER xml;
declare @REVENUESELECTIONID uniqueidentifier;
declare @BASECURRENCYID uniqueidentifier;
declare @ORGANIZATIONCURRENCYID uniqueidentifier;
set @ORGANIZATIONCURRENCYID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();
declare @INCLUDECREDITWITHOUTTYPE tinyint;
declare @ALLOWGROUP bit;
declare @ALLOWHOUSEHOLD bit;
declare @ALLOWORGANIZATION bit;
declare @ALLOWINDIVIDUAL bit;
--Recognition Programs May Use Gross Amount when UK product flag is unlocked
declare @USEGROSSAMOUNT bit;
--Gross Amount Functionality is only applicable in a UK Product flag unlocked environment
declare @ISUK bit;
set @ISUK=dbo.UFN_INSTALLEDPRODUCTS_PRODUCTIS('9568A6C2-F7AA-45fd-8F54-21FE9654EE2D');
if @PROCESSDATE is not null
set @PROCESSDATE = dbo.UFN_DATE_GETLATESTTIME(@PROCESSDATE);
declare @CURRENTDATE datetime;
set @CURRENTDATE = GETDATE();
if @INITIALSTATUSCODE is null
set @INITIALSTATUSCODE = 0;
select
@STARTDATE = dbo.UFN_DATE_GETEARLIESTTIME(STARTDATE),
@RECOGNITIONTYPE = TYPECODE,
@PLANNEDGIFTCODE = PLANNEDGIFTCODE,
@EXPIRESONCODE = EXPIRESONCODE,
@LASTRUNON = LASTPROCESSEDON,
@MAXAMOUNT = MAXIMUMAMOUNT,
@REVENUESELECTIONID = SELECTIONID,
@BASECURRENCYID = BASECURRENCYID,
@INCLUDECREDITWITHOUTTYPE = INCLUDECREDITWITHOUTTYPE,
@GIFTTYPESFILTER = GIFTTYPESFILTER,
@ALLOWGROUP = ALLOWGROUP,
@ALLOWHOUSEHOLD = ALLOWHOUSEHOLD,
@ALLOWORGANIZATION = ALLOWORGANIZATION,
@ALLOWINDIVIDUAL = ALLOWINDIVIDUAL,
@USEGROSSAMOUNT = USEGROSSAMOUNT
from
dbo.RECOGNITIONPROGRAM
where
ID = @PROGRAMID;
--Work out whether this Calculation should use Gross or Net
declare @ISGROSS bit = 0; --defaults as net
if (@ISUK=1 and @USEGROSSAMOUNT=1)
set @ISGROSS = 1;
if object_id('tempdb..#RECPROCESS_GIFTTYPESFILTERVALUE') is not null
drop table #RECPROCESS_GIFTTYPESFILTERVALUE;
create table #RECPROCESS_GIFTTYPESFILTERVALUE
(
TYPECODEID tinyint
);
if @GIFTTYPESFILTER is not null
insert into #RECPROCESS_GIFTTYPESFILTERVALUE(TYPECODEID)
select T.c.value('(TYPECODEID)[1]','tinyint') TYPECODEID
from @GIFTTYPESFILTER.nodes('/GIFTTYPESFILTER/ITEM') T(c);
-- Make a temporary table with the designations allowed
if object_id('tempdb..#RECPROCESS_DESIGNATIONSALLOWED') is not null
drop table #RECPROCESS_DESIGNATIONSALLOWED;
create table #RECPROCESS_DESIGNATIONSALLOWED
(
ID uniqueidentifier
);
insert into #RECPROCESS_DESIGNATIONSALLOWED(ID)
select ID
from dbo.UFN_RECOGNITIONPROGRAM_GETDESIGNATIONS_ALL(@PROGRAMID);
if @CHANGEAGENTID is null
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;
-- Setup the currency to be used
declare @CURRENCYID uniqueidentifier;
declare @CURRENCYDECIMALDIGITS tinyint;
declare @CURRENCYROUNDINGTYPECODE tinyint;
if @BASECURRENCYID is null
select
@CURRENCYID = CURRENCY.ID,
@CURRENCYDECIMALDIGITS = CURRENCY.DECIMALDIGITS,
@CURRENCYROUNDINGTYPECODE = CURRENCY.ROUNDINGTYPECODE
from
dbo.CURRENCY
where
ISORGANIZATIONCURRENCY = 1;
else
select
@CURRENCYID = CURRENCY.ID,
@CURRENCYDECIMALDIGITS = CURRENCY.DECIMALDIGITS,
@CURRENCYROUNDINGTYPECODE = CURRENCY.ROUNDINGTYPECODE
from
dbo.CURRENCY
where
CURRENCY.ID = @BASECURRENCYID;
--first update any existing changed recognition
declare @UPDATECONSTITUENTRECOGNITIONREVENUESQL nvarchar(max) = N'
update
dbo.CONSTITUENTRECOGNITIONREVENUE
set
AMOUNT = RREC_INCURRENCY.AMOUNT,
ORGANIZATIONAMOUNT = RREC_INCURRENCY.ORGANIZATIONAMOUNT,
ORGANIZATIONEXCHANGERATEID = RREC_INCURRENCY.ORGANIZATIONEXCHANGERATEID,
EFFECTIVEDATE = RREC_INCURRENCY.EFFECTIVEDATE,
DATECHANGED = @CURRENTDATE,
CHANGEDBYID = @CHANGEAGENTID
from
(
select
CONSTITUENTRECOGNITIONREVENUE.ID as CONSTITUENTRECOGNITIONREVENUEID,
RREC.AMOUNTINCURRENCY as AMOUNT,
REVENUERECOGNITION.EFFECTIVEDATE,
RREC.ORGANIZATIONEXCHANGERATEID,
REVENUERECOGNITION.ORGANIZATIONAMOUNT
from
dbo.CONSTITUENTRECOGNITIONREVENUE
inner join dbo.REVENUERECOGNITION on REVENUERECOGNITION.ID = CONSTITUENTRECOGNITIONREVENUE.REVENUERECOGNITIONID
inner join '
--Determine which function to use depending on whether gross or net figures have to be used.
if @ISGROSS=1
set @UPDATECONSTITUENTRECOGNITIONREVENUESQL = @UPDATECONSTITUENTRECOGNITIONREVENUESQL + N'dbo.UFN_REVENUERECOGNITIONWITHGIFTAID_GETAMOUNTSINCURRENCY_BULK(@CURRENCYID,@ORGANIZATIONCURRENCYID,@CURRENCYDECIMALDIGITS,@CURRENCYROUNDINGTYPECODE,@ISUK)';
else
set @UPDATECONSTITUENTRECOGNITIONREVENUESQL = @UPDATECONSTITUENTRECOGNITIONREVENUESQL + N'dbo.UFN_REVENUERECOGNITION_GETAMOUNTINCURRENCY_BULK(@CURRENCYID,@ORGANIZATIONCURRENCYID,@CURRENCYDECIMALDIGITS,@CURRENCYROUNDINGTYPECODE)';
set @UPDATECONSTITUENTRECOGNITIONREVENUESQL = @UPDATECONSTITUENTRECOGNITIONREVENUESQL + N' RREC on (RREC.ID = CONSTITUENTRECOGNITIONREVENUE.REVENUERECOGNITIONID)
where
CONSTITUENTRECOGNITIONREVENUE.RECOGNITIONPROGRAMID = @PROGRAMID ';
if @LASTRUNON is not null
--For a gross program date changed is determined from more than the revenue change amounts eg. declaration changes.
--This means datechanged is pulled differently.
if @ISGROSS=1
set @UPDATECONSTITUENTRECOGNITIONREVENUESQL = @UPDATECONSTITUENTRECOGNITIONREVENUESQL + N'
and RREC.DATEGROSSAMOUNTCHANGED > @LASTRUNON ';
else
set @UPDATECONSTITUENTRECOGNITIONREVENUESQL = @UPDATECONSTITUENTRECOGNITIONREVENUESQL + N'
and REVENUERECOGNITION.DATECHANGED > @LASTRUNON ';
if @PROCESSDATE is not null
set @UPDATECONSTITUENTRECOGNITIONREVENUESQL = @UPDATECONSTITUENTRECOGNITIONREVENUESQL + N'
and REVENUERECOGNITION.EFFECTIVEDATE <= @PROCESSDATE ';
set @UPDATECONSTITUENTRECOGNITIONREVENUESQL = @UPDATECONSTITUENTRECOGNITIONREVENUESQL + N'
union all
select
CONSTITUENTRECOGNITIONREVENUE.ID as CONSTITUENTRECOGNITIONREVENUEID,
RREC.AMOUNTINCURRENCY as AMOUNT,
RECOGNITIONCREDIT.EFFECTIVEDATE,
RREC.ORGANIZATIONEXCHANGERATEID,
RECOGNITIONCREDIT.ORGANIZATIONAMOUNT
from
dbo.CONSTITUENTRECOGNITIONREVENUE
inner join dbo.RECOGNITIONCREDIT on RECOGNITIONCREDIT.ID = CONSTITUENTRECOGNITIONREVENUE.RECOGNITIONCREDITID
inner join dbo.UFN_RECOGNITIONCREDIT_GETAMOUNTINCURRENCY_BULK(@CURRENCYID,@ORGANIZATIONCURRENCYID,@CURRENCYDECIMALDIGITS,@CURRENCYROUNDINGTYPECODE) RREC on (RREC.ID = CONSTITUENTRECOGNITIONREVENUE.RECOGNITIONCREDITID)
where
CONSTITUENTRECOGNITIONREVENUE.RECOGNITIONPROGRAMID = @PROGRAMID ';
if @LASTRUNON is not null
set @UPDATECONSTITUENTRECOGNITIONREVENUESQL = @UPDATECONSTITUENTRECOGNITIONREVENUESQL + N'
and RECOGNITIONCREDIT.DATECHANGED > @LASTRUNON ';
if @PROCESSDATE is not null
set @UPDATECONSTITUENTRECOGNITIONREVENUESQL = @UPDATECONSTITUENTRECOGNITIONREVENUESQL + N'
and RECOGNITIONCREDIT.EFFECTIVEDATE <= @PROCESSDATE ';
set @UPDATECONSTITUENTRECOGNITIONREVENUESQL = @UPDATECONSTITUENTRECOGNITIONREVENUESQL + N'
) RREC_INCURRENCY
where
CONSTITUENTRECOGNITIONREVENUE.ID = RREC_INCURRENCY.CONSTITUENTRECOGNITIONREVENUEID and
(RREC_INCURRENCY.AMOUNT <> CONSTITUENTRECOGNITIONREVENUE.AMOUNT or RREC_INCURRENCY.EFFECTIVEDATE <> CONSTITUENTRECOGNITIONREVENUE.EFFECTIVEDATE);'
exec sp_executesql @UPDATECONSTITUENTRECOGNITIONREVENUESQL, N'@CHANGEAGENTID uniqueidentifier, @CURRENTDATE datetime, @ORGANIZATIONCURRENCYID uniqueidentifier, @PROGRAMID uniqueidentifier, @LASTRUNON datetime, @PROCESSDATE datetime,
@CURRENCYID uniqueidentifier, @CURRENCYDECIMALDIGITS tinyint, @CURRENCYROUNDINGTYPECODE tinyint, @ISUK bit',
@CHANGEAGENTID=@CHANGEAGENTID, @CURRENTDATE=@CURRENTDATE, @ORGANIZATIONCURRENCYID=@ORGANIZATIONCURRENCYID, @PROGRAMID=@PROGRAMID, @LASTRUNON=@LASTRUNON, @PROCESSDATE=@PROCESSDATE,
@CURRENCYID=@CURRENCYID, @CURRENCYDECIMALDIGITS=@CURRENCYDECIMALDIGITS, @CURRENCYROUNDINGTYPECODE=@CURRENCYROUNDINGTYPECODE, @ISUK=@ISUK;
declare @INCLUDEDONORCHALLENGECLAIM bit = coalesce((select 1 from #RECPROCESS_GIFTTYPESFILTERVALUE where TYPECODEID = 25), 0)
if object_id('tempdb..#FILTERRECPROCESS_REVENUERECOGNITIONXMLTOBESAVED') is not null
drop table #FILTERRECPROCESS_REVENUERECOGNITIONXMLTOBESAVED;
create table #FILTERRECPROCESS_REVENUERECOGNITIONXMLTOBESAVED
(
ID uniqueidentifier
);
insert into #FILTERRECPROCESS_REVENUERECOGNITIONXMLTOBESAVED(ID)
select
ID from REVENUERECOGNITION where CONSTITUENTID in (
select
CONSTITUENTID from REVENUERECOGNITION group by CONSTITUENTID
having SUM(AMOUNT) >=(select min(AMOUNT) from dbo.RECOGNITIONLEVEL where RECOGNITIONPROGRAMID = @PROGRAMID))
-- Get applicable REVENUERECOGNITION records into temp table to prevent slow performance.
-- Bulk currency function needs a simple join to be able to filter records before performing selects on many rows.
if object_id('tempdb..#RECPROCESS_REVENUERECOGNITION') is not null
drop table #RECPROCESS_REVENUERECOGNITION;
create table #RECPROCESS_REVENUERECOGNITION
(
ID uniqueidentifier
);
declare @REVENUERECOGNITIONSQL nvarchar(max) = N'
insert into #RECPROCESS_REVENUERECOGNITION(ID)
select
REVENUERECOGNITION.ID
from
dbo.REVENUERECOGNITION
inner join dbo.FINANCIALTRANSACTIONLINEITEM on REVENUERECOGNITION.REVENUESPLITID = FINANCIALTRANSACTIONLINEITEM.ID
inner join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = FINANCIALTRANSACTION.ID
inner join dbo.REVENUESPLIT_EXT on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID
inner join #FILTERRECPROCESS_REVENUERECOGNITIONXMLTOBESAVED FRR on REVENUERECOGNITION.ID = FRR.ID '
--If Selection set then inner join to list to filter
if @REVENUESELECTIONID is not null
begin
declare @FILTERTABLE nvarchar(255);
select @FILTERTABLE = 'dbo.' + IDSETREGISTER.DBOBJECTNAME + case IDSETREGISTER.OBJECTTYPE when 1 then '()' else '' end
from IDSETREGISTER
where IDSETREGISTER.ID = @REVENUESELECTIONID;
if object_id('tempdb..#RECPROCESS_IDSET') is not null
drop table #RECPROCESS_IDSET;
create table #RECPROCESS_IDSET
(
ID uniqueidentifier
);
declare @FILTERSQL nvarchar(max) = N'insert into #RECPROCESS_IDSET(ID) select ID from ' + @FILTERTABLE;
exec sp_executesql @FILTERSQL;
set @REVENUERECOGNITIONSQL = @REVENUERECOGNITIONSQL + N'
inner join #RECPROCESS_IDSET IDSET on IDSET.ID = FINANCIALTRANSACTION.ID ';
end
--If designation filters set then inner join to list to filter
if exists(
select
ID
from
#RECPROCESS_DESIGNATIONSALLOWED
)
set @REVENUERECOGNITIONSQL = @REVENUERECOGNITIONSQL + N'
inner join dbo.#RECPROCESS_DESIGNATIONSALLOWED DES on REVENUESPLIT_EXT.DESIGNATIONID = DES.ID ';
if @ALLOWGROUP = 1 or @ALLOWHOUSEHOLD = 1 or @ALLOWORGANIZATION = 1 or @ALLOWINDIVIDUAL = 1
begin
set @REVENUERECOGNITIONSQL = @REVENUERECOGNITIONSQL + N'
inner join dbo.CONSTITUENT on CONSTITUENT.ID = REVENUERECOGNITION.CONSTITUENTID ';
if @ALLOWGROUP = 1 or @ALLOWHOUSEHOLD = 1
set @REVENUERECOGNITIONSQL = @REVENUERECOGNITIONSQL + N'
left join dbo.GROUPDATA on GROUPDATA.ID = CONSTITUENT.ID ';
end
set @REVENUERECOGNITIONSQL = @REVENUERECOGNITIONSQL + N'
where
not exists
(
select
ID
from
dbo.CONSTITUENTRECOGNITIONREVENUE C2
where
C2.REVENUERECOGNITIONID = REVENUERECOGNITION.ID
and C2.RECOGNITIONPROGRAMID = @PROGRAMID
)
and
( ';
if @INCLUDECREDITWITHOUTTYPE = 1
set @REVENUERECOGNITIONSQL = @REVENUERECOGNITIONSQL + N'
REVENUERECOGNITION.REVENUERECOGNITIONTYPECODEID is null
or ';
set @REVENUERECOGNITIONSQL = @REVENUERECOGNITIONSQL + N'
exists(
select
1
from
dbo.RECOGNITIONPROGRAMCREDITTYPE
where
RECOGNITIONPROGRAMID = @PROGRAMID and
REVENUERECOGNITIONTYPECODEID = REVENUERECOGNITION.REVENUERECOGNITIONTYPECODEID)
) ';
if @STARTDATE is not null
set @REVENUERECOGNITIONSQL = @REVENUERECOGNITIONSQL + N'
and REVENUERECOGNITION.EFFECTIVEDATE >= @STARTDATE ';
if @PROCESSDATE is not null
set @REVENUERECOGNITIONSQL = @REVENUERECOGNITIONSQL + N'
and REVENUERECOGNITION.EFFECTIVEDATE <= @PROCESSDATE ';
set @REVENUERECOGNITIONSQL = @REVENUERECOGNITIONSQL + N'
and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null
and FINANCIALTRANSACTIONLINEITEM.TYPECODE <> 1
and FINANCIALTRANSACTION.DELETEDON is null ';
set @REVENUERECOGNITIONSQL = @REVENUERECOGNITIONSQL + N'
and
(
exists
(
select
GTF.TYPECODEID
from
#RECPROCESS_GIFTTYPESFILTERVALUE GTF
where
(FINANCIALTRANSACTION.TYPECODE = 0 and GTF.TYPECODEID = REVENUESPLIT_EXT.APPLICATIONCODE) or -- Payment
(GTF.TYPECODEID = 21 and FINANCIALTRANSACTION.TYPECODE = 1) or --Pledge
(GTF.TYPECODEID = 22 and FINANCIALTRANSACTION.TYPECODE = 2) or --Recurring Gift
(GTF.TYPECODEID = 23 and FINANCIALTRANSACTION.TYPECODE = 3) or -- Matching Gift Claim
(GTF.TYPECODEID = 24 and FINANCIALTRANSACTION.TYPECODE = 7) or -- Auction donations
(GTF.TYPECODEID = 25 and FINANCIALTRANSACTION.TYPECODE = 8) or -- Donor challenge claim
(GTF.TYPECODEID = 26 and FINANCIALTRANSACTION.TYPECODE = 15) -- Membership installment plan
)';
if @PLANNEDGIFTCODE <> 0
set @REVENUERECOGNITIONSQL = @REVENUERECOGNITIONSQL + '
or
(FINANCIALTRANSACTION.TYPECODE = 4) -- Planned Gift, not separate '
set @REVENUERECOGNITIONSQL = @REVENUERECOGNITIONSQL + '
)';
if @ALLOWGROUP = 1 or @ALLOWHOUSEHOLD = 1 or @ALLOWORGANIZATION = 1 or @ALLOWINDIVIDUAL = 1
begin
set @REVENUERECOGNITIONSQL = @REVENUERECOGNITIONSQL + N'
and
(';
if @ALLOWGROUP = 1
begin
if @ALLOWHOUSEHOLD = 0
set @REVENUERECOGNITIONSQL = @REVENUERECOGNITIONSQL + N'
CONSTITUENT.ISGROUP = 1 and coalesce(GROUPDATA.GROUPTYPECODE, -1) = 1 ';
else
set @REVENUERECOGNITIONSQL = @REVENUERECOGNITIONSQL + N'
CONSTITUENT.ISGROUP = 1 ';
end
if @ALLOWHOUSEHOLD = 1
begin
if @ALLOWGROUP = 1
set @REVENUERECOGNITIONSQL = @REVENUERECOGNITIONSQL + N'
or ';
set @REVENUERECOGNITIONSQL = @REVENUERECOGNITIONSQL + N'
coalesce(GROUPDATA.GROUPTYPECODE, -1) = 0 ';
end
if @ALLOWINDIVIDUAL = 1
begin
if @ALLOWGROUP = 1 or @ALLOWHOUSEHOLD = 1
set @REVENUERECOGNITIONSQL = @REVENUERECOGNITIONSQL + N'
or ';
set @REVENUERECOGNITIONSQL = @REVENUERECOGNITIONSQL + N'
CONSTITUENT.ISGROUP = 0 and CONSTITUENT.ISORGANIZATION = 0 ';
end
if @ALLOWORGANIZATION = 1
begin
if @ALLOWGROUP = 1 or @ALLOWHOUSEHOLD = 1 or @ALLOWINDIVIDUAL = 1
set @REVENUERECOGNITIONSQL = @REVENUERECOGNITIONSQL + N'
or ';
set @REVENUERECOGNITIONSQL = @REVENUERECOGNITIONSQL + N'
CONSTITUENT.ISORGANIZATION = 1';
end
set @REVENUERECOGNITIONSQL = @REVENUERECOGNITIONSQL + N'
) ';
end
exec sp_executesql @REVENUERECOGNITIONSQL, N'@PROGRAMID uniqueidentifier, @STARTDATE datetime, @PROCESSDATE datetime',
@PROGRAMID=@PROGRAMID,@STARTDATE=@STARTDATE,@PROCESSDATE=@PROCESSDATE;
--second, insert any new recognition
declare @INSERTCONSTITUENTRECOGNITIONREVENUESQL nvarchar(max) = N'
insert into dbo.CONSTITUENTRECOGNITIONREVENUE (
ID,
AMOUNT,
ORGANIZATIONAMOUNT,
ORGANIZATIONEXCHANGERATEID,
EFFECTIVEDATE,
REVENUERECOGNITIONID,
RECOGNITIONPROGRAMID,
ISPLANNEDGIFT,
BASECURRENCYID,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED,
RECOGNITIONCREDITID
)
select
newid(),
RREC_INCURRENCY.AMOUNT,
RREC_INCURRENCY.ORGANIZATIONAMOUNT,
RREC_INCURRENCY.ORGANIZATIONEXCHANGERATEID,
RREC_INCURRENCY.EFFECTIVEDATE,
case when RREC_INCURRENCY.RECOGNITIONCREDITTYPECODE < 0
then RREC_INCURRENCY.ID
else null
end as REVENUERECOGNITIONID,
@PROGRAMID,
RREC_INCURRENCY.ISPLANNEDGIFT,
@BASECURRENCYID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE,
case when RREC_INCURRENCY.RECOGNITIONCREDITTYPECODE >= 0
then RREC_INCURRENCY.ID
else null
end as RECOGNITIONCREDITID
from
(select
RREC.ID,
RREC.AMOUNTINCURRENCY as AMOUNT,
RREC.EFFECTIVEDATE,
case when RREC.TRANSACTIONTYPECODE = 4 then 1 else 0 end ISPLANNEDGIFT,
RREC.ORGANIZATIONEXCHANGERATEIDINCURRENCY as ORGANIZATIONEXCHANGERATEID,
RREC.RECOGNITIONCREDITTYPECODE as RECOGNITIONCREDITTYPECODE,
RREC.ORGANIZATIONAMOUNT
from
(select
RREC.ID,
RREC.AMOUNTINCURRENCY,
RREC.EFFECTIVEDATE,
RREC.TRANSACTIONTYPECODE,
RREC.ORGANIZATIONEXCHANGERATEIDINCURRENCY,
RREC.RECOGNITIONCREDITTYPECODE,
RREC.ORGANIZATIONAMOUNT
from '
--NOTE: The SQL below for RREC is taken from UFN_RECOGNITIONCREDIT_GETRECOGNITIONS. Any changes made in either place may have to be made in the other.
set @INSERTCONSTITUENTRECOGNITIONREVENUESQL = @INSERTCONSTITUENTRECOGNITIONREVENUESQL + N'
(
select
RR_INCURRENCY.ID,
RR_INCURRENCY.REVENUESPLITID,
RR_INCURRENCY.EFFECTIVEDATE,
RR_INCURRENCY.ORGANIZATIONAMOUNT,
RR_INCURRENCY.REVENUERECOGNITIONTYPECODEID,
RR_INCURRENCY.CONSTITUENTID,
-1 as RECOGNITIONCREDITTYPECODE,
RR_INCURRENCY.AMOUNTINCURRENCY,
RR_INCURRENCY.TRANSACTIONTYPECODE,
RR_INCURRENCY.DESIGNATIONID,
RR_INCURRENCY.ORGANIZATIONEXCHANGERATEID as ORGANIZATIONEXCHANGERATEIDINCURRENCY
from
#RECPROCESS_REVENUERECOGNITION as REVENUERECOGNITION
inner join '
--Determine which function to use depending on whether gross or net figures have to be used.
if @ISGROSS=1
set @INSERTCONSTITUENTRECOGNITIONREVENUESQL = @INSERTCONSTITUENTRECOGNITIONREVENUESQL + N'dbo.UFN_REVENUERECOGNITIONWITHGIFTAID_GETAMOUNTSINCURRENCY_BULK(@CURRENCYID,@ORGANIZATIONCURRENCYID,@CURRENCYDECIMALDIGITS,@CURRENCYROUNDINGTYPECODE,@ISUK)';
else
set @INSERTCONSTITUENTRECOGNITIONREVENUESQL = @INSERTCONSTITUENTRECOGNITIONREVENUESQL + N'dbo.UFN_REVENUERECOGNITION_GETAMOUNTINCURRENCY_BULK(@CURRENCYID,@ORGANIZATIONCURRENCYID,@CURRENCYDECIMALDIGITS,@CURRENCYROUNDINGTYPECODE)';
set @INSERTCONSTITUENTRECOGNITIONREVENUESQL = @INSERTCONSTITUENTRECOGNITIONREVENUESQL + N' as RR_INCURRENCY on REVENUERECOGNITION.ID = RR_INCURRENCY.ID ';
set @INSERTCONSTITUENTRECOGNITIONREVENUESQL = @INSERTCONSTITUENTRECOGNITIONREVENUESQL + N'
) RREC ';
--In the case that we are including donor challenge claims, we need to also include recognition credits when donor challenge uses an internal sponsor
if @INCLUDEDONORCHALLENGECLAIM = 1
begin
-- Get applicable RECOGNITIONCREDIT records into temp table to prevent slow performance.
-- Bulk currency function needs a simple join to be able to filter records before performing selects on many rows.
if object_id('tempdb..#RECPROCESS_RECOGNITIONCREDIT') is not null
drop table #RECPROCESS_RECOGNITIONCREDIT;
create table #RECPROCESS_RECOGNITIONCREDIT
(
ID uniqueidentifier
);
declare @RECOGNITIONCREDITSQL nvarchar(max) = N'
insert into #RECPROCESS_RECOGNITIONCREDIT(ID)
select
RECOGNITIONCREDIT.ID
from
dbo.RECOGNITIONCREDIT
inner join dbo.DONORCHALLENGEENCUMBERED on RECOGNITIONCREDIT.DONORCHALLENGEENCUMBEREDID = DONORCHALLENGEENCUMBERED.ID
inner join dbo.FINANCIALTRANSACTIONLINEITEM on DONORCHALLENGEENCUMBERED.REVENUESPLITID = FINANCIALTRANSACTIONLINEITEM.ID
inner join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = FINANCIALTRANSACTION.ID
inner join dbo.REVENUESPLIT_EXT on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID ';
--If Selection set then inner join to list to filter
if @REVENUESELECTIONID is not null
begin
--temp table was filled in previously
set @RECOGNITIONCREDITSQL = @RECOGNITIONCREDITSQL + N'
inner join #RECPROCESS_IDSET IDSET on IDSET.ID = FINANCIALTRANSACTION.ID ';
end
if @ALLOWGROUP = 1 or @ALLOWHOUSEHOLD = 1 or @ALLOWORGANIZATION = 1 or @ALLOWINDIVIDUAL = 1
begin
set @RECOGNITIONCREDITSQL = @RECOGNITIONCREDITSQL + N'
inner join dbo.CONSTITUENT on CONSTITUENT.ID = RECOGNITIONCREDIT.CONSTITUENTID ';
if @ALLOWGROUP = 1 or @ALLOWHOUSEHOLD = 1
set @RECOGNITIONCREDITSQL = @RECOGNITIONCREDITSQL + N'
left join dbo.GROUPDATA on GROUPDATA.ID = CONSTITUENT.ID ';
end
--If designation filters set then inner join to list to filter
if exists(
select
ID
from
#RECPROCESS_DESIGNATIONSALLOWED
)
set @RECOGNITIONCREDITSQL = @RECOGNITIONCREDITSQL + '
inner join dbo.#RECPROCESS_DESIGNATIONSALLOWED DES on RECOGNITIONCREDIT.DESIGNATIONID = DES.ID '
set @RECOGNITIONCREDITSQL = @RECOGNITIONCREDITSQL + N'
where
RECOGNITIONCREDIT.RECOGNITIONCREDITTYPECODE = 1
and not exists
(
select
ID
from
dbo.CONSTITUENTRECOGNITIONREVENUE C2
where
C2.RECOGNITIONCREDITID = RECOGNITIONCREDIT.ID
and C2.RECOGNITIONPROGRAMID = @PROGRAMID
)
and
( '
if @INCLUDECREDITWITHOUTTYPE = 1
set @RECOGNITIONCREDITSQL = @RECOGNITIONCREDITSQL + N'
RECOGNITIONCREDIT.USERRECOGNITIONTYPECODEID is null
or ';
set @RECOGNITIONCREDITSQL = @RECOGNITIONCREDITSQL + N'
exists(
select
1
from
dbo.RECOGNITIONPROGRAMCREDITTYPE
where
RECOGNITIONPROGRAMID = @PROGRAMID and
REVENUERECOGNITIONTYPECODEID = RECOGNITIONCREDIT.USERRECOGNITIONTYPECODEID)
) ';
if @STARTDATE is not null
set @RECOGNITIONCREDITSQL = @RECOGNITIONCREDITSQL + N'
and RECOGNITIONCREDIT.EFFECTIVEDATE >= @STARTDATE ';
if @PROCESSDATE is not null
set @RECOGNITIONCREDITSQL = @RECOGNITIONCREDITSQL + N'
and RECOGNITIONCREDIT.EFFECTIVEDATE <= @PROCESSDATE ';
set @RECOGNITIONCREDITSQL = @RECOGNITIONCREDITSQL + N'
and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null
and FINANCIALTRANSACTIONLINEITEM.TYPECODE <> 1
and FINANCIALTRANSACTION.DELETEDON is null ';
set @RECOGNITIONCREDITSQL = @RECOGNITIONCREDITSQL + N'
and (
exists(
select
GTF.TYPECODEID
from
#RECPROCESS_GIFTTYPESFILTERVALUE GTF
where
(FINANCIALTRANSACTION.TYPECODE = 0 and GTF.TYPECODEID = REVENUESPLIT_EXT.APPLICATIONCODE) or -- Payment
(GTF.TYPECODEID = 21 and FINANCIALTRANSACTION.TYPECODE = 1) or --Pledge
(GTF.TYPECODEID = 22 and FINANCIALTRANSACTION.TYPECODE = 2) or --Recurring Gift
(GTF.TYPECODEID = 23 and FINANCIALTRANSACTION.TYPECODE = 3) or -- Matching Gift Claim
(GTF.TYPECODEID = 24 and FINANCIALTRANSACTION.TYPECODE = 7) or -- Auction donations
(GTF.TYPECODEID = 25 and FINANCIALTRANSACTION.TYPECODE = 8) or -- Donor challenge claim
(GTF.TYPECODEID = 26 and FINANCIALTRANSACTION.TYPECODE = 15) -- Membership installment plan
)';
if @PLANNEDGIFTCODE <> 0
set @RECOGNITIONCREDITSQL = @RECOGNITIONCREDITSQL + '
or
(FINANCIALTRANSACTION.TYPECODE = 4) -- Planned Gift, not separate '
set @RECOGNITIONCREDITSQL = @RECOGNITIONCREDITSQL + '
)';
if @ALLOWGROUP = 1 or @ALLOWHOUSEHOLD = 1 or @ALLOWORGANIZATION = 1 or @ALLOWINDIVIDUAL = 1
begin
set @RECOGNITIONCREDITSQL = @RECOGNITIONCREDITSQL + N'
and
(';
if @ALLOWGROUP = 1
begin
if @ALLOWHOUSEHOLD = 0
set @RECOGNITIONCREDITSQL = @RECOGNITIONCREDITSQL + N'
CONSTITUENT.ISGROUP = 1 and coalesce(GROUPDATA.GROUPTYPECODE, -1) = 1 ';
else
set @RECOGNITIONCREDITSQL = @RECOGNITIONCREDITSQL + N'
CONSTITUENT.ISGROUP = 1 ';
end
if @ALLOWHOUSEHOLD = 1
begin
if @ALLOWGROUP = 1
set @RECOGNITIONCREDITSQL = @RECOGNITIONCREDITSQL + N'
or ';
set @RECOGNITIONCREDITSQL = @RECOGNITIONCREDITSQL + N'
coalesce(GROUPDATA.GROUPTYPECODE, -1) = 0 ';
end
if @ALLOWINDIVIDUAL = 1
begin
if @ALLOWGROUP = 1 or @ALLOWHOUSEHOLD = 1
set @RECOGNITIONCREDITSQL = @RECOGNITIONCREDITSQL + N'
or ';
set @RECOGNITIONCREDITSQL = @RECOGNITIONCREDITSQL + N'
CONSTITUENT.ISGROUP = 0 and CONSTITUENT.ISORGANIZATION = 0 ';
end
if @ALLOWORGANIZATION = 1
begin
if @ALLOWGROUP = 1 or @ALLOWHOUSEHOLD = 1 or @ALLOWINDIVIDUAL = 1
set @RECOGNITIONCREDITSQL = @RECOGNITIONCREDITSQL + N'
or ';
set @RECOGNITIONCREDITSQL = @RECOGNITIONCREDITSQL + N'
CONSTITUENT.ISORGANIZATION = 1';
end
set @RECOGNITIONCREDITSQL = @RECOGNITIONCREDITSQL + N'
) ';
end
exec sp_executesql @RECOGNITIONCREDITSQL, N'@PROGRAMID uniqueidentifier, @STARTDATE datetime, @PROCESSDATE datetime',
@PROGRAMID=@PROGRAMID,@STARTDATE=@STARTDATE,@PROCESSDATE=@PROCESSDATE;
set @INSERTCONSTITUENTRECOGNITIONREVENUESQL = @INSERTCONSTITUENTRECOGNITIONREVENUESQL + '
union all
select RREC.ID,
RREC.AMOUNTINCURRENCY,
RREC.EFFECTIVEDATE,
RREC.TRANSACTIONTYPECODE,
RREC.ORGANIZATIONEXCHANGERATEIDINCURRENCY,
RREC.RECOGNITIONCREDITTYPECODE,
RREC.ORGANIZATIONAMOUNT
from '
--NOTE: The SQL below for RREC is taken from UFN_RECOGNITIONCREDIT_GETRECOGNITIONS. Any changes made in either place may have to be made in the other.
set @INSERTCONSTITUENTRECOGNITIONREVENUESQL = @INSERTCONSTITUENTRECOGNITIONREVENUESQL + N'
(
select
RECOGNITIONCREDIT_INCURRENCY.ID,
RECOGNITIONCREDIT_INCURRENCY.REVENUESPLITID, --This should be null but we still store some info on the revenue, REVENUECAMPAIGN for example
RECOGNITIONCREDIT_INCURRENCY.EFFECTIVEDATE,
RECOGNITIONCREDIT_INCURRENCY.ORGANIZATIONAMOUNT,
RECOGNITIONCREDIT_INCURRENCY.USERRECOGNITIONTYPECODEID as REVENUERECOGNITIONTYPECODEID,
RECOGNITIONCREDIT_INCURRENCY.CONSTITUENTID,
RECOGNITIONCREDIT_INCURRENCY.RECOGNITIONCREDITTYPECODE,
RECOGNITIONCREDIT_INCURRENCY.AMOUNTINCURRENCY,
8 as TRANSACTIONTYPECODE,
RECOGNITIONCREDIT_INCURRENCY.DESIGNATIONID,
RECOGNITIONCREDIT_INCURRENCY.ORGANIZATIONEXCHANGERATEID as ORGANIZATIONEXCHANGERATEIDINCURRENCY
from
#RECPROCESS_RECOGNITIONCREDIT as RECOGNITIONCREDIT
inner join dbo.UFN_RECOGNITIONCREDIT_GETAMOUNTINCURRENCY_BULK(@CURRENCYID,@ORGANIZATIONCURRENCYID,@CURRENCYDECIMALDIGITS,@CURRENCYROUNDINGTYPECODE) as RECOGNITIONCREDIT_INCURRENCY on RECOGNITIONCREDIT.ID = RECOGNITIONCREDIT_INCURRENCY.ID
) RREC '
end
set @INSERTCONSTITUENTRECOGNITIONREVENUESQL = @INSERTCONSTITUENTRECOGNITIONREVENUESQL + ') RREC
) RREC_INCURRENCY '
--Execute Insert
exec sp_executesql @INSERTCONSTITUENTRECOGNITIONREVENUESQL, N'@PROGRAMID uniqueidentifier,@CHANGEAGENTID uniqueidentifier,@CURRENTDATE datetime,@BASECURRENCYID uniqueidentifier,@ORGANIZATIONCURRENCYID uniqueidentifier,
@CURRENCYID uniqueidentifier, @CURRENCYDECIMALDIGITS tinyint, @CURRENCYROUNDINGTYPECODE tinyint, @ISUK bit',
@PROGRAMID=@PROGRAMID,@CHANGEAGENTID=@CHANGEAGENTID,@CURRENTDATE=@CURRENTDATE,@BASECURRENCYID=@BASECURRENCYID,@ORGANIZATIONCURRENCYID=@ORGANIZATIONCURRENCYID,@CURRENCYID=@CURRENCYID,@CURRENCYDECIMALDIGITS=@CURRENCYDECIMALDIGITS,@CURRENCYROUNDINGTYPECODE=@CURRENCYROUNDINGTYPECODE, @ISUK=@ISUK;
if object_id('tempdb..#RECPROCESS_GIFTTYPESFILTERVALUE') is not null
drop table #RECPROCESS_GIFTTYPESFILTERVALUE;
if object_id('tempdb..#RECPROCESS_DESIGNATIONSALLOWED') is not null
drop table #RECPROCESS_DESIGNATIONSALLOWED;
if object_id('tempdb..#RECPROCESS_REVENUERECOGNITION') is not null
drop table #RECPROCESS_REVENUERECOGNITION;
if object_id('tempdb..#RECPROCESS_IDSET') is not null
drop table #RECPROCESS_IDSET;
if object_id('tempdb..##RECPROCESS_RECOGNITIONCREDIT') is not null
drop table #RECPROCESS_RECOGNITIONCREDIT;
if object_id('tempdb..#FILTERRECPROCESS_REVENUERECOGNITIONXMLTOBESAVED') is not null
drop table #FILTERRECPROCESS_REVENUERECOGNITIONXMLTOBESAVED;
--get the bounds for the recognition levels and dates
declare @RECBOUNDS table
(
AMOUNT money,
PLANNEDGIFTAMOUNT money,
STARTDATE datetime,
ENDDATE datetime,
RECOGNITIONLEVELID uniqueidentifier
);
insert into @RECBOUNDS
select
RLA.AMOUNT,
RLA.PLANNEDGIFTAMOUNT,
coalesce(RLA.ACTIVEDATE, @STARTDATE, 0) as STARTDATE,
(
select min(ACTIVEDATE) from dbo.RECOGNITIONLEVELAMOUNT
where
ACTIVEDATE > coalesce(RLA.ACTIVEDATE, @STARTDATE, 0) and
RECOGNITIONLEVELID = RLA.RECOGNITIONLEVELID
) as ENDDATE,
RLA.RECOGNITIONLEVELID
from
dbo.RECOGNITIONLEVELAMOUNT RLA
inner join dbo.RECOGNITIONLEVEL RL on RLA.RECOGNITIONLEVELID = RL.ID
where
RL.RECOGNITIONPROGRAMID = @PROGRAMID
and RL.ISACTIVE = 1;
declare @CONSTITUENTID uniqueidentifier;
declare @AMOUNT money;
declare @EFFECTIVEDATE datetime;
declare @PREVCONSTITUENTID uniqueidentifier;
declare @TOTALAMOUNT money;
declare @PLANNEDGIFTTOTAL money;
declare @YEAREND datetime;
declare @PREVYEAREND datetime;
declare @ISPLANNEDGIFT bit;
declare @CURRENTRECLEVELID uniqueidentifier;
declare @JOINDATE datetime;
declare @CURRENTRECID uniqueidentifier;
declare @CURRENTTOTAL money;
declare @CURRENTPLANNEDGIFTTOTAL money;
set @TOTALAMOUNT = 0;
set @PLANNEDGIFTTOTAL = 0;
declare @TOPRECOGNITIONLEVEL table
(
CONSTITUENTID uniqueidentifier,
TOPRECOGNITIONLEVELID uniqueidentifier,
TOTALAMOUNT money,
PLANNEDGIFTTOTAL money,
EFFECTIVEDATE datetime,
JOINDATE datetime,
CURRENTTOTAL money,
CURRENTPLANNEDGIFTTOTAL money,
ORGANIZATIONEXCHANGERATEID uniqueidentifier,
ORGANIZATIONTOTALAMOUNT money,
ORGANIZATIONTOTALPLANNEDGIFTAMOUNT money,
YEAREND datetime
);
--process records for rec
if @MAXAMOUNT = 0
begin
declare CONSTITS_CURSOR cursor local fast_forward for
with CONSTITUENT_CTE as (
select distinct
RR_INCURRENCY.CONSTITUENTID
from
dbo.CONSTITUENTRECOGNITIONREVENUE CRR
left join dbo.UFN_REVENUERECOGNITION_GETAMOUNTINCURRENCY_BULK(@CURRENCYID,@ORGANIZATIONCURRENCYID,@CURRENCYDECIMALDIGITS,@CURRENCYROUNDINGTYPECODE) as RR_INCURRENCY
on CRR.REVENUERECOGNITIONID = RR_INCURRENCY.ID
left join dbo.UFN_RECOGNITIONCREDIT_GETAMOUNTINCURRENCY_BULK(@CURRENCYID,@ORGANIZATIONCURRENCYID,@CURRENCYDECIMALDIGITS,@CURRENCYROUNDINGTYPECODE) as RC_INCURRENCY
on CRR.RECOGNITIONCREDITID = RC_INCURRENCY.ID
where CRR.DATECHANGED = @CURRENTDATE
and (RR_INCURRENCY.ID is not null or RC_INCURRENCY.ID is not null)
)
select
RR_INCURRENCY.CONSTITUENTID,
sum(CRR.AMOUNT),
CRR.EFFECTIVEDATE,
case
when @RECOGNITIONTYPE = 1 then
null
when @EXPIRESONCODE = 0 then
dbo.UFN_CONSTITUENTRECOGNITION_CALCULATEEXPIRATIONDATE(@PROGRAMID,CRR.EFFECTIVEDATE)
else
dbo.UFN_CONSTITUENTRECOGNITION_CALCULATEFISCALEXPIRATIONDATE(@PROGRAMID,CRR.EFFECTIVEDATE)
end as YEAREND,
CRR.ISPLANNEDGIFT
from
dbo.CONSTITUENTRECOGNITIONREVENUE CRR
left join dbo.UFN_REVENUERECOGNITION_GETAMOUNTINCURRENCY_BULK(@CURRENCYID,@ORGANIZATIONCURRENCYID,@CURRENCYDECIMALDIGITS,@CURRENCYROUNDINGTYPECODE) as RR_INCURRENCY
on CRR.REVENUERECOGNITIONID = RR_INCURRENCY.ID
left join dbo.UFN_RECOGNITIONCREDIT_GETAMOUNTINCURRENCY_BULK(@CURRENCYID,@ORGANIZATIONCURRENCYID,@CURRENCYDECIMALDIGITS,@CURRENCYROUNDINGTYPECODE) as RC_INCURRENCY
on CRR.RECOGNITIONCREDITID = RC_INCURRENCY.ID
inner join CONSTITUENT_CTE on CONSTITUENT_CTE.CONSTITUENTID = RR_INCURRENCY.CONSTITUENTID
inner join dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTIONLINEITEM.ID = RR_INCURRENCY.REVENUESPLITID
where
CRR.RECOGNITIONPROGRAMID = @PROGRAMID
and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null
and FINANCIALTRANSACTIONLINEITEM.TYPECODE <> 1
and (RR_INCURRENCY.ID is not null or RC_INCURRENCY.ID is not null)
group by
RR_INCURRENCY.CONSTITUENTID,
CRR.EFFECTIVEDATE,
CRR.ISPLANNEDGIFT,
FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID,
CRR.DATEADDED
order by
RR_INCURRENCY.CONSTITUENTID,
CRR.EFFECTIVEDATE,
CRR.DATEADDED asc;
end
else
begin
declare CONSTITS_CURSOR cursor local fast_forward for
with CONSTITUENT_CTE as (
select distinct
RR_INCURRENCY.CONSTITUENTID
from
dbo.CONSTITUENTRECOGNITIONREVENUE CRR
left join dbo.UFN_REVENUERECOGNITION_GETAMOUNTINCURRENCY_BULK(@CURRENCYID,@ORGANIZATIONCURRENCYID,@CURRENCYDECIMALDIGITS,@CURRENCYROUNDINGTYPECODE) as RR_INCURRENCY
on CRR.REVENUERECOGNITIONID = RR_INCURRENCY.ID
left join dbo.UFN_RECOGNITIONCREDIT_GETAMOUNTINCURRENCY_BULK(@CURRENCYID,@ORGANIZATIONCURRENCYID,@CURRENCYDECIMALDIGITS,@CURRENCYROUNDINGTYPECODE) as RC_INCURRENCY
on CRR.RECOGNITIONCREDITID = RC_INCURRENCY.ID
where CRR.DATECHANGED = @CURRENTDATE
and (RR_INCURRENCY.ID is not null or RC_INCURRENCY.ID is not null)
)
select
RR_INCURRENCY.CONSTITUENTID,
sum(CRR.AMOUNT),
CRR.EFFECTIVEDATE,
case
when @RECOGNITIONTYPE = 1 then
null
when @EXPIRESONCODE = 0 then
dbo.UFN_CONSTITUENTRECOGNITION_CALCULATEEXPIRATIONDATE(@PROGRAMID,CRR.EFFECTIVEDATE)
else
dbo.UFN_CONSTITUENTRECOGNITION_CALCULATEFISCALEXPIRATIONDATE(@PROGRAMID,CRR.EFFECTIVEDATE)
end as YEAREND,
CRR.ISPLANNEDGIFT
from
dbo.CONSTITUENTRECOGNITIONREVENUE CRR
left join dbo.UFN_REVENUERECOGNITION_GETAMOUNTINCURRENCY_BULK(@CURRENCYID,@ORGANIZATIONCURRENCYID,@CURRENCYDECIMALDIGITS,@CURRENCYROUNDINGTYPECODE) as RR_INCURRENCY
on CRR.REVENUERECOGNITIONID = RR_INCURRENCY.ID
left join dbo.UFN_RECOGNITIONCREDIT_GETAMOUNTINCURRENCY_BULK(@CURRENCYID,@ORGANIZATIONCURRENCYID,@CURRENCYDECIMALDIGITS,@CURRENCYROUNDINGTYPECODE) as RC_INCURRENCY
on CRR.RECOGNITIONCREDITID = RC_INCURRENCY.ID
inner join CONSTITUENT_CTE on CONSTITUENT_CTE.CONSTITUENTID = RR_INCURRENCY.CONSTITUENTID
inner join dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTIONLINEITEM.ID = RR_INCURRENCY.REVENUESPLITID
where
CRR.RECOGNITIONPROGRAMID = @PROGRAMID
and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null
and FINANCIALTRANSACTIONLINEITEM.TYPECODE <> 1
and (RR_INCURRENCY.ID is not null or RC_INCURRENCY.ID is not null)
group by
RR_INCURRENCY.CONSTITUENTID,
CRR.EFFECTIVEDATE,
CRR.ISPLANNEDGIFT,
FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID,
CRR.DATEADDED
having
sum(CRR.AMOUNT) < @MAXAMOUNT
order by
RR_INCURRENCY.CONSTITUENTID,
CRR.EFFECTIVEDATE,
CRR.DATEADDED asc;
end
open CONSTITS_CURSOR;
fetch next from CONSTITS_CURSOR into @CONSTITUENTID, @AMOUNT, @EFFECTIVEDATE, @YEAREND, @ISPLANNEDGIFT;
while @@FETCH_STATUS = 0
begin
if @CONSTITUENTID <> coalesce(@PREVCONSTITUENTID, '00000000-0000-0000-0000-000000000000')
begin
set @PREVCONSTITUENTID = @CONSTITUENTID;
set @TOTALAMOUNT = 0;
set @PLANNEDGIFTTOTAL = 0;
end
else
--if we change years, start counting over
if @RECOGNITIONTYPE = 0 and @YEAREND <> @PREVYEAREND
begin
set @TOTALAMOUNT = 0;
set @PLANNEDGIFTTOTAL = 0;
end
set @PREVYEAREND = @YEAREND;
set @CURRENTRECID = null;
set @CURRENTRECLEVELID = null;
if @ISPLANNEDGIFT = 1 and @PLANNEDGIFTCODE = 2
begin
set @PLANNEDGIFTTOTAL = @PLANNEDGIFTTOTAL + @AMOUNT
end
else
begin
set @TOTALAMOUNT = @TOTALAMOUNT + @AMOUNT;
end
--get the recognition level qualified for
select top 1
@CURRENTRECLEVELID = RL.RECOGNITIONLEVELID
from
@RECBOUNDS RL
where
(
@TOTALAMOUNT >= RL.AMOUNT
or
(@PLANNEDGIFTCODE = 2 and @PLANNEDGIFTTOTAL >= RL.PLANNEDGIFTAMOUNT)
)
and @EFFECTIVEDATE >= RL.STARTDATE
--When comparing ENDDATE we need to subtract a day and get the latest time. This allows the date comparisons happening below to
--properly choose which level a constituent should be placed into. Previously, they could be the same day and it would choose
--the incorrect level.
and (@EFFECTIVEDATE <= DATEADD(day,-1,dbo.UFN_DATE_GETLATESTTIME(RL.ENDDATE)) or RL.ENDDATE is null)
and RL.RECOGNITIONLEVELID not in
(
select RECOGNITIONLEVELID from dbo.CONSTITUENTRECOGNITIONDECLINEDLEVEL
where CONSTITUENTID = @CONSTITUENTID
and RECOGNITIONPROGRAMID = @PROGRAMID
)
and @PROGRAMID not in
(
select RECOGNITIONPROGRAMID from dbo.CONSTITUENTRECOGNITIONDECLINEDPROGRAM
where CONSTITUENTID = @CONSTITUENTID
and RECOGNITIONPROGRAMID = @PROGRAMID
)
order by
RL.AMOUNT desc,
RL.PLANNEDGIFTAMOUNT desc
if @CURRENTRECLEVELID is not null
begin
declare @ORGANIZATIONTOTALAMOUNT money;
declare @ORGANIZATIONTOTALPLANNEDGIFTAMOUNT money;
declare @ORGANIZATIONEXCHANGERATEID uniqueidentifier;
select
@CURRENTRECID = CONSTITUENTRECOGNITION.ID,
@JOINDATE = CONSTITUENTRECOGNITION.JOINDATE,
@CURRENTTOTAL = coalesce(CONSTITUENTRECOGNITION.TOTALAMOUNT, 0),
@CURRENTPLANNEDGIFTTOTAL = coalesce(CONSTITUENTRECOGNITION.TOTALPLANNEDGIFTAMOUNT, 0),
@ORGANIZATIONEXCHANGERATEID = CONSTITUENTRECOGNITION.ORGANIZATIONEXCHANGERATEID
from
dbo.CONSTITUENTRECOGNITION
where
CONSTITUENTRECOGNITION.RECOGNITIONLEVELID = @CURRENTRECLEVELID
and CONSTITUENTRECOGNITION.CONSTITUENTID = @CONSTITUENTID
and (@RECOGNITIONTYPE = 1 or year(CONSTITUENTRECOGNITION.EXPIRATIONDATE) = year(@YEAREND))
if (@ORGANIZATIONCURRENCYID = @BASECURRENCYID)
begin
set @ORGANIZATIONTOTALAMOUNT = @TOTALAMOUNT;
set @ORGANIZATIONTOTALPLANNEDGIFTAMOUNT = @PLANNEDGIFTTOTAL
end
else
begin
if @ORGANIZATIONEXCHANGERATEID is null
begin
set @ORGANIZATIONEXCHANGERATEID = dbo.UFN_CURRENCYEXCHANGERATE_GETLATEST(@BASECURRENCYID, @ORGANIZATIONCURRENCYID, @CURRENTDATE, 0, null);
end
set @ORGANIZATIONTOTALAMOUNT = dbo.UFN_CURRENCY_CONVERT(@TOTALAMOUNT, @ORGANIZATIONEXCHANGERATEID);
set @ORGANIZATIONTOTALPLANNEDGIFTAMOUNT = dbo.UFN_CURRENCY_CONVERT(@PLANNEDGIFTTOTAL, @ORGANIZATIONEXCHANGERATEID);
end
if @CURRENTRECID is null
begin
if exists (select top 1 1 from @TOPRECOGNITIONLEVEL where CONSTITUENTID = @CONSTITUENTID and EFFECTIVEDATE = @EFFECTIVEDATE and (@RECOGNITIONTYPE = 1 or YEAREND = @YEAREND))
begin
update @TOPRECOGNITIONLEVEL
set
TOPRECOGNITIONLEVELID = @CURRENTRECLEVELID,
TOTALAMOUNT = @TOTALAMOUNT,
PLANNEDGIFTTOTAL = @PLANNEDGIFTTOTAL,
JOINDATE = @JOINDATE,
CURRENTTOTAL = @CURRENTTOTAL,
CURRENTPLANNEDGIFTTOTAL = @CURRENTPLANNEDGIFTTOTAL,
ORGANIZATIONEXCHANGERATEID = @ORGANIZATIONEXCHANGERATEID,
ORGANIZATIONTOTALAMOUNT = @ORGANIZATIONTOTALAMOUNT,
ORGANIZATIONTOTALPLANNEDGIFTAMOUNT = @ORGANIZATIONTOTALPLANNEDGIFTAMOUNT
where
CONSTITUENTID = @CONSTITUENTID
and TOTALAMOUNT <= @TOTALAMOUNT
and EFFECTIVEDATE = @EFFECTIVEDATE
and (@RECOGNITIONTYPE = 1 or YEAREND = @YEAREND);
end
else if exists (select top 1 1 from @TOPRECOGNITIONLEVEL where CONSTITUENTID = @CONSTITUENTID and TOPRECOGNITIONLEVELID = @CURRENTRECLEVELID and (@RECOGNITIONTYPE = 1 or YEAREND = @YEAREND))
begin
update @TOPRECOGNITIONLEVEL
set
TOTALAMOUNT = @TOTALAMOUNT,
PLANNEDGIFTTOTAL = @PLANNEDGIFTTOTAL,
JOINDATE = @JOINDATE,
CURRENTTOTAL = @CURRENTTOTAL,
CURRENTPLANNEDGIFTTOTAL = @CURRENTPLANNEDGIFTTOTAL,
ORGANIZATIONEXCHANGERATEID = @ORGANIZATIONEXCHANGERATEID,
ORGANIZATIONTOTALAMOUNT = @ORGANIZATIONTOTALAMOUNT,
ORGANIZATIONTOTALPLANNEDGIFTAMOUNT = @ORGANIZATIONTOTALPLANNEDGIFTAMOUNT
where
CONSTITUENTID = @CONSTITUENTID
and TOTALAMOUNT <= @TOTALAMOUNT
and TOPRECOGNITIONLEVELID = @CURRENTRECLEVELID
and (@RECOGNITIONTYPE = 1 or YEAREND = @YEAREND);
end
else
begin
insert into @TOPRECOGNITIONLEVEL
(
CONSTITUENTID,
TOPRECOGNITIONLEVELID,
TOTALAMOUNT,
PLANNEDGIFTTOTAL,
EFFECTIVEDATE,
JOINDATE,
CURRENTTOTAL,
CURRENTPLANNEDGIFTTOTAL,
ORGANIZATIONEXCHANGERATEID,
ORGANIZATIONTOTALAMOUNT,
ORGANIZATIONTOTALPLANNEDGIFTAMOUNT,
YEAREND
)
values
(
@CONSTITUENTID,
@CURRENTRECLEVELID,
@TOTALAMOUNT,
@PLANNEDGIFTTOTAL,
@EFFECTIVEDATE,
@JOINDATE,
@CURRENTTOTAL,
@CURRENTPLANNEDGIFTTOTAL,
@ORGANIZATIONEXCHANGERATEID,
@ORGANIZATIONTOTALAMOUNT,
@ORGANIZATIONTOTALPLANNEDGIFTAMOUNT,
@YEAREND
);
end
end
else
begin
if @CURRENTTOTAL < @TOTALAMOUNT or @CURRENTPLANNEDGIFTTOTAL < @PLANNEDGIFTTOTAL
begin
update
dbo.CONSTITUENTRECOGNITION
set
TOTALAMOUNT = @TOTALAMOUNT,
ORGANIZATIONTOTALAMOUNT = @ORGANIZATIONTOTALAMOUNT,
TOTALPLANNEDGIFTAMOUNT = @PLANNEDGIFTTOTAL,
ORGANIZATIONTOTALPLANNEDGIFTAMOUNT = @ORGANIZATIONTOTALPLANNEDGIFTAMOUNT,
ORGANIZATIONEXCHANGERATEID = @ORGANIZATIONEXCHANGERATEID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where
CONSTITUENTRECOGNITION.ID = @CURRENTRECID;
end
if @JOINDATE > @EFFECTIVEDATE
begin
update
dbo.CONSTITUENTRECOGNITION
set
JOINDATE = @EFFECTIVEDATE,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where
CONSTITUENTRECOGNITION.ID = @CURRENTRECID;
end
end
end
fetch next from CONSTITS_CURSOR into @CONSTITUENTID, @AMOUNT, @EFFECTIVEDATE, @YEAREND, @ISPLANNEDGIFT
end
close CONSTITS_CURSOR
deallocate CONSTITS_CURSOR
--new level to add
insert into dbo.CONSTITUENTRECOGNITION
(
ID,
CONSTITUENTID,
RECOGNITIONPROGRAMID,
RECOGNITIONLEVELID,
TOTALAMOUNT,
ORGANIZATIONTOTALAMOUNT,
TOTALPLANNEDGIFTAMOUNT,
ORGANIZATIONTOTALPLANNEDGIFTAMOUNT,
ORGANIZATIONEXCHANGERATEID,
JOINDATE,
EXPIRATIONDATE,
STATUSCODE,
BASECURRENCYID,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
select
newid(),
CONSTITUENTID,
@PROGRAMID,
TOPRECOGNITIONLEVELID,
TOTALAMOUNT,
ORGANIZATIONTOTALAMOUNT,
PLANNEDGIFTTOTAL,
ORGANIZATIONTOTALPLANNEDGIFTAMOUNT,
ORGANIZATIONEXCHANGERATEID,
EFFECTIVEDATE,
YEAREND,
@INITIALSTATUSCODE,
@BASECURRENCYID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from
@TOPRECOGNITIONLEVEL;
--copy benefits for new levels
insert into dbo.CONSTITUENTRECOGNITIONBENEFIT (
ID,
CONSTITUENTRECOGNITIONID,
BENEFITID,
QUANTITY,
UNITVALUE,
ORGANIZATIONUNITVALUE,
ORGANIZATIONEXCHANGERATEID,
DETAILS,
SEQUENCE,
BASECURRENCYID,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
select
newid(),
CRB.CRID,
CRB.BENEFITID,
CRB.QUANTITY,
CRB.UNITVALUE,
case CRB.BASECURRENCYID
when @ORGANIZATIONCURRENCYID then CRB.UNITVALUE
else dbo.UFN_CURRENCY_CONVERT(CRB.UNITVALUE, CRB.ORGANIZATIONEXCHANGERATEID)
end ORGANIZATIONUNITVALUE,
case CRB.BASECURRENCYID
when @ORGANIZATIONCURRENCYID then null
else CRB.ORGANIZATIONEXCHANGERATEID
end ORGANIZATIONEXCHANGERATEID,
CRB.DETAILS,
CRB.SEQUENCE,
CRB.BASECURRENCYID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from (
select
CONSTITUENTRECOGNITION.ID CRID,
RECOGNITIONLEVELBENEFIT.BENEFITID,
RECOGNITIONLEVELBENEFIT.QUANTITY,
RECOGNITIONLEVELBENEFIT.UNITVALUE,
case RECOGNITIONLEVELBENEFIT.BASECURRENCYID
when @ORGANIZATIONCURRENCYID then null
else dbo.UFN_CURRENCYEXCHANGERATE_GETLATEST(RECOGNITIONLEVELBENEFIT.BASECURRENCYID, @ORGANIZATIONCURRENCYID, @CURRENTDATE, 0, null)
end ORGANIZATIONEXCHANGERATEID,
RECOGNITIONLEVELBENEFIT.DETAILS,
RECOGNITIONLEVELBENEFIT.SEQUENCE,
RECOGNITIONLEVELBENEFIT.BASECURRENCYID
from
dbo.CONSTITUENTRECOGNITION
inner join dbo.RECOGNITIONLEVEL on CONSTITUENTRECOGNITION.RECOGNITIONLEVELID = RECOGNITIONLEVEL.ID
inner join dbo.RECOGNITIONLEVELBENEFIT on RECOGNITIONLEVEL.ID = RECOGNITIONLEVELBENEFIT.RECOGNITIONLEVELID
where
CONSTITUENTRECOGNITION.RECOGNITIONPROGRAMID = @PROGRAMID
and CONSTITUENTRECOGNITION.DATECHANGED = @CURRENTDATE
and NOT exists (
select
1
from
dbo.CONSTITUENTRECOGNITIONBENEFIT CRB
where
CRB.BENEFITID = RECOGNITIONLEVELBENEFIT.BENEFITID
and CRB.CONSTITUENTRECOGNITIONID = CONSTITUENTRECOGNITION.ID
)
and
RECOGNITIONLEVELBENEFIT.BENEFITID not in
(
select
BCD.BENEFITID
from
dbo.BENEFITCONSTITUENTDECLINED BCD
where
BCD.CONSTITUENTID = CONSTITUENTRECOGNITION.CONSTITUENTID
)) CRB;
--set last processed on date
update
dbo.RECOGNITIONPROGRAM
set
LASTPROCESSEDON = @CURRENTDATE,
DATECHANGED = @CURRENTDATE,
CHANGEDBYID = @CHANGEAGENTID
where
ID = @PROGRAMID;
--get num processed
select
@NUMPROCESSED = count(*)
from
dbo.CONSTITUENTRECOGNITION
where
DATECHANGED = @CURRENTDATE
and RECOGNITIONPROGRAMID = @PROGRAMID;
return 0
end