USP_DATAFORMTEMPLATE_ADD_REVENUEUPDATEBATCHCOMMIT

CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_REVENUEUPDATEBATCHCOMMIT
(
  @ID uniqueidentifier = null output,  
  @REVENUEID uniqueidentifier,
  @REVENUESPLITID uniqueidentifier = null,
  @BATCHID uniqueidentifier,
  @CHANGEAGENTID uniqueidentifier,
  @CONSTITUENTID uniqueidentifier = null,
  @TYPECODE tinyint,
  @DATE datetime,
  @AMOUNT money,
  @PAYMENTMETHODCODE tinyint = 0,
  @DONOTACKNOWLEDGE bit = 0,
  @CHECKDATE dbo.UDT_FUZZYDATE = '00000000',
  @CHECKNUMBER nvarchar(20) = '',
  @REFERENCEDATE dbo.UDT_FUZZYDATE = '00000000',
  @REFERENCENUMBER nvarchar(20) = '',
  @CARDHOLDERNAME nvarchar(255) = '',
  @CREDITCARDNUMBER nvarchar(20) = '',
  @CREDITTYPECODEID uniqueidentifier = null,
  @AUTHORIZATIONCODE nvarchar(20) = '',
  @EXPIRESON dbo.UDT_FUZZYDATE = '00000000',
  @ISSUER nvarchar(100) = '',
  @NUMBEROFUNITS decimal(20,3) = 0,
  @SYMBOL nvarchar(25) = '',
  @MEDIANPRICE decimal(19,4) = 0,
  @PROPERTYSUBTYPECODEID uniqueidentifier = null,
  @GIFTINKINDSUBTYPECODEID uniqueidentifier = null,
  @RECEIPTAMOUNT money = 0,
  @DONOTRECEIPT bit = 0,
  @CONSTITUENTACCOUNTID uniqueidentifier = null,
  @SPLITS xml = null,
  @SINGLEDESIGNATIONID uniqueidentifier = null,
  @REVENUESTREAMS xml = null,
  @APPLYTOSHOWNFORCONSTITUENTID uniqueidentifier= null,
  @SEQUENCE int,
  @INSTALLMENTFREQUENCYCODE tinyint = 5,
  @INSTALLMENTSTARTDATE datetime = null,
  @INSTALLMENTENDDATE datetime = null,
  @NUMBEROFINSTALLMENTS int = 1,
  @SOLICITORS xml = null,
  @BENEFITS xml = null,
  @FINDERNUMBER bigint = null,
  @SOURCECODE nvarchar(60) = '',
  @APPEALID uniqueidentifier = null,
  @FINDERNUMBERISVALID bit = 0,
  @USERMODIFIEDBENEFITS bit = 0,
  @BENEFITSWAIVED bit = 0,
  @POSTDATE datetime = null,
  @POSTSTATUSCODE tinyint = 1,
  @SENDPLEDGEREMINDER bit = 1,
  @SALEDATE datetime = null,
  @SALEAMOUNT money = 0,
  @BROKERFEE money = 0,
  @SALEPOSTSTATUSCODE tinyint = 1,
  @SALEPOSTDATE datetime = null,
  @NOTETITLE nvarchar(50) = '',
  @NOTEAUTHORID uniqueidentifier = null,
  @NOTEDATEENTERED datetime = null,
  @NOTETYPECODEID uniqueidentifier = null,
  @NOTETEXTNOTE nvarchar(max) = '',
  @GIVENANONYMOUSLY bit = 0,
  @GIVENANONYMOUSLYDEFAULTEDFORCONSTITUENTID uniqueidentifier = null,
  @USERMODIFIEDRECEIPTAMOUNT bit = 0,
  @PLEDGESUBTYPEID uniqueidentifier = null,
  @REJECTIONCODEID uniqueidentifier = null,
  @CONSTITUENTLOOKUPID uniqueidentifier = null,
  @MAILINGID uniqueidentifier = null,
  @CHANNELCODEID uniqueidentifier = null,
  @INSTALLMENTS xml = null,
  @PAYMENTFORPLEDGEAMOUNT money = 0,
  @RECOGNITIONS xml = null,
  @DIDRECOGNITIONSDEFAULT bit = 0,
  @TRIBUTES xml = null,
  @UNAPPLIEDMATCHINGGIFTSPLITS xml = null,
  @UNAPPLIEDMATCHINGGIFTAMOUNT money = 0,
  @RECEIPTTYPECODE tinyint = 0,
  @NEWCONSTITUENT xml = null,
  @OTHERPAYMENTMETHODCODEID uniqueidentifier = null,
  @LETTERCODEID uniqueidentifier = null,
  @ACKNOWLEDGEDATE datetime = null,
  @REFERENCE nvarchar(255) = null,
  @CATEGORYCODEID uniqueidentifier = null,
  @ACKNOWLEDGEEID uniqueidentifier = null,
  @APPLICATIONINFO nvarchar(60) = null,
  @OTHERTYPECODEID uniqueidentifier = null,
  @OPPORTUNITYID uniqueidentifier = null,
  @DIRECTDEBITRESULTCODE nvarchar(10) = '',
  @LOWPRICE decimal(19,4) = 0,
  @HIGHPRICE decimal(19,4) = 0,
  @NUMBEROFUNITSSOLD decimal(20,3) = 0,
  @USERMODIFIEDNUMBEROFUNITSSOLD bit = 0,
  @CREDITCARDTOKEN uniqueidentifier = null,
  @REJECTIONMESSAGE nvarchar(250) = '',
  -- PARTIALCREDITCARDNUMBER is used to support import and 

  -- credit card - last 4 digits recurring gifts  

  @PARTIALCREDITCARDNUMBER nvarchar(4) = '',
  @STANDINGORDERCONSTITUENTACCOUNTID uniqueidentifier = null,
  @STANDINGORDERREFERENCEDATE dbo.UDT_FUZZYDATE = '00000000',
  @STANDINGORDERREFERENCENUMBER nvarchar(20) = '',
  @STANDINGORDERSETUP bit = 0,
  @STANDINGORDERSETUPDATE datetime = null,
  @TAXDECLARATIONS xml = null,
  @TRANSACTIONID uniqueidentifier = null,
  @ISTRANSIENTCARD bit = null,
  @DECLINESGIFTAID bit = null,
  @DDISOURCECODEID uniqueidentifier = null,
  @DDISOURCEDATE date = null,
  @ISCOVENANT bit = null,
  @AMOUNTFORVAT money = 0,
  @VATTAXRATEID uniqueidentifier = null,
  @VATAMOUNT money = 0,
  @ADDITIONALAPPLICATIONSSTREAM xml = null,
  @REVENUELOOKUPID nvarchar(100) = '',
  @APPLICATIONSOLICITORS xml = null,
  @APPLICATIONRECOGNITIONS xml = null,
  @CURRENTAPPUSERID uniqueidentifier = null,
  @MATCHINGGIFTS xml = null,
  @MGGENERATED bit = null,
  @MGALTERED bit = null,
  @PAYINGPENDINGREVENUEID uniqueidentifier = null,
  @GIFTINKINDITEMNAME nvarchar(100) = '',
  @GIFTINKINDDISPOSITIONCODE tinyint = 0,
  @GIFTINKINDNUMBEROFUNITS int = 0,
  @GIFTINKINDFAIRMARKETVALUE money = 0,
  @DIRECTDEBITISREJECTED bit = 0,
  @PERCENTAGEBENEFITS xml = null,
  @ISGIFTAIDSPONSORSHIP bit = null,
  @LOCKBOXID uniqueidentifier = null,
  @LOCKBOXBATCHNUMBER nvarchar(100) = '',
  @LOCKBOXBATCHSEQUENCE int = 0,
  @PDACCOUNTSYSTEMID uniqueidentifier = null,
  @VALIDATEONLY bit = 0,
  @BATCHNUMBER nvarchar(100),
  @ISADJUSTMENT bit = null,
  @ADJUSTMENTDATE datetime = null,
  @ADJUSTMENTPOSTDATE datetime = null,
  @ADJUSTMENTREASON nvarchar(300) = null,
  @ADJUSTMENTREASONCODEID uniqueidentifier = null,
  @ADJUSTMENTPOSTSTATUSCODE tinyint = null,
  @REVENUEDEVELOPMENTFUNCTIONCODEID uniqueidentifier = null,
  @ORIGINALAPPLICATIONINFO nvarchar(60) = null, 
  @RELOADRECOGNITION bit = 0,
  @RELOADSOLICITORS bit = 0,
  @LETTERS xml = null,
  @APPLICATIONBUSINESSUNITS xml = null,
  @NOTES xml = null,
  @TRANSACTIONCURRENCYID uniqueidentifier = null,
  @BASECURRENCYID uniqueidentifier = null,
  @BASEEXCHANGERATEID uniqueidentifier = null,
  @EXCHANGERATE decimal(20,8) = null,
  @BATCHROWID uniqueidentifier = null,
  @GENERATEREFERENCENUMBER bit = 1,
  @UPDATEMATCHINGGIFTCLAIMS tinyint = 0,
  @UPDATEGIFTFEES bit = null,        --This field is set to null so it can behave differently when field is not included in the batch

  @UPDATETRIBUTES bit = 0,
  @INSTALLMENTSCHEDULESEEDDATE datetime = null,
  @APPLYBYPERCENT bit= 0, -- -- AdiSa: This field is for UI purposes only, but needs to be stored in the database.

  @ADJPAYMENT_DATE datetime = null,
  @ADJPAYMENT_POSTDATE datetime = null,
  @ADJPAYMENT_REASONCODEID uniqueidentifier = null,
  @ADJPAYMENT_DETAILS nvarchar(255) = '',
  @SALE_LOWPRICE decimal(19,4) = 0,
  @SALE_MEDIANPRICE decimal(19,4) = 0,
  @SALE_HIGHPRICE decimal(19,4) = 0,
  @VENDORID nvarchar(50) = ''
)
as
  set nocount on;

  if @ID is null
    set @ID = newid()

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

  declare @CURRENTDATE datetime
  set @CURRENTDATE = getdate()

  declare @ISPAYMENT bit;
  declare @ISPOSTED bit;
  declare @ISORDERAPPLICATION bit;
  declare @HASGIFTAIDSPLITONPENDINGR68PROCESS bit;
  declare @ISMISCELLANEOUSPAYMENT bit;
  declare @ISMIXEDORDERPAYMENT bit;
  declare @GIFTFEE_ENABLED bit;
  declare @ISPLEDGE bit;
  declare @ISPENDING bit;
  declare @ISPLANNEDGIFT bit;
  declare @ISRECURRINGGIFT bit;
  declare @ISMGPLEDGE bit;
  declare @ISAUCTIONDONATION bit;
  declare @ISGRANTAWARD bit;
  declare @PLEDGEBALANCE money;
  declare @SPONSORSHIPID uniqueidentifier;
  declare @PAYMENTTYPECODE tinyint;
  declare @HASSOLDSTOCK bit;
  declare @HASSOLDPROPERTY bit;
  declare @EXTRADATAISPOSTED bit;
  declare @ISDONORCHALLENGE bit;

  declare @APPLICATIONSTREAM xml;
  declare @CAMPAIGNSTREAM xml;
  declare @PREVIOUSCONSTITUENTID uniqueidentifier;
  declare @PREVIOUSAMOUNT money;
  declare @PREVIOUSDATE datetime;
  declare @NEXTINSTALLMENTID uniqueidentifier;
  declare @LOCKBOXCHANGED bit;
  declare @PREVIOUSREVENUELOOKUPID nvarchar(100);
  declare @PREVIOUSRECEIPTTYPECODE tinyint;

  declare @BASEAMOUNT money;
  declare @ORGANIZATIONAMOUNT money;
  declare @ORGANIZATIONCURRENCYID uniqueidentifier;
  declare @ORGANIZATIONEXCHANGERATEID uniqueidentifier;

  declare @TRIBUTEAMOUNT money;

  declare @SHOULDUPDATEGIFTFEE tinyint;
  set @SHOULDUPDATEGIFTFEE = 0;

  -- recognitions temp table

  declare @RECOGNITIONSTABLE table
  (
    REVENUESPLITID uniqueidentifier,
    APPLICATIONID uniqueidentifier,
    RECOGNITIONS xml
  )

  -- solicitors temp table

  declare @SOLICITORSTABLE table
  (
    REVENUESPLITID uniqueidentifier,
    APPLICATIONID uniqueidentifier,
    SOLICITORS xml
  )

  -- fields declared below b/c not available in batch yet but used in edit procs.

  declare @HADSPOTRATE bit;
  declare @RATECHANGED bit;

  begin try    
    set @ORGANIZATIONCURRENCYID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();
    set @ORGANIZATIONEXCHANGERATEID = dbo.UFN_CURRENCYEXCHANGERATE_GETLATESTORGANIZATIONRATE(@BASECURRENCYID, @ORGANIZATIONCURRENCYID, @DATE, null, @TRANSACTIONCURRENCYID);
    exec dbo.USP_CURRENCY_GETCURRENCYVALUES
      @AMOUNT,
      @DATE,
      @BASECURRENCYID,
      @BASEEXCHANGERATEID,
      @TRANSACTIONCURRENCYID output,
      @BASEAMOUNT output,
      @ORGANIZATIONCURRENCYID output,
      @ORGANIZATIONAMOUNT output,
      @ORGANIZATIONEXCHANGERATEID output,
      1;

    declare @BATCHOWNERID uniqueidentifier
    select 
      @BATCHOWNERID = BATCH.APPUSERID,
      @BATCHID = BATCH.ID
    from 
      dbo.BATCH
      inner join dbo.BATCHREVENUE on BATCH.ID = BATCHREVENUE.BATCHID
    where 
      BATCHREVENUE.ID = @BATCHROWID

  -- Validate that multiple batch rows are not allowed for single revenue record.

    declare @BATCHROWIDCOUNT integer;
    select @BATCHROWIDCOUNT = count(ID) from BATCHREVENUE 
    where BATCHID = @BATCHID and REVENUEID = @REVENUEID;

    if @BATCHROWIDCOUNT > 1
       raiserror('BBERR_MULTIPLEBATCHROWSNOTALLOWEDFORSINGLEREVENUERECORD',13,1);

    declare @CONSTITUENTSECURITY bit = (select CONSTITUENTSECURITY from dbo.REVENUEBATCHCONSTITUENTSECURITY); 
    if @CONSTITUENTSECURITY = 1
    begin
      if dbo.UFN_CONSTITUENT_CANAPPUSERMODIFY(@CONSTITUENTID, @BATCHOWNERID) = 0
      begin
        raiserror('BBERR_CONSTITUENTSECURITY',13,1);
      end
    end

    if @TYPECODE = 0  -- payment

    begin
      if @PAYMENTMETHODCODE = 3 --Direct Debit

      begin
        if @CONSTITUENTACCOUNTID is null
          raiserror('BBERR_DEBIT_CONSTITUENTACCOUNTIDREQUIRED', 13, 1);
      end

      if @PAYMENTMETHODCODE = 4 --Stock

      begin
        -- Validate that the low/median/high price per share values aren't negative

        if @LOWPRICE < 0
          raiserror('BBERR_LOWPRICEPERSHARENEGATIVE', 13, 1)

        if @MEDIANPRICE < 0
          raiserror('BBERR_MEDIANPRICEPERSHARENEGATIVE', 13, 1)

        if @HIGHPRICE < 0
          raiserror('BBERR_HIGHPRICEPERSHARENEGATIVE', 13, 1)

        if @NUMBEROFUNITSSOLD > @NUMBEROFUNITS
          raiserror('BBERR_NUMBEROFUNITSREMAINING', 13, 1)

        if @NUMBEROFUNITSSOLD = 0 and (@USERMODIFIEDNUMBEROFUNITSSOLD = 1 or @NUMBEROFUNITS <= 0)
          raiserror('BBERR_NUMBEROFUNITS', 13, 1)

        -- Validate that if any of the sold fields are set, the required sale field is set.  For @NUMBEROFUNITSSOLD, verify it isn't 

        -- the same value as @NUMBEROFUNITS since it gets defaulted to that amount all the time.               

        if (@SALEDATE is not null or @SALEAMOUNT <> 0 or @BROKERFEE <> 0 or @SALEPOSTDATE is not null or @SALEPOSTSTATUSCODE <> 1 or (@NUMBEROFUNITSSOLD > 0 and @NUMBEROFUNITSSOLD <> @NUMBEROFUNITS)) 
        begin
          if @SALEDATE is null 
            raiserror('ERR_STOCK_SALEDATEREQUIRED.', 13, 1)                
          if @SALEPOSTSTATUSCODE is null 
            raiserror('ERR_STOCK_SALEGLPOSTSTATUSREQUIRED.', 13, 1)

          if (@SALEPOSTDATE is null and @SALEPOSTSTATUSCODE = 1)
            raiserror('ERR_STOCK_SALEGLPOSTDATEREQUIRED.', 13, 1)          
        end
      end

      if @PAYMENTMETHODCODE = 5 --Property

      begin
        if (@SALEDATE is not null or @SALEAMOUNT <> 0 or @BROKERFEE <> 0 or @SALEPOSTDATE is not null or @SALEPOSTSTATUSCODE <> 1) 
        begin
          if @SALEDATE is null 
            raiserror('ERR_PROPERTY_SALEDATEREQUIRED.', 13, 1)                    

          if @SALEPOSTDATE is null and @SALEPOSTSTATUSCODE = 1
            raiserror('ERR_PROPERTY_SALEGLPOSTDATEREQUIRED.', 13, 1)                    
        end
      end

      if @PAYMENTMETHODCODE = 6 --Gift-in-Kind

      begin
        if @GIFTINKINDITEMNAME is null or @GIFTINKINDITEMNAME = ''
          raiserror('BBERR_GIFTINKINDITEMNAMEREQUIRED', 13, 1)

        if @GIFTINKINDDISPOSITIONCODE is null
          raiserror('BBERR_GIFTINKINDDISPOSITIONCODEREQUIRED', 13, 1)

        if @GIFTINKINDNUMBEROFUNITS < 0
          raiserror('BBERR_GIFTINKINDNUMBEROFUNITSNEGATIVE', 13, 1)

        if @GIFTINKINDFAIRMARKETVALUE < 0
          raiserror('BBERR_GIFTINKINDFAIRMARKETVALUENEGATIVE', 13, 1)

        declare @GIFTINKINDVALUE as money = 0;
        -- Catch any overflows

        begin try
          set @GIFTINKINDVALUE = @GIFTINKINDNUMBEROFUNITS * @GIFTINKINDFAIRMARKETVALUE
        end try
        begin catch
          raiserror('BBERR_REVENUEAMOUNTNOTEQUALGIFTINKINDAMOUNT', 13, 1)
          return 1
        end catch

        if (@GIFTINKINDNUMBEROFUNITS > 0 or @GIFTINKINDFAIRMARKETVALUE > 0) and (@AMOUNT <> @GIFTINKINDVALUE)
        begin
          raiserror('BBERR_REVENUEAMOUNTNOTEQUALGIFTINKINDAMOUNT', 13, 1)
          return 1
        end 
      end --Gift-in-Kind


      if @PAYMENTMETHODCODE = 9 -- None

        raiserror('BBERR_PAYMENTMETHODCODE_NONE.', 13, 1)

      if @PAYMENTMETHODCODE = 255 or @PAYMENTMETHODCODE is null
        raiserror('BBERR_PAYMENTMETHODCODE_BLANK', 13, 1)

      if @PAYMENTMETHODCODE = 10 --Other

      begin
        if @OTHERPAYMENTMETHODCODEID is null
          raiserror('BBERR_PAYMENTMETHODCODE_OTHER', 13, 1);
      end

      if @PAYMENTMETHODCODE = 11 --Standing order

      begin
        if @CONSTITUENTACCOUNTID is null
          raiserror('BBERR_STANDINGORDER_CONSTITUENTACCOUNTIDREQUIRED', 13, 1);
      end

      if @BENEFITSWAIVED = 0
      begin
        if (@BENEFITS is not null) or (@PERCENTAGEBENEFITS is not null)
          exec dbo.USP_REVENUE_BENEFITS_VALIDATEGLMAPPINGS @TYPECODE, @PDAccountSystemID, @CURRENTAPPUSERID
      end
      else
      begin
        set @BENEFITS = null
        set @PERCENTAGEBENEFITS = null
      end

      if @TRIBUTES is not null
      begin
        select @TRIBUTEAMOUNT = sum(AMOUNT) from dbo.UFN_REVENUE_GETTRIBUTES_2_FROMITEMLISTXML(@TRIBUTES)
        -- do not allow the gift amount to be adjusted less than the applied tribute amount. Note that tributes are in base currency

        if (@TRIBUTEAMOUNT is not null) and (@BASEAMOUNT < @TRIBUTEAMOUNT)
        begin
          raiserror('BBERR_TRIBUTEAMOUNT', 13, 1)
        end
      end
    end -- type payment


    else if @TYPECODE = 1 -- Pledge

    begin
      if @PAYMENTMETHODCODE = 255 or @PAYMENTMETHODCODE is null
        set @PAYMENTMETHODCODE = 9;

      if not @PAYMENTMETHODCODE in (2, 3, 9, 11)
      begin
        if dbo.UFN_INSTALLEDPRODUCTS_PRODUCTIS('9568A6C2-F7AA-45fd-8F54-21FE9654EE2D') = 1
          raiserror('ERR_UK_PLEDGE_INVALIDPAYMENTMETHODCODE', 13, 1);
      else
        raiserror('ERR_PLEDGE_INVALIDPAYMENTMETHODCODE2', 13, 1);
      end

      if @PAYMENTMETHODCODE = 3 --Direct Debit

      begin
        if @CONSTITUENTACCOUNTID is null
          raiserror('ERR_PLEDGE_DEBITACCOUNTREQUIRED', 13, 1);
      end

      if @PAYMENTMETHODCODE = 11 --Standing order

      begin
     if @CONSTITUENTACCOUNTID is null
          raiserror('ERR_PLEDGE_STANDINGORDERACCOUNTREQUIRED', 13, 1);
      end

      if @INSTALLMENTSTARTDATE is null
        set @INSTALLMENTSTARTDATE = @DATE;

      if @INSTALLMENTFREQUENCYCODE not in (0,1,2,3,4,5,7,8)
        raiserror('ERR_VALIDATE_INSTALLMENTFREQUENCY', 13, 1);

      -- Validate installments

      exec dbo.USP_REVENUEINSTALLMENT_VALIDATEINSTALLMENTS @INSTALLMENTS, @DATE, @AMOUNT;

      if @INSTALLMENTSTARTDATE < @DATE
        raiserror('ERR_VALIDATE_INSTALLMENTSTARTDATE', 13, 1);

      if @INSTALLMENTENDDATE < @INSTALLMENTSTARTDATE
        raiserror('ERR_VALIDATE_INSTALLMENTENDDATE', 13, 1);

      if @PAYMENTFORPLEDGEAMOUNT > @AMOUNT
        raiserror('ERR_PLEDGE_INVALIDPLEDGEAMOUNT', 13, 1);

      select @TRIBUTEAMOUNT = sum(AMOUNT) from dbo.UFN_REVENUE_GETTRIBUTES_2_FROMITEMLISTXML(@TRIBUTES)
      -- do not allow the gift amount to be adjusted less than the applied tribute amount. Note that tributes are in base currency

      if (@TRIBUTEAMOUNT is not null) and (@BASEAMOUNT < @TRIBUTEAMOUNT)
      begin
        raiserror('BBERR_TRIBUTEAMOUNT', 13, 1)
      end

      -- Validate payment adjust fields

      if dbo.UFN_PLEDGEHASPOSTEDPAYMENTS(@REVENUEID) = 1 
      begin
        -- The pledge amount shouldn't be able to be reduced to less than the applied amount and increasing 

        -- the pledge amount doesn't affect the payments so the only amount change that should trigger a change

        -- to a payment is when relative distribution for a designation is changed.  As a result, changes to 

        -- the percent should be checked rather than changes to the actual amount.  This prevents unnecessary

        -- adjustments from being created for a payment.

        if 
          -- A posted payment on an installment has been edited

          exists( 
            select 1
            from dbo.INSTALLMENTSPLITPAYMENT
            inner join dbo.REVENUESPLIT on REVENUESPLIT.ID = INSTALLMENTSPLITPAYMENT.PAYMENTID
            inner join dbo.REVENUEPOSTED on REVENUEPOSTED.ID = REVENUESPLIT.REVENUEID
            inner join dbo.INSTALLMENTSPLIT on INSTALLMENTSPLIT.ID = INSTALLMENTSPLITPAYMENT.INSTALLMENTSPLITID
            inner join dbo.INSTALLMENT on INSTALLMENTSPLIT.INSTALLMENTID = INSTALLMENT.ID
            left join
            (
              -- Using a derived table so the BATCHREVENUEID filter is applied before the BATCHREVENUEINSTALLMENT tables are left-joined

              select
                BATCHREVENUEINSTALLMENTSPLIT.DESIGNATIONID,
                BATCHREVENUEINSTALLMENTSPLIT.INSTALLMENTSPLITID,
                BATCHREVENUEINSTALLMENT.AMOUNT as INSTALLMENTAMOUNT,
                BATCHREVENUEINSTALLMENTSPLIT.AMOUNT as INSTALLMENTSPLITAMOUNT
              from dbo.BATCHREVENUEINSTALLMENT
              inner join dbo.BATCHREVENUEINSTALLMENTSPLIT on BATCHREVENUEINSTALLMENT.ID = BATCHREVENUEINSTALLMENTSPLIT.BATCHREVENUEINSTALLMENTID
              where
                BATCHREVENUEINSTALLMENT.BATCHREVENUEID = @BATCHROWID
            ) as BATCHREVENUEINSTALLMENTWITHINSTALLMENTSPLIT on BATCHREVENUEINSTALLMENTWITHINSTALLMENTSPLIT.INSTALLMENTSPLITID = INSTALLMENTSPLIT.ID
            where
              INSTALLMENTSPLITPAYMENT.PLEDGEID = @REVENUEID
              and 
              (
                (
                  case
                    when INSTALLMENT.AMOUNT <> 0 then INSTALLMENTSPLIT.AMOUNT / INSTALLMENT.AMOUNT 
                    else 0 
                  end 
                    <> 
                  case
                    when BATCHREVENUEINSTALLMENTWITHINSTALLMENTSPLIT.INSTALLMENTAMOUNT <> 0 then BATCHREVENUEINSTALLMENTWITHINSTALLMENTSPLIT.INSTALLMENTSPLITAMOUNT / BATCHREVENUEINSTALLMENTWITHINSTALLMENTSPLIT.INSTALLMENTAMOUNT
                    else 0
                  end
                  -- If the new or original installment amount is 0, then either the payment applied amount is 0

                  -- or later validation will ensure that the payment amount must be 0.  If the payment amount is 0

                  -- then changing the relative distribution amounts won't require the payment to be adjusted.

                  and INSTALLMENT.AMOUNT <> 0
                  and BATCHREVENUEINSTALLMENTWITHINSTALLMENTSPLIT.INSTALLMENTAMOUNT <> 0
                )
                or INSTALLMENTSPLIT.DESIGNATIONID <> BATCHREVENUEINSTALLMENTWITHINSTALLMENTSPLIT.DESIGNATIONID
                or BATCHREVENUEINSTALLMENTWITHINSTALLMENTSPLIT.DESIGNATIONID is null
              )
            )
            or
            -- Check if the number of installment splits has changed for an installment that has already had payments applied to it and the 

            -- payment has been posted.  The previous check won't handle increasing the number of designations for an installment split when the 

            -- installment amount is $0.

            exists
            ( 
              select 1 from
              (
                select count(*) as INSTALLMENTSPLITCOUNT, INSTALLMENTSPLIT.INSTALLMENTID 
                from dbo.INSTALLMENT
                inner join dbo.INSTALLMENTSPLIT on INSTALLMENTSPLIT.INSTALLMENTID = INSTALLMENT.ID
                where 
                  INSTALLMENT.REVENUEID = @REVENUEID and
                  -- Has posted payment applied to the installment split

                  exists
                  (
                    select 1
                    from dbo.INSTALLMENTPAYMENT
                    inner join dbo.REVENUESPLIT on INSTALLMENTPAYMENT.PAYMENTID = REVENUESPLIT.ID
                    inner join dbo.REVENUEPOSTED on REVENUESPLIT.REVENUEID =  REVENUEPOSTED.ID
                    where
                      INSTALLMENTPAYMENT.INSTALLMENTID = INSTALLMENT.ID
                  )
                group by INSTALLMENTSPLIT.INSTALLMENTID
              ) as EXISTINGINSTALLMENTSPLITCOUNT
              inner join
              (
                select count(*) as INSTALLMENTSPLITCOUNT, BATCHREVENUEINSTALLMENT.INSTALLMENTID
                from dbo.BATCHREVENUEINSTALLMENT
                inner join dbo.BATCHREVENUEINSTALLMENTSPLIT on BATCHREVENUEINSTALLMENT.ID = BATCHREVENUEINSTALLMENTSPLIT.BATCHREVENUEINSTALLMENTID
                where BATCHREVENUEINSTALLMENT.BATCHREVENUEID = @BATCHROWID
                group by BATCHREVENUEINSTALLMENT.INSTALLMENTID
              ) as NEWINSTALLMENTSPLITCOUNT on EXISTINGINSTALLMENTSPLITCOUNT.INSTALLMENTID = NEWINSTALLMENTSPLITCOUNT.INSTALLMENTID
              where EXISTINGINSTALLMENTSPLITCOUNT.INSTALLMENTSPLITCOUNT <>  NEWINSTALLMENTSPLITCOUNT.INSTALLMENTSPLITCOUNT
            )
         or 
            exists
            (
                select  1
                from    dbo.INSTALLMENTSPLITPAYMENT
                inner join REVENUECATEGORY on REVENUECATEGORY.ID = INSTALLMENTSPLITPAYMENT.PAYMENTID
                where INSTALLMENTSPLITPAYMENT.PLEDGEID = @REVENUEID and REVENUECATEGORY.GLREVENUECATEGORYMAPPINGID <> @CATEGORYCODEID
            )
        begin
          if @ADJPAYMENT_REASONCODEID is null
            raiserror('BBERR_ADJPAYMENT_REASONCODEID_REQUIRED', 13, 1);
          if @ADJPAYMENT_DATE is null
            raiserror('BBERR_ADJPAYMENT_DATE_REQUIRED', 13, 1);
          if @ADJPAYMENT_POSTDATE is null
            raiserror('BBERR_ADJPAYMENT_POSTDATE_REQUIRED', 13, 1);
        end
     end
     else -- If the pledge has no posted payments, clear the adjust fields

          select
            @ADJPAYMENT_DATE = null,
            @ADJPAYMENT_POSTDATE = null,
            @ADJPAYMENT_REASONCODEID = null,
            @ADJPAYMENT_DETAILS = '';
     end  --type pledge



    else if @TYPECODE = 3 -- recurring gift

    begin
      if not @SPLITS is null
        exec dbo.USP_REVENUESPLIT_VALIDATESPLITS @SPLITS, @AMOUNT, @TYPECODE, null, @TRANSACTIONCURRENCYID, 0;
      else
        if @SINGLEDESIGNATIONID is null
          raiserror('ERR_SINGLEDESIGNATION_REQUIRED', 13, 1);

      declare @NEXTTRANSACTIONDATE datetime;

      if @INSTALLMENTSTARTDATE is null
        set @INSTALLMENTSTARTDATE = @DATE;

      if @INSTALLMENTFREQUENCYCODE = 4 or @INSTALLMENTFREQUENCYCODE = 5
        raiserror('ERR_VALIDATE_INSTALLMENTFREQUENCY', 13, 1);

      if @PAYMENTMETHODCODE not in (0, 1, 2, 3, 9, 10, 11, 98, 101, 102)
      begin
        if dbo.UFN_INSTALLEDPRODUCTS_PRODUCTIS('9568A6C2-F7AA-45fd-8F54-21FE9654EE2D') = 1
          raiserror('ERR_UK_RECGIFT_INVALIDPAYMENTMETHODCODE', 13, 1);
      else
        raiserror('ERR_RECGIFT_INVALIDPAYMENTMETHODCODE2', 13, 1);
      end

      if @PAYMENTMETHODCODE = 3 --Direct Debit

      begin
        if @CONSTITUENTACCOUNTID is null
          raiserror('ERR_RECGIFT_DEBITACCOUNTREQUIRED', 13, 1);
      end

      if @PAYMENTMETHODCODE = 10 --Other

      begin
        if @OTHERPAYMENTMETHODCODEID is null
          raiserror('ERR_RECGIFT_OTHERMETHODREQUIRED', 13, 1);
      end

      if @PAYMENTMETHODCODE = 11 --Standing order

      begin
        if @CONSTITUENTACCOUNTID is null
          raiserror('ERR_RECGIFT_STANDINGORDERACCOUNTREQUIRED', 13, 1);
      end

      if @INSTALLMENTSTARTDATE < @DATE
        raiserror('ERR_VALIDATE_INSTALLMENTSTARTDATE', 13, 1);

      if @INSTALLMENTSCHEDULESEEDDATE < @INSTALLMENTSTARTDATE
        raiserror('ERR_VALIDATE_INSTALLMENTSCHEDULESEEDDATE', 13, 1);

      if @INSTALLMENTENDDATE < @INSTALLMENTSTARTDATE
        raiserror('ERR_VALIDATE_INSTALLMENTENDDATE', 13, 1);    
    end -- recurring gift


    -- Ensure that sold fixed assets don't have payment method changed

    if @PAYMENTMETHODCODE not in (4, 5, 6) --stock, property, gift-in-kind

    begin
      -- sold stock, sold property, or sold gift-in-kind

      if exists (select 1 from dbo.FINANCIALTRANSACTION
                 where FINANCIALTRANSACTION.PARENTID = @REVENUEID
                 and FINANCIALTRANSACTION.TYPECODE in (21,22,27))
        raiserror('BBERR_VALIDATE_FIXEDASSETSCHANGED', 13, 1);
    end

    if @FINDERNUMBER is null
      set @FINDERNUMBER = 0;


    /* Validate all marketing data and dependent fields */
    exec dbo.[USP_REVENUEBATCH_VALIDATEMARKETINGDATA]
      @FINDERNUMBER = @FINDERNUMBER,
      @SOURCECODE = @SOURCECODE,
      @MAILINGID = @MAILINGID,
      @APPEALID = @APPEALID,
      @CONSTITUENTID = @CONSTITUENTID,
      @BATCHID = @BATCHID;


    if (@DIRECTDEBITISREJECTED = 1) and (@AMOUNT > 0)
    begin
      raiserror('ERR_VALIDATE_DIRECTDEBITISREJECTED', 13, 1);
    end

    declare @CONSTITUENTEXISTS bit
    if exists(select ID from dbo.CONSTITUENT where ID = @CONSTITUENTID) 
      set @CONSTITUENTEXISTS = 1
    else
      set @CONSTITUENTEXISTS = 0

    -- Validate constituent fields if the constituent hasn't been created yet

    if @CONSTITUENTEXISTS = 0
    begin
      exec dbo.USP_REVENUEBATCH_VALIDATENEWCONSTITUENT @REVENUEBATCHCONSTITUENTID = @CONSTITUENTID, @ISDONOR = 1, @BATCHROWID = @BATCHROWID;

      declare @BATCHCONSTITID uniqueidentifier;
      set @BATCHCONSTITID = @CONSTITUENTID;

      -- Only pass recognitions into USP_REVENUEBATCH_CONSTITUENT_ADD if there is atleast one row

      -- so an empty set isn't constantly unserialized and then reserialized

      declare @RECOGNITIONSTOPASS xml
      if exists (select 1 from dbo.UFN_REVENUEBATCH_GETRECOGNITIONS_FROMITEMLISTXML(@RECOGNITIONS) for xml raw('ITEM'), type, elements, root('RECOGNITIONS'), binary base64)
        set @RECOGNITIONSTOPASS = @RECOGNITIONS
      else
        set @RECOGNITIONSTOPASS = null

      --add new constituent

      exec dbo.USP_REVENUEBATCH_CONSTITUENT_ADD 
        @CONSTITUENTID OUTPUT, 
        @CHANGEAGENTID, 
        @BATCHCONSTITID, 
        @CONSTITUENTACCOUNTID, 
        @CONSTITUENTACCOUNTID output,
        @CURRENTRECOGNITIONS = @RECOGNITIONSTOPASS,
        @UPDATEDRECOGNITIONS = @RECOGNITIONS output,
        @UPDATEDAPPLICATIONRECOGNITIONS = @APPLICATIONRECOGNITIONS output;

      if @FINDERNUMBER > 0
        exec dbo.USP_REVENUEBATCH_CONSTITUENTAPPEAL_ADD 
          @CONSTITUENTID, 
          @CHANGEAGENTID, 
          @BATCHCONSTITID,
          @FINDERNUMBER;

      --delete the temporary batch version of the constituent

      exec dbo.USP_REVENUEBATCH_CONSTITUENT_DELETE @BATCHREVENUECONSTITUENTID = @BATCHCONSTITID, @CHANGEAGENTID = @CHANGEAGENTID
    end;

    declare @ISORGANIZATION bit
    select @ISORGANIZATION = ISORGANIZATION
    from dbo.CONSTITUENT where ID = @CONSTITUENTID

    select  
      @PREVIOUSCONSTITUENTID = REVENUE.CONSTITUENTID, 
      @PREVIOUSAMOUNT = REVENUE.AMOUNT,
      --@TRANSACTIONCURRENCYID = REVENUE.TRANSACTIONCURRENCYID,

      --@BASECURRENCYID = REVENUE.BASECURRENCYID, 

      --@BASEEXCHANGERATEID = REVENUE.BASEEXCHANGERATEID,

      @PREVIOUSDATE = REVENUE.DATE,
      @PREVIOUSREVENUELOOKUPID = REVENUE.LOOKUPID,
      @HADSPOTRATE =  
        case
          when CURRENCYEXCHANGERATE.TYPECODE = 2
            then 1
          else 0
        end,
      @RATECHANGED = 0, 
      @PREVIOUSRECEIPTTYPECODE = RECEIPTTYPECODE
    from 
      dbo.REVENUE
      left join dbo.CURRENCYEXCHANGERATE on CURRENCYEXCHANGERATE.ID = REVENUE.BASEEXCHANGERATEID
    where 
      REVENUE.ID = @REVENUEID

    set @LOCKBOXCHANGED = (
            select(
              case when REVENUELOCKBOX.LOCKBOXID <> @LOCKBOXID then 
                1
              when REVENUELOCKBOX.BATCHNUMBER <> @LOCKBOXBATCHNUMBER then 
                1
              when REVENUELOCKBOX.BATCHSEQUENCE <> @LOCKBOXBATCHSEQUENCE then 
                1
              else 
                0
              end)
            from 
              dbo.REVENUELOCKBOX
            where 
              REVENUELOCKBOX.ID = @REVENUEID)

    -- If the system has set that households can't be donors, verify that constituent isn't a household

    if dbo.UFN_INSTALLATIONINFO_GETHOUSEHOLDSCANBEDONORS() = 0 and dbo.UFN_CONSTITUENT_ISHOUSEHOLD(@CONSTITUENTID) = 1
      raiserror('ERR_CONSTITUENT_HOUSEHOLD', 13, 1);

    if @AMOUNT < 0 
      raiserror('ERR_AMOUNT_NEGATIVEAMOUNT', 13, 1);

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

    if @AMOUNTFORVAT = 0 and (@VATTAXRATEID is not null or @VATAMOUNT <> 0)
      raiserror('INVALIDAMOUNTFORVAT_TAXRATEORVATAMOUNT', 13, 1);

    if @AMOUNTFORVAT <> 0 and @AMOUNTFORVAT > @AMOUNT
      raiserror('INVALIDAMOUNTFORVAT_REVENUEAMOUNT', 13, 1);

    if (@POSTSTATUSCODE = 1) and (@VALIDATEONLY = 1) and (@TYPECODE <> 3) -- Currently recurring gifts have no GL mappings

      exec dbo.USP_REVENUE_VALIDATEGLMAPPING @BATCHROWID, 1, @CURRENTAPPUSERID;

    --Validate that the total benefit amount is not greater than the revenue amount.

    declare @TOTALBENEFITAMOUNT money;

    if @BENEFITSWAIVED = 0
    begin
      select @TOTALBENEFITAMOUNT = coalesce(sum(TOTALVALUE), 0) from dbo.UFN_REVENUE_GETBENEFITS_3_FROMITEMLISTXML(@BENEFITS)    
      select @TOTALBENEFITAMOUNT = @TOTALBENEFITAMOUNT + coalesce(sum(PERCENTAPPLICABLEAMOUNT * VALUEPERCENT/100), 0) from dbo.UFN_REVENUEBATCH_GETBENEFITSPCT_FROMITEMLISTXML(@PERCENTAGEBENEFITS);

      if @TOTALBENEFITAMOUNT > @AMOUNT 
        raiserror('ERR_REVENUEBENEFIT_TOTALVALUELESSTHANREVENUEAMOUNT', 13, 1);
    end    

    if @LOCKBOXCHANGED is null and (@LOCKBOXID is not null or @LOCKBOXBATCHNUMBER is not null or  @LOCKBOXBATCHSEQUENCE is not null)
    begin
      set @LOCKBOXCHANGED = 1;
    end;

    -- if the group type can't be a donor, raise an error

    if exists(select GD.ID from dbo.GROUPDATA GD inner join dbo.GROUPTYPE GT on GD.GROUPTYPEID = GT.ID where GD.ID = @CONSTITUENTID and GT.CANBEDONOR = 0)
      raiserror('GROUPCANNOTBEDONOR', 13, 1);

    exec dbo.USP_DATAFORMTEMPLATE_VIEW_REVENUETRANSACTIONPAGEDATA
      @ID = @REVENUEID,
      @ISPAYMENT = @ISPAYMENT output,
      @ISPOSTED = @ISPOSTED output,
      @ISORDERAPPLICATION = @ISORDERAPPLICATION output,
      @HASGIFTAIDSPLITONPENDINGR68PROCESS = @HASGIFTAIDSPLITONPENDINGR68PROCESS output,
      @ISMISCELLANEOUSPAYMENT = @ISMISCELLANEOUSPAYMENT output,
      @ISMIXEDORDERPAYMENT = @ISMIXEDORDERPAYMENT output,
      @GIFTFEE_ENABLED = @GIFTFEE_ENABLED output,
      @ISPLEDGE = @ISPLEDGE output,
      @ISPENDING = @ISPENDING output,
      @ISPLANNEDGIFT = @ISPLANNEDGIFT output,
      @ISRECURRINGGIFT = @ISRECURRINGGIFT output,
      @ISMGPLEDGE = @ISMGPLEDGE output,
      @ISAUCTIONDONATION = @ISAUCTIONDONATION output,
      @ISGRANTAWARD = @ISGRANTAWARD output,
      @PLEDGEBALANCE = @PLEDGEBALANCE output,
      @SPONSORSHIPID = @SPONSORSHIPID output,
      @PAYMENTTYPECODE = @PAYMENTTYPECODE output,
      @HASSOLDSTOCK = @HASSOLDSTOCK output,
      @HASSOLDPROPERTY = @HASSOLDPROPERTY output,
      @EXTRADATAISPOSTED = @EXTRADATAISPOSTED output,
      @ISDONORCHALLENGE = @ISDONORCHALLENGE output

    declare @AUTOPAY bit;
    set @AUTOPAY = (SELECT case when @PAYMENTMETHODCODE in (2, 3, 11) then 1 else 0 end)

    -- paperless mandate order setup not available in batch

    declare @SENDPMINSTRUCTION bit = 0;
    declare @PMINSTRUCTIONTOSENDCODE tinyint = 0;
    declare @PMINSTRUCTIONDATE_NEW date = null;
    declare @PMINSTRUCTIONDATE_CANCEL date = null;
    declare @PMINSTRUCTIONDATE_SETUP date = null;
    declare @PMADVANCENOTICESENTDATE date = null; 
    declare @LASTACTIVITYDATE date = null;
    declare @PREVIOUSSCHEDULESEEDDATE date = null;

    if dbo.UFN_INSTALLEDPRODUCTS_PRODUCTIS('9568A6C2-F7AA-45fd-8F54-21FE9654EE2D') = 0
    begin
      set @DDISOURCECODEID = null;
      set @DDISOURCEDATE = null;
    end
    else if @PAYMENTMETHODCODE = 3 -- direct debit

    begin
      select 
        @SENDPMINSTRUCTION = REVENUESCHEDULEDIRECTDEBITPAYMENT.SENDPMINSTRUCTION,
        @PMINSTRUCTIONTOSENDCODE = REVENUESCHEDULEDIRECTDEBITPAYMENT.PMINSTRUCTIONTOSENDCODE,
        @PMINSTRUCTIONDATE_NEW = REVENUESCHEDULEDIRECTDEBITPAYMENT.PMINSTRUCTIONDATE_NEW,
        @PMINSTRUCTIONDATE_CANCEL = REVENUESCHEDULEDIRECTDEBITPAYMENT.PMINSTRUCTIONDATE_CANCEL,
        @PMINSTRUCTIONDATE_SETUP = REVENUESCHEDULEDIRECTDEBITPAYMENT.PMINSTRUCTIONDATE_SETUP,
        @PMADVANCENOTICESENTDATE = REVENUESCHEDULEDIRECTDEBITPAYMENT.PMADVANCENOTICESENTDATE
      from 
        dbo.REVENUESCHEDULEDIRECTDEBITPAYMENT
      where 
        REVENUESCHEDULEDIRECTDEBITPAYMENT.ID = @REVENUEID;
    end

    if @DDISOURCECODEID is null
    begin
      set @SENDPMINSTRUCTION = 0;
      set @PMINSTRUCTIONTOSENDCODE = 0;
      set @PMINSTRUCTIONDATE_NEW = null;
      set @PMINSTRUCTIONDATE_CANCEL = null;
      set @PMINSTRUCTIONDATE_SETUP = null;
      set @PMADVANCENOTICESENTDATE = null;
    end

    -- extract solicitors and recognitions from the corresponding application xmls

    set @RECOGNITIONS = dbo.UFN_REVENUEUPDATEBATCH_GETRECOGNITIONSXML(@ID, @APPLICATIONRECOGNITIONS)
    set @SOLICITORS = dbo.UFN_REVENUEUPDATEBATCH_GETSOLICITORSXML(@ID, @APPLICATIONSOLICITORS)

    -- fix up the benefits collections. The reason this is needed is that the ID given in the collections is not the correct ID to use when

    --  later code tries to map the benefits to the ones on payments, pledges, etc. The REVENUEBENEFITID field will have this information and

    --  needs to become the ID for the benefit.

    select @BENEFITS = (SELECT
                                T.c.value('(BENEFITCURRENCYID)[1]','uniqueidentifier') AS 'BENEFITCURRENCYID',
                                T.c.value('(BENEFITID)[1]','uniqueidentifier') AS 'BENEFITID',
                                T.c.value('(DETAILS)[1]','nvarchar(255)') AS 'DETAILS',
                                T.c.value('(QUANTITY)[1]','smallint') AS 'QUANTITY',
                                T.c.value('(REVENUEBENEFITID)[1]','uniqueidentifier') AS 'ID',
                                T.c.value('(SEQUENCE)[1]','int') AS 'SEQUENCE',
                                T.c.value('(TOTALVALUE)[1]','money') AS 'TOTALVALUE',
                                T.c.value('(UNITVALUE)[1]','money') AS 'UNITVALUE'
                            FROM @BENEFITS.nodes('/BENEFITS/ITEM') T(c)
                            for xml raw('ITEM'),type,elements,root('BENEFITS'),BINARY BASE64)
    select @PERCENTAGEBENEFITS = (SELECT
                                T.c.value('(BENEFITID)[1]','uniqueidentifier') AS 'BENEFITID',
                                T.c.value('(DETAILS)[1]','nvarchar(255)') AS 'DETAILS',
                                T.c.value('(PERCENTAPPLICABLEAMOUNT)[1]','money') AS 'PERCENTAPPLICABLEAMOUNT',
                                T.c.value('(REVENUEBENEFITID)[1]','uniqueidentifier') AS 'ID',
                                T.c.value('(SEQUENCE)[1]','int') AS 'SEQUENCE',
                                T.c.value('(TOTALVALUE)[1]','decimal(38, 6)') AS 'TOTALVALUE',
                                T.c.value('(VALUEPERCENT)[1]','decimal(20, 4)') AS 'VALUEPERCENT'
                            FROM @PERCENTAGEBENEFITS.nodes('/PERCENTAGEBENEFITS/ITEM') T(c)
                            for xml raw('ITEM'),type,elements,root('PERCENTAGEBENEFITS'),BINARY BASE64)

    -- PAYMENT

    if @TYPECODE = 0 
    begin

      declare @APPLICATIONCODE tinyint;
      declare @SINGLEAPPLICATIONID uniqueidentifier;
      declare @APPLICATIONTYPECODE tinyint;
      declare @APPLICATIONAMOUNT money;
      declare @REVENUESTREAMS1 xml;

      if (@APPLICATIONINFO is null or len(@APPLICATIONINFO) = 0) and @REVENUESTREAMS is null and @ADDITIONALAPPLICATIONSSTREAM is null
        raiserror('ERR_ATLEASTONEAPPLICATION', 13, 1)

      if @APPLICATIONINFO is not null
      begin
        if len(@APPLICATIONINFO) > 3
        begin
          select  
            @SINGLEAPPLICATIONID = SINGLEAPPLICATIONID,
            @APPLICATIONTYPECODE = APPLICATIONTYPECODE,
            @APPLICATIONAMOUNT = APPLICATIONAMOUNT
          from 
            dbo.UFN_REVENUEBATCH_PARSEAPPLICATIONINFO(@APPLICATIONINFO)

          if @APPLICATIONAMOUNT > @AMOUNT
            set @APPLICATIONAMOUNT = @AMOUNT;

          set @REVENUESTREAMS1 = @REVENUESTREAMS;
          set @SINGLEDESIGNATIONID = null
          set @SPLITS = null
          set @ADDITIONALAPPLICATIONSSTREAM = null
          set @REVENUESTREAMS = null
        end
      end  -- applicationinfo


      declare @ADDITIONALAPPLICATIONSAMOUNT money
      if @ADDITIONALAPPLICATIONSSTREAM is not null
        select 
          @ADDITIONALAPPLICATIONSAMOUNT = sum(APPLIED)
        from 
          dbo.UFN_REVENUEBATCH_GETADDITIONALAPPLICATIONS_FROMITEMLISTXML(@ADDITIONALAPPLICATIONSSTREAM);
      else
        set @ADDITIONALAPPLICATIONSAMOUNT = 0;

      if @SINGLEAPPLICATIONID is not null
      begin

        declare @STREAMCOUNT int;
        select @STREAMCOUNT = count(*) from @REVENUESTREAMS1.nodes('/REVENUESTREAMS/ITEM') T(c);
          declare @UNAPPLIEDAMOUNT money;

        if @REVENUESTREAMS1 is not null and @STREAMCOUNT > 0
        begin
          select @UNAPPLIEDAMOUNT = sum(APPLIED)
          from dbo.UFN_REVENUEBATCH_GETAPPLICATIONS_FROMITEMLISTXML(@REVENUESTREAMS1);

          if @UNAPPLIEDAMOUNT + @ADDITIONALAPPLICATIONSAMOUNT <> @AMOUNT
            raiserror('BBERR_ALLMONEYNOTAPPLIED', 13, 1);                  

          set @SINGLEDESIGNATIONID = null
          set @SPLITS = null
        end
        else
          set @UNAPPLIEDAMOUNT = 0; 

        set @APPLICATIONCODE = 
              (
                  select
                  case @APPLICATIONTYPECODE 
                    when 5 then 2 --Pledge Payment

                    when 8 then 7 --MGPledge Payment

                    when 4 then 3 --Recurring Gift Payment

                    when 7 then 1 --Event Registration Payment

                    when 6 then 6 --Planned gift

                    when 2 then 5 --Membership

                    when 1 then 3 --Sponsorship Payment

                    when 9 then 8 --Grant Award Payment

                    when 10 then 13 -- Donor Challenge

                    --else -1

                    -- below for now 5/20/2010

                    else @APPLICATIONCODE -- donation

                  end
                )

        if @REVENUESPLITID is not null
        begin
        set @REVENUESTREAMS = 
                (
                  select
                    @REVENUESPLITID as ID,
                    @REVENUESPLITID as REVENUESPLITID,
                    @SINGLEAPPLICATIONID as APPLICATIONID,
                    (
                      case @APPLICATIONTYPECODE 
                        when 5 then 1 --Pledge Payment

                        when 8 then 3 --MGPledge Payment

                        when 4 then 2 --Recurring Gift Payment

                        when 7 then 6 --Event Registration Payment

                        when 6 then 4 --Planned gift

                        when 2 then 5 --Membership

                        when 10 then 10 -- Donor challenge payment

                        when 1 then 33 --Sponsorship Payment

                        when 9 then 9 --Grant Award Payment

                        else -1
                      end
                    ) as TYPECODE,
                    @AMOUNT as APPLIED,
                    @DECLINESGIFTAID as DECLINESGIFTAID,
                    @OPPORTUNITYID as OPPORTUNITYID,
                    1 as SEQUENCE,
                    @ISGIFTAIDSPONSORSHIP as ISGIFTAIDSPONSORSHIP,
                    @SINGLEDESIGNATIONID as DESIGNATIONID
                  for xml raw('ITEM'),type,elements,root('REVENUESTREAMS'),binary base64)
         end
         else
         begin
          set @REVENUESTREAMS = 
                (
                  select T.c.value('(REVENUESPLITID)[1]','uniqueidentifier') as ID,
                         T.c.value('(REVENUESPLITID)[1]','uniqueidentifier') as REVENUESPLITID,
                         T.c.value('(APPLICATIONID)[1]','uniqueidentifier') as APPLICATIONID,
                         T.c.value('(TYPECODE)[1]','tinyint') as TYPECODE,
                         T.c.value('(APPLIED)[1]','money') AS 'APPLIED',
                         @DECLINESGIFTAID as DECLINESGIFTAID,
                         @OPPORTUNITYID as OPPORTUNITYID,
                         1 as SEQUENCE,
                         @ISGIFTAIDSPONSORSHIP as ISGIFTAIDSPONSORSHIP,
                         @SINGLEDESIGNATIONID as DESIGNATIONID
                  from @REVENUESTREAMS1.nodes('/REVENUESTREAMS/ITEM') T(c)
                  where T.c.value('(APPLIED)[1]','money') > 0
                for xml raw('ITEM'),type,elements,root('REVENUESTREAMS'),binary base64)
         end
      end
      else 
      begin
        -- It's possible for @ADDITIONALAPPLICATIONSSTREAM amounts to be 0.  In that case, the

        -- designations specified in @ADDITIONALAPPLICATIONSSTREAM should still be created; otherwise,

        -- the payment could wind up with no applications, causing an error.  This

        -- occurs specifically in the case of zeroing out a payment.

        set @SPLITS = @ADDITIONALAPPLICATIONSSTREAM

        if not @ADDITIONALAPPLICATIONSSTREAM is null
          exec dbo.USP_REVENUEBATCH_VALIDATEADDITIONALAPPLICATIONS @ADDITIONALAPPLICATIONSSTREAM, @TYPECODE, @PAYMENTMETHODCODE, @CATEGORYCODEID, @ISORGANIZATION;

      end

  -- Check if the payment has been applied to any recurring gift which is not active

  -- TYPECODE : 2 = Recurring gift

  -- STATUSCODE : 0 = Active, 1 = Held, 2 = Terminated, 3 = Canceled

  if(
    exists(
      select
        1
      from
        dbo.REVENUESCHEDULE
      where
        REVENUESCHEDULE.ID in
        (
          select
            REVENUESTREAMS.APPLICATIONID
          from
            dbo.UFN_REVENUEUPDATEBATCH_GETAPPLICATIONS_FROMITEMLISTXML(@REVENUESTREAMS) as REVENUESTREAMS
            inner join dbo.FINANCIALTRANSACTION as RECURRINGGIFTREVENUE on RECURRINGGIFTREVENUE.ID = REVENUESTREAMS.APPLICATIONID
          where
            REVENUESTREAMS.TYPECODE = 2
            -- Bug 301742 Allow edit if payment previously existed

            and not exists
            (
              select 1
              from
                dbo.RECURRINGGIFTACTIVITY
                inner join dbo.FINANCIALTRANSACTIONLINEITEM as PAYMENTREVENUESPLIT on
                  PAYMENTREVENUESPLIT.ID = RECURRINGGIFTACTIVITY.PAYMENTREVENUEID
                  and PAYMENTREVENUESPLIT.TYPECODE <> 1
                  and PAYMENTREVENUESPLIT.DELETEDON is null
                inner join dbo.FINANCIALTRANSACTION as PAYMENTREVENUE on
                  PAYMENTREVENUE.ID = PAYMENTREVENUESPLIT.FINANCIALTRANSACTIONID
                  and PAYMENTREVENUE.ID = @REVENUEID
              where
                RECURRINGGIFTACTIVITY.SOURCEREVENUEID = RECURRINGGIFTREVENUE.ID
            )
        )
        and REVENUESCHEDULE.STATUSCODE in (1,2,3)
      )
    )
  begin
    raiserror('BBERR_PAYMENTAPPLIED_INACTIVERECURRINGGIFT', 13, 1);
  end

      select @APPLICATIONSTREAM = dbo.[UFN_REVENUEUPDATEBATCH_GETAPPLICATIONS_TOITEMLISTXML] (@ID, @SPLITS, @REVENUESTREAMS,@APPEALID, @CATEGORYCODEID,@RECOGNITIONS,@SOLICITORS)

      --set up the recognition credits for the revenuestream (applications)

      insert into @RECOGNITIONSTABLE (REVENUESPLITID, APPLICATIONID, RECOGNITIONS)
      select 
        APPS.REVENUESPLITID,
        APPS.APPLICATIONID,
        (
          select 
            REVENUERECOGNITIONID as ID, 
            CONSTITUENTID, 
            AMOUNT, 
            EFFECTIVEDATE, 
            REVENUERECOGNITIONTYPECODEID, 
            RECOGNITIONCREDITDESIGNATIONID as DESIGNATIONID, 
            RECOGNITIONCREDITFKID, 
            DONORCHALLENGERECOGNITIONTYPECODE
          from 
            dbo.UFN_REVENUEUPDATEBATCH_GETRECOGNITIONSFOREDIT_FROMITEMLISTXML(@RECOGNITIONS) REC
          where 
            REC.APPLICATIONID = coalesce(APPS.REVENUESPLITID, APPS.APPLICATIONID)
            for xml raw('ITEM'),type,elements,root('RECOGNITIONS'),binary base64
        ) as RECOGNITIONS
      from 
        dbo.UFN_REVENUEBATCH_GETAPPLICATIONS_FROMITEMLISTXML(@REVENUESTREAMS) APPS
      where 
        APPS.REVENUESPLITID is not null or
        (APPS.APPLIED > 0 and APPS.APPLICATIONID is not null);

      --set up solicitors for updates

      insert into @SOLICITORSTABLE (REVENUESPLITID, APPLICATIONID, SOLICITORS)
      select 
        APPS.REVENUESPLITID,
        APPS.APPLICATIONID,
        (
          select 
            REVENUESOLICITORID as ID, 
            CONSTITUENTID, 
            AMOUNT, SEQUENCE
          from 
            dbo.UFN_REVENUEUPDATEBATCH_GETSOLICITORSFOREDIT_FROMITEMLISTXML(@SOLICITORS) SOL
          where 
            SOL.APPLICATIONID = coalesce(APPS.REVENUESPLITID, APPS.APPLICATIONID)
          for xml raw('ITEM'),type,elements,root('SOLICITORS'),binary base64
        ) as SOLICITORS
      from 
        dbo.UFN_REVENUEBATCH_GETAPPLICATIONS_FROMITEMLISTXML(@REVENUESTREAMS) APPS
        where 
        APPS.REVENUESPLITID is not null or
        (APPS.APPLIED > 0 and APPS.APPLICATIONID is not null);

    end -- typecode payment


    --

    -- Set statuscode when null to posted 

    if @ISPOSTED = 1
      SET @POSTSTATUSCODE = coalesce(@POSTSTATUSCODE,0)

    -- pledge and recurring gift

    if @TYPECODE in (1, 3) 
    begin

      declare @SPLITTABLE table
      (
        ID uniqueidentifier,
        DESIGNATIONID uniqueidentifier,
        AMOUNT money,
        SEQUENCE int, 
        APPLICATIONCODE tinyint,
        TYPECODE tinyint,
        CATEGORYCODEID uniqueidentifier,
        DIDCAMPAIGNSDEFAULT bit,
        TRANSACTIONCURRENCYID uniqueidentifier,
        CAMPAIGNS xml,
        DECLINESGIFTAID bit, 
        ISGIFTAIDSPONSORSHIP bit, 
        REVENUESPLITID uniqueidentifier,
        ISNEW bit
      );

      insert into @SPLITTABLE 
      (
        ID, 
        DESIGNATIONID,
        AMOUNT,
        SEQUENCE,
        APPLICATIONCODE,
        TYPECODE,
        CATEGORYCODEID,
        DIDCAMPAIGNSDEFAULT,
        CAMPAIGNS,
        DECLINESGIFTAID,
        ISGIFTAIDSPONSORSHIP, 
        REVENUESPLITID,
        ISNEW
      )
      select
        ID,
        DESIGNATIONID,
        AMOUNT,
        SEQUENCE,
        APPLICATIONCODE,
        TYPECODE,
        case when @SPLITS.exist('(/ADDITIONALAPPLICATIONSSTREAM/ITEM/CATEGORYCODEID)') = 0 then @CATEGORYCODEID else CATEGORYCODEID end,
        DIDCAMPAIGNSDEFAULT,
        (
          select
            CAMP.REVENUESPLITCAMPAIGNID as ID, 
            CAMP.CAMPAIGNID,
            CAMP.CAMPAIGNSUBPRIORITYID
          from 
            dbo.UFN_REVENUEBATCH_GETSPLITCAMPAIGNS_FROMITEMLISTXML(CAMPAIGNS) CAMP
          for xml raw('ITEM'),type,elements,root('CAMPAIGNS'),BINARY BASE64
        ) as CAMPAIGNS,
        DECLINESGIFTAID,
        ISGIFTAIDSPONSORSHIP,
        coalesce(REVENUESPLITID, newID()),
        case when REVENUESPLITID is null then 1 else 0 end
      from 
        dbo.UFN_REVENUEBATCH_SPLITSWITHCHILDREN_FROMITEMLISTXML(@SPLITS)

      set @APPLICATIONSTREAM = 
        ( 
          select
            REVENUESPLITID as ID,
            DESIGNATIONID,
            AMOUNT,
            APPLICATIONCODE,
            TYPECODE,
            DECLINESGIFTAID,
            CATEGORYCODEID, 
            @TRANSACTIONCURRENCYID as TRANSACTIONCURRENCYID
          from 
            @SPLITTABLE
          for xml raw('ITEM'),type,elements,root('SPLITS'),binary base64
        )

      set @CAMPAIGNSTREAM = 
      ( 
        select 
          REVENUESPLITID as ID,
          cast((select T.c.query('CAMPAIGNS/ITEM') from CAMPAIGNS.nodes('/') T(c)) as xml) as CAMPAIGNS
        from 
          @SPLITTABLE
        for xml raw('ITEM'),type,elements,root('SPLITS'),binary base64
      )

      -- parse out the recognition credits only for existing splits

      insert into @RECOGNITIONSTABLE (REVENUESPLITID, RECOGNITIONS)
      select 
        SPLITWITHRECOGNITIONS.REVENUESPLITID,
        case 
          -- If this is a new split and the user hasn't manually set recognition credits, create default recognition credits

          when SPLITWITHRECOGNITIONS.ISNEW = 1 and SPLITWITHRECOGNITIONS.RECOGNITIONS is null then
          (
            select
              CONSTITUENTID,
              AMOUNT,
              @DATE EFFECTIVEDATE, 
              REVENUERECOGNITIONTYPECODEID
            from dbo.UFN_REVENUEDETAIL_GETDEFAULTRECOGNITIONS(@GIVENANONYMOUSLY, @CONSTITUENTID, SPLITWITHRECOGNITIONS.AMOUNT, @DATE, null)
            for xml raw('ITEM'),type,elements,root('RECOGNITIONS'),binary base64
          )
          else SPLITWITHRECOGNITIONS.RECOGNITIONS
        end
      from 
      (
        select
          REVENUESPLITID,
          ISNEW,
          AMOUNT,
          (
            select 
              REVENUERECOGNITIONID as ID, 
              CONSTITUENTID, 
              AMOUNT, 
              EFFECTIVEDATE, 
              REVENUERECOGNITIONTYPECODEID
            from dbo.UFN_REVENUEUPDATEBATCH_GETRECOGNITIONSFOREDIT_FROMITEMLISTXML(@RECOGNITIONS) REC
            where REC.DESIGNATIONID = SPLITS.DESIGNATIONID
            for xml raw('ITEM'),type,elements,root('RECOGNITIONS'),binary base64
          ) as RECOGNITIONS
        from @SPLITTABLE SPLITS
      ) as SPLITWITHRECOGNITIONS

      -- handle solicitors

      insert into @SOLICITORSTABLE (REVENUESPLITID, SOLICITORS)
      select 
        SPLITS.REVENUESPLITID,
        (
          select 
            REVENUESOLICITORID as ID, 
            CONSTITUENTID, 
            AMOUNT, 
            SEQUENCE
          from 
            dbo.UFN_REVENUEUPDATEBATCH_GETSOLICITORSFOREDIT_FROMITEMLISTXML(@SOLICITORS) SOL
          where 
            SOL.DESIGNATIONID = SPLITS.DESIGNATIONID
          for xml raw('ITEM'),type,elements,root('SOLICITORS'),binary base64
        ) as SOLICITORS
      from 
        @SPLITTABLE SPLITS

      if not @SPLITS is null
        exec dbo.USP_REVENUESPLIT_VALIDATESPLITS @SPLITS, @AMOUNT, @TYPECODE;
      else
        if @SINGLEDESIGNATIONID is null
          raiserror('ERR_SINGLEDESIGNATION_REQUIRED', 13, 1);

      -- for pledge only

      if @TYPECODE = 1
      begin
        -- redo the splits collection

        set @SPLITS = 
          (
            select 
              REVENUESPLITID as ID,
              DESIGNATIONID,
              AMOUNT,
              SEQUENCE
              APPLICATIONCODE,
              TYPECODE,
              CAMPAIGNS.query('(CAMPAIGNS/ITEM)') as CAMPAIGNS,
              DECLINESGIFTAID,
              ISGIFTAIDSPONSORSHIP,
              @TRANSACTIONCURRENCYID as TRANSACTIONCURRENCYID
            from 
              @SPLITTABLE
            for xml raw('ITEM'),type,elements,root('SPLITS'),binary base64
          )

        -- handle pledge installments here

        set @INSTALLMENTS = 
          (
            select
              T.c.value('(INSTALLMENTID)[1]','uniqueidentifier') AS 'ID',
              T.c.value('(DATE)[1]','datetime') AS 'DATE',
              T.c.value('(AMOUNT)[1]','money') AS 'AMOUNT',
              T.c.value('(RECEIPTAMOUNT)[1]','money') AS 'RECEIPTAMOUNT',
              T.c.value('(BALANCE)[1]','money') AS 'BALANCE',
              T.c.value('(APPLIED)[1]','money') AS 'APPLIED',
              T.c.value('(SEQUENCE)[1]','int') AS 'SEQUENCE',
              @TRANSACTIONCURRENCYID as 'TRANSACTIONCURRENCYID',
              (
                select 
                  INSTALLMENTSPLITID as ID,
                  AMOUNT,
                  APPLIED,
                  DESIGNATIONID,
                  @TRANSACTIONCURRENCYID as 'TRANSACTIONCURRENCYID'
                from 
                  dbo.UFN_REVENUEUPDATEBATCH_GETINSTALLMENTSPLITS_FROMITEMLISTXML(T.c.query('(INSTALLMENTSPLITS)[1]'))
                for xml raw('ITEM'),type,elements,BINARY BASE64
              ) as 'INSTALLMENTSPLITS'
            from 
              @INSTALLMENTS.nodes('/INSTALLMENTS/ITEM') T(c)
            for xml raw('ITEM'),type,elements,root('INSTALLMENTS'),binary base64
          )

          update T1 set T1.INSTALLMENTAMOUNT = T2.INSTALLMENTAMOUNT 
            from dbo.PLEDGEINSTALLMENTOPTION T1 
            inner join dbo.BATCHREVENUE T2 on T1.ID = T2.REVENUEID and T2.ID = @BATCHROWID 


        /*
        exec dbo.USP_REVENUEUPDATEBATCH_BUILDINSTALLMENTS 
            @REVENUEID, 
            @AMOUNT, 
            @PREVIOUSAMOUNT,
            @INSTALLMENTFREQUENCYCODE,
            @NUMBEROFINSTALLMENTS,
            @TRANSACTIONCURRENCYID,
            @SPLITS, 
            @INSTALLMENTS output,
            @DATE,
            @PREVIOUSDATE
        */
      end

      if @TYPECODE = 3 -- recurring gift

      begin
        --get first installment w/ no activity, and after which there is no activity WI#254736

        exec dbo.USP_DATAFORMTEMPLATE_EDITLOAD_RECURRINGGIFT_2 @ID = @REVENUEID, @NEXTINSTALLMENTID = @NEXTINSTALLMENTID output, @LASTACTIVITYDATE = @LASTACTIVITYDATE output, @PREVIOUSSCHEDULESEEDDATE = @PREVIOUSSCHEDULESEEDDATE output;
      end
    end -- typecode pledge or recurring gift


    -- Handle validation for revenue splits and duplicate campaigns.

    -- Only run if validating since we'll get there's a constraint on the table that will generate 

    -- an error if it's inserted and that's processed as an expected DB exception.

    if (@VALIDATEONLY = 1)
    begin
      if (not @SPLITS is null)
      begin
        declare @CAMPAIGNSVALIDATE xml;
        declare CAMPAIGNVALIDATECURSOR cursor local fast_forward for 

        select
          CAMPAIGNS
        from 
          dbo.UFN_REVENUEBATCH_SPLITSWITHCHILDREN_FROMITEMLISTXML(@CAMPAIGNSTREAM);

        open CAMPAIGNVALIDATECURSOR;

        fetch next from CAMPAIGNVALIDATECURSOR into @CAMPAIGNSVALIDATE;

        while @@FETCH_STATUS = 0
        begin
          if exists(
            select 1 
            from 
              dbo.UFN_REVENUESPLIT_CAMPAIGNS_FROMITEMLISTXML(@CAMPAIGNSVALIDATE) 
            group by 
              CAMPAIGNID, CAMPAIGNSUBPRIORITYID
            having count(*) > 1
          )
          begin
            raiserror('ERR_VALIDATION_DUPLICATECAMPAIGN', 13, 1);
          end

          fetch next from CAMPAIGNVALIDATECURSOR into @CAMPAIGNSVALIDATE;
        end

        close CAMPAIGNVALIDATECURSOR;
        deallocate CAMPAIGNVALIDATECURSOR;
      end

      -- When actually committing, the declarations will be validated through table constraints

      exec dbo.USP_BATCH_VALIDATETAXDECLARATIONS @TAXDECLARATIONS = @TAXDECLARATIONS, @BATCHTYPE = 1, @CONSTITUENTID = @CONSTITUENTID
    end

    --Fetching this so we can use the newest payment detail edit procedures

    declare @SEPAMANDATEID uniqueidentifier;
    select
      @SEPAMANDATEID = REVENUESCHEDULEDIRECTDEBITPAYMENT.SEPAMANDATEID
    from
      dbo.REVENUESCHEDULEDIRECTDEBITPAYMENT
    where
      REVENUESCHEDULEDIRECTDEBITPAYMENT.ID = @REVENUEID
      and
      @PAYMENTMETHODCODE = 3;

    -- process the edits

    --

    -- handle payments

    if @ISPAYMENT = 1
    begin 
      if @RECEIPTAMOUNT > @AMOUNT
        raiserror('ERR_VALIDATE_RECEIPTAMOUNT', 13, 1);

      --Calculate the Gift fee if either UPDATEGIFTFEE field is not included or its value is set to true

      if @UPDATEGIFTFEES is null or @UPDATEGIFTFEES = 1
      begin
        set @SHOULDUPDATEGIFTFEE = dbo.UFN_REVENUE_SHOULDUPDATEGIFTFEE(@REVENUEID, @CONSTITUENTID, @PAYMENTMETHODCODE, @TRANSACTIONCURRENCYID, @BASECURRENCYID, @BASEEXCHANGERATEID, @ORGANIZATIONEXCHANGERATEID, @APPLICATIONSTREAM, @RECEIPTAMOUNT, @AMOUNT)
      end

      declare @OLDPOSTSTATUSCODE tinyint;    
      if @ISPOSTED = 0
        select 
          @OLDPOSTSTATUSCODE = 
          (
            case when REVENUE.DONOTPOST = 1 then 
              2 
            else 
              1 
            end 
          )
        from 
          dbo.REVENUE 
        where 
          REVENUE.ID = @REVENUEID;
      else
      begin
        select 
          @OLDPOSTSTATUSCODE = ADJUSTMENT.POSTSTATUSCODE 
        from 
          dbo.ADJUSTMENT 
        where 
          ADJUSTMENT.REVENUEID = @REVENUEID 
          and ADJUSTMENT.POSTSTATUSCODE <> 0;

        if @OLDPOSTSTATUSCODE is null
          set @OLDPOSTSTATUSCODE = @ADJUSTMENTPOSTSTATUSCODE;
      end

      if @ISPOSTED  = 0
      begin
        if @ISMISCELLANEOUSPAYMENT = 0 and @HASGIFTAIDSPLITONPENDINGR68PROCESS = 0
        begin
          exec dbo.USP_DATAFORMTEMPLATE_EDIT_PAYMENT7
            @ID = @REVENUEID,
            @CHANGEAGENTID = @CHANGEAGENTID,
            @DATE = @DATE,
            @AMOUNT = @AMOUNT,
            @RECEIPTAMOUNT = @RECEIPTAMOUNT,
            @REVENUESTREAMS = @APPLICATIONSTREAM,
            @SOURCECODE = @SOURCECODE,
            @APPEALID = @APPEALID,
            @BENEFITS = @BENEFITS,
            @BENEFITSWAIVED = @BENEFITSWAIVED,
            @GIVENANONYMOUSLY = @GIVENANONYMOUSLY,
            @MAILINGID = @MAILINGID,
            @CHANNELCODEID = @CHANNELCODEID,
            @DONOTRECEIPT = @DONOTRECEIPT,
            @DONOTACKNOWLEDGE = @DONOTACKNOWLEDGE,
            @REFERENCE = @REFERENCE,
            @POSTSTATUSCODE = @POSTSTATUSCODE,
            @POSTDATE = @POSTDATE,
            @PAYMENTMETHODCODE = @PAYMENTMETHODCODE,
            @OTHERPAYMENTMETHODCODEID = @OTHERPAYMENTMETHODCODEID,
            @CHECKDATE = @CHECKDATE,
            @CHECKNUMBER = @CHECKNUMBER,
            @REFERENCEDATE = @REFERENCEDATE,
            @REFERENCENUMBER = @REFERENCENUMBER,
            @CARDHOLDERNAME = @CARDHOLDERNAME,
            @CREDITCARDNUMBER = @CREDITCARDNUMBER,
            @CREDITTYPECODEID = @CREDITTYPECODEID,
            @AUTHORIZATIONCODE = @AUTHORIZATIONCODE,
            @EXPIRESON = @EXPIRESON,
            @ISSUER = @ISSUER,
            @NUMBEROFUNITS = @NUMBEROFUNITS,
            @SYMBOL = @SYMBOL,
            @MEDIANPRICE = @MEDIANPRICE,
            @GIFTINKINDSUBTYPECODEID = @GIFTINKINDSUBTYPECODEID,
            @PROPERTYSUBTYPECODEID = @PROPERTYSUBTYPECODEID,
            @CONSTITUENTACCOUNTID = @CONSTITUENTACCOUNTID,
            @DIRECTDEBITRESULTCODE = @DIRECTDEBITRESULTCODE,
            @LOWPRICE = @LOWPRICE,
            @HIGHPRICE = @HIGHPRICE,
            @GIFTINKINDITEMNAME = @GIFTINKINDITEMNAME,
            @GIFTINKINDDISPOSITIONCODE = @GIFTINKINDDISPOSITIONCODE,
            @GIFTINKINDNUMBEROFUNITS = @GIFTINKINDNUMBEROFUNITS,
            @GIFTINKINDFAIRMARKETVALUE = @GIFTINKINDFAIRMARKETVALUE,
            @DIRECTDEBITISREJECTED = @DIRECTDEBITISREJECTED,
            @PERCENTAGEBENEFITS = @PERCENTAGEBENEFITS,
            @BASEEXCHANGERATEID = @BASEEXCHANGERATEID,
            @EXCHANGERATE = @EXCHANGERATE,
            @ADJUSTMATCHINGGIFTCLAIMS = @UPDATEMATCHINGGIFTCLAIMS,
            @CURRENTAPPUSERID = @CURRENTAPPUSERID,   
            @UPDATEGIFTFEEOPTION = null,
            @UPDATETRIBUTEOPTION = null,
            @VALIDATETRIBUTES = null,
            @SALE_SALEDATE = @SALEDATE,
            @SALE_SALEAMOUNT = @SALEAMOUNT,
            @SALE_BROKERFEES = @BROKERFEE,
            @SALE_GLPOSTDATE = @SALEPOSTDATE,
            @SALE_GLPOSTSTATUS = @SALEPOSTSTATUSCODE,
            @SALE_LOWPRICE = @SALE_LOWPRICE,
            @SALE_MEDIANPRICE = @SALE_MEDIANPRICE,
            @SALE_HIGHPRICE = @SALE_HIGHPRICE,
            @SEPAMANDATEID = @SEPAMANDATEID,
            @BATCHROWID = @BATCHROWID,
            @NUMBEROFUNITSSOLD = @NUMBEROFUNITSSOLD;

        end
      end  -- is unposted

      else -- posted payments

      begin
        if @ISMISCELLANEOUSPAYMENT = 0 and (@ISORDERAPPLICATION = 0) and (@HASGIFTAIDSPLITONPENDINGR68PROCESS = 0)
        begin
          exec dbo.USP_DATAFORMTEMPLATE_EDIT_PAYMENTADJUST7
            @ID = @REVENUEID,
            @CHANGEAGENTID = @CHANGEAGENTID,
            @DATE = @DATE,
            @AMOUNT = @AMOUNT,
            @RECEIPTAMOUNT = @RECEIPTAMOUNT,
            @REVENUESTREAMS = @APPLICATIONSTREAM,
            @SOURCECODE = @SOURCECODE,
            @APPEALID = @APPEALID,
            @BENEFITS = @BENEFITS,
            @BENEFITSWAIVED = @BENEFITSWAIVED,
            @GIVENANONYMOUSLY = @GIVENANONYMOUSLY,
            @MAILINGID = @MAILINGID,
            @CHANNELCODEID = @CHANNELCODEID,
            @DONOTRECEIPT = @DONOTRECEIPT,
            @DONOTACKNOWLEDGE = @DONOTACKNOWLEDGE,
            @REFERENCE = @REFERENCE,
            @ADJUSTMENTDATE = @ADJUSTMENTDATE,
            @ADJUSTMENTPOSTDATE = @ADJUSTMENTPOSTDATE,
            @ADJUSTMENTREASON = @ADJUSTMENTREASON,
            @PAYMENTMETHODCODE = @PAYMENTMETHODCODE,
            @OTHERPAYMENTMETHODCODEID = @OTHERPAYMENTMETHODCODEID,
            @CHECKDATE = @CHECKDATE,
            @CHECKNUMBER = @CHECKNUMBER,
            @REFERENCEDATE = @REFERENCEDATE,
     @REFERENCENUMBER = @REFERENCENUMBER,
            @CARDHOLDERNAME = @CARDHOLDERNAME,
            @CREDITCARDNUMBER = @CREDITCARDNUMBER,
            @CREDITTYPECODEID = @CREDITTYPECODEID,
            @AUTHORIZATIONCODE = @AUTHORIZATIONCODE,
            @EXPIRESON = @EXPIRESON,
            @ISSUER = @ISSUER,
            @NUMBEROFUNITS = @NUMBEROFUNITS,
            @SYMBOL = @SYMBOL,
            @MEDIANPRICE = @MEDIANPRICE,
            @GIFTINKINDSUBTYPECODEID = @GIFTINKINDSUBTYPECODEID,
            @PROPERTYSUBTYPECODEID = @PROPERTYSUBTYPECODEID,
            @CONSTITUENTACCOUNTID = @CONSTITUENTACCOUNTID,
            @DIRECTDEBITRESULTCODE = @DIRECTDEBITRESULTCODE,
            @LOWPRICE = @LOWPRICE,
            @HIGHPRICE = @HIGHPRICE,
            @ADJUSTMENTREASONCODEID = @ADJUSTMENTREASONCODEID,
            @ADJUSTMENTPOSTSTATUSCODE = @ADJUSTMENTPOSTSTATUSCODE,
            @GIFTINKINDITEMNAME = @GIFTINKINDITEMNAME,
            @GIFTINKINDDISPOSITIONCODE = @GIFTINKINDDISPOSITIONCODE,
            @GIFTINKINDNUMBEROFUNITS = @GIFTINKINDNUMBEROFUNITS,
            @GIFTINKINDFAIRMARKETVALUE = @GIFTINKINDFAIRMARKETVALUE,
            @DIRECTDEBITISREJECTED = @DIRECTDEBITISREJECTED,
            @PERCENTAGEBENEFITS = @PERCENTAGEBENEFITS,
            @BASEEXCHANGERATEID = @BASEEXCHANGERATEID,
            @EXCHANGERATE = @EXCHANGERATE,
            @CURRENTAPPUSERID = @CURRENTAPPUSERID,
            @ADJUSTMATCHINGGIFTCLAIMS = @UPDATEMATCHINGGIFTCLAIMS,
            @UPDATEGIFTFEEOPTION = @UPDATEGIFTFEES,
            @UPDATETRIBUTEOPTION = null,
            @VALIDATETRIBUTES = null,
            @DEPOSITID = null,
            @SEPAMANDATEID = @SEPAMANDATEID,            
            @NUMBEROFUNITSSOLD = @NUMBEROFUNITSSOLD,
            @SALE_SALEDATE = @SALEDATE,
            @SALE_SALEAMOUNT = @SALEAMOUNT,
            @SALE_BROKERFEES = @BROKERFEE,
            @SALE_GLPOSTDATE = @SALEPOSTDATE,
            @SALE_GLPOSTSTATUS = @SALEPOSTSTATUSCODE,
            @SALE_LOWPRICE = @SALE_LOWPRICE,
            @SALE_MEDIANPRICE = @SALE_MEDIANPRICE,
            @SALE_HIGHPRICE = @SALE_HIGHPRICE;
          set @SHOULDUPDATEGIFTFEE = 0
        end
        /*
        if @ISORDERAPPLICATION and (not @ISMISCELLANEOUSPAYMENT) AndAlso Not @ISMIXEDORDERPAYMENT AndAlso (Not @HASGIFTAIDSPLITONPENDINGR68PROCESS)
        begin
          exec dbo.USP_DATAFORMTEMPLATE_EDIT_ORDERPAYMENTADJUST
        end
        */
      end  

      --AnkushGu - 12/18/2012 - WI 248767

      --We need to perform clean up from RUB when we change the Payment method from Other type to Donation, Unapplied MG etc.

      exec dbo.USP_REVENUEUPDATEBATCH_CLEANUPOTHERPAYMENTRECORDS @REVENUESTREAM = @APPLICATIONSTREAM

      --If any marketing information is null, try to default based on applications

      if @SOURCECODE is null or @SOURCECODE = '' or @MAILINGID is null or @APPEALID is null
        exec dbo.USP_REVENUE_DEFAULTMARKETINGINFORMATION @ID, @CHANGEAGENTID, @CURRENTDATE;

      if @PAYMENTMETHODCODE = 2 and len(@VENDORID) > 0 and @TRANSACTIONID is not null
        update CCD set
          VENDORID = isnull(@VENDORID, '')
          ,TRANSACTIONID = @TRANSACTIONID
          ,CHANGEDBYID = @CHANGEAGENTID
          ,DATECHANGED = @CURRENTDATE
        from dbo.CREDITCARDPAYMENTMETHODDETAIL CCD
        inner join dbo.REVENUEPAYMENTMETHOD RPM on CCD.ID = RPM.ID
        where RPM.REVENUEID = @REVENUEID;

    end  -- is payment

    --

    -- edit pledge

    if @ISPLEDGE = 1
    begin
      declare @ISMEMBERSHIPPLEDGE bit;
      declare @INSTALLMENTAMOUNT money;

      select
        @ISMEMBERSHIPPLEDGE = 
          case 
            when FINANCIALTRANSACTION.TYPECODE = 15 then
              1
            else
              0
          end,
        @INSTALLMENTAMOUNT = PLEDGEINSTALLMENTOPTION.INSTALLMENTAMOUNT
      from
        dbo.FINANCIALTRANSACTION
      left join
        dbo.PLEDGEINSTALLMENTOPTION on PLEDGEINSTALLMENTOPTION.ID = FINANCIALTRANSACTION.ID
      where
        FINANCIALTRANSACTION.ID = @REVENUEID;

      if @ISPOSTED = 0
      begin
        -- edit the main pledge information

        exec dbo.USP_DATAFORMTEMPLATE_EDIT_PLEDGE_8
          @ID = @REVENUEID,
          @CHANGEAGENTID = @CHANGEAGENTID,
          @DATE = @DATE,
          @AMOUNT = @AMOUNT,
          @POSTSTATUSCODE = @POSTSTATUSCODE,
          @POSTDATE = @POSTDATE,
          @SENDPLEDGEREMINDER = @SENDPLEDGEREMINDER,
          @SPLITS = @APPLICATIONSTREAM,
          @FREQUENCYCODE = @INSTALLMENTFREQUENCYCODE,
          @NUMBEROFINSTALLMENTS = @NUMBEROFINSTALLMENTS,
          @NEXTTRANSACTIONDATE = @INSTALLMENTSTARTDATE,
          @INSTALLMENTS = @INSTALLMENTS,
          @SOURCECODE = @SOURCECODE,
          @APPEALID = @APPEALID,
          @BENEFITS = @BENEFITS,
          @BENEFITSWAIVED = @BENEFITSWAIVED,
          @GIVENANONYMOUSLY = @GIVENANONYMOUSLY,
          @MAILINGID = @MAILINGID,
          @CHANNELCODEID = @CHANNELCODEID,
          @DONOTACKNOWLEDGE = @DONOTACKNOWLEDGE,
          @PLEDGESUBTYPEID = @PLEDGESUBTYPEID,
          @OPPORTUNITYID = @OPPORTUNITYID,
          @REFERENCE = @REFERENCE,
          @CATEGORYCODEID = @CATEGORYCODEID,
          @ADJPAYMENT_DATE = @ADJPAYMENT_DATE,
          @ADJPAYMENT_POSTDATE = @ADJPAYMENT_POSTDATE,
          @ADJPAYMENT_REASONCODEID = @ADJPAYMENT_REASONCODEID,
          @ADJPAYMENT_DETAILS = @ADJPAYMENT_DETAILS,
          @PERCENTAGEBENEFITS = @PERCENTAGEBENEFITS,
          @BASECURRENCYID = @BASECURRENCYID,
          @TRANSACTIONCURRENCYID = @TRANSACTIONCURRENCYID,
          @BASEEXCHANGERATEID = @BASEEXCHANGERATEID,
          @EXCHANGERATE = @EXCHANGERATE,
          @HADSPOTRATE = @HADSPOTRATE,
          @RATECHANGED = @RATECHANGED,
          @UPDATERECOGNITIONOPTION = 0,
          @UPDATETRIBUTEOPTION = 0,
          @VALIDATETRIBUTES = 0, --We do our own validation and will update the tributes in a future step.

          @ISMEMBERSHIPPLEDGE = @ISMEMBERSHIPPLEDGE,
          @INSTALLMENTAMOUNT = @INSTALLMENTAMOUNT;
      end
      else
      begin
        exec dbo.USP_DATAFORMTEMPLATE_EDIT_PLEDGEADJUST_9
          @ID = @REVENUEID,
          @CHANGEAGENTID = @CHANGEAGENTID,
          @DATE = @DATE,
          @AMOUNT = @AMOUNT,
          @POSTSTATUSCODE = @POSTSTATUSCODE,
          @POSTDATE = @POSTDATE,
          @SENDPLEDGEREMINDER = @SENDPLEDGEREMINDER,
          @SPLITS = @APPLICATIONSTREAM,
          @FREQUENCYCODE = @INSTALLMENTFREQUENCYCODE,
          @NUMBEROFINSTALLMENTS =@NUMBEROFINSTALLMENTS,
          @NEXTTRANSACTIONDATE = @INSTALLMENTSTARTDATE,
          @INSTALLMENTS = @INSTALLMENTS,
          @SOURCECODE = @SOURCECODE,
          @APPEALID = @APPEALID,
          @BENEFITS = @BENEFITS,
          @BENEFITSWAIVED = @BENEFITSWAIVED,
          @GIVENANONYMOUSLY = @GIVENANONYMOUSLY,
          @MAILINGID = @MAILINGID,
          @CHANNELCODEID = @CHANNELCODEID,
          @DONOTACKNOWLEDGE = @DONOTACKNOWLEDGE,
          @PLEDGESUBTYPEID = @PLEDGESUBTYPEID,
          @OPPORTUNITYID = @OPPORTUNITYID,
          @REFERENCE = @REFERENCE,
          @CATEGORYCODEID = @CATEGORYCODEID,
          @ADJUSTMENTDATE = @ADJUSTMENTDATE,
          @ADJUSTMENTPOSTDATE = @ADJUSTMENTPOSTDATE,
          @ADJUSTMENTREASON = @ADJUSTMENTREASON,
          @ADJUSTMENTREASONCODEID = @ADJUSTMENTREASONCODEID,
          @ADJPAYMENT_DATE = @ADJPAYMENT_DATE,
          @ADJPAYMENT_POSTDATE = @ADJPAYMENT_POSTDATE,
          @ADJPAYMENT_REASONCODEID = @ADJPAYMENT_REASONCODEID,
          @ADJPAYMENT_DETAILS = @ADJPAYMENT_DETAILS,
          @PERCENTAGEBENEFITS = @PERCENTAGEBENEFITS,
          @BASECURRENCYID = @BASECURRENCYID,
          @TRANSACTIONCURRENCYID = @TRANSACTIONCURRENCYID,
          @BASEEXCHANGERATEID = @BASEEXCHANGERATEID,
          @EXCHANGERATE = @EXCHANGERATE,
          @HADSPOTRATE = @HADSPOTRATE,
          @RATECHANGED = @RATECHANGED,
          @UPDATERECOGNITIONOPTION = 0,
          @UPDATETRIBUTEOPTION= 0,
          @VALIDATETRIBUTES = 0; --We do our own validation and will update the tributes in a future step.

      end 

      -- edit the payment method information

      exec dbo.USP_DATAFORMTEMPLATE_EDIT_PLEDGEPAYMENTDETAILS_6
        @ID = @REVENUEID,
        @CHANGEAGENTID = @CHANGEAGENTID,
        @PAYMENTMETHODCODE = @PAYMENTMETHODCODE,
        @AUTOPAY = @AUTOPAY,
        @CARDHOLDERNAME = @CARDHOLDERNAME,
        @CREDITCARDNUMBER = @CREDITCARDNUMBER,
        @CREDITTYPECODEID = @CREDITTYPECODEID,
        @EXPIRESON = @EXPIRESON,
        @REFERENCEDATE = @REFERENCEDATE,
        @REFERENCENUMBER = @REFERENCENUMBER,
        @ACCOUNTID = @CONSTITUENTACCOUNTID,
        @CREDITCARDTOKEN = @CREDITCARDTOKEN,
        @STANDINGORDERSETUP = @STANDINGORDERSETUP,
        @STANDINGORDERSETUPDATE = @STANDINGORDERSETUPDATE,
        @DDISOURCECODEID = @DDISOURCECODEID,
        @DDISOURCEDATE = @DDISOURCEDATE,
        @SENDPMINSTRUCTION = @SENDPMINSTRUCTION,
        @PMINSTRUCTIONTOSENDCODE = @PMINSTRUCTIONTOSENDCODE,
        @PMINSTRUCTIONDATE_NEW = @PMINSTRUCTIONDATE_NEW,
        @PMINSTRUCTIONDATE_CANCEL = @PMINSTRUCTIONDATE_CANCEL,
        @PMINSTRUCTIONDATE_SETUP = @PMINSTRUCTIONDATE_SETUP,
        @PMADVANCENOTICESENTDATE = @PMADVANCENOTICESENTDATE,
        @UPDATEOTHERTRANSACTIONSTOUSENEWCREDITCARD = 0,
        @USESYSTEMGENERATEDREFERENCENUMBER = @GENERATEREFERENCENUMBER,
        @STANDINGORDERREFERENCENUMBER = @STANDINGORDERREFERENCENUMBER,
        @SEPAMANDATEID = @SEPAMANDATEID

    end -- edit pledge

    --

    -- edit recurring gift   

    if @ISRECURRINGGIFT = 1
    begin
      declare @ISMEMBERSHIPRECURRING bit;

      if exists
      (
        select REVENUESPLIT.ID
        from dbo.FINANCIALTRANSACTIONLINEITEM REVENUESPLIT
        inner join dbo.REVENUESPLIT_EXT on REVENUESPLIT.ID = REVENUESPLIT_EXT.ID
        inner join dbo.MEMBERSHIPTRANSACTION on MEMBERSHIPTRANSACTION.REVENUESPLITID = REVENUESPLIT.ID
        where REVENUESPLIT.FINANCIALTRANSACTIONID = @ID and REVENUESPLIT.DELETEDON is null and REVENUESPLIT.TYPECODE != 1
      )
      begin
        set @ISMEMBERSHIPRECURRING = 1
      end
      else
      begin
        set @ISMEMBERSHIPRECURRING = 0
      end

      exec dbo.USP_DATAFORMTEMPLATE_EDIT_RECURRINGGIFT_9
        @ID = @REVENUEID,
        @CHANGEAGENTID = @CHANGEAGENTID,
        @DATE = @DATE,
        @AMOUNT = @AMOUNT,
        @SPLITS = @APPLICATIONSTREAM,
        @FREQUENCYCODE = @INSTALLMENTFREQUENCYCODE,
        @ENDDATE = @INSTALLMENTENDDATE,
        @STARTDATE = @INSTALLMENTSTARTDATE,
        @FINDERNUMBER = @FINDERNUMBER,
        @SOURCECODE = @SOURCECODE,
        @APPEALID = @APPEALID,
        @GIVENANONYMOUSLY = @GIVENANONYMOUSLY,
        @MAILINGID = @MAILINGID,
        @CHANNELCODEID = @CHANNELCODEID,
        @DONOTACKNOWLEDGE = @DONOTACKNOWLEDGE,
        @REFERENCE = @REFERENCE,
        @CATEGORYCODEID = @CATEGORYCODEID,
        @SENDREMINDER = @SENDPLEDGEREMINDER,
        @NEXTINSTALLMENTID = @NEXTINSTALLMENTID,
        @REVENUEDEVELOPMENTFUNCTIONCODEID = @REVENUEDEVELOPMENTFUNCTIONCODEID,
        @TRANSACTIONCURRENCYID = @TRANSACTIONCURRENCYID,
        @BASEEXCHANGERATEID = @BASEEXCHANGERATEID,
        @EXCHANGERATE = @EXCHANGERATE,
        @HADSPOTRATE = @HADSPOTRATE,
        @RATECHANGED = @RATECHANGED,
        @UPDATERECOGNITIONOPTION = 2,
        @SCHEDULESEEDDATE = @INSTALLMENTSCHEDULESEEDDATE,
        @ISMEMBERSHIPRECURRING =  @ISMEMBERSHIPRECURRING,
        @PREVIOUSSCHEDULESEEDDATE = @PREVIOUSSCHEDULESEEDDATE,
        @LASTACTIVITYDATE = @LASTACTIVITYDATE

       -- Use the partial card number if set. This field for the payment method of Credit card - last 4 digits

       if coalesce(@PARTIALCREDITCARDNUMBER, '') <> ''
          set @CREDITCARDNUMBER = @PARTIALCREDITCARDNUMBER

      -- edit payment method details

      exec dbo.USP_DATAFORMTEMPLATE_EDIT_RECURRINGGIFTPAYMENTDETAILS_7
        @ID = @REVENUEID,
        @CHANGEAGENTID = @CHANGEAGENTID, 
        @PAYMENTMETHODCODE = @PAYMENTMETHODCODE,
        @AUTOPAY = @AUTOPAY,
        @CARDHOLDERNAME = @CARDHOLDERNAME,
        @CREDITCARDNUMBER = @CREDITCARDNUMBER,
        @CREDITTYPECODEID = @CREDITTYPECODEID,
        @EXPIRESON = @EXPIRESON,
        @REFERENCEDATE = @REFERENCEDATE,
        @REFERENCENUMBER = @REFERENCENUMBER,
        @ACCOUNTID = @CONSTITUENTACCOUNTID,
        @CREDITCARDTOKEN = @CREDITCARDTOKEN,
        @STANDINGORDERSETUP = @STANDINGORDERSETUP,
        @STANDINGORDERSETUPDATE = @STANDINGORDERSETUPDATE,
        @DDISOURCECODEID = @DDISOURCECODEID,
        @DDISOURCEDATE = @DDISOURCEDATE,
        @SENDPMINSTRUCTION = @SENDPMINSTRUCTION,
        @PMINSTRUCTIONTOSENDCODE = @PMINSTRUCTIONTOSENDCODE,
        @PMINSTRUCTIONDATE_NEW = @PMINSTRUCTIONDATE_NEW,
        @PMINSTRUCTIONDATE_CANCEL = @PMINSTRUCTIONDATE_CANCEL,
        @PMINSTRUCTIONDATE_SETUP = @PMINSTRUCTIONDATE_SETUP,
        @PMADVANCENOTICESENTDATE = @PMADVANCENOTICESENTDATE,
        @UPDATEOTHERTRANSACTIONSTOUSENEWCREDITCARD = 0,
        @USESYSTEMGENERATEDREFERENCENUMBER = @GENERATEREFERENCENUMBER,
        @STANDINGORDERREFERENCENUMBER = @STANDINGORDERREFERENCENUMBER,
        @SEPAMANDATEID = @SEPAMANDATEID,
        @OTHERPAYMENTMETHODCODEID  = @OTHERPAYMENTMETHODCODEID;
    end  -- edit recurring gift


    -- handle campaign updates

    if @CAMPAIGNSTREAM is not null
    begin
      declare @CAMPAIGNREVENUESPLITID uniqueidentifier, @CAMPAIGNS xml
      declare CAMPAIGNCURSOR cursor local fast_forward for 
        select
          ID,
          CAMPAIGNS
        from 
          dbo.UFN_REVENUEBATCH_SPLITSWITHCHILDREN_FROMITEMLISTXML(@CAMPAIGNSTREAM)

      open CAMPAIGNCURSOR
      fetch next from CAMPAIGNCURSOR into @CAMPAIGNREVENUESPLITID, @CAMPAIGNS

      while @@FETCH_STATUS = 0
      begin
        exec dbo.USP_REVENUESPLIT_CAMPAIGNS_UPDATEFROMXML @CAMPAIGNREVENUESPLITID, @CAMPAIGNS, @CHANGEAGENTID;

        fetch next from CAMPAIGNCURSOR into @CAMPAIGNREVENUESPLITID, @CAMPAIGNS
      end

      close CAMPAIGNCURSOR
      deallocate CAMPAIGNCURSOR
    end

    -- handle revenue ID update

    if @PREVIOUSREVENUELOOKUPID <> @REVENUELOOKUPID
    begin
      update dbo.REVENUE 
        set
          CUSTOMIDENTIFIER = rtrim(ltrim(@REVENUELOOKUPID)),
          CHANGEDBYID = @CHANGEAGENTID,
          DATECHANGED = @CURRENTDATE
      where 
        ID = @REVENUEID
    end

    -- handle lockbox changes

    if @LOCKBOXCHANGED = 1
    begin
      exec dbo.USP_DATAFORMTEMPLATE_EDIT_REVENUELOCKBOX
        @ID = @REVENUEID,
        @CHANGEAGENTID = @CHANGEAGENTID,
        @LOCKBOXID = @LOCKBOXID,
        @BATCHNUMBER = @LOCKBOXBATCHNUMBER,
        @BATCHSEQUENCE = @LOCKBOXBATCHSEQUENCE
    end;

    -- always handle tributes in case records have been all deleted

    -- reload tributes replacing id with revenuetributeid

    if @TRIBUTES is not null
    begin
      set @TRIBUTES = 
        (
          select
            coalesce(ID, newid()) as ID,
            AMOUNT,
            TRIBUTEID,
            DESIGNATIONID,
                      BASECURRENCYID,
            ORGANIZATIONAMOUNT,
            ORGANIZATIONEXCHANGERATEID
          from 
            dbo.UFN_REVENUE_GETTRIBUTES_2_FROMITEMLISTXML(@TRIBUTES)
          for xml raw('ITEM'),type,elements,root('TRIBUTES'),binary base64
        )
    end

    set @ORGANIZATIONCURRENCYID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();
    set @ORGANIZATIONEXCHANGERATEID = dbo.UFN_CURRENCYEXCHANGERATE_GETLATESTORGANIZATIONRATE(@BASECURRENCYID, @ORGANIZATIONCURRENCYID, @DATE, null, @TRANSACTIONCURRENCYID);

    set @TRIBUTES = dbo.UFN_REVENUETRIBUTE_CONVERTAMOUNTSINXML(@TRIBUTES, @BASECURRENCYID, @ORGANIZATIONEXCHANGERATEID);
    exec dbo.USP_REVENUE_GETTRIBUTES_2_UPDATEFROMXML 
      @REVENUEID = @REVENUEID, 
      @XML = @TRIBUTES,
      @CHANGEAGENTID = @CHANGEAGENTID,
      @CHANGEDATE = @CURRENTDATE

    /* commenting out since UI will handle creating default tribute letters
    if @TRIBUTES is not null
    begin
      -- insert default revenue tribute letters for any acknowledgees that have corresponding tribute letter codes
      insert into dbo.REVENUETRIBUTELETTER(ID,REVENUETRIBUTEID,CONSTITUENTID,TRIBUTELETTERCODEID,ADDEDBYID,CHANGEDBYID,DATEADDED,DATECHANGED)
      select newid(),RT.ID,TA.CONSTITUENTID,TA.TRIBUTELETTERCODEID,@CHANGEAGENTID,@CHANGEAGENTID,@CURRENTDATE,@CURRENTDATE
      from dbo.REVENUETRIBUTE RT
      inner join dbo.TRIBUTEACKNOWLEDGEE TA on RT.TRIBUTEID = TA.TRIBUTEID
      where TA.TRIBUTELETTERCODEID is not null 
      and RT.REVENUEID = @REVENUEID
      and not exists (select 'x' from REVENUETRIBUTELETTER where REVENUETRIBUTELETTER.REVENUETRIBUTEID = RT.ID and REVENUETRIBUTELETTER.CONSTITUENTID = TA.CONSTITUENTID and REVENUETRIBUTELETTER.TRIBUTELETTERCODEID = TA.TRIBUTELETTERCODEID);
    end
    */

    if @ISRECURRINGGIFT = 1
    begin
    -- handle benefits 

    if @BENEFITS is not null
    begin
      set @BENEFITS = 
        (
          select
            coalesce(REVENUEBENEFITID, newID()) as ID,
            BENEFITID,
            QUANTITY,
            UNITVALUE,
            TOTALVALUE,
            DETAILS,
            SEQUENCE
          from 
            dbo.UFN_REVENUEBATCH_GETBENEFITS_FROMITEMLISTXML(@BENEFITS)  
          for xml raw('ITEM'),type,elements,root('BENEFITS'),binary base64
        )
    end

    if @PERCENTAGEBENEFITS is not null
    begin
      set @PERCENTAGEBENEFITS = 
        (
          select
            coalesce(REVENUEBENEFITID, newID()) as ID,
            BENEFITID,
            PERCENTAPPLICABLEAMOUNT,
            VALUEPERCENT,
            TOTALVALUE,
            DETAILS,
            SEQUENCE
          from 
            dbo.UFN_REVENUEBATCH_GETBENEFITSPCT_FROMITEMLISTXML(@PERCENTAGEBENEFITS)  
          for xml raw('ITEM'),type,elements,root('PERCENTAGEBENEFITS'),binary base64
        )
    end    

        if @ISPOSTED = 0
        begin
          exec dbo.USP_DATAFORMTEMPLATE_EDIT_REVENUEBENEFITS2
            @ID = @REVENUEID,
            @CHANGEAGENTID = @CHANGEAGENTID,
            @BENEFITS = @BENEFITS,
            @BENEFITSWAIVED = @BENEFITSWAIVED,
            @PERCENTAGEBENEFITS = @PERCENTAGEBENEFITS,
            @ADJUSTMATCHINGGIFTCLAIMS = 3,
            @UPDATEGIFTFEEOPTION = 0
        end
        else
        begin
          exec dbo.USP_DATAFORMTEMPLATE_ADJUST_REVENUEBENEFITS_2
            @ID = @REVENUEID,
            @CHANGEAGENTID = @CHANGEAGENTID,
            @BENEFITS = @BENEFITS,
            @BENEFITSWAIVED = @BENEFITSWAIVED,
            @ADJUSTMENTDATE = @ADJUSTMENTDATE,
            @ADJUSTMENTPOSTDATE = @ADJUSTMENTPOSTDATE,
            @ADJUSTMENTREASON = @ADJUSTMENTREASON,
            @ADJUSTMENTREASONCODEID = @ADJUSTMENTREASONCODEID,
            @PERCENTAGEBENEFITS = @PERCENTAGEBENEFITS,
            @ADJUSTMENTPOSTSTATUSCODE = @ADJUSTMENTPOSTSTATUSCODE
        end
    end

    -- handle matching gifts

    declare @DELETEDMGID uniqueidentifier;
    if @MATCHINGGIFTS is not null
    begin
      if exists(
        select 1 
        from
          @MATCHINGGIFTS.nodes('MATCHINGGIFTS/ITEM') as MATCHINGGIFTSXML([MATCHINGGIFT])
        where 
          MATCHINGGIFTSXML.[MATCHINGGIFT].value('AMOUNT[1]', 'money') < 0
      )
        raiserror('BBERR_VALIDATE_MATCHINGGIFTSAMOUNT', 13, 1);

      declare @MGTABLE table
      (
        [ID] uniqueidentifier,
        [MATCHEDCONSTITUENTID] uniqueidentifier,
        [ORGANIZATIONID] uniqueidentifier,
        [RELATIONSHIPID] uniqueidentifier,
        [MATCHINGGIFTCONDITIONTYPEID] uniqueidentifier,
        [AMOUNT] money,
        [DATE] datetime,
        [SPLITS] xml,
        [NEWMGID] uniqueidentifier,
        [TRANSACTIONCURRENCYID] uniqueidentifier,
        [BASECURRENCYID] uniqueidentifier,
        [BASEEXCHANGERATEID] uniqueidentifier,
        [EXCHANGERATE] decimal(20,8),
        [HADSPOTRATE] bit,
        [RATECHANGED] bit,
        [OPPORTUNITYID] uniqueidentifier,
        [RESETSOLICITORCREDITS] bit
      );

      declare @MGID uniqueidentifier, @MGCONSTITUENTID uniqueidentifier, @MGORGANIZATIONID uniqueidentifier;
      declare @MGRELATIONSHIPID uniqueidentifier, @MGCONDITIONTYPEID uniqueidentifier, @MGAMOUNT money;
      declare @MGDATE datetime, @MGSPLITS xml, @MGNEWID uniqueidentifier; 
      declare @MGTRANSACTIONCURRENCYID uniqueidentifier;
      declare @MGBASECURRENCYID uniqueidentifier;
      declare @MGBASEEXCHANGERATEID uniqueidentifier;
      declare @MGEXCHANGERATE decimal(20,8);
      declare @MGHADSPOTRATE bit;
      declare @MGRATECHANGED bit;
      declare @MGOPPORTUNITYID uniqueidentifier;
      declare @MGRESETSOLICITORCREDITS bit;

      insert into @MGTABLE
      (
        [ID],
        [MATCHEDCONSTITUENTID],
        [ORGANIZATIONID],
        [RELATIONSHIPID],
        [MATCHINGGIFTCONDITIONTYPEID],
        [AMOUNT],
        [DATE],
        [SPLITS],
        [NEWMGID],
        [TRANSACTIONCURRENCYID],
        [BASECURRENCYID],
        [BASEEXCHANGERATEID],
        [EXCHANGERATE],
        [HADSPOTRATE],
        [RATECHANGED],
        [OPPORTUNITYID],
        [RESETSOLICITORCREDITS]
      )
      select
        MATCHINGGIFTSXML.[MATCHINGGIFT].value('REVENUEMATCHINGGIFTID[1]', 'uniqueidentifier'),
        @CONSTITUENTID,
        MATCHINGGIFTSXML.[MATCHINGGIFT].value('ORGANIZATIONID[1]', 'uniqueidentifier'),
        MATCHINGGIFTSXML.[MATCHINGGIFT].value('RELATIONSHIPID[1]', 'uniqueidentifier'),
        MATCHINGGIFTSXML.[MATCHINGGIFT].value('MATCHINGGIFTCONDITIONID[1]', 'uniqueidentifier'),
        MATCHINGGIFTSXML.[MATCHINGGIFT].value('AMOUNT[1]', 'money'),
        MATCHINGGIFTSXML.[MATCHINGGIFT].value('DATE[1]', 'datetime'),
        MATCHINGGIFTSXML.[MATCHINGGIFT].query('SPLITS'),
        newID(),
        MATCHINGGIFTSXML.[MATCHINGGIFT].value('TRANSACTIONCURRENCYID[1]', 'uniqueidentifier'),
        REVENUE.BASECURRENCYID,
        MATCHINGGIFTSXML.[MATCHINGGIFT].value('BASEEXCHANGERATEID[1]', 'uniqueidentifier'),
        MATCHINGGIFTSXML.[MATCHINGGIFT].value('EXCHANGERATE[1]', 'decimal(20, 8)'),
        case
          when CURRENCYEXCHANGERATE.TYPECODE = 2
            then 1
          else 
            0
        end HADSPOTRATE,
        0 RATECHANGED,
        MATCHINGGIFTSXML.[MATCHINGGIFT].value('OPPORTUNITYID[1]', 'uniqueidentifier'),
        MATCHINGGIFTSXML.[MATCHINGGIFT].value('RESETSOLICITORCREDITS[1]', 'bit')
      from 
        @MATCHINGGIFTS.nodes('MATCHINGGIFTS/ITEM') as MATCHINGGIFTSXML([MATCHINGGIFT])
      left outer join dbo.REVENUE on REVENUE.ID =  MATCHINGGIFTSXML.[MATCHINGGIFT].value('REVENUEMATCHINGGIFTID[1]', 'uniqueidentifier')
      left outer join dbo.CURRENCYEXCHANGERATE on CURRENCYEXCHANGERATE.ID = REVENUE.BASEEXCHANGERATEID

      --delete existing matching gifts that aren't in the collection


      declare DELETEDMGCURSOR cursor local fast_forward for
        select
          REVENUEMATCHINGGIFT.ID
        from
          dbo.REVENUEMATCHINGGIFT
          left outer join @MGTABLE as MGTABLE on MGTABLE.ID = REVENUEMATCHINGGIFT.ID
        where
          REVENUEMATCHINGGIFT.MGSOURCEREVENUEID = @REVENUEID
          and MGTABLE.ID is null
          and dbo.UFN_PLEDGE_PAYMENTSEXIST(REVENUEMATCHINGGIFT.ID) = 0;

      open DELETEDMGCURSOR
      fetch next from DELETEDMGCURSOR into @DELETEDMGID;
      while @@fetch_status = 0
      begin
        exec dbo.USP_REVENUE_DELETE @DELETEDMGID, @CHANGEAGENTID;
        fetch next from DELETEDMGCURSOR into @DELETEDMGID;
      end  -- loop through MG records


      close DELETEDMGCURSOR;
      deallocate DELETEDMGCURSOR;

     --done delete


      declare MGCURSOR cursor local fast_forward for
        select
          MGTABLE.ID,
          MGTABLE.MATCHEDCONSTITUENTID,
          MGTABLE.ORGANIZATIONID,
          MGTABLE.RELATIONSHIPID,
          MGTABLE.MATCHINGGIFTCONDITIONTYPEID,
          MGTABLE.AMOUNT,
          MGTABLE.DATE,
          MGTABLE.SPLITS, 
          MGTABLE.NEWMGID,
          MGTABLE.TRANSACTIONCURRENCYID, 
          MGTABLE.BASECURRENCYID,
          MGTABLE.BASEEXCHANGERATEID,
          MGTABLE.EXCHANGERATE,
          MGTABLE.HADSPOTRATE,
          MGTABLE.RATECHANGED,
          MGTABLE.OPPORTUNITYID,
          MGTABLE.RESETSOLICITORCREDITS
        from 
          @MGTABLE as MGTABLE;

      declare @MGSPLITSTABLE table
      (
        MGNEWID uniqueidentifier,
        SPLITID uniqueidentifier,
        AMOUNT money,
        DESIGNATIONID uniqueidentifier, 
        TRANSACTIONCURRENCYID uniqueidentifier
      );

      insert into @MGSPLITSTABLE (MGNEWID, SPLITID, AMOUNT, DESIGNATIONID, TRANSACTIONCURRENCYID)
      select
        [MGTABLE].NEWMGID,
        coalesce(T.c.value('REVENUESPLITID[1]','uniqueidentifier'), T.c.value('ID[1]','uniqueidentifier')),
        T.c.value('AMOUNT[1]', 'money'),
        T.c.value('DESIGNATIONID[1]', 'uniqueidentifier'),
        [MGTABLE].TRANSACTIONCURRENCYID
      from 
        @MGTABLE as [MGTABLE]
      cross apply MGTABLE.[SPLITS].nodes('SPLITS/ITEM') as T(c) 

      declare @MGSPLITS_2 xml;

      open MGCURSOR
      fetch next from MGCURSOR into @MGID, @MGCONSTITUENTID, @MGORGANIZATIONID, @MGRELATIONSHIPID, @MGCONDITIONTYPEID, @MGAMOUNT,@MGDATE, @MGSPLITS, @MGNEWID, @MGTRANSACTIONCURRENCYID, @MGBASECURRENCYID, @MGBASEEXCHANGERATEID,  @MGEXCHANGERATE, @MGHADSPOTRATE, @MGRATECHANGED, @MGOPPORTUNITYID, @MGRESETSOLICITORCREDITS
      while @@fetch_status = 0
      begin
        set @MGSPLITS_2 = 
          (
            select  
              MGST.SPLITID as ID, 
              MGST.DESIGNATIONID,
              MGST.AMOUNT, 
              coalesce(SPLITS.APPLICATIONCODE, 0) as APPLICATIONCODE,  
              coalesce(SPLITS.TYPECODE, 0) as TYPECODE,
              MGST.TRANSACTIONCURRENCYID
            from 
              @MGSPLITSTABLE MGST
            left outer join dbo.REVENUESPLIT SPLITS on SPLITS.ID = MGST.SPLITID
            where 
              MGST.MGNEWID = @MGNEWID
            for xml raw('ITEM'),type,elements,root('SPLITS'),BINARY BASE64
          );

        -- add matching gift record if the MGID is not filled in

        if @MGID is null
        begin
          exec dbo.USP_DATAFORMTEMPLATE_ADD_MGPLEDGE_2
            @ID = @MGNEWID output,
            @CHANGEAGENTID = @CHANGEAGENTID,
            @ORIGINALGIFTID = @REVENUEID,
            @MATCHINGORGANIZATIONID = @MGORGANIZATIONID,
            @DATE = @MGDATE,
            @AMOUNT = @MGAMOUNT,
            @SPLITS = @MGSPLITS_2,
            @MATCHINGGIFTCONDITIONID = @MGCONDITIONTYPEID,
            @RELATIONSHIPID = @MGRELATIONSHIPID,
            @CURRENTAPPUSERID = @CURRENTAPPUSERID,
            @TRANSACTIONCURRENCYID = @MGTRANSACTIONCURRENCYID,
            @BASEEXCHANGERATEID = @BASEEXCHANGERATEID,
            @EXCHANGERATE = @EXCHANGERATE

          if @MGOPPORTUNITYID is not null
          begin
            exec dbo.USP_DATAFORMTEMPLATE_ADD_REVENUEOPPORTUNITYLINK null, @MGNEWID, @MGOPPORTUNITYID, @CHANGEAGENTID, @MGRESETSOLICITORCREDITS
          end
        end
        else
        begin
          exec dbo.USP_DATAFORMTEMPLATE_EDIT_MGPLEDGE_4
            @ID = @MGID,
            @CHANGEAGENTID = @CHANGEAGENTID,
            @ORIGINALGIFTID = @REVENUEID,
            @MATCHINGORGANIZATIONID = @MGORGANIZATIONID,
            @DATE = @MGDATE,
            @AMOUNT = @MGAMOUNT,
            @SPLITS = @MGSPLITS_2,
            @MATCHINGGIFTCONDITIONID = @MGCONDITIONTYPEID,
            @RELATIONSHIPID = @MGRELATIONSHIPID,
            @BASECURRENCYID = @MGBASECURRENCYID,
            @TRANSACTIONCURRENCYID = @MGTRANSACTIONCURRENCYID,
            @BASEEXCHANGERATEID = @MGBASEEXCHANGERATEID,
            @EXCHANGERATE = @MGEXCHANGERATE,
            @HADSPOTRATE = @MGHADSPOTRATE,
            @RATECHANGED = @MGRATECHANGED

          if exists(select 1 from dbo.REVENUEOPPORTUNITY inner join @MGSPLITSTABLE as MGST on MGST.SPLITID = REVENUEOPPORTUNITY.ID where MGST.MGNEWID = @MGNEWID)
          begin
            if @MGOPPORTUNITYID is not null
            begin
              exec dbo.USP_REVENUEOPPORTUNITY_UPDATEOPPORTUNITY @REVENUEID = @MGID, @OPPORTUNITYID = @MGOPPORTUNITYID;
            end
            else
            begin
              exec dbo.USP_RECORDOPERATION_REVENUEOPPORTUNITYUNLINK @MGID, @CHANGEAGENTID;
            end
          end
          else
          begin
            if @MGOPPORTUNITYID is not null
            begin
              exec dbo.USP_DATAFORMTEMPLATE_ADD_REVENUEOPPORTUNITYLINK null, @MGID, @MGOPPORTUNITYID, @CHANGEAGENTID, @MGRESETSOLICITORCREDITS
            end
          end

        end -- edit MG


        fetch next from MGCURSOR into @MGID, @MGCONSTITUENTID, @MGORGANIZATIONID, @MGRELATIONSHIPID, @MGCONDITIONTYPEID, @MGAMOUNT,@MGDATE, @MGSPLITS, @MGNEWID, @MGTRANSACTIONCURRENCYID, @MGBASECURRENCYID, @MGBASEEXCHANGERATEID,  @MGEXCHANGERATE, @MGHADSPOTRATE, @MGRATECHANGED, @MGOPPORTUNITYID, @MGRESETSOLICITORCREDITS
      end  -- loop through MG records


      close MGCURSOR;
      deallocate MGCURSOR;
    end -- MG xml is not null

    else
    begin

      --delete all existing matching gifts


      declare DELETEDMGCURSOR cursor local fast_forward for
        select
          REVENUEMATCHINGGIFT.ID
        from
          dbo.REVENUEMATCHINGGIFT
        where
          REVENUEMATCHINGGIFT.MGSOURCEREVENUEID = @REVENUEID
          and dbo.UFN_PLEDGE_PAYMENTSEXIST(REVENUEMATCHINGGIFT.ID) = 0;

      open DELETEDMGCURSOR
      fetch next from DELETEDMGCURSOR into @DELETEDMGID;
      while @@fetch_status = 0
      begin
        exec dbo.USP_REVENUE_DELETE @DELETEDMGID, @CHANGEAGENTID;
        fetch next from DELETEDMGCURSOR into @DELETEDMGID;
      end  -- loop through MG records


      close DELETEDMGCURSOR;
      deallocate DELETEDMGCURSOR;

    end

  /*Begin*/

    declare @NEWAPPLICATIONRECOGNITIONS table
    (
      APPLICATIONID uniqueidentifier,
      APPLICATIONRECOGNITIONS xml,
      REVENUESPLITID uniqueidentifier,
      REVENUESPLITAMOUNT money,
      PERCENTAPPLIED decimal(20, 2),
      REVENUESPLITRECOGNITIONS xml
    );

    /*
    Include all commitment types available, not just through INSTALLMENTSPLITPAYMENT
    */

    insert into
      @NEWAPPLICATIONRECOGNITIONS
    select
      RECTABLE.APPLICATIONID as APPLICATIONID,
      RECTABLE.RECOGNITIONS as APPLICATIONRECOGNITIONS,
      REVENUESPLIT.ID,
      REVENUESPLIT.AMOUNT,
      0,
      null as REVENUESPLITRECOGNITIONS
    from
      @RECOGNITIONSTABLE as RECTABLE
    inner join
      (select distinct PLEDGEID,PAYMENTID from dbo.INSTALLMENTSPLITPAYMENT) ISP on ISP.PLEDGEID = RECTABLE.APPLICATIONID
    inner join
      dbo.REVENUESPLIT on REVENUESPLIT.ID = ISP.PAYMENTID and REVENUESPLIT.REVENUEID = @REVENUEID
    where
      not exists (select 1 from dbo.REVENUESPLIT where ID = RECTABLE.REVENUESPLITID)

    union all

    select
      RECTABLE.APPLICATIONID as APPLICATIONID,
      RECTABLE.RECOGNITIONS as APPLICATIONRECOGNITIONS,
      REVENUESPLIT.ID,
      REVENUESPLIT.AMOUNT,
      0,
      null as REVENUESPLITRECOGNITIONS
    from
      @RECOGNITIONSTABLE as RECTABLE
    inner join
      dbo.EVENTREGISTRANTPAYMENT as ERP on ERP.REGISTRANTID = RECTABLE.APPLICATIONID
    inner join
      dbo.REVENUESPLIT on REVENUESPLIT.ID = ERP.PAYMENTID and REVENUESPLIT.REVENUEID = @REVENUEID
    where
      not exists (select 1 from dbo.REVENUESPLIT where ID = RECTABLE.REVENUESPLITID)

    union all

    select
      RECTABLE.APPLICATIONID as APPLICATIONID,
      RECTABLE.RECOGNITIONS as APPLICATIONRECOGNITIONS,
      REVENUESPLIT.ID,
      REVENUESPLIT.AMOUNT,
      0,
      null as REVENUESPLITRECOGNITIONS
    from
      @RECOGNITIONSTABLE as RECTABLE
    inner join
      dbo.RECURRINGGIFTACTIVITY as RGA on RGA.SOURCEREVENUEID = RECTABLE.APPLICATIONID
    inner join
      dbo.REVENUESPLIT on REVENUESPLIT.ID = RGA.PAYMENTREVENUEID and REVENUESPLIT.REVENUEID = @REVENUEID
    where
      not exists (select 1 from dbo.REVENUESPLIT where ID = RECTABLE.REVENUESPLITID)

    /*
    Prorate these amounts properly (add money to last row)... per constituent
    */
    update
      AR
    set
      AR.PERCENTAPPLIED = (AR.REVENUESPLITAMOUNT / SUBQ.APPLICATIONAMOUNT)
    from
      @NEWAPPLICATIONRECOGNITIONS as AR
    inner join
    (
      select
        APPLICATIONID,
        sum(REVENUESPLITAMOUNT) APPLICATIONAMOUNT
      from
        @NEWAPPLICATIONRECOGNITIONS
      group by
        APPLICATIONID
    ) SUBQ on SUBQ.APPLICATIONID = AR.APPLICATIONID


    update
      AR
    set
      AR.REVENUESPLITRECOGNITIONS = 
      (
        select 
          REVENUERECOGNITIONID as ID,
          CONSTITUENTID, 
          AMOUNT * AR.PERCENTAPPLIED as AMOUNT, 
          EFFECTIVEDATE, 
          REVENUERECOGNITIONTYPECODEID, 
          RECOGNITIONCREDITDESIGNATIONID as DESIGNATIONID, 
          RECOGNITIONCREDITFKID, 
          DONORCHALLENGERECOGNITIONTYPECODE
        from 
          dbo.UFN_REVENUEUPDATEBATCH_GETRECOGNITIONSFOREDIT_FROMITEMLISTXML(AR.APPLICATIONRECOGNITIONS) REC
        for xml raw('ITEM'),type,elements,root('RECOGNITIONS'),binary base64
        )
    from
      @NEWAPPLICATIONRECOGNITIONS as AR


    delete from @RECOGNITIONSTABLE where APPLICATIONID in (select APPLICATIONID from @NEWAPPLICATIONRECOGNITIONS)

    insert into @RECOGNITIONSTABLE(REVENUESPLITID, RECOGNITIONS)
    select
      REVENUESPLITID,
      REVENUESPLITRECOGNITIONS
    from
      @NEWAPPLICATIONRECOGNITIONS


  /*End*/

    declare @NEWAPPLICATIONSOLICITORS table
    (
      APPLICATIONID uniqueidentifier,
      APPLICATIONSOLICITORS xml,
      REVENUESPLITID uniqueidentifier,
      REVENUESPLITAMOUNT money,
      PERCENTAPPLIED decimal(20, 2),
      REVENUESPLITSOLICITORS xml
    );

    /*
    Include all commitment types available, not just through INSTALLMENTSPLITPAYMENT
    */

    insert into
      @NEWAPPLICATIONSOLICITORS
    select
      SOLTABLE.APPLICATIONID as APPLICATIONID,
      SOLTABLE.SOLICITORS as APPLICATIONSOLICITORS,
      REVENUESPLIT.ID,
      REVENUESPLIT.AMOUNT,
      0,
      null as REVENUESPLITSOLICITORS
    from
      @SOLICITORSTABLE as SOLTABLE
    inner join
      (select distinct PLEDGEID,PAYMENTID from dbo.INSTALLMENTSPLITPAYMENT) ISP on ISP.PLEDGEID = SOLTABLE.APPLICATIONID
    inner join
      dbo.REVENUESPLIT on REVENUESPLIT.ID = ISP.PAYMENTID and REVENUESPLIT.REVENUEID = @REVENUEID
    where
      not exists (select 1 from dbo.REVENUESPLIT where ID = SOLTABLE.REVENUESPLITID)

    union all

    select
      SOLTABLE.APPLICATIONID as APPLICATIONID,
      SOLTABLE.SOLICITORS as APPLICATIONSOLICITORS,
      REVENUESPLIT.ID,
      REVENUESPLIT.AMOUNT,
      0,
      null as REVENUESPLITSOLICITORS
    from
      @SOLICITORSTABLE as SOLTABLE
    inner join
      dbo.EVENTREGISTRANTPAYMENT as ERP on ERP.REGISTRANTID = SOLTABLE.APPLICATIONID
    inner join
      dbo.REVENUESPLIT on REVENUESPLIT.ID = ERP.PAYMENTID and REVENUESPLIT.REVENUEID = @REVENUEID
    where
      not exists (select 1 from dbo.REVENUESPLIT where ID = SOLTABLE.REVENUESPLITID)

    union all

    select
      SOLTABLE.APPLICATIONID as APPLICATIONID,
      SOLTABLE.SOLICITORS as APPLICATIONSOLICITORS,
      REVENUESPLIT.ID,
      REVENUESPLIT.AMOUNT,
      0,
      null as REVENUESPLITSOLICITORS
    from
      @SOLICITORSTABLE as SOLTABLE
    inner join
      dbo.RECURRINGGIFTACTIVITY as RGA on RGA.SOURCEREVENUEID = SOLTABLE.APPLICATIONID
    inner join
      dbo.REVENUESPLIT on REVENUESPLIT.ID = RGA.PAYMENTREVENUEID and REVENUESPLIT.REVENUEID = @REVENUEID
    where
      not exists (select 1 from dbo.REVENUESPLIT where ID = SOLTABLE.REVENUESPLITID)

    /*
    Prorate these amounts properly (add money to last row)... per constituent
    */
    update
      APS
    set
      APS.PERCENTAPPLIED = (APS.REVENUESPLITAMOUNT / SUBQ.APPLICATIONAMOUNT)
    from
      @NEWAPPLICATIONSOLICITORS as APS
    inner join
    (
      select
        APPLICATIONID,
        sum(REVENUESPLITAMOUNT) APPLICATIONAMOUNT
      from
        @NEWAPPLICATIONSOLICITORS
      group by
        APPLICATIONID
    ) SUBQ on SUBQ.APPLICATIONID = APS.APPLICATIONID


    update
      APS
    set
      APS.REVENUESPLITSOLICITORS = 
      (
        select 
          REVENUESOLICITORID as ID,
          CONSTITUENTID, 
          AMOUNT * APS.PERCENTAPPLIED as AMOUNT,
          APPLICATIONCODE,
          DESIGNATIONID,
          SEQUENCE
        from 
          dbo.UFN_REVENUEUPDATEBATCH_GETSOLICITORSFOREDIT_FROMITEMLISTXML(APS.APPLICATIONSOLICITORS) SOL
        for xml raw('ITEM'),type,elements,root('SOLICITORS'),binary base64
        )
    from
      @NEWAPPLICATIONSOLICITORS as APS


    delete from @SOLICITORSTABLE where APPLICATIONID in (select APPLICATIONID from @NEWAPPLICATIONSOLICITORS)

    insert into @SOLICITORSTABLE(REVENUESPLITID, SOLICITORS)
    select
      REVENUESPLITID,
      REVENUESPLITSOLICITORS
    from
      @NEWAPPLICATIONSOLICITORS


  /*End*/


    -- handle recognition credits

    declare @RECREVENUESPLITID uniqueidentifier
    declare @RECRECOGNITIONS xml
    declare RECOGNITIONSCURSOR cursor local fast_forward for
    --RECOGNITIONS where predicted based on REVENUESTREAMS, but may not be the actuals

    --some defaults may have been created, or existing ones deleted/recreated

    with CTE_RECOGNITIONS as 
    (
     select distinct ACC.REVENUESPLITID 
     from   
     dbo.UFN_REVENUE_GETRECOGNITIONS_FORREVENUE(@REVENUEID) as ACC  
     where  
      --Do not overwrite predicted splits  

      ACC.REVENUESPLITID not in (select REVENUESPLITID from @RECOGNITIONSTABLE)
    )
    select   
      REVENUESPLITID,   
      RECOGNITIONS
    from   
      @RECOGNITIONSTABLE RCTABLE  
    where  
      --Can only edit records that exist  

    exists (select 1 from dbo.REVENUESPLIT where ID = RCTABLE.REVENUESPLITID)
    union all     
    --Get any distinct new ones that were created by default so they don't get deleted.

    select   
      REVENUESPLITID,  
      dbo.UFN_REVENUE_GETRECOGNITIONS_2_TOITEMLISTXML(REVENUESPLITID) as RECOGNITIONS
    from   
      CTE_RECOGNITIONS;

    open RECOGNITIONSCURSOR
    fetch next from RECOGNITIONSCURSOR into @RECREVENUESPLITID, @RECRECOGNITIONS

    while @@FETCH_STATUS = 0
    begin
      exec dbo.USP_REVENUEUPDATEBATCH_RECOGNITIONS_CREATECONSTITUENTS @RECRECOGNITIONS output, @CHANGEAGENTID

      if @ISRECURRINGGIFT = 1
      begin      
        exec dbo.USP_DATAFORMTEMPLATE_EDIT_REVENUE_RECOGNITIONRECURRINGGIFT
          @ID = @RECREVENUESPLITID,
          @CHANGEAGENTID = @CHANGEAGENTID,
          @RECOGNITIONS = @RECRECOGNITIONS
      end
      else
      begin
        if dbo.UFN_REVENUESPLIT_MATCHEDBYDONORCHALLENGE(@RECREVENUESPLITID) = 1
        begin
          --If the new rows do not have RECOGNITIONCREDITFKID or ID they won't be added (e.g. if credits were reset)

          set @RECRECOGNITIONS = 
          (
            select 
            [AMOUNT], 
            [BASECURRENCYID], 
            [CONSTITUENT], 
            [CONSTITUENTID], 
            [EFFECTIVEDATE], 
            [GROSSAMOUNT], 
            [DESIGNATIONID], 
            case 
              when RECOGNITIONCREDITFKID is null and ID is null then 
              newid() 
              else 
              ID 
            end [ID], 
            [RECOGNITIONTYPE], 
            [REVENUERECOGNITIONTYPECODEID],
            case 
              when RECOGNITIONCREDITFKID is null then 
                @RECREVENUESPLITID 
              else 
                RECOGNITIONCREDITFKID 
            end [RECOGNITIONCREDITFKID],
            case 
              when RECOGNITIONCREDITFKID is null then 
                0 
              else
                DONORCHALLENGERECOGNITIONTYPECODE 
            end [DONORCHALLENGERECOGNITIONTYPECODE] 
            from 
            dbo.UFN_RECOGNITIONCREDIT_DONORCHALLENGE_GETRECOGNITIONS_FROMITEMLISTXML(@RECRECOGNITIONS)
            order by 
            EFFECTIVEDATE asc, CONSTITUENT asc
            for xml raw('ITEM'),type,elements,root('RECOGNITIONS'),BINARY BASE64
          )

          exec dbo.USP_DATAFORMTEMPLATE_EDIT_RECOGNITIONCREDIT_DONORCHALLENGE
            @ID = @RECREVENUESPLITID,
            @CHANGEAGENTID = @CHANGEAGENTID,
            @RECOGNITIONS = @RECRECOGNITIONS
        end
        else
          exec dbo.USP_DATAFORMTEMPLATE_EDIT_REVENUE_RECOGNITION
            @ID = @RECREVENUESPLITID,
            @CHANGEAGENTID = @CHANGEAGENTID,
            @RECOGNITIONS = @RECRECOGNITIONS
      end

      fetch next from RECOGNITIONSCURSOR into @RECREVENUESPLITID, @RECRECOGNITIONS
    end  

    close RECOGNITIONSCURSOR
    deallocate RECOGNITIONSCURSOR

    -- solicitors

    declare @SOLREVENUESPLITID uniqueidentifier
    declare @SOL xml

    declare SOLICITORSCURSOR cursor local fast_forward for
    select 
      REVENUESPLITID, 
      SOLICITORS
    from 
      @SOLICITORSTABLE

    open SOLICITORSCURSOR
    fetch next from SOLICITORSCURSOR into @SOLREVENUESPLITID, @SOL
    while @@FETCH_STATUS = 0
    begin
      exec dbo.USP_DATAFORMTEMPLATE_EDIT_REVENUESOLICITORS
        @ID = @SOLREVENUESPLITID,
        @CHANGEAGENTID = @CHANGEAGENTID,
        @SOLICITORS = @SOL

      fetch next from SOLICITORSCURSOR into @SOLREVENUESPLITID, @SOL
    end

    close SOLICITORSCURSOR
    deallocate SOLICITORSCURSOR

    -- handle business units

    declare @BUSINESSUNITCOUNT int
    select 
      @BUSINESSUNITCOUNT = count(*) 
    from 
      @APPLICATIONBUSINESSUNITS.nodes('/APPLICATIONBUSINESSUNITS/ITEM') T(c)

    if @BUSINESSUNITCOUNT > 0
    begin
      exec dbo.USP_REVENUEBATCH_APPLYBUSINESSUNITS @REVENUEID, @APPLICATIONBUSINESSUNITS, @TYPECODE, @CHANGEAGENTID, null, @REVENUESTREAMS, @ADDITIONALAPPLICATIONSSTREAM;
    end

    -- handle letters

    if @LETTERS is not null
    begin
      -- reset the ID for the letters xml

      set @LETTERS = 
        (
          select 
            ACKNOWLEDGEDATE,
            ACKNOWLEDGEEID,
            LETTERCODEID,
            LETTERTYPECODE,
            REVENUELETTERID as ID,
            TRIBUTEID, 
            OUTOFDATE,
            PROCESSDATE,
            CLEARDATES
          from 
            dbo.UFN_REVENUEUPDATEBATCH_GETLETTERS_FROMITEMLISTXML(@LETTERS)
          for xml raw('ITEM'),type,elements,root('LETTERS'),binary base64
        )
    end

    exec dbo.USP_REVENUE_LETTERS_CUSTOMUPDATEFROMXML
      @REVENUEID = @REVENUEID, 
      @LETTERS = @LETTERS, 
      @CHANGEAGENTID = @CHANGEAGENTID

    -- handle notes

    exec dbo.USP_REVENUEUPDATEBATCH_NOTE_CUSTOMUPDATEFORITEMLISTXML
      @REVENUEID = @REVENUEID, 
      @NOTES = @NOTES,
      @CHANGEAGENTID = @CHANGEAGENTID,
      @CHANGEDATE = @CURRENTDATE

    -- handle VAT edits, for UK only

    if dbo.UFN_INSTALLEDPRODUCTS_PRODUCTIS('9568A6C2-F7AA-45fd-8F54-21FE9654EE2D') = 1
    begin
 exec dbo.USP_DATAFORMTEMPLATE_EDIT_REVENUEVAT 
        @ID = @REVENUEID,
        @CHANGEAGENTID = @CHANGEAGENTID,
        @AMOUNTTOTAX = @AMOUNTFORVAT,
        @VATTAXRATEID = @VATTAXRATEID,
        @VATAMOUNT = @VATAMOUNT
    end;

    -- handle receipttypecode updates

    if @PREVIOUSRECEIPTTYPECODE <> @RECEIPTTYPECODE
    begin
      update
        dbo.REVENUE
      set
        RECEIPTTYPECODE = @RECEIPTTYPECODE,
        CHANGEDBYID = @CHANGEAGENTID,
        DATECHANGED = @CURRENTDATE
      where
        ID = @REVENUEID;
    end;

    -- handle gift fees

    if @SHOULDUPDATEGIFTFEE > 0
    begin
      declare @GIFTFEEADJUSTMENTID uniqueidentifier;      
      if @PREVIOUSCONSTITUENTID <> @CONSTITUENTID
      begin
        if @ISPOSTED = 1
          exec dbo.USP_SAVE_GIFTFEEADJUSTMENT @REVENUEID, @GIFTFEEADJUSTMENTID output, @CHANGEAGENTID, @CURRENTDATE, @ADJUSTMENTDATE, @ADJUSTMENTPOSTDATE, @ADJUSTMENTREASON, default, @ADJUSTMENTREASONCODEID, @ADJUSTMENTPOSTSTATUSCODE;

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

        if not @CHANGEAGENTID is null
          set CONTEXT_INFO @CHANGEAGENTID;

        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 = @REVENUEID 
              and GIFTFEEGLDISTRIBUTION.OUTDATED = 0
          );

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

        if not @contextCache is null
          set CONTEXT_INFO @contextCache;

        exec dbo.USP_PAYMENT_ADDGIFTFEES @REVENUEID, @CONSTITUENTID, @CHANGEAGENTID, @CURRENTDATE;
      end
      else 
      begin
        if @ISPOSTED = 1          
          exec dbo.USP_SAVE_GIFTFEEADJUSTMENT @REVENUEID, @GIFTFEEADJUSTMENTID output, @CHANGEAGENTID, @CURRENTDATE, @ADJUSTMENTDATE, @ADJUSTMENTPOSTDATE, @ADJUSTMENTREASON, default, @ADJUSTMENTREASONCODEID, @ADJUSTMENTPOSTSTATUSCODE;

        exec dbo.USP_REVENUE_UPDATEPAYMENTGIFTFEE @REVENUEID, @CONSTITUENTID, @CHANGEAGENTID

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

        if @POSTSTATUSCODE = 1 or ((@POSTSTATUSCODE is null or @POSTSTATUSCODE = 0) and @ADJUSTMENTPOSTSTATUSCODE <> 2)
          exec dbo.USP_SAVE_GIFTFEEGLDISTRIBUTION @REVENUEID, @CHANGEAGENTID, @CURRENTDATE;
      end
    end
    else if (@ISPOSTED = 0 and @OLDPOSTSTATUSCODE <> @POSTSTATUSCODE) or (@ISPOSTED = 1 and @OLDPOSTSTATUSCODE <> @ADJUSTMENTPOSTSTATUSCODE)
    begin
      delete from dbo.GIFTFEEGLDISTRIBUTION where REVENUEID = @REVENUEID and OUTDATED = 0;
      if @POSTSTATUSCODE = 1 or ((@POSTSTATUSCODE is null or @POSTSTATUSCODE = 0) and @ADJUSTMENTPOSTSTATUSCODE <> 2)
        exec dbo.USP_SAVE_GIFTFEEGLDISTRIBUTION @REVENUEID, @CHANGEAGENTID, @CURRENTDATE;
    end

    -- handle constituent update at the end

    if @PREVIOUSCONSTITUENTID <> @CONSTITUENTID
    begin
      if @ISPOSTED = 0
      begin
        exec dbo.USP_DATAFORMTEMPLATE_EDITSAVE_REVENUETRANSACTIONCHANGECONSTITUENT_2
          @ID = @REVENUEID,
          @CHANGEAGENTID = @CHANGEAGENTID,
          @NEWCONSTITUENTID = @CONSTITUENTID ,
          @CONSTITUENTACCOUNTID = @CONSTITUENTACCOUNTID,
          @RESETRECOGNITIONCREDITS = @RELOADRECOGNITION,
          @RESETSOLICITORS = @RELOADSOLICITORS,
          @CURRENTAPPUSERID = @CURRENTAPPUSERID
      end
      else
      begin
        exec dbo.USP_DATAFORMTEMPLATE_EDITSAVE_REVENUETRANSACTIONCHANGECONSTITUENTPOSTED_3
          @ID = @REVENUEID,
          @CHANGEAGENTID = @CHANGEAGENTID,
          @NEWCONSTITUENTID = @CONSTITUENTID ,
          @CONSTITUENTACCOUNTID = @CONSTITUENTACCOUNTID,
          @ADJUSTMENTPOSTDATE = @ADJUSTMENTPOSTDATE,
          @ADJUSTMENTDATE = @ADJUSTMENTDATE,
          @ADJUSTMENTREASON = @ADJUSTMENTREASON,
          @RESETRECOGNITIONCREDITS = @RELOADRECOGNITION,
          @RESETSOLICITORS = @RELOADSOLICITORS,
          @ADJUSTMENTREASONCODEID = @ADJUSTMENTREASONCODEID
      end
    end

    -- required for control report to work

    set @ID = @REVENUEID

    update LI set
      BATCHID = @BATCHID
      ,CHANGEDBYID = @CHANGEAGENTID
      ,DATECHANGED = @CURRENTDATE
    from dbo.FINANCIALTRANSACTIONLINEITEM LI
    left join dbo.FINANCIALTRANSACTIONLINEITEM REVERSELI on REVERSELI.ID = LI.REVERSEDLINEITEMID and REVERSELI.FINANCIALTRANSACTIONLINEITEMADJUSTMENTID = LI.FINANCIALTRANSACTIONLINEITEMADJUSTMENTID
    where LI.FINANCIALTRANSACTIONID = @ID
      and (LI.CHANGEDBYID = @CHANGEAGENTID
        or REVERSELI.CHANGEDBYID = @CHANGEAGENTID)
      and LI.DELETEDON is null
      and LI.POSTSTATUSCODE != 2;

    update LI set
      BATCHID = @BATCHID
      ,CHANGEDBYID = @CHANGEAGENTID
      ,DATECHANGED = @CURRENTDATE
    from dbo.FINANCIALTRANSACTIONLINEITEM LI
    inner join dbo.FINANCIALTRANSACTION FT on FT.ID = LI.FINANCIALTRANSACTIONID
    left join dbo.FINANCIALTRANSACTIONLINEITEM REVERSELI on REVERSELI.ID = LI.REVERSEDLINEITEMID and REVERSELI.FINANCIALTRANSACTIONLINEITEMADJUSTMENTID = LI.FINANCIALTRANSACTIONLINEITEMADJUSTMENTID
    where FT.PARENTID = @ID
      and (LI.CHANGEDBYID = @CHANGEAGENTID
        or REVERSELI.CHANGEDBYID = @CHANGEAGENTID)
      and LI.DELETEDON is null
      and LI.POSTSTATUSCODE != 2;

  end try

  begin catch
    if CURSOR_STATUS('local','CAMPAIGNVALIDATECURSOR') >= -1
    begin
          close CAMPAIGNVALIDATECURSOR;
          deallocate CAMPAIGNVALIDATECURSOR;
    end

    if CURSOR_STATUS('local','CAMPAIGNCURSOR') >= -1
    begin
          close CAMPAIGNCURSOR;
          deallocate CAMPAIGNCURSOR;
    end

    if CURSOR_STATUS('local','DELETEDMGCURSOR') >= -1
    begin
          close DELETEDMGCURSOR;
          deallocate DELETEDMGCURSOR;
    end

    if CURSOR_STATUS('local','MGCURSOR') >= -1
    begin
          close MGCURSOR;
          deallocate MGCURSOR;
    end

    if CURSOR_STATUS('local','RECOGNITIONSCURSOR') >= -1
    begin
          close RECOGNITIONSCURSOR;
          deallocate RECOGNITIONSCURSOR;
    end

    if CURSOR_STATUS('local','SOLICITORSCURSOR') >= -1
    begin
          close SOLICITORSCURSOR;
          deallocate SOLICITORSCURSOR;
    end

    exec dbo.[USP_RAISE_ERROR];
    return 1;
  end catch

  return 0;