USP_DATAFORMTEMPLATE_EDIT_PLEDGEADJUST_11

CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_PLEDGEADJUST_11 (
  @ID uniqueidentifier
  ,@CHANGEAGENTID uniqueidentifier = null
  ,@DATE datetime
  ,@AMOUNT money
  ,@POSTSTATUSCODE tinyint
  ,@POSTDATE datetime
  ,@SENDPLEDGEREMINDER bit
  ,@SPLITS xml
  ,@FREQUENCYCODE tinyint
  ,@NUMBEROFINSTALLMENTS int
  ,@NEXTTRANSACTIONDATE datetime
  ,@INSTALLMENTS xml
  ,@SOURCECODE nvarchar(50)
  ,@APPEALID uniqueidentifier
  ,@BENEFITS xml
  ,@BENEFITSWAIVED bit
  ,@GIVENANONYMOUSLY bit
  ,@MAILINGID uniqueidentifier
  ,@CHANNELCODEID uniqueidentifier
  ,@DONOTACKNOWLEDGE bit
  ,@PLEDGESUBTYPEID uniqueidentifier
  ,@OPPORTUNITYID uniqueidentifier
  ,@REFERENCE nvarchar(255)
  ,@CATEGORYCODEID uniqueidentifier
  ,@ADJUSTMENTDATE datetime
  ,@ADJUSTMENTPOSTDATE datetime
  ,@ADJUSTMENTREASON nvarchar(300)
  ,@ADJUSTMENTREASONCODEID uniqueidentifier
  ,@ADJPAYMENT_DATE datetime
  ,@ADJPAYMENT_POSTDATE datetime
  ,@ADJPAYMENT_REASONCODEID uniqueidentifier
  ,@ADJPAYMENT_DETAILS nvarchar(255)
  ,@PERCENTAGEBENEFITS xml
  ,@BASECURRENCYID uniqueidentifier
  ,@TRANSACTIONCURRENCYID uniqueidentifier
  ,@BASEEXCHANGERATEID uniqueidentifier
  ,@EXCHANGERATE decimal(20, 8)
  ,@HADSPOTRATE bit
  ,@RATECHANGED bit
  ,@UPDATERECOGNITIONOPTION tinyint
  ,@CURRENTAPPUSERID uniqueidentifier = null
  ,@UPDATETRIBUTEOPTION tinyint
  ,@VALIDATETRIBUTES bit
  ,@ISMEMBERSHIPPLEDGE bit
  ,@INSTALLMENTAMOUNT money

  )
as
begin
  set nocount on;

  declare @CURRENTDATE datetime;
  declare @SUM money;
  declare @COUNT int;
  declare @ADJUST bit = 0;
  declare @ADJUSTMENTID uniqueidentifier;
  declare @CLEARGLDISTRIBUTION bit = 0;
  declare @CLEARWRITEOFFGLDISTRIBUTION bit = 0;
  declare @WRITEOFFADJUSTMENTID uniqueidentifier;
  declare @REACKNOWLEDGEFIELDCHANGED bit = 0;
  declare @ADJUSTBENEFITS bit = 0;
  declare @EMPTYGUID uniqueidentifier = '00000000-0000-0000-0000-000000000000';
  declare @CLEARBENEFITSGLDISTRIBUTION bit = 0;
  declare @BENEFITSADJUSTMENTID uniqueidentifier;

  declare @contextCache varbinary(128);

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

  --cache current context information
  set @contextCache = CONTEXT_INFO();
  --set CONTEXT_INFO to @CHANGEAGENTID
  set CONTEXT_INFO @CHANGEAGENTID;

  begin try
    -- Check GL business rule for this account system and set to 'Do not post' if needed.
    declare @PDACCOUNTSYSTEMID uniqueidentifier;

    select @PDACCOUNTSYSTEMID = PDACCOUNTSYSTEMID
    from dbo.PDACCOUNTSYSTEMFORREVENUE
    where ID = @ID;

    if dbo.UFN_PDACCOUNTSYSTEM_ALLOWGLDISTRIBUTIONS(@PDACCOUNTSYSTEMID) = 0
    begin
      set @POSTSTATUSCODE = 3 -- Do not post
      set @POSTDATE = null
    end
    else
    begin
      set @POSTSTATUSCODE = 2

      if @POSTDATE is null
        set @POSTDATE = @ADJUSTMENTPOSTDATE;-- bug 143015
    end;

    --POSTSTATUSCODE passed to this procedure is assumed to be the FINANCIALTRANSACTION poststatus code (1-not posted, 2-posted, 3-do not post)
    declare @ALTERNATEPOSTSTATUS tinyint = case 
        when @POSTSTATUSCODE = 3
          then 2
        else 1
        end;

    set @CURRENTDATE = GetDate();

    --Multicurrency - AdamBu 4/8/10 - If the revenue previously used a spot rate, but
    -- its rate has changed, store the old rate's ID, so we can remove it later.
    declare @OLDSPOTRATE uniqueidentifier

    --Transaction currency cannot be changed, make sure it is the same as the revenue
    select @TRANSACTIONCURRENCYID = TRANSACTIONCURRENCYID
      ,@OLDSPOTRATE = BASEEXCHANGERATEID
    from dbo.FINANCIALTRANSACTION
    where ID = @ID;

    if @HADSPOTRATE = 0
      or @RATECHANGED = 0
      set @OLDSPOTRATE = null

    --If the record uses a new spot rate, create it and set the rate ID.
    if @BASEEXCHANGERATEID = '00000000-0000-0000-0000-000000000001'
    begin
      set @BASEEXCHANGERATEID = newid();

      --Replace with call to USP_CURRENCYEXCHANGERATE_ENSURESPOTRATEEXISTS for PBI 102747 in the future
      /*if not dbo.UFN_SECURITY_APPUSER_GRANTED_SYSTEMPRIVILEGE_IN_SYSTEMROLE(@CURRENTAPPUSERID,'911f104d-ba5f-4469-b0ae-184c879aea99') = 1
        and not dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1
      begin
        raiserror('User does not have the right to add a new spot rate.', 13, 1);
        return 1;
      end*/
      insert into dbo.CURRENCYEXCHANGERATE (
        ID
        ,FROMCURRENCYID
        ,TOCURRENCYID
        ,RATE
        ,ASOFDATE
        ,TYPECODE
        ,SOURCECODEID
        ,ADDEDBYID
        ,CHANGEDBYID
        ,DATEADDED
        ,DATECHANGED
        )
      values (
        @BASEEXCHANGERATEID
        ,@TRANSACTIONCURRENCYID
        ,@BASECURRENCYID
        ,@EXCHANGERATE
        ,@DATE
        ,2
        ,null
        ,@CHANGEAGENTID
        ,@CHANGEAGENTID
        ,@CURRENTDATE
        ,@CURRENTDATE
        );
    end

    --Multicurrency - Retrieve and calculate the necessary multicurrency values.                
    declare @BASEAMOUNT money;
    declare @ORGANIZATIONCURRENCYID uniqueidentifier;
    declare @ORGANIZATIONAMOUNT money;
    declare @ORGANIZATIONEXCHANGERATEID uniqueidentifier;

    exec dbo.USP_CURRENCY_GETCURRENCYVALUES @AMOUNT
      ,@DATE
      ,@BASECURRENCYID
      ,@BASEEXCHANGERATEID
      ,@TRANSACTIONCURRENCYID output
      ,@BASEAMOUNT output
      ,@ORGANIZATIONCURRENCYID output
      ,@ORGANIZATIONAMOUNT output
      ,@ORGANIZATIONEXCHANGERATEID output
      ,1;

    set @ADJUST = 0;
    set @ADJUSTMENTID = null;

    declare @TOTALBENEFITS xml;

    set @TOTALBENEFITS = dbo.UFN_REVENUEDETAIL_JOINBENEFITS(@BENEFITS, @PERCENTAGEBENEFITS);

    if @BENEFITSWAIVED = 1
      set @TOTALBENEFITS = null

    /* Check if currency values changed */
    if not exists (
        select 1
        from dbo.FINANCIALTRANSACTION
        where FINANCIALTRANSACTION.ID = @ID
          and (
            (BASEEXCHANGERATEID = @BASEEXCHANGERATEID)
            or (
              BASEEXCHANGERATEID is null
              and @BASEEXCHANGERATEID is null
              )
            )
          and (
            (ORGEXCHANGERATEID = @ORGANIZATIONEXCHANGERATEID)
            or (
              ORGEXCHANGERATEID is null
              and @ORGANIZATIONEXCHANGERATEID is null
              )
            )
          and DELETEDON is null
        )
    begin
      set @ADJUST = 1;
      set @CLEARGLDISTRIBUTION = 1;
      set @CLEARWRITEOFFGLDISTRIBUTION = 1;
    end
    else
      if not exists (
          select 1
          from dbo.FINANCIALTRANSACTION
          inner join dbo.REVENUE_EXT on FINANCIALTRANSACTION.ID = REVENUE_EXT.ID
          where FINANCIALTRANSACTION.ID = @ID
            and TRANSACTIONAMOUNT = @AMOUNT
            and ORGAMOUNT = @ORGANIZATIONAMOUNT
            and isnull(REVENUE_EXT.APPEALID, @EMPTYGUID) = isnull(@APPEALID, @EMPTYGUID)
            and isnull(REVENUE_EXT.CHANNELCODEID, @EMPTYGUID) = isnull(@CHANNELCODEID, @EMPTYGUID)
            and TRANSACTIONCURRENCYID = @TRANSACTIONCURRENCYID
            and (
              BASEEXCHANGERATEID = @BASEEXCHANGERATEID
              or (
                BASEEXCHANGERATEID is null
                and @BASEEXCHANGERATEID is null
                )
              )
            and DELETEDON is null
          )
      begin
        set @ADJUST = 1;
        set @CLEARGLDISTRIBUTION = 1;
      end

    declare @SPLITSCHANGED bit = dbo.UFN_CHECKDETAIL_SPLITSCHANGED(@ID, @SPLITS);
    declare @DESIGNATIONCHANGED bit = dbo.UFN_CHECKDETAIL_DESIGNATIONSCHANGED(@ID, @SPLITS);

    /* Check if designations changed */
    if @SPLITSCHANGED = 1
    begin
      set @ADJUST = 1;
      set @CLEARGLDISTRIBUTION = 1;
      set @REACKNOWLEDGEFIELDCHANGED = 1;
    end

    -- check to see if installments have changed
    if @CLEARGLDISTRIBUTION = 0
    begin
      if dbo.UFN_CHECKDETAIL_INSTALLMENTSCHANGED(@ID, @INSTALLMENTS) = 1
      begin
        set @ADJUST = 1;
        set @CLEARGLDISTRIBUTION = 1;
        set @CLEARWRITEOFFGLDISTRIBUTION = 1;
      end
    end

    --Update the organization total value, transaction total value, and exchange rates in the @TOTALBENEFITS XML.
    set @TOTALBENEFITS = dbo.UFN_REVENUEBENEFIT_CONVERTAMOUNTSINXML_2(@TOTALBENEFITS, @TRANSACTIONCURRENCYID, @BASECURRENCYID);

    declare @BENEFITSCHANGED bit = 0;
    set @BENEFITSCHANGED = dbo.UFN_CHECKDETAIL_BENEFITSCHANGEDFORGL(@ID, @TOTALBENEFITS)

    if @BENEFITSCHANGED = 1
    begin
      set @CLEARBENEFITSGLDISTRIBUTION = 1;

      --Only create adjustment if posted benefits exist
      if exists(select top 1 1 from dbo.FINANCIALTRANSACTIONLINEITEM LI inner join dbo.REVENUEBENEFIT_EXT RB on LI.ID = RB.ID where LI.FINANCIALTRANSACTIONID = @ID and LI.POSTSTATUSCODE = 2)
      begin
        set @ADJUSTBENEFITS = 1;
      end
    end

    if @ADJUSTBENEFITS = 0
      if exists (
          select 1
          from dbo.BENEFITADJUSTMENT
          where REVENUEID = @ID
            and POSTSTATUSCODE = 1
          )
      begin
        set @ADJUSTBENEFITS = 1;
      end

    --we need to save the adjustment before we save the benefits so that the previous value will be correct
    --in the adjustment
    if @ADJUSTBENEFITS = 1
    begin
      if @ADJUSTMENTREASONCODEID is null
      begin
        if @ISMEMBERSHIPPLEDGE = 0
          raiserror ('BBERR_ADJUSTMENTREASONCODEIDREQUIRED',13,1);
        else
          raiserror ('BBERR_MEMBERSHIPPLEDGE_ADJUSTMENTREASONCODEIDREQUIRED',13,1);
      end

      if @ISMEMBERSHIPPLEDGE = 0
      begin
        set @BENEFITSADJUSTMENTID = null;
        exec dbo.USP_SAVE_BENEFITADJUSTMENT_2 @ID
          ,@BENEFITSADJUSTMENTID output
          ,@CHANGEAGENTID
          ,@CURRENTDATE
          ,@ADJUSTMENTDATE
          ,@ADJUSTMENTPOSTDATE
          ,@ADJUSTMENTREASON
          ,default
          ,@ADJUSTMENTREASONCODEID
          ,@ALTERNATEPOSTSTATUS
          ,@TOTALBENEFITS;--expects the 'other' post status code (0,1,2) not the one passed to this procedure (1,2,3) - because that would make too much sense
      end
      else --MEMBERSHIPPLEDGE: Not posting benefits right now
      begin
          exec dbo.USP_REVENUE_GETBENEFITS_5_UPDATEFROMXML @ID, @TOTALBENEFITS, @CHANGEAGENTID, @CURRENTDATE, default, default;
      end
    end
    else if @BENEFITSCHANGED = 1
    begin
      -- create benefits
      exec dbo.USP_REVENUE_GETBENEFITS_5_UPDATEFROMXML @ID, @TOTALBENEFITS, @CHANGEAGENTID, @CURRENTDATE, null, @ADJUSTMENTPOSTDATE;
    end

    declare @CATEGORYCHANGED bit = 0;

    if (@SPLITS.exist('(/SPLITS/ITEM/CATEGORYCODEID)') = 1)
    begin
      if (
          exists (
            select 1
            from dbo.UFN_REVENUE_GETSPLITS_2_FROMITEMLISTXML(@SPLITS) as SPLITS
            left join dbo.REVENUECATEGORY on REVENUECATEGORY.ID = SPLITS.ID
            where (
                (REVENUECATEGORY.GLREVENUECATEGORYMAPPINGID <> SPLITS.CATEGORYCODEID)
                or (
                  (REVENUECATEGORY.GLREVENUECATEGORYMAPPINGID is null)
                  and (SPLITS.CATEGORYCODEID is not null)
                  )
                or (
                  (REVENUECATEGORY.GLREVENUECATEGORYMAPPINGID is not null)
                  and (SPLITS.CATEGORYCODEID is null)
                  )
                )
            )
          )
      begin
        set @CATEGORYCHANGED = 1;
      end
    end
    else
      if exists (
          select 1
          from dbo.FINANCIALTRANSACTIONLINEITEM REVENUESPLIT
          inner join dbo.FINANCIALTRANSACTION on REVENUESPLIT.FINANCIALTRANSACTIONID = FINANCIALTRANSACTION.ID
          left join dbo.REVENUECATEGORY on REVENUECATEGORY.ID = REVENUESPLIT.ID
          where FINANCIALTRANSACTION.TYPECODE in (
              0
              ,1
              ,2
              ,3
              ,4
              ,5
              ,6
              ,7
              ,8
              ,9
              ,15
              )
            and (REVENUESPLIT.DELETEDON is null)
            and (REVENUESPLIT.TYPECODE <> 1)
            and (REVENUESPLIT.FINANCIALTRANSACTIONID = @ID)
            and (
              (REVENUECATEGORY.GLREVENUECATEGORYMAPPINGID <> @CATEGORYCODEID)
              or (
                (REVENUECATEGORY.GLREVENUECATEGORYMAPPINGID is null)
                and (@CATEGORYCODEID is not null)
                )
              or (
                (REVENUECATEGORY.GLREVENUECATEGORYMAPPINGID is not null)
                and (@CATEGORYCODEID is null)
                )
              )
          )
      begin
        set @CATEGORYCHANGED = 1;
      end

    declare @OLDPLEDGESUBTYPEID uniqueidentifier

    select @OLDPLEDGESUBTYPEID = PLEDGESUBTYPEID
    from dbo.REVENUESCHEDULE
    where ID = @ID

    /* Check if revenue category changed */
    if (@CATEGORYCHANGED = 1)
      or (
        @PLEDGESUBTYPEID is null
        and @OLDPLEDGESUBTYPEID is not null
        )
      or (
        @PLEDGESUBTYPEID is not null
        and @OLDPLEDGESUBTYPEID is null
        )
      or (@PLEDGESUBTYPEID <> @OLDPLEDGESUBTYPEID)
    begin
      set @ADJUST = 1;
      set @CLEARGLDISTRIBUTION = 1;
      set @CLEARWRITEOFFGLDISTRIBUTION = 1;
    end

    /* Already adjusted */
    if @ADJUST = 0
      if exists (
          select 1
          from dbo.ADJUSTMENT
          where REVENUEID = @ID
            and POSTSTATUSCODE = 1
          )
        set @ADJUST = 1

    declare @TRIBUTEAMOUNT money;

    select @TRIBUTEAMOUNT = sum(AMOUNT)
    from dbo.REVENUETRIBUTE
    where REVENUEID = @ID;

    -- do not allow the gift amount to be adjusted less than the applied tribute amount
    if (@TRIBUTEAMOUNT is not null)
      and (@AMOUNT < @TRIBUTEAMOUNT)
      and (@UPDATETRIBUTEOPTION = 0)
    begin
      raiserror (
          'The pledge amount cannot be less than the sum of the tribute amounts applied to this pledge.'
          ,13
          ,1
          )
    end

    if @ISMEMBERSHIPPLEDGE = 1
    begin
      exec dbo.USP_MEMBERSHIPPLEDGE_VALIDATESPLITS @SPLITS = @SPLITS
        ,@REVENUEAMOUNT = @AMOUNT
        ,@REVENUEID = @ID
        ,@TRANSACTIONCURRENCYID = @TRANSACTIONCURRENCYID;
    end
    else
    begin
    exec dbo.USP_REVENUESPLIT_VALIDATESPLITS @SPLITS = @SPLITS
      ,@REVENUEAMOUNT = @AMOUNT
        ,@REVENUEID = @ID
      ,@TRANSACTIONCURRENCYID = @TRANSACTIONCURRENCYID;
    end

    select @SUM = sum(AMOUNT)
      ,@COUNT = count(AMOUNT)
    from dbo.UFN_INSTALLMENT_GETINSTALLMENTS_2_FROMITEMLISTXML(@INSTALLMENTS);

    if @COUNT = 0
      raiserror (
          'INSTALLMENTCOUNT'
          ,13
          ,1
          );

    if @SUM <> @AMOUNT
    begin
      if @ISMEMBERSHIPPLEDGE = 0
        raiserror('INSTALLMENTSUM',13,1);
      else
        raiserror('BBERR_MEMBERSHIPPLEDGE_INSTALLMENT_SUM',13,1);
    end

    if @NUMBEROFINSTALLMENTS > 150
      raiserror (
          'BBERR_NUMINSTALLMENTS'
          ,13
          ,1
          );

    /* You can only edit unposted pledges here */
    if not exists (
        select 1
        from dbo.REVENUEPOSTED
        where REVENUEPOSTED.ID = @ID
        )
    begin
      if @ISMEMBERSHIPPLEDGE = 0
        raiserror('You cannot edit an unposted pledge.',13,1);
      else
        raiserror('You cannot edit an unposted membership installment plan.',13,1);
    end

    if dbo.UFN_INSTALLMENT_DATESOVERLAP(@INSTALLMENTS) = 1
      raiserror (
          'Installment dates are out of sequence.'
          ,13
          ,1
          );

    if exists (
        select 1
        from dbo.INSTALLMENT
        inner join dbo.UFN_INSTALLMENT_GETINSTALLMENTS_2_FROMITEMLISTXML(@INSTALLMENTS) XMLINST on INSTALLMENT.ID = XMLINST.ID
        where XMLINST.AMOUNT < XMLINST.APPLIED
        )
      raiserror (
          'PLEDGEPAYMENT_INSTALLMENTAPPLIED'
          ,13
          ,1
          );

    if exists (
        select 1
        from dbo.INSTALLMENT
        inner join dbo.UFN_INSTALLMENT_GETINSTALLMENTS_2_FROMITEMLISTXML(@INSTALLMENTS) XMLINST on INSTALLMENT.ID = XMLINST.ID
        where XMLINST.Date < @DATE
        )
    begin
      if @ISMEMBERSHIPPLEDGE = 0
        raiserror('CK_INSTALLMENT_STARTDATEVALID',13,1);
      else
        raiserror('BBERR_INSTALLMENT_MEMBERSHIPPLEDGE_STARTDATEVALID',13,1);
    end

    -- check to see if the revenue record needs to be re-acknowledged                            
    if exists (
        select 1
        from dbo.ACKNOWLEDGEMENTPREFERENCE
        where REACKNOWLEDGEREVENUE = 1
        )
    begin
      -- check to see if amount have changed
      if @REACKNOWLEDGEFIELDCHANGED = 0
        if not exists (
            select 1
            from dbo.FINANCIALTRANSACTION
            where ID = @ID
              and TRANSACTIONAMOUNT = @AMOUNT
            )
          set @REACKNOWLEDGEFIELDCHANGED = 1;

      -- if a field has changed, mark the revenue letters for this record out of date, if necessary
      if @REACKNOWLEDGEFIELDCHANGED = 1
        exec dbo.USP_REVENUELETTER_SETREACKNOWLEDGEMENTS @ID
          ,@CHANGEAGENTID;
    end

    declare @ORIGINALOPPORTUNITYID uniqueidentifier = null;
    declare @ORIGINALDATE date;

    select top 1 @ORIGINALOPPORTUNITYID = RO.OPPORTUNITYID
    from dbo.FINANCIALTRANSACTIONLINEITEM
    left join dbo.REVENUEOPPORTUNITY RO on RO.ID = FINANCIALTRANSACTIONLINEITEM.ID
    where FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = @ID
    and RO.OPPORTUNITYID is not null;

    --get initial value of given anonymously to determine how to handle recognitions
    declare @INITIALGIVENANONYMOUSLY bit;

    select @INITIALGIVENANONYMOUSLY = REVENUE_EXT.GIVENANONYMOUSLY, @ORIGINALDATE = DATE
    from dbo.FINANCIALTRANSACTION
    inner join dbo.REVENUE_EXT on FINANCIALTRANSACTION.ID = REVENUE_EXT.ID
    where FINANCIALTRANSACTION.ID = @ID;

    if @UPDATETRIBUTEOPTION = 1
    begin
      declare @TRIBUTES table (
        TRIBUTEID uniqueidentifier
        ,AMOUNT money
        ,DESIGNATIONID uniqueidentifier
        ,SEQUENCE int
        ,REVENUETRIBUTEID uniqueidentifier
        ,BASECURRENCYID uniqueidentifier
        ,ORGANIZATIONAMOUNT money
        ,ORGANIZATIONEXCHANGERATEID uniqueidentifier
        );

      insert into @TRIBUTES
      select TRIBUTEID
        ,AMOUNT
        ,DESIGNATIONID
        ,SEQUENCE
        ,REVENUETRIBUTEID
        ,BASECURRENCYID
        ,ORGANIZATIONAMOUNT
        ,ORGANIZATIONEXCHANGERATEID
      from dbo.UFN_REVENUE_GETTRIBUTESFORAMOUNT(@ID, @BASEAMOUNT, @BASEEXCHANGERATEID, @EXCHANGERATE);

      update dbo.REVENUETRIBUTE
      set REVENUETRIBUTE.AMOUNT = TRIBUTES.AMOUNT
        ,REVENUETRIBUTE.ORGANIZATIONAMOUNT = TRIBUTES.ORGANIZATIONAMOUNT
        ,REVENUETRIBUTE.ORGANIZATIONEXCHANGERATEID = TRIBUTES.ORGANIZATIONEXCHANGERATEID
        ,REVENUETRIBUTE.CHANGEDBYID = @CHANGEAGENTID
        ,REVENUETRIBUTE.DATECHANGED = @CURRENTDATE
      from dbo.REVENUETRIBUTE
      inner join @TRIBUTES TRIBUTES on TRIBUTES.REVENUETRIBUTEID = REVENUETRIBUTE.ID
    end

    if (
        (@POSTDATE is null)
        and (@POSTSTATUSCODE <> 3)
        )
      raiserror (
          'CK_REVENUE_POSTDATE_REQUIRED'
          ,16
          ,1
          );

    -- Cache the old split and recognition values for recognition updates
    declare @OLDSPLITS xml = dbo.UFN_REVENUE_GETSPLITS_2_TOITEMLISTXML(@ID);
    declare @OLDRECOGNITIONS xml = dbo.UFN_REVENUE_GETRECOGNITIONS_FORREVENUE_TOITEMLISTXML(@ID);

    /* If there was a change to GL related data log an adjustment */
    if @ADJUST = 1
    begin
      if @ADJUSTMENTREASONCODEID is null
        raiserror (
            'BBERR_ADJUSTMENTREASONCODEIDREQUIRED'
            ,13
            ,1
            )

      --JD NOTE: this will reverse posted writeoffs, resulting in no link to revenue splits, but still a link from installmentsplitwriteoff - no link prevents cleanup and delete constraint violation when trying to remove revenue split
      exec dbo.USP_SAVE_ADJUSTMENT @ID
        ,@ADJUSTMENTID output
        ,@CHANGEAGENTID
        ,@CURRENTDATE
        ,@ADJUSTMENTDATE
        ,@ADJUSTMENTPOSTDATE
        ,@ADJUSTMENTREASON
        ,default
       ,@ADJUSTMENTREASONCODEID
        ,@ALTERNATEPOSTSTATUS;--expects the 'other' post status code (0,1,2) not the one passed to this procedure (1,2,3) - because that would make too much sense
    end

    update dbo.FINANCIALTRANSACTION
    set TRANSACTIONAMOUNT = case 
        when @ORGANIZATIONEXCHANGERATEID is null
          and (
            @BASECURRENCYID is null
            or @BASECURRENCYID = @ORGANIZATIONCURRENCYID
            )
          and @BASEEXCHANGERATEID is null
          and (
            @TRANSACTIONCURRENCYID is null
            or @TRANSACTIONCURRENCYID = @ORGANIZATIONCURRENCYID
            )
          and (
            isnull(@ORGANIZATIONAMOUNT, 0) <> isnull(@BASEAMOUNT, 0)
            or isnull(@AMOUNT, 0) <> isnull(@BASEAMOUNT, 0)
            or isnull(@BASEAMOUNT, 0) = 0
            )
          then @BASEAMOUNT
        else isnull(@AMOUNT, 0)
        end
      ,BASEAMOUNT = @BASEAMOUNT
      ,ORGAMOUNT = case 
        when @ORGANIZATIONEXCHANGERATEID is null
          and (
            @BASECURRENCYID is null
            or @BASECURRENCYID = @ORGANIZATIONCURRENCYID
            )
          and @BASEEXCHANGERATEID is null
          and (
            @TRANSACTIONCURRENCYID is null
            or @TRANSACTIONCURRENCYID = @ORGANIZATIONCURRENCYID
            )
          and (
            isnull(@ORGANIZATIONAMOUNT, 0) <> isnull(@BASEAMOUNT, 0)
            or isnull(@AMOUNT, 0) <> isnull(@BASEAMOUNT, 0)
            or isnull(@BASEAMOUNT, 0) = 0
            )
          then @BASEAMOUNT
        else isnull(@ORGANIZATIONAMOUNT, 0)
        end
      ,[DATE] = @DATE
      ,CHANGEDBYID = @CHANGEAGENTID
      ,DATECHANGED = @CURRENTDATE
      ,TRANSACTIONCURRENCYID = @TRANSACTIONCURRENCYID
      ,BASEEXCHANGERATEID = @BASEEXCHANGERATEID
      ,ORGEXCHANGERATEID = @ORGANIZATIONEXCHANGERATEID
      ,POSTDATE = @POSTDATE
      ,POSTSTATUSCODE = @POSTSTATUSCODE
    where ID = @ID

    update dbo.REVENUE_EXT
    -- Pledges should always have their receipt amount 0 but it's possible it could get set to a higher value.  
    -- It needs to be set back to 0 in case the transaction amount is lowered to avoid 
    -- causing a CK_REVENUE_RECEIPTAMOUNTLESSTHANGIFTAMOUNT violation
    set RECEIPTAMOUNT = 0
      ,SOURCECODE = @SOURCECODE
      ,APPEALID = @APPEALID
      ,MAILINGID = @MAILINGID
      ,CHANNELCODEID = @CHANNELCODEID
      ,GIVENANONYMOUSLY = @GIVENANONYMOUSLY
      ,DONOTACKNOWLEDGE = @DONOTACKNOWLEDGE
      ,BENEFITSWAIVED = @BENEFITSWAIVED
      ,CHANGEDBYID = @CHANGEAGENTID
      ,DATECHANGED = @CURRENTDATE
    where ID = @ID

    exec dbo.USP_REVENUEREFERENCE_EDIT @ID
      ,@REFERENCE
      ,@CHANGEAGENTID;

    update dbo.REVENUESCHEDULE
    set FREQUENCYCODE = @FREQUENCYCODE
      ,NUMBEROFINSTALLMENTS = @COUNT
      --,NEXTTRANSACTIONDATE = @NEXTTRANSACTIONDATE --Pledges do not use NEXTTRANSACTIONDATE, STARTDATE surfaces in the UI as next transaction date
      ,PLEDGESUBTYPEID = @PLEDGESUBTYPEID
      ,SENDPLEDGEREMINDER = @SENDPLEDGEREMINDER
      ,CHANGEDBYID = @CHANGEAGENTID
      ,DATECHANGED = @CURRENTDATE
    from dbo.REVENUESCHEDULE
    where REVENUESCHEDULE.ID = @ID;

    -- Set the IDs for any blank entries in the splits XML so that it can associate the declines with gift aid
    -- flag with the generated splits.  Also, pull in the existing value for declines gift aid if it wasn't passed
    -- in the xml.
    --First put the designation splits into a table so I don't repeatedly call UFN_REVENUE_GETSPLITS_2_FROMITEMLISTXML
    declare @SplitsTable table (
      ID uniqueidentifier
      ,Amount money
      ,ApplicationCode tinyint
      ,DesignationID uniqueidentifier
      ,TypeCode tinyint
      ,CategoryCodeID uniqueidentifier
      ,DeclinesGiftAid smallint
      )

    insert into @SplitsTable (
      ID
      ,Amount
      ,ApplicationCode
      ,DesignationID
      ,TypeCode
      ,CategoryCodeID
      ,DeclinesGiftAid
      )
    select case 
        when SPLITS.[ID] is null
     or SPLITS.[ID] = '00000000-0000-0000-0000-000000000000'
          then newid()
        else SPLITS.[ID]
        end [ID]
      ,SPLITS.[AMOUNT]
      ,SPLITS.[APPLICATIONCODE]
      ,SPLITS.[DESIGNATIONID]
      ,SPLITS.[TYPECODE]
      ,case 
        when (@SPLITS.exist('(/SPLITS/ITEM/CATEGORYCODEID)') = 0)
          then @CATEGORYCODEID
        else SPLITS.[CATEGORYCODEID]
        end as [CATEGORYCODEID]
      ,case 
        when SPLITS.[DECLINESGIFTAID] is null
          then REVENUESPLITGIFTAID.DECLINESGIFTAID
        else SPLITS.DECLINESGIFTAID
        end DECLINESGIFTAID
    from dbo.UFN_REVENUE_GETSPLITS_2_FROMITEMLISTXML(@SPLITS) SPLITS
    left join dbo.REVENUESPLITGIFTAID on SPLITS.ID = REVENUESPLITGIFTAID.ID

    --kwb UI doesn't enforce the designation splits being consistent with the installment splits.  This section makes the designation
    -- splits match what has been specified in the installment splits.
    --crr This behavior is inconsistent with unposted edit (Pledge.Edit.#), so opting membership installments out of this. Other revenue types (e.g. pledge) have had this behavior for a while, so keeping it.
    if @ISMEMBERSHIPPLEDGE = 0
    begin
    --Get some values for installment splits that we're going to create that aren't included in the designation splits.  So it's not totally random,
    --use values from the record with the highest amount
    declare @AppCode tinyint
    declare @TypeCode tinyint
    declare @DeclinesGiftAid smallint

    select top 1 @AppCode = ApplicationCode
      ,@TypeCode = TypeCode
      ,@DeclinesGiftAid = DeclinesGiftAid
    from @SplitsTable
    order by Amount desc

    --Now create splits that agree with the designation splits
    set @SPLITS = (
        select isnull(t1.ID, newid()) as ID
          ,v2.AMOUNT
          ,case 
            when t1.DesignationID is null
              then @AppCode
            else t1.ApplicationCode
            end as APPLICATIONCODE
          ,v2.SPLITDESIGNATIONID as DESIGNATIONID
          ,case 
            when t1.DesignationID is null
              then @TypeCode
            else t1.TypeCode
            end as TYPECODE
          ,case 
            when t1.DesignationID is null
              then @CategoryCodeID
            else t1.CategoryCodeID
            end as CATEGORYCODEID
          ,case 
            when t1.DesignationID is null
              then @DeclinesGiftAid
            else t1.DeclinesGiftAid
            end as DECLINESGIFTAID
        from (
          select sum(splitamount) as AMOUNT
            ,SPLITDESIGNATIONID
          from (
            select T2.split.value('(DESIGNATIONID)[1]', 'uniqueidentifier') as 'SPLITDESIGNATIONID'
              ,T2.split.value('(AMOUNT)[1]', 'money') as 'SPLITAMOUNT'
            from @INSTALLMENTS.nodes('/INSTALLMENTS/ITEM') T1(c)
            cross apply T1.c.nodes('./INSTALLMENTSPLITS/ITEM') as T2(split)
            ) v1
          group by SPLITDESIGNATIONID
          ) as v2
        left join @SplitsTable t1 on v2.SPLITDESIGNATIONID = t1.DesignationID
        for xml raw('ITEM')
          ,type
          ,elements
          ,root('SPLITS')
          ,binary BASE64
        );
    end
    else --@ISMEMBERSHIPPLEDGE = 1
    begin
        set @SPLITS = (
            select 
                ID,
                Amount as [AMOUNT],
                ApplicationCode as [APPLICATIONCODE],
                DesignationID as [DESIGNATIONID],
                TypeCode as [TYPECODE],
                CategoryCodeID as [CATEGORYCODEID],
                DeclinesGiftAid as DECLINESGIFTAID
            from @SplitsTable as [SPLITS]
            for xml raw('ITEM'),type,elements,root('SPLITS'),binary BASE64
        );
    end

    --Multicurrency - AdamBu 4/8/10 - Process the splits xml to calculate the base and organization amounts and place them in proper nodes.
    set @SPLITS = dbo.UFN_REVENUESPLIT_CONVERTAMOUNTSINXML(@SPLITS, @BASECURRENCYID, @ORGANIZATIONEXCHANGERATEID, @TRANSACTIONCURRENCYID, @BASEEXCHANGERATEID)


    --kwb Bug 216882
    --exec dbo.USP_REVENUE_GETSPLITS_2_CUSTOMUPDATEFROMXML @ID, @SPLITS, @CHANGEAGENTID, @CURRENTDATE;        
    exec dbo.USP_REVENUE_GETSPLITS_3_CUSTOMUPDATEFROMXML @ID
      ,@SPLITS
      ,@CHANGEAGENTID
      ,@CURRENTDATE
      ,@ADJUSTMENTPOSTDATE;

    -- Update recognition based on user selected option
    exec dbo.USP_REVENUE_UPDATERECOGNITION @ID
      ,@OLDSPLITS
      ,@UPDATERECOGNITIONOPTION
      ,@CHANGEAGENTID
      ,@CURRENTDATE
      ,@OLDRECOGNITIONS;

    -- update campaigns before updating installments so installments will pull the proper campaigns
    -- bug 676001: only update campaigns if designation or opportunity are changed. This conflicts with change made in bug 224396
    if @DESIGNATIONCHANGED = 1 or isnull(@ORIGINALOPPORTUNITYID,@EMPTYGUID) <> isnull(@OPPORTUNITYID,@EMPTYGUID)
    begin
      exec dbo.USP_PLEDGE_CLEARANDADDCAMPAIGNS @REVENUEID = @ID
        ,@OPPORTUNITYID = @OPPORTUNITYID
        ,@CHANGEAGENTID = @CHANGEAGENTID
        ,@CHANGEDATE = @CURRENTDATE
    end

   exec dbo.USP_REVENUE_UPDATESOLICITORS @ID
      ,@CHANGEAGENTID
      ,@CURRENTDATE;

    -- writeoffs without a payment will use pledge reason codes so that reversals fire correctly
    declare @ADJ_DATE datetime = ISNULL(@ADJPAYMENT_DATE,@ADJUSTMENTDATE);
    declare @ADJ_POSTDATE datetime = ISNULL(@ADJPAYMENT_POSTDATE,@ADJUSTMENTPOSTDATE);
    declare @ADJ_REASONCODEID uniqueidentifier = ISNULL(NULLIF(@ADJPAYMENT_REASONCODEID,@EMPTYGUID),@ADJUSTMENTREASONCODEID); 
    declare @ADJ_REASON nvarchar(300) = ISNULL(@ADJPAYMENT_DETAILS,@ADJUSTMENTREASON);

    exec dbo.USP_PLEDGE_UPDATEINSTALLMENT3 @ID
      ,@INSTALLMENTS
      ,@CHANGEAGENTID
      ,@CURRENTDATE
      ,@ADJ_DATE
      ,@ADJ_POSTDATE
      ,@ADJ_REASONCODEID
      ,@ADJ_REASON
      ,@BASECURRENCYID
      ,@ORGANIZATIONEXCHANGERATEID
      ,@TRANSACTIONCURRENCYID
      ,@BASEEXCHANGERATEID;

  --kwb Remove any payments where the adjustment postdate != deposit postdate from their deposit(s)
  update BANKACCOUNTDEPOSITPAYMENT
  set DEPOSITID = null,
  CHANGEDBYID = @CHANGEAGENTID,
  DATECHANGED = @CURRENTDATE
  from dbo.BANKACCOUNTDEPOSITPAYMENT inner join dbo.FINANCIALTRANSACTIONLINEITEM as Payment on BANKACCOUNTDEPOSITPAYMENT.ID = Payment.FINANCIALTRANSACTIONID
  inner join dbo.FINANCIALTRANSACTION as Deposit on BANKACCOUNTDEPOSITPAYMENT.DEPOSITID = Deposit.ID
  inner join dbo.FINANCIALTRANSACTIONLINEITEM as Pledge on Payment.SourceLineItemID = Pledge.ID
  where Pledge.FINANCIALTRANSACTIONID = @ID
  and Payment.POSTDATE != Deposit.POSTDATE
  and Payment.TYPECODE = 0
  and Payment.DELETEDON is null

    if (@ISMEMBERSHIPPLEDGE = 1)
    begin
      if (dbo.UFN_MEMBERSHIPRG_INSTALLMENTSPLITSBALANCE(@ID) = 0)
        raiserror (
            'BBERR_INSTALLMENTSPLITS_MEMBERSHIPPLEDGEBALANCE'
            ,13
            ,10
            );
    end
    else
    begin
      if (dbo.UFN_PLEDGE_INSTALLMENTSPLITSBALANCE(@ID) = 0)
        raiserror (
            'INSTALLMENTSPLITSBALANCE'
            ,13
            ,10
            );
    end

    /* USP_PLEDGE_UPDATEINSTALLMENT3 has to update the writeoffs, so USP_REVENUE_UPDATEWRITEOFFS should not be called
    exec dbo.USP_REVENUE_UPDATEWRITEOFFS @ID
      ,@CHANGEAGENTID
      ,@CURRENTDATE
      ,@ADJPAYMENT_DATE
      ,@ADJPAYMENT_POSTDATE
      ,@ADJPAYMENT_REASONCODEID
      ,@ADJPAYMENT_DETAILS
      ,@CLEARWRITEOFFGLDISTRIBUTION
    */
    -- clear the user-defined gl distributions
    if @CLEARGLDISTRIBUTION = 1
    begin
      -- Clear GL
      delete
      from dbo.REVENUEGLDISTRIBUTION
      where REVENUEID = @ID
        and OUTDATED = 0;

      -- Add new GL distributions
      if @POSTSTATUSCODE <> 3
        exec dbo.USP_SAVE_REVENUEGLDISTRIBUTION @ID
          ,@CHANGEAGENTID
          ,@CURRENTDATE;
    end

    --update benefit gl distributions
    if @CLEARBENEFITSGLDISTRIBUTION = 1 and @ISMEMBERSHIPPLEDGE = 0
    begin

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

      if @POSTSTATUSCODE <> 3
      begin
        exec dbo.USP_SAVE_BENEFITGLDISTRIBUTION_FINANCIALTRANSACTION @ID
          ,@CHANGEAGENTID
          ,@CURRENTDATE;
      end
    end

    exec dbo.USP_PLEDGE_VALIDATE_2 @ID, @ISMEMBERSHIPPLEDGE;

    merge into dbo.PLEDGEINSTALLMENTOPTION as Target
    using (select ID  from dbo.PLEDGEINSTALLMENTOPTION where ID  = @ID)  as Source
    on (Target.ID = Source.ID)
    when matched then
      update set Target.INSTALLMENTAMOUNT = @INSTALLMENTAMOUNT,
      CHANGEDBYID = @CHANGEAGENTID,
      DATECHANGED = @CURRENTDATE
    when not matched by Target then
      insert 
        (ID
        ,INSTALLMENTAMOUNT
        ,SPLITSCHEDULEOPTIONCODE
        ,ADDEDBYID
        ,CHANGEDBYID
        ,DATEADDED
        ,DATECHANGED)
      values (@ID
        ,case @FREQUENCYCODE when 4 then null else @INSTALLMENTAMOUNT end
        ,0 --@INSTALLMENTSPLITSCHEDULEOPTIONCODE
        ,@CHANGEAGENTID
        ,@CHANGEAGENTID
        ,@CURRENTDATE
        ,@CURRENTDATE);      



    --Remove all existing links to the opportunity, including payments.
    if @OPPORTUNITYID is null
    begin
      exec USP_RECORDOPERATION_REVENUEOPPORTUNITYUNLINK @ID, @CHANGEAGENTID;
    end
    else
    begin
      exec dbo.USP_REVENUE_PULLSOLICITORSFROMOPPORTUNITY @ID
        ,@OPPORTUNITYID
        ,@CHANGEAGENTID
        ,@CURRENTDATE

      --No existing opportunity, Add links to opportunity for pledge and all payments.
      if @ORIGINALOPPORTUNITYID is null
      begin
        declare @REVENUEID uniqueidentifier;
        declare ADDSOLICITORS cursor local fast_forward for
        select
         FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID
        from dbo.FINANCIALTRANSACTIONLINEITEM
          left join dbo.FINANCIALTRANSACTIONLINEITEM SOURCE on SOURCE.ID = FINANCIALTRANSACTIONLINEITEM.SOURCELINEITEMID
        where FINANCIALTRANSACTIONLINEITEM.SOURCELINEITEMID = SOURCE.ID
         and SOURCE.FINANCIALTRANSACTIONID = @ID and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null;

        open ADDSOLICITORS;
        begin try
         fetch next from ADDSOLICITORS into @REVENUEID;
         while (@@FETCH_STATUS = 0)
         begin
          exec dbo.USP_REVENUE_PULLSOLICITORSFROMOPPORTUNITY @REVENUEID, @OPPORTUNITYID, @CHANGEAGENTID, @CURRENTDATE

         fetch next from ADDSOLICITORS into @REVENUEID;
         end
         close ADDSOLICITORS;
         deallocate ADDSOLICITORS;
        end try

        begin catch
         close ADDSOLICITORS;

         deallocate ADDSOLICITORS;

         exec dbo.USP_RAISE_ERROR;

         return 1;
        end catch
        exec dbo.USP_DATAFORMTEMPLATE_ADD_REVENUEOPPORTUNITYLINK @REVENUEID = @ID, @OPPORTUNITYID = @OPPORTUNITYID;
      end
      else
      --Opportunity is swapping, update all splits link to new opportunity ID
      begin
       exec USP_REVENUEOPPORTUNITY_UPDATEOPPORTUNITY @ID,@OPPORTUNITYID,@CHANGEAGENTID;
      end

      exec dbo.USP_OPPORTUNITY_UPDATEACCEPTEDSTATUS @OPPORTUNITYID
        ,@DATE
        ,@CHANGEAGENTID
        ,@CURRENTDATE
    end

    --Default Recognition credits based on Given Anonymous flag
    exec USP_PLEDGE_RECOGNITIONCREDIT_DEFAULT @ID, @GIVENANONYMOUSLY, @INITIALGIVENANONYMOUSLY, @CHANGEAGENTID, @CURRENTDATE 

    --Gift Aid is for UK only
    if dbo.UFN_INSTALLEDPRODUCTS_PRODUCTIS('9568A6C2-F7AA-45fd-8F54-21FE9654EE2D') = 1
    begin
      declare @PAYMENTMETHODCODE tinyint;
      declare @CREDITTYPECODEID uniqueidentifier;

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

      if @PAYMENTMETHODCODE = 2
      begin
        select @CREDITTYPECODEID = CREDITCARD.CREDITTYPECODEID
        from dbo.FINANCIALTRANSACTION REVENUE
        inner join dbo.REVENUEPAYMENTMETHOD on REVENUE.ID = REVENUEPAYMENTMETHOD.REVENUEID
        left join dbo.REVENUESCHEDULE on REVENUE.ID = REVENUESCHEDULE.ID
        left join dbo.CREDITCARD on CREDITCARD.ID = REVENUESCHEDULE.CREDITCARDID
        where REVENUE.ID = @ID
          and REVENUE.TYPECODE in (
            0
            ,1
            ,2
            ,3
            ,4
            ,5
            ,6
            ,7
            ,8
            ,9
            )
          and (REVENUE.DELETEDON is null);
      end
      else
        if @PAYMENTMETHODCODE = 3
          and dbo.UFN_PAPERLESSMANDATES_DAYSADVANCEDNOTICE_FORREVENUE(@ID) = - 1
        begin
          --Bug 138736 - AdamBu 2/2/11 - Error on invalid paperless mandate setup.
          raiserror (
              'BBERR_MULTIPLE_PAPERLESSMANDATECONFIGS'
              ,13
              ,1
              );

          return 1;
        end

      declare @SPLITSDECLININGGIFTAID xml

      set @SPLITSDECLININGGIFTAID = (
          select ID as REVENUESPLITID
          from dbo.UFN_REVENUE_GETSPLITS_2_FROMITEMLISTXML(@SPLITS)
          where DECLINESGIFTAID = 1
          for xml raw('ITEM')
            ,type
            ,elements
            ,root('SPLITSDECLININGGIFTAID')
            ,binary BASE64
          );

      exec dbo.USP_GIFTAIDREVENUESPLIT_ADDUPDATESPLITS @ID
        ,@APPEALID
        ,@PAYMENTMETHODCODE
        ,@CREDITTYPECODEID
        ,@CHANGEAGENTID
        ,@DATE
        ,1
        ,@SPLITSDECLININGGIFTAID;--revenue transaction type code of pledge is 1
    end

    if @ADJUST = 1 /*call USP_ADJUSTMENTHISTORY_REVENUE_SAVEHISTORY after the revenue tables are updated */
      exec dbo.USP_ADJUSTMENTHISTORY_REVENUE_SAVEHISTORY @ID
        ,@CHANGEAGENTID
        ,null
        ,@ADJUSTMENTID;

    --Multicurrency - AdamBu 4/12/10 - If we stored an old spot rate earlier, now is the time to
    --    remove it.
    if @OLDSPOTRATE is not null
    begin
      --ThomasGr 10/08/2010 - The currency exchange rate may be used elsewhere and be not able to delete
      begin try
        delete CURRENCYEXCHANGERATE
        where ID = @OLDSPOTRATE
      end try

      begin catch
      end catch
    end
  end try

  begin catch
    --reset CONTEXT_INFO to previous value
    if not @contextCache is null
      set CONTEXT_INFO @contextCache;

    exec dbo.USP_RAISE_ERROR;

    return 1;
  end catch

  --reset CONTEXT_INFO to previous value
  if not @contextCache is null
    set CONTEXT_INFO @contextCache;

  return 0;
end