USP_DATAFORMTEMPLATE_EDIT_MATCHINGGIFTPAYMENTADJUST
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @ID | uniqueidentifier | IN | |
| @CHANGEAGENTID | uniqueidentifier | IN | |
| @CURRENTAPPUSERID | uniqueidentifier | IN | |
| @REVENUESTREAMS | xml | IN | |
| @REMOVEDREVENUESTREAMS | xml | IN | |
| @MATCHINGGIFTCLAIMSPLITS | xml | IN | |
| @ADJUSTMENTDATE | datetime | IN | |
| @ADJUSTMENTPOSTDATE | datetime | IN | |
| @ADJUSTMENTREASON | nvarchar(300) | IN | |
| @ADJUSTMENTREASONCODEID | uniqueidentifier | IN | |
| @ADJUSTMENTPOSTSTATUSCODE | tinyint | IN | |
| @DEPOSITID | uniqueidentifier | IN |
Definition
Copy
CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_MATCHINGGIFTPAYMENTADJUST
(
@ID uniqueidentifier,
@CHANGEAGENTID uniqueidentifier,
@CURRENTAPPUSERID uniqueidentifier = null,
@REVENUESTREAMS xml,
@REMOVEDREVENUESTREAMS xml,
@MATCHINGGIFTCLAIMSPLITS xml,
@ADJUSTMENTDATE datetime,
@ADJUSTMENTPOSTDATE datetime,
@ADJUSTMENTREASON nvarchar(300),
@ADJUSTMENTREASONCODEID uniqueidentifier,
@ADJUSTMENTPOSTSTATUSCODE tinyint,
@DEPOSITID uniqueidentifier
)
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 @ADJUSTMENTID uniqueidentifier;
declare @STOCKSALEADJUSTMENTIDS xml;
declare @GIFTINKINDSALEADJUSTMENTIDS xml;
declare @PROPERTYDETAILADJUSTMENTID uniqueidentifier;
declare @ADJUST bit;
declare @PROPERTYDETAILCOUNT int;
declare @STOCKDETAILCOUNT int;
declare @GIFTINKINDPAYMENTMETHODDETAILCOUNT int;
declare @ADJUSTMENTORIGINALPOSTSTATUS int;
declare @CLEARALLGLDISTRIBUTIONS bit;
declare @ADJUSTGIFTFEEDISTRIBUTION bit;
set @ADJUST = 0;
set @PROPERTYDETAILCOUNT = 0;
set @STOCKDETAILCOUNT = 0;
set @GIFTINKINDPAYMENTMETHODDETAILCOUNT = 0;
set @ADJUSTMENTORIGINALPOSTSTATUS = 0;
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;
/**
* 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
*/
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;
--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
);
declare @ORIGINALPAYMETHODID uniqueidentifier,
@ORIGINALPAYMENTMETHODCODE tinyint,
@ORIGINALGIFTINKINDSUBTYPECODEID uniqueidentifier,
@ORIGINALCREDITTYPECODEID uniqueidentifier,
@ORIGINALPROPERTYSUBTYPECODEID uniqueidentifier,
@ORIGINALOTHERPAYMENTMETHODCODEID uniqueidentifier
select
@ORIGINALPAYMETHODID = RPM.ID,
@ORIGINALPAYMENTMETHODCODE = PAYMENTMETHODCODE,
@ORIGINALGIFTINKINDSUBTYPECODEID = GIFTINKINDSUBTYPECODEID,
@ORIGINALCREDITTYPECODEID = CREDITTYPECODEID,
@ORIGINALPROPERTYSUBTYPECODEID = PROPERTYSUBTYPECODEID,
@ORIGINALOTHERPAYMENTMETHODCODEID = OTHERPAYMENTMETHODCODEID
from
dbo.REVENUEPAYMENTMETHOD RPM
left outer join dbo.GIFTINKINDPAYMENTMETHODDETAIL GIKPMD on RPM.ID = GIKPMD.ID
left outer join dbo.CREDITCARDPAYMENTMETHODDETAIL CCPMD on RPM.ID = CCPMD.ID
left outer join dbo.PROPERTYDETAIL PD on RPM.ID = PD.ID
left outer join dbo.OTHERPAYMENTMETHODDETAIL OPMD on RPM.ID = OPMD.ID
where
RPM.REVENUEID = @ID
-- Check GL business rule for this account system and set to 'Do not post' if needed.
declare @PDACCOUNTSYSTEMID uniqueidentifier;
select @PDACCOUNTSYSTEMID = PDACCOUNTSYSTEMID from dbo.PDACCOUNTSYSTEMFORREVENUE where ID = @ID;
if dbo.UFN_PDACCOUNTSYSTEM_ALLOWGLDISTRIBUTIONS(@PDACCOUNTSYSTEMID) = 0
begin
set @ADJUSTMENTPOSTSTATUSCODE = 2 -- Do not post
set @ADJUSTMENTPOSTDATE = null
end
-- Only unlink the payment if adjustment is postable - Bug 70136
if @ADJUSTMENTPOSTSTATUSCODE <> 2
begin
if (not (@PAYMENTMETHODCODE in (0,1,2,10))) or @PAYMENTMETHODCODE is null
if exists(select DEPOSITID from dbo.BANKACCOUNTDEPOSITPAYMENT where ID = @ID and DEPOSITID is not null)
update dbo.BANKACCOUNTDEPOSITPAYMENT set DEPOSITID = NULL where ID = @ID;
declare @ORIGINALADJUSTMENTPOSTDATE datetime
if exists(select DEPOSITID from dbo.BANKACCOUNTDEPOSITPAYMENT where ID = @ID and DEPOSITID is not null)
begin
select
@ORIGINALADJUSTMENTPOSTDATE = POSTDATE
from
dbo.BANKACCOUNTDEPOSITPAYMENT
inner join dbo.BANKACCOUNTDEPOSIT on BANKACCOUNTDEPOSITPAYMENT.DEPOSITID = BANKACCOUNTDEPOSIT.ID
inner join dbo.BANKACCOUNTTRANSACTION on BANKACCOUNTDEPOSIT.ID = BANKACCOUNTTRANSACTION.ID
where
BANKACCOUNTDEPOSITPAYMENT.ID = @ID;
if @ORIGINALADJUSTMENTPOSTDATE <> @ADJUSTMENTPOSTDATE
update dbo.BANKACCOUNTDEPOSITPAYMENT set DEPOSITID = @DEPOSITID where ID = @ID;
end
end
/* Check if revenue streams changed */
if dbo.UFN_CHECKDETAIL_STREAMSCHANGED(@ID, @REVENUESTREAMS) = 1
begin
set @ADJUST = 1;
set @CLEARALLGLDISTRIBUTIONS = 1;
/* Already adjusted */
if @ADJUST = 0
if (select
COUNT(ADJUSTMENT.ID)
from
dbo.ADJUSTMENT
where
ADJUSTMENT.REVENUEID = @ID and ADJUSTMENT.POSTSTATUSCODE > 0
) > 0
set @ADJUST = 1;
if (select
COUNT(ADJUSTMENT.ID)
from
dbo.ADJUSTMENT
where
ADJUSTMENT.REVENUEID = @ID and ADJUSTMENT.POSTSTATUSCODE = 2
) > 0
set @ADJUSTMENTORIGINALPOSTSTATUS = 2;
/* If there was a change to GL related data log an adjustment for each revenue in the transaction */
if @ADJUST = 1
begin
if @ADJUSTMENTREASONCODEID is null
raiserror('BBERR_ADJUSTMENTREASONCODEIDREQUIRED', 13, 1)
set @ADJUSTMENTID = null;
exec dbo.USP_SAVE_ADJUSTMENT
@ID,
@ADJUSTMENTID output,
@CHANGEAGENTID,
@CURRENTDATE,
@ADJUSTMENTDATE,
@ADJUSTMENTPOSTDATE,
@ADJUSTMENTREASON,
default,
@ADJUSTMENTREASONCODEID,
@ADJUSTMENTPOSTSTATUSCODE;
select
@STOCKDETAILCOUNT = count(STOCKSALE.ID)
from
dbo.STOCKSALE
where
STOCKDETAILID = @ORIGINALPAYMETHODID and SALEPOSTSTATUSCODE = 0;
select
@GIFTINKINDPAYMENTMETHODDETAILCOUNT = count(GIFTINKINDSALE.ID)
from
dbo.GIFTINKINDSALE
where
GIFTINKINDPAYMENTMETHODDETAILID = @ORIGINALPAYMETHODID and SALEPOSTSTATUSCODE = 0;
select
@PROPERTYDETAILCOUNT = count(PROPERTYDETAIL.ID)
from
dbo.PROPERTYDETAIL
where
PROPERTYDETAIL.ID = @ORIGINALPAYMETHODID and SALEPOSTSTATUSCODE = 0;
/* If sold stock has been posted, log stock detail adjustment */
if (@STOCKDETAILCOUNT > 0) and @ORIGINALPAYMENTMETHODCODE = 4
begin
exec dbo.USP_SAVE_STOCKDETAILADJUSTMENT
@ORIGINALPAYMETHODID,
@CHANGEAGENTID,
@CURRENTDATE,
@ADJUSTMENTDATE,
@ADJUSTMENTPOSTDATE,
@ADJUSTMENTREASON,
@STOCKSALEADJUSTMENTIDS output,
@ADJUSTMENTREASONCODEID,
@ADJUSTMENTPOSTSTATUSCODE;
end
/* If the sold gift-in-kind has been posted, log the gift-in-kind detail adjustment */
if (@GIFTINKINDPAYMENTMETHODDETAILCOUNT > 0) and @ORIGINALPAYMENTMETHODCODE = 6
begin
exec dbo.USP_SAVE_GIFTINKINDPAYMENTMETHODDETAILADJUSTMENT
@ORIGINALPAYMETHODID,
@CHANGEAGENTID,
@CURRENTDATE,
@ADJUSTMENTDATE,
@ADJUSTMENTPOSTDATE,
@ADJUSTMENTREASON,
@GIFTINKINDSALEADJUSTMENTIDS output,
@ADJUSTMENTREASONCODEID,
@ADJUSTMENTPOSTSTATUSCODE;
end
/* If sold property has been posted, log property detail adjustment */
else if (@ORIGINALPAYMENTMETHODCODE = 5) and (@PROPERTYDETAILCOUNT > 0)
begin
exec dbo.USP_SAVE_PROPERTYDETAILADJUSTMENT
@ORIGINALPAYMETHODID,
@PROPERTYDETAILADJUSTMENTID output,
@CHANGEAGENTID,
@CURRENTDATE,
@ADJUSTMENTDATE,
@ADJUSTMENTPOSTDATE,
@ADJUSTMENTREASON,
@ADJUSTMENTREASONCODEID,
@ADJUSTMENTPOSTSTATUSCODE;
end
end
if @ADJUSTGIFTFEEDISTRIBUTION = 0 and (select count(ID) from dbo.GIFTFEEADJUSTMENT where REVENUEID = @ID and POSTSTATUSCODE <> 0) > 0
set @ADJUSTGIFTFEEDISTRIBUTION = 1;
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
)
-- create reversals for gift aid tied to splits that have been deleted.
exec dbo.USP_REVENUESPLITGIFTAID_CREATEREVERSALSFORDELETEDSPLITS @OLDGIFTAID, @REVENUESTREAMS, @CHANGEAGENTID, @CURRENTDATE;
exec dbo.USP_PAYMENT_EDIT_2
@ID=@ID
,@CHANGEAGENTID=@CHANGEAGENTID
,@CURRENTDATE=@CURRENTDATE
,@DATE=@DATE
,@AMOUNT=@AMOUNT
,@RECEIPTAMOUNT=@RECEIPTAMOUNT
,@REVENUESTREAMS=@REVENUESTREAMS
,@SOURCECODE=@SOURCECODE
,@APPEALID=@APPEALID
,@BENEFITS=@BENEFITS
,@BENEFITSWAIVED=@BENEFITSWAIVED
,@GIVENANONYMOUSLY=@GIVENANONYMOUSLY
,@MAILINGID=@MAILINGID
,@CHANNELCODEID=@CHANNELCODEID
,@DONOTRECEIPT=@DONOTRECEIPT
,@REFERENCE=@REFERENCE
,@DONOTACKNOWLEDGE=@DONOTACKNOWLEDGE
,@SPLITSDECLININGGIFTAID=@SPLITSDECLININGGIFTAID output
,@PERCENTAGEBENEFITS=@PERCENTAGEBENEFITS output
,@GIFTAIDSPONSORSHIPSPLITS=@GIFTAIDSPONSORSHIPSPLITS output
,@BASEEXCHANGERATEID=@BASEEXCHANGERATEID output
,@EXCHANGERATE=@EXCHANGERATE
,@CURRENTAPPUSERID=@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 JOURNALENTRY
from dbo.JOURNALENTRY inner join dbo.JOURNALENTRY_EXT on JOURNALENTRY.ID = JOURNALENTRY_EXT.ID
inner join dbo.FINANCIALTRANSACTIONLINEITEM on JOURNALENTRY.FINANCIALTRANSACTIONLINEITEMID = FINANCIALTRANSACTIONLINEITEM.ID
where FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = @ID
and JOURNALENTRY_EXT.OUTDATED = 0
and JOURNALENTRY_EXT.TABLENAMECODE in (1,11,13,10)
and FINANCIALTRANSACTIONLINEITEM.POSTSTATUSCODE != 2
and FINANCIALTRANSACTIONLINEITEM.TYPECODE != 1
-- Add new GL distributions
if @ADJUSTMENTPOSTSTATUSCODE <> 2
exec dbo.USP_SAVE_REVENUEGLDISTRIBUTION
@ID,
@CHANGEAGENTID,
@CURRENTDATE;
-- If the payment is linked to a deposit then overwrite the debit account with the bank's default account
select @DEPOSITID = DEPOSITID from dbo.BANKACCOUNTDEPOSITPAYMENT where ID = @ID;
if @DEPOSITID is not null
exec dbo.USP_BANKACCOUNTDEPOSIT_OVERWRITEPAYMENTDEBITACCOUNTS
@ID,
@DEPOSITID,
@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;
end
else
begin
if @ADJUSTMENTPOSTSTATUSCODE = 2
begin
/*
delete from dbo.REVENUEGLDISTRIBUTION where REVENUEID = @ID and OUTDATED = 0;
delete from dbo.PROPERTYDETAILGLDISTRIBUTION where REVENUEID = @ID and OUTDATED = 0;
*/
delete JOURNALENTRY
from dbo.JOURNALENTRY inner join dbo.JOURNALENTRY_EXT on JOURNALENTRY.ID = JOURNALENTRY_EXT.ID
inner join dbo.FINANCIALTRANSACTIONLINEITEM on JOURNALENTRY.FINANCIALTRANSACTIONLINEITEMID = FINANCIALTRANSACTIONLINEITEM.ID
where FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = @ID
and JOURNALENTRY_EXT.OUTDATED = 0
and JOURNALENTRY_EXT.TABLENAMECODE in (1,10)
and FINANCIALTRANSACTIONLINEITEM.POSTSTATUSCODE != 2
and FINANCIALTRANSACTIONLINEITEM.TYPECODE != 1
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
/* add adjustment history information */
if @ADJUST = 1
begin
if @ADJUSTMENTID is not null
exec dbo.USP_ADJUSTMENTHISTORY_REVENUE_SAVEHISTORY
@ID,
@CHANGEAGENTID,
null,
@ADJUSTMENTID;
if @STOCKSALEADJUSTMENTIDS is not null
exec dbo.USP_ADJUSTMENTHISTORY_STOCK_SAVEHISTORY
@ORIGINALPAYMETHODID,
@CHANGEAGENTID,
null,
@STOCKSALEADJUSTMENTIDS;
if @GIFTINKINDSALEADJUSTMENTIDS is not null
exec dbo.USP_ADJUSTMENTHISTORY_GIFTINKIND_SAVEHISTORY
@ORIGINALPAYMETHODID,
@CHANGEAGENTID,
null,
@GIFTINKINDSALEADJUSTMENTIDS;
if @PROPERTYDETAILADJUSTMENTID is not null
exec dbo.USP_ADJUSTMENTHISTORY_PROPERTY_SAVEHISTORY
@ORIGINALPAYMETHODID,
@CHANGEAGENTID,
null,
@PROPERTYDETAILADJUSTMENTID;
end
exec dbo.USP_GIFTAIDREVENUESPLIT_ADDUPDATESPLITS @ID, @APPEALID, @PAYMENTMETHODCODE, @CREDITTYPECODEID, @CHANGEAGENTID, @DATE, 0, @SPLITSDECLININGGIFTAID, null, @GIFTAIDSPONSORSHIPSPLITS; --revenue transaction type code of payment is 0
-- Add/Update gift aid distributions ONLY if exists for original payment - Bug 70202
if exists (
select 1
from
--dbo.GIFTAIDGLDISTRIBUTION t3
dbo.JOURNALENTRY_EXT t3
inner join dbo.REVENUESPLITGIFTAID t1 on t3.REVENUESPLITGIFTAIDID=t1.ID
inner join dbo.FINANCIALTRANSACTIONLINEITEM t2 on t1.ID = t2.ID
inner join dbo.REVENUESPLIT_EXT on t2.ID = REVENUESPLIT_EXT.ID
where
t2.FINANCIALTRANSACTIONID = @ID
and t2.DELETEDON is null
and t2.TYPECODE <> 1
and t3.TABLENAMECODE = 7
)
-- update existing gift aid GL distributions
exec dbo.USP_REVENUESPLITGIFTAID_UPDATEGLDISTRIBUTIONS_FORREVENUE
@ID,
@OLDGIFTAID,
0,
0,
@CHANGEAGENTID,
@CURRENTDATE;
--If original adjustment was set to Do not post and now it is Not Posted, then update the gltransation
if @ADJUSTMENTORIGINALPOSTSTATUS = 2 and @ADJUSTMENTPOSTSTATUSCODE = 1
begin
--JamesWill 76594 Also change the reversal post date to not be null
--RobertJo 168818 Added cases so that it would adjust the other databases as well
update FTLI2
set POSTSTATUSCODE = 1,
POSTDATE = @ADJUSTMENTPOSTDATE,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = getdate()
from dbo.FINANCIALTRANSACTIONLINEITEM inner join dbo.JOURNALENTRY on FINANCIALTRANSACTIONLINEITEM.ID = JOURNALENTRY.FINANCIALTRANSACTIONLINEITEMID
inner join dbo.JOURNALENTRY_EXT on JOURNALENTRY.ID = JOURNALENTRY_EXT.REVERSEDGLTRANSACTIONID
inner join dbo.JOURNALENTRY JE2 on JOURNALENTRY_EXT.ID = JE2.ID
inner join dbo.FINANCIALTRANSACTIONLINEITEM FTLI2 on JE2.FINANCIALTRANSACTIONLINEITEMID = FTLI2.ID and FTLI2.POSTSTATUSCODE != 2
where FTLI2.FINANCIALTRANSACTIONID = @ID
and JOURNALENTRY_EXT.TABLENAMECODE in (0,1,5,11,10,13)
and FINANCIALTRANSACTIONLINEITEM.TYPECODE in (0,1,3,5,6,7,8,98)
and FINANCIALTRANSACTIONLINEITEM.POSTSTATUSCODE = 2
/*
update dbo.GLTRANSACTION
set
POSTSTATUSCODE = 1,
POSTDATE = @ADJUSTMENTPOSTDATE
where
POSTSTATUSCODE > 0
and REVERSEDGLTRANSACTIONID in
(
select
GLTRANSACTIONID
from
dbo.REVENUEGLDISTRIBUTION
where
REVENUEID = @ID
union all
select
GLTRANSACTIONID
from
dbo.BENEFITGLDISTRIBUTION
where
REVENUEID = @ID
union all
select
GLTRANSACTIONID
from
dbo.STOCKSALEGLDISTRIBUTION
where
REVENUEID = @ID
union all
select
GLTRANSACTIONID
from
dbo.PROPERTYDETAILGLDISTRIBUTION
where
REVENUEID = @ID
union all
select
GLTRANSACTIONID
from
dbo.GIFTINKINDSALEGLDISTRIBUTION
where
REVENUEID = @ID
) */
exec dbo.USP_SAVE_REVENUEGLDISTRIBUTION
@ID,
@CHANGEAGENTID,
@CURRENTDATE;
select
@DEPOSITID = DEPOSITID
from
dbo.BANKACCOUNTDEPOSITPAYMENT
where
ID = @ID;
if @DEPOSITID is not null
exec dbo.USP_BANKACCOUNTDEPOSIT_OVERWRITEPAYMENTDEBITACCOUNTS
@ID,
@DEPOSITID,
@CHANGEAGENTID,
@CURRENTDATE;
end
else
if @ADJUSTMENTORIGINALPOSTSTATUS = 0 and @ADJUSTMENTPOSTSTATUSCODE = 2
begin
--RobertJo 168818 Added cases so that it would adjust the other databases as well
update FTLI2
set POSTSTATUSCODE = 3,
POSTDATE = null,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = getdate()
from dbo.FINANCIALTRANSACTIONLINEITEM inner join dbo.JOURNALENTRY on FINANCIALTRANSACTIONLINEITEM.ID = JOURNALENTRY.FINANCIALTRANSACTIONLINEITEMID
inner join dbo.JOURNALENTRY_EXT on JOURNALENTRY.ID = JOURNALENTRY_EXT.REVERSEDGLTRANSACTIONID
inner join dbo.JOURNALENTRY JE2 on JOURNALENTRY_EXT.ID = JE2.ID
inner join dbo.FINANCIALTRANSACTIONLINEITEM FTLI2 on JE2.FINANCIALTRANSACTIONLINEITEMID = FTLI2.ID and FTLI2.POSTSTATUSCODE != 2
where FTLI2.FINANCIALTRANSACTIONID = @ID
and JOURNALENTRY_EXT.TABLENAMECODE in (0,1,5,11,10,13)
and FINANCIALTRANSACTIONLINEITEM.TYPECODE in (0,1,3,5,6,7,8,98)
and FINANCIALTRANSACTIONLINEITEM.POSTSTATUSCODE = 2
/*
update dbo.GLTRANSACTION
set
POSTSTATUSCODE = 2
where
POSTSTATUSCODE = 1
and REVERSEDGLTRANSACTIONID in
(
select
GLTRANSACTIONID
from
dbo.REVENUEGLDISTRIBUTION
where
REVENUEID = @ID
union all
select
GLTRANSACTIONID
from
dbo.BENEFITGLDISTRIBUTION
where
REVENUEID = @ID
union all
select
GLTRANSACTIONID
from
dbo.STOCKSALEGLDISTRIBUTION
where
REVENUEID = @ID
union all
select
GLTRANSACTIONID
from
dbo.PROPERTYDETAILGLDISTRIBUTION
where
REVENUEID = @ID
union all
select
GLTRANSACTIONID
from
dbo.GIFTINKINDSALEGLDISTRIBUTION
where
REVENUEID = @ID
)
*/
end
-- update existing auction purchase GL distributions
if @CLEARALLGLDISTRIBUTIONS = 1
exec dbo.USP_REVENUE_UPDATEAUCTIONPURCHASEGLDISTRIBUTION
@REVENUEID = @ID,
@OLDAUCTIONPURCHASES = @OLDAUCTIONPURCHASES,
@CHANGEAGENTID = @CHANGEAGENTID,
@CHANGEDATE = @CURRENTDATE,
@ADJUSTMENTDATE = @ADJUSTMENTDATE,
@ADJUSTMENTPOSTDATE = @ADJUSTMENTPOSTDATE,
@ADJUSTMENTREASON = @ADJUSTMENTREASON,
@ADJUSTMENTREASONCODEID = @ADJUSTMENTREASONCODEID,
@ADJUSTMENTPOSTSTATUSCODE = @ADJUSTMENTPOSTSTATUSCODE;
end
/**
* 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;