USP_MATCHINGGIFTPLEDGE_AUTOADD
Stored procedure to add matching gifts based on matching gift preferences
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @CONSTITUENTID | uniqueidentifier | IN | |
| @CHANGEAGENTID | uniqueidentifier | IN | |
| @GIFTID | uniqueidentifier | IN | |
| @DATE | datetime | IN | |
| @AMOUNT | money | IN | |
| @RECEIPTAMOUNT | money | IN | |
| @SPLITS | xml | IN | |
| @CURRENTAPPUSERID | uniqueidentifier | IN |
Definition
Copy
CREATE procedure dbo.USP_MATCHINGGIFTPLEDGE_AUTOADD
(
@CONSTITUENTID uniqueidentifier,
@CHANGEAGENTID uniqueidentifier,
@GIFTID uniqueidentifier,
@DATE datetime,
@AMOUNT money,
@RECEIPTAMOUNT money,
@SPLITS xml,
@CURRENTAPPUSERID uniqueidentifier = null
)
as begin
set nocount on;
declare @ID uniqueidentifier;
declare @MGORGID uniqueidentifier;
declare @REVENUEID uniqueidentifier;
declare @RELATIONSHIPID uniqueidentifier;
declare @RELATIONSHIPTYPECODEID uniqueidentifier;
declare @JOBSCHEDULECODEID uniqueidentifier;
declare @CAREERLEVELCODEID uniqueidentifier;
declare @MATCHINGGIFTCONDITIONID uniqueidentifier;
declare @MATCHINGGIFTCONDITIONTYPECODEID uniqueidentifier;
declare @MATCHINGGIFTAMOUNT money;
declare @MATCHINGGIFTMINAMOUNT money;
declare @MATCHINGGIFTMAXAMOUNT money;
declare @MATCHINGGIFTMAXMATCHANNUAL money;
declare @MATCHINGGIFTMAXMATCHTOTAL money;
declare @MATCHINGGIFTFACTOR decimal(30,2);
declare @MGSPLITS xml;
declare @MATCHTYPECODE tinyint;
declare @REVENUETYPECODE tinyint;
declare @CONDITIONCOUNT tinyint;
declare @APPLICATIONCODE tinyint = 0;
declare @TRANSACTIONCURRENCYID uniqueidentifier;
declare @CURRENCYDECIAMLDIGITS tinyint;
declare @CURRENCYROUNDINGTYPECODE tinyint;
if @CHANGEAGENTID is null
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;
select
@TRANSACTIONCURRENCYID = REVENUE.TRANSACTIONCURRENCYID
from dbo.REVENUE
where ID = @GIFTID;
select
@CURRENCYDECIAMLDIGITS = DECIMALDIGITS,
@CURRENCYROUNDINGTYPECODE = ROUNDINGTYPECODE
from dbo.CURRENCY
where ID = @TRANSACTIONCURRENCYID;
if dbo.UFN_REVENUE_HASNEEDEDRATES(@GIFTID) = 1
and @TRANSACTIONCURRENCYID in (
select CURRENCYID
from dbo.UFN_CURRENCYSET_GETTRANSACTIONCURRENCIES(dbo.UFN_CURRENCYSET_GETAPPUSERCURRENCYSET(@CURRENTAPPUSERID))
)
begin
-- loop through matching gift org relationships
declare CUR_MATCHINGGIFTRELATIONSHIPS cursor local fast_forward for
select ID from dbo.RELATIONSHIP
where RELATIONSHIPCONSTITUENTID = @CONSTITUENTID
and ISMATCHINGGIFTRELATIONSHIP = 1
open CUR_MATCHINGGIFTRELATIONSHIPS
fetch next from CUR_MATCHINGGIFTRELATIONSHIPS into @RELATIONSHIPID
while @@FETCH_STATUS = 0
begin
-- get relationship type and MG org id
select
@RELATIONSHIPTYPECODEID = RELATIONSHIPTYPECODEID,
@MGORGID = RECIPROCALCONSTITUENTID,
@JOBSCHEDULECODEID = JOBSCHEDULECODEID,
@CAREERLEVELCODEID = CAREERLEVELCODEID
from dbo.RELATIONSHIP
left join dbo.RELATIONSHIPJOBINFO
on RELATIONSHIPJOBINFO.RELATIONSHIPSETID = RELATIONSHIP.RELATIONSHIPSETID
and (
(RELATIONSHIPJOBINFO.STARTDATE is null or RELATIONSHIPJOBINFO.STARTDATE < getdate())
and (RELATIONSHIPJOBINFO.ENDDATE is null or RELATIONSHIPJOBINFO.ENDDATE > getdate())
)
where
RELATIONSHIP.ID = @RELATIONSHIPID
and ((RELATIONSHIP.STARTDATE is null or RELATIONSHIP.STARTDATE < getdate()) and
(RELATIONSHIP.ENDDATE is null or RELATIONSHIP.ENDDATE > getdate()));
-- get condition type code preference
select @MATCHINGGIFTCONDITIONTYPECODEID = MATCHINGGIFTCONDITIONTYPECODEID
from dbo.MATCHINGGIFTPREFERENCEINFO;
-- check if there are any mg conditions for the current relationship
select
@CONDITIONCOUNT = COUNT(*)
from dbo.MATCHINGGIFTCONDITION
inner join dbo.MATCHINGGIFTCONDITIONRELATIONSHIP
on MATCHINGGIFTCONDITION.ID = MATCHINGGIFTCONDITIONRELATIONSHIP.MATCHINGGIFTCONDITIONID
where
ORGANIZATIONID = @MGORGID
and RELATIONSHIPTYPECODEID = @RELATIONSHIPTYPECODEID
and (JOBSCHEDULECODEID is null or JOBSCHEDULECODEID = @JOBSCHEDULECODEID)
and (CAREERLEVELCODEID is null or CAREERLEVELCODEID = @CAREERLEVELCODEID)
and MATCHINGGIFTCONDITION.BASECURRENCYID = @TRANSACTIONCURRENCYID;
if @CONDITIONCOUNT > 0 -- there matching gift conditions exist for the specific relationship type and
begin
-- go through each condition and see if a matching gift claim can be created
declare CUR_MATCHINGGIFTCONDITIONS cursor local fast_forward for
select ID,
MATCHINGFACTOR,
MINMATCHPERGIFT,
MAXMATCHPERGIFT,
MAXMATCHANNUAL,
MAXMATCHTOTAL,
MATCHTYPECODE,
REVENUETYPECODE
from (
select
MATCHINGGIFTCONDITION.ID, MATCHINGFACTOR, MINMATCHPERGIFT, MAXMATCHPERGIFT, MAXMATCHANNUAL,
MAXMATCHTOTAL,MATCHTYPECODE,REVENUETYPECODE,
row_number() over(partition by REVENUETYPE order by SEQUENCE) as ROWNUM
from dbo.MATCHINGGIFTCONDITION
inner join dbo.MATCHINGGIFTCONDITIONRELATIONSHIP on MATCHINGGIFTCONDITIONRELATIONSHIP.MATCHINGGIFTCONDITIONID = MATCHINGGIFTCONDITION.ID
where ORGANIZATIONID = @MGORGID
and RELATIONSHIPTYPECODEID = @RELATIONSHIPTYPECODEID
and (JOBSCHEDULECODEID is null or JOBSCHEDULECODEID = @JOBSCHEDULECODEID)
and (CAREERLEVELCODEID is null or CAREERLEVELCODEID = @CAREERLEVELCODEID)
and MATCHINGGIFTCONDITION.BASECURRENCYID = @TRANSACTIONCURRENCYID
) as SUBQ
where
ROWNUM = 1
open CUR_MATCHINGGIFTCONDITIONS
fetch next from CUR_MATCHINGGIFTCONDITIONS into @MATCHINGGIFTCONDITIONID,@MATCHINGGIFTFACTOR,
@MATCHINGGIFTMINAMOUNT,@MATCHINGGIFTMAXAMOUNT,@MATCHINGGIFTMAXMATCHANNUAL,@MATCHINGGIFTMAXMATCHTOTAL, @MATCHTYPECODE, @REVENUETYPECODE
while @@FETCH_STATUS = 0
begin
if not @MATCHINGGIFTCONDITIONID is null
begin
--Bug 30719 - AdamBu 4/1/09 - Only generate matching gift claims based on RECEIPTAMOUNT
-- if all the splits in the revenue should be matched
declare @GENERATECLAIMS bit = 1
-- calculate matching gift amount
-- recalculate matchgift amount based on the revenue type code and get application code
-- 17 and 9 are both sponsorships (sponsorship additional gift and sponsorship)
select @AMOUNT = sum(FINANCIALTRANSACTIONLINEITEM.TRANSACTIONAMOUNT)
from dbo.FINANCIALTRANSACTIONLINEITEM
inner join dbo.REVENUESPLIT_EXT on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID
where FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = @GIFTID and (case when REVENUESPLIT_EXT.TYPECODE = 17 then 9 else REVENUESPLIT_EXT.TYPECODE end) = @REVENUETYPECODE
and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null and FINANCIALTRANSACTIONLINEITEM.TYPECODE <> 1;
--AKG CR299627-050108 check if need to apply full amount or tax portion
if @MATCHTYPECODE = 1 and exists(select FINANCIALTRANSACTIONLINEITEM.ID from dbo.FINANCIALTRANSACTIONLINEITEM inner join dbo.REVENUESPLIT_EXT on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID where FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = @GIFTID and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null and FINANCIALTRANSACTIONLINEITEM.TYPECODE <> 1 and (case when REVENUESPLIT_EXT.TYPECODE = 17 then 9 else REVENUESPLIT_EXT.TYPECODE end) = @REVENUETYPECODE)
begin
if exists(
select FINANCIALTRANSACTIONLINEITEM.ID
from dbo.FINANCIALTRANSACTIONLINEITEM
inner join dbo.REVENUESPLIT_EXT on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID
where FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = @GIFTID and (case when REVENUESPLIT_EXT.TYPECODE = 17 then 9 else REVENUESPLIT_EXT.TYPECODE end) <> @REVENUETYPECODE
and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null and FINANCIALTRANSACTIONLINEITEM.TYPECODE <> 1
)
begin
--If we only match tax deductible portions, but not all the splits on a revenue should
-- be matched, then we don't know how much to match for, so don't auto-generate claims.
set @GENERATECLAIMS = 0;
end
else
begin
set @MATCHINGGIFTAMOUNT = dbo.UFN_CURRENCY_ROUND(@RECEIPTAMOUNT * @MATCHINGGIFTFACTOR, @CURRENCYDECIAMLDIGITS, @CURRENCYROUNDINGTYPECODE);
end
end
else
begin
set @MATCHINGGIFTAMOUNT = dbo.UFN_CURRENCY_ROUND(@AMOUNT * @MATCHINGGIFTFACTOR, @CURRENCYDECIAMLDIGITS, @CURRENCYROUNDINGTYPECODE);
end
-- determine if the matching gift needs to be adjusted based on the conditions of the MG organization
set @MATCHINGGIFTAMOUNT = dbo.UFN_MATCHINGGIFTPLEDGE_CALCULATEMGAMOUNTINCURRENCY(@CONSTITUENTID,@MGORGID,@MATCHINGGIFTAMOUNT,@DATE,@MATCHINGGIFTMAXAMOUNT,@MATCHINGGIFTMAXMATCHANNUAL,@MATCHINGGIFTMAXMATCHTOTAL,@TRANSACTIONCURRENCYID);
if (@MATCHINGGIFTAMOUNT >= @MATCHINGGIFTMINAMOUNT) and @MATCHINGGIFTAMOUNT > 0 and @GENERATECLAIMS = 1
begin
-- Get new MG splits
-- get the splits for the current type code
select @SPLITS = (
select sum(FINANCIALTRANSACTIONLINEITEM.TRANSACTIONAMOUNT) AMOUNT,
REVENUESPLIT_EXT.DESIGNATIONID,
(case when REVENUESPLIT_EXT.TYPECODE = 17 then 9 else REVENUESPLIT_EXT.TYPECODE end) TYPECODE,
@TRANSACTIONCURRENCYID [TRANSACTIONCURRENCYID],
0 APPLICATIONCODE
from dbo.FINANCIALTRANSACTIONLINEITEM
inner join dbo.REVENUESPLIT_EXT on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID
where FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = @GIFTID and (case when REVENUESPLIT_EXT.TYPECODE = 17 then 9 else REVENUESPLIT_EXT.TYPECODE end) = @REVENUETYPECODE
and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null and FINANCIALTRANSACTIONLINEITEM.TYPECODE <> 1
group by REVENUESPLIT_EXT.DESIGNATIONID, (case when REVENUESPLIT_EXT.TYPECODE = 17 then 9 else REVENUESPLIT_EXT.TYPECODE end), REVENUESPLIT_EXT.APPLICATIONCODE
for xml raw('ITEM'),type,elements,root('SPLITS'),binary base64
);
set @MGSPLITS = dbo.UFN_MATCHINGGIFT_GETAUTOADDSPLITS_3(@AMOUNT,@MATCHINGGIFTAMOUNT,@SPLITS,@REVENUETYPECODE)
declare @MGBASECURRENCYID uniqueidentifier = dbo.UFN_APPUSER_GETBASECURRENCY(@CURRENTAPPUSERID);
declare @MGBASEEXCHANGERATEID uniqueidentifier;
if @MGBASECURRENCYID <> @TRANSACTIONCURRENCYID
begin
if @MGBASECURRENCYID = (
select isnull(REVENUE_EXT.NONPOSTABLEBASECURRENCYID, CURRENCYSET.BASECURRENCYID) as BASECURRENCYID
from dbo.FINANCIALTRANSACTION
inner join dbo.REVENUE_EXT on FINANCIALTRANSACTION.ID = REVENUE_EXT.ID
inner join dbo.PDACCOUNTSYSTEM on FINANCIALTRANSACTION.PDACCOUNTSYSTEMID = PDACCOUNTSYSTEM.ID
inner join dbo.CURRENCYSET on PDACCOUNTSYSTEM.CURRENCYSETID = CURRENCYSET.ID
where FINANCIALTRANSACTION.ID = @GIFTID
and FINANCIALTRANSACTION.DELETEDON is null
)
begin
select @MGBASEEXCHANGERATEID = FINANCIALTRANSACTION.BASEEXCHANGERATEID
from dbo.FINANCIALTRANSACTION
inner join dbo.REVENUE_EXT on FINANCIALTRANSACTION.ID = REVENUE_EXT.ID
inner join dbo.CURRENCYEXCHANGERATE on CURRENCYEXCHANGERATE.ID = FINANCIALTRANSACTION.BASEEXCHANGERATEID
where FINANCIALTRANSACTION.ID = @GIFTID
and CURRENCYEXCHANGERATE.TYPECODE <> 2
and FINANCIALTRANSACTION.DELETEDON is null;
end
if @MGBASEEXCHANGERATEID is null
begin
set @MGBASEEXCHANGERATEID = dbo.UFN_CURRENCYEXCHANGERATE_GETLATEST(
@TRANSACTIONCURRENCYID,
@MGBASECURRENCYID,
@DATE,
1,
null
);
end
end
-- add matching gift
set @ID = newid();
exec dbo.USP_MATCHINGGIFTPLEDGE_ADD @ID, @CHANGEAGENTID, @GIFTID, @MGORGID, @DATE, @MATCHINGGIFTAMOUNT, @MGSPLITS, @MATCHINGGIFTCONDITIONID, @RELATIONSHIPID, @TRANSACTIONCURRENCYID, @MGBASECURRENCYID, @MGBASEEXCHANGERATEID;
end
else
begin
if @GENERATECLAIMS = 0
begin
--For some reason, we've decided to not create claims. Flag the record as eligible.
update dbo.REVENUE_EXT
set ELIGIBLEFORMATCHINGGIFTCLAIM = 1,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = getdate()
where ID = @GIFTID
end
end
end
--clean up
set @MATCHINGGIFTCONDITIONID = null;
set @MATCHINGGIFTAMOUNT = 0;
set @APPLICATIONCODE = 0;
fetch next from CUR_MATCHINGGIFTCONDITIONS into @MATCHINGGIFTCONDITIONID,@MATCHINGGIFTFACTOR,
@MATCHINGGIFTMINAMOUNT,@MATCHINGGIFTMAXAMOUNT,@MATCHINGGIFTMAXMATCHANNUAL,@MATCHINGGIFTMAXMATCHTOTAL, @MATCHTYPECODE, @REVENUETYPECODE
end
close CUR_MATCHINGGIFTCONDITIONS;
deallocate CUR_MATCHINGGIFTCONDITIONS;
end
-- clean up for next MG Org
set @ID = null;
set @MGORGID = null;
set @MATCHINGGIFTCONDITIONID = null;
set @MATCHINGGIFTAMOUNT = 0;
set @APPLICATIONCODE = 0;
fetch next from CUR_MATCHINGGIFTRELATIONSHIPS into @RELATIONSHIPID
end
close CUR_MATCHINGGIFTRELATIONSHIPS;
deallocate CUR_MATCHINGGIFTRELATIONSHIPS;
end
else
begin
--WI 169658 PatrickMcD Do not flag as eligible for matching gift if exchange rate is not set
if dbo.UFN_REVENUE_HASNEEDEDRATES(@GIFTID) = 1
begin
--For some reason, we've decided to not create claims. Flag the record as eligible.
update dbo.REVENUE_EXT
set ELIGIBLEFORMATCHINGGIFTCLAIM = 1,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = getdate()
where ID = @GIFTID
end
end
return 0;
end