USP_DONORCHALLENGE_ENCUMBERPROCESS
This stored procedure is used by the donor challenge encumber process to encumber funds
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @DCID | uniqueidentifier | IN | |
| @CHANGEAGENTID | uniqueidentifier | IN |
Definition
Copy
CREATE procedure dbo.USP_DONORCHALLENGE_ENCUMBERPROCESS(@DCID uniqueidentifier, @CHANGEAGENTID uniqueidentifier)
as
begin
declare @MINGIFTAMOUNT money;
declare @MATCHINGFACTOR money;
declare @STARTDATE datetime;
declare @ENDDATE datetime;
declare @PLEDGEEND datetime;
declare @MATCHTYPECODE tinyint;
declare @MAXMATCHPERGIFT money;
declare @LASTRUN datetime;
declare @REVENUERECOGNITIONTYPECODEID uniqueidentifier;
declare @TOTALFUNDS money;
declare @HANDLEPLEDGES bit = 1;
declare @BASECURRENCYID uniqueidentifier;
declare @BASECURRENCYDECIMALDIGITS tinyint;
declare @BASECURRENCYROUNDINGTYPECODE tinyint;
if @CHANGEAGENTID is null
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;
select @MINGIFTAMOUNT = DONORCHALLENGE.MINGIFTAMOUNT,
@MATCHINGFACTOR = case when DONORCHALLENGE.TYPECODE = 1 then 1 else DONORCHALLENGE.MATCHINGFACTOR end,
@STARTDATE = DONORCHALLENGE.STARTDATE,
@ENDDATE = DONORCHALLENGE.ENDDATE,
@PLEDGEEND = DATEADD(month, DONORCHALLENGE.PAYPLEDGESWITHIN *
case when DONORCHALLENGE.PAYPLEDGESWITHINUNITCODE = 0 then 12 else 1 end,
DONORCHALLENGE.ENDDATE),
@MATCHTYPECODE = DONORCHALLENGE.MATCHTYPECODE,
@MAXMATCHPERGIFT = DONORCHALLENGE.MAXMATCHPERGIFT,
@REVENUERECOGNITIONTYPECODEID = DONORCHALLENGE.REVENUERECOGNITIONTYPECODEID,
@TOTALFUNDS = case when DONORCHALLENGE.TYPECODE = 1 then DONORCHALLENGE.MATCHTHRESHOLD else DONORCHALLENGE.TOTALFUNDS end,
@LASTRUN = DONORCHALLENGE.PROCESSLASTRUN,
@BASECURRENCYID = DONORCHALLENGE.BASECURRENCYID
from DONORCHALLENGE
where ID = @DCID;
select
@BASECURRENCYDECIMALDIGITS = DECIMALDIGITS,
@BASECURRENCYROUNDINGTYPECODE = ROUNDINGTYPECODE
from
dbo.CURRENCY
where
ID = @BASECURRENCYID;
if exists(select top 1 ID from dbo.DONORCHALLENGEEXCLUDEDAPPLICATIONTYPE where DONORCHALLENGEID = @DCID and APPLICATIONCODE = 2)
set @HANDLEPLEDGES = 0
declare @DATEADDED datetime;
declare @REVENUESPLITID uniqueidentifier;
declare @DESIGNATIONID uniqueidentifier;
declare @ENCUMBERED money;
declare @AMOUNT money;
declare @NOW datetime = getdate();
if @LASTRUN is not null
begin
--Delete revenue that no longer qualifies
delete DONORCHALLENGEENCUMBERED
from dbo.DONORCHALLENGEENCUMBERED
inner join dbo.REVENUESPLIT on REVENUESPLIT.ID = DONORCHALLENGEENCUMBERED.REVENUESPLITID and DONORCHALLENGEENCUMBERED.DONORCHALLENGEID = @DCID
inner join dbo.REVENUE on REVENUE.ID = REVENUESPLIT.REVENUEID
left join dbo.MEMBERSHIPTRANSACTION on MEMBERSHIPTRANSACTION.REVENUESPLITID = REVENUESPLIT.ID
left join dbo.DONORCHALLENGEDESIGNATIONMAP on DONORCHALLENGEDESIGNATIONMAP.DESIGNATIONID = REVENUESPLIT.DESIGNATIONID and DONORCHALLENGEDESIGNATIONMAP.DONORCHALLENGEID=@DCID
left join dbo.DONORCHALLENGEMEMBERSHIPLEVELMAP on DONORCHALLENGEMEMBERSHIPLEVELMAP.MEMBERSHIPLEVELID = MEMBERSHIPTRANSACTION.MEMBERSHIPLEVELID and DONORCHALLENGEMEMBERSHIPLEVELMAP.DONORCHALLENGEID=@DCID
where DONORCHALLENGEENCUMBERED.METHODTYPECODE = 0 and DONORCHALLENGEENCUMBERED.STATUSTYPECODE = 0
and
(
(DONORCHALLENGEDESIGNATIONMAP.ID is null and DONORCHALLENGEMEMBERSHIPLEVELMAP.ID is null)
or
not (
REVENUE.DATE between @STARTDATE and @ENDDATE
and dbo.UFN_REVENUE_GETAMOUNTINCURRENCY(REVENUE.ID, @BASECURRENCYID) >= @MINGIFTAMOUNT
and not exists(select top 1 APPTYPESUB.ID from dbo.DONORCHALLENGEEXCLUDEDAPPLICATIONTYPE APPTYPESUB
where APPTYPESUB.DONORCHALLENGEID = @DCID and REVENUESPLIT.APPLICATIONCODE = APPTYPESUB.APPLICATIONCODE)
)
or (@HANDLEPLEDGES = 0 and REVENUE.TRANSACTIONTYPECODE = 1)
)
--Update DESIGNATION revenue that has been changed
--Payments
update DONORCHALLENGEENCUMBERED
set AMOUNT = case when @MAXMATCHPERGIFT > 0 and SUB.AMOUNT > @MAXMATCHPERGIFT then @MAXMATCHPERGIFT else SUB.AMOUNT end,
ORGANIZATIONAMOUNT = dbo.UFN_CURRENCY_CONVERT((case when @MAXMATCHPERGIFT > 0 and SUB.AMOUNT > @MAXMATCHPERGIFT then @MAXMATCHPERGIFT else SUB.AMOUNT end), DONORCHALLENGEENCUMBERED.ORGANIZATIONEXCHANGERATEID),
DESIGNATIONID = SUB.MATCHINGDESIGNATIONID
from dbo.DONORCHALLENGEENCUMBERED
inner join (select DONORCHALLENGEENCUMBERED.ID,
dbo.UFN_CURRENCY_ROUND(
case when @MATCHTYPECODE = 1 then
case when REVENUE.TRANSACTIONAMOUNT = 0 then 0
else dbo.UFN_CURRENCY_ROUND(REVENUE.RECEIPTAMOUNT/cast(REVENUE.TRANSACTIONAMOUNT As decimal(20,10))
* case
when @BASECURRENCYID = REVENUESPLIT.TRANSACTIONCURRENCYID
then REVENUESPLIT.TRANSACTIONAMOUNT
else dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY(REVENUESPLIT.ID, @BASECURRENCYID)
end, @BASECURRENCYDECIMALDIGITS, @BASECURRENCYROUNDINGTYPECODE)
end
else
case
when @BASECURRENCYID = REVENUESPLIT.TRANSACTIONCURRENCYID
then REVENUESPLIT.TRANSACTIONAMOUNT
else dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY(REVENUESPLIT.ID, @BASECURRENCYID)
end
end
* @MATCHINGFACTOR, @BASECURRENCYDECIMALDIGITS, @BASECURRENCYROUNDINGTYPECODE) AMOUNT,
DONORCHALLENGEDESIGNATIONMAP.MATCHINGDESIGNATIONID
from dbo.DONORCHALLENGEENCUMBERED
inner join dbo.REVENUESPLIT on REVENUESPLIT.ID = DONORCHALLENGEENCUMBERED.REVENUESPLITID and DONORCHALLENGEENCUMBERED.DONORCHALLENGEID = @DCID
inner join dbo.REVENUE on REVENUE.ID = REVENUESPLIT.REVENUEID
inner join dbo.DONORCHALLENGEDESIGNATIONMAP on DONORCHALLENGEDESIGNATIONMAP.DESIGNATIONID = REVENUESPLIT.DESIGNATIONID
where REVENUE.TRANSACTIONTYPECODE = 0
and DONORCHALLENGEDESIGNATIONMAP.DONORCHALLENGEID=@DCID
and DONORCHALLENGEENCUMBERED.METHODTYPECODE = 0 and DONORCHALLENGEENCUMBERED.STATUSTYPECODE = 0
and REVENUESPLIT.DATECHANGED > @LASTRUN) SUB on DONORCHALLENGEENCUMBERED.ID = SUB.ID;
--Pledges
update DONORCHALLENGEENCUMBERED
set AMOUNT = case when @MAXMATCHPERGIFT > 0 and SUB.AMOUNT > @MAXMATCHPERGIFT then @MAXMATCHPERGIFT else SUB.AMOUNT end,
ORGANIZATIONAMOUNT = dbo.UFN_CURRENCY_CONVERT((case when @MAXMATCHPERGIFT > 0 and SUB.AMOUNT > @MAXMATCHPERGIFT then @MAXMATCHPERGIFT else SUB.AMOUNT end), DONORCHALLENGEENCUMBERED.ORGANIZATIONEXCHANGERATEID),
DESIGNATIONID = SUB.MATCHINGDESIGNATIONID
from dbo.DONORCHALLENGEENCUMBERED
inner join (select DONORCHALLENGEENCUMBERED.ID,
round((select sum(dbo.UFN_INSTALLMENTSPLIT_GETAMOUNTINCURRENCY(ISP.ID, @BASECURRENCYID))
from dbo.INSTALLMENTSPLIT ISP
inner join dbo.INSTALLMENT I on ISP.INSTALLMENTID = I.ID
where ISP.PLEDGEID = REVENUE.ID and ISP.DESIGNATIONID = REVENUESPLIT.DESIGNATIONID
and I.DATE <= @PLEDGEEND)
, 2) AMOUNT,
DONORCHALLENGEDESIGNATIONMAP.MATCHINGDESIGNATIONID
from dbo.DONORCHALLENGEENCUMBERED
inner join dbo.REVENUESPLIT on REVENUESPLIT.ID = DONORCHALLENGEENCUMBERED.REVENUESPLITID and DONORCHALLENGEENCUMBERED.DONORCHALLENGEID = @DCID
inner join dbo.REVENUE on REVENUE.ID = REVENUESPLIT.REVENUEID
inner join dbo.DONORCHALLENGEDESIGNATIONMAP on DONORCHALLENGEDESIGNATIONMAP.DESIGNATIONID = REVENUESPLIT.DESIGNATIONID
where REVENUE.TRANSACTIONTYPECODE = 1
and DONORCHALLENGEDESIGNATIONMAP.DONORCHALLENGEID=@DCID
and DONORCHALLENGEENCUMBERED.METHODTYPECODE = 0 and DONORCHALLENGEENCUMBERED.STATUSTYPECODE = 0
and REVENUESPLIT.DATECHANGED > @LASTRUN) SUB on DONORCHALLENGEENCUMBERED.ID = SUB.ID;
--Update MEMBERSHIP revenue that has been changed
update DONORCHALLENGEENCUMBERED
set AMOUNT = case when @MAXMATCHPERGIFT > 0 and SUB.AMOUNT > @MAXMATCHPERGIFT then @MAXMATCHPERGIFT else SUB.AMOUNT end,
ORGANIZATIONAMOUNT = dbo.UFN_CURRENCY_CONVERT((case when @MAXMATCHPERGIFT > 0 and SUB.AMOUNT > @MAXMATCHPERGIFT then @MAXMATCHPERGIFT else SUB.AMOUNT end), DONORCHALLENGEENCUMBERED.ORGANIZATIONEXCHANGERATEID),
DESIGNATIONID = SUB.MATCHINGDESIGNATIONID
from dbo.DONORCHALLENGEENCUMBERED
inner join (select DONORCHALLENGEENCUMBERED.ID,
dbo.UFN_CURRENCY_ROUND(case when @MATCHTYPECODE = 1 then
case when REVENUE.TRANSACTIONAMOUNT = 0 then 0
else dbo.UFN_CURRENCY_ROUND(REVENUE.RECEIPTAMOUNT/cast(REVENUE.TRANSACTIONAMOUNT As decimal(20,10))
* case
when @BASECURRENCYID = REVENUESPLIT.TRANSACTIONCURRENCYID
then REVENUESPLIT.TRANSACTIONAMOUNT
else dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY(REVENUESPLIT.ID, @BASECURRENCYID)
end, @BASECURRENCYDECIMALDIGITS, @BASECURRENCYROUNDINGTYPECODE)
end
else
case
when @BASECURRENCYID = REVENUESPLIT.TRANSACTIONCURRENCYID
then REVENUESPLIT.TRANSACTIONAMOUNT
else dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY(REVENUESPLIT.ID, @BASECURRENCYID)
end
end
* @MATCHINGFACTOR, @BASECURRENCYDECIMALDIGITS, @BASECURRENCYROUNDINGTYPECODE) AMOUNT,
DONORCHALLENGEMEMBERSHIPLEVELMAP.MATCHINGDESIGNATIONID
from dbo.DONORCHALLENGEENCUMBERED
inner join dbo.REVENUESPLIT on REVENUESPLIT.ID = DONORCHALLENGEENCUMBERED.REVENUESPLITID and DONORCHALLENGEENCUMBERED.DONORCHALLENGEID = @DCID
inner join dbo.REVENUE on REVENUE.ID = REVENUESPLIT.REVENUEID
inner join dbo.MEMBERSHIPTRANSACTION on MEMBERSHIPTRANSACTION.REVENUESPLITID = REVENUESPLIT.ID
inner join dbo.DONORCHALLENGEMEMBERSHIPLEVELMAP on DONORCHALLENGEMEMBERSHIPLEVELMAP.MEMBERSHIPLEVELID = MEMBERSHIPTRANSACTION.MEMBERSHIPLEVELID
where DONORCHALLENGEMEMBERSHIPLEVELMAP.DONORCHALLENGEID=@DCID and
DONORCHALLENGEENCUMBERED.METHODTYPECODE = 0 and DONORCHALLENGEENCUMBERED.STATUSTYPECODE = 0
and REVENUESPLIT.DATECHANGED > @LASTRUN) SUB on DONORCHALLENGEENCUMBERED.ID = SUB.ID;
/* todo fix any overruns */
end
select @ENCUMBERED = coalesce(dbo.UFN_DONORCHALLENGE_ENCUMBEREDAMOUNT(@DCID), 0) + coalesce(dbo.UFN_DONORCHALLENGE_MATCHEDAMOUNT(@DCID), 0);
if object_id('tempdb..#PROSPECTIVEREVENUE') is not null
drop table #PROSPECTIVEREVENUE
create table #PROSPECTIVEREVENUE
(REVENUESPLITID uniqueidentifier,
AMOUNT money,
DESIGNATIONID uniqueidentifier,
DATE datetime,
DATEADDED datetime
)
--Add regular payments
insert into #PROSPECTIVEREVENUE
(REVENUESPLITID, AMOUNT, DESIGNATIONID, DATE, DATEADDED)
select REVENUESPLIT.ID,
case when @MATCHTYPECODE = 1 then
case when REVENUE.AMOUNT = 0 then 0
else dbo.UFN_CURRENCY_ROUND(REVENUE.RECEIPTAMOUNT/cast(REVENUE.TRANSACTIONAMOUNT As decimal(20,10))
* case
when @BASECURRENCYID = REVENUESPLIT.TRANSACTIONCURRENCYID
then REVENUESPLIT.TRANSACTIONAMOUNT
else dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY(REVENUESPLIT.ID, @BASECURRENCYID)
end, @BASECURRENCYDECIMALDIGITS, @BASECURRENCYROUNDINGTYPECODE)
end
else
case
when @BASECURRENCYID = REVENUESPLIT.TRANSACTIONCURRENCYID
then REVENUESPLIT.TRANSACTIONAMOUNT
else dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY(REVENUESPLIT.ID, @BASECURRENCYID)
end
end,
DONORCHALLENGEDESIGNATIONMAP.MATCHINGDESIGNATIONID,
REVENUE.DATE,
REVENUE.DATEADDED
from dbo.REVENUESPLIT
inner join dbo.REVENUE on REVENUE.ID = REVENUESPLIT.REVENUEID
inner join dbo.DONORCHALLENGEDESIGNATIONMAP on DONORCHALLENGEDESIGNATIONMAP.DESIGNATIONID = REVENUESPLIT.DESIGNATIONID
left join dbo.DONORCHALLENGEENCUMBERED on REVENUESPLIT.ID = DONORCHALLENGEENCUMBERED.REVENUESPLITID and DONORCHALLENGEENCUMBERED.DONORCHALLENGEID = @DCID
where REVENUE.TRANSACTIONTYPECODE = 0
and DONORCHALLENGEDESIGNATIONMAP.DONORCHALLENGEID=@DCID
and DONORCHALLENGEENCUMBERED.ID is null
and REVENUESPLIT.APPLICATIONCODE in (0, 3, 4, 6, 7, 8, 10)
and REVENUE.DATE between @STARTDATE and @ENDDATE
and REVENUE.AMOUNT >= @MINGIFTAMOUNT
and not exists(select top 1 APPTYPESUB.ID from dbo.DONORCHALLENGEEXCLUDEDAPPLICATIONTYPE APPTYPESUB
where APPTYPESUB.DONORCHALLENGEID = @DCID and REVENUESPLIT.APPLICATIONCODE = APPTYPESUB.APPLICATIONCODE)
--Add pledges
if @HANDLEPLEDGES = 1
insert into #PROSPECTIVEREVENUE
(REVENUESPLITID, AMOUNT, DESIGNATIONID, DATE, DATEADDED)
select
REVENUESPLIT.ID,
(
coalesce((select sum(
case
when @BASECURRENCYID = ISP.TRANSACTIONCURRENCYID
then ISP.TRANSACTIONAMOUNT
else dbo.UFN_INSTALLMENTSPLIT_GETAMOUNTINCURRENCY(ISP.ID, @BASECURRENCYID)
end
)
from dbo.INSTALLMENTSPLIT ISP
inner join dbo.INSTALLMENT I on ISP.INSTALLMENTID = I.ID
where ISP.PLEDGEID = REVENUE.ID and ISP.DESIGNATIONID = REVENUESPLIT.DESIGNATIONID
and I.DATE <= @PLEDGEEND), 0)
-
coalesce((select sum(
case
when @BASECURRENCYID = ISW.TRANSACTIONCURRENCYID
then ISW.TRANSACTIONAMOUNT
else dbo.UFN_INSTALLMENTSPLITWRITEOFF_GETAMOUNTINCURRENCY(ISW.ID, @BASECURRENCYID)
end
)
from INSTALLMENTSPLITWRITEOFF ISW
inner join dbo.INSTALLMENTSPLIT ISP on ISW.INSTALLMENTSPLITID = ISP.ID
inner join dbo.INSTALLMENT I on ISP.INSTALLMENTID = I.ID
where ISP.PLEDGEID = REVENUE.ID and ISP.DESIGNATIONID = REVENUESPLIT.DESIGNATIONID
and I.DATE <= @PLEDGEEND), 0)
),
DONORCHALLENGEDESIGNATIONMAP.MATCHINGDESIGNATIONID,
REVENUE.DATE,
REVENUE.DATEADDED
from dbo.REVENUESPLIT
inner join dbo.REVENUE on REVENUE.ID = REVENUESPLIT.REVENUEID
inner join dbo.DONORCHALLENGEDESIGNATIONMAP on DONORCHALLENGEDESIGNATIONMAP.DESIGNATIONID = REVENUESPLIT.DESIGNATIONID
left join dbo.DONORCHALLENGEENCUMBERED on REVENUESPLIT.ID = DONORCHALLENGEENCUMBERED.REVENUESPLITID and DONORCHALLENGEENCUMBERED.DONORCHALLENGEID = @DCID
where REVENUE.TRANSACTIONTYPECODE = 1
and DONORCHALLENGEDESIGNATIONMAP.DONORCHALLENGEID=@DCID
and DONORCHALLENGEENCUMBERED.ID is null
and REVENUE.DATE between @STARTDATE and @ENDDATE
and REVENUE.AMOUNT >= @MINGIFTAMOUNT
--Add Membership payments
insert into #PROSPECTIVEREVENUE
(REVENUESPLITID, AMOUNT, DESIGNATIONID, DATE, DATEADDED)
select REVENUESPLIT.ID,
case when @MATCHTYPECODE = 1 then
case when REVENUE.TRANSACTIONAMOUNT = 0 then 0
else dbo.UFN_CURRENCY_ROUND(REVENUE.RECEIPTAMOUNT/cast(REVENUE.TRANSACTIONAMOUNT As decimal(20,10))
* case
when @BASECURRENCYID = REVENUESPLIT.TRANSACTIONCURRENCYID
then REVENUESPLIT.TRANSACTIONAMOUNT
else dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY(REVENUESPLIT.ID, @BASECURRENCYID)
end, @BASECURRENCYDECIMALDIGITS, @BASECURRENCYROUNDINGTYPECODE)
end
else
case
when @BASECURRENCYID = REVENUESPLIT.TRANSACTIONCURRENCYID
then REVENUESPLIT.TRANSACTIONAMOUNT
else dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY(REVENUESPLIT.ID, @BASECURRENCYID)
end
end,
DONORCHALLENGEMEMBERSHIPLEVELMAP.MATCHINGDESIGNATIONID,
REVENUE.DATE,
REVENUE.DATEADDED
from dbo.REVENUESPLIT
inner join dbo.REVENUE on REVENUE.ID = REVENUESPLIT.REVENUEID
inner join dbo.MEMBERSHIPTRANSACTION on MEMBERSHIPTRANSACTION.REVENUESPLITID = REVENUESPLIT.ID
inner join dbo.DONORCHALLENGEMEMBERSHIPLEVELMAP on DONORCHALLENGEMEMBERSHIPLEVELMAP.MEMBERSHIPLEVELID = MEMBERSHIPTRANSACTION.MEMBERSHIPLEVELID
left join dbo.DONORCHALLENGEENCUMBERED on REVENUESPLIT.ID = DONORCHALLENGEENCUMBERED.REVENUESPLITID and DONORCHALLENGEENCUMBERED.DONORCHALLENGEID = @DCID
where REVENUE.TRANSACTIONTYPECODE = 0
and DONORCHALLENGEMEMBERSHIPLEVELMAP.DONORCHALLENGEID=@DCID
and DONORCHALLENGEENCUMBERED.ID is null
and REVENUESPLIT.APPLICATIONCODE = 5
and REVENUE.DATE between @STARTDATE and @ENDDATE
and REVENUE.AMOUNT >= @MINGIFTAMOUNT
and not exists(select top 1 APPTYPESUB.ID from dbo.DONORCHALLENGEEXCLUDEDAPPLICATIONTYPE APPTYPESUB
where APPTYPESUB.DONORCHALLENGEID = @DCID and REVENUESPLIT.APPLICATIONCODE = APPTYPESUB.APPLICATIONCODE)
--Loop through new revenue.
--Add one at a time to make sure we don't go over our limit.
declare @ORGANIZATIONCURRENCYID uniqueidentifier = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();
declare @ORGANIZATIONEXCHANGERATEID uniqueidentifier;
declare @ORGANIZATIONAMOUNT money;
declare AVAILABLEREVENUE cursor local fast_forward for
select REVENUESPLITID, AMOUNT, DESIGNATIONID, DATEADDED
from #PROSPECTIVEREVENUE
order by DATE, DATEADDED
open AVAILABLEREVENUE
fetch next from AVAILABLEREVENUE into @REVENUESPLITID, @AMOUNT, @DESIGNATIONID, @DATEADDED;
while (@@FETCH_STATUS = 0 and @ENCUMBERED < @TOTALFUNDS)
begin
set @AMOUNT = round(@AMOUNT * @MATCHINGFACTOR, 2)
if @MAXMATCHPERGIFT > 0 and @AMOUNT > @MAXMATCHPERGIFT
set @AMOUNT = @MAXMATCHPERGIFT;
if @AMOUNT > (@TOTALFUNDS - @ENCUMBERED)
set @AMOUNT = (@TOTALFUNDS - @ENCUMBERED);
set @ORGANIZATIONEXCHANGERATEID = null;
set @ORGANIZATIONEXCHANGERATEID = dbo.UFN_CURRENCYEXCHANGERATE_GETLATEST(@BASECURRENCYID, @ORGANIZATIONCURRENCYID, @DATEADDED, 0, null);
set @ORGANIZATIONAMOUNT = 0;
set @ORGANIZATIONAMOUNT = dbo.UFN_CURRENCY_CONVERT(@AMOUNT, @ORGANIZATIONEXCHANGERATEID);
insert into DONORCHALLENGEENCUMBERED(
DONORCHALLENGEID,
REVENUESPLITID,
DESIGNATIONID,
METHODTYPECODE,
AMOUNT,
REVENUERECOGNITIONTYPECODEID,
BASECURRENCYID,
ORGANIZATIONAMOUNT,
ORGANIZATIONEXCHANGERATEID,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
) values (
@DCID,
@REVENUESPLITID,
@DESIGNATIONID,
0,
@AMOUNT,
@REVENUERECOGNITIONTYPECODEID,
@BASECURRENCYID,
@ORGANIZATIONAMOUNT,
@ORGANIZATIONEXCHANGERATEID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@NOW,
@NOW
)
set @ENCUMBERED = @ENCUMBERED + @AMOUNT;
fetch next from AVAILABLEREVENUE into @REVENUESPLITID, @AMOUNT, @DESIGNATIONID, @DATEADDED;
end
close AVAILABLEREVENUE
deallocate AVAILABLEREVENUE
update DONORCHALLENGE
set PROCESSLASTRUN = @NOW,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @NOW
where ID = @DCID;
end