USP_REVENUE_DELETEPOSTED
Executes the "Posted Revenue: Delete" record operation.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @ID | uniqueidentifier | IN | Input parameter indicating the ID of the record being deleted. |
| @CHANGEAGENTID | uniqueidentifier | IN | Input parameter indicating the ID of the change agent invoking the delete. |
Definition
Copy
CREATE procedure dbo.USP_REVENUE_DELETEPOSTED
(
@ID uniqueidentifier,
@CHANGEAGENTID uniqueidentifier
)
as
set nocount on;
begin try
declare @TRANSACTIONTYPECODE int;
declare @CHANGEDATE datetime;
declare @POSTDATE datetime;
declare @PAYMENTMETHODCODE tinyint;
declare @ADJUSTMENTHISTORYID uniqueidentifier;
declare @ADJUSTMENTHISTORYPROPERTYID uniqueidentifier;
declare @ADJUSTMENTHISTORYSTOCKID uniqueidentifier;
declare @ADJUSTMENTHISTORYWRITEOFFID uniqueidentifier;
declare @REVENUEPAYMENTMETHODID uniqueidentifier;
declare @ERRORMESSAGE nvarchar(255);
declare @ADJUSTMENTHISTORYGIFTINKINDID uniqueidentifier;
if dbo.UFN_REVENUE_ISPOSTED(@ID) = 0
raiserror('Unposted revenue items cannot be deleted with this task.', 13, 1);
select
@TRANSACTIONTYPECODE = TYPECODE,
@CHANGEDATE = getdate()
from dbo.FINANCIALTRANSACTION
where ID = @ID
select
@PAYMENTMETHODCODE = PAYMENTMETHODCODE,
@REVENUEPAYMENTMETHODID = ID
from dbo.REVENUEPAYMENTMETHOD
where REVENUEID = @ID;
-- remove revenueid from committed batches so deleting revenue records will not cause
-- foreign key constraint errors
if exists (select 'x' from batchrevenue where REVENUEID = @ID)
begin
declare @ISINACTIVEBATCH bit;
set @ISINACTIVEBATCH = 0;
select top 1 @ISINACTIVEBATCH = 1
from BATCHREVENUE
inner join BATCH on BATCH.ID = BATCHREVENUE.BATCHID
where BATCHREVENUE.REVENUEID = @ID
and BATCH.STATUSCODE not in (1,2);
if @ISINACTIVEBATCH = 0
begin
update BATCHREVENUE
set REVENUEID = null
where REVENUEID = @ID;
end
end
--Deletion rules for payment
-- 1) Cannot delete a payment if there is a matching gift attached to any of its details
-- 2) Also delete a payment if there is a deleted matching gift attached
if (select count(FINANCIALTRANSACTION.ID)
from dbo.FINANCIALTRANSACTION
inner join dbo.REVENUEMATCHINGGIFT on REVENUEMATCHINGGIFT.MGSOURCEREVENUEID = FINANCIALTRANSACTION.ID
where FINANCIALTRANSACTION.ID = @ID
and FINANCIALTRANSACTION.DELETEDON is null
and exists(select 1 from FINANCIALTRANSACTION where ID =REVENUEMATCHINGGIFT.ID and DELETEDON is null)) > 0
begin
raiserror('There are matching gift claims against this payment. Delete those matching gift claims before deleting this payment.', 13, 1);
return 0;
end
--if there is an unclaimed gift aid refund this needs dealt with before deletion
if exists(select top 1 dbo.FINANCIALTRANSACTION.ID
from dbo.FINANCIALTRANSACTION
inner join dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID=FINANCIALTRANSACTION.ID
inner join dbo.REVENUESPLITGIFTAIDREFUND on REVENUESPLITGIFTAIDREFUND.REVENUESPLITID=FINANCIALTRANSACTIONLINEITEM.ID
where REVENUESPLITGIFTAIDREFUND.REFUNDTAXCLAIMNUMBER =''
and FINANCIALTRANSACTION.ID=@ID)
begin
raiserror('BBERR_REVENUE_ASSOCIATEDOUTSTANDINGGIFTAIDREFUND', 13, 1);
return 0;
end
--Deletion rules for Pledge
if (@TRANSACTIONTYPECODE in (1, 3, 4, 6, 15)) --Pledge, matching gift claim, planned gift, grant award, membership installment plan
begin
-- 1) Cannot delete a pledge if there are payments made against it
if exists (select 1
from dbo.INSTALLMENTSPLITPAYMENT IP
inner join dbo.FINANCIALTRANSACTION PLEDGERD on IP.PLEDGEID = PLEDGERD.ID
inner join dbo.FINANCIALTRANSACTIONLINEITEM PAYRD on IP.PAYMENTID = PAYRD.ID
where PLEDGERD.ID = @ID and PAYRD.DELETEDON is null and PAYRD.TYPECODE != 1
)
begin
if @TRANSACTIONTYPECODE = 1
raiserror('There are payments made against this pledge. Delete those payments before deleting this pledge.', 13, 1);
if @TRANSACTIONTYPECODE = 3
raiserror('There are payments made against this matching gift claim. Delete those payments before deleting this matching gift claim.', 13, 1);
if @TRANSACTIONTYPECODE = 4
raiserror('There are payments made against this planned gift. Delete those payments before deleting this planned gift.', 13, 1);
if @TRANSACTIONTYPECODE = 6
raiserror('There are payments made against this grant award. Delete those payments before deleting this grant award.', 13, 1);
if @TRANSACTIONTYPECODE = 15
raiserror('There are payments made against this membership installment plan. Delete those payments before deleting this plan.', 13, 1);
end
exec dbo.USP_ADJUSTMENTHISTORY_WRITEOFFS_FIXFORDELETE @ID, @CHANGEAGENTID, @CHANGEDATE;
-- Delete unposted write-off distributions.
delete from dbo.WRITEOFFGLDISTRIBUTION where ID in (
select WRITEOFFGLDISTRIBUTION.ID
from dbo.WRITEOFFGLDISTRIBUTION
inner join dbo.GLTRANSACTION on WRITEOFFGLDISTRIBUTION.GLTRANSACTIONID = GLTRANSACTION.ID
where WRITEOFFGLDISTRIBUTION.REVENUEID = @ID and GLTRANSACTION.POSTSTATUSCODE <> 0
);
-- also delete the INSTALLMENTSPLITWRITEOFF wi 328761
delete T1 from
dbo.INSTALLMENTSPLITWRITEOFF T1 join dbo.FINANCIALTRANSACTION T2 on T1.WRITEOFFID = T2.ID
where T2.POSTSTATUSCODE != 2 and T2.PARENTID = @ID;
-- 2) Log reversals for any posted writeoffs this pledge may have
if not exists (select ADJ.ID from dbo.WRITEOFFADJUSTMENT ADJ inner join dbo.WRITEOFF WO on ADJ.WRITEOFFID = WO.ID where WO.REVENUEID = @ID and ADJ.POSTSTATUSCODE > 0)
begin
select top 1 @POSTDATE = WRITEOFFADJUSTMENT.POSTDATE from dbo.WRITEOFFADJUSTMENT
inner join dbo.WRITEOFF on WRITEOFFADJUSTMENT.WRITEOFFID = WRITEOFF.ID
where WRITEOFF.REVENUEID = @ID and WRITEOFFADJUSTMENT.POSTSTATUSCODE = 0 order by WRITEOFFADJUSTMENT.DATEADDED desc
if @POSTDATE is null
select @POSTDATE = POSTDATE from dbo.WRITEOFF where REVENUEID = @ID;
if @POSTDATE is not null
begin
set @ERRORMESSAGE = dbo.UFN_GLDISTRIBUTION_VALIDPOSTDATE(@POSTDATE);
if @ERRORMESSAGE <> ''
raiserror(@ERRORMESSAGE, 13, 1);
end
--Log reversals in the GLTRANSACTION table
exec dbo.USP_GLTRANSACTION_ADDWRITEOFFREVERSALS_BYREVENUEID @ID, @CHANGEAGENTID, @CHANGEDATE, @POSTDATE;
end
else --Update the post date of the existing write-off reversal rows to today's date
begin
update dbo.GLTRANSACTION
set
POSTDATE = dbo.UFN_DATE_GETEARLIESTTIME(@CHANGEDATE),
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CHANGEDATE
where ID in (
select
REVERSAL.ID
from
dbo.WRITEOFFGLDISTRIBUTION
inner join
dbo.GLTRANSACTION on WRITEOFFGLDISTRIBUTION.GLTRANSACTIONID = GLTRANSACTION.ID
inner join
dbo.GLTRANSACTION REVERSAL on GLTRANSACTION.ID = REVERSAL.REVERSEDGLTRANSACTIONID
where
WRITEOFFGLDISTRIBUTION.REVENUEID = @ID
and
REVERSAL.POSTSTATUSCODE > 0);
end
-- 3) Log reversals for any posted unrealized gain/loss this pledge may have
if exists (
select UNREALIZEDGAINLOSSADJUSTMENT.ID
from dbo.UNREALIZEDGAINLOSSADJUSTMENT
where UNREALIZEDGAINLOSSADJUSTMENT.REVENUEID = @ID and UNREALIZEDGAINLOSSADJUSTMENT.POSTSTATUSCODE > 0
)
begin
--Update the post date of the existing unrealized gain/loss reversal rows to today's date
update dbo.GLTRANSACTION
set
POSTDATE = dbo.UFN_DATE_GETEARLIESTTIME(@CHANGEDATE),
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CHANGEDATE
where ID in (
select
REVERSAL.ID
from dbo.UNREALIZEDGAINLOSSGLDISTRIBUTION
inner join dbo.GLTRANSACTION on UNREALIZEDGAINLOSSGLDISTRIBUTION.GLTRANSACTIONID = GLTRANSACTION.ID
inner join dbo.GLTRANSACTION REVERSAL on GLTRANSACTION.ID = REVERSAL.REVERSEDGLTRANSACTIONID
where UNREALIZEDGAINLOSSGLDISTRIBUTION.REVENUEID = @ID
and REVERSAL.POSTSTATUSCODE > 0);
end
else
begin
--Get post date off last posted adjustment or the revenue record.
select top 1 @POSTDATE = UNREALIZEDGAINLOSSADJUSTMENT.POSTDATE
from dbo.UNREALIZEDGAINLOSSADJUSTMENT
where UNREALIZEDGAINLOSSADJUSTMENT.REVENUEID = @ID
and UNREALIZEDGAINLOSSADJUSTMENT.POSTSTATUSCODE = 0
order by UNREALIZEDGAINLOSSADJUSTMENT.DATEADDED desc
if @POSTDATE is null
select @POSTDATE = POSTDATE
from dbo.FINANCIALTRANSACTION
where ID = @ID;
if @POSTDATE is not null
begin
set @ERRORMESSAGE = dbo.UFN_GLDISTRIBUTION_VALIDPOSTDATE(@POSTDATE);
if @ERRORMESSAGE <> ''
begin
raiserror(@ERRORMESSAGE, 13, 1);
return 1;
end
end
--Log reversals in the GLTRANSACTION table
exec dbo.USP_GLTRANSACTION_ADDUNREALIZEDGAINLOSSREVERSALS @ID, @CHANGEAGENTID, @CHANGEDATE, @POSTDATE;
end
--REVISIT
--Pick one of the newly created GLTRANSACTION reversal rows since they will all get posted together
update dbo.ADJUSTMENTHISTORYWRITEOFF
set ADJUSTMENTHISTORYWRITEOFF.GLTRANSACTIONID = (
select top 1 REVERSAL.ID from JOURNALENTRY
inner join dbo.JOURNALENTRY_EXT ON JOURNALENTRY.ID = JOURNALENTRY_EXT.ID and JOURNALENTRY_EXT.TABLENAMECODE = 12
inner join JOURNALENTRY_EXT REVERSAL on JOURNALENTRY_EXT.ID = REVERSAL.REVERSEDGLTRANSACTIONID
inner join dbo.FINANCIALTRANSACTIONLINEITEM ON JOURNALENTRY.FINANCIALTRANSACTIONLINEITEMID = FINANCIALTRANSACTIONLINEITEM.ID
inner join dbo.FINANCIALTRANSACTION ON FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = FINANCIALTRANSACTION.ID
where isnull(JOURNALENTRY_EXT.LOGICALREVENUEID, FINANCIALTRANSACTION.PARENTID) = @ID
order by REVERSAL.DATEADDED desc)
where ADJUSTMENTHISTORYWRITEOFF.DATEADDED = @CHANGEDATE;
end
--Deletion rules for Recurring Gift
-- 1) Cannot delete a Recurring Gift if there are payments made against it
-- 2) Cannot delete a Recurring Gift that is marked as pending (because a gift in a batch somewhere references it).
if @TRANSACTIONTYPECODE = 2
begin
if (select count(ID) from dbo.RECURRINGGIFTACTIVITY where SOURCEREVENUEID = @ID and not PAYMENTREVENUEID is null) > 0
begin
raiserror('There are payments made against this recurring gift. Delete those payments before deleting this recurring gift.', 13, 1);
return 0;
end
if (select ISPENDING from dbo.REVENUESCHEDULE where ID = @ID) = 1
begin
raiserror('There are pending transactions for this recurring gift in one or more revenue batches. Delete those pending transactions before deleting this recurring gift.', 13, 1);
return 0;
end
end
/* if the payment method is sold stock/gift-in-kind/property and at least one other revenue exists for this payment,
create adjustments for the other revenue items so that broker fee and gain/loss information can be recalculated */
if (@PAYMENTMETHODCODE = 4 and exists(select ID from dbo.STOCKSALE where STOCKDETAILID = @REVENUEPAYMENTMETHODID)) or
(@PAYMENTMETHODCODE = 5 and exists(select ID from dbo.PROPERTYDETAIL where ID = @REVENUEPAYMENTMETHODID and ISSOLD = 1)) or
(@PAYMENTMETHODCODE = 6 and exists(select ID from dbo.GIFTINKINDSALE where GIFTINKINDPAYMENTMETHODDETAILID = @REVENUEPAYMENTMETHODID))
begin
declare @ADJUSTMENTDATE datetime;
declare @ADJUSTMENTPOSTDATE datetime;
declare @ADJUSTMENTREASON nvarchar(300);
--use existing unposted adjustment values if present
select top 1
@ADJUSTMENTDATE = coalesce(ADJUSTMENT.DATE, dbo.UFN_DATE_GETEARLIESTTIME(@CHANGEDATE)),
@ADJUSTMENTPOSTDATE = coalesce(ADJUSTMENT.POSTDATE, dbo.UFN_DATE_GETEARLIESTTIME(@CHANGEDATE)),
@ADJUSTMENTREASON = coalesce(ADJUSTMENT.REASON, 'Deleting revenue')
from dbo.FINANCIALTRANSACTION
left outer join dbo.ADJUSTMENT
on FINANCIALTRANSACTION.ID = ADJUSTMENT.REVENUEID and ADJUSTMENT.POSTSTATUSCODE > 0
where FINANCIALTRANSACTION.ID = @ID
end
--Deletion rules for auction item
if (@TRANSACTIONTYPECODE = 7)
begin
-- 1) Cannot delete an auction item if it was purchased
if exists(select 1 from dbo.AUCTIONITEM left join dbo.AUCTIONITEMREVENUEPURCHASE on AUCTIONITEM.ID = AUCTIONITEMREVENUEPURCHASE.AUCTIONITEMID where REVENUEAUCTIONDONATIONID = @ID and AUCTIONITEMREVENUEPURCHASE.REVENUEPURCHASEID is not null)
raiserror('Purchased auction items cannot be deleted.', 13, 1);
exec dbo.USP_ADJUSTMENTHISTORY_WRITEOFFS_FIXFORDELETE @ID, @CHANGEAGENTID, @CHANGEDATE;
-- 2) Log reversals for any posted writeoffs this auction item may have
if not exists (select ADJ.ID from dbo.WRITEOFFADJUSTMENT ADJ inner join dbo.WRITEOFF WO on ADJ.WRITEOFFID = WO.ID where WO.REVENUEID = @ID and ADJ.POSTSTATUSCODE > 0)
begin
select top 1
@POSTDATE = WRITEOFFADJUSTMENT.POSTDATE
from
dbo.WRITEOFFADJUSTMENT
inner join dbo.WRITEOFF on WRITEOFFADJUSTMENT.WRITEOFFID = WRITEOFF.ID
where
WRITEOFF.REVENUEID = @ID
and
WRITEOFFADJUSTMENT.POSTSTATUSCODE = 0
order by WRITEOFFADJUSTMENT.DATEADDED desc
if @POSTDATE is null
select @POSTDATE = POSTDATE
from dbo.WRITEOFF
where REVENUEID = @ID;
if @POSTDATE is not null
begin
set @ERRORMESSAGE = dbo.UFN_GLDISTRIBUTION_VALIDPOSTDATE(@POSTDATE);
if @ERRORMESSAGE <> ''
raiserror(@ERRORMESSAGE, 13, 1);
end
--make sure that the writeoff is actually posted before trying to create reversals for it
if exists(select ID from dbo.WRITEOFF where WRITEOFF.REVENUEID = @ID and WRITEOFF.POSTSTATUSCODE = 0)
exec dbo.USP_GLTRANSACTION_ADDWRITEOFFREVERSALS_BYREVENUEID @ID, @CHANGEAGENTID, @CHANGEDATE, @POSTDATE;
end
else --Update the post date of the existing write-off reversal rows to today's date
begin
update dbo.GLTRANSACTION
set
POSTDATE = dbo.UFN_DATE_GETEARLIESTTIME(@CHANGEDATE),
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CHANGEDATE
where ID in (
select
REVERSAL.ID
from
dbo.WRITEOFFGLDISTRIBUTION
inner join
dbo.GLTRANSACTION on WRITEOFFGLDISTRIBUTION.GLTRANSACTIONID = GLTRANSACTION.ID
inner join
dbo.GLTRANSACTION REVERSAL on GLTRANSACTION.ID = REVERSAL.REVERSEDGLTRANSACTIONID
where
WRITEOFFGLDISTRIBUTION.REVENUEID = @ID
and
REVERSAL.POSTSTATUSCODE > 0
);
end
--REVISIT
--Pick one of the newly created GLTRANSACTION reversal rows since they will all get posted together
update dbo.ADJUSTMENTHISTORYWRITEOFF
set ADJUSTMENTHISTORYWRITEOFF.GLTRANSACTIONID = (
select top 1 REVERSAL.ID from JOURNALENTRY
inner join dbo.JOURNALENTRY_EXT ON JOURNALENTRY.ID = JOURNALENTRY_EXT.ID and JOURNALENTRY_EXT.TABLENAMECODE = 12
inner join JOURNALENTRY_EXT REVERSAL on JOURNALENTRY_EXT.ID = REVERSAL.REVERSEDGLTRANSACTIONID
inner join dbo.FINANCIALTRANSACTIONLINEITEM ON JOURNALENTRY.FINANCIALTRANSACTIONLINEITEMID = FINANCIALTRANSACTIONLINEITEM.ID
inner join dbo.FINANCIALTRANSACTION ON FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = FINANCIALTRANSACTION.ID
where isnull(JOURNALENTRY_EXT.LOGICALREVENUEID, FINANCIALTRANSACTION.PARENTID) = @ID
order by REVERSAL.DATEADDED desc)
where ADJUSTMENTHISTORYWRITEOFF.DATEADDED = @CHANGEDATE;
end
/* update REVENUE */
-- make sure we aren't trying to modify revenue in a locked/closed deposit/bank
exec dbo.USP_EDIT_REVENUE_VALIDATE_DEPOSIT_STATUS @ID;
update dbo.FINANCIALTRANSACTION
set
BASEAMOUNT=0,
TRANSACTIONAMOUNT = 0,
ORGAMOUNT = 0,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CHANGEDATE
where ID = @ID
update dbo.REVENUE_EXT
set
RECEIPTAMOUNT=0,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CHANGEDATE
where ID = @ID
/* update REVENUERECOGNITION */
update REVENUERECOGNITION
set AMOUNT = 0,
CHANGEDBYID = @CHANGEAGENTID
from dbo.REVENUERECOGNITION
inner join dbo.FINANCIALTRANSACTIONLINEITEM
on REVENUERECOGNITION.REVENUESPLITID = FINANCIALTRANSACTIONLINEITEM.ID
where FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = @ID
update dbo.RECOGNITIONCREDIT
set AMOUNT=0,
CHANGEDBYID = @CHANGEAGENTID
from dbo.RECOGNITIONCREDIT
inner join dbo.DONORCHALLENGEENCUMBERED on RECOGNITIONCREDIT.DONORCHALLENGEENCUMBEREDID = DONORCHALLENGEENCUMBERED.ID
inner join dbo.FINANCIALTRANSACTIONLINEITEM
on DONORCHALLENGEENCUMBERED.REVENUESPLITID = FINANCIALTRANSACTIONLINEITEM.ID
where
FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = @ID and
RECOGNITIONCREDIT.RECOGNITIONCREDITTYPECODE = 1;
/*log the deletion for the adjusted revenue report*/
exec dbo.USP_ADJUSTMENTHISTORY_REVENUE_FIXFORDELETE @ID, @CHANGEAGENTID, @CHANGEDATE, @ADJUSTMENTHISTORYID output;
/* LOG REVERSALS */
/*if there is an unposted adjustment, the reversal would already have been logged for that adjustment so don't do it again*/
if (select count(ID) from dbo.ADJUSTMENT where REVENUEID = @ID and POSTSTATUSCODE > 0) = 0
begin
set @POSTDATE = null
select top 1 @POSTDATE = POSTDATE from dbo.ADJUSTMENT where REVENUEID = @ID and POSTSTATUSCODE = 0 order by DATEADDED desc;
if @POSTDATE is null
select @POSTDATE = POSTDATE from dbo.FINANCIALTRANSACTION where ID = @ID;
set @ERRORMESSAGE = dbo.UFN_GLDISTRIBUTION_VALIDPOSTDATE(@POSTDATE);
if @ERRORMESSAGE <> ''
raiserror(@ERRORMESSAGE, 13, 1);
--Log reversals in the GLTRANSACTION table work item 68564
exec dbo.USP_GLTRANSACTION_ADDREVENUEREVERSALS @ID, @CHANGEAGENTID, @CHANGEDATE --, @CHANGEDATE;
end
else --Update the post date of the existing revenue reversal rows to today's date
begin
update dbo.GLTRANSACTION
set
POSTDATE = dbo.UFN_DATE_GETEARLIESTTIME(@CHANGEDATE),
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CHANGEDATE,
POSTSTATUSCODE = 1
where ID in (
select
REVERSAL.ID
from
dbo.REVENUEGLDISTRIBUTION
inner join
dbo.GLTRANSACTION on REVENUEGLDISTRIBUTION.GLTRANSACTIONID = GLTRANSACTION.ID
inner join
dbo.GLTRANSACTION REVERSAL on GLTRANSACTION.ID = REVERSAL.REVERSEDGLTRANSACTIONID
where
REVENUEGLDISTRIBUTION.REVENUEID = @ID
and
REVERSAL.POSTSTATUSCODE > 0);
end
if exists(select 1 from dbo.PLANNEDGIFTPAYOUT where REVENUEID = @ID)
and not exists(select 1 from dbo.PLANNEDGIFTPAYOUTADJUSTMENT where REVENUEID = @ID and POSTSTATUSCODE > 0)
begin
set @POSTDATE = null
select top 1 @POSTDATE = POSTDATE from dbo.PLANNEDGIFTPAYOUTADJUSTMENT where REVENUEID = @ID and POSTSTATUSCODE = 0 order by DATEADDED desc;
if @POSTDATE is null
select @POSTDATE = POSTDATE from dbo.FINANCIALTRANSACTION where ID = @ID;
set @ERRORMESSAGE = dbo.UFN_GLDISTRIBUTION_VALIDPOSTDATE(@POSTDATE);
if @ERRORMESSAGE <> ''
raiserror(@ERRORMESSAGE, 13, 1);
exec dbo.USP_GLTRANSACTION_ADDPAYOUTREVERSALS @ID, @CHANGEAGENTID, @CHANGEDATE, @POSTDATE;
end
else --Update the post date of the existing revenue reversal rows to today's date
begin
update dbo.GLTRANSACTION
set
POSTDATE = dbo.UFN_DATE_GETEARLIESTTIME(@CHANGEDATE),
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CHANGEDATE
where ID in (
select
REVERSAL.ID
from
dbo.PLANNEDGIFTPAYOUTGLDISTRIBUTION
inner join
dbo.GLTRANSACTION on PLANNEDGIFTPAYOUTGLDISTRIBUTION.GLTRANSACTIONID = GLTRANSACTION.ID
inner join
dbo.GLTRANSACTION REVERSAL on GLTRANSACTION.ID = REVERSAL.REVERSEDGLTRANSACTIONID
where
PLANNEDGIFTPAYOUTGLDISTRIBUTION.REVENUEID = @ID
and
REVERSAL.POSTSTATUSCODE > 0);
end
--Update the new ADJUSTMENTHISTORY record with one of the newly created GLTRANSACTION reversal IDs
update dbo.ADJUSTMENTHISTORY
set GLTRANSACTIONID = (
select top 1 REVERSAL.ID from JOURNALENTRY
inner join dbo.JOURNALENTRY_EXT ON JOURNALENTRY.ID = JOURNALENTRY_EXT.ID and JOURNALENTRY_EXT.TABLENAMECODE = 1
inner join JOURNALENTRY_EXT REVERSAL on JOURNALENTRY_EXT.ID = REVERSAL.REVERSEDGLTRANSACTIONID
inner join dbo.FINANCIALTRANSACTIONLINEITEM ON JOURNALENTRY.FINANCIALTRANSACTIONLINEITEMID = FINANCIALTRANSACTIONLINEITEM.ID
inner join dbo.FINANCIALTRANSACTION ON FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = FINANCIALTRANSACTION.ID
where FINANCIALTRANSACTION.DELETEDON IS NULL and FINANCIALTRANSACTION.ID = @ID
order by REVERSAL.DATEADDED desc)
where ID = @ADJUSTMENTHISTORYID;
if (@PAYMENTMETHODCODE = 5) and exists(select ID from dbo.PROPERTYDETAIL where ID = @REVENUEPAYMENTMETHODID and ISSOLD = 1) --Sold property
begin
exec dbo.USP_ADJUSTMENTHISTORY_PROPERTY_FIXFORDELETE @REVENUEPAYMENTMETHODID, 0, @CHANGEAGENTID, @CHANGEDATE, @ADJUSTMENTHISTORYPROPERTYID output;
--If there is no unposted property detail adjustment but the property sale has been posted, create reversals for the property sale.
if
(select count(ID) from dbo.PROPERTYDETAILADJUSTMENT where PROPERTYDETAILID = @REVENUEPAYMENTMETHODID and POSTSTATUSCODE > 0) = 0
and
exists
(
select 1 from dbo.PROPERTYDETAILGLDISTRIBUTION
inner join dbo.GLTRANSACTION on PROPERTYDETAILGLDISTRIBUTION.GLTRANSACTIONID = GLTRANSACTION.ID
where PROPERTYDETAILGLDISTRIBUTION.PROPERTYDETAILID = @REVENUEPAYMENTMETHODID and GLTRANSACTION.POSTSTATUSCODE = 0
)
begin
set @POSTDATE = null
select top 1 @POSTDATE = POSTDATE from dbo.PROPERTYDETAILADJUSTMENT where PROPERTYDETAILID = @REVENUEPAYMENTMETHODID and POSTSTATUSCODE = 0 order by DATEADDED desc;
if @POSTDATE is null
select @POSTDATE = SALEPOSTDATE from dbo.PROPERTYDETAIL where ID = @REVENUEPAYMENTMETHODID;
if @POSTDATE is not null
begin
set @ERRORMESSAGE = dbo.UFN_GLDISTRIBUTION_VALIDPOSTDATE(@POSTDATE);
if @ERRORMESSAGE <> ''
raiserror(@ERRORMESSAGE, 13, 1);
end
--Log reversals in the GLTRANSACTION table
exec dbo.USP_GLTRANSACTION_ADDPROPERTYDETAILREVERSALS @REVENUEPAYMENTMETHODID, @CHANGEAGENTID, @CHANGEDATE, @POSTDATE;
end
else --Update the post date of the existing property detail reversal rows to today's date
begin
update dbo.GLTRANSACTION
set
POSTDATE = dbo.UFN_DATE_GETEARLIESTTIME(@CHANGEDATE),
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CHANGEDATE
where ID in (
select
REVERSAL.ID
from
dbo.PROPERTYDETAILGLDISTRIBUTION
inner join
dbo.GLTRANSACTION on PROPERTYDETAILGLDISTRIBUTION.GLTRANSACTIONID = GLTRANSACTION.ID
inner join
dbo.GLTRANSACTION REVERSAL on GLTRANSACTION.ID = REVERSAL.REVERSEDGLTRANSACTIONID
where
PROPERTYDETAILGLDISTRIBUTION.REVENUEID = @ID
and
REVERSAL.POSTSTATUSCODE > 0);
end
--Update the new ADJUSTMENTHISTORYPROPERTY record with one of the newly created GLTRANSACTION reversal IDs
update dbo.ADJUSTMENTHISTORYPROPERTY
set GLTRANSACTIONID = (
select top 1 REVERSAL.ID from dbo.PROPERTYDETAILGLDISTRIBUTION
inner join dbo.GLTRANSACTION on PROPERTYDETAILGLDISTRIBUTION.GLTRANSACTIONID = GLTRANSACTION.ID
inner join dbo.GLTRANSACTION REVERSAL on GLTRANSACTION.ID = REVERSAL.REVERSEDGLTRANSACTIONID
where PROPERTYDETAILGLDISTRIBUTION.REVENUEID = @ID
order by REVERSAL.DATEADDED desc)
where ID = @ADJUSTMENTHISTORYPROPERTYID;
end
if @PAYMENTMETHODCODE = 4 and exists(select ID from dbo.STOCKSALE where STOCKDETAILID = @REVENUEPAYMENTMETHODID) --Sold stock
begin
exec dbo.USP_ADJUSTMENTHISTORY_STOCK_FIXFORDELETE @REVENUEPAYMENTMETHODID, 0, @CHANGEAGENTID, @CHANGEDATE, @ADJUSTMENTHISTORYSTOCKID output;
-- Delete unposted stock detail distributions.
delete from dbo.STOCKSALEGLDISTRIBUTION where ID in (
select STOCKSALEGLDISTRIBUTION.ID
from dbo.STOCKSALEGLDISTRIBUTION
inner join dbo.GLTRANSACTION on STOCKSALEGLDISTRIBUTION.GLTRANSACTIONID = GLTRANSACTION.ID
where STOCKSALEGLDISTRIBUTION.REVENUEID = @ID and GLTRANSACTION.POSTSTATUSCODE <> 0
);
if (select count(STOCKSALEADJUSTMENT.ID) from dbo.STOCKSALEADJUSTMENT
inner join dbo.STOCKSALE on STOCKSALEADJUSTMENT.STOCKSALEID = STOCKSALE.ID
where STOCKSALE.STOCKDETAILID = @REVENUEPAYMENTMETHODID and POSTSTATUSCODE > 0) = 0
begin
set @POSTDATE = null
select top 1 @POSTDATE = POSTDATE
from dbo.STOCKSALEADJUSTMENT
inner join dbo.STOCKSALE on STOCKSALE.ID = STOCKSALEADJUSTMENT.STOCKSALEID
where STOCKSALE.STOCKDETAILID = @REVENUEPAYMENTMETHODID and POSTSTATUSCODE = 0
order by STOCKSALEADJUSTMENT.DATEADDED desc;
if @POSTDATE is null
select top 1 @POSTDATE = SALEPOSTDATE from dbo.STOCKSALE where STOCKDETAILID = @REVENUEPAYMENTMETHODID order by SALEDATE desc, DATEADDED desc;
if @POSTDATE is not null
begin
set @ERRORMESSAGE = dbo.UFN_GLDISTRIBUTION_VALIDPOSTDATE(@POSTDATE);
if @ERRORMESSAGE <> ''
raiserror(@ERRORMESSAGE, 13, 1);
end
--Log reversals in the GLTRANSACTION table
exec dbo.USP_GLTRANSACTION_ADDSTOCKDETAILREVERSALS @REVENUEPAYMENTMETHODID, @CHANGEAGENTID, @CHANGEDATE, @POSTDATE;
end
else --Update the post date of the existing stock detail reversal rows to today's date
begin
update dbo.GLTRANSACTION
set
POSTDATE = dbo.UFN_DATE_GETEARLIESTTIME(@CHANGEDATE),
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CHANGEDATE
where ID in (
select
REVERSAL.ID
from
dbo.STOCKSALEGLDISTRIBUTION
inner join
dbo.GLTRANSACTION on STOCKSALEGLDISTRIBUTION.GLTRANSACTIONID = GLTRANSACTION.ID
inner join
dbo.GLTRANSACTION REVERSAL on GLTRANSACTION.ID = REVERSAL.REVERSEDGLTRANSACTIONID
where
STOCKSALEGLDISTRIBUTION.REVENUEID = @ID
and
REVERSAL.POSTSTATUSCODE > 0);
end
--Update the new ADJUSTMENTHISTORYSTOCK record with one of the newly created GLTRANSACTION reversal IDs
update dbo.ADJUSTMENTHISTORYSTOCK
set GLTRANSACTIONID = (
select top 1 REVERSAL.ID from dbo.STOCKSALEGLDISTRIBUTION
inner join dbo.GLTRANSACTION on STOCKSALEGLDISTRIBUTION.GLTRANSACTIONID = GLTRANSACTION.ID
inner join dbo.GLTRANSACTION REVERSAL on GLTRANSACTION.ID = REVERSAL.REVERSEDGLTRANSACTIONID
where STOCKSALEGLDISTRIBUTION.REVENUEID = @ID
order by REVERSAL.DATEADDED desc)
where ID = @ADJUSTMENTHISTORYSTOCKID;
end
if @PAYMENTMETHODCODE = 6
while exists(
select GIFTINKINDPAYMENTMETHODDETAIL.ID from dbo.GIFTINKINDPAYMENTMETHODDETAIL
inner join dbo.GIFTINKINDSALE on GIFTINKINDPAYMENTMETHODDETAIL.ID = GIFTINKINDSALE.GIFTINKINDPAYMENTMETHODDETAILID
where GIFTINKINDPAYMENTMETHODDETAIL.ID = @REVENUEPAYMENTMETHODID --Sold gift-in-kind
)
exec dbo.USP_RECORDOPERATION_GIFTINKINDSELLUNDO @REVENUEPAYMENTMETHODID, @CHANGEAGENTID
--If there is no unposted benefit adjustment but the benefit has been posted, create reversals for the benefit.
if (select count(ID) from dbo.BENEFITADJUSTMENT where REVENUEID = @ID and POSTSTATUSCODE > 0) = 0
and
exists
(
select 1 from dbo.BENEFITGLDISTRIBUTION
inner join dbo.GLTRANSACTION on BENEFITGLDISTRIBUTION.GLTRANSACTIONID = GLTRANSACTION.ID
where BENEFITGLDISTRIBUTION.REVENUEID = @ID and GLTRANSACTION.POSTSTATUSCODE = 0
)
begin
set @POSTDATE = null
select top 1 @POSTDATE = POSTDATE from dbo.BENEFITADJUSTMENT where REVENUEID = @ID and POSTSTATUSCODE = 0 order by DATEADDED desc;
if @POSTDATE is null
select @POSTDATE = POSTDATE from dbo.FINANCIALTRANSACTION where ID = @ID;
set @ERRORMESSAGE = dbo.UFN_GLDISTRIBUTION_VALIDPOSTDATE(@POSTDATE);
if @ERRORMESSAGE <> ''
raiserror(@ERRORMESSAGE, 13, 1);
exec dbo.USP_GLTRANSACTION_ADDBENEFITREVERSALS @ID, @CHANGEAGENTID, @CHANGEDATE, @POSTDATE;
exec dbo.USP_GLTRANSACTION_ADDBENEFITLIABILITYREVERSALS @ID, @CHANGEAGENTID, @CHANGEDATE, @POSTDATE;
exec dbo.USP_GLTRANSACTION_ADDBENEFITBACKOUTREVERSALS @ID, @CHANGEAGENTID, @CHANGEDATE, @POSTDATE;
end
else --Update the post date of the existing revenue reversal rows to today's date
begin
update dbo.GLTRANSACTION
set
POSTDATE = dbo.UFN_DATE_GETEARLIESTTIME(@CHANGEDATE),
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CHANGEDATE
where ID in (
select
REVERSAL.ID
from
dbo.BENEFITGLDISTRIBUTION
inner join
dbo.GLTRANSACTION on BENEFITGLDISTRIBUTION.GLTRANSACTIONID = GLTRANSACTION.ID
inner join
dbo.GLTRANSACTION REVERSAL on GLTRANSACTION.ID = REVERSAL.REVERSEDGLTRANSACTIONID
where
BENEFITGLDISTRIBUTION.REVENUEID = @ID
and
REVERSAL.POSTSTATUSCODE > 0);
end
--Update auction purchase distributions
exec dbo.USP_AUCTIONPURCHASEDELETE_UPDATEGLDISTRIBUTION @ID, @CHANGEAGENTID, @CHANGEDATE
if (select count(ID) from dbo.AUCTIONPURCHASEADJUSTMENT where REVENUEID = @ID and POSTSTATUSCODE > 0) <> 0
begin --Update the post date of the existing revenue reversal rows to today's date
update dbo.GLTRANSACTION
set
POSTDATE = dbo.UFN_DATE_GETEARLIESTTIME(@CHANGEDATE),
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CHANGEDATE
where ID in (
select
REVERSAL.ID
from
dbo.AUCTIONPURCHASEGLDISTRIBUTION
inner join
dbo.GLTRANSACTION on AUCTIONPURCHASEGLDISTRIBUTION.GLTRANSACTIONID = GLTRANSACTION.ID
inner join
dbo.GLTRANSACTION REVERSAL on GLTRANSACTION.ID = REVERSAL.REVERSEDGLTRANSACTIONID
where
AUCTIONPURCHASEGLDISTRIBUTION.REVENUEPURCHASEID = @ID
and
REVERSAL.POSTSTATUSCODE > 0);
end
/* Cache CONTEXT INFO */
declare @CONTEXTCACHE varbinary(128);
set @CONTEXTCACHE = CONTEXT_INFO();
if not @CHANGEAGENTID is null
set CONTEXT_INFO @CHANGEAGENTID;
--Log reversals for any posted gift aid
if exists (select 1 from dbo.GIFTAIDGLDISTRIBUTION where REVENUEID = @ID)
begin
-- Delete unposted gift aid distributions.
delete from dbo.GIFTAIDGLDISTRIBUTION where ID in (
select GIFTAIDGLDISTRIBUTION.ID
from dbo.GIFTAIDGLDISTRIBUTION
inner join dbo.GLTRANSACTION on GIFTAIDGLDISTRIBUTION.GLTRANSACTIONID = GLTRANSACTION.ID
where GIFTAIDGLDISTRIBUTION.REVENUEID = @ID and GLTRANSACTION.POSTSTATUSCODE <> 0
);
declare @REVENUESPLITGIFTAIDID uniqueidentifier;
declare GIFTAID_CURSOR cursor local fast_forward for
select FINANCIALTRANSACTIONLINEITEM.ID from dbo.FINANCIALTRANSACTIONLINEITEM
inner join dbo.REVENUESPLITGIFTAID on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLITGIFTAID.ID
where FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = @ID
and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null;
open GIFTAID_CURSOR;
fetch next from GIFTAID_CURSOR into @REVENUESPLITGIFTAIDID;
while (@@FETCH_STATUS = 0)
begin
if dbo.UFN_REVENUESPLITGIFTAID_HASPENDINGADJUSTMENT(@REVENUESPLITGIFTAIDID) = 0
begin
set @ERRORMESSAGE = dbo.UFN_GLDISTRIBUTION_VALIDPOSTDATE(@CHANGEDATE);
if @ERRORMESSAGE <> ''
raiserror(@ERRORMESSAGE, 13, 1);
--Log reversals in the GLTRANSACTION table
exec dbo.USP_GLTRANSACTION_ADDGIFTAIDREVERSALS @REVENUESPLITGIFTAIDID, @CHANGEAGENTID, @CHANGEDATE, @CHANGEDATE;
end
else --Update the post date of the existing gift aid reversal rows to today's date
begin
update dbo.GLTRANSACTION
set
POSTDATE = dbo.UFN_DATE_GETEARLIESTTIME(@CHANGEDATE),
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CHANGEDATE
where ID in (
select
REVERSAL.ID
from
dbo.GIFTAIDGLDISTRIBUTION
inner join
dbo.GLTRANSACTION on GIFTAIDGLDISTRIBUTION.GLTRANSACTIONID = GLTRANSACTION.ID
inner join
dbo.GLTRANSACTION REVERSAL on GLTRANSACTION.ID = REVERSAL.REVERSEDGLTRANSACTIONID
where
GIFTAIDGLDISTRIBUTION.REVENUESPLITGIFTAIDID = @REVENUESPLITGIFTAIDID
and
REVERSAL.POSTSTATUSCODE > 0);
end
fetch next from GIFTAID_CURSOR into @REVENUESPLITGIFTAIDID;
end
close GIFTAID_CURSOR;
deallocate GIFTAID_CURSOR;
end
-- Create gift fee reversals
if (select count(ID) from dbo.GIFTFEEADJUSTMENT where REVENUEID = @ID and POSTSTATUSCODE > 0) = 0
and
exists
(
select 1 from dbo.GIFTFEEGLDISTRIBUTION
inner join dbo.GLTRANSACTION on GIFTFEEGLDISTRIBUTION.GLTRANSACTIONID = GLTRANSACTION.ID
where GIFTFEEGLDISTRIBUTION.REVENUEID = @ID and GLTRANSACTION.POSTSTATUSCODE = 0
)
begin
set @POSTDATE = null
select top 1 @POSTDATE = POSTDATE from dbo.GIFTFEEADJUSTMENT
where REVENUEID = @ID and POSTSTATUSCODE = 0 order by DATEADDED desc;
if @POSTDATE is null
select @POSTDATE = POSTDATE from dbo.FINANCIALTRANSACTION where ID = @ID;
set @ERRORMESSAGE = dbo.UFN_GLDISTRIBUTION_VALIDPOSTDATE(@POSTDATE);
if @ERRORMESSAGE <> ''
raiserror(@ERRORMESSAGE, 13, 1);
--Log reversals in the GLTRANSACTION table
exec dbo.USP_GLTRANSACTION_ADDGIFTFEEREVERSALS @ID, @CHANGEAGENTID, @CHANGEDATE;
end
else --Update the post date of the existing revenue reversal rows to today's date
begin
update dbo.GLTRANSACTION
set
POSTDATE = dbo.UFN_DATE_GETEARLIESTTIME(@CHANGEDATE),
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CHANGEDATE,
POSTSTATUSCODE = 1
where ID in (
select
REVERSAL.ID
from dbo.GIFTFEEGLDISTRIBUTION
inner join dbo.GLTRANSACTION on GIFTFEEGLDISTRIBUTION.GLTRANSACTIONID = GLTRANSACTION.ID
inner join dbo.GLTRANSACTION REVERSAL on GLTRANSACTION.ID = REVERSAL.REVERSEDGLTRANSACTIONID
where
GIFTFEEGLDISTRIBUTION.REVENUEID = @ID and
REVERSAL.POSTSTATUSCODE > 0);
end
--Delete unposted revenue distributions.
delete from dbo.REVENUEGLDISTRIBUTION where ID in (
select REVENUEGLDISTRIBUTION.ID
from dbo.REVENUEGLDISTRIBUTION
inner join dbo.GLTRANSACTION on REVENUEGLDISTRIBUTION.GLTRANSACTIONID = GLTRANSACTION.ID
where REVENUEGLDISTRIBUTION.REVENUEID = @ID and GLTRANSACTION.POSTSTATUSCODE <> 0
);
delete from dbo.PLANNEDGIFTPAYOUTGLDISTRIBUTION where ID in (
select PLANNEDGIFTPAYOUTGLDISTRIBUTION.ID
from dbo.PLANNEDGIFTPAYOUTGLDISTRIBUTION
inner join dbo.GLTRANSACTION on PLANNEDGIFTPAYOUTGLDISTRIBUTION.GLTRANSACTIONID = GLTRANSACTION.ID
where PLANNEDGIFTPAYOUTGLDISTRIBUTION.REVENUEID = @ID and GLTRANSACTION.POSTSTATUSCODE <> 0
);
--Delete unposted gift fee distributions
delete from dbo.GIFTFEEGLDISTRIBUTION where ID in (
select
GIFTFEEGLDISTRIBUTION.ID
from dbo.GIFTFEEGLDISTRIBUTION
inner join dbo.GLTRANSACTION on GIFTFEEGLDISTRIBUTION.GLTRANSACTIONID = GLTRANSACTION.ID
where
GIFTFEEGLDISTRIBUTION.REVENUEID = @ID and
GLTRANSACTION.POSTSTATUSCODE <> 0
);
declare @RGINSTALLMENTSTOFIX table (ID uniqueidentifier);
if @TRANSACTIONTYPECODE = 0
begin
-- Delete unposted property detail distributions.
delete from dbo.PROPERTYDETAILGLDISTRIBUTION where ID in (
select PROPERTYDETAILGLDISTRIBUTION.ID
from dbo.PROPERTYDETAILGLDISTRIBUTION
inner join dbo.GLTRANSACTION on PROPERTYDETAILGLDISTRIBUTION.GLTRANSACTIONID = GLTRANSACTION.ID
where PROPERTYDETAILGLDISTRIBUTION.REVENUEID = @ID and GLTRANSACTION.POSTSTATUSCODE <> 0
);
-- Cache any recurring gifts to which this payment was applied; need to make updates to these.
insert into @RGINSTALLMENTSTOFIX
select distinct RECURRINGGIFTINSTALLMENT.REVENUEID
from dbo.RECURRINGGIFTINSTALLMENTPAYMENT
inner join dbo.RECURRINGGIFTINSTALLMENT on RECURRINGGIFTINSTALLMENT.ID = RECURRINGGIFTINSTALLMENTPAYMENT.RECURRINGGIFTINSTALLMENTID
where RECURRINGGIFTINSTALLMENTPAYMENT.PAYMENTID = @ID;
exec dbo.USP_RECURRINGGIFT_UNDOINSTALLMENTCHANGESONDELETEPAYMENT
@PAYMENTID = @ID,
@CHANGEAGENTID = @CHANGEAGENTID,
@CURRENTDATE = @CHANGEDATE
--Remove the link to any event registration payments
delete dbo.EVENTREGISTRANTPAYMENT
where EVENTREGISTRANTPAYMENT.PAYMENTID in (select FINANCIALTRANSACTIONLINEITEM.ID from dbo.FINANCIALTRANSACTIONLINEITEM where FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = @ID);
end
if @TRANSACTIONTYPECODE in (1,4,6,15) --Pledge, planned gift, grant award, membership installment plan
begin
--Delete unposted unrealized gain/loss distributions.
delete from dbo.UNREALIZEDGAINLOSSGLDISTRIBUTION where ID in (
select UNREALIZEDGAINLOSSGLDISTRIBUTION.ID
from dbo.UNREALIZEDGAINLOSSGLDISTRIBUTION
inner join dbo.GLTRANSACTION on UNREALIZEDGAINLOSSGLDISTRIBUTION.GLTRANSACTIONID = GLTRANSACTION.ID
where UNREALIZEDGAINLOSSGLDISTRIBUTION.REVENUEID = @ID and GLTRANSACTION.POSTSTATUSCODE <> 0
);
end
if @TRANSACTIONTYPECODE = 7
begin
--Delete the auction item information in REVENUEAUCTIONDONATION
delete dbo.REVENUEAUCTIONDONATION
where ID = @ID;
end
/* DELETE RECURRING GIFT ACTIVITY */
delete RECURRINGGIFTACTIVITY
from dbo.RECURRINGGIFTACTIVITY
inner join dbo.FINANCIALTRANSACTIONLINEITEM
on FINANCIALTRANSACTIONLINEITEM.ID = RECURRINGGIFTACTIVITY.PAYMENTREVENUEID
where FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = @ID
and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null
/* DELETE LINK TO PLANNED GIFT */
delete from dbo.PLANNEDGIFTREVENUE
where REVENUEID = @ID;
delete from dbo.PLANNEDGIFTPAYOUT
where REVENUEID = @ID;
delete from dbo.PLANNEDGIFTADDITIONREVENUE
where REVENUEID = @ID;
-- Delete link to Opportunity. Even though the FK is set to cascade delete, this is still
-- necessary since posted revenue will be soft-deleted by having its DELETEDON field set.
delete from dbo.REVENUEOPPORTUNITY
where
ID in
(
select
ID
from dbo.FINANCIALTRANSACTIONLINEITEM
where
FINANCIALTRANSACTIONID = @ID
)
-- delete the linked sponsorship payment. Same reason as for revenueopportunity delete
delete from dbo.SPONSORSHIPPAYMENT
where
ID in
(
select
ID
from dbo.FINANCIALTRANSACTIONLINEITEM
where
FINANCIALTRANSACTIONID = @ID
)
--Remove all encumbered funds that are not currently matched
delete DCE
from dbo.DONORCHALLENGEENCUMBERED DCE
inner join dbo.FINANCIALTRANSACTIONLINEITEM
on DCE.REVENUESPLITID = FINANCIALTRANSACTIONLINEITEM.ID
where
FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = @ID and
DCE.STATUSTYPECODE <> 1;
-- Delete link to revenue funding request
delete from dbo.REVENUEFUNDINGREQUEST
where ID = @ID;
/* Restore CONTEXT INFO */
if not @CONTEXTCACHE is null
set CONTEXT_INFO @CONTEXTCACHE;
exec dbo.USP_REVENUE_CREATEREFUNDSONDELETE @ID, @CHANGEAGENTID
/* delete posted writeoffs */
if exists (select 1 from dbo.FINANCIALTRANSACTION left join dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTION.ID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID
where ((FINANCIALTRANSACTION.POSTSTATUSCODE = 2 and FINANCIALTRANSACTION.DELETEDON is null)
or (FINANCIALTRANSACTIONLINEITEM.POSTSTATUSCODE = 2 and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null))
and FINANCIALTRANSACTION.PARENTID = @ID)
begin
while exists (select 1 from dbo.FINANCIALTRANSACTION where TYPECODE = 20 and POSTSTATUSCODE = 2 and DELETEDON is null and PARENTID = @ID)
begin
declare @WRITEOFFID uniqueidentifier = (select top 1 ID from dbo.FINANCIALTRANSACTION where TYPECODE = 20 and POSTSTATUSCODE = 2 and DELETEDON is null and PARENTID = @ID)
exec dbo.USP_WRITEOFF_DELETEPOSTED @WRITEOFFID, @CHANGEAGENTID
end
end
-- Delete any CRM refund records
declare @REFUNDS table (ID uniqueidentifier);
insert into @REFUNDS select CREDITID from dbo.CREDITPAYMENT where REVENUEID = @ID;
if exists(select 1 from @REFUNDS)
begin
delete from dbo.CREDITPAYMENT where CREDITID in (select id from @REFUNDS);
delete from dbo.FINANCIALTRANSACTION where id in (select id from @REFUNDS);
end
if not @CHANGEAGENTID is null
set CONTEXT_INFO @CHANGEAGENTID;
/* delete revenue */
-- replacing USP_REVENUE_DELETEBYID_WITHCHANGEAGENTID with instead of delete trigger logic on revenue view (excluding statements for non-posted revenue)
-- new financial transaction types are not picked up with delete against review view
--deleting split payments (there is a check for split payments for several revenue types under "Deletion rules for Pledge" which will cause the record op to fail if they exist. Still, I'm keeping this delete in place as it is the existing behavior (for other revenue types?)).
delete INSTALLMENTSPLITPAYMENT
from dbo.INSTALLMENTSPLITPAYMENT
inner join dbo.FINANCIALTRANSACTIONLINEITEM on INSTALLMENTSPLITPAYMENT.PAYMENTID = FINANCIALTRANSACTIONLINEITEM.ID
where FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = @ID
--Stock, Gift in kind
merge dbo.FINANCIALTRANSACTION as target
using (
select FT.ID, FT.POSTSTATUSCODE
from dbo.FINANCIALTRANSACTION FT
where FT.PARENTID = @ID and FT.TYPECODE in (21,27)
) as source
on (target.ID = source.ID)
when matched and source.POSTSTATUSCODE = 2
then
update set
target.DELETEDON = @CHANGEDATE,
target.CHANGEDBYID = @CHANGEAGENTID,
target.DATECHANGED = @CHANGEDATE
when matched
then delete;
--Sold stock
merge dbo.FINANCIALTRANSACTION as target
using (
select top 1 FT.ID,
FT.POSTSTATUSCODE,
FTLI.POSTSTATUSCODE as HASADJUSTMENT
from dbo.FINANCIALTRANSACTION FT
left outer join FINANCIALTRANSACTIONLINEITEM FTLI on FTLI.FINANCIALTRANSACTIONID = FT.ID and FTLI.POSTSTATUSCODE = 2
where FT.PARENTID = @ID and FT.TYPECODE in (22)
) as source
on (target.ID = source.ID)
when matched and (source.POSTSTATUSCODE = 2 or source.HASADJUSTMENT = 2)
then
update set
target.DELETEDON = @CHANGEDATE,
target.CHANGEDBYID = @CHANGEAGENTID,
target.DATECHANGED = @CHANGEDATE
when matched
then delete;
--Auction donation Write-off
merge dbo.FINANCIALTRANSACTION as target
using (
select FT.ID, FT.POSTSTATUSCODE from dbo.FINANCIALTRANSACTION FT
where FT.PARENTID = @ID and FT.TYPECODE = 20 and @TRANSACTIONTYPECODE = 7
) as source
on (target.ID = source.ID)
when matched and source.POSTSTATUSCODE = 2
then
update set
target.DELETEDON = @CHANGEDATE,
target.CHANGEDBYID = @CHANGEAGENTID,
target.DATECHANGED = @CHANGEDATE
when matched
then delete;
--Mark child splits deleted
update dbo.FINANCIALTRANSACTIONLINEITEM
set
DELETEDON = @CHANGEDATE,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CHANGEDATE
from dbo.FINANCIALTRANSACTION FT
inner join dbo.FINANCIALTRANSACTIONLINEITEM on
FT.id = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID
where FT.PARENTID = @ID
--Mark transaction as deleted
merge dbo.FINANCIALTRANSACTION as target
using (
select
FT2.ID,
FT2.POSTSTATUSCODE,
case
when exists (
select FT3.POSTSTATUSCODE
from dbo.FINANCIALTRANSACTION as FT3
where FT3.PARENTID = FT2.ID and FT3.POSTSTATUSCODE = 2 and FT3.TYPECODE != 26
)
then 2
else 0
end as HASPOSTEDCHILD
from dbo.FINANCIALTRANSACTION as FT2
where FT2.ID = @ID
) as source
on (target.ID = source.ID)
when matched and (source.POSTSTATUSCODE = 2 or source.HASPOSTEDCHILD = 2)
then
update set
target.DELETEDON = @CHANGEDATE,
target.CHANGEDBYID = @CHANGEAGENTID,
target.DATECHANGED = @CHANGEDATE
when matched
then delete;
update dbo.FINANCIALTRANSACTIONLINEITEM
set
DELETEDON = @CHANGEDATE,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CHANGEDATE
where FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = @ID
delete from dbo.BANKACCOUNTDEPOSITPAYMENT where ID = @ID;
if not @contextCache is null
set CONTEXT_INFO @contextCache;
-- Update RG installments
declare @RGID uniqueidentifier;
declare RGCURSOR cursor local fast_forward for
select ID
from @RGINSTALLMENTSTOFIX;
open RGCURSOR;
fetch next from RGCURSOR into @RGID;
while @@FETCH_STATUS = 0
begin
exec dbo.USP_RECURRINGGIFT_DELETEFUTUREINSTALLMENTS @RGID, @CHANGEAGENTID;
exec dbo.USP_RECURRINGGIFT_UPDATEREVENUESCHEDULE
@REVENUEID = @RGID,
@STATUSCHANGETYPECODE = 2,
@CHANGEAGENTID = @CHANGEAGENTID,
@CURRENTDATETIME = @CHANGEDATE;
fetch next from RGCURSOR into @RGID;
end
deallocate RGCURSOR;
end try
begin catch
exec dbo.USP_RAISE_ERROR;
return 1;
end catch
return 0;