USP_DATAFORMTEMPLATE_ADD_REVENUEBATCHCOMMIT

CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_REVENUEBATCHCOMMIT
(
  @ID uniqueidentifier = null output,
  @CHANGEAGENTID uniqueidentifier,
  @BATCHNUMBER nvarchar(100),
  @CONSTITUENTID uniqueidentifier = null,
  @TYPECODE tinyint = 0,
  @DATE datetime,
  @AMOUNT money,
  @PAYMENTMETHODCODE tinyint = 0,
  @DONOTACKNOWLEDGE bit = 0,
  @DONOTRECEIPT 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,
  @INSTALLMENTFREQUENCYCODE tinyint = 5,
  @INSTALLMENTSTARTDATE datetime = null,
  @INSTALLMENTENDDATE datetime = null,
  @NUMBEROFINSTALLMENTS int = 1,
  @PROPERTYSUBTYPECODEID uniqueidentifier = null,
  @GIFTINKINDSUBTYPECODEID uniqueidentifier = null,
  @RECEIPTAMOUNT money = 0,
  @CONSTITUENTACCOUNTID uniqueidentifier = null,
  @SPLITS xml = null,
  @SINGLEDESIGNATIONID uniqueidentifier = null,
  @REVENUESTREAMS xml = null,
  @SOLICITORS xml = null,
  @BENEFITS xml = null,
  @FINDERNUMBER bigint = 0,
  @SOURCECODE nvarchar(60) = '',
  @APPEALID uniqueidentifier = null,
  @FINDERNUMBERISVALID bit = null,
  @USERMODIFIEDBENEFITS bit = 0,
  @BENEFITSWAIVED bit = 0,
  @POSTDATE datetime = null,
  @POSTSTATUSCODE tinyint = null,
  @SENDPLEDGEREMINDER bit = 1,
  @SALEDATE datetime = null,
  @SALEAMOUNT money = null,
  @BROKERFEE money = null,
  @SALEPOSTSTATUSCODE tinyint = null,
  @SALEPOSTDATE datetime = null,
  @NOTETITLE nvarchar(50) = '',
  @NOTEAUTHORID uniqueidentifier = null,
  @NOTEDATEENTERED datetime = null,
  @NOTETYPECODEID uniqueidentifier = null,
  @NOTETEXTNOTE nvarchar(max) = '',
  @MGMATCHINGCONSTITUENTID uniqueidentifier = null,
  @MGDATE datetime = null,
  @MGAMOUNT money = 0,
  @MGPOSTDATE datetime = null,
  @MGPOSTSTATUSCODE tinyint = 1,
  @MGCONDITIONID uniqueidentifier = null,
  @MGSPLITS xml = null,
  @GIVENANONYMOUSLY bit = 0,
  @GIVENANONYMOUSLYDEFAULTEDFORCONSTITUENTID uniqueidentifier = null,
  @USERMODIFIEDRECEIPTAMOUNT bit = 0,
  @PLEDGESUBTYPEID uniqueidentifier = null,
  @REJECTIONCODEID uniqueidentifier = null,
  @CONSTITUENTLOOKUPID uniqueidentifier = null,
  @MAILINGID uniqueidentifier = null,
  @CHANNELCODEID uniqueidentifier = null,
  @VALIDATEONLY bit = 0,
  @INSTALLMENTS xml = null,
  @PAYMENTFORPLEDGEAMOUNT money = null,
  @RECOGNITIONS xml = null,
  @DIDRECOGNITIONSDEFAULT bit = 1,
  @TRIBUTES xml = null,
  @UNAPPLIEDMATCHINGGIFTSPLITS xml = null,
  @UNAPPLIEDMATCHINGGIFTAMOUNT money = 0,
  @RECEIPTTYPECODE tinyint = null,
  @MGRELATIONSHIPID uniqueidentifier = null,
  @OTHERPAYMENTMETHODCODEID uniqueidentifier = null,
  @LETTERCODEID as uniqueidentifier = null,
  @ACKNOWLEDGEDATE as datetime = null,
  @REFERENCE as nvarchar(255) = null,
  @CATEGORYCODEID uniqueidentifier = null,
  @ACKNOWLEDGEEID uniqueidentifier = null,
  @APPLICATIONCODE tinyint = 0,
  @OTHERTYPECODEID uniqueidentifier = null,
  @OPPORTUNITYID uniqueidentifier = null,
  @DIRECTDEBITRESULTCODE nvarchar(10) = '',
  @LOWPRICE decimal(19,4) = null,
  @HIGHPRICE decimal(19,4) = null,
  @NUMBEROFUNITSSOLD decimal(20,3) = null,
  @USERMODIFIEDNUMBEROFUNITSSOLD bit = null,
  @CREDITCARDTOKEN uniqueidentifier = null,
  @REJECTIONMESSAGE nvarchar(250) = '',
  -- PARTIALCREDITCARDNUMBER is used just to support import and so is only 
  -- actually used on RevenueBatchDataForm.Add.xml
  @PARTIALCREDITCARDNUMBER nvarchar(4) = '',
  @STANDINGORDERCONSTITUENTACCOUNTID uniqueidentifier = null,
  @STANDINGORDERREFERENCENUMBER nvarchar(18) = '',
  @STANDINGORDERREFERENCEDATE dbo.UDT_FUZZYDATE = '00000000',
  @STANDINGORDERSETUP bit = null,
  @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,
  @CURRENTAPPUSERID uniqueidentifier = null,
  @PERCENTAGEBENEFITS xml = null,
  @BATCHROWID uniqueidentifier = null,
  @ISGIFTAIDSPONSORSHIP bit = null,
  @GENERATEREFERENCENUMBER bit = 1,
  @SOURCECODEIMPORT nvarchar(60) = '',
  @MERCHANTACCOUNTID uniqueidentifier = null
)
as
  set nocount on;

  declare @PDACCOUNTSYSTEMID uniqueidentifier
  declare @NEXTTRANSACTIONDATE datetime;

  --Multicurrency workaround for Fire: Set currency fields for all inserts and updates to avoid the trigger performance hit during batch commit (we are assuming this is a single currency system for Fire).
  declare @ORGANIZATIONCURRENCYID uniqueidentifier;
  set @ORGANIZATIONCURRENCYID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();

  declare @CURRENTDATE datetime;
  declare @APPLIEDTOPLEDGES money;
  declare @TRANSACTIONAMOUNT money;

  set @TRANSACTIONAMOUNT = @AMOUNT;

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

  if @PAYMENTFORPLEDGEAMOUNT is null
    set @PAYMENTFORPLEDGEAMOUNT = 0;

  if @PAYMENTMETHODCODE = 11 --Standing order
  begin
    set @CONSTITUENTACCOUNTID = @STANDINGORDERCONSTITUENTACCOUNTID;
    set @REFERENCEDATE = @STANDINGORDERREFERENCEDATE;

    if @CONSTITUENTACCOUNTID is not null and not exists (
      select 1 from dbo.CONSTITUENTACCOUNT
      where CONSTITUENTID = coalesce(@CONSTITUENTID,@CONSTITUENTLOOKUPID) and ID = @CONSTITUENTACCOUNTID
      union
      select 1 from dbo.BATCHREVENUECONSTITUENTACCOUNT
      where CONSTITUENTID = coalesce(@CONSTITUENTID,@CONSTITUENTLOOKUPID) and ID = @CONSTITUENTACCOUNTID
      )
      raiserror('BBERR_STANDINGORDERCONSTITUENTACCOUNTID_CONSTITUENT_NOTEQUAL',13,1)

    if @GENERATEREFERENCENUMBER is null or @GENERATEREFERENCENUMBER = 1
      select 
        @STANDINGORDERREFERENCENUMBER = '',
        @GENERATEREFERENCENUMBER = 1
  end

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

  --If the post status is null, set it to 'Not posted' so that the GL distributions will get created for the record.
  if @POSTSTATUSCODE is null
    set @POSTSTATUSCODE = 1;

  --JamesWill CR275936-052407 2007/05/29 Don't try to post recurring gifts
  if @TYPECODE = 3
    set @POSTSTATUSCODE = 2;

  if @POSTSTATUSCODE = 2 --Do not post
    set @POSTDATE = null
  else if @POSTDATE is null
    set @POSTDATE = dbo.UFN_DATE_GETEARLIESTTIME(getdate()); --JamesWill CR275936-052407 2007/05/29 Don't include time-stamp on postdate

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

  set @CURRENTDATE = GetDate();

  set @APPLIEDTOPLEDGES = 0;

  if @RECEIPTAMOUNT is null
    set @RECEIPTAMOUNT = 0;

  if @APPLICATIONCODE = 255 or @APPLICATIONCODE is null
    set @APPLICATIONCODE = 0;

  --We need to convert the MGSPLITS to regular SPLITS (involves changing the root from "MGSPLITS" to "SPLITS" and adding a "REVENUEID" element) 
  declare @MGSPLITSTABLE table
  (
    ID uniqueidentifier,
    DESIGNATIONID uniqueidentifier,
    AMOUNT money,
    SEQUENCE int
  );

  declare @SENDNEWINSTRUCTION bit;
  declare @NEWINSTRUCTIONTOSEND tinyint;

  begin try

    if @FINDERNUMBER is null
      set @FINDERNUMBER = 0;

    if coalesce(@REJECTIONMESSAGE, '') <> ''
    begin
      raiserror(@REJECTIONMESSAGE, 13, 1);
    end

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

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

    if @BENEFITSWAIVED = 0
    begin
      if @BENEFITS is not null
        begin
          if exists(select BENEFITID,QUANTITY,UNITVALUE
                    from dbo.UFN_REVENUEBATCH_GETBENEFITS_FROMITEMLISTXML(@BENEFITS)  
                    where QUANTITY<0)
            raiserror('ERR_REVENUEBENEFIT_QUANTITY_NONNEGATIVE', 13, 1);

          if exists(select BENEFITID,QUANTITY,UNITVALUE
                    from dbo.UFN_REVENUEBATCH_GETBENEFITS_FROMITEMLISTXML(@BENEFITS)  
                    where UNITVALUE<0)
            raiserror('ERR_REVENUEBENEFIT_VALUE_NONNEGATIVE', 13, 1);
        end

      if @PERCENTAGEBENEFITS is not null
          begin
            if exists(select BENEFITID, PERCENTAPPLICABLEAMOUNT, VALUEPERCENT
                      from dbo.UFN_REVENUEBATCH_GETBENEFITSPCT_FROMITEMLISTXML(@PERCENTAGEBENEFITS)
                      where PERCENTAPPLICABLEAMOUNT<0)
              raiserror('ERR_REVENUEBENEFIT_PERCENTAPPLICABLEAMOUNT_NONNEGATIVE', 13, 1);

            if exists(select BENEFITID, PERCENTAPPLICABLEAMOUNT, VALUEPERCENT
                      from dbo.UFN_REVENUEBATCH_GETBENEFITSPCT_FROMITEMLISTXML(@PERCENTAGEBENEFITS)
                      where VALUEPERCENT<0)
              raiserror('ERR_REVENUEBENEFIT_VALUEPERCENT_NONNEGATIVE', 13, 1);
          end

      if (@BENEFITS is not null) or (@PERCENTAGEBENEFITS is not null)
        exec dbo.USP_REVENUE_BENEFITS_VALIDATEGLMAPPINGS @TYPECODE, null, @CURRENTAPPUSERID, @BENEFITS, @PERCENTAGEBENEFITS
    end

    /*Join the two benefit types together for the purpose of committing*/
    declare @TOTALBENEFITS xml;
    set @TOTALBENEFITS = dbo.UFN_REVENUEDETAIL_JOINBENEFITS(@BENEFITS, @PERCENTAGEBENEFITS);

    --Remove benefits if they are waived
    if @BENEFITSWAIVED = 1
      set @TOTALBENEFITS = null;
    else
    begin
      --Validate that the total benefit amount is not greater than the revenue amount.
      declare @TOTALBENEFITAMOUNT money;

      select @TOTALBENEFITAMOUNT = coalesce(sum(TOTALVALUE), 0)
      from dbo.UFN_REVENUE_GETBENEFITS_3_FROMITEMLISTXML(@TOTALBENEFITS);

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

    /*JamesWill CR269601-030907 2007/03/09 If @GIVENANONYMOUSLYDEFAULTEDFORCONSTITUENTID is not @CONSTITUENT ID, that means we 
    * didn't default GIVENANONYMOUSLY for this row. So default it now. */
    if @GIVENANONYMOUSLYDEFAULTEDFORCONSTITUENTID is null or @GIVENANONYMOUSLYDEFAULTEDFORCONSTITUENTID <> @CONSTITUENTID
    begin
      select
        @GIVENANONYMOUSLY = GIVESANONYMOUSLY
      from dbo.CONSTITUENT
      where ID = @CONSTITUENTID;
    end

    -- 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('HOUSEHOLDSCANNOTBEDONORS', 13, 1);

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

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

    declare @BATCHOWNERID uniqueidentifier;
    declare @BATCHID uniqueidentifier;

    select
      @BATCHOWNERID = APPUSERID,
      @MERCHANTACCOUNTID = BATCHREVENUE.MERCHANTACCOUNTID,
      @BATCHID = [BATCH].[ID]
    from dbo.BATCH
    inner join dbo.BATCHREVENUE on BATCH.ID = BATCHREVENUE.BATCHID
    where BATCHREVENUE.ID = @BATCHROWID

    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 = 255
        set @PAYMENTMETHODCODE = null;

      if @CREDITCARDTOKEN is not null and len(@AUTHORIZATIONCODE) = 0 and @PAYMENTMETHODCODE = 2 and @AMOUNT > 0 -- credit card
      begin
        raiserror('BBERR_CREDITCARD_NOTPROCESSED', 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('The low price per share cannot be negative.', 13, 1)

        if @MEDIANPRICE < 0
          raiserror('The median price per share cannot be negative.', 13, 1)

        if @HIGHPRICE < 0
          raiserror('The high price per share cannot be negative.', 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 @SALEPOSTDATE is null and @SALEPOSTSTATUSCODE = 1
              raiserror('ERR_STOCK_SALEGLPOSTDATEREQUIRED', 13, 1)

            -- Validate GL Mappings
            if @NUMBEROFUNITSSOLD > 0
              exec dbo.USP_REVENUE_STOCK_VALIDATEGLMAPPINGS @PDACCOUNTSYSTEMID, @CURRENTAPPUSERID, @AMOUNT, @SALEAMOUNT, @NUMBEROFUNITSSOLD, @MEDIANPRICE, @BROKERFEE
          end

        if @NUMBEROFUNITSSOLD > @NUMBEROFUNITS
          raiserror('Units sold must be less than or equal to units remaining.', 13, 1)

        if @NUMBEROFUNITSSOLD = 0 and (@USERMODIFIEDNUMBEROFUNITSSOLD = 1 or @NUMBEROFUNITS <= 0)
          raiserror('You must sell one or more unit.', 13, 1)
      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      

      --JamesWill 2007/06/04 CR276243-053007 
      if @PAYMENTMETHODCODE = 3 --Direct Debit
      begin
        if @CONSTITUENTACCOUNTID is null
        raiserror('Please enter an Account for direct debit payments.', 13, 1);
      end

      -- DJH 5/27/2008 CR301226-052108 Validate that the payment method 'None' isn't used with payments
      if @PAYMENTMETHODCODE = 9 -- None
        raiserror('Only pledges or recurring gifts can have a payment method of ''None''.', 13, 1)

      if @PAYMENTMETHODCODE = 10 --Other
      begin
        if @OTHERPAYMENTMETHODCODEID is null
          raiserror('Please enter an ''other method'' for other payments.', 13, 1);
      end

      if @PAYMENTMETHODCODE = 11 --Standing order
      begin
        if @CONSTITUENTACCOUNTID is null
          raiserror('Please enter an Account for standing order payments.', 13, 1);
      end

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

      declare @APPLIEDAMOUNT money = 0;
      declare @RECEIPTABLEAMOUNT money = @AMOUNT;

      if @REVENUESTREAMS is not null and @STREAMCOUNT > 0
      begin

        declare @AMOUNTNOTRECEIPTABLE money = 0;

        with [APPLICATIONS]
        as
        (
          -- AdamBu - 7/22/11 - The only portion of revenue that shouldn't be receipted are event 
          --  costs, which are prorated depending on how much of the registration price was paid.
          select
            case  
              when TYPECODE = 6 then
                APPLIED - dbo.UFN_EVENTREGISTRANT_GETRECEIPTAMOUNT_NOBENEFITS(
                  APPLICATIONID,
                  @DATE,
                  @AMOUNT,
                  @ORGANIZATIONCURRENCYID,
                  @ORGANIZATIONCURRENCYID,
                  null,
                  0,
                  coalesce((select 1 from dbo.BATCHREVENUEREGISTRANT where ID = APPLICATIONID), 0),
                  null
                )
              else 0
            end as [AMOUNTNOTRECEIPTABLE],
            APPLIED
          from dbo.UFN_REVENUEBATCH_GETAPPLICATIONS_FROMITEMLISTXML(@REVENUESTREAMS)
          where APPLIED > 0
        )
        select 
          @APPLIEDAMOUNT = coalesce(sum(APPLIED), 0), -- If no applied amount > 0 exists, we want to still set the applied amount to 0 instead of null.
          @AMOUNTNOTRECEIPTABLE = sum(coalesce(AMOUNTNOTRECEIPTABLE, 0))
        from [APPLICATIONS];

        set @RECEIPTABLEAMOUNT = @AMOUNT - coalesce(@AMOUNTNOTRECEIPTABLE, 0);
      end

      declare @UNAPPLIEDAMOUNT money = @AMOUNT - @APPLIEDAMOUNT;

      if not @REVENUESTREAMS is null and @STREAMCOUNT > 0
        exec dbo.USP_REVENUEBATCH_VALIDATEREVENUESTREAMS @AMOUNT, @REVENUESTREAMS, @PAYMENTMETHODCODE;

      if not @UNAPPLIEDMATCHINGGIFTSPLITS is null
        exec dbo.USP_REVENUESPLITS_VALIDATEGLMAPPINGS @UNAPPLIEDMATCHINGGIFTSPLITS, @TYPECODE, @PAYMENTMETHODCODE, null, 1;

      if not @SOLICITORS is null
        exec dbo.USP_REVENUESOLICITOR_VALIDATESOLICITORS @SOLICITORS, @UNAPPLIEDAMOUNT;

      if not @TRIBUTES is null
      begin
        if (select sum(AMOUNT) from dbo.UFN_REVENUE_GETTRIBUTES_FROMITEMLISTXML(@TRIBUTES)) > @UNAPPLIEDAMOUNT
          raiserror('The sum of the tribute amounts cannot be greater than the revenue amount.', 13, 1)
      end

      if not @SPLITS is null 
      begin
        exec dbo.USP_REVENUESPLIT_VALIDATESPLITS @SPLITS, @UNAPPLIEDAMOUNT, @TYPECODE;

        exec dbo.USP_REVENUESPLITS_VALIDATEGLMAPPINGS @SPLITS, @TYPECODE, @PAYMENTMETHODCODE, @CATEGORYCODEID;

        -- 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
          declare @CAMPAIGNS xml
          declare CAMPAIGNCURSOR cursor local fast_forward for 
          select
            CAMPAIGNS
          from dbo.UFN_REVENUEBATCH_SPLITSWITHCHILDREN_FROMITEMLISTXML(@SPLITS)

          open CAMPAIGNCURSOR

          fetch next from CAMPAIGNCURSOR into @CAMPAIGNS

          while @@FETCH_STATUS = 0
          begin
            if exists(select 1 
                      from dbo.UFN_REVENUESPLIT_CAMPAIGNS_FROMITEMLISTXML(@CAMPAIGNS) 
                      group by CAMPAIGNID, CAMPAIGNSUBPRIORITYID
                      having count(*) > 1)
            begin
              raiserror('Duplicate campaigns and subpriorities cannot be specified for the same designation.', 13, 1)
            end

            fetch next from CAMPAIGNCURSOR into @CAMPAIGNS
          end

          close CAMPAIGNCURSOR
          deallocate CAMPAIGNCURSOR
        end
      end;
      else
      begin
        if @UNAPPLIEDAMOUNT > 0 or (@AMOUNT =0 and @UNAPPLIEDAMOUNT=0)
        begin
          if @SINGLEDESIGNATIONID is null
            raiserror('Please enter at least one designation.', 13, 1);

          exec dbo.USP_DESIGNATION_VALIDATEGLMAPPINGS @SINGLEDESIGNATIONID, @TYPECODE, @PAYMENTMETHODCODE, 0, 0, @CATEGORYCODEID;
        end
      end;

      if @RECEIPTAMOUNT > @RECEIPTABLEAMOUNT
        raiserror('The receipt amount must be less than or equal to the gift amount.', 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;

      --JamesWill 02/12/2008 CR293355-021208 Do validation for any created MG Claims
      if not @MGMATCHINGCONSTITUENTID is null
      begin
        if @MGAMOUNT < 0 
          raiserror('The matching gift amount cannot be negative.', 13, 1);

        --We need to convert the MGSPLITS to regular SPLITS (involves changing the root from "MGSPLITS" to "SPLITS" and adding a "REVENUEID" element) 
        /* JamesWill CR273846-042607 2007/04/27 the IDs from BATCHREVENUEMATCHINGGIFTSPLIT have the same IDs as corresponding
        * rows from BATCHREVENUESPLIT. Since USP_REVENUE_GETSPLITS_ADDFROMXML will use the ID from the xml to add to REVENUESPLIT, 
        * we need to use new IDs for matching gift splits. Making the change here instead of in the client component when we generate 
        * these splits allows the fix to work for previously existing batches without having to run "update BATCHREVENUEMATCHINGGIFTSPLIT set ID = newid()"
        * as a service revision. 
        */
        insert into @MGSPLITSTABLE(ID, DESIGNATIONID, AMOUNT, SEQUENCE) 
          select newid(), DESIGNATIONID, AMOUNT, SEQUENCE from dbo.UFN_REVENUEBATCH_GETMATCHINGGIFTSPLITS_FROMITEMLISTXML(@MGSPLITS);

        set @MGSPLITS = (select ID, DESIGNATIONID, AMOUNT, SEQUENCE, null as [REVENUEID], 0 as APPLICATIONCODE, 0 as TYPECODE
                         from @MGSPLITSTABLE 
                         for xml raw('ITEM'),type,elements,root('SPLITS'),binary base64 )

        exec dbo.USP_REVENUESPLIT_VALIDATESPLITS @MGSPLITS, @MGAMOUNT, @TYPECODE;
      end

      -- DJH 5/27/2008 CR301499-052308 Validate that SALEPOSTDATE is set if the payment type is sold property or stock
      --if @SALEDATE is not null and (@SALEPOSTDATE is null and @SALEPOSTSTATUSCODE =1)
      --  raiserror('Sale GL post date is required.', 13, 1)

      -- Verify that if the application type is other, that the other type code is set
      if @APPLICATIONCODE = 1 and @OTHERTYPECODEID is null
        raiserror('Other type is required if the application is Other.', 13, 1)

      -- If an unapplied matching gift payment was added, verify the constituent selected is an organization
      if @APPLICATIONCODE = 2
      begin
        declare @ISORGANIZATION bit
        select @ISORGANIZATION = ISORGANIZATION
        from dbo.CONSTITUENT where ID = @CONSTITUENTID

        if @ISORGANIZATION is null
          select @ISORGANIZATION = ISORGANIZATION
          from dbo.BATCHREVENUECONSTITUENT where ID = @CONSTITUENTID

        if @ISORGANIZATION = 0
          raiserror('Only organizations can add unapplied matching gift payments.', 13, 1)
      end
    end;
    else if @TYPECODE = 1  -- pledge
    begin

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

      if (@VALIDATEONLY = 1) and (@POSTSTATUSCODE = 1)
        exec dbo.USP_REVENUE_VALIDATEGLMAPPING @BATCHROWID, 1, @CURRENTAPPUSERID;

      if not @PAYMENTMETHODCODE in (2, 3, 9, 11)
      begin
        if dbo.UFN_INSTALLEDPRODUCTS_PRODUCTIS('9568A6C2-F7AA-45fd-8F54-21FE9654EE2D') = 1
          raiserror('Payment method for a pledge must be ''Credit card'', ''Direct debit'', ''Standing order'', or ''None''.', 13, 1);
        else
          raiserror('Payment method for a pledge must be ''Credit card'', ''Direct debit'', or ''None''.', 13, 1);
      end
      --JamesWill 2007/06/04 CR276243-053007 
      if @PAYMENTMETHODCODE = 3 --Direct Debit
      begin
        if @CONSTITUENTACCOUNTID is null
        raiserror('Please enter an Account for direct debit pledges.', 13, 1);
      end

      if @PAYMENTMETHODCODE = 11 --Standing order
      begin
        if @CONSTITUENTACCOUNTID is null
        raiserror('Please enter an Account for standing order pledges.', 13, 1);
      end

      if not @SPLITS is null
      begin
        exec dbo.USP_REVENUESPLIT_VALIDATESPLITS @SPLITS, @AMOUNT, @TYPECODE;

        exec dbo.USP_REVENUESPLITS_VALIDATEGLMAPPINGS @SPLITS, @TYPECODE, @PAYMENTMETHODCODE, @CATEGORYCODEID;
      end
      else
      begin
        if @SINGLEDESIGNATIONID is null
          raiserror('Please enter at least one designation.', 13, 1);

        exec dbo.USP_DESIGNATION_VALIDATEGLMAPPINGS @SINGLEDESIGNATIONID, @TYPECODE, @PAYMENTMETHODCODE, 0, 0, @CATEGORYCODEID;
      end

      if not @SOLICITORS is null
        exec dbo.USP_REVENUESOLICITOR_VALIDATESOLICITORS @SOLICITORS, @AMOUNT;

      if not @TRIBUTES is null
      begin
        if (select sum(AMOUNT) from dbo.UFN_REVENUE_GETTRIBUTES_FROMITEMLISTXML(@TRIBUTES)) > @AMOUNT
          raiserror('The sum of the tribute amounts cannot be greater than the revenue amount.', 13, 1)
      end

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

      if @INSTALLMENTFREQUENCYCODE not in (0,1,2,3,4,5,7,8)
        raiserror('The installment frequencies of bimonthly and weekly are not supported by pledges.', 13, 1);

      -- Validate installments if the frequency is irregular
      if @INSTALLMENTFREQUENCYCODE = 4
        exec dbo.USP_REVENUEINSTALLMENT_VALIDATEINSTALLMENTS @INSTALLMENTS, @DATE, @AMOUNT;

      if @INSTALLMENTSTARTDATE < @DATE
        raiserror('The schedule cannot start before the gift date.', 13, 1);

      if @INSTALLMENTENDDATE < @INSTALLMENTSTARTDATE
        raiserror('The schedule end date cannot occur before the schedule start date.', 13, 1);

      if @PAYMENTFORPLEDGEAMOUNT > @AMOUNT
        raiserror('The payment amount for a pledge cannot be more than the total pledge amount', 13, 1);
    end;
    else if @TYPECODE = 3  -- recurring gift
    begin
      if not @SPLITS is null
        exec dbo.USP_REVENUESPLIT_VALIDATESPLITS @SPLITS, @AMOUNT, @TYPECODE;
      else
        if @SINGLEDESIGNATIONID is null
          raiserror('Please enter at least one designation.', 13, 1);

      if not @SOLICITORS is null
        exec dbo.USP_REVENUESOLICITOR_VALIDATESOLICITORS @SOLICITORS, @AMOUNT;


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

      --if semi-monthly ensure nexttransaction date starts on 1st or 15th
      --JamesWill WI170811 2011-08-11 Do this before validating the transaction dates
      if @INSTALLMENTFREQUENCYCODE = 7 and not (day(@INSTALLMENTSTARTDATE) = 1 or day(@INSTALLMENTSTARTDATE) = 15)
        --Note: Use the old UFN_REVENUE_GETNEXTTRANSACTIONDATE function here because it doesn't require an ID and does exactly what is needed right here
        set @NEXTTRANSACTIONDATE = dbo.UFN_REVENUE_GETNEXTTRANSACTIONDATE(@INSTALLMENTFREQUENCYCODE, @INSTALLMENTSTARTDATE);
      else
        set @NEXTTRANSACTIONDATE = @INSTALLMENTSTARTDATE;



      if @INSTALLMENTFREQUENCYCODE = 4 or @INSTALLMENTFREQUENCYCODE = 5
        raiserror('The installment frequencies of irregular and single installment are not supported by recurring gifts.', 13, 1);

      if @PAYMENTMETHODCODE not in (2, 3, 9, 11)
      begin
        if dbo.UFN_INSTALLEDPRODUCTS_PRODUCTIS('9568A6C2-F7AA-45fd-8F54-21FE9654EE2D') = 1
          raiserror('Payment method for a recurring gift must be ''Credit card'', ''Direct debit'', ''Standing order'', or ''None''.', 13, 1);
        else
          raiserror('Payment method for a recurring gift must be ''Credit card'', ''Direct debit'', or ''None''.', 13, 1);
      end

      --JamesWill 2007/06/04 CR276243-053007 
      if @PAYMENTMETHODCODE = 3 --Direct Debit
      begin
        if @CONSTITUENTACCOUNTID is null
          raiserror('Please enter an Account for direct debit recurring gifts.', 13, 1);
      end

      if @PAYMENTMETHODCODE = 11 --Standing order
      begin
        if @CONSTITUENTACCOUNTID is null
          raiserror('Please enter an Account for standing order recurring gifts.', 13, 1);
      end

      if @INSTALLMENTSTARTDATE < @DATE
        raiserror('The schedule cannot start before the gift date.', 13, 1);

      if @INSTALLMENTENDDATE < @INSTALLMENTSTARTDATE
        raiserror('The schedule end date cannot occur before the schedule start date.', 13, 1);

      --JamesWill WI170811 2011-08-11 Validate the next transaction date in the same manner as CK_REVENUESCHEDULE_NEXTTRANSACTIONDATEVALID on 
      --REVENUESCHEDULE
      if not @NEXTTRANSACTIONDATE is null and @NEXTTRANSACTIONDATE > @INSTALLMENTENDDATE
        raiserror('BBERR_RECURRINGGIFT_NEXTTRANSACTIONDATEVALID', 13, 1);
    end;


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


    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
      exec dbo.USP_REVENUEBATCH_VALIDATENEWCONSTITUENT
        @REVENUEBATCHCONSTITUENTID = @CONSTITUENTID,
        @ISDONOR = 1,
        @BATCHROWID = @BATCHROWID;

    exec dbo.USP_REVENUEBATCH_VALIDATERECOGNITIONS @RECOGNITIONS, @BATCHROWID;

    -- DJH CR299323-042908 4/30/2008
    -- Using @GENERATEREVENUENOTE so that the same logic that determines
    -- whether validation occurs also determines whether the table is 
    -- actually populated
    --declare @GENERATEREVENUENOTE bit not necessary after revenue changes
    if (@NOTETITLE <> '') or (not @NOTEAUTHORID is null) or (not @NOTEDATEENTERED is null) or (not @NOTETYPECODEID is null) or (not @NOTETEXTNOTE = '') 
    begin
      --set @GENERATEREVENUENOTE = 1 not necessary after revenue changes

      if @NOTETYPECODEID is null
        raiserror('Please enter a type for the revenue note.', 13, 1)

      if @NOTEDATEENTERED is null
        raiserror('Please enter a date for the revenue note.', 13, 1)
    end

    if @VALIDATEONLY = 1
    begin
      -- When actually committing, the declarations will be validated through table constraints
      exec dbo.USP_BATCH_VALIDATETAXDECLARATIONS @TAXDECLARATIONS = @TAXDECLARATIONS, @BATCHTYPE = 1, @CONSTITUENTID = @CONSTITUENTID
    end
    else if @VALIDATEONLY = 0
    begin
      if @CONSTITUENTEXISTS = 0
      begin
        declare @BATCHCONSTITID uniqueidentifier;
        set @BATCHCONSTITID = @CONSTITUENTID;
        set @CONSTITUENTID = newID();

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

        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;

      -- Single designation ID is deprecated so it should be in the splits collection
      if @SINGLEDESIGNATIONID is not null and @SPLITS is null
      begin
        set @SPLITS = (select
                         @SINGLEDESIGNATIONID as DESIGNATIONID,
                         @AMOUNT as AMOUNT,
                         @DECLINESGIFTAID as DECLINESGIFTAID,
                         1 as SEQUENCE,
                         @ISGIFTAIDSPONSORSHIP as ISGIFTAIDSPONSORSHIP
                       for xml raw('ITEM'),type,elements,root('SPLITS'),binary base64)
      end

      if @TYPECODE = 0  -- payment
      begin

        --Default the receipt amount to be the gift amount - benefits if the user did not explicitly set the receipt amount
        if @USERMODIFIEDRECEIPTAMOUNT = 0
        begin
          if not @BENEFITS is null and @BENEFITSWAIVED = 0
          begin
            declare @BENEFITAMOUNT money;
            select @BENEFITAMOUNT = coalesce(sum(QUANTITY * UNITVALUE), 0)
            from dbo.UFN_REVENUE_GETBENEFITS_FROMITEMLISTXML(@BENEFITS);

            select @BENEFITAMOUNT = @BENEFITAMOUNT + coalesce(sum(PERCENTAPPLICABLEAMOUNT * VALUEPERCENT/100), 0)
            from dbo.UFN_REVENUE_GETBENEFITS2_FROMITEMLISTXML(@PERCENTAGEBENEFITS);

            select @RECEIPTAMOUNT = @RECEIPTABLEAMOUNT - @BENEFITAMOUNT;
          end
          else
            set @RECEIPTAMOUNT = @RECEIPTABLEAMOUNT;
        end 

        if @RECEIPTAMOUNT < 0
          set @RECEIPTAMOUNT = 0;

        if @RECEIPTAMOUNT > @TRANSACTIONAMOUNT
          raiserror('ERR_VALIDATE_RECEIPTAMOUNT', 13, 1);

        if not @SPLITS is null
          exec dbo.USP_REVENUESPLIT_VALIDATESPLITS @SPLITS, @UNAPPLIEDAMOUNT, @TYPECODE;

        --if no receipt type is specified, get the default receipt type
        if @RECEIPTTYPECODE is null or @RECEIPTTYPECODE = 255
          set @RECEIPTTYPECODE = dbo.UFN_CONSTITUENT_GETRECEIPTPREFERENCE(@CONSTITUENTID,@TYPECODE);

        insert into dbo.REVENUE (ID, CONSTITUENTID, DATE, BATCHNUMBER, POSTDATE, DONOTPOST, AMOUNT, TRANSACTIONTYPECODE, RECEIPTAMOUNT, FINDERNUMBER, SOURCECODE, APPEALID, GIVENANONYMOUSLY, DONOTACKNOWLEDGE, DONOTRECEIPT, BENEFITSWAIVED, MAILINGID, CHANNELCODEID, RECEIPTTYPECODE, BASECURRENCYID, ORGANIZATIONAMOUNT, ORGANIZATIONEXCHANGERATEID, TRANSACTIONAMOUNT, TRANSACTIONCURRENCYID, BASEEXCHANGERATEID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
          values (@ID, @CONSTITUENTID, @DATE, @BATCHNUMBER, @POSTDATE, @DONOTPOST, @AMOUNT, 0, @RECEIPTAMOUNT, @FINDERNUMBER, @SOURCECODE, @APPEALID, @GIVENANONYMOUSLY, @DONOTACKNOWLEDGE, @DONOTRECEIPT, @BENEFITSWAIVED, @MAILINGID, @CHANNELCODEID, @RECEIPTTYPECODE, @ORGANIZATIONCURRENCYID, @AMOUNT, null, @AMOUNT, @ORGANIZATIONCURRENCYID, null, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE);

        --Add origination source
        exec dbo.USP_REVENUE_ADDORIGIN @ID, @CONSTITUENTID, @CHANGEAGENTID, @CURRENTDATE;

        --Add payment original amount
        exec dbo.USP_PAYMENT_ADDORIGINALAMOUNT @ID, @CHANGEAGENTID, @CURRENTDATE;

        if not @LETTERCODEID is null
        begin

          if @ACKNOWLEDGEEID is null
            set @ACKNOWLEDGEEID = @CONSTITUENTID;

          insert into dbo.REVENUELETTER
            (ID,REVENUEID,LETTERCODEID,ACKNOWLEDGEDATE,ACKNOWLEDGEEID,ADDEDBYID,CHANGEDBYID,DATEADDED,DATECHANGED)
          values
            (NewID(),@ID,@LETTERCODEID,@ACKNOWLEDGEDATE,@ACKNOWLEDGEEID, @CHANGEAGENTID,@CHANGEAGENTID,@CURRENTDATE,@CURRENTDATE);
        end;

        declare @SPLITSDECLININGGIFTAIDAPPLICATIONS xml, @COVENANTGIFTSPLITS xml
        if @REVENUESTREAMS is not null and @STREAMCOUNT > 0
        begin
          -- BEGIN NEW PLEDGES FOR PAYMENTS HANDLING
          -- A payment revenue application may contain a new pledge which needs to be created.
          -- To handle this, we...
          --  1. Get the pledge definition in XML out of the @REVENUESTREAMS xml
          --  2. Use the stored procedure from Pledge.Add.xml to create a new pledge in the 
          --     database using variables pulled from the XML as parameters.
          --  3. Insert an APPLICATIONID element into the @REVENUESTREAMS item with the
          --     uniqueidentifier created by adding the pledge.  SQL Server does not allow
          --     SQL variables within the SQL XML modify statement, so this requires some trickery:
          --     a. Create a temporary XML variable combining the entire @REVENUESTREAMS and the 
          --        APPLICATIONID element using a SELECT ... FOR XML statement.
          --     b. Use the .modify method to insert the APPLICATIONID element into the revenue stream
          --        which contains the pledge element.
          --     c. Delete the outer APPLICATIONID element from the variable and reassign back to original variable.
          --  4. Carry on business as usual

          declare @APPLICATIONPLEDGE xml;
          select @APPLICATIONPLEDGE = PLEDGES from UFN_REVENUEBATCH_GETAPPLICATIONSWITHCHILDREN_FROMITEMLISTXML(@REVENUESTREAMS) where PLEDGES is not null;

          if (select count(ID) from UFN_REVENUEBATCH_GETPAYMENTPLEDGE_FROMITEMLISTXML(@APPLICATIONPLEDGE)) = 1
          begin
            declare @APPLICATIONPLEDGEREVENUEID uniqueidentifier;
            declare @APCONSTITUENTID uniqueidentifier;
            declare @APDATE datetime;
            declare @APAMOUNT money;
            declare @APPOSTSTATUSCODE tinyint;
            declare @APPOSTDATE datetime;
            declare @APSENDPLEDGEREMINDER bit;
            declare @APSPLITS xml;
            declare @APFREQUENCYCODE tinyint;
            declare @APNUMBEROFINSTALLMENTS int;
            declare @APSTARTDATE datetime;
            declare @APINSTALLMENTS xml;
            declare @APFINDERNUMBER bigint;
            declare @APSOURCECODE nvarchar(50);
            declare @APAPPEALID uniqueidentifier;
            declare @APBENEFITS xml;
            declare @APBENEFITSWAIVED bit;
            declare @APGIVENANONYMOUSLY bit;
            declare @APMAILINGID uniqueidentifier;
            declare @APCHANNELCODEID uniqueidentifier;
            declare @APDONOTACKNOWLEDGE bit;
            declare @APPLEDGESUBTYPEID uniqueidentifier;
            declare @APREFERENCE nvarchar(255);
            declare @APCATEGORYCODEID uniqueidentifier;
            declare @APOPPORTUNITYID uniqueidentifier;
            declare @APSPLITSDECLININGGIFTAID xml;
            declare @APPERCENTAGEBENEFITS xml;

            select
              @APCONSTITUENTID = BRAP.CONSTITUENTID,
              @APDATE = XML.DATE,
              @APAMOUNT = XML.AMOUNT,
              @APPOSTSTATUSCODE = XML.POSTSTATUSCODE,
              @APPOSTDATE = XML.POSTDATE,
              @APSENDPLEDGEREMINDER = XML.SENDPLEDGEREMINDER,
              @APSPLITS = XML.SPLITS,
              @APFREQUENCYCODE = XML.FREQUENCYCODE,
              @APNUMBEROFINSTALLMENTS = XML.NUMBEROFINSTALLMENTS,
              @APSTARTDATE = XML.STARTDATE,
              @APINSTALLMENTS = XML.INSTALLMENTS,
              @APFINDERNUMBER = XML.FINDERNUMBER,
              @APSOURCECODE = case when XML.SOURCECODE is null then '' else XML.SOURCECODE end,
              @APAPPEALID = XML.APPEALID,
              @APBENEFITS = XML.BENEFITS,
              @APBENEFITSWAIVED = XML.BENEFITSWAIVED,
              @APGIVENANONYMOUSLY = XML.GIVENANONYMOUSLY,
              @APMAILINGID = XML.MAILINGID,
              @APCHANNELCODEID = XML.CHANNELCODEID,
              @APDONOTACKNOWLEDGE = XML.DONOTACKNOWLEDGE,
              @APPLEDGESUBTYPEID = XML.PLEDGESUBTYPEID,
              @APREFERENCE = XML.REFERENCE,
              @APCATEGORYCODEID = XML.GLREVENUECATEGORYMAPPINGID,
              @APOPPORTUNITYID = XML.OPPORTUNITYID,
              @APPERCENTAGEBENEFITS = XML.PERCENTAGEBENEFITS

            from
            dbo.UFN_REVENUEBATCH_GETPAYMENTPLEDGE_FROMITEMLISTXML(@APPLICATIONPLEDGE) XML
            -- join to BATCHREVENUEAPPLICATIONPLEDGE to get the constituent ID
            -- in case it changed as a result of it originally being a batch revenue constituent ID.
            inner join dbo.BATCHREVENUEAPPLICATIONPLEDGE BRAP on XML.ID = BRAP.ID

            exec dbo.USP_PLEDGE_ADD
              @APPLICATIONPLEDGEREVENUEID output,
              @CHANGEAGENTID,
              @APCONSTITUENTID,
              @APDATE,
              @APAMOUNT,
              @APPOSTSTATUSCODE,
              @APPOSTDATE,
              @APSENDPLEDGEREMINDER,
              @APSPLITS,
              @APFREQUENCYCODE,
              @APNUMBEROFINSTALLMENTS,
              @APSTARTDATE,
              @APINSTALLMENTS,
              0,
              9,
              '',
              '',
              null,
              '00000000',
              '00000000',
              '',
              null,
              @APFINDERNUMBER,
              @APSOURCECODE,
              @APAPPEALID,
              @APBENEFITS,
              @APBENEFITSWAIVED,
              @APGIVENANONYMOUSLY,
              @APMAILINGID,
              @APCHANNELCODEID,
              @APDONOTACKNOWLEDGE,
              @APPLEDGESUBTYPEID,
              @BATCHNUMBER,
              @APOPPORTUNITYID,
              @APREFERENCE,
              @APCATEGORYCODEID,
              null,
              0,
              null,
              null,
              null,
              @APSPLITSDECLININGGIFTAID output,
              @APPERCENTAGEBENEFITS;

            exec dbo.USP_GIFTAIDREVENUESPLIT_ADDUPDATESPLITS @APPLICATIONPLEDGEREVENUEID, @APAPPEALID, 9, null, @CHANGEAGENTID, @APDATE, 1, @APSPLITSDECLININGGIFTAID; --revenue transaction type code of pledge is 1

            declare @APPLICATIONIDNODE xml;
            set @APPLICATIONIDNODE = '<APPLICATIONID>' + cast(@APPLICATIONPLEDGEREVENUEID as varchar(36)) + '</APPLICATIONID>';

            declare @COMBINEDXML xml;
            select @COMBINEDXML =
            (
              select 
                @APPLICATIONIDNODE,
                @REVENUESTREAMS
              for xml path(''), type
            );

            --JamesWill 03/27/2008 CR296359-032108 We also need to delete the original APPLICATIONID element from that /REVENUESTREAMS/ITEM or we'll end up with 2. The first will
            --be <APPLICATIONID>00000000-0000-0000-0000-000000000000</APPLICATIONID> and the second one will have the pledge's revenue ID. The empty guid will confuse code in
            --USP_REVENUEBATCH_APPLYTOREVENUESTREAMS and make it think it is applying to a MG Claim instead of a pledge.
            set @COMBINEDXML.modify('delete /REVENUESTREAMS/ITEM[count(./PLEDGES/ITEM)>0]/APPLICATIONID[1]');

            set @COMBINEDXML.modify('insert /APPLICATIONID[1] into (/REVENUESTREAMS/ITEM[count(./PLEDGES/ITEM)>0])[1]');

            set @COMBINEDXML.modify('delete /APPLICATIONID[1]');

            set @REVENUESTREAMS = @COMBINEDXML;
          end

          declare @returnid uniqueidentifier;
          --need to add @MAILINGID and @CHANNELCODEID.  currently passed in as null

          -- LTM - WI #186567, #167954 removing call to reduce extra logic since the actual population happens within the 2 SPs for pledge payment and recurring gift payment.
          --CR165881, ryu 07/15/11 need to populate marketing code/IDs from revenueStreams before the splits and recognitions are created
          --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_REVENUEBATCH_DEFAULTMARKETINGINFORMATION_FROMREVENUESTREAMS @REVENUESTREAMS, @ID, @CHANGEAGENTID, @CURRENTDATE;

          exec dbo.USP_REVENUEBATCH_APPLYTOREVENUESTREAMS @ID, @REVENUESTREAMS, @CONSTITUENTID, @DATE, @PAYMENTMETHODCODE, @BATCHNUMBER, @POSTDATE, @POSTSTATUSCODE, @DONOTRECEIPT, @DONOTACKNOWLEDGE, @FINDERNUMBER, @SOURCECODE, @APPEALID, @MAILINGID, @CHANNELCODEID, @CHECKDATE, @CHECKNUMBER, @CONSTITUENTACCOUNTID, @REFERENCEDATE, @REFERENCENUMBER, @CARDHOLDERNAME, @CREDITCARDNUMBER, @CREDITTYPECODEID, @AUTHORIZATIONCODE, @EXPIRESON, @ISSUER, @NUMBEROFUNITS, @SYMBOL, @MEDIANPRICE, @SALEDATE, @SALEAMOUNT, @BROKERFEE, @SALEPOSTSTATUSCODE, @SALEPOSTDATE, @PROPERTYSUBTYPECODEID, @GIFTINKINDSUBTYPECODEID, @CHANGEAGENTID, @CURRENTDATE, @UNAPPLIEDMATCHINGGIFTSPLITS,@APPLIEDTOPLEDGES output, @returnid output, 1, @OTHERPAYMENTMETHODCODEID, @REFERENCE, @CATEGORYCODEID, @SPLITSDECLININGGIFTAIDAPPLICATIONS output, @COVENANTGIFTSPLITS output, @BATCHOWNERID;

          --JamesWill CR268756-030207 2007/03/02 unset the ISPENDING flag for gifts which were generated
          update dbo.REVENUESCHEDULE
            set ISPENDING = 0,
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @CURRENTDATE
          from dbo.REVENUESCHEDULE
          where REVENUESCHEDULE.ID in (select APPLICATIONID from dbo.UFN_REVENUEBATCH_GETAPPLICATIONS_FROMITEMLISTXML(@REVENUESTREAMS) where APPLIED > 0 and WASGENERATED = 1)

          -- if a receipt type has been specified, update the revenue streams
          --bez 3/18/06 since the receipttype defaults to 0, don't set it if it is going to be zero
          if @RECEIPTTYPECODE is not null and @RECEIPTTYPECODE <> 255 and @RECEIPTTYPECODE <> 0
            update dbo.REVENUE 
              set RECEIPTTYPECODE = @RECEIPTTYPECODE, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CURRENTDATE
            where ID = @ID;

        end

        declare @REVENUESPLITAPPLICATIONCODE tinyint
        set @REVENUESPLITAPPLICATIONCODE = case
                                             when @APPLICATIONCODE = 0 then 0 -- Donation
                                             when @APPLICATIONCODE = 1 then 4 -- Other
                                             when @APPLICATIONCODE = 2 then 7 -- Unapplied matching gift claim
                                           end

        declare @REVENUESPLITTYPECODE tinyint
        set @REVENUESPLITTYPECODE = case 
                                      when @APPLICATIONCODE = 0 or @APPLICATIONCODE = 2 then 0
                                      when @APPLICATIONCODE = 1 then 4
                                    end

        declare @SPLITSDECLININGGIFTAIDDESIGNATIONS xml;
        declare @ISGIFTAIDSPONSORSHIPSPLITSDESIGNATIONS xml;
        exec dbo.USP_REVENUEBATCHCOMMIT_SPLITSWITHCHILDREN_ADDFROMXML @ID = @ID,
          @SPLITS = @SPLITS,
          @REVENUESPLITAPPLICATIONCODE = @REVENUESPLITAPPLICATIONCODE,
          @REVENUESPLITTYPECODE = @REVENUESPLITTYPECODE,
          @OTHERTYPECODEID = @OTHERTYPECODEID,
          @CATEGORYCODEID = @CATEGORYCODEID,
          @CHANGEAGENTID = @CHANGEAGENTID,
          @CURRENTDATE = @CURRENTDATE,
          @OPPORTUNITYID = @OPPORTUNITYID,
          @ADDITIONALAPPLICATIONS = 0,
          @SPLITSDECLININGGIFTAID = @SPLITSDECLININGGIFTAIDDESIGNATIONS output,
          @ISGIFTAIDSPONSORSHIPSPLITS = @ISGIFTAIDSPONSORSHIPSPLITSDESIGNATIONS output;

        -- If this isn't a Donation, then gift aid can't be declined and the application can not be a Gift Aid sponsorship
        if @APPLICATIONCODE <> 0
        begin
          set @SPLITSDECLININGGIFTAIDDESIGNATIONS = null
          set @ISGIFTAIDSPONSORSHIPSPLITSDESIGNATIONS = null
        end

        declare @SPLITSDECLININGGIFTAID xml;
        set @SPLITSDECLININGGIFTAID = (
          select REVENUESPLITID from
          (
            select
              T.c.value('(REVENUESPLITID)[1]','uniqueidentifier') AS 'REVENUESPLITID'
            from @SPLITSDECLININGGIFTAIDAPPLICATIONS.nodes('/SPLITSDECLININGGIFTAID/ITEM') T(c)
            union all
            select
              T.c.value('(REVENUESPLITID)[1]','uniqueidentifier') AS 'REVENUESPLITID'
            from @SPLITSDECLININGGIFTAIDDESIGNATIONS.nodes('/SPLITSDECLININGGIFTAID/ITEM') T(c)
          ) as DATA
          for xml raw('ITEM'),type,elements,root('SPLITSDECLININGGIFTAID'),BINARY BASE64);

        declare @ISGIFTAIDSPONSORSHIPSPLITS xml;
       set @ISGIFTAIDSPONSORSHIPSPLITS = (
          select REVENUESPLITID from
          (
            select
              T.c.value('(REVENUESPLITID)[1]','uniqueidentifier') AS 'REVENUESPLITID'
            from @ISGIFTAIDSPONSORSHIPSPLITSDESIGNATIONS.nodes('/ISGIFTAIDSPONSORSHIPSPLITS/ITEM') T(c)
          ) as DATA
          for xml raw('ITEM'),type,elements,root('GIFTAIDSPONSORSHIPSPLITS'),BINARY BASE64);

        --bez 2/11/09 adding reference for donations (reference was already being added from applytorevenuestreams for other application types)
        exec dbo.USP_REVENUEREFERENCE_ADD @ID, @REFERENCE, @CHANGEAGENTID;

        -- Add payment details after splits are created so the distribution can be generated for sold stock/property
        declare @REVENUEPAYMETHODID uniqueidentifier
        set @REVENUEPAYMETHODID = newid();
        insert into dbo.REVENUEPAYMENTMETHOD (ID,REVENUEID, PAYMENTMETHODCODE, AMOUNT, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
          values (@REVENUEPAYMETHODID,@ID,@PAYMENTMETHODCODE, @AMOUNT, @CHANGEAGENTID,@CHANGEAGENTID,@CURRENTDATE,@CURRENTDATE)

        if @POSTSTATUSCODE = 0
          insert into dbo.REVENUEPOSTED(ID,ADDEDBYID,CHANGEDBYID,DATEADDED,DATECHANGED)
            values(@ID,@CHANGEAGENTID,@CHANGEAGENTID,@CURRENTDATE,@CURRENTDATE);

        exec dbo.USP_REVENUE_ADDPAYMENTDETAILS @REVENUEPAYMETHODID, @PAYMENTMETHODCODE, @CHECKDATE, @CHECKNUMBER, @CONSTITUENTACCOUNTID, @REFERENCEDATE, @REFERENCENUMBER, @CARDHOLDERNAME, @CREDITCARDNUMBER, @CREDITTYPECODEID, @AUTHORIZATIONCODE, @EXPIRESON, @ISSUER, @NUMBEROFUNITS, @SYMBOL, @MEDIANPRICE, @SALEDATE, @SALEAMOUNT, @BROKERFEE, @SALEPOSTSTATUSCODE, @SALEPOSTDATE, @PROPERTYSUBTYPECODEID, @GIFTINKINDSUBTYPECODEID, @CHANGEAGENTID, @CURRENTDATE, 1, @OTHERPAYMENTMETHODCODEID, @DIRECTDEBITRESULTCODE, @LOWPRICE, @HIGHPRICE, @NUMBEROFUNITSSOLD, @USERMODIFIEDNUMBEROFUNITSSOLD, @TRANSACTIONID, @TRANSACTIONAMOUNT, '', 0, 0, 0, 0, null, null, null, @MERCHANTACCOUNTID

        if not @SOLICITORS is null
          exec dbo.USP_REVENUEBATCH_ADDSOLICITORS @ID, @SOLICITORS, @CHANGEAGENTID, @CURRENTDATE;

        if not @RECOGNITIONS is null and not @GIVENANONYMOUSLY = 1
          exec dbo.USP_REVENUEBATCH_ADDRECOGNITIONS @ID, @RECOGNITIONS, @CHANGEAGENTID, @CURRENTDATE;

        --Update the organization total value, transaction total value, and exchange rates in the @TOTALBENEFITS XML.
        set @TOTALBENEFITS = dbo.UFN_REVENUEBENEFIT_CONVERTAMOUNTSINXML(@TOTALBENEFITS,@ORGANIZATIONCURRENCYID);
        exec dbo.USP_REVENUE_GETBENEFITS_3_ADDFROMXML @ID, @TOTALBENEFITS, @CHANGEAGENTID, @CURRENTDATE;

        --bez CR296386-032108 notes only got added for revenue when there was no donation
        --if @GENERATEREVENUENOTE = 1 not necessary after revenue changes
        --  insert into dbo.REVENUENOTE(DATEENTERED, TITLE, AUTHORID, TEXTNOTE, REVENUENOTETYPECODEID, REVENUEID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
        --    values(@NOTEDATEENTERED, @NOTETITLE, @NOTEAUTHORID, @NOTETEXTNOTE, @NOTETYPECODEID, @ID, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE)

        if not @TRIBUTES is null
        begin
          exec dbo.USP_REVENUE_GETTRIBUTES_UPDATEFROMXML @ID, @TRIBUTES, @CHANGEAGENTID, @CURRENTDATE;

          -- 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 = @ID;
        end

        declare @SPLITSFORGENERATEMGCLAIM xml;
        declare @AMOUNTFORGENERATINGMGCLAIM money;
        select @SPLITSFORGENERATEMGCLAIM = (select sum(AMOUNT) AMOUNT, DESIGNATIONID, 0 as TYPECODE
                                            from dbo.REVENUESPLIT
                                            where REVENUEID = @ID and REVENUESPLIT.TYPECODE = 0
                                            group by DESIGNATIONID
                                            for xml raw('ITEM'),type,elements,root('SPLITS'),binary base64);

        select @AMOUNTFORGENERATINGMGCLAIM = sum(AMOUNT)
        from dbo.REVENUESPLIT
        where REVENUEID = @ID and REVENUESPLIT.TYPECODE = 0;

        if not @MGMATCHINGCONSTITUENTID is null
        begin

          declare @MGREVENUEID uniqueidentifier

          set @MGREVENUEID = newid();

          --JamesWill CR275664-052107 2007/05/24 Matching gift claims are never posted
          insert into dbo.REVENUE(ID, CONSTITUENTID, DATE, POSTDATE, DONOTPOST, AMOUNT, TRANSACTIONTYPECODE, RECEIPTAMOUNT, FINDERNUMBER, SOURCECODE, APPEALID, BATCHNUMBER, BASECURRENCYID, ORGANIZATIONAMOUNT, ORGANIZATIONEXCHANGERATEID, TRANSACTIONAMOUNT, TRANSACTIONCURRENCYID, BASEEXCHANGERATEID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
            values(@MGREVENUEID, @MGMATCHINGCONSTITUENTID, @MGDATE, null, 1, @MGAMOUNT, 3, @MGAMOUNT, 0, '', null, @BATCHNUMBER, @ORGANIZATIONCURRENCYID, @MGAMOUNT, null, @MGAMOUNT, @ORGANIZATIONCURRENCYID, null, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE);

          --Add origination source
          exec dbo.USP_REVENUE_ADDORIGIN @MGREVENUEID, @MGMATCHINGCONSTITUENTID, @CHANGEAGENTID, @CURRENTDATE;


          insert into dbo.REVENUEPAYMENTMETHOD (REVENUEID, PAYMENTMETHODCODE, AMOUNT, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
            values (@MGREVENUEID, 9, @AMOUNT, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE)

          insert into dbo.REVENUEMATCHINGGIFT (ID, MATCHINGGIFTCONDITIONID, MGSOURCEREVENUEID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED,RELATIONSHIPID,ISACTIVE)
            values(@MGREVENUEID, @MGCONDITIONID, @ID, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE,@MGRELATIONSHIPID,1)

          insert into dbo.REVENUESCHEDULE (ID, STARTDATE, FREQUENCYCODE, NUMBEROFINSTALLMENTS, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
            values(@MGREVENUEID, @MGDATE, 5, 1, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE);

          insert into dbo.INSTALLMENT (ID, REVENUEID, AMOUNT, DATE, SEQUENCE, BASECURRENCYID, ORGANIZATIONAMOUNT, ORGANIZATIONEXCHANGERATEID, TRANSACTIONAMOUNT, TRANSACTIONCURRENCYID, BASEEXCHANGERATEID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
            values(newid(), @MGREVENUEID, @MGAMOUNT, @MGDATE, 1, @ORGANIZATIONCURRENCYID, @MGAMOUNT, null, @MGAMOUNT, @ORGANIZATIONCURRENCYID, null, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE);

          set @MGSPLITS = dbo.UFN_REVENUESPLIT_CONVERTAMOUNTSINXML(@MGSPLITS,@ORGANIZATIONCURRENCYID,null,@ORGANIZATIONCURRENCYID,null)
          exec dbo.USP_REVENUE_GETSPLITS_2_ADDFROMXML @MGREVENUEID, @MGSPLITS, @CHANGEAGENTID, @CURRENTDATE;

          exec dbo.USP_PLEDGE_GENERATEINSTALLMENTSPLITS @MGREVENUEID, @CHANGEAGENTID, @CURRENTDATE;

          if @CATEGORYCODEID is not null
            insert into dbo.REVENUECATEGORY (ID, GLREVENUECATEGORYMAPPINGID, DATEADDED, DATECHANGED, ADDEDBYID, CHANGEDBYID)
            select
              REVENUESPLIT.ID,
              @CATEGORYCODEID,
              @CURRENTDATE, @CURRENTDATE, @CHANGEAGENTID, @CHANGEAGENTID
            from dbo.REVENUESPLIT
            where
              REVENUESPLIT.REVENUEID = @MGREVENUEID

        end
        else if @AMOUNTFORGENERATINGMGCLAIM > 0
        begin

          declare @CREATEMGFROMMGRELATIONS bit;
          set @CREATEMGFROMMGRELATIONS = dbo.UFN_MATCHINGGIFTPREFERENCE_AUTOADDMG();

          declare @CREATEMGFROMSPOUSEMGRELATIONS bit;
          set @CREATEMGFROMSPOUSEMGRELATIONS = dbo.UFN_MATCHINGGIFTPREFERENCE_AUTOADDSPOUSEMG();

          -- create matching gift records
          if @CREATEMGFROMMGRELATIONS > 0
            exec USP_MATCHINGGIFTPLEDGE_AUTOADD @CONSTITUENTID, @CHANGEAGENTID, @ID, @DATE, @AMOUNTFORGENERATINGMGCLAIM, @RECEIPTAMOUNT, @SPLITSFORGENERATEMGCLAIM, @CURRENTAPPUSERID;

          if @CREATEMGFROMSPOUSEMGRELATIONS > 0
            exec USP_MATCHINGGIFTPLEDGE_AUTOADDFROMSPOUSE @CONSTITUENTID, @CHANGEAGENTID, @ID, @DATE, @AMOUNTFORGENERATINGMGCLAIM, @RECEIPTAMOUNT, @SPLITSFORGENERATEMGCLAIM, @CURRENTAPPUSERID;
        end

        /* JamesWill CR258591-102706 2007/03/05 Add the note for every record in the transaction. */
        if (@NOTETITLE <> '') or (not @NOTEAUTHORID is null) or (not @NOTEDATEENTERED is null) or (not @NOTETYPECODEID is null) or (not @NOTETEXTNOTE = '') 
        begin
          insert into dbo.REVENUENOTE(DATEENTERED, TITLE, AUTHORID, TEXTNOTE, REVENUENOTETYPECODEID, REVENUEID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
            values(@NOTEDATEENTERED, @NOTETITLE, @NOTEAUTHORID, @NOTETEXTNOTE, @NOTETYPECODEID, @ID, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE)
        end

        --If any marketing information is null, try to default based on applications
        /* replaced by USP_REVENUEBATCH_DEFAULTMARKETINGINFORMATION_FROMREVENUESTREAMS above, so no longer needed here
        if @SOURCECODE is null or @SOURCECODE = '' or @MAILINGID is null or @APPEALID is null
          exec dbo.USP_REVENUE_DEFAULTMARKETINGINFORMATION @ID, @CHANGEAGENTID, @CURRENTDATE;
        */            
        -- Link revenue to Default Account System
        if dbo.UFN_VALID_BASICGL_INSTALLED() = 1
        begin
          set @PDACCOUNTSYSTEMID = dbo.UFN_PDACCOUNTSYSTEM_GETDEFAULTSYSTEMIDSFORUSER(@CURRENTAPPUSERID)
          exec dbo.USP_PDACCOUNTSYSTEM_LINKTOREVENUE @ID, @PDACCOUNTSYSTEMID, @CURRENTDATE, @CHANGEAGENTID;
        end


        --Save the GL distributions
        if @POSTSTATUSCODE <> 2
        begin
          exec dbo.USP_SAVE_REVENUEGLDISTRIBUTION @ID, @CHANGEAGENTID, @CURRENTDATE;
          -- Priyanka - 125381 - save any benefit distributions
          exec dbo.USP_SAVE_PAYMENT_BENEFITGLDISTRIBUTION @ID, @CHANGEAGENTID, @CURRENTDATE;
        end

        --Add Gift Fees
        exec dbo.USP_PAYMENT_ADDGIFTFEES @ID, @CONSTITUENTID, @CHANGEAGENTID, @CURRENTDATE;

        --Add VAT
        if @AMOUNTFORVAT > 0
          insert into dbo.REVENUEVAT(ID, AMOUNTTOTAX, VATTAXRATEID, VATAMOUNT, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
            values(@ID, @AMOUNTFORVAT, @VATTAXRATEID, @VATAMOUNT, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE);

          -- Generate gift aid revenue split records
          exec dbo.USP_GIFTAIDREVENUESPLIT_ADDUPDATESPLITS @ID, @APPEALID, @PAYMENTMETHODCODE, @CREDITTYPECODEID, @CHANGEAGENTID, @DATE, @TYPECODE, @SPLITSDECLININGGIFTAID, @COVENANTGIFTSPLITS, @ISGIFTAIDSPONSORSHIPSPLITS
        end
      else if @TYPECODE = 1  -- pledge
      begin

        declare @i int;
        declare @TYPICALAMOUNT money;
        declare @INSTALLMENTTABLE table(
          DATE datetime,
          AMOUNT money,
          RECEIPTAMOUNT money,
          SEQUENCE int
        );

        insert into dbo.REVENUE (ID,CONSTITUENTID,DATE,AMOUNT, TRANSACTIONTYPECODE, RECEIPTAMOUNT, FINDERNUMBER, SOURCECODE, APPEALID, GIVENANONYMOUSLY, DONOTACKNOWLEDGE, BATCHNUMBER, POSTDATE, DONOTPOST, DONOTRECEIPT, BENEFITSWAIVED, MAILINGID, CHANNELCODEID, BASECURRENCYID, ORGANIZATIONAMOUNT, ORGANIZATIONEXCHANGERATEID, TRANSACTIONAMOUNT, TRANSACTIONCURRENCYID, BASEEXCHANGERATEID, ADDEDBYID,CHANGEDBYID,DATEADDED,DATECHANGED)
          values (@ID,@CONSTITUENTID,@DATE, @AMOUNT, 1, 0, @FINDERNUMBER, @SOURCECODE, @APPEALID, @GIVENANONYMOUSLY, @DONOTACKNOWLEDGE, @BATCHNUMBER, @POSTDATE, @DONOTPOST, 1, @BENEFITSWAIVED, @MAILINGID, @CHANNELCODEID, @ORGANIZATIONCURRENCYID, @AMOUNT, null, @AMOUNT, @ORGANIZATIONCURRENCYID, null, @CHANGEAGENTID, @CHANGEAGENTID,@CURRENTDATE,@CURRENTDATE);

        --Add origination source
        exec dbo.USP_REVENUE_ADDORIGIN @ID, @CONSTITUENTID, @CHANGEAGENTID, @CURRENTDATE;

        --Add pledge original amount
        exec dbo.USP_PLEDGE_ADDORIGINALAMOUNT @ID, null, @CHANGEAGENTID, @CURRENTDATE;

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

        if not @LETTERCODEID is null
        begin
          if @ACKNOWLEDGEEID is null
            set @ACKNOWLEDGEEID = @CONSTITUENTID;

          insert into dbo.REVENUELETTER
            (ID,REVENUEID,LETTERCODEID,ACKNOWLEDGEDATE,ACKNOWLEDGEEID,ADDEDBYID,CHANGEDBYID,DATEADDED,DATECHANGED)
          values
            (NewID(),@ID,@LETTERCODEID,@ACKNOWLEDGEDATE,@ACKNOWLEDGEEID,@CHANGEAGENTID,@CHANGEAGENTID,@CURRENTDATE,@CURRENTDATE);
        end;

        insert into dbo.REVENUEPAYMENTMETHOD (REVENUEID, PAYMENTMETHODCODE, AMOUNT, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
          values (@ID,@PAYMENTMETHODCODE, @AMOUNT,@CHANGEAGENTID,@CHANGEAGENTID,@CURRENTDATE,@CURRENTDATE)

        if @POSTSTATUSCODE = 0
          insert into dbo.REVENUEPOSTED(ID,ADDEDBYID,CHANGEDBYID,DATEADDED,DATECHANGED)
            values(@ID,@CHANGEAGENTID,@CHANGEAGENTID,@CURRENTDATE,@CURRENTDATE);

        declare @PLEDGECREDITCARDID uniqueidentifier
        if @PAYMENTMETHODCODE = 2 -- Credit Card
        begin
          exec dbo.USP_CREDITCARD_SAVE @ID = @PLEDGECREDITCARDID output,
            @CREDITCARDTOKEN = @CREDITCARDTOKEN,
            @CARDHOLDERNAME = @CARDHOLDERNAME,
            @CREDITCARDPARTIALNUMBER = @CREDITCARDNUMBER,
            @CREDITTYPECODEID = @CREDITTYPECODEID,
            @EXPIRESON = @EXPIRESON,
            @CHANGEAGENTID = @CHANGEAGENTID,
            @CURRENTDATE = @CURRENTDATE
        end

        insert into dbo.REVENUESCHEDULE (ID,STARTDATE,FREQUENCYCODE,NUMBEROFINSTALLMENTS,PLEDGESUBTYPEID,SENDPLEDGEREMINDER,CREDITCARDID,ADDEDBYID,CHANGEDBYID,DATEADDED,DATECHANGED)
          values (@ID, @INSTALLMENTSTARTDATE, @INSTALLMENTFREQUENCYCODE, @NUMBEROFINSTALLMENTS, @PLEDGESUBTYPEID, @SENDPLEDGEREMINDER, @PLEDGECREDITCARDID, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE);

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

          set @SENDNEWINSTRUCTION = 0;
          set @NEWINSTRUCTIONTOSEND = 0;

          if not @DDISOURCECODEID is null
          begin
            set @SENDNEWINSTRUCTION = 1;
            set @NEWINSTRUCTIONTOSEND = 1;
          end

          insert into dbo.REVENUESCHEDULEDIRECTDEBITPAYMENT(ID, REFERENCEDATE, REFERENCENUMBER, CONSTITUENTACCOUNTID, DDISOURCECODEID, DDISOURCEDATE, SENDPMINSTRUCTION, PMINSTRUCTIONTOSENDCODE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, DIRECTDEBITRESULTCODE)
            values (@ID, @REFERENCEDATE, @REFERENCENUMBER, @CONSTITUENTACCOUNTID, @DDISOURCECODEID, @DDISOURCEDATE, @SENDNEWINSTRUCTION, @NEWINSTRUCTIONTOSEND, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE, @DIRECTDEBITRESULTCODE);
        end

        if @PAYMENTMETHODCODE = 11 -- Standing Order
        begin
          insert into dbo.REVENUESCHEDULESTANDINGORDERPAYMENT(ID, REFERENCEDATE, REFERENCENUMBER, CONSTITUENTACCOUNTID, STANDINGORDERSETUP, STANDINGORDERSETUPDATE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
            values (@ID, @REFERENCEDATE, @STANDINGORDERREFERENCENUMBER, @CONSTITUENTACCOUNTID, @STANDINGORDERSETUP, @STANDINGORDERSETUPDATE, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE);

          insert into dbo.REVENUESTANDINGORDER(ID, CUSTOMREFERENCENUMBER, USESYSTEMGENERATEDREFERENCENUMBER, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
            values(@ID, @STANDINGORDERREFERENCENUMBER, @GENERATEREFERENCENUMBER, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE);
        end

        declare @SPLITSDECLININGGIFTAIDPLEDGE xml
        exec dbo.USP_REVENUEBATCHCOMMIT_SPLITSWITHCHILDREN_ADDFROMXML @ID = @ID,
          @SPLITS = @SPLITS,
          @REVENUESPLITAPPLICATIONCODE = null,
          @REVENUESPLITTYPECODE = null,
          @OTHERTYPECODEID = null,
          @CATEGORYCODEID = @CATEGORYCODEID,
          @CHANGEAGENTID = @CHANGEAGENTID,
          @CURRENTDATE = @CURRENTDATE,
          @OPPORTUNITYID = @OPPORTUNITYID,
          @ADDITIONALAPPLICATIONS = 0,
          @SPLITSDECLININGGIFTAID = @SPLITSDECLININGGIFTAIDPLEDGE output

        declare @COVENANTGIFTSPLITSPLEDGE xml
        if @ISCOVENANT = 1
        begin
          set @COVENANTGIFTSPLITSPLEDGE = (select
                                              REVENUESPLIT.ID as REVENUESPLITID
                                            from dbo.REVENUESPLIT
                                            where REVENUEID = @ID
                                            for xml raw('ITEM'),type,elements,root('COVENANTGIFTSPLITS'),BINARY BASE64)
        end

        -- Generate gift aid revenue split records
        exec dbo.USP_GIFTAIDREVENUESPLIT_ADDUPDATESPLITS @ID, @APPEALID, @PAYMENTMETHODCODE, @CREDITTYPECODEID, @CHANGEAGENTID, @DATE, @TYPECODE, @SPLITSDECLININGGIFTAIDPLEDGE, @COVENANTGIFTSPLITSPLEDGE

        -- create installments for pledge
        set @i = 0;
        set @TYPICALAMOUNT = ROUND(@AMOUNT / @NUMBEROFINSTALLMENTS, 2);

        if @AMOUNT > 0
        begin
          if @AMOUNT - (@TYPICALAMOUNT * (@NUMBEROFINSTALLMENTS - 1)) < 0 
            set @TYPICALAMOUNT = @TYPICALAMOUNT - power(10, -2); 
        end

        if @INSTALLMENTFREQUENCYCODE <> 4
        begin
          while @i < @NUMBEROFINSTALLMENTS
          begin

            if @i = @NUMBEROFINSTALLMENTS - 1
              set @TYPICALAMOUNT = @AMOUNT - (@TYPICALAMOUNT * (@NUMBEROFINSTALLMENTS - 1));

            if @INSTALLMENTFREQUENCYCODE = 0
              insert into @INSTALLMENTTABLE values (dateadd(yy, @i, @INSTALLMENTSTARTDATE), @TYPICALAMOUNT, @TYPICALAMOUNT, @i + 1);
            else if @INSTALLMENTFREQUENCYCODE = 1
              insert into @INSTALLMENTTABLE values (dateadd(mm, @i * 6, @INSTALLMENTSTARTDATE), @TYPICALAMOUNT, @TYPICALAMOUNT, @i + 1);
            else if @INSTALLMENTFREQUENCYCODE = 2
              insert into @INSTALLMENTTABLE values (dateadd(qq, @i, @INSTALLMENTSTARTDATE), @TYPICALAMOUNT, @TYPICALAMOUNT, @i + 1);
            else if @INSTALLMENTFREQUENCYCODE = 3
              insert into @INSTALLMENTTABLE values (dateadd(mm, @i, @INSTALLMENTSTARTDATE), @TYPICALAMOUNT, @TYPICALAMOUNT, @i + 1);
            else if @INSTALLMENTFREQUENCYCODE = 5
              insert into @INSTALLMENTTABLE values (@INSTALLMENTSTARTDATE, @TYPICALAMOUNT, @TYPICALAMOUNT, @i + 1);
            else if @INSTALLMENTFREQUENCYCODE = 7
              begin
                -- This is based on the algorithm for calculating semi-monthly dates found in RecurringGiftScheduleDataList.vb
                declare @NUMMONTHS int;
                declare @NEXTDATE datetime;

                set @NUMMONTHS = @i / 2;
                set @NEXTDATE = dateadd(mm,@NUMMONTHS,@INSTALLMENTSTARTDATE);

                if @i % 2 <> 0
                  begin
                    declare @NEXTDATE2 datetime;

                    if datepart(dd,@INSTALLMENTSTARTDATE) <= 15
                      begin

                        set @NEXTDATE2 = dateadd(dd,15,@NEXTDATE);

                        if datepart(mm,@NEXTDATE2) > datepart(mm,@NEXTDATE)
                          set @NEXTDATE2 = dbo.UFN_DATE_THISMONTH_LASTDAY(@NEXTDATE,0)

                        set @NEXTDATE = @NEXTDATE2;
                      end
                    else 
                      begin
                        if datepart(dd,@INSTALLMENTSTARTDATE) > 15
                          begin

                            set @NEXTDATE2 = dateadd(dd,15,@NEXTDATE);

                            if datepart(mm,@NEXTDATE2) = datepart(mm,@NEXTDATE) and datepart(dd,@NEXTDATE2) = 31
                              set @NEXTDATE2 = dateadd(dd,1,@NEXTDATE2);

                            set @NEXTDATE = @NEXTDATE2;

                          end
                        else
                          begin
                            set @NEXTDATE = dateadd(dd,15,@NEXTDATE);
                          end
                      end
                  end

                insert into @INSTALLMENTTABLE values (@NEXTDATE, @TYPICALAMOUNT, @TYPICALAMOUNT, @i + 1);

              end
            else if @INSTALLMENTFREQUENCYCODE = 8
              insert into @INSTALLMENTTABLE values (dateadd(ww, @i * 2, @INSTALLMENTSTARTDATE), @TYPICALAMOUNT, @TYPICALAMOUNT, @i + 1);

            set @i = @i + 1;
          end

          declare @TOTALRECEIPTAMOUNT money = @AMOUNT - @TOTALBENEFITAMOUNT
          set @i = @NUMBEROFINSTALLMENTS

          -- calculate receipt amount for pledge
          while @i > 0
          begin
            declare @INSTALLMENTAMOUNT money
            select @INSTALLMENTAMOUNT = AMOUNT
            from @INSTALLMENTTABLE
            where SEQUENCE = @i

            if @TOTALRECEIPTAMOUNT > @INSTALLMENTAMOUNT
            begin
              update @INSTALLMENTTABLE
              set RECEIPTAMOUNT = @INSTALLMENTAMOUNT
              where SEQUENCE = @i
            end
            else
            begin
              update @INSTALLMENTTABLE
              set RECEIPTAMOUNT = @TOTALRECEIPTAMOUNT
              where SEQUENCE = @i
            end

            set @TOTALRECEIPTAMOUNT = @TOTALRECEIPTAMOUNT - @INSTALLMENTAMOUNT
            if @TOTALRECEIPTAMOUNT < 0
              set @TOTALRECEIPTAMOUNT = 0

            set @i = @i - 1
          end

          set @INSTALLMENTS = (select DATE, AMOUNT, RECEIPTAMOUNT, SEQUENCE
                                from @INSTALLMENTTABLE
                                for xml raw ('ITEM'), type, elements, root ('INSTALLMENTS'), binary base64
                              );
        end

        --Multicurrency - Process the installments xml to calculate the base and organization amounts and place them in proper nodes.
        set @INSTALLMENTS = dbo.UFN_INSTALLMENT_CONVERTAMOUNTSINXML(@INSTALLMENTS,@ORGANIZATIONCURRENCYID,null,@ORGANIZATIONCURRENCYID,null);

        -- Installments should never be null.  If they are, there is an error.
        exec dbo.USP_INSTALLMENT_GETINSTALLMENTS_2_ADDFROMXML @ID, @INSTALLMENTS, @CHANGEAGENTID, @CURRENTDATE;
        exec dbo.USP_PLEDGE_GENERATEINSTALLMENTSPLITS @ID, @CHANGEAGENTID, @CURRENTDATE;


        if @PAYMENTMETHODCODE = 3 and @DDISOURCECODEID is not null and dbo.UFN_PAPERLESSMANDATES_DAYSADVANCEDNOTICE_FORREVENUE(@ID) = -1
        begin
          raiserror('BBERR_MULTIPLE_PAPERLESSMANDATECONFIGS', 13, 1);
          return 1;
        end

        -- create payment for pledge
        if not @PAYMENTFORPLEDGEAMOUNT = 0
        begin
          declare @PAYMENTFORPLEDGE_REVSTREAMS xml;
          set @PAYMENTFORPLEDGE_REVSTREAMS = 
          (
            select
            @ID as 'APPLICATIONID',
            @PAYMENTFORPLEDGEAMOUNT as 'APPLIED',
            2 as 'APPLICATIONCODE'
            for xml raw('ITEM'), type, elements, root('REVENUESTREAMS'), binary base64
          );

          declare @PAYMENTFORPLEDGE_REVENUEID uniqueidentifier;
          exec dbo.USP_PAYMENT_ADD 
            @ID = @PAYMENTFORPLEDGE_REVENUEID output, 
            @CHANGEAGENTID = @CHANGEAGENTID, 
            @CURRENTDATE = @CURRENTDATE, 
            @CONSTITUENTID = @CONSTITUENTID, 
            @DATE = @DATE, 
            @AMOUNT=@PAYMENTFORPLEDGEAMOUNT,
            @POSTDATE = @DATE,
            @REVENUESTREAMS = @PAYMENTFORPLEDGE_REVSTREAMS,
            @FINDERNUMBER = @FINDERNUMBER,
            @SOURCECODE = @SOURCECODE,
            @BATCHNUMBER = @BATCHNUMBER,
            @RECEIPTAMOUNT = @PAYMENTFORPLEDGEAMOUNT;

        end

        if not @SOLICITORS is null
          exec dbo.USP_REVENUEBATCH_ADDSOLICITORS @ID, @SOLICITORS, @CHANGEAGENTID, @CURRENTDATE;

        if not @RECOGNITIONS is null and not @GIVENANONYMOUSLY = 1
          exec dbo.USP_REVENUEBATCH_ADDRECOGNITIONS @ID, @RECOGNITIONS, @CHANGEAGENTID, @CURRENTDATE;

        --Update the organization total value, transaction total value, and exchange rates in the @TOTALBENEFITS XML.
        set @TOTALBENEFITS = dbo.UFN_REVENUEBENEFIT_CONVERTAMOUNTSINXML(@TOTALBENEFITS,@ORGANIZATIONCURRENCYID);
        exec dbo.USP_REVENUE_GETBENEFITS_3_ADDFROMXML @ID, @TOTALBENEFITS, @CHANGEAGENTID, @CURRENTDATE;

        if not @TRIBUTES is null
        begin
          exec dbo.USP_REVENUE_GETTRIBUTES_UPDATEFROMXML @ID, @TRIBUTES, @CHANGEAGENTID, @CURRENTDATE;

          -- 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 = @ID;
        end

        if (@NOTETITLE <> '') or (not @NOTEAUTHORID is null) or (not @NOTEDATEENTERED is null) or (not @NOTETYPECODEID is null) or (not @NOTETEXTNOTE = '') 
          insert into dbo.REVENUENOTE(DATEENTERED, TITLE, AUTHORID, TEXTNOTE, REVENUENOTETYPECODEID, REVENUEID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
            values(@NOTEDATEENTERED, @NOTETITLE, @NOTEAUTHORID, @NOTETEXTNOTE, @NOTETYPECODEID, @ID, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE)

        --Save the GL distributions
        if @POSTSTATUSCODE <> 2
                begin
          exec dbo.USP_SAVE_REVENUEGLDISTRIBUTION @ID, @CHANGEAGENTID, @CURRENTDATE;
          -- Priyanka - 125381 - save any benefit distributions
            if (dbo.UFN_INSTALLEDPRODUCTS_PRODUCTIS('0E85C527-E6E9-4C5F-A8E8-105FD0E18FE7') = 1)  
            exec USP_SAVE_BENEFITGLDISTRIBUTION_FINANCIALTRANSACTION @ID, @CHANGEAGENTID, @CURRENTDATE
        end

        --Add VAT
        if @AMOUNTFORVAT > 0
          insert into dbo.REVENUEVAT(ID, AMOUNTTOTAX, VATTAXRATEID, VATAMOUNT, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
            values(@ID, @AMOUNTFORVAT, @VATTAXRATEID, @VATAMOUNT, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE);
      end

      else if @TYPECODE = 3  -- recurring gift
      begin
        insert into dbo.REVENUE (ID, CONSTITUENTID, DATE, AMOUNT, TRANSACTIONTYPECODE, RECEIPTAMOUNT, FINDERNUMBER, SOURCECODE, APPEALID, GIVENANONYMOUSLY, DONOTACKNOWLEDGE, BATCHNUMBER, POSTDATE, DONOTPOST, BENEFITSWAIVED, MAILINGID, CHANNELCODEID, BASECURRENCYID, ORGANIZATIONAMOUNT, ORGANIZATIONEXCHANGERATEID, TRANSACTIONAMOUNT, TRANSACTIONCURRENCYID, BASEEXCHANGERATEID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
          values (@ID, @CONSTITUENTID, @DATE, @AMOUNT, 2, @AMOUNT, @FINDERNUMBER, @SOURCECODE, @APPEALID, @GIVENANONYMOUSLY, @DONOTACKNOWLEDGE, @BATCHNUMBER, @POSTDATE, @DONOTPOST, @BENEFITSWAIVED, @MAILINGID, @CHANNELCODEID, @ORGANIZATIONCURRENCYID, @AMOUNT, null, @AMOUNT, @ORGANIZATIONCURRENCYID, null, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE);

        --Add origination source
        exec dbo.USP_REVENUE_ADDORIGIN @ID, @CONSTITUENTID, @CHANGEAGENTID, @CURRENTDATE;

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

        if not @LETTERCODEID is null
        begin
          if @ACKNOWLEDGEEID is null
            set @ACKNOWLEDGEEID = @CONSTITUENTID;

          insert into dbo.REVENUELETTER
            (ID,REVENUEID,LETTERCODEID,ACKNOWLEDGEDATE,ACKNOWLEDGEEID,ADDEDBYID,CHANGEDBYID,DATEADDED,DATECHANGED)
          values
            (NewID(),@ID,@LETTERCODEID,@ACKNOWLEDGEDATE,@ACKNOWLEDGEEID,@CHANGEAGENTID,@CHANGEAGENTID,@CURRENTDATE,@CURRENTDATE);
        end;

        insert into dbo.REVENUEPAYMENTMETHOD (REVENUEID, PAYMENTMETHODCODE, AMOUNT, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
          values (@ID,@PAYMENTMETHODCODE, @AMOUNT,@CHANGEAGENTID,@CHANGEAGENTID,@CURRENTDATE,@CURRENTDATE)

        if @POSTSTATUSCODE = 0
          insert into dbo.REVENUEPOSTED(ID,ADDEDBYID,CHANGEDBYID,DATEADDED,DATECHANGED)
            values(@ID,@CHANGEAGENTID,@CHANGEAGENTID,@CURRENTDATE,@CURRENTDATE);

        declare @SPLITSDECLININGGIFTAIDRECURRINGGIFT xml
        exec dbo.USP_REVENUEBATCHCOMMIT_SPLITSWITHCHILDREN_ADDFROMXML @ID = @ID,
          @SPLITS = @SPLITS,
          @REVENUESPLITAPPLICATIONCODE = null,
          @REVENUESPLITTYPECODE = null,
          @OTHERTYPECODEID = null,
          @CATEGORYCODEID = @CATEGORYCODEID,
          @CHANGEAGENTID = @CHANGEAGENTID,
          @CURRENTDATE = @CURRENTDATE,
          @OPPORTUNITYID = null,
          @ADDITIONALAPPLICATIONS = 0,
          @SPLITSDECLININGGIFTAID = @SPLITSDECLININGGIFTAIDRECURRINGGIFT output

        -- Generate gift aid revenue split records
        exec dbo.USP_GIFTAIDREVENUESPLIT_ADDUPDATESPLITS 
          @ID, 
          @APPEALID, 
          @PAYMENTMETHODCODE, 
          @CREDITTYPECODEID, 
          @CHANGEAGENTID, 
          @DATE, 
          2, -- 2 is the actual transaction type code for recurring gifts
          @SPLITSDECLININGGIFTAIDRECURRINGGIFT



        declare @RECURRINGGIFTCREDITCARDID uniqueidentifier
        if @PAYMENTMETHODCODE = 2 -- Credit Card
        begin
          exec dbo.USP_CREDITCARD_SAVE @ID = @RECURRINGGIFTCREDITCARDID output,
            @CREDITCARDTOKEN = @CREDITCARDTOKEN,
            @CARDHOLDERNAME = @CARDHOLDERNAME,
            @CREDITCARDPARTIALNUMBER = @CREDITCARDNUMBER,
            @CREDITTYPECODEID = @CREDITTYPECODEID,
            @EXPIRESON = @EXPIRESON,
            @CHANGEAGENTID = @CHANGEAGENTID,
            @CURRENTDATE = @CURRENTDATE
        end

        insert into dbo.REVENUESCHEDULE (ID, SENDPLEDGEREMINDER, STARTDATE, ENDDATE, FREQUENCYCODE, NEXTTRANSACTIONDATE, CREDITCARDID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
          values (@ID, @SENDPLEDGEREMINDER, @INSTALLMENTSTARTDATE, @INSTALLMENTENDDATE, @INSTALLMENTFREQUENCYCODE, @NEXTTRANSACTIONDATE, @RECURRINGGIFTCREDITCARDID, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE);

        -- add first installment
        insert into dbo.RECURRINGGIFTINSTALLMENT (ID, REVENUEID, AMOUNT, DATE, BASECURRENCYID, ORGANIZATIONAMOUNT, ORGANIZATIONEXCHANGERATEID, TRANSACTIONAMOUNT, TRANSACTIONCURRENCYID, BASEEXCHANGERATEID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
        values (newid(), @ID, @AMOUNT, @NEXTTRANSACTIONDATE, @ORGANIZATIONCURRENCYID, @AMOUNT, null, @AMOUNT, @ORGANIZATIONCURRENCYID, null, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE);

        -- add any additional installments to get us current
        exec dbo.USP_RECURRINGGIFT_ADDMISSINGINSTALLMENTS
          @ID,
          @CHANGEAGENTID,
          @CURRENTDATE,
          @AMOUNT

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

          set @SENDNEWINSTRUCTION = 0;
          set @NEWINSTRUCTIONTOSEND = 0;

          if not @DDISOURCECODEID is null
          begin
            set @SENDNEWINSTRUCTION = 1;
            set @NEWINSTRUCTIONTOSEND = 1;
          end


          if @PAYMENTMETHODCODE = 3 and @DDISOURCECODEID is not null and dbo.UFN_PAPERLESSMANDATES_DAYSADVANCEDNOTICE_FORREVENUE(@ID) = -1
          begin
            raiserror('BBERR_MULTIPLE_PAPERLESSMANDATECONFIGS', 13, 1);
            return 1;
          end

          insert into dbo.REVENUESCHEDULEDIRECTDEBITPAYMENT(ID, REFERENCEDATE, REFERENCENUMBER, CONSTITUENTACCOUNTID, DDISOURCECODEID, DDISOURCEDATE, SENDPMINSTRUCTION, PMINSTRUCTIONTOSENDCODE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, DIRECTDEBITRESULTCODE)
            values (@ID, @REFERENCEDATE, @REFERENCENUMBER, @CONSTITUENTACCOUNTID, @DDISOURCECODEID, @DDISOURCEDATE, @SENDNEWINSTRUCTION, @NEWINSTRUCTIONTOSEND, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE, @DIRECTDEBITRESULTCODE);
        end

        if @PAYMENTMETHODCODE = 11 -- Standing Order
        begin
          insert into dbo.REVENUESCHEDULESTANDINGORDERPAYMENT(ID, REFERENCEDATE, REFERENCENUMBER, CONSTITUENTACCOUNTID, STANDINGORDERSETUP, STANDINGORDERSETUPDATE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
            values (@ID, @REFERENCEDATE, @STANDINGORDERREFERENCENUMBER, @CONSTITUENTACCOUNTID, @STANDINGORDERSETUP, @STANDINGORDERSETUPDATE, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE);

          insert into dbo.REVENUESTANDINGORDER(ID, CUSTOMREFERENCENUMBER, USESYSTEMGENERATEDREFERENCENUMBER, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
            values(@ID, @STANDINGORDERREFERENCENUMBER, @GENERATEREFERENCENUMBER, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE);
        end

        if not @SOLICITORS is null
          exec dbo.USP_REVENUEBATCH_ADDSOLICITORS @ID, @SOLICITORS, @CHANGEAGENTID, @CURRENTDATE;

        if not @RECOGNITIONS is null and not @GIVENANONYMOUSLY = 1
          exec dbo.USP_REVENUEBATCH_ADDRECOGNITIONS @ID, @RECOGNITIONS, @CHANGEAGENTID, @CURRENTDATE;

        --Update the organization total value, transaction total value, and exchange rates in the @TOTALBENEFITS XML.
        set @TOTALBENEFITS = dbo.UFN_REVENUEBENEFIT_CONVERTAMOUNTSINXML(@TOTALBENEFITS,@ORGANIZATIONCURRENCYID);
        exec dbo.USP_REVENUE_GETBENEFITS_3_ADDFROMXML @ID, @TOTALBENEFITS, @CHANGEAGENTID, @CURRENTDATE;

        if (@NOTETITLE <> '') or (not @NOTEAUTHORID is null) or (not @NOTEDATEENTERED is null) or (not @NOTETYPECODEID is null) or (not @NOTETEXTNOTE = '') 
          insert into dbo.REVENUENOTE(DATEENTERED, TITLE, AUTHORID, TEXTNOTE, REVENUENOTETYPECODEID, REVENUEID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
            values(@NOTEDATEENTERED, @NOTETITLE, @NOTEAUTHORID, @NOTETEXTNOTE, @NOTETYPECODEID, @ID, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE)

        if @AMOUNTFORVAT > 0
          insert into dbo.REVENUEVAT(ID, AMOUNTTOTAX, VATTAXRATEID, VATAMOUNT, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
            values(@ID, @AMOUNTFORVAT, @VATTAXRATEID, @VATAMOUNT, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE);
      end

      -- Tax declarations can be added for all transaction types
      if @TAXDECLARATIONS is not null
      begin
        -- Make sure the constituent is an individual
        if exists (select 1 from dbo.CONSTITUENT where ID = @CONSTITUENTID and ISORGANIZATION = 0 and ISGROUP = 0)
        begin
          insert into dbo.TAXDECLARATION
          (
            CONSTITUENTID,
            DECLARATIONINDICATORCODE, 
            DECLARATIONMADE, 
            DECLARATIONSTARTS, 
            DECLARATIONENDS, 
            CHARITYCLAIMREFERENCENUMBERID, 
            PAYSTAXCODE,
            DECLARATIONSOURCECODEID,
            ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
          )
   select
            @CONSTITUENTID,
            DECLARATIONINDICATORCODE, 
            DECLARATIONMADE, 
            DECLARATIONSTARTS, 
            DECLARATIONENDS, 
            CHARITYCLAIMREFERENCENUMBERID, 
            PAYSTAXCODE,
            DECLARATIONSOURCECODEID,
            @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
          from dbo.UFN_REVENUEBATCH_GETTAXDECLARATIONS_FROMITEMLISTXML(@TAXDECLARATIONS)

          -- Only update tribute gift aid amounts if there is a Yes declaration since this is the only
          -- state that will affect gift aid eligibility
          if exists (select 1 from dbo.UFN_CONSTITUENTBATCH_GETTAXDECLARATIONS_FROMITEMLISTXML(@TAXDECLARATIONS) where PAYSTAXCODE = 1)
            exec dbo.USP_REVENUETRIBUTETAXCLAIMAMOUNT_ADDUPDATEBYCONSTITUENT @CONSTITUENTID, @CHANGEAGENTID, @CURRENTDATE;
        end
      end

      --Only create the records if this is a UK product and there were tributes included in this batch
      if dbo.UFN_INSTALLEDPRODUCTS_PRODUCTIS('9568A6C2-F7AA-45fd-8F54-21FE9654EE2D') = 1 and @TRIBUTES is not null
        exec dbo.USP_REVENUETRIBUTETAXCLAIMAMOUNT_ADDUPDATE @ID, @CHANGEAGENTID, @CURRENTDATE;

    end
  end try

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

  return 0;