USP_DATAFORMTEMPLATE_EDIT_MATCHINGGIFTPAYMENT
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @ID | uniqueidentifier | IN | |
| @CHANGEAGENTID | uniqueidentifier | IN | |
| @CURRENTAPPUSERID | uniqueidentifier | IN | |
| @REVENUESTREAMS | xml | IN | |
| @REMOVEDREVENUESTREAMS | xml | IN | |
| @MATCHINGGIFTCLAIMSPLITS | xml | IN |
Definition
Copy
CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_MATCHINGGIFTPAYMENT
(
@ID uniqueidentifier,
@CHANGEAGENTID uniqueidentifier,
@CURRENTAPPUSERID uniqueidentifier = null,
@REVENUESTREAMS xml,
@REMOVEDREVENUESTREAMS xml,
@MATCHINGGIFTCLAIMSPLITS xml
)
as
set nocount on;
if @REVENUESTREAMS.exist('/REVENUESTREAMS/ITEM') = 0
and @REMOVEDREVENUESTREAMS.exist('/REMOVEDREVENUESTREAMS/ITEM') = 0
and @MATCHINGGIFTCLAIMSPLITS.exist('/MATCHINGGIFTCLAIMSPLITS/ITEM') = 0
begin
--Nothing was changed, so just return.
return 0;
end
declare @CURRENTDATE datetime;
declare @ORIGINALREVENUESTREAMS xml;
--Move @REVENUESTREAMS to @MODIFIEDREVENUESTREAMS so that code copied
-- from payment edit doesn't have to be changed where it references @REVENUESTREAMS.
declare @MODIFIEDREVENUESTREAMS xml;
set @MODIFIEDREVENUESTREAMS = @REVENUESTREAMS;
set @REVENUESTREAMS = null;
declare @CLEARALLGLDISTRIBUTIONS bit;
set @CLEARALLGLDISTRIBUTIONS = 0;
declare
@DATE datetime,
@AMOUNT money,
@RECEIPTAMOUNT money,
@SOURCECODE nvarchar(50),
@POSTSTATUSCODE tinyint,
@APPEALID uniqueidentifier,
@BENEFITS xml,
@BENEFITSWAIVED bit,
@GIVENANONYMOUSLY bit,
@MAILINGID uniqueidentifier,
@CHANNELCODEID uniqueidentifier,
@DONOTRECEIPT bit,
@REFERENCE nvarchar(255),
@DONOTACKNOWLEDGE bit,
@SPLITSDECLININGGIFTAID xml,
@PERCENTAGEBENEFITS xml,
@GIFTAIDSPONSORSHIPSPLITS xml,
@BASEEXCHANGERATEID uniqueidentifier,
@EXCHANGERATE decimal(20,8),
@OLDSPOTRATEID uniqueidentifier;
declare
@PAYMENTMETHODCODE tinyint,
@CREDITTYPECODEID uniqueidentifier;
if @CHANGEAGENTID is null
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;
if @CURRENTDATE is null
set @CURRENTDATE = getdate();
declare @contextCache varbinary(128);
--cache current context information
set @contextCache = CONTEXT_INFO();
--set CONTEXT_INFO to @CHANGEAGENTID
set CONTEXT_INFO @CHANGEAGENTID;
begin try
/**
* Load current payment details
*/
--Load the original values from the payment; pass in null for anything we don't need.
exec dbo.USP_PAYMENT_LOAD
@ID,
null, --@DATALOADED output,
null, --@CONSTITUENTID output,
null, --@CONSTITUENTNAME output,
@DATE output,
@AMOUNT output,
null, --@APPLICATIONCODE output,
@RECEIPTAMOUNT output,
@ORIGINALREVENUESTREAMS output,
@SOURCECODE output,
@POSTSTATUSCODE output,
null, --@POSTDATE output,
@APPEALID output, @BENEFITS output, @BENEFITSWAIVED output,
@GIVENANONYMOUSLY output, @MAILINGID output, @CHANNELCODEID output,
@DONOTRECEIPT output, @REFERENCE output,
null, --@TSLONG output,
@DONOTACKNOWLEDGE output,
null, --@FINDERNUMBER output,
@PERCENTAGEBENEFITS output,
null, --@BASECURRENCYID output,
null, --@TRANSACTIONCURRENCYID output,
@BASEEXCHANGERATEID output,
@EXCHANGERATE output,
null, --@HADSPOTRATE output,
null, --@BASEDECIMALDIGITS output,
null, --@BASEROUNDINGTYPECODE output,
null; --@TRANSACTIONCURRENCYDESCRIPTION output;
exec dbo.USP_REVENUE_GETPAYMENTDETAILS
@REVENUEID = @ID,
@PAYMENTMETHODCODE = @PAYMENTMETHODCODE output,
@CREDITTYPECODEID = @CREDITTYPECODEID output;
declare @ORIGINALDONOTPOST bit
declare @DONOTPOST bit
select @ORIGINALDONOTPOST = case FINANCIALTRANSACTION.POSTSTATUSCODE when 3 then 1 else 0 end from dbo.FINANCIALTRANSACTION inner join dbo.REVENUE_EXT on FINANCIALTRANSACTION.ID = REVENUE_EXT.ID where FINANCIALTRANSACTION.ID = @ID and FINANCIALTRANSACTION.DELETEDON is null;
set @DONOTPOST = @ORIGINALDONOTPOST;
/**
* Update matching gift claim splits
*/
--We do this after loading the payment details so that the tweaks
-- we make here don't affect the original values that we load.
declare @MATCHINGGIFTCLAIMLINEITEMMAP xml;
exec dbo.USP_MATCHINGGIFTPAYMENT_UPDATEMATCHINGGIFTCLAIMS
@ID,
@MATCHINGGIFTCLAIMSPLITS,
@CHANGEAGENTID,
@CURRENTDATE,
@MATCHINGGIFTCLAIMLINEITEMMAP output;
/**
* Edit the payment applications
*/
--Build the modified revenue streams by combining the current revenue
-- streams (@ORIGINALREVENUESTREAMS, loaded above) with the revenue
-- streams from this form (@MATCHINGGIFTCLAIMLINEITEMMAP, computed
-- when updating claim splits earlier).
with
MATCHINGGIFTCLAIMLINEITEMMAP as
(
select
MATCHINGGIFTCLAIMLINEITEMMAP.ITEM.value('(FINANCIALTRANSACTIONLINEITEMID)[1]', 'uniqueidentifier') as FINANCIALTRANSACTIONLINEITEMID,
MATCHINGGIFTCLAIMLINEITEMMAP.ITEM.value('(REPLACEMENTFINANCIALTRANSACTIONLINEITEMID)[1]', 'uniqueidentifier') as REPLACEMENTFINANCIALTRANSACTIONLINEITEMID
from
@MATCHINGGIFTCLAIMLINEITEMMAP.nodes('/MATCHINGGIFTCLAIMLINEITEMMAP/ITEM') as MATCHINGGIFTCLAIMLINEITEMMAP(ITEM)
)
select @MODIFIEDREVENUESTREAMS =
(
select
MODIFIEDREVENUESTREAMS.ITEM.query('./*[local-name() != "APPLICATIONSPLITS"]'),
(
select
coalesce(MATCHINGGIFTCLAIMLINEITEMMAP.REPLACEMENTFINANCIALTRANSACTIONLINEITEMID, APPLICATIONSPLITS.ITEM.value('(ID)[1]', 'uniqueidentifier')) as ID,
APPLICATIONSPLITS.ITEM.value('(AMOUNT)[1]', 'money') as AMOUNT
from
MODIFIEDREVENUESTREAMS.ITEM.nodes('./APPLICATIONSPLITS/ITEM') as APPLICATIONSPLITS(ITEM)
left join MATCHINGGIFTCLAIMLINEITEMMAP on APPLICATIONSPLITS.ITEM.value('(ID)[1]', 'uniqueidentifier') = MATCHINGGIFTCLAIMLINEITEMMAP.FINANCIALTRANSACTIONLINEITEMID
for xml raw('ITEM'),type,elements,root('APPLICATIONSPLITS'), binary base64
)
from
@MODIFIEDREVENUESTREAMS.nodes('/REVENUESTREAMS/ITEM') as MODIFIEDREVENUESTREAMS(ITEM)
for xml raw('ITEM'),type,elements,root('REVENUESTREAMS'), binary base64
);
set @REVENUESTREAMS =
(
select
MERGEDREVENUESTREAMS.ITEMXML.query('ITEM/*')
from
(
select
--Use the modified revenue stream item if one was supplied for this ID, otherwise fall back to the
-- original revenue stream item.
coalesce(MODIFIEDREVENUESTREAMS.ITEM.query('.'), ORIGINALREVENUESTREAMS.ITEM.query('.')) as ITEMXML
from
@ORIGINALREVENUESTREAMS.nodes('/REVENUESTREAMS/ITEM') as ORIGINALREVENUESTREAMS(ITEM)
full join @MODIFIEDREVENUESTREAMS.nodes('/REVENUESTREAMS/ITEM') as MODIFIEDREVENUESTREAMS(ITEM) on
ORIGINALREVENUESTREAMS.ITEM.value('ID[1]', 'uniqueidentifier') = MODIFIEDREVENUESTREAMS.ITEM.value('ID[1]', 'uniqueidentifier')
full join @REMOVEDREVENUESTREAMS.nodes('/REMOVEDREVENUESTREAMS/ITEM') as REMOVEDREVENUESTREAMS(ITEM) on
REMOVEDREVENUESTREAMS.ITEM.value('ID[1]','uniqueidentifier') = ORIGINALREVENUESTREAMS.ITEM.value('ID[1]', 'uniqueidentifier')
where
isnull(MODIFIEDREVENUESTREAMS.ITEM.exist('(ID)[1]'), 0) = 1
or
(
isnull(ORIGINALREVENUESTREAMS.ITEM.exist('(ID)[1]'), 0) = 1
and
REMOVEDREVENUESTREAMS.ITEM.query('.') is null
)
) as MERGEDREVENUESTREAMS
for xml raw('ITEM'),type,elements,root('REVENUESTREAMS'), binary base64
);
/* Check if revenue streams changed */
if @CLEARALLGLDISTRIBUTIONS = 0
if dbo.UFN_CHECKDETAIL_STREAMSCHANGED(@ID, @REVENUESTREAMS) = 1
begin
set @CLEARALLGLDISTRIBUTIONS = 1;
end
declare @OLDAUCTIONPURCHASES xml;
--Remove conditional because we can't know yet if multicurrency fields will change, clearing GL distributions.
--if @CLEARALLGLDISTRIBUTIONS = 1 or @CLEARAUCTIONPURCHASEGLDISTRIBUTION = 1
set @OLDAUCTIONPURCHASES = (
select AUCTIONITEM.ID as AUCTIONITEMID,AUCTIONITEM.REVENUEAUCTIONDONATIONID,REVENUESPLIT.ID,REVENUESPLIT.BASEAMOUNT as AMOUNT
from dbo.AUCTIONITEM inner join dbo.AUCTIONITEMPURCHASE on AUCTIONITEM.ID = AUCTIONITEMPURCHASE.AUCTIONITEMID
inner join dbo.FINANCIALTRANSACTIONLINEITEM REVENUESPLIT on REVENUESPLIT.ID = AUCTIONITEMPURCHASE.PURCHASEID
inner join dbo.REVENUESPLIT_EXT on REVENUESPLIT.ID = REVENUESPLIT_EXT.ID
where REVENUESPLIT.FINANCIALTRANSACTIONID = @ID and REVENUESPLIT_EXT.APPLICATIONCODE = 12 and REVENUESPLIT.DELETEDON is null and REVENUESPLIT.TYPECODE != 1
for xml raw('ITEM'),type,elements,root('AUCTIONPURCHASES'),BINARY BASE64
)
declare @OLDGIFTAID xml;
set @OLDGIFTAID = (
select REVENUESPLIT.ID, REVENUESPLIT_EXT.DESIGNATIONID, coalesce(REVENUECATEGORY.GLREVENUECATEGORYMAPPINGID, '00000000-0000-0000-0000-000000000000') as GLREVENUECATEGORYMAPPINGID, dbo.UFN_GIFTAIDREVENUESPLIT_GETQUALIFICATIONSTATUS(REVENUESPLIT.ID) as STATUS
from dbo.FINANCIALTRANSACTIONLINEITEM REVENUESPLIT
inner join dbo.REVENUESPLIT_EXT on REVENUESPLIT.ID = REVENUESPLIT_EXT.ID
inner join dbo.REVENUESPLITGIFTAID on REVENUESPLIT.ID = REVENUESPLITGIFTAID.ID
left join dbo.REVENUECATEGORY on REVENUESPLIT.ID = REVENUECATEGORY.ID
where REVENUESPLIT.FINANCIALTRANSACTIONID = @ID and REVENUESPLIT.DELETEDON is null and REVENUESPLIT.TYPECODE != 1
for xml raw('ITEM'),type,elements,root('GIFTAIDQUALIFICATIONSTATUS'),BINARY BASE64
)
exec dbo.USP_PAYMENT_EDIT_2
@ID, @CHANGEAGENTID, @CURRENTDATE, @DATE, @AMOUNT, @RECEIPTAMOUNT,
@REVENUESTREAMS, @SOURCECODE, @APPEALID,@BENEFITS, @BENEFITSWAIVED,
@GIVENANONYMOUSLY, @MAILINGID, @CHANNELCODEID, @DONOTRECEIPT, @REFERENCE, @DONOTACKNOWLEDGE,
@SPLITSDECLININGGIFTAID output, @PERCENTAGEBENEFITS, @GIFTAIDSPONSORSHIPSPLITS output,
@BASEEXCHANGERATEID output, @EXCHANGERATE, @OLDSPOTRATEID output, @CURRENTAPPUSERID;
-- clear the user-defined gl distributions
if @CLEARALLGLDISTRIBUTIONS = 1
begin
-- Clear GL
delete from dbo.REVENUEGLDISTRIBUTION where REVENUEID = @ID and OUTDATED = 0;
delete from dbo.STOCKSALEGLDISTRIBUTION where REVENUEID = @ID and OUTDATED = 0;
delete from dbo.GIFTINKINDSALEGLDISTRIBUTION where REVENUEID = @ID and OUTDATED = 0;
delete from dbo.PROPERTYDETAILGLDISTRIBUTION where REVENUEID = @ID and OUTDATED = 0;
delete from dbo.BENEFITGLDISTRIBUTION where REVENUEID = @ID and OUTDATED = 0;
-- Add new GL distributions
if @POSTSTATUSCODE <> 2
begin
-- Add new GL distributions
exec dbo.USP_SAVE_REVENUEGLDISTRIBUTION @ID, @CHANGEAGENTID, @CURRENTDATE;
-- Add new stock detail GL distributions
exec dbo.USP_SAVE_STOCKDETAILGLDISTRIBUTION @ID, @CHANGEAGENTID, @CURRENTDATE;
-- Add new gift-in-kind detail GL distributions
exec dbo.USP_SAVE_GIFTINKINDPAYMENTMETHODDETAILGLDISTRIBUTION @ID, @CHANGEAGENTID, @CURRENTDATE;
-- Add new property detail GL distributions
exec dbo.USP_SAVE_PROPERTYDETAILGLDISTRIBUTION @ID, @CHANGEAGENTID, @CURRENTDATE;
-- add benefit distributions
exec dbo.USP_SAVE_PAYMENT_BENEFITGLDISTRIBUTION @ID, @CHANGEAGENTID, @CURRENTDATE;
declare @DEPOSITID uniqueidentifier;
select @DEPOSITID = DEPOSITID
from dbo.BANKACCOUNTDEPOSITPAYMENT
where ID = @ID;
if @DEPOSITID is not null
exec dbo.USP_BANKACCOUNTDEPOSIT_OVERWRITEPAYMENTDEBITACCOUNTS @ID, @DEPOSITID, @CHANGEAGENTID, @CURRENTDATE;
end
end
--Check to make sure gift amount is more than the gift Fee
--####
declare @GIFTFEE_ENABLED bit;
declare @GIFTFEEAMOUNT money;
set @GIFTFEEAMOUNT = 0.00;
select @GIFTFEE_ENABLED = dbo.UFN_GIFTFEE_ENABLED();
if @GIFTFEE_ENABLED = 1 and exists (select 1 from dbo.REVENUESPLITGIFTFEE inner join dbo.FINANCIALTRANSACTIONLINEITEM on REVENUESPLITGIFTFEE.ID = FINANCIALTRANSACTIONLINEITEM.ID
inner join dbo.REVENUESPLIT_EXT on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID
where FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = @ID and REVENUESPLITGIFTFEE.WAIVED <> 1
and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null and FINANCIALTRANSACTIONLINEITEM.TYPECODE <> 1)
begin
select @GIFTFEEAMOUNT = coalesce(sum(REVENUESPLITGIFTFEE.TRANSACTIONAMOUNT), 0.00)
from dbo.REVENUESPLITGIFTFEE
inner join dbo.FINANCIALTRANSACTIONLINEITEM on REVENUESPLITGIFTFEE.ID = FINANCIALTRANSACTIONLINEITEM.ID
inner join dbo.REVENUESPLIT_EXT on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID
where FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = @ID
and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null
and FINANCIALTRANSACTIONLINEITEM.TYPECODE <> 1;
if @GIFTFEEAMOUNT > @AMOUNT
-- The gift fee cannot be greater than the application amount
raiserror('BBERR_AMOUNTLESSTHANGIFTFEE', 13, 1);
--####
end
exec dbo.USP_GIFTAIDREVENUESPLIT_ADDUPDATESPLITS @ID, @APPEALID, @PAYMENTMETHODCODE, @CREDITTYPECODEID, @CHANGEAGENTID, @DATE, 0, @SPLITSDECLININGGIFTAID, null, @GIFTAIDSPONSORSHIPSPLITS; --revenue transaction type code of payment is 0
-- update existing gift aid GL distributions
exec dbo.USP_REVENUESPLITGIFTAID_UPDATEGLDISTRIBUTIONS_FORREVENUE @ID, @OLDGIFTAID, @ORIGINALDONOTPOST, @DONOTPOST, @CHANGEAGENTID, @CURRENTDATE;
/**
* Validate the modified matching gift claims.
* This step comes after editing the payment, since a modified matching
* gift claim designation will be accompanied by an updated payment revenue split.
*/
declare MODIFIEDMATCHINGGIFTCLAIMTOVALIDATECURSOR cursor local fast_forward for
select
FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID
from
@MATCHINGGIFTCLAIMSPLITS.nodes('/MATCHINGGIFTCLAIMSPLITS/ITEM') as MATCHINGGIFTCLAIMSPLITS(ITEM)
inner join dbo.FINANCIALTRANSACTIONLINEITEM on MATCHINGGIFTCLAIMSPLITS.ITEM.value('REVENUESPLITID[1]', 'uniqueidentifier') = FINANCIALTRANSACTIONLINEITEM.ID
group by
FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID;
declare @MODIFIEDMATCHINGGIFTCLAIMTOVALIDATEREVENUEID uniqueidentifier;
open MODIFIEDMATCHINGGIFTCLAIMTOVALIDATECURSOR;
fetch next from MODIFIEDMATCHINGGIFTCLAIMTOVALIDATECURSOR into
@MODIFIEDMATCHINGGIFTCLAIMTOVALIDATEREVENUEID;
while @@FETCH_STATUS = 0
begin
exec dbo.USP_PLEDGE_VALIDATE_2 @MODIFIEDMATCHINGGIFTCLAIMTOVALIDATEREVENUEID, 0;
fetch next from MODIFIEDMATCHINGGIFTCLAIMTOVALIDATECURSOR into
@MODIFIEDMATCHINGGIFTCLAIMTOVALIDATEREVENUEID;
end
close MODIFIEDMATCHINGGIFTCLAIMTOVALIDATECURSOR;
deallocate MODIFIEDMATCHINGGIFTCLAIMTOVALIDATECURSOR;
end try
begin catch
--reset CONTEXT_INFO to previous value
if not @contextCache is null
set CONTEXT_INFO @contextCache;
exec dbo.USP_RAISE_ERROR;
return 1;
end catch
--reset CONTEXT_INFO to previous value
if not @contextCache is null
set CONTEXT_INFO @contextCache;
return 0;