USP_REVENUE_DELETE

          CREATE procedure dbo.USP_REVENUE_DELETE
          (
            @ID uniqueidentifier,
            @CHANGEAGENTID uniqueidentifier 
          )
          as 
          set nocount on;

          declare @TRANSACTIONTYPECODE int;
          declare @DETAILID uniqueidentifier; 
          declare @CHANGEDATE datetime = getdate(); 
          declare @REVENUEPAYMENTMETHODID uniqueidentifier;

          --if (select REVENUEPOSTED.ID from dbo.REVENUE left join dbo.REVENUEPOSTED on REVENUEPOSTED.ID = REVENUE.ID where REVENUE.ID = @ID) is not null

          if (select POSTSTATUSCODE from dbo.FINANCIALTRANSACTION where ID = @ID and DELETEDON is null) = 2
          begin
            raiserror('Posted revenue items cannot be deleted.', 13, 1);
            return 0;
          end

          -- Recalculate FAF raised total if the credit is tied to

          declare @CONSTITUENTID uniqueidentifier; 
          declare @EVENTID uniqueidentifier; 
          select REVENUERECOGNITION.CONSTITUENTID, EVENT.ID
          from dbo.FINANCIALTRANSACTION
            inner join dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = FINANCIALTRANSACTION.ID
            inner join dbo.REVENUERECOGNITION on REVENUERECOGNITION.REVENUESPLITID = FINANCIALTRANSACTIONLINEITEM.ID
            inner join dbo.REVENUE_EXT on REVENUE_EXT.ID=FINANCIALTRANSACTION.ID
            inner join dbo.EVENT on REVENUE_EXT.APPEALID = EVENT.APPEALID
            inner join dbo.EVENTEXTENSION on EVENT.ID   = EVENTEXTENSION.EVENTID
          where FINANCIALTRANSACTION.ID = @ID
            and FINANCIALTRANSACTION.DELETEDON is null;

          select 
            @TRANSACTIONTYPECODE = FINANCIALTRANSACTION.TYPECODE,
            @REVENUEPAYMENTMETHODID = REVENUEPAYMENTMETHOD.ID
          from dbo.FINANCIALTRANSACTION
            inner join dbo.REVENUE_EXT on FINANCIALTRANSACTION.ID = REVENUE_EXT.ID
            inner join dbo.REVENUEPAYMENTMETHOD on FINANCIALTRANSACTION.ID = REVENUEPAYMENTMETHOD.REVENUEID
          where FINANCIALTRANSACTION.ID = @ID
            and FINANCIALTRANSACTION.DELETEDON is null

          -- 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([ORG].ID) 
            from dbo.FINANCIALTRANSACTION as [ORG]
              inner join dbo.REVENUEMATCHINGGIFT as [RMG] on [RMG].MGSOURCEREVENUEID = [ORG].ID                            
            where [ORG].ID = @ID
              and exists(select 1 from FINANCIALTRANSACTION where ID =[RMG].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 exists (
            select 1
            from dbo.FINANCIALTRANSACTION
              inner join dbo.FINANCIALTRANSACTIONLINEITEM 
               on FINANCIALTRANSACTION.ID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID
            where 
         FINANCIALTRANSACTION.TYPECODE = 23 and --Refund

              exists (
                select 1
                from dbo.FINANCIALTRANSACTIONLINEITEM [SOURCEITEMS]
                  where 
                    [SOURCEITEMS].FINANCIALTRANSACTIONID = @ID and
                    [SOURCEITEMS].ID = FINANCIALTRANSACTIONLINEITEM.SOURCELINEITEMID
                )
            )
          begin
            raiserror('You cannot delete this payment because it was refunded.', 13, 1);
            return 0;
          end

          --Deletion rules for Pledge

          -- 1) Cannot delete a pledge if there are payments made against it

          if @TRANSACTIONTYPECODE = 1
          begin                        
            if (select count(ID) from dbo.INSTALLMENTSPLITPAYMENT ISP where PLEDGEID = @ID) > 0 
            begin
              raiserror('There are payments made against this pledge. Delete those payments before deleting this pledge.', 13, 1);
              return 0;
            end
          end

          if @TRANSACTIONTYPECODE = 15
          begin                        
            if (select count(ID) from dbo.INSTALLMENTSPLITPAYMENT ISP where PLEDGEID = @ID) > 0 
            begin
              raiserror('There are payments made against this installment plan. Delete those payments before deleting this installment plan.', 13, 1);
              return 0;
            end
          end

          --Deletion rules for MG Pledge

          -- 1) Cannot delete a MG pledge if there are payments made against it

          if @TRANSACTIONTYPECODE = 3
          begin
            if (select count(ID) from dbo.INSTALLMENTSPLITPAYMENT where PLEDGEID = @ID) > 0 
            begin
              raiserror('There are payments made against this matching gift claim. Delete those payments before deleting this matching gift claim.', 13, 1);
              return 0;
            end
          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

          --Deletion rules for Planned gift

          -- 1) Cannot delete a planned gift if there are payments made against it

          if @TRANSACTIONTYPECODE = 4
          begin                        
            if (select count(ID) from dbo.INSTALLMENTSPLITPAYMENT ISP where PLEDGEID = @ID) > 0 
            begin
              raiserror('There are payments made against this planned gift. Delete those payments before deleting this planned gift.', 13, 1);
              return 0;
            end
          end

          --Deletion rules for grant award

          -- 1) Cannot delete a grant award if there are payments made against it

          if @TRANSACTIONTYPECODE = 6
          begin                        
            if (select count(ID) from dbo.INSTALLMENTSPLITPAYMENT ISP where PLEDGEID = @ID) > 0 
            begin
              raiserror('There are payments made against this grant award. Delete those payments before deleting this grant award.', 13, 1);
              return 0;
            end
          end

          --Deletion rules for auction item

          -- 1) Cannot delete an auction item if there are payments made against it

          if @TRANSACTIONTYPECODE = 7
          begin                        
            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)
              begin
                raiserror('Purchased auction items cannot be deleted.', 13, 1);
              return 0;
            end
          end

          --Deletion rules for Donor Challenge

          -- 1) Cannot delete a Donor Challenge if there are payments made against it

          if @TRANSACTIONTYPECODE = 8
          begin
            if (select count(ID) from dbo.INSTALLMENTSPLITPAYMENT where PLEDGEID = @ID) > 0 
            begin
              raiserror('There are payments made against this donor challenge claim. Delete those payments before deleting this donor challenge.', 13, 1);
              return 0;
            end
          end

          --Deletion rules for Pending Gift

          -- 1) Cannot delete a Pending Gift if there are payments made against it

          if @TRANSACTIONTYPECODE = 9
            begin
              if (select count(ID) from dbo.INSTALLMENTSPLITPAYMENT where PLEDGEID = @ID) > 0 
              begin
                raiserror('There are payments made against this pending gift. Delete those payments before deleting this pending gift.', 13, 1);
                return 0;
              end
            end

          --check deletion rules, if any

          if exists(select top 1 BANKACCOUNTDEPOSIT.ID
            from dbo.FINANCIALTRANSACTION
              inner join dbo.REVENUE_EXT on FINANCIALTRANSACTION.ID = REVENUE_EXT.ID
              inner join BANKACCOUNTDEPOSITPAYMENT on FINANCIALTRANSACTION.ID = BANKACCOUNTDEPOSITPAYMENT.ID
              inner join BANKACCOUNTDEPOSIT on BANKACCOUNTDEPOSIT.ID = BANKACCOUNTDEPOSITPAYMENT.DEPOSITID
              where FINANCIALTRANSACTION.ID = @ID AND BANKACCOUNTDEPOSIT.STATUSCODE = 0
                and FINANCIALTRANSACTION.DELETEDON is null)
          begin
            --JDH REVISIT 04.28.09 - this doesn't look complete (what about closed bank)

            raiserror('Payments cannot be removed from locked deposits.', 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

          --If exists an open deposit containing the payment, payment must be unlinked from the deposit

          if exists(select top 1 BANKACCOUNTDEPOSIT.ID
            from dbo.FINANCIALTRANSACTION
              inner join dbo.REVENUE_EXT on FINANCIALTRANSACTION.ID = REVENUE_EXT.ID
              inner join BANKACCOUNTDEPOSITPAYMENT on FINANCIALTRANSACTION.ID = BANKACCOUNTDEPOSITPAYMENT.ID
              inner join BANKACCOUNTDEPOSIT on BANKACCOUNTDEPOSIT.ID = BANKACCOUNTDEPOSITPAYMENT.DEPOSITID
            where FINANCIALTRANSACTION.ID = @ID AND BANKACCOUNTDEPOSIT.STATUSCODE = 1
              and FINANCIALTRANSACTION.DELETEDON is null)
           begin
             exec USP_BANKACCOUNTDEPOSITPAYMENT_REMOVE @ID=@ID,@CHANGEAGENTID=@CHANGEAGENTID
           end

           --If a constituent's origin is tied to this revenue remove the default revenue infosource

           if exists (select ID from dbo.CONSTITUENTORIGINATION where REVENUEID = @ID)
           begin
             declare @DEFAULTREVENUEINFOSOURCECODEID uniqueidentifier;
             set @DEFAULTREVENUEINFOSOURCECODEID = null;

             select top(1) @DEFAULTREVENUEINFOSOURCECODEID = REVENUESOURCECODEID from dbo.ORIGINATIONSETTING

             if not @DEFAULTREVENUEINFOSOURCECODEID is null
               update dbo.CONSTITUENTORIGINATION set
                 INFOSOURCECODEID = null,
                 CHANGEDBYID = @CHANGEAGENTID,
                 DATECHANGED = @CHANGEDATE
               where REVENUEID = @ID and INFOSOURCECODEID = @DEFAULTREVENUEINFOSOURCECODEID
           end

          --Cache CONTEXT INFO

          declare @contextCache varbinary(128);
          set @contextCache = CONTEXT_INFO();

          if not @CHANGEAGENTID is null
            set CONTEXT_INFO @CHANGEAGENTID;

          --If this is a planned gift, this will remove the link to the planned gift

          if exists (select 1 from dbo.PLANNEDGIFTREVENUE where REVENUEID = @ID)
            delete from dbo.PLANNEDGIFTREVENUE where REVENUEID = @ID;
          if exists (select 1 from dbo.PLANNEDGIFTADDITIONREVENUE where REVENUEID = @ID)                        
            delete from dbo.PLANNEDGIFTADDITIONREVENUE where REVENUEID = @ID;

          --Delete any named recognitions for this revenue

          if exists (select 1 from dbo.NAMINGOPPORTUNITYRECOGNITION inner join dbo.NAMINGOPPORTUNITYRECOGNITIONREVENUESPLIT on NAMINGOPPORTUNITYRECOGNITIONREVENUESPLIT.NAMINGOPPORTUNITYRECOGNITIONID = NAMINGOPPORTUNITYRECOGNITION.ID 
             inner join dbo.FINANCIALTRANSACTIONLINEITEM on NAMINGOPPORTUNITYRECOGNITIONREVENUESPLIT.REVENUESPLITID = FINANCIALTRANSACTIONLINEITEM.ID
             where FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = @ID)
            delete NAMINGOPPORTUNITYRECOGNITION
            from dbo.NAMINGOPPORTUNITYRECOGNITION inner join dbo.NAMINGOPPORTUNITYRECOGNITIONREVENUESPLIT on NAMINGOPPORTUNITYRECOGNITIONREVENUESPLIT.NAMINGOPPORTUNITYRECOGNITIONID = NAMINGOPPORTUNITYRECOGNITION.ID 
              inner join dbo.FINANCIALTRANSACTIONLINEITEM on NAMINGOPPORTUNITYRECOGNITIONREVENUESPLIT.REVENUESPLITID = FINANCIALTRANSACTIONLINEITEM.ID
            where FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = @ID

          declare @RGINSTALLMENTSTOFIX table (ID uniqueidentifier);

          if @TRANSACTIONTYPECODE = 0
          begin
            -- Update auction purchase distributions.

            exec dbo.USP_AUCTIONPURCHASEDELETE_UPDATEGLDISTRIBUTION @ID, @CHANGEAGENTID, @CHANGEDATE

            -- 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
          end

          if @TRANSACTIONTYPECODE = 7
          begin
            --Delete the auction item information in REVENUEAUCTIONDONATION

            delete dbo.REVENUEAUCTIONDONATION
            where ID = @ID
          end

          exec dbo.USP_REVENUE_CREATEREFUNDSONDELETE @ID, @CHANGEAGENTID

          --JamesWill 04/04/2006 CR239859-040306

          --If this is a recurring gift payment, this will remove its record of payment

          if exists (select 1 from dbo.RECURRINGGIFTACTIVITY inner join dbo.FINANCIALTRANSACTIONLINEITEM on RECURRINGGIFTACTIVITY.PAYMENTREVENUEID = FINANCIALTRANSACTIONLINEITEM.ID
              where FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = @ID)
            delete RECURRINGGIFTACTIVITY 
            from dbo.RECURRINGGIFTACTIVITY inner join dbo.FINANCIALTRANSACTIONLINEITEM on RECURRINGGIFTACTIVITY.PAYMENTREVENUEID = FINANCIALTRANSACTIONLINEITEM.ID
            where FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = @ID 

          --If this is a recurring gift, this will remove all of its activity

          if exists (select 1 from dbo.RECURRINGGIFTACTIVITY where SOURCEREVENUEID = @ID)
            delete from dbo.RECURRINGGIFTACTIVITY where SOURCEREVENUEID = @ID;

          if exists (select 1 from dbo.INSTALLMENTSPLITPAYMENT inner join dbo.FINANCIALTRANSACTIONLINEITEM on INSTALLMENTSPLITPAYMENT.PAYMENTID = FINANCIALTRANSACTIONLINEITEM.ID
              where FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = @ID)
            delete INSTALLMENTSPLITPAYMENT
            from dbo.INSTALLMENTSPLITPAYMENT inner join dbo.FINANCIALTRANSACTIONLINEITEM on INSTALLMENTSPLITPAYMENT.PAYMENTID = FINANCIALTRANSACTIONLINEITEM.ID
            where FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = @ID 

          --Multicurrency - AdamBu 4/12/10   - When deleting a revenue that uses a spot rate, delete that as well

          --                RobertDi 4/14/10 - Fixed this to check that the rate is a spot rate.

          declare @OLDSPOTRATE uniqueidentifier
          select @OLDSPOTRATE = FINANCIALTRANSACTION.BASEEXCHANGERATEID
          from dbo.FINANCIALTRANSACTION
          --inner join dbo.REVENUE_EXT on FINANCIALTRANSACTION.ID = REVENUE_EXT.ID

          inner join dbo.CURRENCYEXCHANGERATE on FINANCIALTRANSACTION.BASEEXCHANGERATEID = CURRENCYEXCHANGERATE.ID
          where FINANCIALTRANSACTION.ID = @ID and CURRENCYEXCHANGERATE.TYPECODE = 2
            and FINANCIALTRANSACTION.DELETEDON is null;

          --Remove all encumbered funds that are not currently matched

          if exists (select 1 from dbo.DONORCHALLENGEENCUMBERED DCE inner join
              dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTIONLINEITEM.ID = DCE.REVENUESPLITID
              where
              FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = @ID and
              FINANCIALTRANSACTIONLINEITEM.TYPECODE <> 1 and
              FINANCIALTRANSACTIONLINEITEM.DELETEDON is null and
              DCE.STATUSTYPECODE <> 1)
            delete DCE from
              dbo.DONORCHALLENGEENCUMBERED DCE
                inner join
                  dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTIONLINEITEM.ID = DCE.REVENUESPLITID
                inner join
                  dbo.REVENUESPLIT_EXT on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID
            where
              FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = @ID and
              FINANCIALTRANSACTIONLINEITEM.TYPECODE <> 1 and
              FINANCIALTRANSACTIONLINEITEM.DELETEDON is null and
              DCE.STATUSTYPECODE <> 1;

          --Delete link to revenue funding request

          delete from dbo.REVENUEFUNDINGREQUEST
          where ID = @ID;

          --These match the statements from the delete trigger on GLTRANSACTION view

          if exists (select 1 from dbo.ADJUSTMENTHISTORY inner join dbo.JOURNALENTRY on ADJUSTMENTHISTORY.GLTRANSACTIONID = JOURNALENTRY.ID inner join dbo.FINANCIALTRANSACTIONLINEITEM on JOURNALENTRY.FINANCIALTRANSACTIONLINEITEMID = FINANCIALTRANSACTIONLINEITEM.ID where FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = @ID)
            update ADJUSTMENTHISTORY set GLTRANSACTIONID = null from dbo.ADJUSTMENTHISTORY inner join dbo.JOURNALENTRY on ADJUSTMENTHISTORY.GLTRANSACTIONID = JOURNALENTRY.ID inner join dbo.FINANCIALTRANSACTIONLINEITEM on JOURNALENTRY.FINANCIALTRANSACTIONLINEITEMID = FINANCIALTRANSACTIONLINEITEM.ID where FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = @ID
          if exists (select 1 from dbo.ADJUSTMENTHISTORYGIFTINKIND inner join dbo.JOURNALENTRY on ADJUSTMENTHISTORYGIFTINKIND.GLTRANSACTIONID = JOURNALENTRY.ID inner join dbo.FINANCIALTRANSACTIONLINEITEM on JOURNALENTRY.FINANCIALTRANSACTIONLINEITEMID = FINANCIALTRANSACTIONLINEITEM.ID where FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = @ID)
            update ADJUSTMENTHISTORYGIFTINKIND set GLTRANSACTIONID = null from dbo.ADJUSTMENTHISTORYGIFTINKIND inner join dbo.JOURNALENTRY on ADJUSTMENTHISTORYGIFTINKIND.GLTRANSACTIONID = JOURNALENTRY.ID inner join dbo.FINANCIALTRANSACTIONLINEITEM on JOURNALENTRY.FINANCIALTRANSACTIONLINEITEMID = FINANCIALTRANSACTIONLINEITEM.ID where FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = @ID
          if exists (select 1 from dbo.ADJUSTMENTHISTORYPROPERTY inner join dbo.JOURNALENTRY on ADJUSTMENTHISTORYPROPERTY.GLTRANSACTIONID = JOURNALENTRY.ID inner join dbo.FINANCIALTRANSACTIONLINEITEM on JOURNALENTRY.FINANCIALTRANSACTIONLINEITEMID = FINANCIALTRANSACTIONLINEITEM.ID where FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = @ID)
            update ADJUSTMENTHISTORYPROPERTY set GLTRANSACTIONID = null from dbo.ADJUSTMENTHISTORYPROPERTY inner join dbo.JOURNALENTRY on ADJUSTMENTHISTORYPROPERTY.GLTRANSACTIONID = JOURNALENTRY.ID inner join dbo.FINANCIALTRANSACTIONLINEITEM on JOURNALENTRY.FINANCIALTRANSACTIONLINEITEMID = FINANCIALTRANSACTIONLINEITEM.ID where FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = @ID
          if exists (select 1 from dbo.ADJUSTMENTHISTORYSTOCK inner join dbo.JOURNALENTRY on ADJUSTMENTHISTORYSTOCK.GLTRANSACTIONID = JOURNALENTRY.ID inner join dbo.FINANCIALTRANSACTIONLINEITEM on JOURNALENTRY.FINANCIALTRANSACTIONLINEITEMID = FINANCIALTRANSACTIONLINEITEM.ID where FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = @ID)
            update ADJUSTMENTHISTORYSTOCK set GLTRANSACTIONID = null from dbo.ADJUSTMENTHISTORYSTOCK inner join dbo.JOURNALENTRY on ADJUSTMENTHISTORYSTOCK.GLTRANSACTIONID = JOURNALENTRY.ID inner join dbo.FINANCIALTRANSACTIONLINEITEM on JOURNALENTRY.FINANCIALTRANSACTIONLINEITEMID = FINANCIALTRANSACTIONLINEITEM.ID where FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = @ID
          if exists (select 1 from dbo.ADJUSTMENTHISTORYWRITEOFF inner join dbo.JOURNALENTRY on ADJUSTMENTHISTORYWRITEOFF.GLTRANSACTIONID = JOURNALENTRY.ID inner join dbo.FINANCIALTRANSACTIONLINEITEM on JOURNALENTRY.FINANCIALTRANSACTIONLINEITEMID = FINANCIALTRANSACTIONLINEITEM.ID where FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = @ID)
            update ADJUSTMENTHISTORYWRITEOFF set GLTRANSACTIONID = null from dbo.ADJUSTMENTHISTORYWRITEOFF inner join dbo.JOURNALENTRY on ADJUSTMENTHISTORYWRITEOFF.GLTRANSACTIONID = JOURNALENTRY.ID inner join dbo.FINANCIALTRANSACTIONLINEITEM on JOURNALENTRY.FINANCIALTRANSACTIONLINEITEMID = FINANCIALTRANSACTIONLINEITEM.ID where FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = @ID

          if exists (select 1 from dbo.INSTALLMENTSPLITWRITEOFF inner join dbo.INSTALLMENTSPLIT on INSTALLMENTSPLITWRITEOFF.INSTALLMENTSPLITID = INSTALLMENTSPLIT.ID where INSTALLMENTSPLIT.PLEDGEID = @ID)
            delete INSTALLMENTSPLITWRITEOFF from dbo.INSTALLMENTSPLITWRITEOFF inner join dbo.INSTALLMENTSPLIT on INSTALLMENTSPLITWRITEOFF.INSTALLMENTSPLITID = INSTALLMENTSPLIT.ID where INSTALLMENTSPLIT.PLEDGEID = @ID
          if exists (select 1 from dbo.INSTALLMENTSPLIT where PLEDGEID = @ID)
            delete from dbo.INSTALLMENTSPLIT where PLEDGEID = @ID

          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

            update dbo.FINANCIALTRANSACTIONLINEITEM
            set DELETEDON = @CHANGEDATE,
              CHANGEDBYID = @CHANGEAGENTID,
              DATECHANGED = @CHANGEDATE
            from dbo.FINANCIALTRANSACTION inner join dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTION.ID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID
            where FINANCIALTRANSACTION.PARENTID = @ID
            and    FINANCIALTRANSACTIONLINEITEM.DELETEDON is null

            update dbo.FINANCIALTRANSACTION
            set DELETEDON = @CHANGEDATE,
              CHANGEDBYID = @CHANGEAGENTID,
              DATECHANGED = @CHANGEDATE
            where PARENTID = @ID
              and DELETEDON is null
            end

          if exists (select ID from dbo.DIRECTDEBITPAYMENTMETHODDETAIL where ID = @REVENUEPAYMENTMETHODID and SEPAMANDATEID is not null) and @TRANSACTIONTYPECODE = 0
          begin
            declare @SEPAMANDATEID uniqueidentifier;

            select 
              @SEPAMANDATEID = SEPAMANDATEID
            from dbo.DIRECTDEBITPAYMENTMETHODDETAIL
            where ID = @REVENUEPAYMENTMETHODID;

            update 
              dbo.SEPAMANDATE
            set PAYMENTCOUNT = PAYMENTCOUNT - 1
            where SEPAMANDATE.ID = @SEPAMANDATEID
              and PAYMENTCOUNT > 0;
          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

          declare @RELATEDLINEITEMSCOUNT integer;
          set @RELATEDLINEITEMSCOUNT = (select COUNT(1) from dbo.FINANCIALTRANSACTIONLINEITEM LI inner join dbo.FINANCIALTRANSACTIONLINEITEM PAYMENT_LI on PAYMENT_LI.SOURCELINEITEMID = LI.ID where LI.FINANCIALTRANSACTIONID = @ID)

          if exists (select 1 from dbo.FINANCIALTRANSACTION where PARENTID = @ID and DELETEDON is not null) or (@RELATEDLINEITEMSCOUNT > 0 and
              (select COUNT(1) from dbo.FINANCIALTRANSACTIONLINEITEM LI inner join dbo.FINANCIALTRANSACTIONLINEITEM PAYMENT_LI on PAYMENT_LI.SOURCELINEITEMID = LI.ID where LI.FINANCIALTRANSACTIONID = @ID and PAYMENT_LI.DELETEDON is not null) = @RELATEDLINEITEMSCOUNT) or
              exists(select 1 
                from dbo.FINANCIALTRANSACTION as [ORG]
                  inner join dbo.REVENUEMATCHINGGIFT as [RMG] on [RMG].MGSOURCEREVENUEID = [ORG].ID                            
                where [ORG].ID = @ID
                and exists(select 1 from FINANCIALTRANSACTION where ID =[RMG].ID and DELETEDON is not null)) 
          begin
            update FINANCIALTRANSACTIONLINEITEM
            set DELETEDON = @CHANGEDATE,
              CHANGEDBYID = @CHANGEAGENTID,
              DATECHANGED = @CHANGEDATE
            where FINANCIALTRANSACTIONID = @ID

            update dbo.FINANCIALTRANSACTION 
            set DELETEDON = @CHANGEDATE,
              CHANGEDBYID = @CHANGEAGENTID,
              DATECHANGED = @CHANGEDATE
            where FINANCIALTRANSACTION.ID = @ID
          end
          else
          begin
            delete dbo.FAFDONATION WHERE REVENUEID in (select ID from dbo.FINANCIALTRANSACTION  where ID = @ID or PARENTID = @ID)

            delete FINANCIALTRANSACTION
            from dbo.FINANCIALTRANSACTION 
            where ID = @ID 
              or PARENTID = @ID
          end

          --Delete the spot rate used on the revenue, unless it is currently in use on the GIFTAIDREFUND table.

          if @OLDSPOTRATE is not null and not exists(select 1 from dbo.REVENUESPLITGIFTAIDREFUND where REVENUESPLITGIFTAIDREFUND.ORIGINALBASEEXCHANGERATEID = @OLDSPOTRATE)
            delete dbo.CURRENCYEXCHANGERATE
            where ID = @OLDSPOTRATE

          --Restore CONTEXT INFO 

          if not @contextCache is null
            set CONTEXT_INFO @contextCache; 

          -- Update RG installments

          if (select count(*) from @RGINSTALLMENTSTOFIX) > 0
          begin
            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

          -- Recalculate FAF raised total if the credit is tied to

          exec dbo.USP_FAFRAISEDTOTAL_CALCULATE @EVENTID = @EVENTID, @CONSTITUENTID = @CONSTITUENTID
          return 0;