USP_REVENUE_UPDATEREVENUESTREAMS
Stored proc to update the revenue streams on a payment.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @REVENUEID | uniqueidentifier | IN | |
| @CONSTITUENTID | uniqueidentifier | IN | |
| @DATE | datetime | IN | |
| @REVENUESTREAMS | xml | IN | |
| @CHANGEAGENTID | uniqueidentifier | IN | |
| @CHANGEDATE | datetime | IN | |
| @SPLITSDECLININGGIFTAID | xml | INOUT | |
| @ORIGINALGIVENANONYMOUSLY | bit | IN | |
| @GIFTAIDSPONSORSHIPSPLITS | xml | INOUT | |
| @PREVIOUSDATE | datetime | IN | |
| @PREVIOUSDONOTRECEIPT | bit | IN | |
| @PREVIOUSRECEIPTAMOUNT | money | IN |
Definition
Copy
CREATE procedure dbo.USP_REVENUE_UPDATEREVENUESTREAMS
(
@REVENUEID uniqueidentifier,
@CONSTITUENTID uniqueidentifier,
@DATE datetime,
@REVENUESTREAMS xml,
@CHANGEAGENTID uniqueidentifier,
@CHANGEDATE datetime,
@SPLITSDECLININGGIFTAID xml = null output,
@ORIGINALGIVENANONYMOUSLY bit = null,
@GIFTAIDSPONSORSHIPSPLITS xml = null output,
@PREVIOUSDATE datetime = null,
@PREVIOUSDONOTRECEIPT bit = null,
@PREVIOUSRECEIPTAMOUNT money = null
)
as
set NOCOUNT on;
declare @ID uniqueidentifier;
declare @APPLICATIONID uniqueidentifier;
declare @APPLICATIONTYPE tinyint;
declare @APPLIEDAMOUNT money;
declare @APPLICATIONCODE tinyint;
declare @GIFTFIELDS xml;
declare @OTHERFIELDS xml;
declare @DECLINESGIFTAID bit;
declare @ISGIFTAIDSPONSORSHIP bit;
declare @ORIGINALSPLITAMOUNT money;
declare @BASEAPPLIEDAMOUNT money;
declare @ORGANIZATIONAPPLIEDAMOUNT money;
declare @REVENUETYPECODE tinyint;
declare @DESIGNATIONID uniqueidentifier;
declare @OPPORTUNITYID uniqueidentifier;
declare @OTHERTYPECODEID uniqueidentifier;
declare @CATEGORYCODEID uniqueidentifier;
declare @CAMPAIGNS xml;
declare @SOLICITORS xml;
declare @RECOGNITIONCREDITS xml;
declare @UPDATERECOGNITIONOPTION tinyint;
declare @UPDATESOLICITORSOPTION tinyint;
declare @CONTEXTCACHE varbinary(128);
declare @PAYMENTBASECURRENCYID uniqueidentifier;
declare @PAYMENTTRANSACTIONCURRENCYID uniqueidentifier;
declare @PAYMENTBASEEXCHANGERATEID uniqueidentifier;
declare @PAYMENTORGANIZATIONEXCHANGERATEID uniqueidentifier;
declare @PAYMENTRANSACTIONCURRENCYDECIMALDIGITS tinyint;
declare @APPLICATIONCURRENCYID uniqueidentifier;
declare @CROSSCURRENCYAPPLICATIONEXCHANGERATECANCHANGE bit;
declare @APPLIEDAMOUNTCONVERTED money;
declare @CURRENTDATE datetime = getdate();
declare @PLANNEDGIFTUPDATES table
(
PLANNEDGIFTID uniqueidentifier,
PLANNEDGIFTADDITIONID uniqueidentifier,
OLDAMOUNT money,
NEWAMOUNT money,
OLDDATE datetime,
NEWDATE datetime,
OLDTRANSACTIONAMOUNT money,
NEWTRANSACTIONAMOUNT money,
BASECURRENCYID uniqueidentifier,
TRANSACTIONCURRENCYID uniqueidentifier
)
declare @REGISTRANTIDS table
(
ID uniqueidentifier
);
declare @ORGANIZATIONCURRENCYID uniqueidentifier = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();
if @PREVIOUSDATE is null
set @PREVIOUSDATE = @DATE;
if @PREVIOUSDATE = @DATE
set @CROSSCURRENCYAPPLICATIONEXCHANGERATECANCHANGE = 0;
else
set @CROSSCURRENCYAPPLICATIONEXCHANGERATECANCHANGE = 1;
select
@PAYMENTBASECURRENCYID = CS.BASECURRENCYID,
@PAYMENTTRANSACTIONCURRENCYID = FT.TRANSACTIONCURRENCYID,
@PAYMENTBASEEXCHANGERATEID = FT.BASEEXCHANGERATEID,
@PAYMENTORGANIZATIONEXCHANGERATEID = FT.ORGEXCHANGERATEID,
@PAYMENTRANSACTIONCURRENCYDECIMALDIGITS = CURRENCY.DECIMALDIGITS
from dbo.FINANCIALTRANSACTION FT
inner join dbo.PDACCOUNTSYSTEM PD on PD.ID = FT.PDACCOUNTSYSTEMID
inner join dbo.CURRENCYSET CS on CS.ID = PD.CURRENCYSETID
inner join dbo.CURRENCY on FT.TRANSACTIONCURRENCYID = CURRENCY.ID
where
FT.ID = @REVENUEID;
--We must swap out auction packages for all of the auction items within the package
--auction packages have no revenue records (just an empty shell), but their items are splits
exec dbo.USP_REVENUE_UPDATEAPPLICATIONS_AUCTIONPACKAGES @REVENUEID, @REVENUESTREAMS output
set @REVENUESTREAMS = dbo.UFN_REVENUE_REVENUESTREAMS_CONVERTAMOUNTSINXML(@REVENUESTREAMS, @PAYMENTBASECURRENCYID, @PAYMENTORGANIZATIONEXCHANGERATEID, @PAYMENTTRANSACTIONCURRENCYID, @PAYMENTBASEEXCHANGERATEID);
declare UPDATECURSOR cursor local fast_forward for
select
[NEW].ID,
[NEW].APPLICATIONID,
[NEW].APPLIED,
[NEW].APPLICATIONCODE,
[NEW].GIFTFIELDS,
[NEW].OTHERFIELDS,
[NEW].DECLINESGIFTAID,
[NEW].ISSPONSORSHIP,
[OLD].TRANSACTIONAMOUNT,
[NEW].UPDATERECOGNITIONOPTION,
[NEW].APPLIEDBASEAMOUNT,
[NEW].APPLIEDORGANIZATIONAMOUNT,
[NEW].UPDATESOLICITORSOPTION,
[NEW].CATEGORYCODEID
from dbo.UFN_REVENUE_GETAPPLICATIONS_FROMXML(@REVENUESTREAMS) as [NEW]
inner join dbo.FINANCIALTRANSACTIONLINEITEM as [OLD] on [NEW].ID = [OLD].ID
inner join dbo.REVENUESPLIT_EXT on [OLD].ID = REVENUESPLIT_EXT.ID
where [OLD].DELETEDON is null and [OLD].TYPECODE != 1;
/* cache current context information */
set @CONTEXTCACHE = CONTEXT_INFO();
/* set CONTEXT_INFO to @CHANGEAGENTID */
set CONTEXT_INFO @CHANGEAGENTID;
declare @DELETEDIDS table
(
ID uniqueidentifier
);
--Delete revenue
declare @NEWREVENUESTREAM table
(
ID uniqueidentifier
);
insert into @NEWREVENUESTREAM (ID)
select ID from dbo.UFN_REVENUE_GETAPPLICATIONS_FROMXML(@REVENUESTREAMS)
insert into @DELETEDIDS
select LI.ID
from dbo.FINANCIALTRANSACTION FT
inner join dbo.FINANCIALTRANSACTIONLINEITEM LI on FT.ID = LI.FINANCIALTRANSACTIONID
inner join dbo.REVENUESPLIT_EXT on REVENUESPLIT_EXT.ID = LI.ID
where FT.ID = @REVENUEID and LI.DELETEDON is null and LI.TYPECODE != 1
--Using table variable since a poor plan is chosen when using the XML function UFN_REVENUE_GETAPPLICATIONS_FROMXML
EXCEPT select ID from @NEWREVENUESTREAM;
--Gift Aid is for UK only
declare @PRODUCTISUK bit = dbo.UFN_INSTALLEDPRODUCTS_PRODUCTIS('9568A6C2-F7AA-45fd-8F54-21FE9654EE2D');
declare @GBPCURRENCYID uniqueidentifier = (select CURRENCY.ID from dbo.CURRENCY where CURRENCY.ISO4217 = 'GBP');
if @PRODUCTISUK = 1
begin
declare @DELETEDIDSXML xml = (
select ID
from @DELETEDIDS
for xml raw('ITEM')
,type
,elements
,root('DELETEDIDS')
,binary BASE64
);
--We need to cache the deleted/changed split information in order potentially refund it later in this SP after the splits have been
--actually deleted or updated. We have to wait until after the new splits are created (to calculate CCRN) in order determine if the deleted/changed splits should be refunded.
declare @DELETEDANDCHANGEDSPLITSINFO xml ;
exec USP_GETGIFTAIDSPLITSTOCACHE @REVENUEID, @ORGANIZATIONCURRENCYID, @DELETEDIDSXML, @DELETEDANDCHANGEDSPLITSINFO output
end
--JamesWill 03/23/2006 CR237947-031406PROD
--Ensure that no DELETEDIDS have MG Pledges linked to it
if (select count([DEL].ID)
from @DELETEDIDS as [DEL]
inner join dbo.REVENUEMATCHINGGIFT as [RMG] on [RMG].MGSOURCEREVENUEID = [DEL].ID ) > 0
begin
raiserror('All applications with matching gift claims must have the matching gift claim removed before deleting the gift.', 13, 1);
return 0;
end
-- update RG installments
declare @RGAPPLICATIONS table (APPLICATIONID uniqueidentifier,
APPLIED money,
PREVIOUSAPPLIED money,
ISDELETED bit,
APPLICATIONEXCHANGERATEID uniqueidentifier,
PREVIOUSAPPLIEDCONVERTED money,
EFFECTIVEDATE datetime,
TRANSACTIONCURRENCYID uniqueidentifier,
BASECURRENCYID uniqueidentifier,
BASEEXCHANGERATEID uniqueidentifier,
ORGANIZATIONEXCHANGERATEID uniqueidentifier);
with APPS
as
(
select
NEW.APPLICATIONID,
NEW.APPLIED,
OLD.TRANSACTIONAMOUNT PREVIOUSAPPLIED,
0 ISDELETED,
(
select
RECURRINGGIFTACTIVITY.AMOUNT
from
dbo.RECURRINGGIFTACTIVITY
where
RECURRINGGIFTACTIVITY.SOURCEREVENUEID = NEW.APPLICATIONID
and RECURRINGGIFTACTIVITY.PAYMENTREVENUEID = NEW.ID
) PREVIOUSAPPLIEDCONVERTED,
OLD.DATEADDED EFFECTIVEDATE
from
dbo.UFN_REVENUE_GETAPPLICATIONS_FROMXML(@REVENUESTREAMS) NEW
inner join dbo.FINANCIALTRANSACTIONLINEITEM OLD on OLD.ID = NEW.ID
inner join dbo.REVENUESPLIT_EXT on [OLD].ID = REVENUESPLIT_EXT.ID
where NEW.APPLICATIONCODE = 3 and [OLD].DELETEDON is null and [OLD].TYPECODE != 1
union all
select
RECURRINGGIFTACTIVITY.SOURCEREVENUEID,
0,
RECURRINGGIFTACTIVITY.AMOUNT,
1,
RECURRINGGIFTACTIVITY.AMOUNT,
null -- effectivedate not needed for deleted splits
from
dbo.RECURRINGGIFTACTIVITY
where
PAYMENTREVENUEID in (select ID from @DELETEDIDS)
),
APPLICATIONEXCHANGERATE
as
(
select
REVENUE.ID [REVENUEID],
case
when (REVENUE.TRANSACTIONCURRENCYID = @PAYMENTTRANSACTIONCURRENCYID)
then 0
else
1
end [APPLICATIONEXCHANGERATENEEDED],
--Same logic as USP_REVENUE_GETUPDATEDAPPLICATIONEXCHANGERATE
case
when (REVENUE.TRANSACTIONCURRENCYID = @PAYMENTTRANSACTIONCURRENCYID)
then null
when (REVENUE.TRANSACTIONCURRENCYID = @PAYMENTBASECURRENCYID)
then @PAYMENTBASEEXCHANGERATEID
when (@CROSSCURRENCYAPPLICATIONEXCHANGERATECANCHANGE = 0)
--Don't allow a change in cross currency application exchange rates (because the revenue date didn't change
-- and it's a system-selected rate). All application exchange rates on installment payments made by this
-- payment against this recurring gift should already have the same rate, so just select the top 1.
then
(
select top (1)
RECURRINGGIFTINSTALLMENTPAYMENT.APPLICATIONEXCHANGERATEID
from
dbo.RECURRINGGIFTINSTALLMENT
inner join dbo.RECURRINGGIFTINSTALLMENTPAYMENT on RECURRINGGIFTINSTALLMENT.ID = RECURRINGGIFTINSTALLMENTPAYMENT.RECURRINGGIFTINSTALLMENTID
where
RECURRINGGIFTINSTALLMENTPAYMENT.PAYMENTID = @REVENUEID
and RECURRINGGIFTINSTALLMENT.REVENUEID = REVENUE.ID
)
else
dbo.UFN_CURRENCYEXCHANGERATE_GETLATEST(@PAYMENTTRANSACTIONCURRENCYID,REVENUE.TRANSACTIONCURRENCYID,@DATE,0,null)
end [APPLICATIONEXCHANGERATEID],
REVENUE.TRANSACTIONCURRENCYID,
REVENUE_EXT.NONPOSTABLEBASECURRENCYID BASECURRENCYID,
REVENUE.BASEEXCHANGERATEID,
REVENUE.ORGEXCHANGERATEID ORGANIZATIONEXCHANGERATEID
from dbo.FINANCIALTRANSACTION REVENUE
inner join dbo.REVENUE_EXT on REVENUE_EXT.ID = REVENUE.ID
where REVENUE.ID in (select APPS.APPLICATIONID from APPS)
and REVENUE.TYPECODE in (0,1,2,3,4,5,6,7,8,9,15) and REVENUE.DELETEDON is null
)
insert into @RGAPPLICATIONS
select
APPS.APPLICATIONID,
sum(APPS.APPLIED),
sum(APPS.PREVIOUSAPPLIED),
min(APPS.ISDELETED),
APPLICATIONEXCHANGERATE.APPLICATIONEXCHANGERATEID,
sum(APPS.PREVIOUSAPPLIEDCONVERTED),
min(APPS.EFFECTIVEDATE),
APPLICATIONEXCHANGERATE.TRANSACTIONCURRENCYID,
APPLICATIONEXCHANGERATE.BASECURRENCYID,
APPLICATIONEXCHANGERATE.BASEEXCHANGERATEID,
APPLICATIONEXCHANGERATE.ORGANIZATIONEXCHANGERATEID
from
APPS
inner join APPLICATIONEXCHANGERATE on APPS.APPLICATIONID = APPLICATIONEXCHANGERATE.REVENUEID
group by
APPS.APPLICATIONID,
APPLICATIONEXCHANGERATE.APPLICATIONEXCHANGERATEID,
APPLICATIONEXCHANGERATE.TRANSACTIONCURRENCYID,
APPLICATIONEXCHANGERATE.BASECURRENCYID,
APPLICATIONEXCHANGERATE.BASEEXCHANGERATEID,
APPLICATIONEXCHANGERATE.ORGANIZATIONEXCHANGERATEID
having
sum(APPLIED) <> sum(PREVIOUSAPPLIED)
or min(ISDELETED) = 1
or
(
max(APPLICATIONEXCHANGERATE.APPLICATIONEXCHANGERATENEEDED) = 1
and
(
APPLICATIONEXCHANGERATE.APPLICATIONEXCHANGERATEID is null
or exists
(
select
1
from
dbo.RECURRINGGIFTINSTALLMENT
inner join dbo.RECURRINGGIFTINSTALLMENTPAYMENT on RECURRINGGIFTINSTALLMENT.ID = RECURRINGGIFTINSTALLMENTPAYMENT.RECURRINGGIFTINSTALLMENTID
where
RECURRINGGIFTINSTALLMENTPAYMENT.PAYMENTID = @REVENUEID
and RECURRINGGIFTINSTALLMENT.REVENUEID = APPS.APPLICATIONID
and RECURRINGGIFTINSTALLMENTPAYMENT.APPLICATIONEXCHANGERATEID <> APPLICATIONEXCHANGERATE.APPLICATIONEXCHANGERATEID
)
)
);
if @@ROWCOUNT > 0
begin
declare @RGID uniqueidentifier,
@APPLIED money,
@PREVIOUSAPPLIED money,
@ISDELETED bit,
@APPLICATIONEXCHANGERATEID uniqueidentifier,
@PREVIOUSAPPLIEDCONVERTED money,
@APPLICATIONEFFECTIVEDATETIME datetime,
@APPLICATIONEFFECTIVEDATE date,
@RGTRANSACTIONCURRENCYID uniqueidentifier,
@RGBASECURRENCYID uniqueidentifier,
@RGBASEEXCHANGERATEID uniqueidentifier,
@RGORGANIZATIONEXCHANGERATEID uniqueidentifier;
declare RGCURSOR cursor local fast_forward for
select *
from @RGAPPLICATIONS;
open RGCURSOR;
fetch next from RGCURSOR into @RGID, @APPLIED, @PREVIOUSAPPLIED, @ISDELETED, @APPLICATIONEXCHANGERATEID, @PREVIOUSAPPLIEDCONVERTED, @APPLICATIONEFFECTIVEDATETIME, @RGTRANSACTIONCURRENCYID, @RGBASECURRENCYID, @RGBASEEXCHANGERATEID, @RGORGANIZATIONEXCHANGERATEID;
while @@FETCH_STATUS = 0
begin
set @APPLICATIONEFFECTIVEDATE = @APPLICATIONEFFECTIVEDATETIME;
select
@APPLICATIONCURRENCYID = REVENUE.TRANSACTIONCURRENCYID
from dbo.FINANCIALTRANSACTION REVENUE
inner join dbo.REVENUE_EXT on REVENUE_EXT.ID = REVENUE.ID
where REVENUE.ID = @RGID
and REVENUE.TYPECODE in (0,1,2,3,4,5,6,7,8,9,15) and REVENUE.DELETEDON is null;
if @APPLICATIONCURRENCYID <> @PAYMENTTRANSACTIONCURRENCYID
begin
if @APPLICATIONEXCHANGERATEID is null
begin
raiserror('BBERR_APPLICATIONEXCHANGERATEDOESNOTEXIST : A payment can only be applied to an application if the payment and application have the same transaction currency or if there is an exchange rate from the payment transaction currency to the application transaction currency.', 13, 1);
return 1;
end
end
if @APPLICATIONEXCHANGERATEID is null
set @APPLIEDAMOUNTCONVERTED = @APPLIED;
else
set @APPLIEDAMOUNTCONVERTED = dbo.UFN_CURRENCY_CONVERT(@APPLIED, @APPLICATIONEXCHANGERATEID);
if @ISDELETED = 1
begin
exec dbo.USP_RECURRINGGIFT_UNDOINSTALLMENTCHANGESONDELETEPAYMENT
@PAYMENTID = @REVENUEID,
@RECURRINGGIFTID = @RGID,
@CHANGEAGENTID = @CHANGEAGENTID,
@CURRENTDATE = @CURRENTDATE
-- remove all money applied to the RG
delete from dbo.RECURRINGGIFTINSTALLMENTPAYMENT
where RECURRINGGIFTINSTALLMENTID in(select ID from dbo.RECURRINGGIFTINSTALLMENT where REVENUEID = @RGID)
and PAYMENTID = @REVENUEID;
exec dbo.USP_RECURRINGGIFT_DELETEFUTUREINSTALLMENTS @RGID, @CHANGEAGENTID;
exec dbo.USP_RECURRINGGIFT_UPDATEREVENUESCHEDULE
@REVENUEID = @RGID,
@STATUSCHANGETYPECODE = 2,
@CHANGEAGENTID = @CHANGEAGENTID,
@CURRENTDATETIME = @CURRENTDATE;
end
else if @APPLIEDAMOUNTCONVERTED > @PREVIOUSAPPLIEDCONVERTED
begin
declare @ADDAMOUNT money;
set @ADDAMOUNT = @APPLIED - @PREVIOUSAPPLIED;
declare @NEXTTRANSACTIONDATE date;
-- apply additional amount
exec dbo.USP_RECURRINGGIFT_APPLYPAYMENTTOINSTALLMENTS
@RGID,
@CHANGEAGENTID,
@CHANGEDATE,
@REVENUEID,
@ADDAMOUNT,
@NEXTTRANSACTIONDATE output,
@APPLICATIONCURRENCYID,
@APPLICATIONEXCHANGERATEID,
@PREVIOUSAPPLIED,
@PREVIOUSAPPLIEDCONVERTED,
@DATE,
@APPLICATIONEFFECTIVEDATETIME
end
else
begin
-- remove subtracted amount
-- payment handling rules
declare @FIRSTINSTALLMENTCODE tinyint,
@INSTALLMENTUNDERPAYMENTCODE tinyint,
@APPLYTOPASTINSTALLMENTS bit,
@PASTBALANCEUNDERPAYMENTCODE tinyint,
@OVERPAYMENTCODE tinyint;
if dbo.UFN_RECURRINGGIFT_ISMEMBERSHIP(@RGID) = 1
select @FIRSTINSTALLMENTCODE = 0, --oldest first
@INSTALLMENTUNDERPAYMENTCODE = 0, --leave balance
@APPLYTOPASTINSTALLMENTS = 1,
@PASTBALANCEUNDERPAYMENTCODE = 0, --leave balance
@OVERPAYMENTCODE = 0 --apply to future
else
select @FIRSTINSTALLMENTCODE = FIRSTINSTALLMENTCODE, --0=oldest first, 1=most recent first
@INSTALLMENTUNDERPAYMENTCODE = INSTALLMENTUNDERPAYMENTCODE, --0=leave balance, 1=write-off, 2=adjust
@APPLYTOPASTINSTALLMENTS = APPLYTOPASTINSTALLMENTS,
@PASTBALANCEUNDERPAYMENTCODE = PASTBALANCEUNDERPAYMENTCODE, --0=leave balance, 1=write-off, 2=adjust
@OVERPAYMENTCODE = OVERPAYMENTCODE --0=apply to future, 1=adjust, 2=donation(handled at a higher level)
from dbo.UFN_RECURRINGGIFTSETTING_GETFOREFFECTIVEDATE(@APPLICATIONEFFECTIVEDATETIME);
declare @REMOVEAMOUNTCONVERTED money;
set @REMOVEAMOUNTCONVERTED = @PREVIOUSAPPLIEDCONVERTED - @APPLIEDAMOUNTCONVERTED;
declare @RGIPID uniqueidentifier,
@RGIID uniqueidentifier,
@INSTALLMENTAPPLIEDAMOUNT money,
@INSTALLMENTDATE date,
@INSTALLMENTAMOUNT money,
@INSTALLMENTBALANCE money,
@PASTBALANCEINSTALLMENT bit,
@MOSTRECENTINSTALLMENTREPEATED bit,
@ORIGINALWRITEOFFAMOUNT money,
@WRITEOFFAMOUNT money,
@ORIGINALADJUSTMENTAMOUNT money,
@ADJUSTINSTALLMENTSTATUSCODE tinyint = 99,
@ADJUSTMENTAMOUNT money;
declare INSTALLMENTCURSOR cursor local fast_forward for
with INSTALLMENTS as (
select RGIP.ID RGIPID,
RGIP.AMOUNT ORIGINALPAYMENTAMOUNT,
RGI.ID RGIID,
RGI.DATE,
RGI.TRANSACTIONAMOUNT INSTALLMENTAMOUNT,
dbo.UFN_RECURRINGGIFTINSTALLMENT_GETINSTALLMENTBALANCE(RGI.ID) BALANCE,
E.OLDAMOUNT ORIGINALINSTALLMENTAMOUNT,
isnull(W.TRANSACTIONAMOUNT,0) ORIGINALWRITEOFFAMOUNT,
isnull(RGI.TRANSACTIONAMOUNT - E.OLDAMOUNT,0) ORIGINALADJUSTMENTAMOUNT
from dbo.RECURRINGGIFTINSTALLMENTPAYMENT RGIP
inner join dbo.RECURRINGGIFTINSTALLMENT RGI on RGI.ID = RGIP.RECURRINGGIFTINSTALLMENTID
left join dbo.RECURRINGGIFTINSTALLMENTEVENT E on E.RECURRINGGIFTINSTALLMENTID = RGI.ID and E.PAYMENTID = @REVENUEID
outer apply (select IW.TRANSACTIONAMOUNT
from dbo.RECURRINGGIFTINSTALLMENTWRITEOFF IW
inner join dbo.RECURRINGGIFTWRITEOFF W on W.ID = IW.WRITEOFFID
where IW.RECURRINGGIFTINSTALLMENTID = RGI.ID
and W.PAYMENTID = @REVENUEID) W
where RGI.REVENUEID = @RGID
and RGIP.PAYMENTID = @REVENUEID
),
MOSTRECENTINSTALLMENTAPPLIEDTWICE as (
select top 1 RGIID ID
from INSTALLMENTS
where ORIGINALPAYMENTAMOUNT > ORIGINALINSTALLMENTAMOUNT
and DATE <= @APPLICATIONEFFECTIVEDATE
and @APPLYTOPASTINSTALLMENTS = 1
and @OVERPAYMENTCODE = 1
order by DATE desc
)
select RGIPID, ORIGINALPAYMENTAMOUNT, RGIID, DATE, INSTALLMENTAMOUNT, BALANCE, ORIGINALWRITEOFFAMOUNT, ORIGINALADJUSTMENTAMOUNT, MOSTRECENTINSTALLMENTREPEATED, PASTBALANCEINSTALLMENT
from (select RGIPID,
case when m.ID is null then ORIGINALPAYMENTAMOUNT
else ORIGINALINSTALLMENTAMOUNT end ORIGINALPAYMENTAMOUNT,
RGIID,
DATE,
case when m.ID is null then INSTALLMENTAMOUNT
else ORIGINALINSTALLMENTAMOUNT end INSTALLMENTAMOUNT,
BALANCE,
ORIGINALWRITEOFFAMOUNT,
case when m.ID is null then ORIGINALADJUSTMENTAMOUNT else 0 end ORIGINALADJUSTMENTAMOUNT,
0 MOSTRECENTINSTALLMENTREPEATED,
0 PASTBALANCEINSTALLMENT
from INSTALLMENTS i
left join MOSTRECENTINSTALLMENTAPPLIEDTWICE m on m.ID = i.RGIID
union all
select RGIPID,
ORIGINALPAYMENTAMOUNT-ORIGINALINSTALLMENTAMOUNT,
RGIID,
DATE,
INSTALLMENTAMOUNT,
BALANCE,
ORIGINALWRITEOFFAMOUNT,
ORIGINALADJUSTMENTAMOUNT,
1,
0
from INSTALLMENTS i
inner join MOSTRECENTINSTALLMENTAPPLIEDTWICE m on m.ID = i.RGIID
union all
-- past balances to write-off/adjust
select null,
null,
I.ID,
I.DATE,
I.TRANSACTIONAMOUNT,
dbo.UFN_RECURRINGGIFTINSTALLMENT_GETINSTALLMENTBALANCE(I.ID),
W.TRANSACTIONAMOUNT,
0,
null,
1
from dbo.RECURRINGGIFTINSTALLMENT I
outer apply (select IW.TRANSACTIONAMOUNT
from dbo.RECURRINGGIFTINSTALLMENTWRITEOFF IW
inner join dbo.RECURRINGGIFTWRITEOFF W on W.ID = IW.WRITEOFFID
where IW.RECURRINGGIFTINSTALLMENTID = I.ID
and W.PAYMENTID = @REVENUEID) W
where I.REVENUEID = @RGID
and I.DATE <= @APPLICATIONEFFECTIVEDATE
and I.STATUSCODE in(0,1)
and @PASTBALANCEUNDERPAYMENTCODE in(1,2)
) i
-- reverse order of that from USP_RECURRINGGIFT_APPLYPAYMENTTOINSTALLMENTS
order by PASTBALANCEINSTALLMENT, -- paid installments first, then past installments with a balance if needed
case when DATE <= @APPLICATIONEFFECTIVEDATE then 1 else 2 end desc, -- today/past, then future
MOSTRECENTINSTALLMENTREPEATED desc, -- repeated most recent installment after other today/past installments
case when DATE > @APPLICATIONEFFECTIVEDATE or @FIRSTINSTALLMENTCODE = 0 then DATE end desc, -- date asc if appropriate (past installments by rule, future installments always)
case when DATE <= @APPLICATIONEFFECTIVEDATE and @FIRSTINSTALLMENTCODE = 1 then DATE end; -- date desc if appropriate
open INSTALLMENTCURSOR;
fetch next from INSTALLMENTCURSOR into @RGIPID, @INSTALLMENTAPPLIEDAMOUNT, @RGIID, @INSTALLMENTDATE, @INSTALLMENTAMOUNT, @INSTALLMENTBALANCE, @ORIGINALWRITEOFFAMOUNT, @ORIGINALADJUSTMENTAMOUNT, @MOSTRECENTINSTALLMENTREPEATED, @PASTBALANCEINSTALLMENT;
while @@FETCH_STATUS = 0
begin
set @WRITEOFFAMOUNT = 0;
set @ADJUSTINSTALLMENTSTATUSCODE = 99;
set @ADJUSTMENTAMOUNT = 0;
-- exit the loop if we've removed all the money and we don't need to handle write-offs/adjustments of past balances
if @REMOVEAMOUNTCONVERTED <= 0 and @PASTBALANCEUNDERPAYMENTCODE = 0
break;
if @REMOVEAMOUNTCONVERTED > 0
begin
if @REMOVEAMOUNTCONVERTED >= @INSTALLMENTAPPLIEDAMOUNT and @MOSTRECENTINSTALLMENTREPEATED = 0 and @APPLIEDAMOUNTCONVERTED > 0
begin
-- removing more than what was applied to this installment alone
-- remove everything related to this payment from this installment
exec dbo.USP_RECURRINGGIFT_UNDOINSTALLMENTCHANGESONDELETEPAYMENT
@PAYMENTID = @REVENUEID,
@RECURRINGGIFTID = @RGID,
@RECURRINGGIFTINSTALLMENTID = @RGIID,
@CHANGEAGENTID = @CHANGEAGENTID,
@CURRENTDATE = @CURRENTDATE
exec dbo.USP_RECURRINGGIFTINSTALLMENTPAYMENT_DELETEBYID_WITHCHANGEAGENTID @RGIPID, @CHANGEAGENTID;
if @INSTALLMENTDATE <= @APPLICATIONEFFECTIVEDATE
begin
if @PASTBALANCEUNDERPAYMENTCODE = 1 -- write-off
set @WRITEOFFAMOUNT = @ORIGINALWRITEOFFAMOUNT + @INSTALLMENTAPPLIEDAMOUNT + @INSTALLMENTBALANCE;
else if @PASTBALANCEUNDERPAYMENTCODE = 2 -- adjust amount
begin
set @ADJUSTINSTALLMENTSTATUSCODE = 2;
set @ADJUSTMENTAMOUNT = @INSTALLMENTBALANCE;
end
end
end
else
begin
-- not removing all money applied to this installment (or $0 applied to RG)
-- underpayment rule only applies to past/today installments
-- this is intentionally @CURRENTDATE, not @APPLICATIONEFFECTIVEDATE - we still want to apply the installment underpayment rule for
-- any past installment, even if it's after the application effective date
if @INSTALLMENTDATE <= @CURRENTDATE and @INSTALLMENTUNDERPAYMENTCODE = 1 -- write-off
set @WRITEOFFAMOUNT = @ORIGINALWRITEOFFAMOUNT +
case when @REMOVEAMOUNTCONVERTED > @INSTALLMENTAPPLIEDAMOUNT then @INSTALLMENTAPPLIEDAMOUNT --mostrecentinstallmentrepeated=1
else @REMOVEAMOUNTCONVERTED end +
@INSTALLMENTBALANCE -
@ORIGINALADJUSTMENTAMOUNT; -- decrease by any adjusted amount - we would only write-off after removing any adjustments
if @INSTALLMENTUNDERPAYMENTCODE = 2 -- adjust amount
begin
set @ADJUSTINSTALLMENTSTATUSCODE = 2;
set @ADJUSTMENTAMOUNT = @INSTALLMENTAMOUNT -
case when @REMOVEAMOUNTCONVERTED > @INSTALLMENTAPPLIEDAMOUNT then @INSTALLMENTAPPLIEDAMOUNT --mostrecentinstallmentrepeated=1
else @REMOVEAMOUNTCONVERTED end -
@INSTALLMENTBALANCE;
end
else if @ORIGINALADJUSTMENTAMOUNT > 0
begin
-- decrease/remove the overpayment adjustment amount but do not create an underpayment adjustment
set @ADJUSTINSTALLMENTSTATUSCODE = case when @REMOVEAMOUNTCONVERTED > @INSTALLMENTAPPLIEDAMOUNT then 2
when @REMOVEAMOUNTCONVERTED > @ORIGINALADJUSTMENTAMOUNT then 0
else 2 end;
set @ADJUSTMENTAMOUNT = @INSTALLMENTAMOUNT -
case when @REMOVEAMOUNTCONVERTED > @INSTALLMENTAPPLIEDAMOUNT then @INSTALLMENTAPPLIEDAMOUNT --mostrecentinstallmentrepeated=1
when @REMOVEAMOUNTCONVERTED > @ORIGINALADJUSTMENTAMOUNT then @ORIGINALADJUSTMENTAMOUNT
else @REMOVEAMOUNTCONVERTED end;
end
update dbo.RECURRINGGIFTINSTALLMENTPAYMENT
set AMOUNT = AMOUNT - case when @REMOVEAMOUNTCONVERTED > @INSTALLMENTAPPLIEDAMOUNT then @INSTALLMENTAPPLIEDAMOUNT else @REMOVEAMOUNTCONVERTED end,
APPLICATIONEXCHANGERATEID = @APPLICATIONEXCHANGERATEID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CHANGEDATE
where ID = @RGIPID;
end
set @REMOVEAMOUNTCONVERTED = @REMOVEAMOUNTCONVERTED - @INSTALLMENTAPPLIEDAMOUNT;
end
else if @PASTBALANCEINSTALLMENT = 1
begin
if @PASTBALANCEUNDERPAYMENTCODE = 1 -- write-off
set @WRITEOFFAMOUNT = @INSTALLMENTBALANCE;
else if @PASTBALANCEUNDERPAYMENTCODE = 2 -- adjust amount
begin
set @ADJUSTINSTALLMENTSTATUSCODE = 2;
set @ADJUSTMENTAMOUNT = @INSTALLMENTAMOUNT - @INSTALLMENTBALANCE;
end
end
if @ADJUSTINSTALLMENTSTATUSCODE < 99
begin
declare @ORIGINALINSTALLMENTAMOUNT money = @INSTALLMENTAMOUNT - @ORIGINALADJUSTMENTAMOUNT;
exec dbo.USP_RECURRINGGIFT_ADJUSTINSTALLMENTAMOUNT
@RGIID,
@ORIGINALINSTALLMENTAMOUNT,
@ADJUSTMENTAMOUNT,
@RGTRANSACTIONCURRENCYID,
@RGBASECURRENCYID,
@RGBASEEXCHANGERATEID,
@RGORGANIZATIONEXCHANGERATEID,
@ADJUSTINSTALLMENTSTATUSCODE,
@REVENUEID,
@APPLICATIONEFFECTIVEDATETIME,
@CHANGEAGENTID,
@CURRENTDATE
end
if @WRITEOFFAMOUNT > 0
begin
declare @WRITEOFFBASEAMOUNT money,
@WRITEOFFORGANIZATIONAMOUNT money,
@WRITEOFFID uniqueidentifier;
exec dbo.USP_CURRENCY_GETCURRENCYVALUES
@WRITEOFFAMOUNT,
null,
@RGBASECURRENCYID,
@RGBASEEXCHANGERATEID,
@RGTRANSACTIONCURRENCYID,
@WRITEOFFBASEAMOUNT output,
null,
@WRITEOFFORGANIZATIONAMOUNT output,
@RGORGANIZATIONEXCHANGERATEID,
0;
select @WRITEOFFID = w.ID
from dbo.RECURRINGGIFTINSTALLMENTWRITEOFF iw
inner join dbo.RECURRINGGIFTWRITEOFF w on w.ID = iw.WRITEOFFID
where iw.RECURRINGGIFTINSTALLMENTID = @RGIID
and w.REASONTYPECODE = 1;
if @WRITEOFFID is not null
update dbo.RECURRINGGIFTINSTALLMENTWRITEOFF
set TRANSACTIONAMOUNT = @WRITEOFFAMOUNT,
AMOUNT = @WRITEOFFBASEAMOUNT,
ORGANIZATIONAMOUNT = @WRITEOFFORGANIZATIONAMOUNT,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CHANGEDATE
where WRITEOFFID = @WRITEOFFID;
else
exec dbo.USP_RECURRINGGIFT_ADDPAYMENTHANDLINGWRITEOFF
@RGID,
@RGIID,
@REVENUEID,
@DATE,
@WRITEOFFAMOUNT,
@RGBASECURRENCYID,
@RGBASEEXCHANGERATEID,
@RGTRANSACTIONCURRENCYID,
@RGORGANIZATIONEXCHANGERATEID,
@CHANGEAGENTID,
@CHANGEDATE
end
fetch next from INSTALLMENTCURSOR into @RGIPID, @INSTALLMENTAPPLIEDAMOUNT, @RGIID, @INSTALLMENTDATE, @INSTALLMENTAMOUNT, @INSTALLMENTBALANCE, @ORIGINALWRITEOFFAMOUNT, @ORIGINALADJUSTMENTAMOUNT, @MOSTRECENTINSTALLMENTREPEATED, @PASTBALANCEINSTALLMENT;
end
deallocate INSTALLMENTCURSOR;
exec dbo.USP_RECURRINGGIFT_DELETEFUTUREINSTALLMENTS @RGID, @CHANGEAGENTID;
if @APPLICATIONEXCHANGERATEID is not null
update dbo.RECURRINGGIFTINSTALLMENTPAYMENT
set
APPLICATIONEXCHANGERATEID = @APPLICATIONEXCHANGERATEID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CHANGEDATE
from
dbo.RECURRINGGIFTINSTALLMENTPAYMENT
inner join dbo.RECURRINGGIFTINSTALLMENT on RECURRINGGIFTINSTALLMENT.ID = RECURRINGGIFTINSTALLMENTPAYMENT.RECURRINGGIFTINSTALLMENTID
inner join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTION.ID = @RGID
inner join dbo.REVENUE_EXT on FINANCIALTRANSACTION.ID = REVENUE_EXT.ID
where
RECURRINGGIFTINSTALLMENT.REVENUEID = @RGID
and FINANCIALTRANSACTION.DELETEDON is null
and RECURRINGGIFTINSTALLMENTPAYMENT.PAYMENTID = @REVENUEID
and RECURRINGGIFTINSTALLMENTPAYMENT.APPLICATIONEXCHANGERATEID <> @APPLICATIONEXCHANGERATEID;
exec dbo.USP_RECURRINGGIFT_UPDATEREVENUESCHEDULE
@REVENUEID = @RGID,
@STATUSCHANGETYPECODE = 2,
@CHANGEAGENTID = @CHANGEAGENTID,
@CURRENTDATETIME = @CURRENTDATE;
end
fetch next from RGCURSOR into @RGID, @APPLIED, @PREVIOUSAPPLIED, @ISDELETED, @APPLICATIONEXCHANGERATEID, @PREVIOUSAPPLIEDCONVERTED, @APPLICATIONEFFECTIVEDATETIME, @RGTRANSACTIONCURRENCYID, @RGBASECURRENCYID, @RGBASEEXCHANGERATEID, @RGORGANIZATIONEXCHANGERATEID;
end
deallocate RGCURSOR;
end -- update RG installments
delete dbo.RECURRINGGIFTACTIVITY
from dbo.RECURRINGGIFTACTIVITY
where PAYMENTREVENUEID in (select ID from @DELETEDIDS);
-- update RG write-offs associated with this payment to the new payment date
update dbo.RECURRINGGIFTWRITEOFF
set DATE = @DATE,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CHANGEDATE
where PAYMENTID = @REVENUEID
and DATE <> @DATE;
-- update RG installment adjustments associated with this payment to the new payment date
update dbo.RECURRINGGIFTINSTALLMENTEVENT
set DATE = @DATE,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CHANGEDATE
where PAYMENTID = @REVENUEID
and DATE <> @DATE;
delete dbo.AUCTIONITEMREVENUEPURCHASE
from
dbo.AUCTIONITEM
inner join dbo.AUCTIONITEMREVENUEPURCHASE on AUCTIONITEM.ID = AUCTIONITEMREVENUEPURCHASE.AUCTIONITEMID
inner join dbo.AUCTIONITEMPURCHASE on AUCTIONITEM.ID = AUCTIONITEMPURCHASE.AUCTIONITEMID
where
AUCTIONITEMREVENUEPURCHASE.REVENUEPURCHASEID = @REVENUEID
and
AUCTIONITEMPURCHASE.PURCHASEID in (select ID from @DELETEDIDS);
delete dbo.AUCTIONITEMPURCHASE
where
AUCTIONITEMPURCHASE.PURCHASEID in (select ID from @DELETEDIDS);
declare @SPLITSTODELETE xml;
set @SPLITSTODELETE = (select SPLITS.ID from (select ID
from dbo.UFN_REVENUE_GETSPLITS_2(@REVENUEID)) SPLITS
where SPLITS.ID in (select ID from @DELETEDIDS)
for xml raw('ITEM'),type,elements,root('SPLITSTODELETE'),BINARY BASE64);
--exec dbo.USP_REVENUESPLIT_DELETESPLITS @SPLITSTODELETE, @CHANGEAGENTID, @CHANGEDATE;
if @SPLITSTODELETE is not null
exec dbo.USP_REVENUESPLIT_DELETESPLITS @SPLITSTODELETE, @CHANGEAGENTID, @CHANGEDATE;
declare @SPLITSDECLININGGIFTAIDTBL table
(
REVENUESPLITID uniqueidentifier
)
declare @GIFTAIDSPONSORSHIPSPLITSTBL table
(
REVENUESPLITID uniqueidentifier
)
--Update Changed Revenue Items
open UPDATECURSOR;
fetch next from UPDATECURSOR into @ID, @APPLICATIONID, @APPLIEDAMOUNT, @APPLICATIONTYPE, @GIFTFIELDS, @OTHERFIELDS, @DECLINESGIFTAID, @ISGIFTAIDSPONSORSHIP, @ORIGINALSPLITAMOUNT, @UPDATERECOGNITIONOPTION, @BASEAPPLIEDAMOUNT, @ORGANIZATIONAPPLIEDAMOUNT, @UPDATESOLICITORSOPTION, @CATEGORYCODEID;
while @@FETCH_STATUS = 0
begin
if @APPLICATIONTYPE = 0 --Gift
begin
SELECT top 1
@DESIGNATIONID = T.c.value('(DESIGNATIONID)[1]','uniqueidentifier'),
@OPPORTUNITYID = T.c.value('(OPPORTUNITYID)[1]','uniqueidentifier'),
@CATEGORYCODEID = T.c.value('(CATEGORYCODEID)[1]','uniqueidentifier'),
@CAMPAIGNS = c.query('./CAMPAIGNS'),
@SOLICITORS = c.query('./SOLICITORS'),
@RECOGNITIONCREDITS = c.query('./RECOGNITIONS'),
@REVENUETYPECODE = case when T.c.exist('REVENUETYPECODE/text()') = 1 then T.c.value('(REVENUETYPECODE)[1]','tinyint') else null end
FROM @GIFTFIELDS.nodes('/GIFTFIELDS/ITEM') T(c);
end
if @APPLICATIONTYPE = 100 -- unnappliedmatching gift claim
begin
SELECT top 1
@DESIGNATIONID = T.c.value('(DESIGNATIONID)[1]','uniqueidentifier'),
@CATEGORYCODEID = T.c.value('(CATEGORYCODEID)[1]','uniqueidentifier'),
@CAMPAIGNS = c.query('./CAMPAIGNS'),
@SOLICITORS = c.query('./SOLICITORS'),
@RECOGNITIONCREDITS = c.query('./RECOGNITIONS')
FROM @GIFTFIELDS.nodes('/GIFTFIELDS/ITEM') T(c);
end
if @APPLICATIONTYPE = 4 --Other
begin
SELECT top 1
@DESIGNATIONID = T.c.value('(DESIGNATIONID)[1]','uniqueidentifier'),
@OTHERTYPECODEID = T.c.value('(OTHERTYPECODEID)[1]','uniqueidentifier'),
@CATEGORYCODEID = T.c.value('(CATEGORYCODEID)[1]','uniqueidentifier'),
@CAMPAIGNS = c.query('./CAMPAIGNS'),
@SOLICITORS = c.query('./SOLICITORS'),
@RECOGNITIONCREDITS = c.query('./RECOGNITIONS')
FROM @OTHERFIELDS.nodes('/OTHERFIELDS/ITEM') T(c);
end
declare @REC table
(
ID uniqueidentifier,
CONSTITUENTID uniqueidentifier,
AMOUNT money,
EFFECTIVEDATE datetime,
REVENUERECOGNITIONTYPECODEID uniqueidentifier,
BASECURRENCYID uniqueidentifier,
DESIGNATIONID uniqueidentifier, --field for donor challenge internal sponsor credit
DESIGNATION nvarchar(512), --field for donor challenge internal sponsor credit
DONOR nvarchar(293), --field for donor challenge internal sponsor credit
DONORCHALLENGERECOGNITIONTYPECODE tinyint, --field for donor challenge internal sponsor credit
RECOGNITIONCREDITFKID uniqueidentifier --field for donor challenge internal sponsor credit
);
-- Clearing @REC since table variables are initialized once per batch and aren't scoped to the loop.
-- As a result, the table will still hold results from the previous loop run.
delete from @REC;
declare @MATCHEDBYDONORCHALLENGE bit = 0;
if dbo.UFN_REVENUESPLIT_MATCHEDBYDONORCHALLENGE(@ID) = 1
begin
set @MATCHEDBYDONORCHALLENGE = 1;
-- If the amount on the split has been changed and its matched by a donor challenge. Throw an error preventing the form from saving.
-- This change is occurring because of Bug 487336. I don't think this a good idea.
if @APPLIEDAMOUNT <> @ORIGINALSPLITAMOUNT
begin
raiserror('BBERR_DONORCHALLENGEMATCHEDSPLITCHANGED', 13, 1);
end
end
if @MATCHEDBYDONORCHALLENGE = 1
begin
insert into @REC(ID, CONSTITUENTID, AMOUNT, EFFECTIVEDATE, REVENUERECOGNITIONTYPECODEID, BASECURRENCYID, DESIGNATIONID, DESIGNATION, DONOR, DONORCHALLENGERECOGNITIONTYPECODE, RECOGNITIONCREDITFKID)
select
T.c.value('(ID)[1]','uniqueidentifier') AS 'ID',
T.c.value('(CONSTITUENTID)[1]','uniqueidentifier') AS 'CONSTITUENTID',
T.c.value('(AMOUNT)[1]','money') AS 'AMOUNT',
T.c.value('(EFFECTIVEDATE)[1]','datetime') AS 'EFFECTIVEDATE',
T.c.value('(REVENUERECOGNITIONTYPECODEID)[1]','uniqueidentifier') AS 'REVENUERECOGNITIONTYPECODEID',
T.c.value('(BASECURRENCYID)[1]','uniqueidentifier') AS 'BASECURRENCYID',
T.c.value('(DESIGNATIONID)[1]','uniqueidentifier') AS 'DESIGNATIONID',
T.c.value('(DESIGNATION)[1]','nvarchar(512)') AS 'DESIGNATION',
T.c.value('(DONOR)[1]','nvarchar(293)') AS 'DONOR',
T.c.value('(DONORCHALLENGERECOGNITIONTYPECODE)[1]','tinyint') AS 'DONORCHALLENGERECOGNITIONTYPECODE',
T.c.value('(RECOGNITIONCREDITFKID)[1]','uniqueidentifier') AS 'RECOGNITIONCREDITFKID'
from @RECOGNITIONCREDITS.nodes('/RECOGNITIONS/ITEM') T(c);
end
else
begin
insert into @REC(ID, CONSTITUENTID, AMOUNT, EFFECTIVEDATE, REVENUERECOGNITIONTYPECODEID)
select
T.c.value('(ID)[1]','uniqueidentifier') AS 'ID',
T.c.value('(CONSTITUENTID)[1]','uniqueidentifier') AS 'CONSTITUENTID',
T.c.value('(AMOUNT)[1]','money') AS 'AMOUNT',
T.c.value('(EFFECTIVEDATE)[1]','datetime') AS 'EFFECTIVEDATE',
T.c.value('(REVENUERECOGNITIONTYPECODEID)[1]','uniqueidentifier') AS 'REVENUERECOGNITIONTYPECODEID'
from @RECOGNITIONCREDITS.nodes('/RECOGNITIONS/ITEM') T(c);
end
declare REC_CURSOR cursor local dynamic for --The cursor must be dynamic because the data in @REC will be updated as constituents are created
select CONSTITUENTID, AMOUNT, EFFECTIVEDATE, REVENUERECOGNITIONTYPECODEID from @REC;
open REC_CURSOR;
declare @RECOGNITION_CONSTITUENTID uniqueidentifier;
declare @AMOUNT money;
declare @EFFECTIVEDATE datetime;
declare @REVENUERECOGNITIONTYPECODEID uniqueidentifier;
declare @BATCHCONSTITUENTID uniqueidentifier;
fetch next from REC_CURSOR into @RECOGNITION_CONSTITUENTID, @AMOUNT, @EFFECTIVEDATE, @REVENUERECOGNITIONTYPECODEID;
while @@FETCH_STATUS = 0
begin
--Create the constituent if necessary
if not exists(select ID from dbo.CONSTITUENT where ID = @RECOGNITION_CONSTITUENTID)
begin
set @BATCHCONSTITUENTID = @RECOGNITION_CONSTITUENTID;
set @RECOGNITION_CONSTITUENTID = newid();
exec USP_REVENUEBATCH_CONSTITUENT_ADD @RECOGNITION_CONSTITUENTID OUTPUT, @CHANGEAGENTID, @BATCHCONSTITUENTID;
update @REC
set CONSTITUENTID = @RECOGNITION_CONSTITUENTID
where CONSTITUENTID = @BATCHCONSTITUENTID;
exec dbo.USP_REVENUEBATCH_CONSTITUENT_DELETE @BATCHREVENUECONSTITUENTID = @BATCHCONSTITUENTID, @CHANGEAGENTID = @CHANGEAGENTID
end
fetch next from REC_CURSOR into @RECOGNITION_CONSTITUENTID, @AMOUNT, @EFFECTIVEDATE, @REVENUERECOGNITIONTYPECODEID;
end
close REC_CURSOR;
deallocate REC_CURSOR;
if @MATCHEDBYDONORCHALLENGE = 1
begin
set @RECOGNITIONCREDITS =
(
select
ID, CONSTITUENTID, AMOUNT, EFFECTIVEDATE, REVENUERECOGNITIONTYPECODEID, BASECURRENCYID, DESIGNATIONID, DESIGNATION, DONOR, DONORCHALLENGERECOGNITIONTYPECODE, RECOGNITIONCREDITFKID
from @REC
order by EFFECTIVEDATE desc
for xml raw('ITEM'),type,elements,root('RECOGNITIONS'),binary base64
);
end
else
begin
set @RECOGNITIONCREDITS =
(
select
ID, CONSTITUENTID, AMOUNT, EFFECTIVEDATE, REVENUERECOGNITIONTYPECODEID
from @REC
order by EFFECTIVEDATE desc
for xml raw('ITEM'),type,elements,root('RECOGNITIONS'),binary base64
);
end
if @APPLICATIONTYPE = 0 --Gift
begin
exec dbo.USP_GIFT_UPDATEPAYMENT
@ID = @ID,
@REVENUEID = @REVENUEID,
@AMOUNT = @APPLIEDAMOUNT,
@DESIGNATIONID = @DESIGNATIONID,
@OPPORTUNITYID = @OPPORTUNITYID,
@CAMPAIGNS = @CAMPAIGNS,
@SOLICITORS = @SOLICITORS,
@CATEGORYCODEID = @CATEGORYCODEID,
@RECOGNITIONCREDITS = @RECOGNITIONCREDITS,
@CHANGEDATE = @CHANGEDATE,
@CHANGEAGENTID = @CHANGEAGENTID,
@BASEAMOUNT = @BASEAPPLIEDAMOUNT,
@ORGANIZATIONAMOUNT = @ORGANIZATIONAPPLIEDAMOUNT,
@REVENUETYPECODE = @REVENUETYPECODE;
if @DECLINESGIFTAID = 1
insert into @SPLITSDECLININGGIFTAIDTBL (REVENUESPLITID) values (@ID)
if @ISGIFTAIDSPONSORSHIP = 1
insert into @GIFTAIDSPONSORSHIPSPLITSTBL (REVENUESPLITID) values (@ID)
end
if @APPLICATIONTYPE = 1 --Event Registration
begin
exec dbo.USP_EVENT_UPDATEPAYMENT
@ID = @ID,
@AMOUNT = @APPLIEDAMOUNT,
@CHANGEDATE = @CHANGEDATE,
@CHANGEAGENTID = @CHANGEAGENTID,
@PREVIOUSDATE = @PREVIOUSDATE,
@UPDATERECOGNITIONOPTION = @UPDATERECOGNITIONOPTION,
@BASEAPPLIEDAMOUNT = @BASEAPPLIEDAMOUNT,
@ORGANIZATIONAPPLIEDAMOUNT = @ORGANIZATIONAPPLIEDAMOUNT,
@UPDATESOLICITORSOPTION = @UPDATESOLICITORSOPTION,
@CATEGORYCODEID = @CATEGORYCODEID;
--Store registrant ID's for later balance check
insert into @REGISTRANTIDS (ID) values (@APPLICATIONID)
end
if @APPLICATIONTYPE in(
2, -- Pledge
7, -- MG Pledge
6, -- Planned Gift
8, -- Grant award
13, -- Donor challenge
17, -- Pending gift
19 -- Membership installment plan
)
begin
declare @PLANNEDGIFTID uniqueidentifier;
declare @PLANNEDGIFTADDITIONID uniqueidentifier;
declare @OLDAMOUNT money;
declare @OLDTRANSACTIONAMOUNT money;
declare @BASECURRENCYID uniqueidentifier;
declare @TRANSACTIONCURRENCYID uniqueidentifier;
if @APPLICATIONTYPE = 6
begin
select @PLANNEDGIFTID = PLANNEDGIFTID, @PLANNEDGIFTADDITIONID = PLANNEDGIFTADDITIONID
from dbo.V_PLANNEDGIFTREVENUE_WITHADDITIONS
where REVENUEID = @APPLICATIONID
select
@OLDAMOUNT = LI.BASEAMOUNT,
@OLDTRANSACTIONAMOUNT = LI.TRANSACTIONAMOUNT,
@BASECURRENCYID = CS.BASECURRENCYID,
@TRANSACTIONCURRENCYID = FT.TRANSACTIONCURRENCYID
from dbo.FINANCIALTRANSACTIONLINEITEM LI
inner join dbo.FINANCIALTRANSACTION FT on FT.ID = LI.FINANCIALTRANSACTIONID
inner join dbo.PDACCOUNTSYSTEM PD on PD.ID = FT.PDACCOUNTSYSTEMID
inner join dbo.CURRENCYSET CS on CS.ID = PD.CURRENCYSETID
where LI.ID = @ID
end
exec dbo.USP_PLEDGE_UPDATEPAYMENT
@ID = @ID,
@APPLIEDAMOUNT = @APPLIEDAMOUNT,
@CHANGEDATE = @CHANGEDATE,
@CHANGEAGENTID = @CHANGEAGENTID,
@UPDATERECOGNITIONOPTION = @UPDATERECOGNITIONOPTION,
@BASEAPPLIEDAMOUNT = @BASEAPPLIEDAMOUNT,
@ORGANIZATIONAPPLIEDAMOUNT = @ORGANIZATIONAPPLIEDAMOUNT,
@APPLICATIONTYPE = @APPLICATIONTYPE,
@APPLICATIONID = @APPLICATIONID,
@UPDATESOLICITORSOPTION = @UPDATESOLICITORSOPTION;
if @DECLINESGIFTAID = 1
insert into @SPLITSDECLININGGIFTAIDTBL (REVENUESPLITID) values (@ID)
if @APPLICATIONTYPE = 6
begin
if exists (select 1 from @PLANNEDGIFTUPDATES where PLANNEDGIFTID = @PLANNEDGIFTID and ((@PLANNEDGIFTADDITIONID is null and PLANNEDGIFTADDITIONID is null) or PLANNEDGIFTADDITIONID = @PLANNEDGIFTADDITIONID))
update @PLANNEDGIFTUPDATES
set OLDAMOUNT = OLDAMOUNT + @OLDAMOUNT,
NEWAMOUNT = NEWAMOUNT + @BASEAPPLIEDAMOUNT,
OLDTRANSACTIONAMOUNT = OLDTRANSACTIONAMOUNT + @OLDTRANSACTIONAMOUNT,
NEWTRANSACTIONAMOUNT = NEWTRANSACTIONAMOUNT + @APPLIEDAMOUNT
where PLANNEDGIFTID = @PLANNEDGIFTID and ((@PLANNEDGIFTADDITIONID is null and PLANNEDGIFTADDITIONID is null) or PLANNEDGIFTADDITIONID = @PLANNEDGIFTADDITIONID)
else
insert into @PLANNEDGIFTUPDATES(PLANNEDGIFTID, PLANNEDGIFTADDITIONID, OLDAMOUNT, NEWAMOUNT, OLDDATE, NEWDATE, OLDTRANSACTIONAMOUNT, NEWTRANSACTIONAMOUNT, BASECURRENCYID, TRANSACTIONCURRENCYID)
values(@PLANNEDGIFTID, @PLANNEDGIFTADDITIONID, @OLDAMOUNT, @BASEAPPLIEDAMOUNT, @PREVIOUSDATE, @DATE, @OLDTRANSACTIONAMOUNT, @APPLIEDAMOUNT, @BASECURRENCYID, @TRANSACTIONCURRENCYID)
end
end
if @APPLICATIONTYPE = 3 --Recurring Gift
begin
exec dbo.USP_RECURRINGGIFT_UPDATEPAYMENT
@ID = @ID,
@APPLIEDAMOUNT = @APPLIEDAMOUNT,
@CHANGEDATE = @CHANGEDATE,
@CHANGEAGENTID = @CHANGEAGENTID,
@UPDATERECOGNITIONOPTION = @UPDATERECOGNITIONOPTION,
@BASEAPPLIEDAMOUNT = @BASEAPPLIEDAMOUNT,
@ORGANIZATIONAPPLIEDAMOUNT = @ORGANIZATIONAPPLIEDAMOUNT,
@UPDATESOLICITORSOPTION = @UPDATESOLICITORSOPTION;
if @DECLINESGIFTAID = 1
insert into @SPLITSDECLININGGIFTAIDTBL (REVENUESPLITID) values (@ID)
end
if @APPLICATIONTYPE = 4 --Other
begin
exec dbo.USP_OTHER_UPDATEPAYMENT
@ID = @ID,
@REVENUEID = @REVENUEID,
@AMOUNT = @APPLIEDAMOUNT,
@DESIGNATIONID = @DESIGNATIONID,
@OTHERTYPECODEID = @OTHERTYPECODEID,
@CAMPAIGNS = @CAMPAIGNS,
@SOLICITORS = @SOLICITORS,
@CATEGORYCODEID = @CATEGORYCODEID,
@RECOGNITIONCREDITS = @RECOGNITIONCREDITS,
@CHANGEDATE = @CHANGEDATE,
@CHANGEAGENTID = @CHANGEAGENTID,
@BASEAPPLIEDAMOUNT = @BASEAPPLIEDAMOUNT,
@ORGANIZATIONAPPLIEDAMOUNT = @ORGANIZATIONAPPLIEDAMOUNT;
end
if @APPLICATIONTYPE = 100 -- unnappliedmatching gift claim
begin
exec dbo.USP_UNAPPLIEDMG_UPDATEPAYMENT
@ID = @ID,
@REVENUEID = @REVENUEID,
@AMOUNT = @APPLIEDAMOUNT,
@DESIGNATIONID = @DESIGNATIONID,
@CAMPAIGNS = @CAMPAIGNS,
@SOLICITORS = @SOLICITORS,
@CATEGORYCODEID = @CATEGORYCODEID,
@RECOGNITIONCREDITS = @RECOGNITIONCREDITS,
@CHANGEDATE = @CHANGEDATE,
@CHANGEAGENTID = @CHANGEAGENTID,
@BASEAMOUNT = @BASEAPPLIEDAMOUNT,
@ORGANIZATIONAMOUNT = @ORGANIZATIONAPPLIEDAMOUNT;
end
if @APPLICATIONTYPE = 11 -- miscellaneous payments
begin
update dbo.FINANCIALTRANSACTIONLINEITEM set
TRANSACTIONAMOUNT = @APPLIEDAMOUNT
,BASEAMOUNT = @BASEAPPLIEDAMOUNT
,ORGAMOUNT = @ORGANIZATIONAPPLIEDAMOUNT
,CHANGEDBYID = @CHANGEAGENTID
,DATECHANGED = @CHANGEDATE
where ID = @ID
and (
TRANSACTIONAMOUNT != @APPLIEDAMOUNT or
BASEAMOUNT != @BASEAPPLIEDAMOUNT or
ORGAMOUNT != @ORGANIZATIONAPPLIEDAMOUNT);
end
if @APPLICATIONTYPE = 5 -- Membership
begin
exec dbo.USP_MEMBERSHIP_UPDATEPAYMENT
@ID = @ID,
@AMOUNT = @APPLIEDAMOUNT,
@CHANGEDATE = @CHANGEDATE,
@CHANGEAGENTID = @CHANGEAGENTID,
@UPDATERECOGNITIONOPTION = @UPDATERECOGNITIONOPTION,
@BASEAPPLIEDAMOUNT = @BASEAPPLIEDAMOUNT,
@ORGANIZATIONAPPLIEDAMOUNT = @ORGANIZATIONAPPLIEDAMOUNT,
@UPDATESOLICITORSOPTION = @UPDATESOLICITORSOPTION;
end
if @APPLICATIONTYPE = 18 -- Membership add-on
begin
exec dbo.USP_MEMBERSHIPADDON_UPDATEPAYMENT
@ID = @ID,
@AMOUNT = @APPLIEDAMOUNT,
@CHANGEDATE = @CHANGEDATE,
@CHANGEAGENTID = @CHANGEAGENTID,
@UPDATERECOGNITIONOPTION = @UPDATERECOGNITIONOPTION,
@BASEAPPLIEDAMOUNT = @BASEAPPLIEDAMOUNT,
@ORGANIZATIONAPPLIEDAMOUNT = @ORGANIZATIONAPPLIEDAMOUNT,
@UPDATESOLICITORSOPTION = @UPDATESOLICITORSOPTION;
end
if @APPLICATIONTYPE = 12 -- Auction Purchase
begin
declare @SPLITREVENUEID uniqueidentifier
select @SPLITREVENUEID = FINANCIALTRANSACTIONID from dbo.FINANCIALTRANSACTIONLINEITEM where ID = @ID
declare @OLDSPLITS xml = dbo.UFN_REVENUE_GETSPLITS_2_TOITEMLISTXML(@SPLITREVENUEID)
declare @OLDRECOGNITIONS xml = dbo.UFN_REVENUE_GETRECOGNITIONS_FORREVENUE_TOITEMLISTXML(@SPLITREVENUEID)
update dbo.FINANCIALTRANSACTIONLINEITEM set
TRANSACTIONAMOUNT = @APPLIEDAMOUNT
,BASEAMOUNT = @BASEAPPLIEDAMOUNT
,ORGAMOUNT = @ORGANIZATIONAPPLIEDAMOUNT
,CHANGEDBYID = @CHANGEAGENTID
,DATECHANGED = @CHANGEDATE
where ID = @ID
and (
TRANSACTIONAMOUNT != @APPLIEDAMOUNT or
BASEAMOUNT != @BASEAPPLIEDAMOUNT or
ORGAMOUNT != @ORGANIZATIONAPPLIEDAMOUNT);
exec dbo.USP_REVENUE_UPDATERECOGNITION @SPLITREVENUEID, @OLDSPLITS, @UPDATERECOGNITIONOPTION, @CHANGEAGENTID, @CHANGEDATE, @OLDRECOGNITIONS;
exec dbo.USP_REVENUESPLIT_UPDATESOLICITORS @REVENUESPLITID = @ID, @CHANGEAGENTID = @CHANGEAGENTID, @CHANGEDATE = @CHANGEDATE, @UPDATEOPTION = @UPDATESOLICITORSOPTION
end
if @APPLICATIONTYPE = 15
begin
exec dbo.USP_SPONSOR_UPDATEPAYMENT
@ID = @ID,
@AMOUNT = @APPLIEDAMOUNT,
@CHANGEDATE = @CHANGEDATE,
@CHANGEAGENTID = @CHANGEAGENTID,
@UPDATERECOGNITIONOPTION = @UPDATERECOGNITIONOPTION,
@BASEAPPLIEDAMOUNT = @BASEAPPLIEDAMOUNT,
@ORGANIZATIONAPPLIEDAMOUNT = @ORGANIZATIONAPPLIEDAMOUNT,
@UPDATESOLICITORSOPTION = @UPDATESOLICITORSOPTION;
end
--Check if given anonymously has changed.
declare @GIVENANONYMOUSLY bit;
select @GIVENANONYMOUSLY = GIVENANONYMOUSLY
from dbo.REVENUE_EXT
where ID = @REVENUEID;
--Select the value for default anonymous recognition setting
declare @DEFAULTANONYMOUSRECOGNITION bit = 0;
select @DEFAULTANONYMOUSRECOGNITION = DEFAULTANONYMOUSRECOGNITION
from dbo.RECOGNITIONDEFAULT
--Added APPLICATIONTYPE 8 - planned gift.
-- 13 - Donor challenge claim.
if @APPLICATIONTYPE in (1,2,3,6,8,13,19)
begin
if @GIVENANONYMOUSLY = 1 and @ORIGINALGIVENANONYMOUSLY = 0 and @DEFAULTANONYMOUSRECOGNITION = 0
begin
--gone from named to anonymous, so delete recognition
delete dbo.REVENUERECOGNITION where REVENUESPLITID = @ID;
end
else if (@GIVENANONYMOUSLY = 0 and @ORIGINALGIVENANONYMOUSLY = 1) or (@GIVENANONYMOUSLY = 1 and @ORIGINALGIVENANONYMOUSLY = 0 and @DEFAULTANONYMOUSRECOGNITION = 1)
begin
--Do not add any recognition credits if user added/deleted/modified when the transaction was anonymous
if not exists (select 1 from dbo.REVENUERECOGNITION where REVENUESPLITID = @ID)
begin
--gone from anonymous to named, so create default recognition if user agrees
declare @SOURCERECURRINGGIFTID uniqueidentifier = null;
select @SOURCERECURRINGGIFTID = SOURCEREVENUEID
from dbo.RECURRINGGIFTACTIVITY
where PAYMENTREVENUEID = @ID and @APPLICATIONCODE = 3;
exec dbo.USP_REVENUEDETAIL_CREATERECOGNITIONS @ID, @CHANGEAGENTID, @CHANGEDATE, @SOURCERECURRINGGIFTID;
end
end
end
declare @VID char(36);
set @VID = lower(cast(@ID as char(36)));
--TommyVe 2009-11-19 Lower-case the value from @REVENUESTREAMS too, it doesn't always come in as lower case.
/* remove updated from XML so only new IDs are left */
set @REVENUESTREAMS.modify('
delete /REVENUESTREAMS/ITEM[lower-case(ID[1]) = sql:variable("@VID")]
')
fetch next from UPDATECURSOR into @ID, @APPLICATIONID, @APPLIEDAMOUNT, @APPLICATIONTYPE, @GIFTFIELDS, @OTHERFIELDS, @DECLINESGIFTAID, @ISGIFTAIDSPONSORSHIP, @ORIGINALSPLITAMOUNT, @UPDATERECOGNITIONOPTION, @BASEAPPLIEDAMOUNT, @ORGANIZATIONAPPLIEDAMOUNT, @UPDATESOLICITORSOPTION, @CATEGORYCODEID;
end
--Get PDACCOUNTSYSTEMID from parent revenue. This is needed for auction splits because they have a restriction of which account systems
--they can be applied to.
declare @PDACCOUNTSYSTEMID uniqueidentifier;
select @PDACCOUNTSYSTEMID = PDACCOUNTSYSTEMID from dbo.FINANCIALTRANSACTION where ID = @REVENUEID;
--If event registrations exist, check that they don't go over the total balance of the event registration.
--We see a performance increase if we only do this operation once per unique application and after all the potential currency conversions have already occurred.
if exists (select 1 from @REGISTRANTIDS)
begin
declare EVENTREGISTRANT cursor local fast_forward for select distinct ID from @REGISTRANTIDS
declare @CURRENTREGISTRANTID uniqueidentifier;
--Balance check event registrations
open EVENTREGISTRANT;
fetch next from EVENTREGISTRANT into @CURRENTREGISTRANTID;
while @@FETCH_STATUS = 0
begin
if dbo.UFN_EVENTREGISTRANT_GETBALANCE(@CURRENTREGISTRANTID) < 0
begin
raiserror('BBERR_OVERPAIDCOMMITMENT', 13, 1);
end
fetch next from EVENTREGISTRANT into @CURRENTREGISTRANTID;
end
end
-- apply newly added streams
declare @NEWSPLITSDECLININGGIFTAID xml;
declare @NEWGIFTAIDSPONSORSHIPSPLITS xml;
exec dbo.USP_REVENUE_APPLYTOREVENUESTREAMS @REVENUEID, @CONSTITUENTID, @DATE, @REVENUESTREAMS, @CHANGEAGENTID, @CHANGEDATE, @NEWSPLITSDECLININGGIFTAID output, null, @NEWGIFTAIDSPONSORSHIPSPLITS output,@PDACCOUNTSYSTEMID;
if @NEWSPLITSDECLININGGIFTAID is not null
insert into @SPLITSDECLININGGIFTAIDTBL (REVENUESPLITID)
select T.c.value('(REVENUESPLITID)[1]','uniqueidentifier')
from @NEWSPLITSDECLININGGIFTAID.nodes('/SPLITSDECLININGGIFTAID/ITEM') T(c);
if @NEWGIFTAIDSPONSORSHIPSPLITS is not null
insert into @GIFTAIDSPONSORSHIPSPLITSTBL (REVENUESPLITID)
select T.c.value('(REVENUESPLITID)[1]','uniqueidentifier')
from @NEWGIFTAIDSPONSORSHIPSPLITS.nodes('/GIFTAIDSPONSORSHIPSPLITS/ITEM') T(c);
update dbo.FINANCIALTRANSACTIONLINEITEM
set TYPECODE = 99, --orphaned
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CHANGEDATE
where FINANCIALTRANSACTIONID = @REVENUEID
and TYPECODE = 0
and DELETEDON is not null
and POSTSTATUSCODE = 1
and REVERSEDLINEITEMID is null
and SOURCELINEITEMID is null
/* reset CONTEXT_INFO to previous value */
if not @CONTEXTCACHE is null
set CONTEXT_INFO @CONTEXTCACHE;
set @SPLITSDECLININGGIFTAID = ( select
REVENUESPLITID
from @SPLITSDECLININGGIFTAIDTBL
for xml raw('ITEM'),type,elements,root('SPLITSDECLININGGIFTAID'),BINARY BASE64);
set @GIFTAIDSPONSORSHIPSPLITS = ( select
REVENUESPLITID
from @GIFTAIDSPONSORSHIPSPLITSTBL
for xml raw('ITEM'),type,elements,root('GIFTAIDSPONSORSHIPSPLITS'),BINARY BASE64);
begin try
--cache current context information
set @CONTEXTCACHE = CONTEXT_INFO();
--set CONTEXT_INFO to @CHANGEAGENTID
set CONTEXT_INFO @CHANGEAGENTID;
delete REVENUESPLITBUSINESSUNIT
from dbo.REVENUESPLITBUSINESSUNIT
inner join dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLITBUSINESSUNIT.REVENUESPLITID
inner join dbo.REVENUESPLIT_EXT on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID
where FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = @REVENUEID and REVENUESPLIT_EXT.OVERRIDEBUSINESSUNITS=0;
--reset CONTEXT_INFO to previous value
if not @CONTEXTCACHE is null
set CONTEXT_INFO @CONTEXTCACHE;
end try
begin catch
if not @CONTEXTCACHE is null
set CONTEXT_INFO @CONTEXTCACHE;
exec dbo.USP_RAISE_ERROR;
return 1;
end catch
exec dbo.USP_REVENUESPLIT_APPLYBUSINESSUNITS @REVENUEID, @CHANGEAGENTID, @CHANGEDATE;
if @PRODUCTISUK = 1
begin
set @SPLITSDECLININGGIFTAID = ( select
REVENUESPLITID
from @SPLITSDECLININGGIFTAIDTBL
for xml raw('ITEM'),type,elements,root('SPLITSDECLININGGIFTAID'),BINARY BASE64);
set @GIFTAIDSPONSORSHIPSPLITS = ( select
REVENUESPLITID
from @GIFTAIDSPONSORSHIPSPLITSTBL
for xml raw('ITEM'),type,elements,root('GIFTAIDSPONSORSHIPSPLITS'),BINARY BASE64);
exec USP_MANAGEGIFTAIDFORSPLITS @REVENUEID=@REVENUEID, @ORGANIZATIONCURRENCYID=@ORGANIZATIONCURRENCYID, @CHANGEAGENTID=@CHANGEAGENTID, @CURRENTDATE=@CURRENTDATE, @DATE=@DATE, @SPLITSDECLININGGIFTAIDTBLXML=@SPLITSDECLININGGIFTAID, @GIFTAIDSPONSORSHIPSPLITSTBLXML=@GIFTAIDSPONSORSHIPSPLITS, @DELETEDANDCHANGEDSPLITSINFOXML=@DELETEDANDCHANGEDSPLITSINFO
end
while exists (select 1 from @PLANNEDGIFTUPDATES)
begin
declare @PGID uniqueidentifier;
declare @PGAID uniqueidentifier;
declare @ORIGINALAMOUNT money;
declare @NEWAMOUNT money;
declare @OLDDATE datetime;
declare @NEWDATE datetime;
declare @OLDTRANAMOUNT money;
declare @NEWTRANAMOUNT money;
declare @DONOTRECEIPT bit;
declare @RECEIPTAMOUNT money;
declare @PGBASECURRENCYID uniqueidentifier;
declare @PGTRANSACTIONCURRENCYID uniqueidentifier;
select top 1 @PGID = PLANNEDGIFTID,
@PGAID = PLANNEDGIFTADDITIONID,
@ORIGINALAMOUNT = OLDAMOUNT,
@NEWAMOUNT = NEWAMOUNT,
@OLDDATE = OLDDATE,
@NEWDATE = NEWDATE,
@OLDTRANAMOUNT = OLDTRANSACTIONAMOUNT,
@NEWTRANAMOUNT = NEWTRANSACTIONAMOUNT,
@PGBASECURRENCYID = BASECURRENCYID,
@PGTRANSACTIONCURRENCYID = TRANSACTIONCURRENCYID
from @PLANNEDGIFTUPDATES;
select
@DONOTRECEIPT = REVENUE_EXT.DONOTRECEIPT,
@RECEIPTAMOUNT = REVENUE_EXT.RECEIPTAMOUNT
from
dbo.FINANCIALTRANSACTION
inner join
dbo.REVENUE_EXT on FINANCIALTRANSACTION.ID = REVENUE_EXT.ID
where
FINANCIALTRANSACTION.ID = @REVENUEID;
exec dbo.USP_PLANNEDGIFTRECONCILE_SAVE @PGID, @REVENUEID, @PGAID, @ORIGINALAMOUNT, @NEWAMOUNT,
@OLDDATE, @NEWDATE, 0, @CHANGEAGENTID, @CHANGEDATE,
@OLDTRANAMOUNT, @NEWTRANAMOUNT,
@PREVIOUSDONOTRECEIPT, @DONOTRECEIPT,
@PREVIOUSRECEIPTAMOUNT, @RECEIPTAMOUNT,
@PGBASECURRENCYID, @PGTRANSACTIONCURRENCYID;
delete top (1) from @PLANNEDGIFTUPDATES;
end