USP_DATAFORMTEMPLATE_EDITSAVE_REVENUETRANSACTIONCHANGECONSTITUENTPOSTED_5

CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDITSAVE_REVENUETRANSACTIONCHANGECONSTITUENTPOSTED_5
(
  @ID uniqueidentifier,
  @CHANGEAGENTID uniqueidentifier = null,
  @NEWCONSTITUENTID uniqueidentifier,
  @CONSTITUENTACCOUNTID uniqueidentifier,
  @ADJUSTMENTPOSTDATE datetime,
  @ADJUSTMENTDATE datetime,
  @ADJUSTMENTREASON nvarchar(100),
  @RESETRECOGNITIONCREDITS bit,
  @RESETSOLICITORS bit,
  @ADJUSTMENTREASONCODEID uniqueidentifier,
  @RESETMATCHINGGIFTCLAIMS bit,
  @CURRENTAPPUSERID uniqueidentifier = null,
  @SEPAMANDATEID uniqueidentifier
)
as
  set nocount on;

  -- Verify the new constituent isn't the same as the previous one

  declare @PREVIOUSCONSTITUENTID uniqueidentifier;
  declare @TRANSACTIONTYPECODE tinyint;
  declare @TRANSACTIONCURRENCYID uniqueidentifier;
  declare @BASECURRENCYID uniqueidentifier;
  declare @ADJUSTMENTPOSTSTATUSCODE tinyint;
  declare @ISMEMBERSHIPPLEDGE bit;

  if exists(select ADJUSTMENT.POSTSTATUSCODE from dbo.ADJUSTMENT inner join dbo.FINANCIALTRANSACTION on ADJUSTMENT.REVENUEID = FINANCIALTRANSACTION.ID inner join dbo.REVENUE_EXT on FINANCIALTRANSACTION.ID = REVENUE_EXT.ID where FINANCIALTRANSACTION.ID = @ID and ADJUSTMENT.POSTSTATUSCODE > 0 and FINANCIALTRANSACTION.DELETEDON is null)
  begin
    select top 1 
      @ADJUSTMENTPOSTSTATUSCODE = ADJUSTMENT.POSTSTATUSCODE
      from dbo.ADJUSTMENT
      inner join dbo.FINANCIALTRANSACTION on ADJUSTMENT.REVENUEID = FINANCIALTRANSACTION.ID
      inner join dbo.REVENUE_EXT on FINANCIALTRANSACTION.ID = REVENUE_EXT.ID
    where FINANCIALTRANSACTION.ID = @ID and ADJUSTMENT.POSTSTATUSCODE > 0
      and FINANCIALTRANSACTION.DELETEDON is null;
  end
  else
    Set @ADJUSTMENTPOSTSTATUSCODE = 1

  select top 1 
    @PREVIOUSCONSTITUENTID = FINANCIALTRANSACTION.CONSTITUENTID,
    @TRANSACTIONTYPECODE = FINANCIALTRANSACTION.TYPECODE,
    @TRANSACTIONCURRENCYID = FINANCIALTRANSACTION.TRANSACTIONCURRENCYID
  from dbo.FINANCIALTRANSACTION
  inner join dbo.REVENUE_EXT on FINANCIALTRANSACTION.ID = REVENUE_EXT.ID
  where FINANCIALTRANSACTION.ID = @ID
      and FINANCIALTRANSACTION.DELETEDON is null;

    set @ISMEMBERSHIPPLEDGE = case when @TRANSACTIONTYPECODE = 15 then 1 else 0 end

    select @BASECURRENCYID = BASECURRENCYID
    from dbo.V_BASECURRENCYFORFINANCIALTRANSACTION_I
    where FINANCIALTRANSACTIONID = @ID;

  if @PREVIOUSCONSTITUENTID = @NEWCONSTITUENTID
  begin
    raiserror('NEWCONSTITUENTCANNOTBECURRENTCONSTITUENT', 13, 1);
    return 1;
  end

  if @ADJUSTMENTPOSTSTATUSCODE = 2
  begin
    raiserror('CANNOTCHANGEDONOTPOST', 13, 1)
    return 1
  end

  declare @CHANGEDATE datetime;
  set @CHANGEDATE = getdate();

  if @CHANGEAGENTID is null  
    exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

  -- Get payment code

  declare @PAYMENTMETHODCODE tinyint;
  declare @REVENUEPAYMENTMETHODID uniqueidentifier;

  select 
    @PAYMENTMETHODCODE = PAYMENTMETHODCODE,
    @REVENUEPAYMENTMETHODID = ID 
  from 
    dbo.REVENUEPAYMENTMETHOD 
  where
    REVENUEID = @ID;

  -- Verify the transaction has already been posted

  declare @ISPOSTED bit
  select
    @ISPOSTED = 
    case 
      when exists (select 1 from dbo.REVENUEPOSTED RP where RP.ID = @ID) then 1
      else 0
    end

  if @ISPOSTED = 0
  begin
    raiserror('TRANSACTIONMUSTBEPOSTED', 13, 1);
    return 1  ;         
  end

  -- Verify the adjustment dates are set

  if @ADJUSTMENTPOSTDATE is null
  begin
    raiserror('ADJUSTMENTPOSTDATEREQUIRED', 13, 1);
    return 1;
  end

  if @ADJUSTMENTDATE is null
  begin
    raiserror('ADJUSTMENTDATEREQUIRED', 13, 1);
    return 1;
  end

  if @ADJUSTMENTREASONCODEID is null
  begin
    raiserror('BBERR_ADJUSTMENTREASONCODEIDREQUIRED', 13, 1);
    return 1;
  end

  -- Direct debit account is required for payments

  if @TRANSACTIONTYPECODE = 0 and @CONSTITUENTACCOUNTID is null
  begin
    if @PAYMENTMETHODCODE = 3 -- Direct debit

    begin
      raiserror('BBERR_ACCOUNTREQUIREDFORDIRECTDEBITPAYMENTS', 13, 1);
      return 1;
    end
    else if @PAYMENTMETHODCODE = 11 -- Standing order

    begin
      raiserror('BBERR_ACCOUNTREQUIREDFORSTANDINGORDERPAYMENTS', 13, 1);
      return 1;             
    end
  end

  declare @ADJUSTMENTID uniqueidentifier;
  declare @STOCKSALEADJUSTMENTIDS xml;
  declare @PROPERTYDETAILADJUSTMENTID uniqueidentifier;
  declare @WRITEOFFADJUSTMENTID uniqueidentifier;
  declare @PROPERTYDETAILCOUNT int;
  declare @ADJUSTEDWRITEOFFS table(WRITEOFFID uniqueidentifier, ADJUSTMENTID uniqueidentifier);
  declare @GIFTFEEADJUSTMENTID uniqueidentifier;
  declare @GIFTINKINDSALEADJUSTMENTIDS xml;
  declare @AUCTIONPURCHASEADJUSTMENTID uniqueidentifier;

  begin try
    declare @ADJUSTBENEFITS bit;
    declare @BENEFITSADJUSTMENTID uniqueidentifier;

    set @ADJUSTBENEFITS = 0;
    set @BENEFITSADJUSTMENTID = null;

    --JamesWill 129145 2010-11-10 If there are posted benefit distributions, prepare to adjust them 

    --(at the very least, the reference will need to change to the new constituent's name)

    if exists(select ID from dbo.REVENUEBENEFIT where REVENUEID = @ID) and @ISMEMBERSHIPPLEDGE = 0
      set @ADJUSTBENEFITS = 1;

    exec dbo.USP_SAVE_ADJUSTMENT @ID, @ADJUSTMENTID output, @CHANGEAGENTID, @CHANGEDATE, @ADJUSTMENTDATE, 
      @ADJUSTMENTPOSTDATE, @ADJUSTMENTREASON, default, @ADJUSTMENTREASONCODEID, @ADJUSTMENTPOSTSTATUSCODE, 1;

     update UNPOSTEDFTLI set UNPOSTEDFTLI.SOURCELINEITEMID = ORIGINALFTLI.SOURCELINEITEMID
     from dbo.FINANCIALTRANSACTIONLINEITEM UNPOSTEDFTLI 
     inner join dbo.FINANCIALTRANSACTIONLINEITEM ORIGINALFTLI on ORIGINALFTLI.REVERSEDLINEITEMID = UNPOSTEDFTLI.ID
     where UNPOSTEDFTLI.TYPECODE = 0
       and UNPOSTEDFTLI.FINANCIALTRANSACTIONLINEITEMADJUSTMENTID = @ADJUSTMENTID
       and UNPOSTEDFTLI.POSTSTATUSCODE = 1
       and ORIGINALFTLI.DELETEDON is not null

    if @TRANSACTIONTYPECODE = 0
      exec dbo.USP_SAVE_GIFTFEEADJUSTMENT @ID, @GIFTFEEADJUSTMENTID output, @CHANGEAGENTID, @CHANGEDATE, @ADJUSTMENTDATE, 
        @ADJUSTMENTPOSTDATE, @ADJUSTMENTREASON, default, @ADJUSTMENTREASONCODEID;

    if @ADJUSTBENEFITS = 1
    begin
      declare @BENEFITS xml, @PERCENTAGEBENEFITS xml;
      exec dbo.USP_REVENUE_GETBENEFITSSPLIT @ID, @BENEFITS output, @PERCENTAGEBENEFITS output

      declare @TOTALBENEFITS xml;
      set @TOTALBENEFITS = dbo.UFN_REVENUEDETAIL_JOINBENEFITS(@BENEFITS, @PERCENTAGEBENEFITS);
      set @TOTALBENEFITS = dbo.UFN_REVENUEBENEFIT_CONVERTAMOUNTSINXML_2(@TOTALBENEFITS, @TRANSACTIONCURRENCYID, @BASECURRENCYID);

      exec dbo.USP_SAVE_BENEFITADJUSTMENT_2 @ID, @BENEFITSADJUSTMENTID output, @CHANGEAGENTID, @CHANGEDATE, 
                        @ADJUSTMENTDATE, @ADJUSTMENTPOSTDATE, @ADJUSTMENTREASON, default, 
                        @ADJUSTMENTREASONCODEID, @ADJUSTMENTPOSTSTATUSCODE, @TOTALBENEFITS;
    end
    if @TRANSACTIONTYPECODE = 1 
    begin
      if exists (select JOURNAL.ID from dbo.JOURNALENTRY_EXT JOURNAL 
                  inner join dbo.FINANCIALTRANSACTION on JOURNAL.WRITEOFFID = FINANCIALTRANSACTION.ID and JOURNAL.TABLENAMECODE = 12
                 where FINANCIALTRANSACTION.PARENTID = @ID and FINANCIALTRANSACTION.DELETEDON is null and FINANCIALTRANSACTION.TYPECODE = 20) 
      begin
        declare @WRITEOFFID uniqueidentifier;
        declare @WRITEOFFIDTABLE UDT_GENERICID;

        insert into @WRITEOFFIDTABLE
        select ID
        from dbo.FINANCIALTRANSACTION
        where PARENTID = @ID
        and TYPECODE = 20
        and DELETEDON is null;

        declare @WRITEOFFCURSOR cursor;
        set @WRITEOFFCURSOR = cursor local fast_forward for
        select ID
        from @WRITEOFFIDTABLE

        open @WRITEOFFCURSOR;
        fetch next from @WRITEOFFCURSOR into @WRITEOFFID;

        while @@FETCH_STATUS = 0
        begin
          set @WRITEOFFADJUSTMENTID = null;

          exec dbo.USP_SAVE_WRITEOFFADJUSTMENT @WRITEOFFID, @WRITEOFFADJUSTMENTID output, @CHANGEAGENTID, 
            @CHANGEDATE, @ADJUSTMENTDATE, @ADJUSTMENTPOSTDATE, @ADJUSTMENTREASON, @ADJUSTMENTREASONCODEID;

          --Save adjustment IDs for adjustment history

          insert into @ADJUSTEDWRITEOFFS(WRITEOFFID,ADJUSTMENTID) values (@WRITEOFFID,@WRITEOFFADJUSTMENTID);

          fetch next from @WRITEOFFCURSOR into @WRITEOFFID;  
        end

        close @WRITEOFFCURSOR;
        deallocate @WRITEOFFCURSOR;
      end
    end             
    else
    begin               
      select @PROPERTYDETAILCOUNT = count(PROPERTYDETAIL.ID)
      from dbo.PROPERTYDETAIL 
      where PROPERTYDETAIL.ID = @REVENUEPAYMENTMETHODID and SALEPOSTSTATUSCODE = 0;

      /* If sold stock has been posted, log stock detail adjustment */
      if @PAYMENTMETHODCODE = 4 and exists(select ID from dbo.STOCKSALE where STOCKDETAILID = @REVENUEPAYMENTMETHODID and SALEPOSTSTATUSCODE = 0)
      begin
        exec dbo.USP_SAVE_STOCKDETAILADJUSTMENT @REVENUEPAYMENTMETHODID, @CHANGEAGENTID, @CHANGEDATE, @ADJUSTMENTDATE, 
          @ADJUSTMENTPOSTDATE, @ADJUSTMENTREASON, @STOCKSALEADJUSTMENTIDS output, @ADJUSTMENTREASONCODEID;
      end

      /* If sold gift-in-kind has been posted, log gift-in-kind detail adjustment */
      if @PAYMENTMETHODCODE = 6 and exists(select ID from dbo.GIFTINKINDSALE where GIFTINKINDPAYMENTMETHODDETAILID = @REVENUEPAYMENTMETHODID and SALEPOSTSTATUSCODE = 0)
      begin
        exec dbo.USP_SAVE_GIFTINKINDPAYMENTMETHODDETAILADJUSTMENT @REVENUEPAYMENTMETHODID, @CHANGEAGENTID, @CHANGEDATE, @ADJUSTMENTDATE, 
          @ADJUSTMENTPOSTDATE, @ADJUSTMENTREASON, @GIFTINKINDSALEADJUSTMENTIDS output, @ADJUSTMENTREASONCODEID;
      end

      /* If sold property has been posted, log property detail adjustment */
      else if (@PAYMENTMETHODCODE = 5) and (@PROPERTYDETAILCOUNT > 0) 
      begin
        exec dbo.USP_SAVE_PROPERTYDETAILADJUSTMENT @REVENUEPAYMENTMETHODID, @PROPERTYDETAILADJUSTMENTID output, 
          @CHANGEAGENTID, @CHANGEDATE, @ADJUSTMENTDATE, @ADJUSTMENTPOSTDATE, @ADJUSTMENTREASON, @ADJUSTMENTREASONCODEID;
      end

      /*Auction purchase -- log adjustment*/

      if exists(select 1 from dbo.FINANCIALTRANSACTIONLINEITEM inner join dbo.REVENUESPLIT_EXT on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID where FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = @ID and REVENUESPLIT_EXT.TYPECODE = 12 and FINANCIALTRANSACTIONLINEITEM.TYPECODE <> 1 and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null)
          exec dbo.USP_SAVE_AUCTIONPURCHASEADJUSTMENT @REVENUEID = @ID, @ADJUSTMENTID = @AUCTIONPURCHASEADJUSTMENTID output, @CHANGEAGENTID = @CHANGEAGENTID,
              @CHANGEDATE = @CHANGEDATE, @DATE = @ADJUSTMENTDATE, @POSTDATE = @ADJUSTMENTPOSTDATE, @ADJUSTMENTREASON = @ADJUSTMENTREASON, 
              @ADJUSTMENTREASONCODEID = @ADJUSTMENTREASONCODEID
    end

    exec dbo.USP_REVENUE_UPDATERERECEIPTS @ID, @CHANGEAGENTID, @CHANGEDATE;

    -- WI 292233

    -- update the acknowledgee on letters that have not been sent

    update
      dbo.REVENUELETTER
    set
      REVENUELETTER.ACKNOWLEDGEEID = @NEWCONSTITUENTID,
      REVENUELETTER.CHANGEDBYID = @CHANGEAGENTID, 
      REVENUELETTER.DATECHANGED = @CHANGEDATE
    where
      REVENUELETTER.REVENUEID = @ID
      and REVENUELETTER.ACKNOWLEDGEEID = @PREVIOUSCONSTITUENTID
      and (REVENUELETTER.PROCESSDATE is null and REVENUELETTER.ACKNOWLEDGEDATE is null);

    -- if we're going to be resending letters, update the acknowledgee on letters that have been sent

    -- (if we're not going to be resending letters then the sent letters should reflect the state they were in when they were sent)

    if (coalesce((select top 1 REACKNOWLEDGEREVENUE from dbo.ACKNOWLEDGEMENTPREFERENCE), 0)) = 1
      update
        dbo.REVENUELETTER
      set
        REVENUELETTER.ACKNOWLEDGEEID = @NEWCONSTITUENTID,
        REVENUELETTER.CHANGEDBYID = @CHANGEAGENTID, 
        REVENUELETTER.DATECHANGED = @CHANGEDATE
      where
        REVENUELETTER.REVENUEID = @ID
        and REVENUELETTER.ACKNOWLEDGEEID = @PREVIOUSCONSTITUENTID
        and (REVENUELETTER.PROCESSDATE is not null or REVENUELETTER.ACKNOWLEDGEDATE is not null);
    -- end WI 292233


    exec dbo.USP_REVENUELETTER_SETREACKNOWLEDGEMENTS @ID, @CHANGEAGENTID, @CHANGEDATE;

    --UK product flag.

    if dbo.UFN_INSTALLEDPRODUCTS_PRODUCTIS('9568A6C2-F7AA-45fd-8F54-21FE9654EE2D') = 1
      -- Create manual refunds for any claimed revenue splits

      exec dbo.USP_REVENUE_CREATEREFUNDS @ID, 1, @CHANGEAGENTID, @CHANGEDATE;

    -- Update the constituent for all revenue entries belonging to the transaction

    update dbo.FINANCIALTRANSACTION set 
      CONSTITUENTID = @NEWCONSTITUENTID,
      CHANGEDBYID = @CHANGEAGENTID,
      DATECHANGED = @CHANGEDATE
    where FINANCIALTRANSACTION.ID = @ID
      and FINANCIALTRANSACTION.DELETEDON is null;

    update dbo.FINANCIALTRANSACTIONLINEITEMADJUSTMENT
    set CONSTITUENTID = @NEWCONSTITUENTID,
    CHANGEDBYID = @CHANGEAGENTID,
    DATECHANGED = @CHANGEDATE
    from dbo.FINANCIALTRANSACTIONLINEITEMADJUSTMENT inner join dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTIONLINEITEMADJUSTMENT.ID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONLINEITEMADJUSTMENTID
    where FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = @ID
    and FINANCIALTRANSACTIONLINEITEM.POSTSTATUSCODE = 1
    and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null
    and FINANCIALTRANSACTIONLINEITEM.TYPECODE = 0

    declare @contextCache varbinary(128);

    if @RESETRECOGNITIONCREDITS = 1
    begin
      --Cache CONTEXT INFO

      set @contextCache = CONTEXT_INFO();

      if not @CHANGEAGENTID is null
        set CONTEXT_INFO @CHANGEAGENTID;

      -- Remove previous recognition credits

      delete from dbo.REVENUERECOGNITION where REVENUESPLITID in (select FINANCIALTRANSACTIONLINEITEM.ID from dbo.FINANCIALTRANSACTIONLINEITEM inner join dbo.REVENUESPLIT_EXT on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID where FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = @ID and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null and FINANCIALTRANSACTIONLINEITEM.TYPECODE <> 1)

      --Remove other recognition credits

      delete dbo.RECOGNITIONCREDIT
      from dbo.RECOGNITIONCREDIT
      inner join dbo.DONORCHALLENGEENCUMBERED on RECOGNITIONCREDIT.DONORCHALLENGEENCUMBEREDID = DONORCHALLENGEENCUMBERED.ID
      where RECOGNITIONCREDIT.RECOGNITIONCREDITTYPECODE = 1
        and DONORCHALLENGEENCUMBERED.REVENUESPLITID in (select FINANCIALTRANSACTIONLINEITEM.ID from dbo.FINANCIALTRANSACTIONLINEITEM inner join dbo.REVENUESPLIT_EXT on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID where FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = @ID and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null and FINANCIALTRANSACTIONLINEITEM.TYPECODE <> 1);

      --Remove credits on the donor challenge claim to match edit form

      delete dbo.REVENUERECOGNITION
      from dbo.REVENUERECOGNITION
      inner join dbo.FINANCIALTRANSACTIONLINEITEM on REVENUERECOGNITION.REVENUESPLITID = FINANCIALTRANSACTIONLINEITEM.ID
      inner join dbo.REVENUESPLIT_EXT on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID
      inner join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = FINANCIALTRANSACTION.ID
      inner join dbo.REVENUE_EXT on FINANCIALTRANSACTION.ID = REVENUE_EXT.ID
      inner join dbo.DONORCHALLENGEENCUMBERED on FINANCIALTRANSACTION.ID = DONORCHALLENGEENCUMBERED.MATCHEDREVENUEID
      where DONORCHALLENGEENCUMBERED.REVENUESPLITID in (select FINANCIALTRANSACTIONLINEITEM.ID from dbo.FINANCIALTRANSACTIONLINEITEM inner join dbo.REVENUESPLIT_EXT on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID where FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = @ID and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null and FINANCIALTRANSACTIONLINEITEM.TYPECODE <> 1)
        and FINANCIALTRANSACTION.CONSTITUENTID <> REVENUERECOGNITION.CONSTITUENTID
        and REVENUESPLIT_EXT.DESIGNATIONID = DONORCHALLENGEENCUMBERED.DESIGNATIONID
        and FINANCIALTRANSACTION.DELETEDON is null and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null
        and FINANCIALTRANSACTIONLINEITEM.TYPECODE <> 1;

      --Restore CONTEXT_INFO

      if not @contextCache is null
        set CONTEXT_INFO @contextCache;

      -- Create the new default recognition credits

      insert into dbo.REVENUERECOGNITION
      (
        REVENUESPLITID, 
        CONSTITUENTID, 
        AMOUNT,
        REVENUERECOGNITIONTYPECODEID,
        EFFECTIVEDATE,
        ADDEDBYID,CHANGEDBYID,DATEADDED,DATECHANGED
      )
      select
        RS.ID,
        RR.CONSTITUENTID,
        RR.AMOUNT,
        RR.REVENUERECOGNITIONTYPECODEID,
        cast(R.DATE as datetime) as DATE,
        @CHANGEAGENTID,@CHANGEAGENTID,@CHANGEDATE,@CHANGEDATE
      from dbo.FINANCIALTRANSACTION R
      inner join dbo.REVENUE_EXT on R.ID = REVENUE_EXT.ID
      inner join dbo.FINANCIALTRANSACTIONLINEITEM RS on RS.FINANCIALTRANSACTIONID = R.ID
      inner join dbo.REVENUESPLIT_EXT on RS.ID = REVENUESPLIT_EXT.ID
      left join dbo.RECURRINGGIFTACTIVITY RGA on RGA.PAYMENTREVENUEID = R.ID
      cross apply dbo.UFN_REVENUEDETAIL_GETDEFAULTRECOGNITIONS(REVENUE_EXT.GIVENANONYMOUSLY, R.CONSTITUENTID, RS.BASEAMOUNT, cast(R.DATE as datetime), RGA.SOURCEREVENUEID) RR
      where R.ID = @ID
          and R.DELETEDON is null
          and RS.DELETEDON is null
          and RS.TYPECODE <> 1;
    end

    if @PAYMENTMETHODCODE in (3, 11) --Direct debit or standing order

    begin
      if @TRANSACTIONTYPECODE in (1, 2, 15) -- Pledge or recurring gift or membership installment plan

      begin
        if @CONSTITUENTACCOUNTID is not null
        begin
          if @PAYMENTMETHODCODE = 3 -- Direct debit

          begin
            update dbo.REVENUESCHEDULEDIRECTDEBITPAYMENT set
              CONSTITUENTACCOUNTID = @CONSTITUENTACCOUNTID,
              SEPAMANDATEID = @SEPAMANDATEID,
              CHANGEDBYID = @CHANGEAGENTID,
              DATECHANGED = @CHANGEDATE
            where REVENUESCHEDULEDIRECTDEBITPAYMENT.ID = @ID;
          end
          else if @PAYMENTMETHODCODE = 11 -- Standing order

          begin
            update dbo.REVENUESCHEDULESTANDINGORDERPAYMENT set
              CONSTITUENTACCOUNTID = @CONSTITUENTACCOUNTID,
              CHANGEDBYID = @CHANGEAGENTID,
              DATECHANGED = @CHANGEDATE
            where REVENUESCHEDULESTANDINGORDERPAYMENT.ID = @ID;
          end
        end
        else
        begin
          -- No longer autopay

          update dbo.REVENUEPAYMENTMETHOD set
            PAYMENTMETHODCODE = 9,
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @CHANGEDATE
          where 
            REVENUEID = @ID;

          -- Delete entry

          exec dbo.USP_REVENUESCHEDULEDIRECTDEBITPAYMENT_DELETEBYID_WITHCHANGEAGENTID @ID, @CHANGEAGENTID;
        end
      end
      else if @TRANSACTIONTYPECODE = 0 -- Payment

      begin
        if @PAYMENTMETHODCODE = 3 -- Direct debit

        begin

          --Run this before we update the payment method details, because we need to see if the SEPA Mandate has changed

          exec dbo.USP_SEPAMANDATE_PAYMENTMADE 
            @SEPAMANDATEID, 
            null, --BATCHROWID

            @ID, --REVENUEID

            @CHANGEAGENTID;

          update dbo.DIRECTDEBITPAYMENTMETHODDETAIL 
          set
            CONSTITUENTACCOUNTID = @CONSTITUENTACCOUNTID,
            SEPAMANDATEID = @SEPAMANDATEID,
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @CHANGEDATE
          where 
            DIRECTDEBITPAYMENTMETHODDETAIL.ID = @REVENUEPAYMENTMETHODID;

        end
        else if @PAYMENTMETHODCODE = 11 -- Standing order

        begin
          update dbo.STANDINGORDERPAYMENTMETHODDETAIL set 
            CONSTITUENTACCOUNTID = @CONSTITUENTACCOUNTID,
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @CHANGEDATE
          where ID = @REVENUEPAYMENTMETHODID;
        end
      end
    end

    -- Clear GL

    delete JOURNALENTRY
    from dbo.JOURNALENTRY 
      inner join dbo.FINANCIALTRANSACTIONLINEITEM on JOURNALENTRY.FINANCIALTRANSACTIONLINEITEMID = FINANCIALTRANSACTIONLINEITEM.ID
      inner join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = FINANCIALTRANSACTION.ID 
    inner join dbo.JOURNALENTRY_EXT on JOURNALENTRY.ID = JOURNALENTRY_EXT.ID
    where 
      FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = @ID
    and FINANCIALTRANSACTIONLINEITEM.TYPECODE != 1
      and FINANCIALTRANSACTION.DELETEDON is null
    and JOURNALENTRY_EXT.OUTDATED = 0
    and JOURNALENTRY_EXT.TABLENAMECODE = 1;

    delete from dbo.WRITEOFFGLDISTRIBUTION where WRITEOFFID in (select WO.ID from dbo.WRITEOFF WO where WO.REVENUEID = @ID) and OUTDATED = 0;
    delete from dbo.STOCKSALEGLDISTRIBUTION where REVENUEID = @ID and OUTDATED = 0;
    delete from dbo.PROPERTYDETAILGLDISTRIBUTION where REVENUEID = @ID and OUTDATED = 0;
    delete from dbo.GIFTINKINDSALEGLDISTRIBUTION where REVENUEID = @ID and OUTDATED = 0;
    delete from dbo.AUCTIONPURCHASEGLDISTRIBUTION where REVENUEPURCHASEID = @ID and OUTDATED = 0;

    if @ADJUSTBENEFITS = 1 
      delete from dbo.BENEFITGLDISTRIBUTION where REVENUEID = @ID and OUTDATED = 0;

    if @TRANSACTIONTYPECODE = 0
    begin
      delete from dbo.GLTRANSACTION
      where GLTRANSACTION.ID in (select GIFTFEEGLDISTRIBUTION.GLTRANSACTIONID 
        from dbo.GIFTFEEGLDISTRIBUTION
        inner join dbo.REVENUESPLIT on REVENUESPLIT.ID = GIFTFEEGLDISTRIBUTION.REVENUESPLITGIFTFEEID
        where REVENUESPLIT.REVENUEID = @ID and GIFTFEEGLDISTRIBUTION.OUTDATED = 0);

      delete from dbo.REVENUESPLITGIFTFEE
      where REVENUESPLITGIFTFEE.ID in (select REVENUESPLIT.ID from dbo.REVENUESPLIT where REVENUESPLIT.REVENUEID = @ID);

      exec dbo.USP_PAYMENT_ADDGIFTFEES @ID, @NEWCONSTITUENTID, @CHANGEAGENTID, @CHANGEDATE;
    end

    -- Add new GL distributions

    if (select FINANCIALTRANSACTION.POSTSTATUSCODE from dbo.FINANCIALTRANSACTION inner join dbo.REVENUE_EXT on FINANCIALTRANSACTION.ID = REVENUE_EXT.ID where FINANCIALTRANSACTION.ID = @ID and FINANCIALTRANSACTION.DELETEDON is null) != 3 --Do not post

    begin
      -- Add new GL distributions

      exec dbo.USP_SAVE_REVENUEGLDISTRIBUTION @ID, @CHANGEAGENTID, @CHANGEDATE;

      -- Add new stock detail GL distributions

      if @PAYMENTMETHODCODE = 4 and exists(select ID from dbo.STOCKSALE where STOCKDETAILID = @REVENUEPAYMENTMETHODID)
        exec dbo.USP_SAVE_STOCKDETAILGLDISTRIBUTION @ID, @CHANGEAGENTID, @CHANGEDATE;

      -- Add new gift-in-kind detail GL distributions

      if @PAYMENTMETHODCODE = 6 and exists(select ID from dbo.GIFTINKINDSALE where GIFTINKINDPAYMENTMETHODDETAILID = @REVENUEPAYMENTMETHODID)
        exec dbo.USP_SAVE_GIFTINKINDPAYMENTMETHODDETAILGLDISTRIBUTION @ID, @CHANGEAGENTID, @CHANGEDATE;

      -- Add new property detail GL distributions

      if @PAYMENTMETHODCODE = 5 and exists(select ID from dbo.PROPERTYDETAIL where ID = @REVENUEPAYMENTMETHODID and ISSOLD = 1)
        exec dbo.USP_SAVE_PROPERTYDETAILGLDISTRIBUTION @ID, @CHANGEAGENTID, @CHANGEDATE;

      -- Add new write-off GL distributions

      if @TRANSACTIONTYPECODE in (1, 15) and dbo.UFN_VALID_BASICGL_INSTALLED() = 1 
        exec dbo.USP_SAVE_PLEDGEWRITEOFFGLDISTRIBUTION @ID, @WRITEOFFIDTABLE, @CHANGEAGENTID, @CHANGEDATE
      else if @TRANSACTIONTYPECODE = 1
        exec dbo.USP_SAVE_WRITEOFFGLDISTRIBUTION @ID, @CHANGEAGENTID, @CHANGEDATE;

      -- Add new benefit GL distributions

      if @ADJUSTBENEFITS = 1
        exec USP_SAVE_BENEFITGLDISTRIBUTION_FINANCIALTRANSACTION @ID, @CHANGEAGENTID, @CHANGEDATE;

      -- Add new auction purchase GL distributions

      if exists(select 1 from dbo.FINANCIALTRANSACTIONLINEITEM inner join dbo.REVENUESPLIT_EXT on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID where FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = @ID and REVENUESPLIT_EXT.TYPECODE = 12 and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null and FINANCIALTRANSACTIONLINEITEM.TYPECODE <> 1)
          exec dbo.USP_SAVE_AUCTIONPURCHASEGLDISTRIBUTION @REVENUEID = @ID, @CHANGEAGENTID = @CHANGEAGENTID, @CHANGEDATE = @CHANGEDATE

      declare @DEPOSITID uniqueidentifier;
      declare @DEPOSITPOSTDATE date;
      select @DEPOSITID = BADP.DEPOSITID
        ,@DEPOSITPOSTDATE = FT.POSTDATE
      from dbo.BANKACCOUNTDEPOSITPAYMENT BADP
      inner join dbo.FINANCIALTRANSACTION FT on FT.ID = BADP.DEPOSITID
      where BADP.ID = @ID;

      if @DEPOSITID is not null
      begin
        if @DEPOSITPOSTDATE = isnull(@ADJUSTMENTPOSTDATE, @DEPOSITPOSTDATE)
          exec dbo.USP_BANKACCOUNTDEPOSIT_OVERWRITEPAYMENTDEBITACCOUNTS @ID, @DEPOSITID, @CHANGEAGENTID, @CHANGEDATE;
        else
          update dbo.BANKACCOUNTDEPOSITPAYMENT set DEPOSITID = null where ID = @ID;
      end
    end

    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 @REVENUEPAYMENTMETHODID, @CHANGEAGENTID, null, @STOCKSALEADJUSTMENTIDS;

    if @GIFTINKINDSALEADJUSTMENTIDS is not null
      exec dbo.USP_ADJUSTMENTHISTORY_GIFTINKIND_SAVEHISTORY @REVENUEPAYMENTMETHODID, @CHANGEAGENTID, null, @GIFTINKINDSALEADJUSTMENTIDS;

    if @PROPERTYDETAILADJUSTMENTID is not null
      exec dbo.USP_ADJUSTMENTHISTORY_PROPERTY_SAVEHISTORY @REVENUEPAYMENTMETHODID, @CHANGEAGENTID, null, @PROPERTYDETAILADJUSTMENTID;

    -- If this is a pledge, save the adjustment history for any write-offs

    if @TRANSACTIONTYPECODE = 1 and (select count(*) from @ADJUSTEDWRITEOFFS) > 0
    begin
      declare @HISTORYWRITEOFFID uniqueidentifier;
      declare @HISTORYADJUSTMENTID uniqueidentifier;

      /* Cursor to use for logging history adjustments */
      declare HISTORYCURSOR cursor local fast_forward for
      select WRITEOFFID, ADJUSTMENTID from @ADJUSTEDWRITEOFFS

      open HISTORYCURSOR;
      fetch next from HISTORYCURSOR into @HISTORYWRITEOFFID, @HISTORYADJUSTMENTID;

      while @@FETCH_STATUS = 0 
      begin
        if @HISTORYADJUSTMENTID is not null
          exec dbo.USP_ADJUSTMENTHISTORY_WRITEOFF_SAVEHISTORY @HISTORYWRITEOFFID, @CHANGEAGENTID, null, @HISTORYADJUSTMENTID;

        fetch next from HISTORYCURSOR into @HISTORYWRITEOFFID, @HISTORYADJUSTMENTID;
      end

      close HISTORYCURSOR;
      deallocate HISTORYCURSOR;
    end

    if @RESETMATCHINGGIFTCLAIMS = 1
    begin
      -- Create default matching gifts for the new constituent

      exec USP_REVENUE_MATCHINGGIFT_CHANGECONSTITUENT @ID, @CHANGEAGENTID, @CHANGEDATE, @CURRENTAPPUSERID;
    end

  end try
  begin catch
    exec dbo.USP_RAISE_ERROR;
    return 1;
  end catch

  return 0;