USP_DATAFORMTEMPLATE_ADD_2_REVENUEBATCHCOMMIT

CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_2_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) = '',
  @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,
  @OTHERPAYMENTMETHODCODEID uniqueidentifier = null,
  @LETTERCODEID as uniqueidentifier = null,
  @ACKNOWLEDGEDATE as datetime = null,
  @REFERENCE as nvarchar(255) = null,
  @CATEGORYCODEID uniqueidentifier = null,
  @ACKNOWLEDGEEID uniqueidentifier = null,
  @APPLICATIONINFO as nvarchar(60) = null,
  @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(500) = '',
  -- PARTIALCREDITCARDNUMBER is used to support import and 

  -- credit card - last 4 digits recurring gifts

  @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,
  @ADDITIONALAPPLICATIONSSTREAM xml = null,
  @REVENUELOOKUPID nvarchar(100) = '',
  @APPLICATIONSOLICITORS xml = null,
  @APPLICATIONRECOGNITIONS xml = null,
  @CURRENTAPPUSERID uniqueidentifier = null,
  @MGGENERATED bit = null,
  @MGALTERED bit = null,
  @MATCHINGGIFTS xml = null,
  @PAYINGPENDINGREVENUEID uniqueidentifier = null,
  @GIFTINKINDITEMNAME nvarchar(100) = '',
  @GIFTINKINDDISPOSITIONCODE tinyint = 0,
  @GIFTINKINDNUMBEROFUNITS int = 0,
  @GIFTINKINDFAIRMARKETVALUE money = 0,
  @DIRECTDEBITISREJECTED bit = null,
  @PERCENTAGEBENEFITS xml = null,
  @BATCHROWID uniqueidentifier = null,
  @ISGIFTAIDSPONSORSHIP bit = null,
  @LOCKBOXID uniqueidentifier = null,
  @LOCKBOXBATCHNUMBER nvarchar(100) = '',
  @LOCKBOXBATCHSEQUENCE int = 0,
  @PDACCOUNTSYSTEMID uniqueidentifier = null,
  @APPLICATIONBUSINESSUNITS xml = null,
  @TRANSACTIONCURRENCYID uniqueidentifier = null,
  @BASECURRENCYID uniqueidentifier = null,
  @BASEEXCHANGERATEID uniqueidentifier = null,
  @EXCHANGERATE decimal(20,8) = null,
  @GENERATEREFERENCENUMBER bit = 1,
  @APPLYBYPERCENT bit = 0, -- AdiSa: This field is for UI purposes only, but needs to be stored in the database.

  @SOURCECODEIMPORT nvarchar(60) = '',
  @MERCHANTACCOUNTID uniqueidentifier = null,
  @SETNULLBASEEXCHANGERATETOLATEST bit = 1,
  @SALE_LOWPRICE decimal(19,4) = null,
  @SALE_MEDIANPRICE decimal(19,4) = null,
  @SALE_HIGHPRICE decimal(19,4) = null,
  @VENDORID nvarchar(50) = '',
  @BBNCTRANID int = 0,
  @BBNCORIGINPAGENAME nvarchar(100) = '',
  @BBNCORIGINPAGEID int = 0,
  @RECEIPTNUMBER nvarchar(30) = null,
  @RECEIPTSTACKSHORTNAME nvarchar(20) = '',
  @MARKGIFTASRECEIPTED bit = 0,
  @EMAILID int = 0,
  @EMAILSUBJECT nvarchar(4000) = '',
  @EMAILNAME nvarchar(510) = '',
  @NAMECODE tinyint = 1,
  @SIMILARADDRESSCODE tinyint = 3,
  @UNSIMILARADDRESSCODE tinyint = 3,
  @NEWADDRESSENDDATECODE tinyint = 0,
  @NEWADDRESSPRIMARYCODE tinyint = 1,
  @BIRTHDATERULECODE  tinyint = 0,
  @DIFFERENTPHONECODE tinyint = 3,
  @NEWPHONEENDDATECODE tinyint = 0,
  @NEWPHONEPRIMARYCODE tinyint = 1,
  @DIFFERENTEMAILCODE tinyint = 3,
  @NEWEMAILENDDATECODE tinyint = 0,
  @NEWEMAILPRIMARYCODE tinyint = 1,
  @USEGLOBALSETTINGS bit = 1,
  @CREATEHISTORICALNAMECODE tinyint = 1,
  @PAYMENTFORPLEDGERECEIPTAMOUNT money = 0,
  @SEPAMANDATEID uniqueidentifier = null,
  @ADDSEPAMANDATE bit = 0,
  @SEPAMANDATECUSTOMIDENTIFIER nvarchar(35) = '',
  @SEPAMANDATESIGNATUREDATE date = null,
  @SEPAMANDATETYPECODE tinyint = 0,
  @INSTALLMENTAMOUNT money = 0,
  @REQUIRECREDITCARDPROCESSING bit = 0,
  @NOTEHTMLNOTE nvarchar(max) = '',
  @IMPORT bit = 0,
  @SOLICITCODES xml = null
)
as
  set nocount on;

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

  set @ADDITIONALAPPLICATIONS = 0;
  set @TRANSACTIONAMOUNT = @AMOUNT;

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

  set @CURRENTDATE = GetDate();

  declare @NEXTTRANSACTIONDATE datetime;

  -- Multicurrency setup

  declare @BASEAMOUNT money;
  declare @ORGANIZATIONAMOUNT money;
  declare @ORGANIZATIONCURRENCYID uniqueidentifier;
  declare @ORGANIZATIONEXCHANGERATEID uniqueidentifier;
  declare @TRANSACTIONVATAMOUNT money;
  declare @TRANSACTIONAMOUNTTOTAX money;
  declare @BASEVATAMOUNT money;
  declare @BASEAMOUNTTOTAX money;
  declare @ORGANIZATIONVATAMOUNT money;
  declare @ORGANIZATIONAMOUNTTOTAX money;
  declare @BASETOORGANIZATIONEXCHANGERATEID uniqueidentifier;  

  set @ORGANIZATIONCURRENCYID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();

  -- Grab base and transaction currency if they weren't provided.

  -- First make sure we have an account system, and get the base currency from that.

  -- Then make sure we've got a base currency, and get the transaction currency from that.


  if @PDACCOUNTSYSTEMID is null and @TYPECODE <> 3
    set @PDACCOUNTSYSTEMID = dbo.UFN_PDACCOUNTSYSTEM_GETDEFAULTSYSTEMIDSFORUSER(@CURRENTAPPUSERID)

  --Use the following logic for recurring gifts.

  if @TYPECODE = 3
    begin

          -- Check for BBIS batch assignment settings.

          if @BBNCTRANID > 0
          begin

              declare @MAPPINGRULEID uniqueidentifier 
                declare @BID uniqueidentifier 
                declare @ORIGINATINGBATCHID uniqueidentifier
              declare @COUNTER int = 1

                select @BID = BATCHID from dbo.BATCHREVENUE BR where BR.ID = @BATCHROWID
                select @ORIGINATINGBATCHID = ORIGINATINGBATCHID from dbo.BATCH where ID = @BID

                -- This logic will Returns the original batch id if batch has multiple exception batches.

                -- Also only original batch has its reference with NETCOMMUNITYMAPPINGRULEBATCH record in order to get batch assignment account system id

                -- COUNTER variable here with take care while loop not get infinite 

                while (@ORIGINATINGBATCHID IS NOT NULL AND @COUNTER <= 100)
                begin
                    select @ORIGINATINGBATCHID = ORIGINATINGBATCHID,  @BID = ID from dbo.BATCH where ID = @ORIGINATINGBATCHID;
                    set @COUNTER = @COUNTER + 1;
                end

                select @MAPPINGRULEID = MAPPINGRULEID from dbo.NETCOMMUNITYMAPPINGRULEBATCH where BATCHID = @BID

              select @PDACCOUNTSYSTEMID = DEFAULTPDACCOUNTSYSTEMID from dbo.NETCOMMUNITYMAPPINGRULE NCMR left join dbo.NETCOMMUNITYTRANSACTIONPROCESSOR NCTP on NCMR.NETCOMMUNITYTRANSACTIONPROCESSORID = NCTP.ID
              right join dbo.BATCHREVENUEBBNCINFO BRB on BRB.NETCOMMUNITYTRANSACTIONPROCESSORID = NCTP.ID
              where BATCHREVENUEID = @BATCHROWID AND NCMR.ID = @MAPPINGRULEID

        end

        -- Check for default in batch template.

        if @PDACCOUNTSYSTEMID is null
        begin

        with xmlnamespaces('bb_appfx_commontypes' as ns)
            ,CTE_BATCHTEMPLATEDETAILS as
            (
                select t.c.value('@FieldID','varchar(max)') AS FieldID,t.c.value('@DefaultValueText','varchar(max)') AS DefaultValueText
                from (select BT.FORMDEFINITIONXML from BATCHREVENUE BR 
                left join BATCH B on BR.BATCHID = B.ID left join 
                BATCHTEMPLATE BT on BT.ID = B.BATCHTEMPLATEID
                where BR.ID = @BATCHROWID) B
                cross apply B.FORMDEFINITIONXML.nodes('//ns:FormFields/ns:FormField') t(c)
            )
            select @PDACCOUNTSYSTEMID = DefaultValueText from CTE_BATCHTEMPLATEDETAILS
            where FieldID = 'PDACCOUNTSYSTEMID'

        end

        -- If account is still not populated, fall back to user's default account system.

        if @PDACCOUNTSYSTEMID is null
            set @PDACCOUNTSYSTEMID = dbo.UFN_PDACCOUNTSYSTEM_GETDEFAULTSYSTEMIDSFORUSER(@CURRENTAPPUSERID)

  end

  -- Make sure @brokerfee is not null (avoid non-nullable error)

  if @BROKERFEE is null
    set @BROKERFEE = 0;

  if @BASECURRENCYID is null
  begin
    declare @CURRENCYSETID uniqueidentifier
    select @CURRENCYSETID = CURRENCYSETID
    from dbo.PDACCOUNTSYSTEM
    where ID = @PDACCOUNTSYSTEMID

    select 
      @BASECURRENCYID = CURRENCYSET.BASECURRENCYID
    from 
      dbo.CURRENCYSET
    where 
      CURRENCYSET.ID = coalesce(@CURRENCYSETID,dbo.UFN_CURRENCYSET_GETAPPUSERCURRENCYSET(@CURRENTAPPUSERID));

    if @BASECURRENCYID is null
      set @BASECURRENCYID = @ORGANIZATIONCURRENCYID;
  end

  if @TRANSACTIONCURRENCYID is null
    set @TRANSACTIONCURRENCYID = @BASECURRENCYID;

  if @EXCHANGERATE is null
    set @EXCHANGERATE = 0;

  -- Create the base exchange rate when "Spot rate" is selected.

  if @BASEEXCHANGERATEID = '00000000-0000-0000-0000-000000000001'
  begin
    set @BASEEXCHANGERATEID = newid();

    --Replace with call to USP_CURRENCYEXCHANGERATE_ENSURESPOTRATEEXISTS for PBI 102747 in the future

    /*if not dbo.UFN_SECURITY_APPUSER_GRANTED_SYSTEMPRIVILEGE_IN_SYSTEMROLE(@CURRENTAPPUSERID,'911f104d-ba5f-4469-b0ae-184c879aea99') = 1
      and not dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1
    begin
      raiserror('User does not have the right to add a new spot rate.', 13, 1);
      return 1;
    end*/

    insert into dbo.CURRENCYEXCHANGERATE(
      ID, 
      FROMCURRENCYID,
      TOCURRENCYID,
      RATE,
      ASOFDATE,
      TYPECODE,
      SOURCECODEID,
      ADDEDBYID, 
      CHANGEDBYID, 
      DATEADDED, 
      DATECHANGED
    )
    values(
      @BASEEXCHANGERATEID,
      @TRANSACTIONCURRENCYID,
      @BASECURRENCYID,
      @EXCHANGERATE,
      @DATE,
      2,
      null,
      @CHANGEAGENTID,
      @CHANGEAGENTID,
      @CURRENTDATE,
      @CURRENTDATE
    );
  end

  -- Populate the other amount fields and the organization exchange rate.

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

  -- Get the default anonymous recognition setting. 

  declare @DEFAULTANONYMOUSRECOGNITION bit = 0;
  if @GIVENANONYMOUSLY = 1
      begin
        --Select the value for default anonymous recognition setting

        select @DEFAULTANONYMOUSRECOGNITION = DEFAULTANONYMOUSRECOGNITION
        from dbo.RECOGNITIONDEFAULT
      end

  -- Add transaction currency to the @SPLITS collection to help in validation.

  set @SPLITS =
  (
    select
      ID,
      DESIGNATIONID,
      AMOUNT,
      SEQUENCE,
      APPLICATIONCODE,
      TYPECODE,
      DIDCAMPAIGNSDEFAULT,
      case when CAMPAIGNS is null then null else CAMPAIGNS.query('(CAMPAIGNS/ITEM)') end as CAMPAIGNS,
      DECLINESGIFTAID,
      ISGIFTAIDSPONSORSHIP,
      REVENUESPLITID,
      @TRANSACTIONCURRENCYID TRANSACTIONCURRENCYID
    from dbo.UFN_REVENUEBATCH_SPLITSWITHCHILDREN_FROMITEMLISTXML(@SPLITS)
    for xml raw('ITEM'),type,elements,root('SPLITS'),BINARY BASE64
  );

  if @PAYMENTFORPLEDGEAMOUNT is null
    set @PAYMENTFORPLEDGEAMOUNT = 0;

  if @PAYMENTFORPLEDGERECEIPTAMOUNT is null
    set @PAYMENTFORPLEDGERECEIPTAMOUNT = 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

    if @REFERENCEDATE is null or @REFERENCEDATE = '' or @REFERENCEDATE = '00000000'
      select @REFERENCEDATE = convert(nvarchar,DATEPART(yyyy,@CURRENTDATE)) + right('00' + convert(nvarchar,DATEPART(mm,@CURRENTDATE)),2) + right('00' + convert(nvarchar,DATEPART(dd,@CURRENTDATE)),2);
  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 OR Check GL business rule for this account system 

  if @TYPECODE = 3 or dbo.UFN_PDACCOUNTSYSTEM_ALLOWGLDISTRIBUTIONS(@PDACCOUNTSYSTEMID) = 0
    set @POSTSTATUSCODE = 2;
  if @POSTSTATUSCODE = 2 --Do not post

    set @POSTDATE = null
  else
    if @POSTDATE is null
      set @POSTDATE = @DATE

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

  --Validate post date --JamesWill WI164979 2011-07-01 Don't validate post date if we're not posting this

  declare @ERROR nvarchar(255)
  if @DONOTPOST <> 1 
  begin
    set @ERROR = dbo.UFN_GLDISTRIBUTION_VALIDPOSTDATE(@POSTDATE)
    if @ERROR <> ''
    raiserror(@ERROR,13,1)
  end



  set @APPLIEDTOPLEDGES = 0;

  if @RECEIPTAMOUNT is null
    set @RECEIPTAMOUNT = 0;

  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

  --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 @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)) and (@DONOTPOST = 0)
        exec dbo.USP_REVENUE_BENEFITS_VALIDATEGLMAPPINGS @TYPECODE, @PDACCOUNTSYSTEMID, @CURRENTAPPUSERID, @BENEFITS, @PERCENTAGEBENEFITS
    end

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

    --Remove benefits if they are waived

    if @BENEFITSWAIVED = 1
      set @TOTALBENEFITS = null;

    --Update the organization total value, transaction total value, and exchange rates in the @TOTALBENEFITS XML.

    set @TOTALBENEFITS = dbo.UFN_REVENUEBENEFIT_CONVERTAMOUNTSINXML_2(@TOTALBENEFITS,@TRANSACTIONCURRENCYID,@BASECURRENCYID);

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

    declare @TOTALBENEFITAMOUNT money;


    /*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. */
    --[157230] Don't default GIVENANONYMOUSLY if it is set to true

    if @GIVENANONYMOUSLY <> 1
    begin
      if @GIVENANONYMOUSLYDEFAULTEDFORCONSTITUENTID is null or @GIVENANONYMOUSLYDEFAULTEDFORCONSTITUENTID <> @CONSTITUENTID
      begin
        select
          @GIVENANONYMOUSLY = GIVESANONYMOUSLY
        from dbo.CONSTITUENT
        where ID = @CONSTITUENTID;
      end
    end

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

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

    if @NUMBEROFINSTALLMENTS = 0
      set @NUMBEROFINSTALLMENTS = 1;

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

    if @LOCKBOXID is not null
      if @LOCKBOXBATCHNUMBER is null or @LOCKBOXBATCHSEQUENCE is null or Len(@LOCKBOXBATCHNUMBER) = 0
        raiserror('INVALID_LOCKBOXREQUIREDFIELDS', 13, 1);

    if @LOCKBOXBATCHSEQUENCE < 0
      raiserror('INVALID_LOCKBOXBATCHSEQUENCEFIELDS', 13, 1);

    if @BENEFITSWAIVED = 0  
    begin
      select @TOTALBENEFITAMOUNT = coalesce(sum(TRANSACTIONTOTALVALUE), 0)
      from dbo.UFN_REVENUE_GETBENEFITS_3_FROMITEMLISTXML(@TOTALBENEFITS);

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

    -- Currently recurring gifts have no GL mappings; JamesWill WI142554 payment methods of "Cash", "Check", "Credit Card", "None" and "Credit card - last 4 digits" can't be mapped, so this will throw a bad validation message

    -- Pledge with payment method of "None" and "Credit Card" must be validated

    if (@POSTSTATUSCODE = 1) and (@VALIDATEONLY = 1) and 
    (((@TYPECODE <> 3) and @PAYMENTMETHODCODE not in (0,1,2,9,98)) or ((@TYPECODE = 1) and @PAYMENTMETHODCODE in (2,9)))
      exec dbo.USP_REVENUE_VALIDATEGLMAPPING @BATCHROWID, 1, @CURRENTAPPUSERID;

    declare @SPLITCOUNT int
    select @SPLITCOUNT = count(*) from @SPLITS.nodes('/SPLITS/ITEM') T(c)
    declare @ADDITIONALAPPLICATIONCOUNT int

    select @ADDITIONALAPPLICATIONCOUNT = count(*) from @ADDITIONALAPPLICATIONSSTREAM.nodes('/ADDITIONALAPPLICATIONSSTREAM/ITEM') T(c)                

    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 @CONSTITUENTEXISTS bit
    if exists(select ID from dbo.CONSTITUENT where ID = @CONSTITUENTID) 
      set @CONSTITUENTEXISTS = 1
    else
      set @CONSTITUENTEXISTS = 0

    -- Constituent record can't be restricted if it didn't exist already in the database,

    -- so don't bother checking security if it's a constituent being added by this commit.

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

    if @TYPECODE = 0  -- payment

    begin

    -- Revenue category not passed in case of pledge payment

    if @REVENUESTREAMS is not null and @MATCHINGGIFTS is not null and @CATEGORYCODEID is null
    begin
        declare @PLEDGETYPECODE int = 0;
        declare @APPLICATIONID uniqueidentifier = null;

        select @PLEDGETYPECODE = TYPECODE, @APPLICATIONID = APPLICATIONID
        from dbo.UFN_REVENUEBATCH_GETAPPLICATIONS_FROMITEMLISTXML(@REVENUESTREAMS);

        if @PLEDGETYPECODE = 1 and  @APPLICATIONID is not null 
        begin
            select @CATEGORYCODEID = RC.GLREVENUECATEGORYMAPPINGID 
            from dbo.REVENUECATEGORY RC 
              inner join FINANCIALTRANSACTIONLINEITEM FTLI on FTLI.ID = RC.ID 
              inner join REVENUE_EXT RE on RE.ID = FTLI.FINANCIALTRANSACTIONID 
            where RE.ID = @APPLICATIONID;

            if @CATEGORYCODEID = CAST(CAST(0 AS BINARY) AS UNIQUEIDENTIFIER)
              set @CATEGORYCODEID = null
        end
    end    
      --Bug 158105 - AdamBu - 6/13/11 - Ensure that validation catches missing exchange rates.

      if @VALIDATEONLY = 1 and @REVENUESTREAMS is not null and @BASEEXCHANGERATEID is null and @TRANSACTIONCURRENCYID <> @BASECURRENCYID
      begin
        if exists(
          select 1
          from dbo.UFN_REVENUEBATCH_GETAPPLICATIONS_FROMITEMLISTXML(@REVENUESTREAMS)
          where not (
            TYPECODE in (0,4,11,100)
          )
        )
        begin
          raiserror('BBERR_INVALIDAPPLICATIONSWITHNORATE : Payments without an exchange rate can only be applied to the donation, other, or unapplied matching gift application types.', 13, 1);
          return 1;
        end
      end

      if @PAYMENTMETHODCODE = 255
        set @PAYMENTMETHODCODE = null;

      if @PAYMENTMETHODCODE = 2 and  @AMOUNT > 0
      begin
        if @VALIDATEONLY = 0
        begin
            --If attempting to pay by credit card  with the intent to process (token is not null), ensure an authorization code exists.

            --This prevents a batch of credit card payments from being committed without the process running. 

            if @CREDITCARDTOKEN is not null and len(@AUTHORIZATIONCODE) = 0
            begin
              raiserror('BBERR_CREDITCARD_NOTPROCESSED', 13, 1);
            end
        end

        --If REQUIRECREDITCARDPROCESSING is true and we don't have a token then the only way we want this batch to commit is if the user enters an authorization code.

        if @CREDITCARDTOKEN is null and len(@AUTHORIZATIONCODE) = 0 and @REQUIRECREDITCARDPROCESSING = 1 
        begin
            raiserror('BBERR_CREDITCARD_MISSINGTOKEN', 13, 1);
        end
      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)) 
          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 and (@POSTSTATUSCODE = 1)
              exec dbo.USP_REVENUE_STOCK_VALIDATEGLMAPPINGS @PDACCOUNTSYSTEMID, @CURRENTAPPUSERID, @AMOUNT, @SALEAMOUNT, @NUMBEROFUNITSSOLD, @MEDIANPRICE, @BROKERFEE

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

        if @NUMBEROFUNITSSOLD > @NUMBEROFUNITS
          raiserror('Units sold must be less than or equal to units remaining.', 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

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

        if @GIFTINKINDFAIRMARKETVALUE < 0
          raiserror('CK_GIFTINKINDPAYMENTMETHODDETAIL_VALIDFORFAIRMARKETVALUE', 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

      -- 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 = 255 or @PAYMENTMETHODCODE is null
        raiserror('Only pledges or recurring gifts can have a blank payment method.', 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

      if @PAYMENTMETHODCODE = 98 --Credit card - last 4 digits

        raiserror('Only recurring gifts can have a payment method of ''Credit card - last 4 digits''.', 13, 1);

      declare @APPLICATIONCODE tinyint;
      declare @SINGLEAPPLICATIONID uniqueidentifier;
      declare @APPLICATIONTYPECODE tinyint;
      declare @COMMITMENTAMOUNTDUE money;                   -- The amount due on the commitment in its currency.

      declare @APPLICATIONCURRENCYID uniqueidentifier;      -- The currency of the commitment.

      declare @APPLICATIONEXCHANGERATEID uniqueidentifier;  -- The exchange rate from row trans currency to the commitment currency.

      declare @APPLICATIONCURRENCYAMOUNT money = @AMOUNT;

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

      if @APPLICATIONINFO is not null        
        if len(@APPLICATIONINFO) > 0          
          if len(@APPLICATIONINFO) = 3
          begin
            set @APPLICATIONCODE = cast(substring(@APPLICATIONINFO, 3, 1) as tinyint);
            set @revenuestreams = null

            --JAK 10/21/10 bug 124899 - need to pull campaigns from additional applications in case the user modified them

            if @ADDITIONALAPPLICATIONCOUNT = 1
            begin
              --DavidHe 11/11/2010 Bug 127918 - If category code isn't set in the row, but is set on a single additional application, set the value

              if @CATEGORYCODEID is null
                select 
                  @CATEGORYCODEID = CATEGORYCODEID
                from dbo.UFN_REVENUEBATCH_ADDITIONALAPPLICATIONWITHCHILDREN_FROMITEMLISTXML(@ADDITIONALAPPLICATIONSSTREAM)
            end
          end
          else if len(@APPLICATIONINFO) > 3
          begin
            select  
              @SINGLEAPPLICATIONID = SINGLEAPPLICATIONID,
              @APPLICATIONTYPECODE = APPLICATIONTYPECODE,
              @COMMITMENTAMOUNTDUE = APPLICATIONAMOUNT
            from dbo.UFN_REVENUEBATCH_PARSEAPPLICATIONINFO(@APPLICATIONINFO)

            set @APPLICATIONCURRENCYID = 
              coalesce(
                dbo.UFN_REVENUEBATCH_GETCURRENCYFORAPPLICATION(@SINGLEAPPLICATIONID, @APPLICATIONTYPECODE),
                @TRANSACTIONCURRENCYID
              );

            if @TRANSACTIONCURRENCYID = @APPLICATIONCURRENCYID
            begin
              set @APPLICATIONCURRENCYAMOUNT = @AMOUNT;
            end
            else
            begin
              if @BASECURRENCYID = @APPLICATIONCURRENCYID
              begin
                set @APPLICATIONEXCHANGERATEID = @BASEEXCHANGERATEID
              end
              else
              begin
                set @APPLICATIONEXCHANGERATEID = dbo.UFN_CURRENCYEXCHANGERATE_GETLATEST(@TRANSACTIONCURRENCYID, @APPLICATIONCURRENCYID, @DATE, 0, null);
              end
                set @APPLICATIONCURRENCYAMOUNT = dbo.UFN_CURRENCY_CONVERT(@AMOUNT, @APPLICATIONEXCHANGERATEID);
            end

            set @SINGLEDESIGNATIONID = null
            set @SPLITS = null
            set @ADDITIONALAPPLICATIONSSTREAM = null
          end

      declare @UNAPPLIEDAMOUNT money;
      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 null
        if @REVENUESTREAMS is not null and @STREAMCOUNT > 0
        begin
          select @UNAPPLIEDAMOUNT = sum(APPLIED)
          from dbo.UFN_REVENUEBATCH_GETAPPLICATIONS_FROMITEMLISTXML(@REVENUESTREAMS);

          if @UNAPPLIEDAMOUNT + @ADDITIONALAPPLICATIONSAMOUNT <> @AMOUNT
            raiserror('The total amount must be equal to the sum of the amounts applied to commitments.', 13, 1);                  

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

          -- User is unable to modify applied amounts for EFT batches (PAYINGPENDINGREVENUEID), so skip this check in that case.

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

            if @UNAPPLIEDAMOUNT <> @AMOUNT
              raiserror('The total amount must be equal to the sum of the amounts applied to commitments.', 13, 1);
          end
          else
            set @UNAPPLIEDAMOUNT = @AMOUNT

        --I do not believe this is a valid error - you should be able to over-apply a single commitment (bug 169531)

        --if @COMMITMENTAMOUNTDUE < @APPLICATIONCURRENCYAMOUNT

        --  raiserror('The total amount must be equal to the sum of the amounts applied to commitments.', 13, 1);

      end

      declare @REVENUESTREAMSAPPLIEDAMOUNT money = @UNAPPLIEDAMOUNT
      if (@APPLICATIONINFO is null or len(@APPLICATIONINFO) = 0)
        and (@ADDITIONALAPPLICATIONSAMOUNT = 0 and @ADDITIONALAPPLICATIONSSTREAM is null)
        and (@REVENUESTREAMS is null or @REVENUESTREAMSAPPLIEDAMOUNT = 0)
        -- no single app info + no revenue stream + no additional amounts = problem (no application) [bug #124131]

        raiserror('ERR_ATLEASTONEAPPLICATION', 13, 1);

      set @UNAPPLIEDAMOUNT = @AMOUNT - @UNAPPLIEDAMOUNT;

      if not @REVENUESTREAMS is null and @STREAMCOUNT > 0
      begin
        exec dbo.USP_REVENUEBATCH_VALIDATEREVENUESTREAMS @AMOUNT, @REVENUESTREAMS, @PAYMENTMETHODCODE, @TRANSACTIONCURRENCYID, @DATE, @BASECURRENCYID, @BASEEXCHANGERATEID;
      end

      --KevinKoe 5/23/11 -- If a pledge was added through the "Apply to commitments" window but is not being applied to, then throw an error

      if exists(select ID from dbo.BATCHREVENUEAPPLICATION where BATCHREVENUEID = @BATCHROWID and APPLIED = 0 and APPLICATIONTYPECODE = 2)
      begin
        raiserror('ERR_NEWPLEDGEADDED_UNAPPLIEDTO', 13, 1);
      end

      if @SINGLEAPPLICATIONID is null
      begin
        if not @ADDITIONALAPPLICATIONSSTREAM is null
          exec dbo.USP_REVENUEBATCH_VALIDATEADDITIONALAPPLICATIONS @ADDITIONALAPPLICATIONSSTREAM, @TYPECODE, @PAYMENTMETHODCODE, @CATEGORYCODEID, @ISORGANIZATION;
      end
      else
        exec dbo.USP_REVENUEBATCH_VALIDATESINGLEAPPLICATION @AMOUNT, @SINGLEAPPLICATIONID, @APPLICATIONTYPECODE, @APPLICATIONCURRENCYAMOUNT, @PAYMENTMETHODCODE;

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

      declare @BASEUNAPPLIEDAMOUNT money;
      if @BASECURRENCYID <> @TRANSACTIONCURRENCYID
        set @BASEUNAPPLIEDAMOUNT = dbo.UFN_CURRENCY_CONVERT(@UNAPPLIEDAMOUNT, @BASEEXCHANGERATEID);
      else
        set @BASEUNAPPLIEDAMOUNT = @UNAPPLIEDAMOUNT;

      if @APPLICATIONCODE is not null and @SPLITCOUNT > 1
        exec dbo.USP_REVENUEBATCH_VALIDATEAPPLICATIONSOLICITORS @APPLICATIONSOLICITORS, @SPLITS, @APPLICATIONCODE, null, @BASEEXCHANGERATEID
      else if @APPLICATIONCODE is null and @SINGLEAPPLICATIONID is null and @ADDITIONALAPPLICATIONCOUNT > 1
        exec dbo.USP_REVENUEBATCH_VALIDATEAPPLICATIONSOLICITORS @APPLICATIONSOLICITORS, null, null, @ADDITIONALAPPLICATIONSSTREAM, @BASEEXCHANGERATEID
      else if not @SOLICITORS is null
        exec dbo.USP_REVENUESOLICITOR_VALIDATESOLICITORS @SOLICITORS, @BASEUNAPPLIEDAMOUNT;

      if not @TRIBUTES is null
      begin
        if (select sum(AMOUNT) from dbo.UFN_REVENUE_GETTRIBUTES_2_FROMITEMLISTXML(@TRIBUTES)) > @BASEAMOUNT
          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, null, @TRANSACTIONCURRENCYID;

        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 - @ADDITIONALAPPLICATIONSAMOUNT > 0 or (@AMOUNT=0 and @ADDITIONALAPPLICATIONCOUNT <= 1 and @UNAPPLIEDAMOUNT - @ADDITIONALAPPLICATIONSAMOUNT=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 > @AMOUNT
        raiserror('The receipt amount must be less than or equal to the amount.', 13, 1);

      /* AAW 10/27/09: Updated to validate multiple matching gifts with multiple MG splits. */
      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('The matching gift amount cannot be negative.', 13, 1);
        -- Validate each matching gift splits collection:

        declare MGSPLITS_CURSOR cursor local fast_forward for
          select
            MATCHINGGIFTSXML.[MATCHINGGIFT].value('AMOUNT[1]', 'money'),
            MATCHINGGIFTSXML.[MATCHINGGIFT].query('SPLITS')
          from
            @MATCHINGGIFTS.nodes('MATCHINGGIFTS/ITEM') as MATCHINGGIFTSXML([MATCHINGGIFT])
          where
          -- Filter out records that don't have splits.  They won't be processed later when adding the matching gifts either.

          MATCHINGGIFTSXML.[MATCHINGGIFT].exist('SPLITS') = 1;

        declare @MGSPLITS xml;
        declare @MGAMOUNT money;

        open MGSPLITS_CURSOR;
        fetch next from MGSPLITS_CURSOR into @MGAMOUNT, @MGSPLITS;

        while @@fetch_status = 0
        begin
          -- Note that @MGAMOUNT is in transaction currency.

          -- Do not validate transaction currency of the splits as we are deriving transaction currency from the associated matching gift.

          exec dbo.USP_REVENUESPLIT_VALIDATESPLITS @MGSPLITS, @MGAMOUNT, @TYPECODE, null, null, 0;

          fetch next from MGSPLITS_CURSOR into @MGAMOUNT, @MGSPLITS;
        end

        close MGSPLITS_CURSOR;
        deallocate MGSPLITS_CURSOR;
      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
        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 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, null, @TRANSACTIONCURRENCYID;

        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, @BASEAMOUNT;

      if not @TRIBUTES is null
      begin
        if (select sum(AMOUNT) from dbo.UFN_REVENUE_GETTRIBUTES_2_FROMITEMLISTXML(@TRIBUTES)) > @BASEAMOUNT
          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);

      if @PAYMENTFORPLEDGERECEIPTAMOUNT > @PAYMENTFORPLEDGEAMOUNT
        raiserror('BBERR_PAYMENTFORPLEDGERECEIPTAMOUNT_RECEIPTAMOUNTLARGERTHANAMOUNT', 13, 1);
    end;
    else if @TYPECODE = 3  -- recurring gift

    begin

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

      if not @SPLITS is null
        exec dbo.USP_REVENUESPLIT_VALIDATESPLITS @SPLITS, @AMOUNT, @TYPECODE, null, @TRANSACTIONCURRENCYID;
      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, @BASEAMOUNT;

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

      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 (0, 1, 2, 3, 9, 10, 11, 98, 101, 102)
      begin
        if dbo.UFN_INSTALLEDPRODUCTS_PRODUCTIS('9568A6C2-F7AA-45fd-8F54-21FE9654EE2D') = 1
          raiserror('Payment method for a recurring gift must be ''Cash'', ''Check'', ''Credit card'', ''Credit card - last 4 digits'', ''Direct debit'', ''Other'', ''Standing order'', ''PayPal'', ''Venmo'', or ''None''.', 13, 1);
        else
          raiserror('Payment method for a recurring gift must be ''Cash'', ''Check'', ''Credit card'', ''Credit card - last 4 digits'', ''Direct debit'', ''Other'', ''PayPal'', ''Venmo'', or ''None''.', 13, 1);
      end

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

      --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 = 10 --Other

      begin
        if @OTHERPAYMENTMETHODCODEID is null
          raiserror('Please enter an ''Other method'' for other payment method 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 constituent fields if the constituent hasn't been created yet

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

    /* 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 @APPLICATIONCODE is not null and @SPLITCOUNT > 1 and @TYPECODE = 0
      exec dbo.USP_REVENUEBATCH_VALIDATEAPPLICATIONRECOGNITIONS @RECOGNITIONS, @SPLITS, @APPLICATIONCODE, NULL, @BATCHROWID
    else if @APPLICATIONCODE is null and @SINGLEAPPLICATIONID is null and @ADDITIONALAPPLICATIONCOUNT > 1 and @TYPECODE = 0
      exec dbo.USP_REVENUEBATCH_VALIDATEAPPLICATIONRECOGNITIONS @RECOGNITIONS, NULL, NULL, @ADDITIONALAPPLICATIONSSTREAM, @BATCHROWID
    else
      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 @DIRECTDEBITISREJECTED is null --JamesWill WI174301 2011-11-21 

      set @DIRECTDEBITISREJECTED = 0;

    if @DIRECTDEBITISREJECTED = 1
      raiserror('A rejected direct debit payment cannot be committed.', 13, 1);

    --Duplicate check for Revenue Lookup ID

    if @REVENUELOOKUPID is not null and rtrim(ltrim(@REVENUELOOKUPID)) <> '' and exists (select ID from dbo.FINANCIALTRANSACTION with (INDEX(IX_FINANCIALTRANSACTION_CALCULATEDUSERDEFINEDID)) where CALCULATEDUSERDEFINEDID = rtrim(ltrim(@REVENUELOOKUPID)) and ID <> @ID)
       raiserror('BBERR_DUPLICATELOOKUPID', 13, 1); 

    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

      if @TYPECODE = 0 -- payment

      begin
        if not @LOCKBOXID is null
        begin
          if exists(select * from dbo.REVENUELOCKBOX where LOCKBOXID=@LOCKBOXID and BATCHNUMBER=@LOCKBOXBATCHNUMBER and BATCHSEQUENCE=@LOCKBOXBATCHSEQUENCE)
            raiserror('ERR_UIX_REVENUELOCKBOX_LOCKBOXID_BATCHNUMBER_BATCHSEQUENCE', 13, 1);

          if dbo.UFN_BATCHREVENUE_LOCKBOXINFOEXISTS(@BATCHROWID, @LOCKBOXID, @LOCKBOXBATCHNUMBER, @LOCKBOXBATCHSEQUENCE)=1
            raiserror('ERR_UIX_REVENUELOCKBOX_LOCKBOXID_BATCHNUMBER_BATCHSEQUENCE', 13, 1);
        end
      end

    end

    -- Verify the solicit codes are valid

    if @SOLICITCODES is not null
    begin

        -- address the auto end date issues for consent based SC

        exec USP_BATCHREVENUE_ADJUSTSOLICITCODEDATERANGES @CONSTITUENTID, @SOLICITCODES, @CHANGEAGENTID;

        -- reload from table to reflect changes made above

        select @SOLICITCODES = dbo.UFN_BATCHREVENUE_GETCONSTITUENTSOLICITCODES_TOITEMLISTXML(@BATCHROWID);

        -- validate the SC

        declare @SOLICITCODESTABLEVALIDATION table
        (
            ID uniqueidentifier,
            STARTDATE datetime,
            ENDDATE datetime,
            SOLICITCODEID uniqueidentifier
        );

        insert into @SOLICITCODESTABLEVALIDATION
        (
            ID,
            STARTDATE,
            ENDDATE,
            SOLICITCODEID
        )
        select
            case when ID is null then newid() else ID end,
            STARTDATE,
            ENDDATE,
            SOLICITCODEID
        from dbo.UFN_BATCHREVENUE_GETCONSTITUENTSOLICITCODES_FROMITEMLISTXML(@SOLICITCODES);

        -- Check for solicit codes whose end date is before the start date

        if exists (    select 1
                    from @SOLICITCODESTABLEVALIDATION
                    where
                        STARTDATE is not null and
                        STARTDATE > ENDDATE)
            raiserror('BBERR_SOLICITCODES_STARTENDDATE', 13, 1);

        -- Check for records with the same solicit code and overlapping dates

        if exists (    select 1
                    from @SOLICITCODESTABLEVALIDATION SOLICITCODEONE
                    inner join @SOLICITCODESTABLEVALIDATION SOLICITCODETWO on
                        SOLICITCODEONE.ID <> SOLICITCODETWO.ID and
                        SOLICITCODEONE.SOLICITCODEID = SOLICITCODETWO.SOLICITCODEID and
                        dbo.UFN_DATES_AREDATESOVERLAPPING(SOLICITCODEONE.STARTDATE, SOLICITCODEONE.ENDDATE, SOLICITCODETWO.STARTDATE, SOLICITCODETWO.ENDDATE) = 1)
            raiserror('BBERR_SOLICITCODES_NOOVERLAPPINGDATES', 13, 1);
    end

    -- Apply edits to an existing constituent

    if @CONSTITUENTEXISTS <> 0
      begin
        if exists (select 'x' from dbo.BATCHCONSTITUENTUPDATE where ID = @BATCHROWID)
        begin
          declare
            @CONSTITUENTTYPECODE tinyint,
            @LASTNAME nvarchar(100),
            @ORGANIZATIONNAME nvarchar(100),
            @FIRSTNAME nvarchar(50),
            @MIDDLENAME nvarchar(50),
            @MAIDENNAME nvarchar(100),
            @NICKNAME nvarchar(50),
            @TITLECODEID uniqueidentifier,
            @SUFFIXCODEID uniqueidentifier,
            @GENDERCODE tinyint,
            @GENDERCODEID uniqueidentifier,
            @BIRTHDATE dbo.UDT_FUZZYDATE,
            @GIVESANONYMOUSLY bit,
            @WEBADDRESS dbo.UDT_WEBADDRESS,
            @ADDRESSES xml,
            @PHONES xml,
            @EMAILADDRESSES xml,
            @GROUP_DESCRIPTION nvarchar(300),
            @GROUP_GROUPTYPEID uniqueidentifier,
            @GROUP_STARTDATE datetime,
            @INDUSTRYCODEID uniqueidentifier,
            @NUMEMPLOYEES int,
            @NUMSUBSIDIARIES int,
            @PARENTCORPID uniqueidentifier,
            @MARITALSTATUSCODEID uniqueidentifier,
            @LOOKUPID nvarchar(100),
            @ALTERNATELOOKUPIDS xml,
            @SECURITYATTRIBUTES xml,
            @DECEASED bit,
            @DECEASEDDATE dbo.UDT_FUZZYDATE,
            @INTERESTS xml,
            @PROSPECTMANAGERFUNDRAISERID uniqueidentifier,
            @CONSTITUENCIES xml,
            @CONSTITUENT_SITES xml,
            -- BBIS fields 

            @BBISCONSTITUENT_SITES xml,
            @BBISSECURITYATTRIBUTES xml,
            @BBISCONSTITUENCIES xml,
            --spouse and business fields

            @SPOUSEID uniqueidentifier,
            @SPOUSE_LASTNAME nvarchar(100),
            @SPOUSE_FIRSTNAME nvarchar(50),
            @SPOUSE_MIDDLENAME nvarchar(50),
            @SPOUSE_MAIDENNAME nvarchar(100),
            @SPOUSE_NICKNAME nvarchar(50),
            @SPOUSE_TITLECODEID uniqueidentifier,
            @SPOUSE_SUFFIXCODEID uniqueidentifier,
            @SPOUSE_GENDERCODE tinyint,
            @SPOUSE_GENDERCODEID uniqueidentifier,
            @SPOUSE_BIRTHDATE dbo.UDT_FUZZYDATE,
            @SPOUSE_RECIPROCALTYPECODEID uniqueidentifier,
            @SPOUSE_RELATIONSHIPTYPECODEID uniqueidentifier,
            @BUSINESSID uniqueidentifier,
            @BUSINESS_ADDRESSBLOCK nvarchar(150),
            @BUSINESS_ADDRESSTYPECODEID uniqueidentifier,
            @BUSINESS_CART nvarchar(10),
            @BUSINESS_CITY nvarchar(50),
            @BUSINESS_COUNTRYID uniqueidentifier,
            @BUSINESS_DONOTMAIL bit,
            @BUSINESS_DONOTMAILREASONCODEID uniqueidentifier,
            @BUSINESS_DPC nvarchar(8),
            @BUSINESS_LOT nvarchar(5),
            @BUSINESS_NAME nvarchar(100),
            @BUSINESS_NUMBER nvarchar(100),
            @BUSINESS_PHONETYPECODEID uniqueidentifier,
            @BUSINESS_RELATIONSHIPTYPECODEID uniqueidentifier,
            @BUSINESS_RECIPROCALTYPECODEID uniqueidentifier,
            @BUSINESS_STATEID uniqueidentifier,
            @BUSINESS_POSTCODE nvarchar(12),
            @REMOVESPOUSE bit,
            @SPOUSE_LOOKUPID nvarchar(100),
            @NAMEFORMATS xml,
            @SPOUSE_PRIMARYSOFTCREDITRELATIONSHIPEXISTS bit,
            @SPOUSE_PRIMARYSOFTCREDITMATCHFACTOR decimal(5,2),
            @SPOUSE_PRIMARYRECOGNITIONTYPECODEID uniqueidentifier,
            @SPOUSE_RECIPROCALSOFTCREDITRELATIONSHIPEXISTS bit,
            @SPOUSE_RECIPROCALSOFTCREDITMATCHFACTOR decimal(5,2), 
            @SPOUSE_RECIPROCALRECOGNITIONTYPECODEID uniqueidentifier,
            @BUSINESS_PRIMARYSOFTCREDITRELATIONSHIPEXISTS bit,
            @BUSINESS_PRIMARYSOFTCREDITMATCHFACTOR decimal(5,2),
            @BUSINESS_PRIMARYRECOGNITIONTYPECODEID uniqueidentifier,
            @BUSINESS_RECIPROCALSOFTCREDITRELATIONSHIPEXISTS bit,
            @BUSINESS_RECIPROCALSOFTCREDITMATCHFACTOR decimal(5,2), 
            @BUSINESS_RECIPROCALRECOGNITIONTYPECODEID uniqueidentifier




          exec dbo.USP_EDITLOAD_BATCHCONSTITUENTUPDATEBATCHROW 
            @BATCHROWID, 
            null, --@DATALOADED output,

            null, --@TSLONG output,

            null, --@SEQUENCE

            null, --@PRIMARYRECORDID 

            @CONSTITUENTTYPECODE output, 
            @BIRTHDATE output,
            @FIRSTNAME output, 
            @GENDERCODE output, 
            @GIVESANONYMOUSLY output, 
            @LASTNAME output, 
            null, --@LOOKUP_ID

            @MAIDENNAME output, 
            @MIDDLENAME output,
            @NICKNAME output, 
            @SUFFIXCODEID output, 
            @TITLECODEID output, 
            @WEBADDRESS output, 
            null, --@ALTERNATELOOKUPIDS

            @BBISSECURITYATTRIBUTES output,
            null, --@DECEASED

            null, --@DECEASEDDATE

            null, --@CONSTITUENT_SITEID

            null, --@INTERESTS

            null, --@PROSPECTMANAGERFUNDRAISERID

            @ADDRESSES output,
            @PHONES output, 
            @EMAILADDRESSES output, 
            @BBISCONSTITUENCIES output, 
            @GROUP_GROUPTYPEID output, 
            @GROUP_DESCRIPTION output, 
            @GROUP_STARTDATE output,
            @INDUSTRYCODEID output, 
            @NUMEMPLOYEES output, 
            @NUMSUBSIDIARIES output, 
            @PARENTCORPID output, 
            @MARITALSTATUSCODEID output,
            @SPOUSEID output,
            @SPOUSE_TITLECODEID output,
            @SPOUSE_FIRSTNAME output,
            @SPOUSE_NICKNAME output,
            @SPOUSE_MIDDLENAME output,
            @SPOUSE_MAIDENNAME output,
            @SPOUSE_LASTNAME output,
            @SPOUSE_SUFFIXCODEID output,
            @SPOUSE_BIRTHDATE output,
            @SPOUSE_GENDERCODE output,
            @SPOUSE_LOOKUPID output,
            @SPOUSE_RELATIONSHIPTYPECODEID output,
            @SPOUSE_RECIPROCALTYPECODEID output,
            @BUSINESSID output,
            @BUSINESS_ADDRESSBLOCK output,
            @BUSINESS_ADDRESSTYPECODEID output,
            @BUSINESS_CART output,
            @BUSINESS_CITY output,
            @BUSINESS_COUNTRYID output,
            @BUSINESS_DONOTMAIL output,
            @BUSINESS_DONOTMAILREASONCODEID output,
            @BUSINESS_DPC output,
            null, --@BUSINESS_EMAILADDRESS

            null, --@BUSINESS_EMAILADDRESSTYPECODEID

            null, --@BUSINESS_INDUSTRYCODEID

            null, --@BUSINESS_LOOKUPID

            @BUSINESS_LOT output,
            @BUSINESS_NAME output,
            null, --@BUSINESS_NUMEMPLOYEES

            null, --@BUSINESS_NUMSUBSIDIARIES

            null, --@BUSINESS_PARENTCORPID

            @BUSINESS_NUMBER output,
            null, --@BUSINESS_PHONE_COUNTRYID

            @BUSINESS_PHONETYPECODEID output,
            @BUSINESS_RELATIONSHIPTYPECODEID output,
            @BUSINESS_RECIPROCALTYPECODEID output,
            @BUSINESS_STATEID output,
            null, --@BUSINESS_WEBADDRESS

            @BUSINESS_POSTCODE output,
            null, --@SPOUSE_RELATIONSHIPTYPECODEIDSIMPLELIST

            null, --@SPOUSE_RECIPROCALTYPECODEIDSIMPLELIST

            null, --@BUSINESS_RELATIONSHIPTYPECODEIDSIMPLELIST

            null, --@BUSINESS_RECIPROCALTYPECODEIDSIMPLELIST

            @BUSINESS_PRIMARYSOFTCREDITRELATIONSHIPEXISTS output,
            @BUSINESS_PRIMARYSOFTCREDITMATCHFACTOR output,
            @BUSINESS_RECIPROCALSOFTCREDITRELATIONSHIPEXISTS output,
            @BUSINESS_RECIPROCALSOFTCREDITMATCHFACTOR output,
            @BUSINESS_PRIMARYRECOGNITIONTYPECODEID output,
            @BUSINESS_RECIPROCALRECOGNITIONTYPECODEID output,
            null, --@BUSINESS_EMAILADDRESSSTARTDATE

            @SPOUSE_PRIMARYSOFTCREDITRELATIONSHIPEXISTS output,
            @SPOUSE_PRIMARYSOFTCREDITMATCHFACTOR output,
            @SPOUSE_RECIPROCALSOFTCREDITRELATIONSHIPEXISTS output,
            @SPOUSE_RECIPROCALSOFTCREDITMATCHFACTOR output,
            @SPOUSE_PRIMARYRECOGNITIONTYPECODEID output,
            @SPOUSE_RECIPROCALRECOGNITIONTYPECODEID output,
            @BBISCONSTITUENT_SITES output, 
            null, --@SPOUSE_ALTERNATELOOKUPIDS

            null, --@SPOUSE_IMPORTLOOKUPID

            null, --@ROWFROMBATCHUI

            null, --@BBNCTRANID

            null, --@PAGEID

            null, --@PAGENAME

            null, --@BBNCUSERID

            @REMOVESPOUSE output,
            null, --@DUPLICATERECORDID

            null, --@SOCIALMEDIAACCOUNTS

            null, --@DOMANUALREVIEWFORAUTOMATCH

            null, --@RELATIONSHIPS

            null, --@NETCOMMUNITYTRANSACTIONPROCESSORID

            null, --@BBNCID,

            null, --@NAMECODE

            null, --@SIMILARADDRESSCODE

            null, --@UNSIMILARADDRESSCODE

            null, --@NEWADDRESSENDDATECODE

            null, --@NEWADDRESSPRIMARYCODE

            null, --@BIRTHDATERULECODE

            null, --@DIFFERENTPHONECODE

            null, --@NEWPHONEENDDATECODE

            null, --@NEWPHONEPRIMARYCODE

            null, --@DIFFERENTEMAILCODE

            null, --@NEWEMAILENDDATECODE

            null, --@NEWEMAILPRIMARYCODE

            null, --@USEGLOBALSETTINGS

            null, --@CREATEHISTORICALNAMECODE

            @NAMEFORMATS output,
            null, --@SUBMITTEDLOOKUPID

            null, --@SUBMITTEDCLASSYEAR

            null, --@SUBMITTEDEDUCATIONALINSTITUTION

            null, --@REQUESTSNOEMAIL 

            null, --@ORIGINAL_KEYNAME 

            null, --@ORIGINAL_FIRSTNAME 

            null, --@SOLICITCODES

            @GENDERCODEID output,
            @SPOUSE_GENDERCODEID output

          -- Pull in fields not handled by the ERB constituent edit form so they don't get overwritten by CUB commit.

          exec dbo.USP_DATAFORMTEMPLATE_EDITLOAD_CONSTITUENTUPDATEBATCHTEMPLATE
            @CONSTITUENTID, 
            @LOOKUPID = @LOOKUPID output,
            @ALTERNATELOOKUPIDS = @ALTERNATELOOKUPIDS output,
            @SECURITYATTRIBUTES = @SECURITYATTRIBUTES output,
            @DECEASED = @DECEASED output,
            @DECEASEDDATE = @DECEASEDDATE output,
            @INTERESTS = @INTERESTS output,
            @PROSPECTMANAGERFUNDRAISERID = @PROSPECTMANAGERFUNDRAISERID output,
            @CONSTITUENCIES = @CONSTITUENCIES output,
            @CONSTITUENT_SITES = @CONSTITUENT_SITES output

          -- add the bbis values to the constituency, sited and security attributes collections 

          set @CONSTITUENT_SITES = (select * from 
             (select SITEID
              from dbo.UFN_BATCHCONSTITUENTUPDATE_GETSITES_FROMITEMLISTXML(@CONSTITUENT_SITES) 
              union 
              select SITEID
              from dbo.UFN_BATCHCONSTITUENTUPDATE_GETSITES_FROMITEMLISTXML(@BBISCONSTITUENT_SITES)
              where SITEID not in (select SITEID from dbo.UFN_BATCHCONSTITUENTUPDATE_GETSITES_FROMITEMLISTXML(@CONSTITUENT_SITES))) A
            for xml raw('ITEM'),type,elements,root('CONSTITUENT_SITES'),BINARY BASE64
            )  

          set @CONSTITUENT_SITES = (select * from 
             (select SITEID
              from dbo.UFN_BATCHCONSTITUENTUPDATE_GETSITES_FROMITEMLISTXML(@CONSTITUENT_SITES) 
              union 
              select SITEID
              from dbo.UFN_BATCHCONSTITUENTUPDATE_GETSITES_FROMITEMLISTXML(@BBISCONSTITUENT_SITES)
              where SITEID not in (select SITEID from dbo.UFN_BATCHCONSTITUENTUPDATE_GETSITES_FROMITEMLISTXML(@CONSTITUENT_SITES))) A
            for xml raw('ITEM'),type,elements,root('CONSTITUENT_SITES'),BINARY BASE64
            )  

          set @SECURITYATTRIBUTES = (select * from 
             (select CONSTIT_SECURITY_ATTRIBUTEID
              from dbo.UFN_BATCHCONSTITUENTUPDATE_GETSECURITYATTRIBUTES_FROMITEMLISTXML(@SECURITYATTRIBUTES) 
              union 
              select CONSTIT_SECURITY_ATTRIBUTEID
              from dbo.UFN_BATCHCONSTITUENTUPDATE_GETSECURITYATTRIBUTES_FROMITEMLISTXML(@BBISSECURITYATTRIBUTES)
              where CONSTIT_SECURITY_ATTRIBUTEID not in (select CONSTIT_SECURITY_ATTRIBUTEID from dbo.UFN_BATCHCONSTITUENTUPDATE_GETSECURITYATTRIBUTES_FROMITEMLISTXML(@SECURITYATTRIBUTES))) A
            for xml raw('ITEM'),type,elements,root('SECURITYATTRIBUTES'),BINARY BASE64
            )  

          set @CONSTITUENCIES = (select * from 
             (select CONSTITUENCYCODEID,
                     DATEFROM,
                     DATETO,
                     ORIGINALCONSTITUENCYID
              from dbo.UFN_BATCHCONSTITUENTUPDATE_GETCONSTITUENCIES_FROMITEMLISTXML(@CONSTITUENCIES) 
              union 
              select CONSTITUENCYCODEID,
                     DATEFROM,
                     DATETO,
                     ORIGINALCONSTITUENCYID
              from dbo.UFN_BATCHCONSTITUENTUPDATE_GETCONSTITUENCIES_FROMITEMLISTXML(@BBISCONSTITUENCIES)                   
              -- OK to insert the same CONSTITUENCYCODEID as long as the begin/end dates do not overlap

              where ID not in 
                (select C2.ID from dbo.UFN_BATCHCONSTITUENTUPDATE_GETCONSTITUENCIES_FROMITEMLISTXML(@CONSTITUENCIES) C1
                inner join dbo.UFN_BATCHCONSTITUENTUPDATE_GETCONSTITUENCIES_FROMITEMLISTXML(@BBISCONSTITUENCIES) C2
                on C1.CONSTITUENCYCODEID = C2.CONSTITUENCYCODEID
                    where(
                    (C1.DATETO   between C2.DATEFROM and C2.DATETO) or
                    (C2.DATETO   between  C1.DATEFROM and  C1.DATETO) or
                    (C1.DATEFROM between C2.DATEFROM and C2.DATETO) or
                    (C2.DATEFROM between  C1.DATEFROM and  C1.DATETO) or

                    ( C1.DATEFROM is null and C2.DATEFROM <=  C1.DATETO)   or
                    (C2.DATEFROM is null and  C1.DATEFROM <= C2.DATETO)   or
                    (C1.DATETO   is null and C2.DATETO   >=  C1.DATEFROM) or
                    (C2.DATETO   is null and  C1.DATETO   >= C2.DATEFROM) or

                    (C2.DATEFROM is null and  C1.DATEFROM is null) or
                    (C2.DATETO   is null and  C1.DATETO   is null) or
                    (C2.DATEFROM is null and C2.DATETO   is null) or
                    (C1.DATEFROM is null and  C1.DATETO   is null)
                  )
                )
             ) A
          for xml raw('ITEM'),type,elements,root('CONSTITUENCIES'),BINARY BASE64
          )

          exec dbo.USP_DATAFORMTEMPLATE_EDIT_CONSTITUENTUPDATEBATCHTEMPLATE_15
            @ID = @CONSTITUENTID,
            @CHANGEAGENTID = @CHANGEAGENTID,
            @VALIDATEONLY = @VALIDATEONLY,
            @CONSTITUENTTYPECODE = @CONSTITUENTTYPECODE,
            @BIRTHDATE = @BIRTHDATE,
            @FIRSTNAME = @FIRSTNAME,
            @GENDERCODE = @GENDERCODE,
            @GIVESANONYMOUSLY = @GIVESANONYMOUSLY,
            @KEYNAME = @LASTNAME,
            @LOOKUPID = @LOOKUPID,
            @MAIDENNAME = @MAIDENNAME,
            @MIDDLENAME = @MIDDLENAME,
            @NICKNAME = @NICKNAME,
            @SUFFIXCODEID = @SUFFIXCODEID,
            @TITLECODEID = @TITLECODEID,
            @WEBADDRESS = @WEBADDRESS,
            @ALTERNATELOOKUPIDS = @ALTERNATELOOKUPIDS,
            @SECURITYATTRIBUTES = @SECURITYATTRIBUTES,
            @DECEASED = @DECEASED,
            @DECEASEDDATE = @DECEASEDDATE,
            @CONSTITUENT_SITEID = null,
            @INTERESTS = @INTERESTS,
            @PROSPECTMANAGERFUNDRAISERID = @PROSPECTMANAGERFUNDRAISERID,
            @ADDRESSES = @ADDRESSES,
            @EMAILADDRESSES = @EMAILADDRESSES,
            @PHONES = @PHONES,
            @CONSTITUENCIES = @CONSTITUENCIES,
            @GROUPTYPEID = @GROUP_GROUPTYPEID,
            @GROUPDESCRIPTION = @GROUP_DESCRIPTION,
            @GROUPSTARTDATE = @GROUP_STARTDATE,
            @ORG_INDUSTRYCODEID = @INDUSTRYCODEID,
            @ORG_NUMEMPLOYEES = @NUMEMPLOYEES,
            @ORG_NUMSUBSIDIARIES = @NUMSUBSIDIARIES,
            @ORG_PARENTCORPID = @PARENTCORPID,
            @MARITALSTATUSCODEID = @MARITALSTATUSCODEID,
            @SPOUSE_ID = @SPOUSEID,
            @SPOUSE_BIRTHDATE = @SPOUSE_BIRTHDATE,
            @SPOUSE_FIRSTNAME = @SPOUSE_FIRSTNAME,
            @SPOUSE_GENDERCODE = @SPOUSE_GENDERCODE,
            @SPOUSE_LASTNAME = @SPOUSE_LASTNAME,
            @SPOUSE_LOOKUPID = @SPOUSE_LOOKUPID,
            @SPOUSE_MAIDENNAME = @SPOUSE_MAIDENNAME,
            @SPOUSE_MIDDLENAME = @SPOUSE_MIDDLENAME,
            @SPOUSE_NICKNAME = @SPOUSE_NICKNAME,
            @SPOUSE_SUFFIXCODEID = @SPOUSE_SUFFIXCODEID,
            @SPOUSE_TITLECODEID = @SPOUSE_TITLECODEID,
            @SPOUSE_RELATIONSHIPTYPECODEID = @SPOUSE_RELATIONSHIPTYPECODEID,
            @SPOUSE_RECIPROCALTYPECODEID = @SPOUSE_RECIPROCALTYPECODEID,
            @BUSINESS_ADDRESSBLOCK = @BUSINESS_ADDRESSBLOCK,
            @BUSINESS_ADDRESSTYPECODEID = @BUSINESS_ADDRESSTYPECODEID,
            @BUSINESS_CART = @BUSINESS_CART,
            @BUSINESS_CITY = @BUSINESS_CITY,
            @BUSINESS_COUNTRYID = @BUSINESS_COUNTRYID,
            @BUSINESS_DONOTMAIL = @BUSINESS_DONOTMAIL,
            @BUSINESS_DONOTMAILREASONCODEID = @BUSINESS_DONOTMAILREASONCODEID,
            @BUSINESS_DPC = @BUSINESS_DPC,
            @BUSINESS_EMAILADDRESS = null,
            @BUSINESS_EMAILADDRESSTYPECODEID = null,
            @BUSINESS_INDUSTRYCODEID = null,
            @BUSINESS_LOOKUPID = null,
            @BUSINESS_LOT = @BUSINESS_LOT,
            @BUSINESS_ID = @BUSINESSID,
            @BUSINESS_NAME = @BUSINESS_NAME,
            @BUSINESS_NUMEMPLOYEES = null,
            @BUSINESS_NUMSUBSIDIARIES = null,
            @BUSINESS_PARENTCORPID = null,
            @BUSINESS_PHONENUMBER = @BUSINESS_NUMBER,
            @BUSINESS_PHONE_COUNTRYID = null,
            @BUSINESS_PHONETYPECODEID = @BUSINESS_PHONETYPECODEID,
            @BUSINESS_RELATIONSHIPTYPECODEID = @BUSINESS_RELATIONSHIPTYPECODEID,
            @BUSINESS_RECIPROCALTYPECODEID = @BUSINESS_RECIPROCALTYPECODEID,
            @BUSINESS_STATEID = @BUSINESS_STATEID,
            @BUSINESS_WEBADDRESS = null,
            @BUSINESS_POSTCODE = @BUSINESS_POSTCODE,
            @CURRENTAPPUSERID = @CURRENTAPPUSERID,
            @SPOUSE_RELATIONSHIPTYPECODEIDSIMPLELIST = @SPOUSE_RELATIONSHIPTYPECODEID,
            @SPOUSE_RECIPROCALTYPECODEIDSIMPLELIST = @SPOUSE_RECIPROCALTYPECODEID,
            @BUSINESS_RELATIONSHIPTYPECODEIDSIMPLELIST = @BUSINESS_RELATIONSHIPTYPECODEID,
            @BUSINESS_RECIPROCALTYPECODEIDSIMPLELIST = @BUSINESS_RECIPROCALTYPECODEID,
            @BUSINESS_PRIMARYSOFTCREDITRELATIONSHIPEXISTS = @BUSINESS_PRIMARYSOFTCREDITRELATIONSHIPEXISTS,
            @BUSINESS_PRIMARYSOFTCREDITMATCHFACTOR = @BUSINESS_PRIMARYSOFTCREDITMATCHFACTOR,
            @BUSINESS_RECIPROCALSOFTCREDITRELATIONSHIPEXISTS = @BUSINESS_RECIPROCALSOFTCREDITRELATIONSHIPEXISTS,
            @BUSINESS_RECIPROCALSOFTCREDITMATCHFACTOR = @BUSINESS_RECIPROCALSOFTCREDITMATCHFACTOR,
            @BUSINESS_PRIMARYRECOGNITIONTYPECODEID = @BUSINESS_PRIMARYRECOGNITIONTYPECODEID,
            @BUSINESS_RECIPROCALRECOGNITIONTYPECODEID = @BUSINESS_RECIPROCALRECOGNITIONTYPECODEID,
            @BUSINESS_EMAILADDRESSSTARTDATE = null,
            @SPOUSE_PRIMARYSOFTCREDITRELATIONSHIPEXISTS = @SPOUSE_PRIMARYSOFTCREDITRELATIONSHIPEXISTS,
            @SPOUSE_PRIMARYSOFTCREDITMATCHFACTOR = @SPOUSE_PRIMARYSOFTCREDITMATCHFACTOR,
            @SPOUSE_RECIPROCALSOFTCREDITRELATIONSHIPEXISTS = @SPOUSE_RECIPROCALSOFTCREDITRELATIONSHIPEXISTS,
            @SPOUSE_RECIPROCALSOFTCREDITMATCHFACTOR = @SPOUSE_RECIPROCALSOFTCREDITMATCHFACTOR,
            @SPOUSE_PRIMARYRECOGNITIONTYPECODEID = @SPOUSE_PRIMARYRECOGNITIONTYPECODEID,
            @SPOUSE_RECIPROCALRECOGNITIONTYPECODEID = @SPOUSE_RECIPROCALRECOGNITIONTYPECODEID,
            @CONSTITUENT_SITES = @CONSTITUENT_SITES,
            @BATCHOWNERID = @BATCHOWNERID,
            @BATCHROWID = @BATCHROWID,
            @SPOUSE_ALTERNATELOOKUPIDS = null,
            @SPOUSE_IMPORTLOOKUPID = null,
            @ROWFROMBATCHUI = null,
            @BBNCTRANID = null,
            @REMOVESPOUSE = @REMOVESPOUSE,
            @SOCIALMEDIAACCOUNTS = null,
            @RELATIONSHIPS = null,
            @NETCOMMUNITYTRANSACTIONPROCESSORID = null,
            @NEWADDRESSENDDATECODE = @NEWADDRESSENDDATECODE,
            @CREATEHISTORICALNAMECODE = @CREATEHISTORICALNAMECODE,
            @NEWPHONEENDDATECODE = @NEWPHONEENDDATECODE,
            @NEWEMAILENDDATECODE = @NEWEMAILENDDATECODE,
            @NAMEFORMATS = @NAMEFORMATS,
            @REQUESTSNOEMAIL=null,
            @SOLICITCODES = @SOLICITCODES,
            @GENDERCODEID = @GENDERCODEID,
            @SPOUSE_GENDERCODEID = @SPOUSE_GENDERCODEID;

            -- BUG#584814

            -- If a new spouse is created/added while editing the constituent,

            -- then the recognition credits stored for the spouse in BATCHREVENUERECOGNITION using

            -- ERB's row's ID as CONSTITUENTID should be rectified in both BATCHREVENUERECOGNITION and @RECOGNITIONS

            -- with the spouse's ID, which has now been created above by using CUB's commit.

            if @VALIDATEONLY = 0 and @SPOUSEID is null and len(coalesce(@SPOUSE_LASTNAME, '')) > 0
            begin
              declare @REC table
                      (
                          ID uniqueidentifier,
                          CONSTITUENTID uniqueidentifier
                      );

                      insert into @REC(ID, CONSTITUENTID)
                        select
                T.c.value('(ID)[1]','uniqueidentifier') AS 'ID',
                            T.c.value('(CONSTITUENTID)[1]','uniqueidentifier') AS 'CONSTITUENTID'
                        from @RECOGNITIONS.nodes('/RECOGNITIONS/ITEM') T(c);

              declare @NEWSPOUSE_RELATIONSHIPTYPECODEID uniqueidentifier = (select SPOUSE_RELATIONSHIPTYPECODEID
                                                                            from dbo.BATCHCONSTITUENTUPDATE
                                                                            where ID = @BATCHROWID);
              declare @NEWSPOUSE_ID uniqueidentifier = (select RECIPROCALCONSTITUENTID
                                                        from dbo.RELATIONSHIP
                                                        where RELATIONSHIPCONSTITUENTID = @CONSTITUENTID
                                                        and RELATIONSHIPTYPECODEID = @NEWSPOUSE_RELATIONSHIPTYPECODEID
                                                        and ISSPOUSE = 1),
                      @REC_ID uniqueidentifier,
                      @REC_CONSTITUENTID uniqueidentifier,
                      @ROW_COUNTER int = 1;

              declare REC_CURSOR cursor local for
                        select ID, CONSTITUENTID from @REC;

              open REC_CURSOR;
              fetch next from REC_CURSOR into @REC_ID, @REC_CONSTITUENTID;
                      while @@FETCH_STATUS = 0
                      begin
                if @REC_CONSTITUENTID = @BATCHROWID
                begin
                  -- change the spouse's id from the batch's id to the new id

                  update dbo.BATCHREVENUERECOGNITION
                  set CONSTITUENTID = @NEWSPOUSE_ID
                  where ID = @REC_ID;

                  -- update the id in @RECOGNITIONS too

                  set @RECOGNITIONS.modify('replace value of (/RECOGNITIONS/ITEM[sql:variable("@ROW_COUNTER")]/CONSTITUENTID/text())[1] with sql:variable("@NEWSPOUSE_ID")');
                end

                set @ROW_COUNTER = @ROW_COUNTER + 1;

                fetch next from REC_CURSOR into @REC_ID, @REC_CONSTITUENTID;
              end

              close REC_CURSOR;
                      deallocate REC_CURSOR;
            end

        end
      end

    if @VALIDATEONLY = 0
    begin

      if @CONSTITUENTEXISTS = 0
        begin

          -- 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, 
            @CONSTITUENTID, 
            @CONSTITUENTACCOUNTID, 
            @CONSTITUENTACCOUNTID output,
            @CURRENTRECOGNITIONS = @RECOGNITIONSTOPASS,
            @UPDATEDRECOGNITIONS = @RECOGNITIONS output,
            @UPDATEDAPPLICATIONRECOGNITIONS = @APPLICATIONRECOGNITIONS output;

        end

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

      if (
          select
            count(ID)
          from
            dbo.CONSTITUENTACCOUNT
          where
            CONSTITUENTACCOUNT.ID = @CONSTITUENTACCOUNTID
         ) = 0
      begin
          exec dbo.USP_REVENUEBATCH_GENERATECONSTITUENTACCOUNTS
              @BATCHREVENUECONSTITUENTID = @CONSTITUENTID, 
              @CONSTITUENTID = @CONSTITUENTID, 
              @CHANGEAGENTID = @CHANGEAGENTID,
              @BATCHREVENUECONSTITUENTACCOUNTID = @CONSTITUENTACCOUNTID,
              @CONSTITUENTACCOUNTID = @CONSTITUENTACCOUNTID output
      end

      --delete the temporary batch version of the constituent

      -- SHL BBIS Bug 400987; Moved USP_REVENUEBATCH_CONSTITUENT_DELETE to after USP_REVENUEBATCH_GENERATECONSTITUENTACCOUNTS because USP_REVENUEBATCH_GENERATECONSTITUENTACCOUNTS uses the temporary batch information deleted by USP_REVENUEBATCH_CONSTITUENT_DELETE

      exec dbo.[USP_REVENUEBATCH_CONSTITUENT_DELETE] @BATCHREVENUECONSTITUENTID = @CONSTITUENTID, @CHANGEAGENTID = @CHANGEAGENTID;

      if (@ADDITIONALAPPLICATIONSAMOUNT > 0 or (@AMOUNT = 0 and @ADDITIONALAPPLICATIONCOUNT > 0))
      begin
        set @SPLITS = ( select 
                  ID,
                  case TYPECODE when 0 then 0 when 1 then 4 when 2 then 7  when 3 then 0 end as APPLICATIONCODE,
                  case TYPECODE when 0 then 0 when 1 then 4 when 2 then 0 when 3 then 17 end as TYPECODE,
                  DESIGNATIONID,
                  APPLIED as AMOUNT,
                  OTHERTYPECODEID,
                  DECLINESGIFTAID,
                  OPPORTUNITYID,
                  SPONSORSHIPID,
                  ISGIFTAIDSPONSORSHIP,
                  CATEGORYCODEID,
                  case when CAMPAIGNS is null then null else CAMPAIGNS.query('(CAMPAIGNS/ITEM)') end as CAMPAIGNS,
                  @TRANSACTIONCURRENCYID as TRANSACTIONCURRENCYID
                from dbo.UFN_REVENUEBATCH_ADDITIONALAPPLICATIONWITHCHILDREN_FROMITEMLISTXML(@ADDITIONALAPPLICATIONSSTREAM)
                for xml raw('ITEM'),type,elements,root('SPLITS'),binary base64)
        set @ADDITIONALAPPLICATIONS = 1;
      end
      else 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,
                          @TRANSACTIONCURRENCYID as TRANSACTIONCURRENCYID
                        for xml raw('ITEM'),type,elements,root('SPLITS'),binary base64)
      end

      declare @BENEFITAMOUNT money;
      select @BENEFITAMOUNT = coalesce(sum(TRANSACTIONTOTALVALUE), 0)
      from dbo.UFN_REVENUE_GETBENEFITS_3_FROMITEMLISTXML(@TOTALBENEFITS);

      if @TYPECODE = 0 -- payment

      begin
        declare @INITIALAPPLIEDTOPLEDGESAMOUNT money
        set @INITIALAPPLIEDTOPLEDGESAMOUNT = 0

        declare @APPLIEDRECEIPTAMOUNT money
        set @APPLIEDRECEIPTAMOUNT = 0;

        declare @APPLICATIONPLEDGEREVENUEID uniqueidentifier;
        if @SINGLEAPPLICATIONID is null
        begin
          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 @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;
              declare @APBASEEXCHANGERATEID uniqueidentifier;
              declare @APEXCHANGERATE decimal(20,8); 
              declare @APINSTALLMENTAMOUNT money;              

              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,
                @APBASEEXCHANGERATEID = XML.BASEEXCHANGERATEID,
                @APEXCHANGERATE = XML.EXCHANGERATE,
                @APINSTALLMENTAMOUNT = BRAP.INSTALLMENTAMOUNT
              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;

              --Bug 128299 - AdamBu - We need to insert the transaction currency into the split XML so

              --that it can pass the validation checks done in USP_PLEDGE_ADD.

              set @APSPLITS = (
                select 
                  T.c.value('(ID)[1]','uniqueidentifier') AS 'ID',
                  T.c.value('(BATCHREVENUEAPPLICATIONPLEDGEID)[1]','uniqueidentifier') AS 'BATCHREVENUEAPPLICATIONPLEDGEID',
                  T.c.value('(DESIGNATIONID)[1]','uniqueidentifier') AS 'DESIGNATIONID',
                  T.c.value('(AMOUNT)[1]','money') AS 'AMOUNT',
                  T.c.value('(APPLICATIONCODE)[1]','tinyint') AS 'APPLICATIONCODE',
                  T.c.value('(TYPECODE)[1]','tinyint') AS 'TYPECODE',
                  T.c.value('(DECLINESGIFTAID)[1]','bit') AS 'DECLINESGIFTAID',
                  @TRANSACTIONCURRENCYID TRANSACTIONCURRENCYID
                from @APSPLITS.nodes('/SPLITS/ITEM') T(c)
                for xml raw('ITEM'),type,elements,root('SPLITS'),BINARY BASE64
              );

              --Bug 136065 - AdamBu - 1/6/11 - Generate a spot rate for the new pledge's exchange rate, if we need it.

              if @APBASEEXCHANGERATEID = '00000000-0000-0000-0000-000000000001'
              begin
                set @APBASEEXCHANGERATEID = newid();

                --Replace with call to USP_CURRENCYEXCHANGERATE_ENSURESPOTRATEEXISTS for PBI 102747 in the future

                /*if not dbo.UFN_SECURITY_APPUSER_GRANTED_SYSTEMPRIVILEGE_IN_SYSTEMROLE(@CURRENTAPPUSERID,'911f104d-ba5f-4469-b0ae-184c879aea99') = 1
                  and not dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1
                begin
                  raiserror('User does not have the right to add a new spot rate.', 13, 1);
                  return 1;
                end*/

                insert into dbo.CURRENCYEXCHANGERATE(
                  ID, 
                  FROMCURRENCYID,
                  TOCURRENCYID,
                  RATE,
                  ASOFDATE,
                  TYPECODE,
                  SOURCECODEID,
                  ADDEDBYID, 
                  CHANGEDBYID, 
                  DATEADDED, 
                  DATECHANGED
                )
                values(
                  @APBASEEXCHANGERATEID,
                  @TRANSACTIONCURRENCYID,
                  @BASECURRENCYID,
                  @APEXCHANGERATE,
                  @APDATE,
                  2,
                  null,
                  @CHANGEAGENTID,
                  @CHANGEAGENTID,
                  @CURRENTDATE,
                  @CURRENTDATE
                );
              end

              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,
                  @TRANSACTIONCURRENCYID,
                  @BASECURRENCYID,
                  @APBASEEXCHANGERATEID,
                  @PDACCOUNTSYSTEMID,
                  default,
                  default,
                  default,
                  default,
                  default,
                  default,
                  @SEPAMANDATEID;

              insert into dbo.PLEDGEINSTALLMENTOPTION
              (ID,INSTALLMENTAMOUNT,SPLITSCHEDULEOPTIONCODE,ADDEDBYID,CHANGEDBYID,DATEADDED,DATECHANGED)
              values 
              (@APPLICATIONPLEDGEREVENUEID,case @APFREQUENCYCODE when 4 then null else @APINSTALLMENTAMOUNT end,0,@CHANGEAGENTID,@CHANGEAGENTID,@CURRENTDATE,@CURRENTDATE);

              update dbo.FINANCIALTRANSACTIONLINEITEM set
                BATCHID = @BATCHID
                ,CHANGEDBYID = @CHANGEAGENTID
                ,DATECHANGED = @CURRENTDATE
              where FINANCIALTRANSACTIONID = @APPLICATIONPLEDGEREVENUEID;

              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
          end;
            with [CTE] as
            (
              select
                APPLIED,
                case 
                  when TYPECODE = 6 
                    then dbo.UFN_EVENTREGISTRANT_GETRECEIPTAMOUNT_NOBENEFITS(
                        APPLICATIONID,
                        @DATE,
                        APPLIED,
                        @TRANSACTIONCURRENCYID,
                        @BASECURRENCYID,
                        @BASEEXCHANGERATEID,
                        0,
                        coalesce((select 1 from dbo.BATCHREVENUEREGISTRANT where ID = APPLICATIONID), 0),
                        null
                      )
                  when TYPECODE = 1 then
                    dbo.UFN_PLEDGE_CALCULATERECEIPTAMOUNT(APPLICATIONID, APPLIED, OVERPAYMENTAPPLICATIONTYPECODE)
                  else
                    APPLIED
                end as [RECEIPTAMOUNT] --JamesWill WI125245 2010-10-19 Event receipt amounts are in the receipt amount field. So don't double count them here.

              from dbo.UFN_REVENUEBATCH_GETAPPLICATIONS_FROMITEMLISTXML(@REVENUESTREAMS)
              where APPLIED > 0
            )
            select
              @INITIALAPPLIEDTOPLEDGESAMOUNT = coalesce(sum(APPLIED), 0),
              @APPLIEDRECEIPTAMOUNT = coalesce(sum(RECEIPTAMOUNT), 0)
            from [CTE]
        end
        else
        begin
          set @INITIALAPPLIEDTOPLEDGESAMOUNT = @AMOUNT;

          if @APPLICATIONTYPECODE = 5 -- pledge

          begin
            if @REVENUESTREAMS is not null and @RECEIPTAMOUNT = 0
            begin
              set @APPLIEDRECEIPTAMOUNT = dbo.UFN_PLEDGE_CALCULATERECEIPTAMOUNT(@SINGLEAPPLICATIONID, @AMOUNT, 255);
            end
            else
            begin
              set @APPLIEDRECEIPTAMOUNT = @RECEIPTAMOUNT + @BENEFITAMOUNT;
            end
          end
          else if @APPLICATIONTYPECODE = 7 -- event registration

          begin
            select @APPLIEDRECEIPTAMOUNT = dbo.UFN_EVENTREGISTRANT_GETRECEIPTAMOUNT_NOBENEFITS(
              @SINGLEAPPLICATIONID,
              @DATE,
              @AMOUNT,
              @TRANSACTIONCURRENCYID,
              @BASECURRENCYID,
              @BASEEXCHANGERATEID,
              0,
              0,
              null
            )
          end
          else
          begin
            set @APPLIEDRECEIPTAMOUNT = @AMOUNT;
          end
        end

        declare @FULLAMOUNT money;
        declare @BASEFULLAMOUNT money;
        declare @ORGANIZATIONFULLAMOUNT money;

        set @FULLAMOUNT = @AMOUNT;
        set @BASEFULLAMOUNT = @BASEAMOUNT;
        set @ORGANIZATIONFULLAMOUNT = @ORGANIZATIONAMOUNT;

        set @AMOUNT = @AMOUNT - @INITIALAPPLIEDTOPLEDGESAMOUNT;
        exec dbo.USP_CURRENCY_GETCURRENCYVALUES @AMOUNT, @DATE, @BASECURRENCYID, @BASEEXCHANGERATEID output, @TRANSACTIONCURRENCYID output, @BASEAMOUNT output, @ORGANIZATIONCURRENCYID output, @ORGANIZATIONAMOUNT output, @ORGANIZATIONEXCHANGERATEID output, 0;

        if @AMOUNT < 0 
        begin
          raiserror('The gift amount must be greater than or equal to the sum of all the applications.', 13, 1);
          return 1;
        end

        --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 @TOTALBENEFITS is null and @BENEFITSWAIVED = 0
          begin
            set @RECEIPTAMOUNT = @AMOUNT - @BENEFITAMOUNT;
          end
          else
          begin
            set @RECEIPTAMOUNT = @AMOUNT;
          end

          set @RECEIPTAMOUNT = @RECEIPTAMOUNT + @APPLIEDRECEIPTAMOUNT

          if @RECEIPTAMOUNT < 0
          begin
            set @RECEIPTAMOUNT = 0
          end
        end

        if not @SPLITS is null
          exec dbo.USP_REVENUESPLIT_VALIDATESPLITS @SPLITS, @AMOUNT, @TYPECODE, null, @TRANSACTIONCURRENCYID;
        else
          if @AMOUNT <> 0
            raiserror('Please enter at least one designation.', 13, 1);

        declare @SHOULDDEFAULTRECEIPTTYPECODE bit = 0;

        --if no receipt type is specified, get the default receipt type

        if @RECEIPTTYPECODE is null or @RECEIPTTYPECODE = 255
        begin
          -- UFN_CONSTITUENT_GETRECEIPTPREFERENCE expects the app type code mapping found on Payment.Add.xml.

          -- Translate the mapping from those defined in USP_REVENUEBATCH_CONSTITUENTCOMMITMENT (which is ultimately

          -- where APPLICATIONTYPECODE comes from) to those found on Payment.Add.xml.

          declare @TRANSLATEDAPPTYPECODE as tinyint;
          set @TRANSLATEDAPPTYPECODE = 
            case
              when @APPLICATIONTYPECODE = 1 then 3 -- Sponsorship recurring gift

              when @APPLICATIONTYPECODE = 4 then 3 -- Recurring gift

              when @APPLICATIONTYPECODE = 5 then 2 -- Pledge

              else 0 -- Recurring gift and Pledge are the only application types we care about for receipt preference.

            end;

          set @RECEIPTTYPECODE = dbo.UFN_CONSTITUENT_GETRECEIPTPREFERENCE(@CONSTITUENTID,@TRANSLATEDAPPTYPECODE);

          -- If this is actually a split payment, we'll need to let USP_REVENUEBATCH_APPLYTOREVENUESTREAMS

          -- know that it needs to default receipt type based on the splits.

          set @SHOULDDEFAULTRECEIPTTYPECODE = 1;
        end

        insert into dbo.FINANCIALTRANSACTION(
           ID
          ,CONSTITUENTID
          ,TYPECODE
          ,TRANSACTIONAMOUNT
          ,BASEAMOUNT
          ,ORGAMOUNT            
          ,DATE
          ,TRANSACTIONCURRENCYID
          ,BASEEXCHANGERATEID
          ,ORGEXCHANGERATEID
          ,PDACCOUNTSYSTEMID
          ,POSTDATE
          ,POSTSTATUSCODE
          ,USERDEFINEDID
          ,ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
        values(
           @ID
          ,@CONSTITUENTID
          ,0 --TYPCODE

          ,case when              
              @ORGANIZATIONEXCHANGERATEID is null
              and (@BASECURRENCYID is null or  @BASECURRENCYID = @ORGANIZATIONCURRENCYID)
              and @BASEEXCHANGERATEID is null
              and (@TRANSACTIONCURRENCYID is null or @TRANSACTIONCURRENCYID = @ORGANIZATIONCURRENCYID)
              and (isnull(@ORGANIZATIONFULLAMOUNT,0) <> isnull(@BASEFULLAMOUNT,0)
                    or isnull(@FULLAMOUNT,0) <> isnull(@BASEFULLAMOUNT,0)
                    or isnull(@BASEFULLAMOUNT,0) = 0 ) 
            then 
              @BASEFULLAMOUNT
            else 
              @FULLAMOUNT
            end
          ,@BASEFULLAMOUNT
          ,case when 
            @ORGANIZATIONEXCHANGERATEID is null
              and (@BASECURRENCYID is null or  @BASECURRENCYID = @ORGANIZATIONCURRENCYID)
              and @BASEEXCHANGERATEID is null
              and (@TRANSACTIONCURRENCYID is null or @TRANSACTIONCURRENCYID = @ORGANIZATIONCURRENCYID)
              and (isnull( @ORGANIZATIONFULLAMOUNT,0) <> isnull(@BASEFULLAMOUNT,0)
                    or isnull(@FULLAMOUNT,0) <> isnull(@BASEFULLAMOUNT,0)
                    or isnull(@BASEFULLAMOUNT,0) = 0) 
              then                      
                @BASEFULLAMOUNT
              else 
                @ORGANIZATIONFULLAMOUNT
            end
          ,@DATE
          ,@TRANSACTIONCURRENCYID
          ,@BASEEXCHANGERATEID
          ,@ORGANIZATIONEXCHANGERATEID
          ,@PDACCOUNTSYSTEMID
          ,@POSTDATE
          ,case 
            when @DONOTPOST = 1 then 3
            else 1
            end
          ,coalesce(rtrim(ltrim(@REVENUELOOKUPID)),'')
          ,@CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE);

        insert into dbo.REVENUE_EXT (
           ID
          ,BATCHNUMBER
          ,DONOTRECEIPT
          ,RECEIPTAMOUNT
          ,SOURCECODE
          ,FINDERNUMBER
          ,APPEALID
          ,MAILINGID
          ,CHANNELCODEID
          ,GIVENANONYMOUSLY
          ,DONOTACKNOWLEDGE
          ,BENEFITSWAIVED
          ,RECEIPTTYPECODE
          ,NEEDSRERECEIPT
          ,ELIGIBLEFORMATCHINGGIFTCLAIM
          ,ISREIMBURSABLE
          ,REFERENCE
          ,NONPOSTABLEBASECURRENCYID
          -- Boilerplate

          ,ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
        values(
           @ID
          ,@BATCHNUMBER
          ,@DONOTRECEIPT            
          ,@RECEIPTAMOUNT
          ,@SOURCECODE
          ,@FINDERNUMBER            
          ,@APPEALID
          ,@MAILINGID
          ,@CHANNELCODEID
          ,@GIVENANONYMOUSLY           
          ,@DONOTACKNOWLEDGE            
          ,@BENEFITSWAIVED            
          ,@RECEIPTTYPECODE
          ,0 --NEEDSRERECEIPT

          ,0 --ELIGIBLEFORMATCHINGGIFTCLAIM

          ,0 --ISREIMBURSABLE

          ,'' --REFERENCE

          ,null --NONPOSTABLEBASECURRENCYID

          ,@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 a SEPA mandate is used, mark the mandate record to show that a payment has been made towards it.

        if @SEPAMANDATEID is not null
          exec dbo.USP_SEPAMANDATE_PAYMENTMADE @SEPAMANDATEID,@BATCHROWID,@ID, @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;


        declare @SPLITSDECLININGGIFTAIDAPPLICATIONS xml, @COVENANTGIFTSPLITS xml
        declare @returnid uniqueidentifier;
        if @SINGLEAPPLICATIONID is null
        begin
          if @REVENUESTREAMS is not null and @STREAMCOUNT > 0
          begin

            --need to add @MAILINGID and @CHANNELCODEID.  currently passed in as null

            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, @SHOULDDEFAULTRECEIPTTYPECODE;

            if @APPLIEDTOPLEDGES <> @INITIALAPPLIEDTOPLEDGESAMOUNT
              raiserror('There was an error calculating the amount applied values.', 13, 1)

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

          end
        end
        else -- single application

        begin
          exec dbo.USP_REVENUEBATCH_APPLYTOSINGLEAPPLICATION @ID
          ,@SINGLEAPPLICATIONID
          ,@APPLICATIONTYPECODE
          ,@FULLAMOUNT
          ,@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
          ,@CURRENTAPPUSERID
          ,@TRANSACTIONCURRENCYID
          ,@BATCHROWID
          ,@SHOULDDEFAULTRECEIPTTYPECODE
          ,@REVENUESTREAMS;

          update dbo.REVENUESCHEDULE
          set ISPENDING = 0
            ,CHANGEDBYID = @CHANGEAGENTID
            ,DATECHANGED = @CURRENTDATE
          from dbo.REVENUESCHEDULE
          where REVENUESCHEDULE.ID = @SINGLEAPPLICATIONID
        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

            when @APPLICATIONCODE = 3 then 0 -- Donation, but sponsorship additional Donation

          end

        declare @REVENUESPLITTYPECODE tinyint
        set @REVENUESPLITTYPECODE = 
          case 
            when @APPLICATIONCODE = 0 or @APPLICATIONCODE = 2 then 0
            when @APPLICATIONCODE = 1 then 4
            when @APPLICATIONCODE = 3 then 17 -- set sponsorship revenue for additional Donation

          end

        declare @SPLITSDECLININGGIFTAIDDESIGNATIONS xml
        declare @ISGIFTAIDSPONSORSHIPSPLITSDESIGNATIONS xml
        declare @REVENUESPLITSPONSORSHIPID 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 = @ADDITIONALAPPLICATIONS,
            @SPLITSDECLININGGIFTAID = @SPLITSDECLININGGIFTAIDDESIGNATIONS output,
            @ISGIFTAIDSPONSORSHIPSPLITS = @ISGIFTAIDSPONSORSHIPSPLITSDESIGNATIONS output,
            @TRANSACTIONCURRENCYID = @TRANSACTIONCURRENCYID,
            @BASECURRENCYID = @BASECURRENCYID,
            @BASEEXCHANGERATEID = @BASEEXCHANGERATEID,
            @ORGANIZATIONEXCHANGERATEID = @ORGANIZATIONEXCHANGERATEID,
            @REVENUESPLITSPONSORSHIPID = @REVENUESPLITSPONSORSHIPID output

        -- If this isn't a Donation, then gift aid can't be declined

        if @APPLICATIONCODE <> 0 and @APPLICATIONCODE <> 3
          set @SPLITSDECLININGGIFTAIDDESIGNATIONS = null

        -- If this isn't a Donation, then this can not be a sponsorship

        if @APPLICATIONCODE <> 0
          set @ISGIFTAIDSPONSORSHIPSPLITSDESIGNATIONS = null

        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)

        --vka 1/30/12 Bug 188343: in some cases a blank reference record already exists, so we need to call EDIT and not ADD on it

        if not exists(select ID from dbo.REVENUEREFERENCE where ID = @ID)
          exec dbo.USP_REVENUEREFERENCE_ADD @ID, @REFERENCE, @CHANGEAGENTID;
        else
          exec dbo.USP_REVENUEREFERENCE_EDIT @ID, @REFERENCE, @CHANGEAGENTID;

        -- Add payment details after splits are created so the distribution can be generated for sold stock/property

        -- Note that REVENUEPAYMENTMETHOD doesn't need multicurrency values; see comment on the table spec.

        declare @REVENUEPAYMETHODID uniqueidentifier
        set @REVENUEPAYMETHODID = newid();
        insert into dbo.REVENUEPAYMENTMETHOD (ID,REVENUEID, PAYMENTMETHODCODE, AMOUNT, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
          values (@REVENUEPAYMETHODID,@ID,@PAYMENTMETHODCODE, @FULLAMOUNT, @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, @GIFTINKINDITEMNAME, @GIFTINKINDDISPOSITIONCODE,
                                               @GIFTINKINDNUMBEROFUNITS, @GIFTINKINDFAIRMARKETVALUE,
                                               @DIRECTDEBITISREJECTED, @BASECURRENCYID, @TRANSACTIONCURRENCYID,
                                               @BASEEXCHANGERATEID, @MERCHANTACCOUNTID, @SALE_LOWPRICE, @SALE_MEDIANPRICE, @SALE_HIGHPRICE,
                                               @VENDORID, @SEPAMANDATEID;

        -- Note that @TOTALBENEFITS was run through its CONVERTAMOUNTSINXML SP earlier on in the commit.

        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

          -- Update the organization amount and exchange rate in the TRIBUTES XML.

          set @TRIBUTES = dbo.UFN_REVENUETRIBUTE_CONVERTAMOUNTSINXML(@TRIBUTES, @BASECURRENCYID, @ORGANIZATIONEXCHANGERATEID);
          exec dbo.USP_REVENUE_GETTRIBUTES_2_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


        --

        -- BEGIN Matching Gift code

        --


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

        declare @APPLIEDAMOUNTFORGENERATEMGCLAIM money;
        select @APPLIEDAMOUNTFORGENERATEMGCLAIM = sum(TRANSACTIONAMOUNT)
          from dbo.REVENUESPLIT
          where REVENUEID = @ID and REVENUESPLIT.TYPECODE = 0;

        /* AAW 10/27/09: Updated to work with multiple matching gifts, and auto-generated MG claims. */
        declare @MGTABLE table
        (
          [ID] uniqueidentifier,
          [MATCHEDCONSTITUENTID] uniqueidentifier,
          [ORGANIZATIONID] uniqueidentifier,
  [RELATIONSHIPID] uniqueidentifier,
          [MATCHINGGIFTCONDITIONTYPEID] uniqueidentifier,
          [AMOUNT] money, -- BASE AMOUNT

          [DATE] datetime,
          [SPLITS] xml,
          [COULDNOTDEFAULTMATCHINGGIFT] bit,
          [ORGANIZATIONAMOUNT] money,
          [ORGANIZATIONEXCHANGERATEID] uniqueidentifier,
          [TRANSACTIONAMOUNT] money,
          [TRANSACTIONCURRENCYID] uniqueidentifier,
          [BASEEXCHANGERATEID] uniqueidentifier,
          [EXCHANGERATE] decimal(20,8),
          [ISSPOTRATE] bit,
          [OPPORTUNITYID] uniqueidentifier
        );

        if @MGALTERED = 0
        begin

          insert into @MGTABLE
          (
            [ID],
            [MATCHEDCONSTITUENTID],
            [ORGANIZATIONID],
            [RELATIONSHIPID],
            [MATCHINGGIFTCONDITIONTYPEID],
            [TRANSACTIONAMOUNT],
            [DATE],
            [SPLITS],
            [COULDNOTDEFAULTMATCHINGGIFT],
            [TRANSACTIONCURRENCYID],
            [BASEEXCHANGERATEID],
            [EXCHANGERATE],
            [ISSPOTRATE],
            [OPPORTUNITYID]
          )
          select
            newid(),
            DEFAULTMATCHINGGIFTS.[MATCHEDCONSTITUENTID],
            DEFAULTMATCHINGGIFTS.[ORGANIZATIONID],
            DEFAULTMATCHINGGIFTS.[RELATIONSHIPID],
            DEFAULTMATCHINGGIFTS.[MATCHINGGIFTCONDITIONTYPEID],
            DEFAULTMATCHINGGIFTS.[AMOUNT],
            DEFAULTMATCHINGGIFTS.[DATE],
            DEFAULTMATCHINGGIFTS.[SPLITS],
            DEFAULTMATCHINGGIFTS.[COULDNOTDEFAULTMATCHINGGIFT],
            @TRANSACTIONCURRENCYID,
            @BASEEXCHANGERATEID, --Use the same exchange rate as the payment

            null,
            0,
            null
          from dbo.UFN_REVENUEBATCH_GETDEFAULTMATCHINGGIFTS_4(
            @CONSTITUENTID, 
            @DATE, 
            @APPLIEDAMOUNTFORGENERATEMGCLAIM, 
            @RECEIPTAMOUNT, 
            null, 
            @TRANSACTIONCURRENCYID, 
            @REVENUESTREAMS, 
            @ADDITIONALAPPLICATIONSSTREAM
          ) as [DEFAULTMATCHINGGIFTS];

          if exists(select 1 from @MGTABLE where COULDNOTDEFAULTMATCHINGGIFT = 1)
          begin
            --For some reason, we've decided to not create claims.  Flag the record as eligible.

            update dbo.REVENUE
            set ELIGIBLEFORMATCHINGGIFTCLAIM = 1,
              CHANGEDBYID = @CHANGEAGENTID,
              DATECHANGED = @CURRENTDATE
            where ID = @ID
          end

          delete @MGTABLE where COULDNOTDEFAULTMATCHINGGIFT = 1;

        end  
        else
        begin

          insert into @MGTABLE
          (
            [ID],
            [MATCHEDCONSTITUENTID],
            [ORGANIZATIONID],
            [RELATIONSHIPID],
            [MATCHINGGIFTCONDITIONTYPEID],
            [TRANSACTIONAMOUNT],
            [DATE],
            [SPLITS],
            [TRANSACTIONCURRENCYID],
            [BASEEXCHANGERATEID],
            [EXCHANGERATE],
            [ISSPOTRATE],
            [OPPORTUNITYID]
          )
          select
            case when MATCHINGGIFTSXML.[MATCHINGGIFT].value('ID[1]', 'uniqueidentifier') = '00000000-0000-0000-0000-000000000000' 
              then newid() 
              else MATCHINGGIFTSXML.[MATCHINGGIFT].value('ID[1]', 'uniqueidentifier') 
            end,
            @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'),
            MATCHINGGIFTSXML.[MATCHINGGIFT].value('TRANSACTIONCURRENCYID[1]', 'uniqueidentifier'),
            case MATCHINGGIFTSXML.[MATCHINGGIFT].value('BASEEXCHANGERATEID[1]', 'uniqueidentifier')
              when '00000000-0000-0000-0000-000000000001'
                then newid()
                else MATCHINGGIFTSXML.[MATCHINGGIFT].value('BASEEXCHANGERATEID[1]', 'uniqueidentifier')
              end,
            MATCHINGGIFTSXML.[MATCHINGGIFT].value('EXCHANGERATE[1]', 'decimal(20,8)'),
            case MATCHINGGIFTSXML.[MATCHINGGIFT].value('BASEEXCHANGERATEID[1]', 'uniqueidentifier')
              when '00000000-0000-0000-0000-000000000001'
                then 1
                else 0
              end,
            MATCHINGGIFTSXML.[MATCHINGGIFT].value('OPPORTUNITYID[1]', 'uniqueidentifier')
                from 
            @MATCHINGGIFTS.nodes('MATCHINGGIFTS/ITEM') as MATCHINGGIFTSXML([MATCHINGGIFT])
          where 
            MATCHINGGIFTSXML.[MATCHINGGIFT].value('COULDNOTDEFAULTMATCHINGGIFT[1]', 'bit') = 0 and
            -- Filter out records that don't have splits.

            MATCHINGGIFTSXML.[MATCHINGGIFT].exist('SPLITS') = 1;

        end

        -- Create spot rate records for any spot rates defined in the MATCHINGGIFTS collection

        --Replace with call to USP_CURRENCYEXCHANGERATE_ENSURESPOTRATEEXISTS for PBI 102747 in the future

        /*if not dbo.UFN_SECURITY_APPUSER_GRANTED_SYSTEMPRIVILEGE_IN_SYSTEMROLE(@CURRENTAPPUSERID,'911f104d-ba5f-4469-b0ae-184c879aea99') = 1
          and not dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1
        begin
          raiserror('User does not have the right to add a new spot rate.', 13, 1);
          return 1;
        end*/

        insert into dbo.CURRENCYEXCHANGERATE(
          ID, 
          FROMCURRENCYID,
          TOCURRENCYID,
          RATE,
          ASOFDATE,
          TYPECODE,
          SOURCECODEID,
          ADDEDBYID, 
          CHANGEDBYID, 
          DATEADDED, 
          DATECHANGED
        )
        select 
          BASEEXCHANGERATEID,
          TRANSACTIONCURRENCYID,
          @BASECURRENCYID,
          EXCHANGERATE,
          DATE,
          2,
          null,
          @CHANGEAGENTID,
          @CHANGEAGENTID,
          @CURRENTDATE,
          @CURRENTDATE
        from
          @MGTABLE [MGTABLE]
        where
          [MGTABLE].ISSPOTRATE = 1;

        -- Update @MGTABLE with converted amounts; can't do this in the initial insert because we may have to create spot rate records

        update 
          @MGTABLE
        set 
          [ORGANIZATIONAMOUNT] = CURRENCYVALUES.ORGANIZATIONAMOUNT,
          [ORGANIZATIONEXCHANGERATEID] = CURRENCYVALUES.ORGANIZATIONEXCHANGERATEID,
          [AMOUNT] = CURRENCYVALUES.BASEAMOUNT
        from
          @MGTABLE [MGTABLE]
          cross apply dbo.UFN_CURRENCY_GETCURRENCYVALUES(MGTABLE.[TRANSACTIONAMOUNT], MGTABLE.[DATE], @BASECURRENCYID, MGTABLE.[BASEEXCHANGERATEID], MGTABLE.[TRANSACTIONCURRENCYID]) CURRENCYVALUES


        --WI86405 JamesWill 2010-03-22 We need to have consistent REVENUESPLIT IDs across multiple table inserts

        declare @MGSPLITS_T table --@MGSPLITSTABLE is already being used

        (
          MGTABLEID uniqueidentifier,
          SPLITID uniqueidentifier,
          AMOUNT money,
          DESIGNATIONID uniqueidentifier,
          DESIGNATIONTRANSLATION nvarchar(255),
          TYPECODE tinyint,
          BASECURRENCYID uniqueidentifier,
          ORGANIZATIONAMOUNT money,
          ORGANIZATIONEXCHANGERATEID uniqueidentifier,
          TRANSACTIONAMOUNT money,
          TRANSACTIONCURRENCYID uniqueidentifier,
          BASEEXCHANGERATEID uniqueidentifier
        );

        insert into @MGSPLITS_T 
        (
          MGTABLEID, 
          SPLITID, 
          AMOUNT, 
          DESIGNATIONID, 
          DESIGNATIONTRANSLATION, 
          TYPECODE, 
BASECURRENCYID, 
          ORGANIZATIONAMOUNT, 
          ORGANIZATIONEXCHANGERATEID,
          TRANSACTIONAMOUNT,
          TRANSACTIONCURRENCYID,
          BASEEXCHANGERATEID
        )
        select
          [MGTABLE].ID,
          newid(),
          CURRENCYVALUES.BASEAMOUNT,
          T.c.value('DESIGNATIONID[1]', 'uniqueidentifier'),
          T.c.value('DESIGNATIONTTRANSLATION[1]', 'nvarchar(255)'),
          T.c.value('TYPECODE[1]', 'tinyint'),
          @BASECURRENCYID,
          CURRENCYVALUES.ORGANIZATIONAMOUNT,
          CURRENCYVALUES.ORGANIZATIONEXCHANGERATEID,
          T.c.value('AMOUNT[1]', 'money'), -- This is transaction amount

          [MGTABLE].TRANSACTIONCURRENCYID,
          [MGTABLE].BASEEXCHANGERATEID
        from 
          @MGTABLE as [MGTABLE]
          cross apply MGTABLE.[SPLITS].nodes('SPLITS/ITEM') as T(c)
          cross apply dbo.UFN_CURRENCY_GETCURRENCYVALUES
          (
            T.c.value('AMOUNT[1]', 'money'), 
            [MGTABLE].[DATE], 
            @BASECURRENCYID, 
            [MGTABLE].[BASEEXCHANGERATEID], 
            [MGTABLE].[TRANSACTIONCURRENCYID]
          ) CURRENCYVALUES

        insert into dbo.FINANCIALTRANSACTION
        (
            ID
          ,CONSTITUENTID
          ,TYPECODE
          ,TRANSACTIONAMOUNT
          ,BASEAMOUNT
          ,ORGAMOUNT
          ,[DATE]
          ,TRANSACTIONCURRENCYID
          ,BASEEXCHANGERATEID
          ,ORGEXCHANGERATEID
          ,PDACCOUNTSYSTEMID
          ,POSTDATE
          ,POSTSTATUSCODE
          ,USERDEFINEDID
          ,ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
        )
        select
          MGTABLE.ID
          ,MGTABLE.ORGANIZATIONID
          ,3 TYPECODE
          ,MGTABLE.TRANSACTIONAMOUNT
          ,MGTABLE.AMOUNT
          ,MGTABLE.ORGANIZATIONAMOUNT
          ,MGTABLE.[DATE]
          ,MGTABLE.TRANSACTIONCURRENCYID
          ,MGTABLE.BASEEXCHANGERATEID
          ,MGTABLE.ORGANIZATIONEXCHANGERATEID
          ,@PDACCOUNTSYSTEMID
          ,null POSTDATE
          ,3 POSTSTATUSCODE -- Matching gifts aren't posted.

          ,'' USERDEFINEDID
          ,@CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
        from 
          @MGTABLE as MGTABLE

        insert into dbo.REVENUE_EXT
        (
          ID
          ,BATCHNUMBER
          ,NONPOSTABLEBASECURRENCYID
          ,ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
        )
        select
          ID
          ,@BATCHNUMBER
          ,@BASECURRENCYID
          ,@CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
        from
          @MGTABLE as MGTABLE

        insert into dbo.FINANCIALTRANSACTIONLINEITEM
        (
          ID
          ,FINANCIALTRANSACTIONID
          ,TRANSACTIONAMOUNT
          ,[DESCRIPTION]
          ,SEQUENCE
          ,TYPECODE
          ,POSTSTATUSCODE
          ,BASEAMOUNT
          ,ORGAMOUNT
          ,ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
        )
        select
          MGSPLITS.SPLITID
          ,MGTABLE.ID
          ,MGSPLITS.TRANSACTIONAMOUNT
          ,'' [DESCRIPTION]
          ,row_number() over (order by MGSPLITS.MGTABLEID) SEQUENCE
          ,0 TYPECODE
          ,3 POSTSTATUSCODE
          ,MGSPLITS.AMOUNT BASEAMOUNT
          ,MGSPLITS.ORGANIZATIONAMOUNT
          ,@CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
        from
          @MGSPLITS_T as [MGSPLITS] 
          inner join @MGTABLE as [MGTABLE] on [MGTABLE].ID = [MGSPLITS].MGTABLEID

        insert into dbo.REVENUESPLIT_EXT
        (
            ID
          ,DESIGNATIONID
          ,TYPECODE
          ,APPLICATIONCODE
          ,ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
        )
        select
          MGSPLITS.SPLITID
          ,MGSPLITS.DESIGNATIONID
          ,0 TYPECODE
          ,0 APPLICATIONCODE
          ,@CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
        from @MGSPLITS_T as [MGSPLITS] 
        inner join @MGTABLE as [MGTABLE] on [MGTABLE].ID = [MGSPLITS].MGTABLEID

        /* Create the revenue payment method entry: */
        insert into dbo.REVENUEPAYMENTMETHOD
        (
          [REVENUEID],
          [PAYMENTMETHODCODE], 
          [AMOUNT], 
          [ADDEDBYID], 
          [CHANGEDBYID], 
          [DATEADDED], 
          [DATECHANGED]
        )
        select
          MGTABLE.[ID],
          9,
          MGTABLE.[AMOUNT],
          @CHANGEAGENTID,
          @CHANGEAGENTID,
          @CURRENTDATE,
          @CURRENTDATE
        from @MGTABLE as [MGTABLE]

        insert into dbo.REVENUEMATCHINGGIFT 
        (
          [ID], 
          [MATCHINGGIFTCONDITIONID], 
          [MGSOURCEREVENUEID],
          [ADDEDBYID], 
          [CHANGEDBYID], 
          [DATEADDED], 
          [DATECHANGED], 
          [RELATIONSHIPID], 
          [ISACTIVE]
        )
        select
          MGTABLE.[ID],
          MGTABLE.[MATCHINGGIFTCONDITIONTYPEID],
          @ID,
          @CHANGEAGENTID,
          @CHANGEAGENTID,
          @CURRENTDATE,
          @CURRENTDATE,
          MGTABLE.[RELATIONSHIPID],
          1
        from @MGTABLE as [MGTABLE]

        insert into dbo.REVENUESCHEDULE 
        (
          [ID], 
          [STARTDATE], 
          [FREQUENCYCODE], 
          [NUMBEROFINSTALLMENTS], 
          [ADDEDBYID], 
          [CHANGEDBYID], 
          [DATEADDED], 
          [DATECHANGED]
        )
        select
          MGTABLE.[ID],
          MGTABLE.[DATE],
          5,
          1,
          @CHANGEAGENTID,
          @CHANGEAGENTID,
          @CURRENTDATE,
          @CURRENTDATE
        from  @MGTABLE as [MGTABLE];

        insert into dbo.INSTALLMENT 
        (
          [ID], 
          [REVENUEID], 
          [AMOUNT], 
          [DATE], 
          [SEQUENCE], 
          [BASECURRENCYID],
          [ORGANIZATIONAMOUNT],
          [ORGANIZATIONEXCHANGERATEID],
          [TRANSACTIONAMOUNT],
          [TRANSACTIONCURRENCYID],
          [BASEEXCHANGERATEID],
          [ADDEDBYID], 
          [CHANGEDBYID], 
          [DATEADDED], 
          [DATECHANGED]
        )
        select
          newid(),
          MGTABLE.[ID],
          MGTABLE.[AMOUNT],
          MGTABLE.[DATE],
          1,
          @BASECURRENCYID,
          MGTABLE.[ORGANIZATIONAMOUNT], 
          MGTABLE.[ORGANIZATIONEXCHANGERATEID], 
          MGTABLE.[TRANSACTIONAMOUNT], 
          MGTABLE.[TRANSACTIONCURRENCYID],
          MGTABLE.[BASEEXCHANGERATEID], 
          @CHANGEAGENTID,
          @CHANGEAGENTID,
          @CURRENTDATE,
          @CURRENTDATE
        from 
          @MGTABLE as [MGTABLE];

        declare MGOPPORTUNITYCURSOR cursor local fast_forward for
          select
            MGTABLE.[ID],
            MGTABLE.[OPPORTUNITYID]
          from
            @MGTABLE as [MGTABLE]

        declare @MGID uniqueidentifier;
        declare @MGOPPORTUNITYID uniqueidentifier;

        open MGOPPORTUNITYCURSOR
        fetch next from MGOPPORTUNITYCURSOR into @MGID, @MGOPPORTUNITYID
        while @@fetch_status = 0
        begin
          if @MGOPPORTUNITYID is not null
            exec dbo.USP_DATAFORMTEMPLATE_ADD_REVENUEOPPORTUNITYLINK null, @MGID, @MGOPPORTUNITYID, @CHANGEAGENTID, 1

          fetch next from MGOPPORTUNITYCURSOR into @MGID, @MGOPPORTUNITYID
        end

        close MGOPPORTUNITYCURSOR;
        DEALLOCATE MGOPPORTUNITYCURSOR;

        declare @GIFTSPLIT table
        (
          ID uniqueidentifier,
          DESIGNATIONID uniqueidentifier
        );

        insert into @GIFTSPLIT (ID, DESIGNATIONID)
        select
          --Because of the group by and having clauses there wil be only

          --one ID per designation returned and the aggregator does not matter

          cast(max(cast(REVENUESPLIT_EXT.ID as binary(16))) as uniqueidentifier) as ID,
          REVENUESPLIT_EXT.DESIGNATIONID
        from 
          dbo.FINANCIALTRANSACTIONLINEITEM 
          inner join dbo.REVENUESPLIT_EXT on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID
        where 
          FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = @ID
          and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null
          and FINANCIALTRANSACTIONLINEITEM.TYPECODE != 1
        group by
          REVENUESPLIT_EXT.DESIGNATIONID
        having
          count(*) = 1;

        declare CUR_MGCAMPAIGNS cursor local fast_forward for 
        select 
          [MGSPLITS].SPLITID MGSPLITID,
          case
            when GIFTSPLIT.DESIGNATIONID is null then (
                select CAMPAIGNID,CAMPAIGNSUBPRIORITYID 
                from dbo.UFN_DESIGNATION_GETCAMPAIGNSTODEFAULT([MGSPLITS].DESIGNATIONID, @DATE) 
                for xml raw('ITEM'),type,elements,root('CAMPAIGNS'),BINARY BASE64
              )
            else  (
                select CAMPAIGNID,CAMPAIGNSUBPRIORITYID 
                from dbo.UFN_REVENUESPLIT_GETCAMPAIGNS(GIFTSPLIT.ID) 
                for xml raw('ITEM'),type,elements,root('CAMPAIGNS'),BINARY BASE64
              )
          end
        from
          @MGSPLITS_T as [MGSPLITS]
          left join @GIFTSPLIT as GIFTSPLIT on GIFTSPLIT.DESIGNATIONID = [MGSPLITS].DESIGNATIONID;

        open CUR_MGCAMPAIGNS;
        declare @MGSPLITID uniqueidentifier;
        declare @MGCAMPAIGNS xml;

        fetch next from CUR_MGCAMPAIGNS into @MGSPLITID, @MGCAMPAIGNS;
        while @@fetch_status <> -1
        begin
          if @@fetch_status <> -2
          begin
            exec dbo.USP_REVENUESPLIT_GETCAMPAIGNS_ADDFROMXML @MGSPLITID, @MGCAMPAIGNS, @CHANGEAGENTID;
          end

          fetch next from CUR_MGCAMPAIGNS into @MGSPLITID, @MGCAMPAIGNS;
        end

        close CUR_MGCAMPAIGNS;
        deallocate CUR_MGCAMPAIGNS;

        --

        -- END Matching Gift code

        --



        /* 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 = '') or (not @NOTEHTMLNOTE = '')
        begin
          insert into dbo.REVENUENOTE(DATEENTERED, TITLE, AUTHORID, TEXTNOTE, REVENUENOTETYPECODEID, REVENUEID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, HTMLNOTE)
            values(@NOTEDATEENTERED, @NOTETITLE, @NOTEAUTHORID, @NOTETEXTNOTE, @NOTETYPECODEID, @ID, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE, @NOTEHTMLNOTE)
        end

        --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 dbo.UFN_VALID_BASICGL_INSTALLED() = 1
        begin
          if @PDACCOUNTSYSTEMID is null
            set @PDACCOUNTSYSTEMID = dbo.UFN_PDACCOUNTSYSTEM_GETDEFAULTSYSTEMIDSFORUSER(@CURRENTAPPUSERID)

          -- Link revenue to Account System

          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
      begin
        set @TRANSACTIONVATAMOUNT = @VATAMOUNT;
        set @TRANSACTIONAMOUNTTOTAX = @AMOUNTFORVAT;

        exec dbo.USP_CURRENCY_GETCURRENCYVALUES @TRANSACTIONVATAMOUNT,
                                                @CURRENTDATE,
                                                @BASECURRENCYID,
                                                @BASEEXCHANGERATEID,
                                                @TRANSACTIONCURRENCYID,
                                                @BASEVATAMOUNT output,
                                                @ORGANIZATIONCURRENCYID,
                                                @ORGANIZATIONVATAMOUNT output, 
                                                @ORGANIZATIONEXCHANGERATEID, 
                                                0;

        exec dbo.USP_CURRENCY_GETCURRENCYVALUES @TRANSACTIONAMOUNTTOTAX,
                                                @CURRENTDATE,
                                                @BASECURRENCYID,
                                                @BASEEXCHANGERATEID,
                                                @TRANSACTIONCURRENCYID,
                                                @BASEAMOUNTTOTAX output,
                                                @ORGANIZATIONCURRENCYID,
                                                @ORGANIZATIONAMOUNTTOTAX output, 
                                                @ORGANIZATIONEXCHANGERATEID, 
                                                0;

        insert into dbo.REVENUEVAT(
          ID,
          AMOUNTTOTAX,
          VATAMOUNT,
          BASECURRENCYID,
          BASEEXCHANGERATEID,
          TRANSACTIONAMOUNTTOTAX,
          TRANSACTIONVATAMOUNT,
          TRANSACTIONCURRENCYID,
          ORGANIZATIONAMOUNTTOTAX,
          ORGANIZATIONVATAMOUNT,
          ORGANIZATIONEXCHANGERATEID,
          VATTAXRATEID,
          ADDEDBYID,
          CHANGEDBYID,
          DATEADDED,
          DATECHANGED
        )values(
          @ID,
          @BASEAMOUNTTOTAX,
          @BASEVATAMOUNT,
          @BASECURRENCYID,
          @BASEEXCHANGERATEID,
          @TRANSACTIONAMOUNTTOTAX,
          @TRANSACTIONVATAMOUNT,
          @TRANSACTIONCURRENCYID,
          @ORGANIZATIONAMOUNTTOTAX,
          @ORGANIZATIONVATAMOUNT,
          @ORGANIZATIONEXCHANGERATEID,
          @VATTAXRATEID,
          @CHANGEAGENTID,
          @CHANGEAGENTID,
          @CURRENTDATE,
          @CURRENTDATE
        )
      end

      -- Generate gift aid revenue split records

      exec dbo.USP_GIFTAIDREVENUESPLIT_ADDUPDATESPLITS @ID, @APPEALID, @PAYMENTMETHODCODE, @CREDITTYPECODEID, @CHANGEAGENTID, @DATE, @TYPECODE, @SPLITSDECLININGGIFTAID, @COVENANTGIFTSPLITS, @ISGIFTAIDSPONSORSHIPSPLITS


      -- Solicitors and recognition credits need to be added after REVENUESPLITGIFTAID records are added since that table is used in the REVENUESPLITID

      -- lookup since 'declines gift aid' is a part of the check.

      if (@APPLICATIONCODE is not null and @SPLITCOUNT > 1) or (@APPLICATIONCODE is null and @SINGLEAPPLICATIONID is null and (@ADDITIONALAPPLICATIONCOUNT + @STREAMCOUNT > 1))
      begin
        if not @APPLICATIONSOLICITORS is null
          exec dbo.USP_REVENUEBATCH_ADDAPPLICATIONSOLICITORS @ID, @APPLICATIONSOLICITORS, @CHANGEAGENTID, @CURRENTDATE, @REVENUESPLITSPONSORSHIPID;
      end
      else
        if not @SOLICITORS is null
          exec dbo.USP_REVENUEBATCH_ADDSOLICITORS @ID, @SOLICITORS, @CHANGEAGENTID, @CURRENTDATE;

      if (@APPLICATIONCODE is not null and @SPLITCOUNT > 1) or (@APPLICATIONCODE is null and @SINGLEAPPLICATIONID is null and (@ADDITIONALAPPLICATIONCOUNT + @STREAMCOUNT > 1))
      begin
        -- Recognition credits should be created even when user is anonymous but has defined explicit recognitions.

        if not @APPLICATIONRECOGNITIONS is null
        begin
          exec dbo.USP_REVENUEBATCH_ADDAPPLICATIONRECOGNITIONS @ID , @APPLICATIONRECOGNITIONS, @CHANGEAGENTID, @CURRENTDATE, @REVENUESPLITSPONSORSHIPID, @APPLICATIONPLEDGEREVENUEID;
        end
        else
        begin
          -- BBIS split transactions have additional applications, but not application recognitions

          -- We need to call USP_REVENUEBATCH_ADDRECOGNITIONS to add recognitions in this case

          if not @RECOGNITIONS is null and @BBNCTRANID > 0
          begin
            exec dbo.USP_REVENUEBATCH_ADDRECOGNITIONS @ID, @RECOGNITIONS, @CHANGEAGENTID, @CURRENTDATE;
          end
        end
      end
      else  -- Recognition credits should be created even when user is anonymous but has defined explicit recognitions.

        if not @RECOGNITIONS is null 
          exec dbo.USP_REVENUEBATCH_ADDRECOGNITIONS @ID, @RECOGNITIONS, @CHANGEAGENTID, @CURRENTDATE;

      declare @AUTOADDMGCLAIMCREDIT bit = 0;
      declare @CLAIMDEFAULTCREDITTYPEID uniqueidentifier = null;
      declare @SHOULDADDDEFAULTSFORINDIVIDUAL bit = 0;
      declare @SHOULDADDDEFAULTSFORGROUP bit = 0;
      declare @AUTOADDMGCLAIMCREDITFORORG bit;
      declare @CLAIMDEFAULTCREDITTYPEIDFORORG uniqueidentifier;

      -- Do matching gift claim recognitions AFTER gift recognitions,

      -- otherwise, they may not be added correctly.

      select
        @AUTOADDMGCLAIMCREDIT = ADDRECOGNITIONCREDITSONMGCLAIMADD,
        @CLAIMDEFAULTCREDITTYPEID = 
        case MGCLAIMADDRECOGNITIONTYPECODE
          when 0 then CLAIMREVENUERECOGNITIONTYPECODEID
          when 1 then null
        end,
        @AUTOADDMGCLAIMCREDITFORORG = ADDRECOGNITIONCREDITSTOMATCHINGORGONMGCLAIMADD,
        @CLAIMDEFAULTCREDITTYPEIDFORORG = MATCHINGORGCLAIMREVENUERECOGNITIONTYPECODEID
      from MATCHINGGIFTPREFERENCEINFO;

      if @AUTOADDMGCLAIMCREDIT = 1 and (select count(*) from dbo.MATCHINGGIFTPREFERENCEINFO where MGCLAIMADDRECOGNITIONTYPECODE = 0) > 0
        set @SHOULDADDDEFAULTSFORINDIVIDUAL = 1;

      if @AUTOADDMGCLAIMCREDIT = 1 and (select count(*) from dbo.MATCHINGGIFTPREFERENCEINFO where MGCLAIMADDRECOGNITIONTYPECODE = 1) > 0
        set @SHOULDADDDEFAULTSFORGROUP = 1;

      declare @MATCHINGGIFTID uniqueidentifier;
      declare @MATCHINGORGANIZATIONID uniqueidentifier;
      declare @MATCHINGGIFTDATE datetime;
      declare MGCURSOR cursor local fast_forward for
        select
          MGTABLE.[ID],
          MGTABLE.[ORGANIZATIONID],
          MGTABLE.[DATE]
        from @MGTABLE as [MGTABLE];

      declare @MGRECOGNITIONSPLITS xml;

      open MGCURSOR;
      fetch next from MGCURSOR into @MATCHINGGIFTID, @MATCHINGORGANIZATIONID, @MATCHINGGIFTDATE;
      while @@fetch_status = 0
      begin
        set @MGRECOGNITIONSPLITS = 
        (
          select SPLITID as ID, AMOUNT, DESIGNATIONID, DESIGNATIONTRANSLATION, TYPECODE, BASECURRENCYID, ORGANIZATIONAMOUNT, ORGANIZATIONEXCHANGERATEID
          from @MGSPLITS_T 
          where MGTABLEID = @MATCHINGGIFTID
          for xml raw('ITEM'),type,elements,root('SPLITS'),binary base64
        );

        exec dbo.USP_PLEDGE_GENERATEINSTALLMENTSPLITS @MATCHINGGIFTID, @CHANGEAGENTID, @CURRENTDATE;
        exec dbo.USP_REVENUECATEGORY_UPDATESPLITS @MATCHINGGIFTID, @CATEGORYCODEID, @CHANGEAGENTID, @CURRENTDATE;

        --JamesWill 2010-02-22 WI75863 If the system is configured to auto-create recognitions for matching gifts, do so now

        if @AUTOADDMGCLAIMCREDIT = 1
        begin
          declare @ORIGINALGIFTID uniqueidentifier = @ID;
          declare @ORIGINALDONORID uniqueidentifier = @CONSTITUENTID;
          declare @ORIGINALDONATIONDATE datetime = @DATE;

          if @SHOULDADDDEFAULTSFORINDIVIDUAL = 1
            exec dbo.USP_RECOGNITIONCREDIT_ADDDEFAULTSFORMGSPLITS @MGRECOGNITIONSPLITS, @ORIGINALDONORID, @ORIGINALDONATIONDATE, @CLAIMDEFAULTCREDITTYPEID, @CHANGEAGENTID, @CURRENTDATE;      

          if @SHOULDADDDEFAULTSFORGROUP = 1
            exec dbo.USP_RECOGNITIONCREDIT_ADDTOGROUPMEMBERS @MGRECOGNITIONSPLITS, @ORIGINALDONORID, @ORIGINALDONATIONDATE, @ORIGINALGIFTID, @CLAIMDEFAULTCREDITTYPEID, @CHANGEAGENTID, @CURRENTDATE, 1;
        end

        if @AUTOADDMGCLAIMCREDITFORORG = 1
          exec dbo.USP_RECOGNITIONCREDIT_ADDDEFAULTSFORMGSPLITS @MGRECOGNITIONSPLITS, @MATCHINGORGANIZATIONID, @MATCHINGGIFTDATE, @CLAIMDEFAULTCREDITTYPEIDFORORG, @CHANGEAGENTID, @CURRENTDATE;

        fetch next from MGCURSOR into @MATCHINGGIFTID, @MATCHINGORGANIZATIONID, @MATCHINGGIFTDATE;
      end

      close MGCURSOR;
      deallocate MGCURSOR;
    end
    else if @TYPECODE = 1 -- pledge

    begin
      insert into dbo.FINANCIALTRANSACTION 
        (ID, CONSTITUENTID, TYPECODE, TRANSACTIONAMOUNT, TRANSACTIONCURRENCYID, BASEEXCHANGERATEID, ORGEXCHANGERATEID, USERDEFINEDID,
            BASEAMOUNT, ORGAMOUNT, PDACCOUNTSYSTEMID, DATE, POSTDATE, POSTSTATUSCODE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
      select
        @ID, @CONSTITUENTID, 1, @AMOUNT, @TRANSACTIONCURRENCYID, @BASEEXCHANGERATEID, @ORGANIZATIONEXCHANGERATEID, coalesce(rtrim(ltrim(@REVENUELOOKUPID)),''),
            @BASEAMOUNT, @ORGANIZATIONAMOUNT, @PDACCOUNTSYSTEMID, @DATE, @POSTDATE, case when @DONOTPOST = 1 then 3 else 1 end, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
      insert into dbo.REVENUE_EXT
        (ID, BATCHNUMBER, DONOTRECEIPT, RECEIPTAMOUNT, SOURCECODE, FINDERNUMBER, APPEALID, MAILINGID, CHANNELCODEID, GIVENANONYMOUSLY, 
            DONOTACKNOWLEDGE, BENEFITSWAIVED, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
      values
        (@ID, @BATCHNUMBER, 1, 0, @SOURCECODE, @FINDERNUMBER, @APPEALID, @MAILINGID, @CHANNELCODEID, @GIVENANONYMOUSLY,
            @DONOTACKNOWLEDGE, @BENEFITSWAIVED, @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, @BASEAMOUNT,@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, SEPAMANDATEID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, DIRECTDEBITRESULTCODE)
          values (@ID, @REFERENCEDATE, @REFERENCENUMBER, @CONSTITUENTACCOUNTID, @DDISOURCECODEID, @DDISOURCEDATE, @SENDNEWINSTRUCTION, @NEWINSTRUCTIONTOSEND, @SEPAMANDATEID, @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,
          @TRANSACTIONCURRENCYID = @TRANSACTIONCURRENCYID,
          @BASECURRENCYID = @BASECURRENCYID,
          @BASEEXCHANGERATEID = @BASEEXCHANGERATEID,
          @ORGANIZATIONEXCHANGERATEID = @ORGANIZATIONEXCHANGERATEID

      exec dbo.USP_REVENUECATEGORY_UPDATESPLITS @REVENUEID = @ID, 
        @CATEGORYCODEID = @CATEGORYCODEID, 
        @CHANGEAGENTID = @CHANGEAGENTID,
        @CURRENTDATE = @CURRENTDATE

      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

      if @INSTALLMENTFREQUENCYCODE <> 4 and (coalesce(@INSTALLMENTS.exist('INSTALLMENTS/ITEM'),0) = 0 or exists(select 1 from @INSTALLMENTS.nodes('/INSTALLMENTS/ITEM') T1(c) where (T1.c.value('(SEQUENCE)[1]','INTEGER') is null)))
          set @INSTALLMENTS = dbo.UFN_REVENUEBATCH_GENERATEINSTALLMENTS2(@AMOUNT, @INSTALLMENTFREQUENCYCODE, @INSTALLMENTSTARTDATE, @NUMBEROFINSTALLMENTS, @TRANSACTIONCURRENCYID, @TOTALBENEFITS);

      if @INSTALLMENTS is null or  not exists(select 1 from @INSTALLMENTS.nodes('/INSTALLMENTS/ITEM') T1(c) cross apply T1.c.nodes('./INSTALLMENTSPLITS/ITEM') as T2(split) where T2.split.value('(DESIGNATIONID)[1]', 'uniqueidentifier') is not null)
      begin

        --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,@BASECURRENCYID,@ORGANIZATIONEXCHANGERATEID,@TRANSACTIONCURRENCYID,@BASEEXCHANGERATEID);

        -- 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;
      end
      else
      begin
        exec dbo.USP_PLEDGE_UPDATEINSTALLMENT3 @ID
        ,@INSTALLMENTS
        ,@CHANGEAGENTID
        ,@CURRENTDATE
        ,null
        ,null
        ,null
        ,null
        ,@BASECURRENCYID
        ,@ORGANIZATIONEXCHANGERATEID
        ,@TRANSACTIONCURRENCYID
        ,@BASEEXCHANGERATEID;
      end

      insert into dbo.PLEDGEINSTALLMENTOPTION
      (ID,INSTALLMENTAMOUNT,SPLITSCHEDULEOPTIONCODE,ADDEDBYID,CHANGEDBYID,DATEADDED,DATECHANGED)
      values 
      (@ID,case @INSTALLMENTFREQUENCYCODE when 4 then null else @INSTALLMENTAMOUNT end,0,@CHANGEAGENTID,@CHANGEAGENTID,@CURRENTDATE,@CURRENTDATE);     

      -- Solicitors and recognition credits need to be created before the payment for 

      -- pledge since the payment defaults its solicitors and recognition credits from

      -- the pledge record

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

      -- Recognition credits should be created even when user is anonymous but has defined explicit recognitions.

      if not @RECOGNITIONS is null
        exec dbo.USP_REVENUEBATCH_ADDRECOGNITIONS @ID, @RECOGNITIONS, @CHANGEAGENTID, @CURRENTDATE;

      if @PAYMENTMETHODCODE = 3 and @DDISOURCECODEID is not null and dbo.UFN_PAPERLESSMANDATES_DAYSADVANCEDNOTICE_FORREVENUE(@ID) = -1
      begin
        --Bug 138736 - AdamBu 2/2/11 - Error on invalid paperless mandate setup.

        raiserror('BBERR_MULTIPLE_PAPERLESSMANDATECONFIGS', 13, 1);
        return 1;
      end

      -- create payment for pledge

      if not @PAYMENTFORPLEDGEAMOUNT = 0
      begin

        -- Need to run the LINKTOREVENUE SP before attempting to add a payment or USP_PAYMENT_ADD can't check for mismatching account systems

        if dbo.UFN_VALID_BASICGL_INSTALLED() = 1
        begin
          if @PDACCOUNTSYSTEMID is null
            set @PDACCOUNTSYSTEMID = dbo.UFN_PDACCOUNTSYSTEM_GETDEFAULTSYSTEMIDSFORUSER(@CURRENTAPPUSERID)

          -- Link revenue to Account System

          exec dbo.USP_PDACCOUNTSYSTEM_LINKTOREVENUE @ID, @PDACCOUNTSYSTEMID, @CURRENTDATE, @CHANGEAGENTID;
        end

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

        --If the payment to be paid uses a payment method of Credit card - 4 digits (98) then add the payment with Credit Card method, since the 4 digits is not a supported method on payments

        declare @ALTPLEDGEPAYMENTMETHODCODE tinyint;
        set @ALTPLEDGEPAYMENTMETHODCODE = case when @PAYMENTMETHODCODE = 98 then 2 else @PAYMENTMETHODCODE end;

        if @ALTPLEDGEPAYMENTMETHODCODE = 9 -- None

          set @ALTPLEDGEPAYMENTMETHODCODE = 1; -- Check


        declare @PAYMENTFORPLEDGE_REVENUEID uniqueidentifier;
        exec dbo.USP_PAYMENT_ADD 
@ID = @PAYMENTFORPLEDGE_REVENUEID output, 
            @CHANGEAGENTID = @CHANGEAGENTID, 
            @CURRENTDATE = @CURRENTDATE, 
            @CONSTITUENTID = @CONSTITUENTID, 
            @DATE = @DATE, 
            @AMOUNT = @PAYMENTFORPLEDGEAMOUNT,
            @PAYMENTMETHODCODE = @ALTPLEDGEPAYMENTMETHODCODE,
            @CREDITTYPECODEID = @CREDITTYPECODEID,
            @CREDITCARDNUMBER = @CREDITCARDNUMBER,
            @CARDHOLDERNAME = @CARDHOLDERNAME,
            @EXPIRESON = @EXPIRESON,
            @AUTHORIZATIONCODE = @AUTHORIZATIONCODE,
            @REFERENCENUMBER = @REFERENCENUMBER,
            @REFERENCEDATE = @REFERENCEDATE,
            @CONSTITUENTACCOUNTID = @CONSTITUENTACCOUNTID,
            @DIRECTDEBITRESULTCODE = @DIRECTDEBITRESULTCODE,
            @OTHERPAYMENTMETHODCODEID = @OTHERPAYMENTMETHODCODEID,
            @POSTDATE = @DATE,
            @REVENUESTREAMS = @PAYMENTFORPLEDGE_REVSTREAMS,
            @FINDERNUMBER = @FINDERNUMBER,
            @SOURCECODE = @SOURCECODE,
            @BATCHNUMBER = @BATCHNUMBER,
            @RECEIPTAMOUNT = @PAYMENTFORPLEDGERECEIPTAMOUNT,
            @PDACCOUNTSYSTEMID = @PDACCOUNTSYSTEMID,
            @TRANSACTIONCURRENCYID = @TRANSACTIONCURRENCYID,
            @BASECURRENCYID = @BASECURRENCYID,
            @BASEEXCHANGERATEID = @BASEEXCHANGERATEID,
            @EXCHANGERATE = @EXCHANGERATE,
            @CURRENTAPPUSERID = @CURRENTAPPUSERID,
            @GIVENANONYMOUSLY = @GIVENANONYMOUSLY
      end

      -- Note that @TOTALBENEFITS was run through its CONVERTAMOUNTSINXML SP earlier on in the commit.

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

      if not @TRIBUTES is null
      begin

        --SlyyMu 8/17/10.  Tributes don't have a transaction currency, @BASETOORGANIZATIONEXCHANGERATEID handles the

        -- case where the revenue split ORGANIZATIONEXCHANGERATE is for converting from transaction to org currencies

        set @TRIBUTES = dbo.UFN_REVENUETRIBUTE_CONVERTAMOUNTSINXML(@TRIBUTES, @BASECURRENCYID, @BASETOORGANIZATIONEXCHANGERATEID);

        exec dbo.USP_REVENUE_GETTRIBUTES_2_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 = '') or (not @NOTEHTMLNOTE = '') 
        insert into dbo.REVENUENOTE(DATEENTERED, TITLE, AUTHORID, TEXTNOTE, REVENUENOTETYPECODEID, REVENUEID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, HTMLNOTE)
          values(@NOTEDATEENTERED, @NOTETITLE, @NOTEAUTHORID, @NOTETEXTNOTE, @NOTETYPECODEID, @ID, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE, @NOTEHTMLNOTE)

      if dbo.UFN_VALID_BASICGL_INSTALLED() = 1 and @PAYMENTFORPLEDGEAMOUNT = 0
      begin
        if @PDACCOUNTSYSTEMID is null
          set @PDACCOUNTSYSTEMID = dbo.UFN_PDACCOUNTSYSTEM_GETDEFAULTSYSTEMIDSFORUSER(@CURRENTAPPUSERID)

        -- Link revenue to Account System

        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

        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
      begin
        set @TRANSACTIONVATAMOUNT = @VATAMOUNT;
        set @TRANSACTIONAMOUNTTOTAX = @AMOUNTFORVAT;

        exec dbo.USP_CURRENCY_GETCURRENCYVALUES @TRANSACTIONVATAMOUNT,
                                                @CURRENTDATE,
                                                @BASECURRENCYID,
                                                @BASEEXCHANGERATEID,
                                                @TRANSACTIONCURRENCYID,
                                                @BASEVATAMOUNT output,
                                                @ORGANIZATIONCURRENCYID,
                                                @ORGANIZATIONVATAMOUNT output, 
                                                @ORGANIZATIONEXCHANGERATEID, 
                                                0;

        exec dbo.USP_CURRENCY_GETCURRENCYVALUES @TRANSACTIONAMOUNTTOTAX,
                                                @CURRENTDATE,
                                                @BASECURRENCYID,
                                                @BASEEXCHANGERATEID,
                                                @TRANSACTIONCURRENCYID,
                                                @BASEAMOUNTTOTAX output,
                                                @ORGANIZATIONCURRENCYID,
                                                @ORGANIZATIONAMOUNTTOTAX output, 
                                                @ORGANIZATIONEXCHANGERATEID, 
                                                0;

        insert into dbo.REVENUEVAT(
          ID,
          AMOUNTTOTAX,
          VATAMOUNT,
          BASECURRENCYID,
          BASEEXCHANGERATEID,
          TRANSACTIONAMOUNTTOTAX,
          TRANSACTIONVATAMOUNT,
          TRANSACTIONCURRENCYID,
          ORGANIZATIONAMOUNTTOTAX,
          ORGANIZATIONVATAMOUNT,
          ORGANIZATIONEXCHANGERATEID,
          VATTAXRATEID,
          ADDEDBYID,
          CHANGEDBYID,
          DATEADDED,
          DATECHANGED
        )values(
          @ID,
          @BASEAMOUNTTOTAX,
          @BASEVATAMOUNT,
          @BASECURRENCYID,
          @BASEEXCHANGERATEID,
          @TRANSACTIONAMOUNTTOTAX,
          @TRANSACTIONVATAMOUNT,
          @TRANSACTIONCURRENCYID,
          @ORGANIZATIONAMOUNTTOTAX,
          @ORGANIZATIONVATAMOUNT,
          @ORGANIZATIONEXCHANGERATEID,
          @VATTAXRATEID,
          @CHANGEAGENTID,
          @CHANGEAGENTID,
          @CURRENTDATE,
          @CURRENTDATE
        )
      end
    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, CUSTOMIDENTIFIER,
                                BASECURRENCYID, ORGANIZATIONAMOUNT, ORGANIZATIONEXCHANGERATEID,
                                TRANSACTIONAMOUNT, TRANSACTIONCURRENCYID, BASEEXCHANGERATEID,
                                ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
      values
                                (@ID, @CONSTITUENTID, @DATE, @BASEAMOUNT, 2, @AMOUNT,
                                @FINDERNUMBER, @SOURCECODE, @APPEALID, @GIVENANONYMOUSLY, @DONOTACKNOWLEDGE,
                                @BATCHNUMBER, @POSTDATE, @DONOTPOST, @BENEFITSWAIVED, @MAILINGID,
                                @CHANNELCODEID, coalesce(rtrim(ltrim(@REVENUELOOKUPID)),''),
                                @BASECURRENCYID, @ORGANIZATIONAMOUNT, @ORGANIZATIONEXCHANGERATEID,
                                @AMOUNT, @TRANSACTIONCURRENCYID, @BASEEXCHANGERATEID,
                                @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,case when @PAYMENTMETHODCODE = 98 then 2 else @PAYMENTMETHODCODE end, @BASEAMOUNT,@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,
          @TRANSACTIONCURRENCYID = @TRANSACTIONCURRENCYID,
          @BASECURRENCYID = @BASECURRENCYID,
          @BASEEXCHANGERATEID = @BASEEXCHANGERATEID,
          @ORGANIZATIONEXCHANGERATEID = @ORGANIZATIONEXCHANGERATEID

      exec dbo.USP_REVENUECATEGORY_UPDATESPLITS @REVENUEID = @ID,
          @CATEGORYCODEID = @CATEGORYCODEID, 
          @CHANGEAGENTID = @CHANGEAGENTID,
          @CURRENTDATE = @CURRENTDATE

      -- 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, SCHEDULESEEDDATE, ENDDATE, FREQUENCYCODE, NEXTTRANSACTIONDATE, CREDITCARDID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
        values (@ID, @SENDPLEDGEREMINDER, @INSTALLMENTSTARTDATE, @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, @BASEAMOUNT, @NEXTTRANSACTIONDATE, @BASECURRENCYID, @ORGANIZATIONAMOUNT, @ORGANIZATIONEXCHANGERATEID, @AMOUNT, @TRANSACTIONCURRENCYID, @BASEEXCHANGERATEID, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE);

      -- add any additional installments to get us current

      exec dbo.USP_RECURRINGGIFT_ADDMISSINGINSTALLMENTS
        @ID = @ID,
        @CHANGEAGENTID = @CHANGEAGENTID,
        @CREATIONDATE = @CURRENTDATE,
        @AMOUNT = @BASEAMOUNT,
        @BASECURRENCYID = @BASECURRENCYID,
        @ORGANIZATIONAMOUNT = @ORGANIZATIONAMOUNT,
        @ORGANIZATIONEXCHANGERATEID = @ORGANIZATIONEXCHANGERATEID,
        @TRANSACTIONAMOUNT = @AMOUNT,
        @TRANSACTIONCURRENCYID = @TRANSACTIONCURRENCYID,
        @BASEEXCHANGERATEID = @BASEEXCHANGERATEID;

      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

          if dbo.UFN_PAPERLESSMANDATES_DAYSADVANCEDNOTICE_FORREVENUE(@ID) = -1
          begin
            raiserror('BBERR_MULTIPLE_PAPERLESSMANDATECONFIGS', 13, 1);
            return 1;
          end

          set @SENDNEWINSTRUCTION = 1;
          set @NEWINSTRUCTIONTOSEND = 1;
        end

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

      if @PAYMENTMETHODCODE = 10 -- Other

      begin
        insert into dbo.OTHERPAYMENTMETHODDETAIL (ID, REFERENCEDATE, REFERENCENUMBER, OTHERPAYMENTMETHODCODEID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
          select ID, @REFERENCEDATE, @REFERENCENUMBER, @OTHERPAYMENTMETHODCODEID, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
        from dbo.REVENUEPAYMENTMETHOD 
        where REVENUEID = @ID; 
      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 @PAYMENTMETHODCODE = 98 -- Credit card - last 4 digits

      begin
        set @CREDITCARDNUMBER = coalesce(@PARTIALCREDITCARDNUMBER, '');

        insert into dbo.CREDITCARDPAYMENTMETHODDETAIL (ID, CARDHOLDERNAME, CREDITCARDPARTIALNUMBER, CREDITTYPECODEID, EXPIRESON, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
          select ID, @CARDHOLDERNAME, @CREDITCARDNUMBER, @CREDITTYPECODEID, @EXPIRESON, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
        from dbo.REVENUEPAYMENTMETHOD 
        where REVENUEID = @ID; 
      end

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

      -- Recognition credits should be created even when user is anonymous but has defined explicit recognitions.

      if not @RECOGNITIONS is null
        exec dbo.USP_REVENUEBATCH_ADDRECOGNITIONS @ID, @RECOGNITIONS, @CHANGEAGENTID, @CURRENTDATE;

      -- create payment for recurring gift (uses same code path as pledge, just changed applicationcode)

      if not @PAYMENTFORPLEDGEAMOUNT = 0
      begin

        -- Need to run the LINKTOREVENUE SP before attempting to add a payment or USP_PAYMENT_ADD can't check for mismatching account systems

        if dbo.UFN_VALID_BASICGL_INSTALLED() = 1
        begin
          if @PDACCOUNTSYSTEMID is null
            set @PDACCOUNTSYSTEMID = dbo.UFN_PDACCOUNTSYSTEM_GETDEFAULTSYSTEMIDSFORUSER(@CURRENTAPPUSERID)

          -- Link revenue to Account System

          exec dbo.USP_PDACCOUNTSYSTEM_LINKTOREVENUE @ID, @PDACCOUNTSYSTEMID, @CURRENTDATE, @CHANGEAGENTID;
        end

        declare @PAYMENTFORRG_REVSTREAMS xml;
        set @PAYMENTFORRG_REVSTREAMS = 
          (
            select
              @ID as 'APPLICATIONID',
              @PAYMENTFORPLEDGEAMOUNT as 'APPLIED',
              3 as 'APPLICATIONCODE'
            for xml raw('ITEM'), type, elements, root('REVENUESTREAMS'), binary base64
          );

        --If the RG to be paid uses a payment method of Credit card - 4 digits (98) then add the payment with Credit Card method, since the 4 digits is not a supported method on payments

        declare @ALTPAYMENTMETHODCODE tinyint;
        set @ALTPAYMENTMETHODCODE = case when @PAYMENTMETHODCODE = 98 then 2 else @PAYMENTMETHODCODE end;

        if @ALTPAYMENTMETHODCODE = 9 -- None

          set @ALTPAYMENTMETHODCODE = 1; -- Check


        declare @PAYMENTFORRG_REVENUEID uniqueidentifier;
        exec dbo.USP_PAYMENT_ADD 
            @ID = @PAYMENTFORRG_REVENUEID output, 
            @CHANGEAGENTID = @CHANGEAGENTID, 
            @CURRENTDATE = @CURRENTDATE, 
            @CONSTITUENTID = @CONSTITUENTID, 
            @DATE = @DATE, 
            @AMOUNT = @PAYMENTFORPLEDGEAMOUNT,
            @PAYMENTMETHODCODE = @ALTPAYMENTMETHODCODE,
            @CARDHOLDERNAME = @CARDHOLDERNAME,
            @CREDITCARDNUMBER =  @CREDITCARDNUMBER,
            @CREDITTYPECODEID = @CREDITTYPECODEID,
            @AUTHORIZATIONCODE = @AUTHORIZATIONCODE,
            @EXPIRESON = @EXPIRESON,
            @REFERENCENUMBER = @REFERENCENUMBER,
            @REFERENCEDATE = @REFERENCEDATE,
            @CONSTITUENTACCOUNTID = @CONSTITUENTACCOUNTID,
            @DIRECTDEBITRESULTCODE = @DIRECTDEBITRESULTCODE,
            @POSTDATE = @DATE,
            @REVENUESTREAMS = @PAYMENTFORRG_REVSTREAMS,
            @FINDERNUMBER = @FINDERNUMBER,
            @SOURCECODE = @SOURCECODE,
            @BATCHNUMBER = @BATCHNUMBER,
            @OTHERPAYMENTMETHODCODEID = @OTHERPAYMENTMETHODCODEID,
            @RECEIPTAMOUNT = @PAYMENTFORPLEDGEAMOUNT,
            @PDACCOUNTSYSTEMID = @PDACCOUNTSYSTEMID,
            @TRANSACTIONCURRENCYID = @TRANSACTIONCURRENCYID,
            @BASECURRENCYID = @BASECURRENCYID,
            @BASEEXCHANGERATEID = @BASEEXCHANGERATEID,
            @EXCHANGERATE = @EXCHANGERATE,
            @CURRENTAPPUSERID = @CURRENTAPPUSERID,
            @GIVENANONYMOUSLY = @GIVENANONYMOUSLY,
            @TRANSACTIONID = @TRANSACTIONID,
            @MERCHANTACCOUNTID = @MERCHANTACCOUNTID,
            @VENDORID = @VENDORID;
      end

      -- Note that @TOTALBENEFITS was run through its CONVERTAMOUNTSINXML SP earlier on in the commit.

      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
      begin
        set @TRANSACTIONVATAMOUNT = @VATAMOUNT;
        set @TRANSACTIONAMOUNTTOTAX = @AMOUNTFORVAT;

        exec dbo.USP_CURRENCY_GETCURRENCYVALUES @TRANSACTIONVATAMOUNT,
                                                @CURRENTDATE,
                                                @BASECURRENCYID,
                                                @BASEEXCHANGERATEID,
                                                @TRANSACTIONCURRENCYID,
                                                @BASEVATAMOUNT output,
                                                @ORGANIZATIONCURRENCYID,
                                                @ORGANIZATIONVATAMOUNT output, 
                                                @ORGANIZATIONEXCHANGERATEID, 
                                                0;

        exec dbo.USP_CURRENCY_GETCURRENCYVALUES @TRANSACTIONAMOUNTTOTAX,
                                                @CURRENTDATE,
                                                @BASECURRENCYID,
                                                @BASEEXCHANGERATEID,
                                                @TRANSACTIONCURRENCYID,
                                                @BASEAMOUNTTOTAX output,
                                                @ORGANIZATIONCURRENCYID,
                                                @ORGANIZATIONAMOUNTTOTAX output, 
                                                @ORGANIZATIONEXCHANGERATEID, 
                                                0;

        insert into dbo.REVENUEVAT(
          ID,
          AMOUNTTOTAX,
          VATAMOUNT,
          BASECURRENCYID,
          BASEEXCHANGERATEID,
          TRANSACTIONAMOUNTTOTAX,
          TRANSACTIONVATAMOUNT,
          TRANSACTIONCURRENCYID,
          ORGANIZATIONAMOUNTTOTAX,
          ORGANIZATIONVATAMOUNT,
          ORGANIZATIONEXCHANGERATEID,
          VATTAXRATEID,
          ADDEDBYID,
          CHANGEDBYID,
          DATEADDED,
          DATECHANGED
        )values(
          @ID,
          @BASEAMOUNTTOTAX,
          @BASEVATAMOUNT,
          @BASECURRENCYID,
          @BASEEXCHANGERATEID,
          @TRANSACTIONAMOUNTTOTAX,
          @TRANSACTIONVATAMOUNT,
          @TRANSACTIONCURRENCYID,
          @ORGANIZATIONAMOUNTTOTAX,
          @ORGANIZATIONVATAMOUNT,
          @ORGANIZATIONEXCHANGERATEID,
          @VATTAXRATEID,
          @CHANGEAGENTID,
          @CHANGEAGENTID,
          @CURRENTDATE,
          @CURRENTDATE
        )
      end

      --create a row in the RecurringGiftAmendment table for the activity timeline

      insert into dbo.RECURRINGGIFTAMENDMENT(ID, FINANCIALTRANSACTIONID, AMENDMENTTYPECODE, DATE, STATUSCODE,
            TRANSACTIONAMOUNT, TRANSACTIONCURRENCYID, BASEAMOUNT, BASECURRENCYID, BASEEXCHANGERATEID,
            ORGANIZATIONAMOUNT,  ORGANIZATIONEXCHANGERATEID, FREQUENCYCODE,
            DESIGNATIONS, SOURCECODE, FINDERNUMBER, APPEALID, MAILINGID, CHANNELCODEID, PAYMENTMETHODCODE, CREDITTYPECODEID,
            CREDITCARDPARTIALNUMBER, EXPIRESON, CARDHOLDERNAME, CONSTITUENTACCOUNTID, REFERENCEDATE, 
            REFERENCENUMBER, CONSTITUENTID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, OTHERPAYMENTMETHODCODEID,
            DDISOURCECODEID, DDISOURCEDATE, SENDPMINSTRUCTION, PMINSTRUCTIONTOSENDCODE, SEPAMANDATEID, STANDINGORDERSETUP, STANDINGORDERSETUPDATE, USESYSTEMGENERATEDREFERENCENUMBER)

      values(newid(), @ID, 0, @CURRENTDATE, 0, @AMOUNT, @TRANSACTIONCURRENCYID, @BASEAMOUNT, @BASECURRENCYID, @BASEEXCHANGERATEID, @ORGANIZATIONAMOUNT,
           @ORGANIZATIONEXCHANGERATEID, @INSTALLMENTFREQUENCYCODE, @SPLITS, @SOURCECODE, @FINDERNUMBER, @APPEALID, @MAILINGID, @CHANNELCODEID, @PAYMENTMETHODCODE, @CREDITTYPECODEID,
           isnull(@CREDITCARDNUMBER, ''), isnull(@EXPIRESON, '00000000'), isnull(@CARDHOLDERNAME, ''),@CONSTITUENTACCOUNTID, @REFERENCEDATE, 
           case when @PAYMENTMETHODCODE = 11 then case when @GENERATEREFERENCENUMBER = 1 then dbo.UFN_STANDINGORDER_REFERENCENUMBER_FORDISPLAY(@ID) else @STANDINGORDERREFERENCENUMBER end else @REFERENCENUMBER end,
           @CONSTITUENTID, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE, @OTHERPAYMENTMETHODCODEID, @DDISOURCECODEID, @DDISOURCEDATE, isnull(@SENDNEWINSTRUCTION,0), isnull(@NEWINSTRUCTIONTOSEND,0),
           @SEPAMANDATEID, isnull(@STANDINGORDERSETUP,0), @STANDINGORDERSETUPDATE, case when @PAYMENTMETHODCODE = 11 then @GENERATEREFERENCENUMBER else 0 end)

    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;

    if @LOCKBOXID is not null
    begin
      insert into dbo.REVENUELOCKBOX
      (
        ID,
        LOCKBOXID,
        BATCHNUMBER,
        BATCHSEQUENCE,
        ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
      )
      values
      (
        @ID,
        @LOCKBOXID,
        @LOCKBOXBATCHNUMBER,
        @LOCKBOXBATCHSEQUENCE,
        @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
      )
    end

    --Create BBIS transaction record

    if @BBNCTRANID > 0
    begin
      insert into dbo.REVENUEBBNC
      (
        ID,
        NETCOMMUNITYTRANSACTIONID,
        NETCOMMUNITYPAGENAME,
        NETCOMMUNITYPAGEID,
        ADDEDBYID, 
        CHANGEDBYID, 
        DATEADDED, 
        DATECHANGED,
        EMAILID,
        EMAILSUBJECT,
        EMAILNAME

      )
      values
      (
        @ID,
        @BBNCTRANID,
        @BBNCORIGINPAGENAME,
        @BBNCORIGINPAGEID,
        @CHANGEAGENTID, 
        @CHANGEAGENTID, 
        @CURRENTDATE, 
        @CURRENTDATE,
        @EMAILID,
        @EMAILSUBJECT,
        @EMAILNAME
      )

      --Mark donation transaction as processed in BBIS

      exec dbo.spTransactions_MarkDonationAsProcessed @BBNCTRANID;

      --The revenue receipt is traditionally generated by the receipting process, but BBIS transactions can be eReceipted.

      --DONOTRECEIPT will override the BBIS setting MARKRECEIPTED.

      if @MARKGIFTASRECEIPTED = 1 and @DONOTRECEIPT <> 1
      begin
        --Discard @RECEIPTNUMBER parameter value, as it is a character data type and could be holding a non-integer value.

        select
          @RECEIPTNUMBER = RECEIPTNUMBER 
        from 
          dbo.BATCHREVENUE
        where 
          BATCHREVENUE.ID = @BATCHROWID;

        if len(@RECEIPTSTACKSHORTNAME) > 10
        begin
          raiserror('BBERR_RECEIPTSTACKSHORTNAME_LENGTH', 13, 1);
          return 1;
        end

        insert into dbo.REVENUERECEIPT
        ( 
          ID, 
          REVENUEID, 
          RECEIPTNUMBER, 
          RECEIPTDATE, 
          ADDEDBYID, 
          CHANGEDBYID, 
          DATEADDED, 
          DATECHANGED, 
          RECEIPTSTACKSHORTNAME
        )
        values
        (
          newid(), 
          @ID, 
          coalesce(@RECEIPTNUMBER, 0), 
          @DATE, 
          @CHANGEAGENTID, 
          @CHANGEAGENTID, 
          @CURRENTDATE, 
          @CURRENTDATE, 
          @RECEIPTSTACKSHORTNAME
        );
      end
    end
  end 

  exec dbo.USP_REVENUEBATCH_APPLYBUSINESSUNITS @ID, @APPLICATIONBUSINESSUNITS, @TYPECODE, @CHANGEAGENTID, @REVENUESPLITSPONSORSHIPID, @REVENUESTREAMS, @ADDITIONALAPPLICATIONSSTREAM;

  update dbo.FINANCIALTRANSACTIONLINEITEM set
    BATCHID = @BATCHID
    ,CHANGEDBYID = @CHANGEAGENTID
    ,DATECHANGED = @CURRENTDATE
  where FINANCIALTRANSACTIONID = @ID;

  -- clear the BATCHID in the CC/EFT log table, so if the batch is later deleted it won't clear any existing CC/EFT events.

  update dbo.RECURRINGGIFTINSTALLMENTEVENT 
    set BATCHID = NULL,
    CHANGEDBYID = @CHANGEAGENTID,
    DATECHANGED = @CURRENTDATE
  from RECURRINGGIFTINSTALLMENTEVENT as RGIE
  inner join RECURRINGGIFTINSTALLMENT as RGI on RGI.ID = RGIE.RECURRINGGIFTINSTALLMENTID
  where RGI.REVENUEID = @ID
  and RGIE.BATCHID = @BATCHID

  if @SOLICITCODES is not null and @VALIDATEONLY = 0
  begin
    begin try
      declare @SOLICITCODESTABLE table
      (
        [CONSTITUENTSOLICITCODEID] [uniqueidentifier] NOT NULL,
        [CONSTITUENTID] [uniqueidentifier] NOT NULL,
        [SOLICITCODEID] [uniqueidentifier] NOT NULL,
        [SEQUENCE] [int] NOT NULL,
        [STARTDATE] [datetime] NULL,
        [ENDDATE] [datetime] NULL,
        [COMMENTS] [nvarchar](100) NOT NULL,
        [CONSENTPREFERENCECODE] [tinyint] NOT NULL,
        [SOURCECODEID] [uniqueidentifier] NULL,
        [SOURCEFILEPATH] [nvarchar](260) NOT NULL,
        [PRIVACYPOLICYFILEPATH] [nvarchar](260) NOT NULL,
        [SUPPORTINGINFORMATION] [nvarchar](max) NOT NULL,
        [CONSENTSTATEMENT] [nvarchar](max) NOT NULL
      );

      insert into @SOLICITCODESTABLE
      (
        CONSTITUENTSOLICITCODEID,
        CONSTITUENTID,
        SOLICITCODEID,
        STARTDATE,
        ENDDATE,
        COMMENTS,
        SEQUENCE,
        CONSENTPREFERENCECODE,
        SOURCECODEID,
        SOURCEFILEPATH,
        PRIVACYPOLICYFILEPATH,
        SUPPORTINGINFORMATION,
        CONSENTSTATEMENT
      )
      select
        isnull(CONSTITUENTSOLICITCODEID, newid()) CONSTITUENTSOLICITCODEID,
        @CONSTITUENTID,
        SOLICITCODEID,
        STARTDATE,
        ENDDATE,
        COMMENTS,
        SEQUENCE,
        CONSENTPREFERENCECODE,
        SOURCECODEID,
        SOURCEFILEPATH,
        PRIVACYPOLICYFILEPATH,
        SUPPORTINGINFORMATION,
        CONSENTSTATEMENT
      from dbo.UFN_BATCHREVENUE_GETCONSTITUENTSOLICITCODES_FROMITEMLISTXML(@SOLICITCODES);

      update dbo.CONSTITUENTSOLICITCODE set
        CONSTITUENTSOLICITCODE.CONSTITUENTID = SOLICITCODES.CONSTITUENTID,
        CONSTITUENTSOLICITCODE.SOLICITCODEID = SOLICITCODES.SOLICITCODEID,
        CONSTITUENTSOLICITCODE.STARTDATE = SOLICITCODES.STARTDATE,
        CONSTITUENTSOLICITCODE.ENDDATE = SOLICITCODES.ENDDATE,
        CONSTITUENTSOLICITCODE.COMMENTS = SOLICITCODES.COMMENTS,
        CONSTITUENTSOLICITCODE.SEQUENCE = SOLICITCODES.SEQUENCE,
        CONSTITUENTSOLICITCODE.CONSENTPREFERENCECODE = SOLICITCODES.CONSENTPREFERENCECODE,
        CONSTITUENTSOLICITCODE.SOURCECODEID = SOLICITCODES.SOURCECODEID,
        CONSTITUENTSOLICITCODE.SOURCEFILEPATH = SOLICITCODES.SOURCEFILEPATH,
        CONSTITUENTSOLICITCODE.PRIVACYPOLICYFILEPATH = SOLICITCODES.PRIVACYPOLICYFILEPATH,
        CONSTITUENTSOLICITCODE.SUPPORTINGINFORMATION = SOLICITCODES.SUPPORTINGINFORMATION,
        CONSTITUENTSOLICITCODE.CONSENTSTATEMENT = SOLICITCODES.CONSENTSTATEMENT,
        CONSTITUENTSOLICITCODE.CHANGEDBYID = @CHANGEAGENTID,
        CONSTITUENTSOLICITCODE.DATECHANGED = @CURRENTDATE
      from @SOLICITCODESTABLE SOLICITCODES
      where CONSTITUENTSOLICITCODE.ID = SOLICITCODES.CONSTITUENTSOLICITCODEID;

      -- Mark associated mail preferences as Do not send when the ended solicit code is no longer active

      update MAILPREFERENCE set
        SENDMAIL = 0,
        USESEASONALADDRESS = 0,
        USEPRIMARYADDRESS = 0,
        USEPRIMARYEMAIL = 0,
        CHANGEDBYID = @CHANGEAGENTID,
        DATECHANGED = @CURRENTDATE
      from @SOLICITCODESTABLE SOLICITCODES
        inner join dbo.MAILPREFERENCE on SOLICITCODES.CONSTITUENTSOLICITCODEID = MAILPREFERENCE.CONSTITUENTSOLICITCODEID
      where SOLICITCODES.ENDDATE < convert(date, @CURRENTDATE);

      -- prune updates from table variable

      delete SOLICITCODES 
      from @SOLICITCODESTABLE SOLICITCODES 
      join dbo.BATCHREVENUECONSTITUENTSOLICITCODE BSC on BSC.CONSTITUENTSOLICITCODEID = SOLICITCODES.CONSTITUENTSOLICITCODEID;

      -- do inserts

      insert into CONSTITUENTSOLICITCODE
      (
        ID,
        CONSTITUENTID,
        SOLICITCODEID,
        STARTDATE,
        ENDDATE,
        COMMENTS,
        SEQUENCE,
        CONSENTPREFERENCECODE,
        SOURCECODEID,
        SOURCEFILEPATH,
        PRIVACYPOLICYFILEPATH,
        SUPPORTINGINFORMATION,
        CONSENTSTATEMENT,
        ADDEDBYID,
        CHANGEDBYID,
        DATEADDED,
        DATECHANGED
      )
      select
        CONSTITUENTSOLICITCODEID,
        @CONSTITUENTID,
        SOLICITCODEID,
        STARTDATE,
        ENDDATE,
        COMMENTS,
        SEQUENCE,
        CONSENTPREFERENCECODE,
        SOURCECODEID,
        SOURCEFILEPATH,
        PRIVACYPOLICYFILEPATH,
        SUPPORTINGINFORMATION,
        CONSENTSTATEMENT,
        @CHANGEAGENTID, 
        @CHANGEAGENTID, 
        @CURRENTDATE, 
        @CURRENTDATE
      from @SOLICITCODESTABLE;
    end try
    begin catch
        exec.dbo.USP_RAISE_ERROR
        return 1
    end catch
  end

  end try

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

  return 0;