USP_DATAFORMTEMPLATE_EDIT_PLEDGE_8

CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_PLEDGE_8 (
  @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
  ,@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 @contextCache varbinary(128);
  declare @EMPTYGUID uniqueidentifier = '00000000-0000-0000-0000-000000000000';

  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.FINANCIALTRANSACTION
    where ID = @ID;

    if @FREQUENCYCODE = 4
      exec dbo.USP_REVENUEINSTALLMENT_VALIDATEINSTALLMENTS @INSTALLMENTS, @DATE, @AMOUNT,@ISMEMBERSHIPPLEDGE;

    declare @ALLOWGLDISTRIBUTIONS bit;

    set @ALLOWGLDISTRIBUTIONS = dbo.UFN_PDACCOUNTSYSTEM_ALLOWGLDISTRIBUTIONS(@PDACCOUNTSYSTEMID);

    if @ALLOWGLDISTRIBUTIONS = 0
    begin
      set @POSTSTATUSCODE = 2 -- posted
      set @POSTDATE = null
    end

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

    set @CURRENTDATE = GetDate();
    --JamesWill 2010-01-14 This needs to be AFTER the check for @CHANGEAGENTID is null. Otherwise, it will fail
    --when @CHANGEAGENTID is null.
    --cache current context information
    set @contextCache = CONTEXT_INFO();
    --set CONTEXT_INFO to @CHANGEAGENTID
    set CONTEXT_INFO @CHANGEAGENTID;

    --Business units - AdiSa 6/13/10 - Calculate and store business unit ratios for all new splits given
    --the old splits.
    declare @BUSINESSUNITSRATIO table (
      DESIGNATIONID uniqueidentifier
      ,OVERRIDEBUSINESSUNITS bit
      ,REASON uniqueidentifier
      ,BUSINESSUNITCODEID uniqueidentifier
      ,RATIO float
      )

    insert into @BUSINESSUNITSRATIO
    select REVENUESPLIT_EXT.DESIGNATIONID
      ,REVENUESPLIT_EXT.OVERRIDEBUSINESSUNITS
      ,REVENUESPLIT_EXT.REVENUESPLITBUSINESSUNITOVERRIDECODEID as REASON
      ,REVENUESPLITBUSINESSUNIT.BUSINESSUNITCODEID
      ,isnull(REVENUESPLITBUSINESSUNIT.AMOUNT / nullif(FINANCIALTRANSACTIONLINEITEM.BASEAMOUNT, 0), 0) as RATIO
    from dbo.FINANCIALTRANSACTIONLINEITEM
    inner join dbo.REVENUESPLIT_EXT on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID
    inner join dbo.REVENUESPLITBUSINESSUNIT on REVENUESPLITBUSINESSUNIT.REVENUESPLITID = FINANCIALTRANSACTIONLINEITEM.ID
    where FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = @ID

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

    --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 @OLDSPOTRATEID uniqueidentifier

    if @HADSPOTRATE = 1
      and @RATECHANGED = 1
    begin
      select @OLDSPOTRATEID = BASEEXCHANGERATEID
      from dbo.FINANCIALTRANSACTION
      where ID = @ID
    end

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

    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 exists (
        select 1
        from dbo.REVENUEPOSTED
        where REVENUEPOSTED.ID = @ID
        )
    begin
      if @ISMEMBERSHIPPLEDGE = 0
        raiserror('You cannot edit a posted pledge.',13,1);
      else
        raiserror('You cannot edit a posted 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

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

    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 (@BASEAMOUNT < @TRIBUTEAMOUNT)
      and (@UPDATETRIBUTEOPTION = 0)
      and (@VALIDATETRIBUTES = 1)
    begin
      raiserror (
          'The pledge amount cannot be less than the sum of the tribute amounts applied to this pledge.'
          ,13
          ,1
          )
    end

    declare @DONOTPOST bit;

    set @DONOTPOST = case @POSTSTATUSCODE
        when 2
          then 1
        else 0
        end;

    declare @REACKNOWLEDGEFIELDCHANGED bit;
    declare @CLEARGLDISTRIBUTION bit;
    declare @CLEARWRITEOFFGLDISTRIBUTION bit;
    declare @CLEARBENEFITSGLDISTRIBUTION bit;

    set @REACKNOWLEDGEFIELDCHANGED = 0;
    set @CLEARGLDISTRIBUTION = 0;
    set @CLEARWRITEOFFGLDISTRIBUTION = 0;
    set @CLEARBENEFITSGLDISTRIBUTION = 0;

    -- check to see if designations have changed (this compares both the DESIGNATIONID AND the AMOUNT)
    if dbo.UFN_CHECKDETAIL_SPLITSCHANGED(@ID, @SPLITS) = 1
    begin
      set @CLEARGLDISTRIBUTION = 1;
      set @CLEARWRITEOFFGLDISTRIBUTION = 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 @CLEARGLDISTRIBUTION = 1;
        set @CLEARWRITEOFFGLDISTRIBUTION = 1;
      end
    end

    if (@SPLITS.exist('(/SPLITS/ITEM/CATEGORYCODEID)') = 0)
    begin
      if (
          exists (
            select REVENUECATEGORY.ID
            from dbo.REVENUECATEGORY
            left join dbo.UFN_REVENUE_GETSPLITS_2_FROMITEMLISTXML(@SPLITS) as SPLITS 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 @CLEARGLDISTRIBUTION = 1;
        set @CLEARWRITEOFFGLDISTRIBUTION = 1;
      end
    end
    else
      if (
          exists (
            select REVENUECATEGORY.ID
            from dbo.REVENUECATEGORY
            inner join dbo.FINANCIALTRANSACTIONLINEITEM on REVENUECATEGORY.ID = FINANCIALTRANSACTIONLINEITEM.ID
            where (FINANCIALTRANSACTIONLINEITEM.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)
                  )
                and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null
                and FINANCIALTRANSACTIONLINEITEM.TYPECODE != 1
                )
            )
          )
      begin
        set @CLEARGLDISTRIBUTION = 1;
        set @CLEARWRITEOFFGLDISTRIBUTION = 1;
      end

    -- check to see if amounts, postdate, post status, or exchange rates have changed
    -- changing AppealID can change the calculated account number
    if @CLEARGLDISTRIBUTION = 0
      and @CLEARWRITEOFFGLDISTRIBUTION = 0
      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 @CLEARGLDISTRIBUTION = 1;
        set @CLEARWRITEOFFGLDISTRIBUTION = 1;
      end

    if @CLEARGLDISTRIBUTION = 0
    begin
      if (
          (@POSTSTATUSCODE <> 3)
          and (@POSTSTATUSCODE <> 1)
          )
      begin
        set @CLEARGLDISTRIBUTION = 1;
      end
      else
        if not exists (
            select 1
            from dbo.FINANCIALTRANSACTION
            inner join dbo.REVENUE_EXT on FINANCIALTRANSACTION.ID = REVENUE_EXT.ID
            inner join dbo.PDACCOUNTSYSTEM on FINANCIALTRANSACTION.PDACCOUNTSYSTEMID = PDACCOUNTSYSTEM.ID
            inner join dbo.CURRENCYSET on PDACCOUNTSYSTEM.CURRENCYSETID = CURRENCYSET.ID
            where FINANCIALTRANSACTION.ID = @ID
              and FINANCIALTRANSACTION.TRANSACTIONAMOUNT = @AMOUNT
              and FINANCIALTRANSACTION.POSTDATE = @POSTDATE
              and isnull(REVENUE_EXT.APPEALID, @EMPTYGUID) = isnull(@APPEALID, @EMPTYGUID)
              and isnull(REVENUE_EXT.CHANNELCODEID, @EMPTYGUID) = isnull(@CHANNELCODEID, @EMPTYGUID)
              and FINANCIALTRANSACTION.BASEAMOUNT = @BASEAMOUNT
              and FINANCIALTRANSACTION.ORGAMOUNT = @ORGANIZATIONAMOUNT
              and FINANCIALTRANSACTION.TRANSACTIONCURRENCYID = @TRANSACTIONCURRENCYID
              and CURRENCYSET.BASECURRENCYID = @BASECURRENCYID
            )
        begin
          set @CLEARGLDISTRIBUTION = 1;
        end
    end

    if dbo.UFN_CHECKDETAIL_BENEFITSCHANGEDFORGL(@ID, @BENEFITS) = 1
      and dbo.UFN_INSTALLEDPRODUCTS_PRODUCTIS('0E85C527-E6E9-4C5F-A8E8-105FD0E18FE7') = 1
      set @CLEARBENEFITSGLDISTRIBUTION = 1;

    -- check to see if the revenue record needs to be re-acknowledged
    if (
        coalesce((
            select top 1 REACKNOWLEDGEREVENUE
            from dbo.ACKNOWLEDGEMENTPREFERENCE
            ), 0)
        ) = 1
    begin
      -- check to see if amount have changed
      if @REACKNOWLEDGEFIELDCHANGED = 0
        if not exists (
            select 1
            from dbo.FINANCIALTRANSACTION
            where FINANCIALTRANSACTION.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;
    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;

    --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, @AMOUNT, @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

    update dbo.FINANCIALTRANSACTION
    set [DATE] = @DATE
      ,POSTSTATUSCODE = case @POSTSTATUSCODE
        when 0
          then 2
        when 1
          then 1
        when 2
          then 3
        end
      ,POSTDATE = @POSTDATE
      ,BASEAMOUNT = @BASEAMOUNT
      ,CHANGEDBYID = @CHANGEAGENTID
      ,DATECHANGED = @CURRENTDATE
      ,ORGAMOUNT = @ORGANIZATIONAMOUNT
      ,TRANSACTIONAMOUNT = @AMOUNT
      ,BASEEXCHANGERATEID = @BASEEXCHANGERATEID
      ,ORGEXCHANGERATEID = @ORGANIZATIONEXCHANGERATEID
    where ID = @ID

    update dbo.FINANCIALTRANSACTIONLINEITEM
    set POSTSTATUSCODE = case @POSTSTATUSCODE
        when 0
          then 2
        when 1
          then 1
        when 2
          then 3
        end
      ,POSTDATE = @POSTDATE
      ,CHANGEDBYID = @CHANGEAGENTID
      ,DATECHANGED = @CURRENTDATE
    where POSTSTATUSCODE != 2
      and FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = @ID

    update dbo.REVENUE_EXT
    set SOURCECODE = @SOURCECODE
      ,APPEALID = @APPEALID
      ,BENEFITSWAIVED = @BENEFITSWAIVED
      ,GIVENANONYMOUSLY = @GIVENANONYMOUSLY
      ,MAILINGID = @MAILINGID
      ,CHANNELCODEID = @CHANNELCODEID
      ,DONOTACKNOWLEDGE = @DONOTACKNOWLEDGE
      ,CHANGEDBYID = @CHANGEAGENTID
      ,DATECHANGED = @CURRENTDATE
      ,RECEIPTAMOUNT = 0
    where ID = @ID

    declare @REFERENCECHANGED bit = 0
    exec dbo.USP_REVENUEREFERENCE_EDIT @ID
      ,@REFERENCE
      ,@CHANGEAGENTID
      ,@REFERENCECHANGED output;

    --Set GL to clear if reference is updated
    if @REFERENCECHANGED = 1
        set @CLEARGLDISTRIBUTION = 1

    --Changing PledgeSubType can change the calculated account number
    if @CLEARGLDISTRIBUTION = 0
    begin
      if (
          select isnull(PLEDGESUBTYPEID, @EMPTYGUID)
          from dbo.REVENUESCHEDULE
          where REVENUESCHEDULE.ID = @ID
          ) != isnull(@PLEDGESUBTYPEID, @EMPTYGUID)
        set @CLEARGLDISTRIBUTION = 1
    end

    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;

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

    -- 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.
    set @SPLITS = (
        select case 
            when SPLITS.[ID] is null
              or SPLITS.[ID] = @EMPTYGUID
              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
        for xml raw('ITEM')
          ,type
          ,elements
          ,root('SPLITS')
          ,binary BASE64
        );

    declare @SPLITSAMOUNTMODIFIED table (ID uniqueidentifier);

    insert into @SPLITSAMOUNTMODIFIED (ID)
    select SPLITS.ID
    from dbo.UFN_REVENUE_GETSPLITS_2_FROMITEMLISTXML(@SPLITS) SPLITS
    where not exists (
        select 1
        from dbo.FINANCIALTRANSACTIONLINEITEM
        where FINANCIALTRANSACTIONLINEITEM.ID = SPLITS.ID
          and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null
          and FINANCIALTRANSACTIONLINEITEM.TYPECODE <> 1
          and FINANCIALTRANSACTIONLINEITEM.BASEAMOUNT = SPLITS.AMOUNT
        );

    -- 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);

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

    exec dbo.USP_REVENUE_GETSPLITS_2_CUSTOMUPDATEFROMXML @ID
      ,@SPLITS
      ,@CHANGEAGENTID
      ,@CURRENTDATE;

    -- Update recognition credits 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

    -- bug 137802: Moving solicitor updates to be here b/c USP_PLEDGE_UPDATEINSTALLMENT3 uses the revenuesolicitors table
    -- so it's important to have the records here updated first, esp when amount has changed on the pledge.
    exec dbo.USP_REVENUE_UPDATESOLICITORS_2 @ID, @OPPORTUNITYID, @CHANGEAGENTID, @CURRENTDATE, @OLDSPLITS;

    --declare @ORIGINALWRITEOFFAMOUNTS table (WRITEOFFID uniqueidentifier, TOTAL money);
    --insert into @ORIGINALWRITEOFFAMOUNTS (WRITEOFFID, TOTAL) select FT.ID,sum(IWO.TRANSACTIONAMOUNT) 
    --from dbo.FINANCIALTRANSACTION FT inner join dbo.INSTALLMENTWRITEOFF IWO on FT.ID=IWO.WRITEOFFID
    --where FT.PARENTID = @ID and FT.TYPECODE = 20 and FT.DELETEDON is null GROUP BY FT.ID;
    --Multicurrency - AdamBu 4/8/10 - Pass in multicurrency information so we can convert the amount in the xml 
    --    once installment splits are pulled out of their installments.
    exec dbo.USP_PLEDGE_UPDATEINSTALLMENT3 @ID
      ,@INSTALLMENTS
      ,@CHANGEAGENTID
      ,@CURRENTDATE
      ,@ADJPAYMENT_DATE
      ,@ADJPAYMENT_POSTDATE
      ,@ADJPAYMENT_REASONCODEID
      ,@ADJPAYMENT_DETAILS
      ,@BASECURRENCYID
      ,@ORGANIZATIONEXCHANGERATEID
      ,@TRANSACTIONCURRENCYID
      ,@BASEEXCHANGERATEID
      ,@SPLITS;

    --MMR not sure we still need this?
    --exec dbo.USP_PLEDGE_FIXDEPENDENTSPLITS @ID, @CHANGEAGENTID, @CURRENTDATE;
    if (@BENEFITSWAIVED = 0)
    begin
      -- update benefits
      declare @TOTALBENEFITS xml;

      set @TOTALBENEFITS = dbo.UFN_REVENUEDETAIL_JOINBENEFITS(@BENEFITS, @PERCENTAGEBENEFITS);
      --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);
    end
    else
    begin
      set @TOTALBENEFITS = null;
    end

    exec dbo.USP_REVENUE_GETBENEFITS_4_UPDATEFROMXML @ID
      ,@TOTALBENEFITS
      ,@CHANGEAGENTID
      ,@CURRENTDATE;

    exec dbo.USP_PLEDGE_VALIDATE_2 @ID, @ISMEMBERSHIPPLEDGE;

    /* 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;
      delete JOURNALENTRY
      from dbo.JOURNALENTRY
      inner join dbo.FINANCIALTRANSACTIONLINEITEM on JOURNALENTRY.FINANCIALTRANSACTIONLINEITEMID = FINANCIALTRANSACTIONLINEITEM.ID
      inner join dbo.JOURNALENTRY_EXT on JOURNALENTRY.ID = JOURNALENTRY_EXT.ID
      where FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = @ID
        and FINANCIALTRANSACTIONLINEITEM.POSTSTATUSCODE != 2
        and FINANCIALTRANSACTIONLINEITEM.TYPECODE != 1
        and JOURNALENTRY_EXT.OUTDATED = 0
        and JOURNALENTRY_EXT.TABLENAMECODE = 1;

      if (dbo.UFN_INSTALLEDPRODUCTS_PRODUCTIS('0E85C527-E6E9-4C5F-A8E8-105FD0E18FE7') = 1)
        delete JOURNALENTRY
        from dbo.JOURNALENTRY
        inner join dbo.FINANCIALTRANSACTIONLINEITEM on JOURNALENTRY.FINANCIALTRANSACTIONLINEITEMID = FINANCIALTRANSACTIONLINEITEM.ID
        inner join dbo.JOURNALENTRY_EXT on JOURNALENTRY.ID = JOURNALENTRY_EXT.ID
        where FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = @ID
          and FINANCIALTRANSACTIONLINEITEM.POSTSTATUSCODE != 2
          and JOURNALENTRY_EXT.OUTDATED = 0
          and JOURNALENTRY_EXT.TABLENAMECODE = 5;

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

        if (dbo.UFN_INSTALLEDPRODUCTS_PRODUCTIS('0E85C527-E6E9-4C5F-A8E8-105FD0E18FE7') = 1) and @ISMEMBERSHIPPLEDGE = 0
        begin
          exec dbo.USP_REVENUEDETAIL_SETSENDBENEFITCODE @ID
            ,0

          exec USP_SAVE_BENEFITGLDISTRIBUTION_FINANCIALTRANSACTION @ID
            ,@CHANGEAGENTID
            ,@CURRENTDATE
        end
      end
    end
    else
      if @CLEARBENEFITSGLDISTRIBUTION = 1
      begin
        delete JOURNALENTRY
        from dbo.JOURNALENTRY
        inner join dbo.JOURNALENTRY_EXT on JOURNALENTRY.ID = JOURNALENTRY_EXT.ID
          and JOURNALENTRY_EXT.TABLENAMECODE = 5
        inner join dbo.FINANCIALTRANSACTIONLINEITEM on JOURNALENTRY.FINANCIALTRANSACTIONLINEITEMID = FINANCIALTRANSACTIONLINEITEM.ID
        where JOURNALENTRY_EXT.LOGICALREVENUEID = @ID
          and JOURNALENTRY_EXT.OUTDATED = 0
          and FINANCIALTRANSACTIONLINEITEM.POSTSTATUSCODE != 2
          and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null

        if @POSTSTATUSCODE <> 2
        begin
          exec dbo.USP_REVENUEDETAIL_SETSENDBENEFITCODE @ID
            ,0

          exec USP_SAVE_BENEFITGLDISTRIBUTION_FINANCIALTRANSACTION @ID
            ,@CHANGEAGENTID
            ,@CURRENTDATE
        end
      end

    --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 Givenanonymous 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
        inner join dbo.REVENUEPAYMENTMETHOD on FINANCIALTRANSACTION.ID = REVENUEPAYMENTMETHOD.REVENUEID
        left join dbo.REVENUESCHEDULE on FINANCIALTRANSACTION.ID = REVENUESCHEDULE.ID
        left join dbo.CREDITCARD on CREDITCARD.ID = REVENUESCHEDULE.CREDITCARDID
        where FINANCIALTRANSACTION.ID = @ID
          and FINANCIALTRANSACTION.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

    --Multicurrency - AdamBu 4/12/10 - If we stored an old spot rate earlier, now is the time to
    --    remove it.
    if @OLDSPOTRATEID is not null
      and not exists (
        select 1
        from dbo.PLEDGEORIGINALAMOUNT
        where ID = @ID
          and BASEEXCHANGERATEID = @OLDSPOTRATEID
        )
    begin
      exec dbo.USP_CURRENCYEXCHANGERATE_DELETEBYID_WITHCHANGEAGENTID @OLDSPOTRATEID
        ,@CHANGEAGENTID;
    end

    --Business units - AdiSa 8/12/10 - add adjusted splits back.
    declare @REVENUESPLITBUSINESSUNITID uniqueidentifier;

    declare BUSINESSUNITS cursor local fast_forward
    for
    select REVENUESPLITBUSINESSUNIT.ID
    from dbo.REVENUESPLITBUSINESSUNIT
    inner join dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLITBUSINESSUNIT.REVENUESPLITID
    where FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = @ID

    open BUSINESSUNITS;

    begin try
      fetch next
      from BUSINESSUNITS
      into @REVENUESPLITBUSINESSUNITID

      while (@@FETCH_STATUS = 0)
      begin
        exec dbo.USP_REVENUESPLITBUSINESSUNIT_DELETEBYID_WITHCHANGEAGENTID @REVENUESPLITBUSINESSUNITID
          ,@CHANGEAGENTID;

        fetch next
        from BUSINESSUNITS
        into @REVENUESPLITBUSINESSUNITID
      end

      close BUSINESSUNITS;

      deallocate BUSINESSUNITS;
    end try

    begin catch
      close BUSINESSUNITS;

      deallocate BUSINESSUNITS;

      exec dbo.USP_RAISE_ERROR;

      return 1;
    end catch

    update REVENUESPLIT_EXT
    set OVERRIDEBUSINESSUNITS = BUR.OVERRIDEBUSINESSUNITS
      ,REVENUESPLITBUSINESSUNITOVERRIDECODEID = BUR.REASON
      ,CHANGEDBYID = @CHANGEAGENTID
      ,DATECHANGED = @CURRENTDATE
    from dbo.REVENUESPLIT_EXT
    inner join dbo.FINANCIALTRANSACTIONLINEITEM on REVENUESPLIT_EXT.ID = FINANCIALTRANSACTIONLINEITEM.ID
    inner join @BUSINESSUNITSRATIO as BUR on REVENUESPLIT_EXT.DESIGNATIONID = BUR.DESIGNATIONID
    where FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = @ID
      and BUR.OVERRIDEBUSINESSUNITS = 1
      and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null
      and FINANCIALTRANSACTIONLINEITEM.TYPECODE != 1

    insert into dbo.REVENUESPLITBUSINESSUNIT (
      ID
      ,REVENUESPLITID
      ,BUSINESSUNITCODEID
      ,AMOUNT
      ,BASECURRENCYID
      ,ADDEDBYID
      ,CHANGEDBYID
      ,DATEADDED
      ,DATECHANGED
      )
    select newid()
      ,FINANCIALTRANSACTIONLINEITEM.ID
      ,BUR.BUSINESSUNITCODEID
      ,FINANCIALTRANSACTIONLINEITEM.BASEAMOUNT * BUR.RATIO
      ,CURRENCYSET.BASECURRENCYID
      ,@CHANGEAGENTID
      ,@CHANGEAGENTID
      ,@CURRENTDATE
      ,@CURRENTDATE
    from dbo.FINANCIALTRANSACTIONLINEITEM
    inner join dbo.REVENUESPLIT_EXT on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID
    inner join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = FINANCIALTRANSACTION.ID
    inner join dbo.PDACCOUNTSYSTEM on FINANCIALTRANSACTION.PDACCOUNTSYSTEMID = PDACCOUNTSYSTEM.ID
    inner join dbo.CURRENCYSET on PDACCOUNTSYSTEM.CURRENCYSETID = CURRENCYSET.ID
    inner join @BUSINESSUNITSRATIO BUR on BUR.DESIGNATIONID = REVENUESPLIT_EXT.DESIGNATIONID
    where FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = @ID
      and REVENUESPLIT_EXT.OVERRIDEBUSINESSUNITS = 1
      and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null
      and FINANCIALTRANSACTIONLINEITEM.TYPECODE != 1
      --$0 business units are not allowed, so don't create them
      and BUR.RATIO != 0
      and FINANCIALTRANSACTIONLINEITEM.BASEAMOUNT != 0

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

    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);      

  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