USP_DATAFORMTEMPLATE_ADD_2_REVENUEBATCHROW

CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_2_REVENUEBATCHROW
(
  @ID uniqueidentifier = null output,
  @BATCHID uniqueidentifier,
  @CHANGEAGENTID uniqueidentifier,
  @CONSTITUENTID uniqueidentifier = null,
  @TYPECODE tinyint,
  @DATE datetime,
  @AMOUNT money,
  @PAYMENTMETHODCODE tinyint = 0,
  @DONOTACKNOWLEDGE bit = 0,
  @CHECKDATE dbo.UDT_FUZZYDATE = '00000000',
  @CHECKNUMBER nvarchar(20) = '',
  @REFERENCEDATE dbo.UDT_FUZZYDATE = '00000000',
  @REFERENCENUMBER nvarchar(20) = '',
  @CARDHOLDERNAME nvarchar(255) = '',
  @CREDITCARDNUMBER nvarchar(20) = '',
  @CREDITTYPECODEID uniqueidentifier = null,
  @AUTHORIZATIONCODE nvarchar(20) = '',
  @EXPIRESON dbo.UDT_FUZZYDATE = '00000000',
  @ISSUER nvarchar(100) = '',
  @NUMBEROFUNITS decimal(20,3) = 0,
  @SYMBOL nvarchar(25) = '',
  @MEDIANPRICE decimal(19,4) = 0,
  @PROPERTYSUBTYPECODEID uniqueidentifier = null,
  @GIFTINKINDSUBTYPECODEID uniqueidentifier = null,
  @RECEIPTAMOUNT money = 0,
  @DONOTRECEIPT bit = 0,
  @CONSTITUENTACCOUNTID uniqueidentifier = null,
  @SPLITS xml = null,
  @SINGLEDESIGNATIONID uniqueidentifier = null,
  @REVENUESTREAMS xml = null,
  @APPLYTOSHOWNFORCONSTITUENTID uniqueidentifier= null,
  @SEQUENCE int,
  @INSTALLMENTFREQUENCYCODE tinyint = 5,
  @INSTALLMENTSTARTDATE datetime = null,
  @INSTALLMENTENDDATE datetime = null,
  @NUMBEROFINSTALLMENTS int = 1,
  @SOLICITORS xml = null,
  @BENEFITS xml = null,
  @FINDERNUMBER bigint = null,
  @SOURCECODE nvarchar(60) = '',
  @APPEALID uniqueidentifier = null,
  @FINDERNUMBERISVALID bit = 0,
  @USERMODIFIEDBENEFITS bit = 0,
  @BENEFITSWAIVED bit = 0,
  @POSTDATE datetime = null,
  @POSTSTATUSCODE tinyint = 1,
  @SENDPLEDGEREMINDER bit = 1,
  @SALEDATE datetime = null,
  @SALEAMOUNT money = 0,
  @BROKERFEE money = 0,
  @SALEPOSTSTATUSCODE tinyint = 1,
  @SALEPOSTDATE datetime = null,
  @NOTETITLE nvarchar(50) = '',
  @NOTEAUTHORID uniqueidentifier = null,
  @NOTEDATEENTERED datetime = null,
  @NOTETYPECODEID uniqueidentifier = null,
  @NOTETEXTNOTE nvarchar(max) = '',
  @GIVENANONYMOUSLY bit = 0,
  @GIVENANONYMOUSLYDEFAULTEDFORCONSTITUENTID uniqueidentifier = null,
  @USERMODIFIEDRECEIPTAMOUNT bit = 0,
  @PLEDGESUBTYPEID uniqueidentifier = null,
  @REJECTIONCODEID uniqueidentifier = null,
  @CONSTITUENTLOOKUPID uniqueidentifier = null,
  @MAILINGID uniqueidentifier = null,
  @CHANNELCODEID uniqueidentifier = null,
  @INSTALLMENTS xml = null,
  @PAYMENTFORPLEDGEAMOUNT money = 0,
  @RECOGNITIONS xml = null,
  @DIDRECOGNITIONSDEFAULT bit = 0,
  @TRIBUTES xml = null,
  @UNAPPLIEDMATCHINGGIFTSPLITS xml = null,
  @UNAPPLIEDMATCHINGGIFTAMOUNT money = 0,
  @RECEIPTTYPECODE tinyint = 0,
  @NEWCONSTITUENT xml = null,
  @OTHERPAYMENTMETHODCODEID uniqueidentifier = null,
  @LETTERCODEID uniqueidentifier = null,
  @ACKNOWLEDGEDATE datetime = null,
  @REFERENCE nvarchar(255) = null,
  @CATEGORYCODEID uniqueidentifier = null,
  @ACKNOWLEDGEEID uniqueidentifier = null,
  @APPLICATIONINFO nvarchar(60) = null,
  @OTHERTYPECODEID uniqueidentifier = null,
  @OPPORTUNITYID uniqueidentifier = null,
  @DIRECTDEBITRESULTCODE nvarchar(10) = '',
  @LOWPRICE decimal(19,4) = 0,
  @HIGHPRICE decimal(19,4) = 0,
  @NUMBEROFUNITSSOLD decimal(20,3) = 0,
  @USERMODIFIEDNUMBEROFUNITSSOLD bit = 0,
  @CREDITCARDTOKEN uniqueidentifier = null,
  @REJECTIONMESSAGE nvarchar(500) = '',
  @PARTIALCREDITCARDNUMBER nvarchar(4) = '',
  @STANDINGORDERCONSTITUENTACCOUNTID uniqueidentifier = null,
  @STANDINGORDERREFERENCEDATE dbo.UDT_FUZZYDATE = '00000000',
  @STANDINGORDERREFERENCENUMBER nvarchar(18) = '',
  @STANDINGORDERSETUP bit = 0,
  @STANDINGORDERSETUPDATE datetime = null,
  @TAXDECLARATIONS xml = null,
  @TRANSACTIONID uniqueidentifier = null,
  @ISTRANSIENTCARD bit = null,
  @DECLINESGIFTAID bit = null,
  @DDISOURCECODEID uniqueidentifier = null,
  @DDISOURCEDATE date = null,
  @ISCOVENANT bit = null,
  @AMOUNTFORVAT money = 0,
  @VATTAXRATEID uniqueidentifier = null,
  @VATAMOUNT money = 0,
  @ADDITIONALAPPLICATIONSSTREAM xml = null,
  @REVENUELOOKUPID nvarchar(100) = '',
  @APPLICATIONSOLICITORS xml = null,
  @APPLICATIONRECOGNITIONS xml = null,
  @CURRENTAPPUSERID uniqueidentifier = null,
  @MATCHINGGIFTS xml = null,
  @MGGENERATED bit = null,
  @MGALTERED bit = null,
  @PAYINGPENDINGREVENUEID uniqueidentifier = null,
  @GIFTINKINDITEMNAME nvarchar(100) = '',
  @GIFTINKINDDISPOSITIONCODE tinyint = 0,
  @GIFTINKINDNUMBEROFUNITS int = 0,
  @GIFTINKINDFAIRMARKETVALUE money = 0,
  @DIRECTDEBITISREJECTED bit = 0,
  @PERCENTAGEBENEFITS xml = null,
  @ISGIFTAIDSPONSORSHIP bit = null,
  @LOCKBOXID uniqueidentifier = null,
  @LOCKBOXBATCHNUMBER nvarchar(100) = '',
  @LOCKBOXBATCHSEQUENCE int = 0,
  @PDACCOUNTSYSTEMID uniqueidentifier = null,
  @APPLICATIONBUSINESSUNITS xml = null,
  @TRANSACTIONCURRENCYID uniqueidentifier = null,
  @BASECURRENCYID uniqueidentifier = null,
  @BASEEXCHANGERATEID uniqueidentifier = null,
  @EXCHANGERATE decimal(20,8) = null,
  @GENERATEREFERENCENUMBER bit = 1,
  @APPLYBYPERCENT bit = 0,
  @SOURCECODEIMPORT nvarchar(60) = '',
  @CURRENTBATCHROWID uniqueidentifier = '00000000-0000-0000-0000-000000000000',
  @MERCHANTACCOUNTID uniqueidentifier = null,
  @SETNULLBASEEXCHANGERATETOLATEST bit = 1,
  @APPEALIDFORIMPORT uniqueidentifier = null,
  @SALE_LOWPRICE decimal(19,4) = 0,
  @SALE_MEDIANPRICE decimal(19,4) = 0,
  @SALE_HIGHPRICE decimal(19,4) = 0,
  @CREDITCARDATTEMPTCOUNT tinyint = 0,
  @VENDORID nvarchar(50) = '',
  @CLIENTAPPLICENSEID nvarchar(100) = '',
  @BBNCTRANSACTIONPROCESSORID uniqueidentifier = null,
  @BBNCORIGINAPPEALID uniqueidentifier = null,
  @BBNCTRANID int = 0,
  @BBNCID int = 0,
  @BBNCORIGINPAGENAME nvarchar(100) = '',
  @BBNCORIGINPAGEID int = 0,
  @FINANCIALINSTITUTIONID uniqueidentifier = null,
  @ACCOUNTNUMBER nvarchar(50) = '',
  @ACCOUNTTYPE tinyint = null,
  @ACCOUNTNAME nvarchar(100) = '',
  @RECEIPTNUMBER nvarchar(30) = null,
  @RECEIPTSTACKSHORTNAME nvarchar(20) = '',
  @MARKGIFTASRECEIPTED bit = 0,
  @EMAILID int = 0,
  @EMAILSUBJECT nvarchar(510) = '',
  @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,
  @FIRSTPAYMENTPROCESSED nvarchar(10) = '',
  @PAYMENTFORPLEDGERECEIPTAMOUNT money = 0,
  @SEPAMANDATEID uniqueidentifier = null,
  @ADDSEPAMANDATE bit = 0,
  @SEPAMANDATECUSTOMIDENTIFIER nvarchar(35) = '',
  @SEPAMANDATESIGNATUREDATE date = null,
  @SEPAMANDATETYPECODE tinyint = 0,
  @FINANCIALINSTITUTIONNAME nvarchar(100) = '',
  @BANKINGSYSTEMID uniqueidentifier = null,
  @BRANCHNAME nvarchar(100) = '',
  @ROUTINGNUMBER nvarchar(9) = '',
  @SORTCODE nvarchar(6) = '',
  @BIC nvarchar(11) = '',
  @BANKCODE nvarchar(25) = '',
  @INSTALLMENTAMOUNT money = 0,
  @REQUIRECREDITCARDPROCESSING bit = 0,
  @NOTEHTMLNOTE nvarchar(max) = '',
  @IMPORT bit = 1,
  @SOLICITCODES  xml = null
) 
as
begin
  set nocount on;

  declare @CURRENTDATE datetime;

  declare
    @ErrorMessage nvarchar(1000),
    @InfoMsg nvarchar(100),
    @ErrorSeverity int,
    @ErrorState int;

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

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

  set @CURRENTDATE = GetDate();

  begin try
    -- Check batch status

    -- Note: The Infinity platform checks for this, but the BizOps endpoints do not. This change prevents adding/editing rows in committed/deleted batches.

    if (select STATUSCODE from dbo.BATCH where ID = @BATCHID) > 0
      raiserror('BBERR_BATCH_STATUSCODE_INVALIDFORADDEDIT', 13, 1);

    -- Check constituent security.

    if exists (select top 1 ID from REVENUEBATCHCONSTITUENTSECURITY where CONSTITUENTSECURITY = 1)
      and exists (select top 1 ID from CONSTITUENT where ID = @CONSTITUENTID) -- Don't check if new constituent

    begin -- site security is enabled

        if not ((dbo .UFN_APPUSER_ISSYSADMIN( @CURRENTAPPUSERID) = 1) or
      ((dbo. UFN_SECURITY_APPUSER_GRANTED_FORM_FORCONSTIT (@CURRENTAPPUSERID, '3E5B7B99-FB01-49D4-9020-C953006B7D0F' , @CONSTITUENTID) = 1 ) and -- Constituent group security

        exists ( select 1 from dbo.UFN_SITEID_MAPFROM_CONSTITUENTID( @CONSTITUENTID)
             where dbo.UFN_SECURITY_APPUSER_GRANTED_FORM_FORSITE(@CURRENTAPPUSERID , '3E5B7B99-FB01-49D4-9020-C953006B7D0F' , SITEID) = 1 ))) -- Site security

      begin
        raiserror ('BBERR_DB_RECORDSECURITY_PERMISSION_DENIED' , 13, 1)
      end   
    end

    if isnull(@SOURCECODE, '') = ''
      set @SOURCECODE = @SOURCECODEIMPORT;


    if @TYPECODE = 0 -- payment

    begin
      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 is null
          raiserror('BBERR_GIFTINKINDNUMBEROFUNITSREQUIRED', 13, 1)
        if @GIFTINKINDFAIRMARKETVALUE is null
          raiserror('BBERR_GIFTINKINDFAIRMARKETVALUEREQUIRED', 13, 1)
        if @GIFTINKINDNUMBEROFUNITS < 0
          raiserror('BBERR_GIFTINKINDNUMBEROFUNITSNEGATIVE', 13, 1)
        if @GIFTINKINDFAIRMARKETVALUE < 0
          raiserror('BBERR_GIFTINKINDFAIRMARKETVALUENEGATIVE', 13, 1)
      end
    end

    if @PAYMENTMETHODCODE = 11 --Standing order

    begin
      set @CONSTITUENTACCOUNTID = @STANDINGORDERCONSTITUENTACCOUNTID;
      --if @CONSTITUENTACCOUNTID is null

      --begin

        --raiserror('Standing order account must be specified for standing order payments', 13, 1)

      --end

      set @REFERENCEDATE = @STANDINGORDERREFERENCEDATE;

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

    --JamesWill CR269707-031107 2007/03/19 Set default values for any non-nullable fields that might come in as null

    if @DONOTACKNOWLEDGE is null
      set @DONOTACKNOWLEDGE = 0;
    if @CHECKDATE is null
      set @CHECKDATE = '00000000';
    if @CHECKNUMBER is null
      set @CHECKNUMBER = '';
    if @REFERENCEDATE is null
      set @REFERENCEDATE = '00000000';
    if @REFERENCENUMBER is null
      set @REFERENCENUMBER = '';
    if @CARDHOLDERNAME is null
      set @CARDHOLDERNAME = '';
    if @CREDITCARDNUMBER is null
      set @CREDITCARDNUMBER = '';
    if @AUTHORIZATIONCODE is null
      set @AUTHORIZATIONCODE = '';
    if @EXPIRESON is null
      set @EXPIRESON = '00000000';
    if @ISSUER is null
      set @ISSUER = '';
    if @NUMBEROFUNITS is null
      set @NUMBEROFUNITS = 0;
    if @NUMBEROFUNITSSOLD is null
      set @NUMBEROFUNITSSOLD = 0;
    if @NUMBEROFUNITSSOLD > 0 and @NUMBEROFUNITS <> @NUMBEROFUNITSSOLD
      set @USERMODIFIEDNUMBEROFUNITSSOLD = 1
    if @SYMBOL is null
      set @SYMBOL = '';
    if @MEDIANPRICE is null
      set @MEDIANPRICE = 0;
    if @LOWPRICE is null
      set @LOWPRICE = 0;
    if @HIGHPRICE is null
      set @HIGHPRICE = 0;
    if @RECEIPTAMOUNT is null
      set @RECEIPTAMOUNT = 0;
    if @ADDSEPAMANDATE is null
      set @ADDSEPAMANDATE = 0;
    if @TYPECODE <> 0
      set @LOCKBOXID = null;
    if @REQUIRECREDITCARDPROCESSING is null
      set @REQUIRECREDITCARDPROCESSING = 0;

    if not @LOCKBOXID is null
        if @LOCKBOXBATCHSEQUENCE is null or @LOCKBOXBATCHNUMBER is null
          raiserror('BBERR_LOCKBOXFIELDREQUIRED', 13, 1)

    if @LOCKBOXBATCHNUMBER is null or @LOCKBOXID is null
      set @LOCKBOXBATCHNUMBER = '';
    if @LOCKBOXBATCHSEQUENCE is null or @LOCKBOXID is null
      set @LOCKBOXBATCHSEQUENCE = 0;

    if @PAYMENTFORPLEDGEAMOUNT is null
      set @PAYMENTFORPLEDGEAMOUNT = 0;

    if @PAYMENTFORPLEDGERECEIPTAMOUNT is null
      set @PAYMENTFORPLEDGERECEIPTAMOUNT = 0;

    -- Default in account system unless we're dealing with a recurring gift.

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

    -- Clear account system if we are dealing with a recurring gift.

    if @TYPECODE = 3
      set @PDACCOUNTSYSTEMID = null;

    -- If revenue streams doesn't have any values, ensure it is set to null.

    -- If any of revenue streams' children are mapped during import, revenue streams will not be null even if

    -- there are no revenue stream entries.

    if (select count(*) from @REVENUESTREAMS.nodes('/REVENUESTREAMS/ITEM') T(c)) = 0
        set @REVENUESTREAMS = null;

    -- Get base currency from account system's currency set, if available; from user's default set otherwise.


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

    if @EXCHANGERATE is null
      set @EXCHANGERATE = 0;

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

    if @TRANSACTIONCURRENCYID <> @BASECURRENCYID
    begin
      if @BASEEXCHANGERATEID is null and (@SETNULLBASEEXCHANGERATETOLATEST is null or @SETNULLBASEEXCHANGERATETOLATEST = 1)
        set @BASEEXCHANGERATEID = dbo.UFN_CURRENCYEXCHANGERATE_GETLATEST(@TRANSACTIONCURRENCYID,@BASECURRENCYID,@DATE,1,null)
      else
      begin
        if @BASEEXCHANGERATEID <> '00000000-0000-0000-0000-000000000001'
        begin
          declare @RATEVALID bit;
          set @RATEVALID = 1;
          declare @RATESOURCE uniqueidentifier;

          select @RATESOURCE = SOURCECODEID
          from dbo.CURRENCYEXCHANGERATE
          where ID = @BASEEXCHANGERATEID
          and FROMCURRENCYID = @TRANSACTIONCURRENCYID
          and TOCURRENCYID = @BASECURRENCYID
          and TYPECODE in (0,1)
          and ASOFDATE <= dbo.UFN_DATE_GETLATESTTIME(@DATE)
          and (EXPIRATIONDATE is null or EXPIRATIONDATE >= dbo.UFN_TIMEZONEENTRY_GETSYSTEMDEFAULTDATEWITHTIMEOFFSET(@DATE,0));

          if @@ROWCOUNT = 0
            set @RATEVALID = 0;

          if @RATEVALID = 1
          begin
            declare @VALIDRATE uniqueidentifier;
            set @VALIDRATE = dbo.UFN_CURRENCYEXCHANGERATE_GETLATEST(@TRANSACTIONCURRENCYID,@BASECURRENCYID,@DATE,1,@RATESOURCE)

            if @VALIDRATE <> @BASEEXCHANGERATEID
              set @RATEVALID = 0;
          end

          if @RATEVALID = 0
          begin
            set @BASEEXCHANGERATEID = null;
            set @EXCHANGERATE = 0;

            raiserror('The specified exchange rate is not valid.', 13, 1)
          end
        end
      end
    end
    else
    begin
      set @BASEEXCHANGERATEID = null;
      set @EXCHANGERATE = 0;
    end

    declare @BASECURRENCYDECIMALDIGITS tinyint;
    declare @BASECURRENCYROUNDINGTYPECODE tinyint;

    select @BASECURRENCYDECIMALDIGITS = DECIMALDIGITS, @BASECURRENCYROUNDINGTYPECODE = ROUNDINGTYPECODE
    from dbo.CURRENCY where ID = @BASECURRENCYID;

    if @DONOTRECEIPT is null
      set @DONOTRECEIPT = 0;
    if @NUMBEROFINSTALLMENTS is null
      set @NUMBEROFINSTALLMENTS = 1;
    if @SOURCECODE is null
      set @SOURCECODE = '';
    if @FINDERNUMBERISVALID is null
      set @FINDERNUMBERISVALID = 0;
    if @USERMODIFIEDBENEFITS is null
      set @USERMODIFIEDBENEFITS = 0;
    if @BENEFITSWAIVED is null
      set @BENEFITSWAIVED = 0;
    if @SENDPLEDGEREMINDER is null
      set @SENDPLEDGEREMINDER = 1;
    if @SALEAMOUNT is null
      set @SALEAMOUNT = 0;
    if @BROKERFEE is null
      set @BROKERFEE = 0;
    if @NOTETITLE is null 
      set @NOTETITLE = '';
    if @NOTETEXTNOTE is null
      set @NOTETEXTNOTE = '';
    if @NOTEHTMLNOTE is null
      set @NOTEHTMLNOTE = '';
    if @GIVENANONYMOUSLY is null
      set @GIVENANONYMOUSLY = 0;
    if @USERMODIFIEDRECEIPTAMOUNT is null
      set @USERMODIFIEDRECEIPTAMOUNT = 0;
    if @DIDRECOGNITIONSDEFAULT is null
      set @DIDRECOGNITIONSDEFAULT = 0;
    if @REFERENCE is null
      set @REFERENCE = '';
    if @STANDINGORDERREFERENCEDATE is null
      set @REFERENCEDATE = '00000000';
    if @STANDINGORDERSETUP is null
      set @STANDINGORDERSETUP = 0;
    if @STANDINGORDERREFERENCENUMBER is null
      set @STANDINGORDERREFERENCENUMBER = '';
    if @AMOUNTFORVAT is null
      set @AMOUNTFORVAT = 0;
    if @VATAMOUNT is null
      set @VATAMOUNT = 0;
    if @DIRECTDEBITISREJECTED is null
      set @DIRECTDEBITISREJECTED = 0;
    if @GIFTINKINDITEMNAME is null
      set @GIFTINKINDITEMNAME = '';
    if @GIFTINKINDDISPOSITIONCODE is null
      set @GIFTINKINDDISPOSITIONCODE = 0;
    if @GIFTINKINDNUMBEROFUNITS is null
      set @GIFTINKINDNUMBEROFUNITS = 0;
    if @GIFTINKINDFAIRMARKETVALUE is null
      set @GIFTINKINDFAIRMARKETVALUE = 0;
    --if @AMOUNT < 0 

      -- raiserror('The amount cannot be negative.', 13, 1) 

    if @FINDERNUMBER is null
      set @FINDERNUMBER = 0;
    /* JamesWill CR267402-021907 INSTALLMENTFREQUENCYCODE cannot be null in the database, but it can be null in the defaults */
    if @INSTALLMENTFREQUENCYCODE is null
    begin
      -- For Recurring Gifts, default to Monthly.  For other types, use Single Installment.

      if @TYPECODE = 3
        set @INSTALLMENTFREQUENCYCODE = 3;
      else
        set @INSTALLMENTFREQUENCYCODE = 5;
    end
    /* JamesWill CR265838-020507 2007/03/07 */
    if @POSTSTATUSCODE is null
      set @POSTSTATUSCODE = 255;
    if @SALEPOSTSTATUSCODE is null
      set @SALEPOSTSTATUSCODE = 255;
    if @MGGENERATED is null
      set @MGGENERATED = 0;
    if @DECLINESGIFTAID is null
      set @DECLINESGIFTAID = 0;

    if @CREDITCARDATTEMPTCOUNT is null
      set @CREDITCARDATTEMPTCOUNT = 0;

    -- WI 167235 when importing matching gift claims set @MGALTERED = 1 so that the imported

    -- MG will be added by the batch.

    if exists (
        select
            1
        from
            @MATCHINGGIFTS.nodes('MATCHINGGIFTS/ITEM') as MATCHINGGIFTSXML ([MATCHINGGIFT])
        where
            MATCHINGGIFTSXML.[MATCHINGGIFT].value('MATCHINGCONSTITUENTID[1]', 'uniqueidentifier') is not null
            and
            coalesce(MATCHINGGIFTSXML.[MATCHINGGIFT].value('COULDNOTDEFAULTMATCHINGGIFT[1]', 'bit'),0) = 0
    )
        set @MGALTERED = 1;
    else
        if @MGALTERED is null
          set @MGALTERED = 0;

    if cast(@SPLITS as nvarchar(max)) = '' 
    begin
      set @SPLITS = null;
    end
    else
    begin
      if not exists (select 1 from @SPLITS.nodes('/SPLITS/ITEM') T(c))
        set @SPLITS = null;
    end
    if not @SPLITS is null
      set @SINGLEDESIGNATIONID = null;
    if @TYPECODE = 1 or @TYPECODE = 3 --Pledges and Recurring gifts do not receipt

      set @DONOTRECEIPT = 1;
    if @PAYMENTMETHODCODE is null
set @PAYMENTMETHODCODE = 255;
    if @RECEIPTTYPECODE is null
      set @RECEIPTTYPECODE = 255
    if @CONSTITUENTID is null 
      select @CONSTITUENTID = @CONSTITUENTLOOKUPID;

    -- Bug#254908 - When importing benefits set @USERMODIFIEDBENEFITS = 1 so that the they are not removed when constituent/amount etc is changed.

    if @BENEFITS.exist('BENEFITS/ITEM[BENEFITID]') = 1 or @PERCENTAGEBENEFITS.exist('PERCENTAGEBENEFITS/ITEM[BENEFITID]') = 1
    begin
      set @USERMODIFIEDBENEFITS = 1;
    end

    if @NEWCONSTITUENT.exist('NEWCONSTITUENT/ITEM') = 1 and not exists (select ID from dbo.CONSTITUENT where ID = @CONSTITUENTID)
      exec dbo.USP_REVENUEBATCH_ADDNEWCONSTITUENTFROMXML_1 @NEWCONSTITUENT, @CHANGEAGENTID, @CONSTITUENTID output;

    declare @MARKETINGCONSTITUENTID uniqueidentifier = @CONSTITUENTID;

    /* Lookup and set all possible marketing data (via output params) from the data that was specified. */
    declare @LOOKUPSINGLEDESIGNATION bit = (case when @SINGLEDESIGNATIONID is null and @SPLITS is null and @REVENUESTREAMS is null and @ADDITIONALAPPLICATIONSSTREAM is null then 1 else 0 end);
    exec dbo.[USP_REVENUEBATCH_GETMARKETINGDATA]
      @FINDERNUMBER = @FINDERNUMBER,
      @LOOKUPSINGLEDESIGNATION = @LOOKUPSINGLEDESIGNATION,
      @CHANGEAGENTID = @CHANGEAGENTID,
      @CURRENTAPPUSERID = @CURRENTAPPUSERID,
      @SOURCECODE = @SOURCECODE output,
      @MAILINGID = @MAILINGID output,
      @APPEALID = @APPEALID output,
      @CONSTITUENTID = @MARKETINGCONSTITUENTID output,
      @FINDERNUMBERISVALID = @FINDERNUMBERISVALID output,
      @SINGLEDESIGNATIONID = @SINGLEDESIGNATIONID output,
      @BATCHID = @BATCHID,
      @IMPORT = @IMPORT;

    if @BBNCTRANID = 0
      set @CONSTITUENTID = @MARKETINGCONSTITUENTID;
    else
      begin

        /* This is a BBIS transaction. Check to see if the first, last and email address match the constituent, if so assume it is
           an acquisition list constituent and overwrite the constituent ID. */
        declare @ISTEMPORARYCONSTITUENT bit = case when exists (select top 1 1 from dbo.[BATCHREVENUECONSTITUENTAPPEAL] where [CONSTITUENTID] = @MARKETINGCONSTITUENTID) then 1 else 0 end;

        if @ISTEMPORARYCONSTITUENT = 1 
          begin

            declare @CONSTFIRSTNAME nvarchar(255)= '';
            declare @CONSTLASTNAME nvarchar(255) = '';
            declare @CONSTEMAILADDRESS dbo.UDT_EMAILADDRESS = '';

            declare @FINDERFIRSTNAME nvarchar(255)= '';
            declare @FINDERLASTNAME nvarchar(255)= '';
            declare @FINDEREMAILADDRESS dbo.UDT_EMAILADDRESS = '';

            select top 1
              @CONSTFIRSTNAME = coalesce(T.c.value('(FIRSTNAME)[1]','nvarchar(50)'), ''),
              @CONSTLASTNAME = coalesce(T.c.value('(LASTNAME)[1]','nvarchar(100)'), ''),
              @CONSTEMAILADDRESS = coalesce(T.c.value('(EMAILADDRESS_EMAILADDRESS)[1]','dbo.UDT_EMAILADDRESS'), '')
            from @NEWCONSTITUENT.nodes('/NEWCONSTITUENT/ITEM') T(c);

            select
              @FINDERFIRSTNAME = [FIRSTNAME],
              @FINDERLASTNAME = [KEYNAME],
              @FINDEREMAILADDRESS = [EMAILADDRESS]
            from dbo.[BATCHREVENUECONSTITUENT]
            where [ID] = @MARKETINGCONSTITUENTID;

            /* WI374441 If we match the constituent to an acquisition list imported constituent we should take the temp constituent ID created 
               by the marketing function. If the constituent does not match then assume the gift was given by someone else and do not 
               overwrite the constituent ID. */
            if @FINDEREMAILADDRESS = @CONSTEMAILADDRESS and @FINDERLASTNAME = @CONSTLASTNAME and @FINDERFIRSTNAME = @CONSTFIRSTNAME
              set @CONSTITUENTID = @MARKETINGCONSTITUENTID;

          end

      end

    -- If constituent is still null, try to figure it out based on applications to commitments

    -- All underlying commitments must point to the same constituent to get a result

    if @CONSTITUENTID is null
    begin
      select @CONSTITUENTID = dbo.UFN_REVENUEBATCH_GETCONSTITUENTIDFROMAPPLICATIONS(@REVENUESTREAMS)
    end

    -- Use the partial card number if set.  This field is used for import or when using the payment method of Credit card - last 4 digits

    -- and doesn't have any CC processing code run for it.

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

    -- Validate that if one credit card field is entered, the required fields are set.

    -- Server code should generally have validated this already unless the code is being

    -- added through import.

    if @PAYMENTMETHODCODE = 2
    begin
      if len(coalesce(@CREDITCARDNUMBER, '')) > 4
      begin
        if coalesce(@CARDHOLDERNAME, '') = ''
          raiserror('BBERR_CARDHOLDERFIELDSREQUIRED', 13, 1)

        if coalesce(@EXPIRESON, '00000000') = '00000000'
          raiserror('BBERR_EXPIRESONFIELDSREQUIRED', 13, 1)
      end
    end

    declare @CREDITCARDID uniqueidentifier
    if @PAYMENTMETHODCODE = 2
    begin try
      exec dbo.USP_CREDITCARD_SAVE
        @ID = @CREDITCARDID output,
        @CREDITCARDTOKEN = @CREDITCARDTOKEN,
        @CARDHOLDERNAME = @CARDHOLDERNAME,
        @CREDITCARDPARTIALNUMBER = @CREDITCARDNUMBER,
        @CREDITTYPECODEID = @CREDITTYPECODEID,
        @EXPIRESON = @EXPIRESON,
        @CHANGEAGENTID = @CHANGEAGENTID,
        @CURRENTDATE = @CURRENTDATE,
        @ISTRANSIENT = @ISTRANSIENTCARD;
    end try
    begin catch
      if (@CREDITCARDTOKEN is null or len(coalesce(@CREDITCARDNUMBER, '')) < 4)
        raiserror('BBERR_CREDITCARDFIELDSREQUIRED', 13, 1);
      if coalesce(@EXPIRESON, '00000000') = '00000000'
        raiserror('BBERR_EXPIRESONFIELDSREQUIRED', 13, 1);
      if len(coalesce(@CARDHOLDERNAME, '')) = 0 
        raiserror('BBERR_CARDHOLDERFIELDSREQUIRED', 13, 1);
    end catch

    --Try getting account ID if this direct debit and CONSTITUENTACCOUNTID is not provied.

    if @PAYMENTMETHODCODE = 3 and @CONSTITUENTACCOUNTID is null
    begin
        exec dbo.USP_REVENUEBATCH_GETORCREATECONSTITUENTACCOUNT
            @CONSTITUENTACCOUNTID = @CONSTITUENTACCOUNTID output,
            @CONSTITUENTID = @CONSTITUENTID,
            @ACCOUNTNAME = @ACCOUNTNAME,
            @ACCOUNTNUMBER = @ACCOUNTNUMBER,
            @ACCOUNTTYPE = @ACCOUNTTYPE,
            @FINANCIALINSTITUTIONID = @FINANCIALINSTITUTIONID output,
            @FINANCIALINSTITUTIONNAME = @FINANCIALINSTITUTIONNAME,
            @BANKINGSYSTEMID = @BANKINGSYSTEMID,
            @BRANCHNAME = @BRANCHNAME,
            @ROUTINGNUMBER = @ROUTINGNUMBER,
            @SORTCODE = @SORTCODE,
            @BIC = @BIC,
            @BANKCODE = @BANKCODE,
            @CURRENTAPPUSERID = @CURRENTAPPUSERID,
            @CHANGEAGENTID = @CHANGEAGENTID,
            @BATCHID = @BATCHID
    end

    exec dbo.USP_REVENUEBATCH_SEPAMANDATE_VALIDATEORCREATE
      @SEPAMANDATEID output,
      @CHANGEAGENTID,
      @CURRENTDATE,
      @ADDSEPAMANDATE,
      @SEPAMANDATECUSTOMIDENTIFIER,
      @SEPAMANDATESIGNATUREDATE,
      @SEPAMANDATETYPECODE,
      @PAYMENTMETHODCODE,
      @CONSTITUENTACCOUNTID,
      @FINANCIALINSTITUTIONID,
      @TRANSACTIONCURRENCYID;

    -- If @APPEALIDFORIMPORT is passed in and the appeal field is null,

    -- default appeal-related fields

    if @APPEALIDFORIMPORT is not null and @APPEALID is null
    begin
      set @APPEALID = @APPEALIDFORIMPORT
      -- Default designation and memberships. Designation campaigns will be added later.

      if @SINGLEDESIGNATIONID is null and @SPLITS is null and @REVENUESTREAMS is null and @ADDITIONALAPPLICATIONSSTREAM is null
      begin
        -- If there is no designation, default one.

        select 
          @SINGLEDESIGNATIONID = APPEALDESIGNATION.DESIGNATIONID
        from 
          dbo.APPEALDESIGNATION
          inner join dbo.DESIGNATION on DESIGNATION.ID = APPEALDESIGNATION.DESIGNATIONID
        where 
          APPEALDESIGNATION.APPEALID = @APPEALID 
          and APPEALDESIGNATION.ISDEFAULT = 1

        -- Default memberships if the record is a payment and there is no application

        if @TYPECODE = 0 and (@APPLICATIONINFO is null or @APPLICATIONINFO = '')
        begin
          declare @MEMBERSHIPLEVELTERMID uniqueidentifier;
          select
            @MEMBERSHIPLEVELTERMID = MEMBERSHIPLEVELTERMID
          from dbo.UFN_APPEAL_GETMEMBERSHIPDEFAULTS(@APPEALID, @AMOUNT)

          if @MEMBERSHIPLEVELTERMID is not null and @CONSTITUENTID is not null and @DATE is not null
          begin
            declare @DEFAULTMEMBERSHIPFORMID nvarchar(100) = cast(@MEMBERSHIPLEVELTERMID as nvarchar(36)) + '|' + cast(@CONSTITUENTID as nvarchar(36)) + '|' + cast(@DATE as nvarchar(28))
            declare @DEFAULTMEMBERSHIPFORMDATALOADED bit;
            declare @DEFAULTMEMBERSHIPFORMEXPIRATIONDATE datetime;
            declare @DEFAULTMEMBERSHIPFORMAMOUNT money;
            declare @DEFAULTMEMBERSHIPFORMMEMBERSHIPPROGRAMID uniqueidentifier;
            declare @DEFAULTMEMBERSHIPFORMMEMBERSHIPLEVELID uniqueidentifier;
            declare @DEFAULTMEMBERSHIPFORMMEMBERSHIPLEVELTERMID uniqueidentifier;
            declare @DEFAULTMEMBERSHIPFORMMEMBERSHIPTYPECODEID uniqueidentifier;
            declare @DEFAULTMEMBERSHIPFORMNUMBEROFCHILDREN tinyint;
            declare @DEFAULTMEMBERSHIPFORMCOMMENTS nvarchar(1000);
            declare @DEFAULTMEMBERSHIPFORMMEMBERS xml;
            declare @DEFAULTMEMBERSHIPFORMISGIFT bit;
            declare @DEFAULTMEMBERSHIPFORMSENDRENEWALCODE tinyint;
            declare @DEFAULTMEMBERSHIPFORMGIVENBYID uniqueidentifier;

            exec dbo.USP_DATAFORMTEMPLATE_ADD_PRELOAD_BATCHREVENUEMEMBERSHIPAPPEALDEFAULT 
              @ID=@DEFAULTMEMBERSHIPFORMID,
              @DATALOADED=@DEFAULTMEMBERSHIPFORMDATALOADED output, 
              @EXPIRATIONDATE=@DEFAULTMEMBERSHIPFORMEXPIRATIONDATE output,
              @AMOUNT=@DEFAULTMEMBERSHIPFORMAMOUNT output,
              @MEMBERSHIPPROGRAMID=@DEFAULTMEMBERSHIPFORMMEMBERSHIPPROGRAMID output,
              @MEMBERSHIPLEVELID=@DEFAULTMEMBERSHIPFORMMEMBERSHIPLEVELID output,
              @MEMBERSHIPLEVELTERMID=@DEFAULTMEMBERSHIPFORMMEMBERSHIPLEVELTERMID output,
              @MEMBERSHIPTYPECODEID=@DEFAULTMEMBERSHIPFORMMEMBERSHIPTYPECODEID output,
              @NUMBEROFCHILDREN=@DEFAULTMEMBERSHIPFORMNUMBEROFCHILDREN output,
              @COMMENTS=@DEFAULTMEMBERSHIPFORMCOMMENTS output,
              @MEMBERS=@DEFAULTMEMBERSHIPFORMMEMBERS output,
              @ISGIFT=@DEFAULTMEMBERSHIPFORMISGIFT output,
              @SENDRENEWALCODE=@DEFAULTMEMBERSHIPFORMSENDRENEWALCODE output,
              @GIVENBYID=@DEFAULTMEMBERSHIPFORMGIVENBYID output

            if @DEFAULTMEMBERSHIPFORMDATALOADED = 1
              set @REVENUESTREAMS = (
                select
                  newid() APPLICATIONID,
                  @DEFAULTMEMBERSHIPFORMAMOUNT APPLIED,
                  @DEFAULTMEMBERSHIPFORMAMOUNT BALANCE,
                  @DEFAULTMEMBERSHIPFORMAMOUNT AMOUNTDUE,
                  cast(getdate() as DATE) DATEDUE,
                  5 TYPECODE,
                  (
                    select
                      @DEFAULTMEMBERSHIPFORMEXPIRATIONDATE EXPIRATIONDATE,
                      @DEFAULTMEMBERSHIPFORMAMOUNT AMOUNT,
                      @DEFAULTMEMBERSHIPFORMMEMBERSHIPPROGRAMID MEMBERSHIPPROGRAMID,
                      @DEFAULTMEMBERSHIPFORMMEMBERSHIPLEVELID MEMBERSHIPLEVELID,
                      @DEFAULTMEMBERSHIPFORMMEMBERSHIPLEVELTERMID MEMBERSHIPLEVELTERMID,
                      @DEFAULTMEMBERSHIPFORMMEMBERSHIPTYPECODEID MEMBERSHIPTYPECODEID,
          isnull(@DEFAULTMEMBERSHIPFORMNUMBEROFCHILDREN, 0) NUMBEROFCHILDREN,
                      isnull(@DEFAULTMEMBERSHIPFORMCOMMENTS, '') COMMENTS,
                      @DEFAULTMEMBERSHIPFORMMEMBERS,
                      isnull(@DEFAULTMEMBERSHIPFORMISGIFT, 0) ISGIFT, 
                      isnull(@DEFAULTMEMBERSHIPFORMSENDRENEWALCODE, 0) SENDRENEWALCODE,
                      @DEFAULTMEMBERSHIPFORMGIVENBYID GIVENBYID
                    for xml raw('ITEM'), type, elements, binary base64
                  ) MEMBERSHIPS
                for xml raw('ITEM'), type, elements, root('REVENUESTREAMS'), binary base64
              )
          end
        end
      end
    end

    --Default benefits

    if @USERMODIFIEDBENEFITS = 0 and @BENEFITSWAIVED = 0
    begin
      exec dbo.USP_REVENUEBATCH_GETDEFAULTBENEFITS @APPLICATIONINFO, @REVENUESTREAMS, @APPEALID, @AMOUNT, @DATE, @TRANSACTIONCURRENCYID, @BASECURRENCYID,
        @BENEFITS output, @PERCENTAGEBENEFITS output, @EXCHANGERATE, @BASEEXCHANGERATEID;
    end

    --Calculate receipt amount

    declare @CALCULATEDRECEIPTAMOUNT money = dbo.UFN_REVENUEBATCH_CALCULATERECEIPTAMOUNT
                                              (
                                                @AMOUNT, @RECEIPTAMOUNT, @APPLICATIONINFO, @DATE, @BENEFITSWAIVED, @BENEFITS, @PERCENTAGEBENEFITS, @REVENUESTREAMS,
                                                @TRANSACTIONCURRENCYID, @BASECURRENCYID, @BASEEXCHANGERATEID
                                              );

    --User modified receipt amount if it is not set to calculated amount

    if @RECEIPTAMOUNT > 0 and @RECEIPTAMOUNT <> @CALCULATEDRECEIPTAMOUNT
      set @USERMODIFIEDRECEIPTAMOUNT = 1;

    if @USERMODIFIEDRECEIPTAMOUNT = 0
      set @RECEIPTAMOUNT = @CALCULATEDRECEIPTAMOUNT;

    --Default installment amount or number of installments if not provided

    if @INSTALLMENTAMOUNT = 0
    begin
        --This should be defaulted to 1 if not provided, but its possible 0 has been passed in

        if @NUMBEROFINSTALLMENTS > 0 
        begin
          set @INSTALLMENTAMOUNT = ROUND(@AMOUNT / @NUMBEROFINSTALLMENTS, 2);

          if @AMOUNT > 0
          begin
            if @AMOUNT - (@INSTALLMENTAMOUNT * (@NUMBEROFINSTALLMENTS - 1)) < 0 
             set @INSTALLMENTAMOUNT = @INSTALLMENTAMOUNT - power(CAST(10.0 AS float), -2); 
          end
        end
    end
    else if @NUMBEROFINSTALLMENTS = 1 and @INSTALLMENTAMOUNT <> @AMOUNT
    begin
        if @INSTALLMENTAMOUNT > @AMOUNT
          set @INSTALLMENTAMOUNT = @AMOUNT
        else
          set @NUMBEROFINSTALLMENTS = CEILING(CAST(@AMOUNT AS float) / CAST(@INSTALLMENTAMOUNT AS float))
    end

    --Clear the credit card fields before inserting them into the batch table if the payment method is credit card (2)

    --They are saved above in that scenario and they only need to be saved to the BATCHREVENUE table if the payment method is 98 - Credit card - last 4 digits only

    if @PAYMENTMETHODCODE = 2
    begin
      set @CARDHOLDERNAME = '';
      set @CREDITTYPECODEID = null;
      set @CREDITCARDNUMBER = '';
      set @EXPIRESON = '00000000';
    end

    --WI#526372

    --If any of revenue streams' children are mapped during import, calculate @APPLICATIONINFO if everything is fine.

    declare @REVENUESTREAMSCOUNT int 
    declare @REVENUESTREAMSAPPLICATIONID varchar(36)
    declare @REVENUESTREAMSAPPLICATIONCOMMITMENTID varchar(36)
    declare @REVENUESTREAMSTYPECODE varchar(5)
    declare @REVENUESTREAMSAPPLIEDAMOUNT varchar(20)

    select @REVENUESTREAMSCOUNT = COUNT(1) from @REVENUESTREAMS.nodes('/REVENUESTREAMS/ITEM') T(c);    

    --Only set if there is exactly one revenue stream and no additional applications

    if (@APPLICATIONINFO is null or @APPLICATIONINFO = '') and @REVENUESTREAMSCOUNT = 1 and @ADDITIONALAPPLICATIONSSTREAM is null
    begin
      select 
        @REVENUESTREAMSAPPLICATIONID = cast(T.c.value('APPLICATIONID[1]', 'uniqueidentifier') as varchar(36)),
        @REVENUESTREAMSAPPLICATIONCOMMITMENTID = cast(T.c.value('APPLICATIONCOMMITMENTID[1]', 'uniqueidentifier') as varchar(36)),
        @REVENUESTREAMSTYPECODE = cast(T.c.value('TYPECODE[1]', 'tinyint') as varchar(5)),
        @REVENUESTREAMSAPPLIEDAMOUNT = cast(T.c.value('APPLIED[1]', 'money') as varchar(20))
      from 
        @REVENUESTREAMS.nodes('/REVENUESTREAMS/ITEM') T(c);
      if(@REVENUESTREAMSAPPLICATIONID = '' or @REVENUESTREAMSAPPLICATIONID is null)
        set @REVENUESTREAMSAPPLICATIONID = @REVENUESTREAMSAPPLICATIONCOMMITMENTID

      --If the payment typecode was provided, default it to the correct type using the revenue id

      if (@REVENUESTREAMSTYPECODE = '0' and @REVENUESTREAMSAPPLICATIONID <> '')
      begin
        select @REVENUESTREAMSTYPECODE = TYPECODE 
        from FINANCIALTRANSACTION 
        where ID = @REVENUESTREAMSAPPLICATIONID
      end

      --Bug#743568 - Get GIVENANONYMOUSLY from Applied Revenue(Import case and this wasn't set true in Import CSV)

      if (@IMPORT = 1 and @GIVENANONYMOUSLY <> 1)
      begin
        select         
          @GIVENANONYMOUSLY = GIVENANONYMOUSLY 
        from dbo.REVENUE_EXT
        where ID = @REVENUESTREAMSAPPLICATIONID;
      end

      --Do not set if this is a new commitment, application info needs to be null in this case

      if (@REVENUESTREAMSAPPLICATIONID <> '' and @REVENUESTREAMSTYPECODE <> '' and @REVENUESTREAMSAPPLIEDAMOUNT <> '' and exists(select 1 from FINANCIALTRANSACTION where ID = @REVENUESTREAMSAPPLICATIONID))
        begin
          select  @APPLICATIONINFO = 
            @REVENUESTREAMSAPPLICATIONID
            + ':' +
            case @REVENUESTREAMSTYPECODE 
              when '1' then '5'   -- Pledge

              when '2' then '4'   -- Recurring gift

              when '3' then '8'   -- Matching gift claim

              when '4' then '6'   -- Planned gift

              else
                @REVENUESTREAMSTYPECODE   -- 9. Grant award, 10. Donor challenge

            end
            + ':' + 
            @REVENUESTREAMSAPPLIEDAMOUNT
        end
    end

    insert into dbo.BATCHREVENUE
    (
      ID,BATCHID,CONSTITUENTID,DATE,PAYMENTMETHODCODE,DONOTACKNOWLEDGE,CHECKDATE,CHECKNUMBER,REFERENCEDATE,REFERENCENUMBER, 
      CREDITCARDID, AUTHORIZATIONCODE, CONSTITUENTACCOUNTID, AMOUNT, TYPECODE, RECEIPTAMOUNT, 
      DONOTRECEIPT, SEQUENCE, APPLYTOSHOWNFORCONSTITUENTID, INSTALLMENTFREQUENCYCODE, INSTALLMENTSTARTDATE, 
      INSTALLMENTENDDATE, NUMBEROFINSTALLMENTS, FINDERNUMBER, SOURCECODE, APPEALID, FINDERNUMBERISVALID, 
      USERMODIFIEDBENEFITS, BENEFITSWAIVED, POSTDATE, POSTSTATUSCODE, PROPERTYSUBTYPECODEID, GIFTINKINDSUBTYPECODEID, 
      SENDPLEDGEREMINDER, SALEDATE, SALEAMOUNT, BROKERFEE, SALEPOSTSTATUSCODE, SALEPOSTDATE, ISSUER, NUMBEROFUNITS, 
      SYMBOL, MEDIANPRICE, NOTETITLE, NOTEAUTHORID, NOTEDATEENTERED, NOTETYPECODEID, NOTETEXTNOTE, 
      GIVENANONYMOUSLY, GIVENANONYMOUSLYDEFAULTEDFORCONSTITUENTID, USERMODIFIEDRECEIPTAMOUNT, PLEDGESUBTYPEID,
      REJECTIONMESSAGE, MAILINGID, CHANNELCODEID, PAYMENTFORPLEDGEAMOUNT, RECEIPTTYPECODE, 
      ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED,OTHERPAYMENTMETHODCODEID, LETTERCODEID, ACKNOWLEDGEDATE, REFERENCE, 
      GLREVENUECATEGORYMAPPINGID, ACKNOWLEDGEEID, OTHERTYPECODEID,OPPORTUNITYID, DIRECTDEBITRESULTCODE, LOWPRICE, HIGHPRICE, NUMBEROFUNITSSOLD,
      USERMODIFIEDNUMBEROFUNITSSOLD, STANDINGORDERSETUP, STANDINGORDERSETUPDATE, TRANSACTIONID, DECLINESGIFTAID, DDISOURCECODEID, DDISOURCEDATE, ISCOVENANT,
      AMOUNTFORVAT, VATTAXRATEID, VATAMOUNT, APPLICATIONINFO, REVENUELOOKUPID, MGGENERATED, MGALTERED, PAYINGPENDINGREVENUEID,
      GIFTINKINDITEMNAME, GIFTINKINDDISPOSITIONCODE, GIFTINKINDNUMBEROFUNITS, GIFTINKINDFAIRMARKETVALUE, DIRECTDEBITISREJECTED, ISGIFTAIDSPONSORSHIP,
      LOCKBOXID, LOCKBOXBATCHNUMBER, LOCKBOXBATCHSEQUENCE, PDACCOUNTSYSTEMID, BASECURRENCYID, TRANSACTIONCURRENCYID,
      BASEEXCHANGERATEID, EXCHANGERATE, USESYSTEMGENERATEDREFERENCENUMBER, STANDINGORDERREFERENCENUMBER,APPLYBYPERCENT, MERCHANTACCOUNTID,
      SALE_LOWPRICE, SALE_MEDIANPRICE, SALE_HIGHPRICE,CREDITCARDATTEMPTCOUNT,VENDORID, CLIENTAPPLICENSEID, RECEIPTNUMBER, RECEIPTSTACKSHORTNAME,
      NAMECODE, SIMILARADDRESSCODE,UNSIMILARADDRESSCODE, NEWADDRESSENDDATECODE, NEWADDRESSPRIMARYCODE, BIRTHDATERULECODE, DIFFERENTPHONECODE,
      NEWPHONEENDDATECODE, NEWPHONEPRIMARYCODE, DIFFERENTEMAILCODE, NEWEMAILENDDATECODE, NEWEMAILPRIMARYCODE, USEGLOBALSETTINGS, CREATEHISTORICALNAMECODE,
      PAYMENTFORPLEDGERECEIPTAMOUNT, SEPAMANDATEID, INSTALLMENTAMOUNT,REQUIRECREDITCARDPROCESSING, NOTEHTMLNOTE, CARDHOLDERNAME, CREDITTYPECODEID, CREDITCARDPARTIALNUMBER, EXPIRESON
    )
    values 
    (
      @ID,@BATCHID,@CONSTITUENTID,@DATE,@PAYMENTMETHODCODE,@DONOTACKNOWLEDGE,@CHECKDATE,@CHECKNUMBER,@REFERENCEDATE,@REFERENCENUMBER,
      @CREDITCARDID, @AUTHORIZATIONCODE, @CONSTITUENTACCOUNTID, @AMOUNT, @TYPECODE, @RECEIPTAMOUNT, 
      @DONOTRECEIPT,@SEQUENCE,@APPLYTOSHOWNFORCONSTITUENTID, @INSTALLMENTFREQUENCYCODE, @INSTALLMENTSTARTDATE, 
      @INSTALLMENTENDDATE, @NUMBEROFINSTALLMENTS, @FINDERNUMBER, @SOURCECODE, @APPEALID, @FINDERNUMBERISVALID, 
      @USERMODIFIEDBENEFITS, @BENEFITSWAIVED, @POSTDATE, @POSTSTATUSCODE, @PROPERTYSUBTYPECODEID, @GIFTINKINDSUBTYPECODEID, 
      @SENDPLEDGEREMINDER, @SALEDATE, @SALEAMOUNT, @BROKERFEE, @SALEPOSTSTATUSCODE, @SALEPOSTDATE, @ISSUER, @NUMBEROFUNITS, 
      @SYMBOL, @MEDIANPRICE, @NOTETITLE, @NOTEAUTHORID, @NOTEDATEENTERED, @NOTETYPECODEID, @NOTETEXTNOTE, 
      @GIVENANONYMOUSLY, @GIVENANONYMOUSLYDEFAULTEDFORCONSTITUENTID, @USERMODIFIEDRECEIPTAMOUNT, @PLEDGESUBTYPEID, 
      @REJECTIONMESSAGE, @MAILINGID, @CHANNELCODEID, @PAYMENTFORPLEDGEAMOUNT, @RECEIPTTYPECODE, 
      @CHANGEAGENTID, @CHANGEAGENTID,@CURRENTDATE,@CURRENTDATE,@OTHERPAYMENTMETHODCODEID,@LETTERCODEID,@ACKNOWLEDGEDATE,@REFERENCE,
      @CATEGORYCODEID, @ACKNOWLEDGEEID, @OTHERTYPECODEID, @OPPORTUNITYID, @DIRECTDEBITRESULTCODE, @LOWPRICE, @HIGHPRICE, @NUMBEROFUNITSSOLD,
      @USERMODIFIEDNUMBEROFUNITSSOLD, @STANDINGORDERSETUP, @STANDINGORDERSETUPDATE, @TRANSACTIONID, @DECLINESGIFTAID, @DDISOURCECODEID, @DDISOURCEDATE, 
      @ISCOVENANT, @AMOUNTFORVAT, @VATTAXRATEID, @VATAMOUNT, @APPLICATIONINFO, coalesce(rtrim(ltrim(@REVENUELOOKUPID)),''), @MGGENERATED, @MGALTERED, @PAYINGPENDINGREVENUEID,
      @GIFTINKINDITEMNAME, @GIFTINKINDDISPOSITIONCODE, @GIFTINKINDNUMBEROFUNITS, @GIFTINKINDFAIRMARKETVALUE, @DIRECTDEBITISREJECTED, coalesce(@ISGIFTAIDSPONSORSHIP, 0),
      @LOCKBOXID, @LOCKBOXBATCHNUMBER, @LOCKBOXBATCHSEQUENCE, @PDACCOUNTSYSTEMID, @BASECURRENCYID, @TRANSACTIONCURRENCYID,
      @BASEEXCHANGERATEID, @EXCHANGERATE, @GENERATEREFERENCENUMBER, @STANDINGORDERREFERENCENUMBER,@APPLYBYPERCENT, @MERCHANTACCOUNTID,
      @SALE_LOWPRICE, @SALE_MEDIANPRICE, @SALE_HIGHPRICE, @CREDITCARDATTEMPTCOUNT,isnull(@VENDORID, ''), isnull(@CLIENTAPPLICENSEID, ''), coalesce(@RECEIPTNUMBER, 0), coalesce(@RECEIPTSTACKSHORTNAME, ''),
      @NAMECODE, @SIMILARADDRESSCODE, @UNSIMILARADDRESSCODE, @NEWADDRESSENDDATECODE, @NEWADDRESSPRIMARYCODE, @BIRTHDATERULECODE, @DIFFERENTPHONECODE,
      @NEWPHONEENDDATECODE, @NEWPHONEPRIMARYCODE, @DIFFERENTEMAILCODE, @NEWEMAILENDDATECODE, @NEWEMAILPRIMARYCODE, @USEGLOBALSETTINGS, @CREATEHISTORICALNAMECODE,
      @PAYMENTFORPLEDGERECEIPTAMOUNT, @SEPAMANDATEID, @INSTALLMENTAMOUNT,@REQUIRECREDITCARDPROCESSING, @NOTEHTMLNOTE, @CARDHOLDERNAME, @CREDITTYPECODEID, @CREDITCARDNUMBER, @EXPIRESON
    );

    -- Create the splits collection with the single designation if the splits collection is null and the designation isn't

    if @SPLITS is null and @SINGLEDESIGNATIONID is not null and @TYPECODE <> 0 -- Payment

    begin
      set @SPLITS = ( select
                @SINGLEDESIGNATIONID as DESIGNATIONID,
@AMOUNT as AMOUNT,
                1 as SEQUENCE,
                @DECLINESGIFTAID as DECLINESGIFTAID,
                @ISGIFTAIDSPONSORSHIP as ISGIFTAIDSPONSORSHIP
                for xml raw('ITEM'),type,elements,root('SPLITS'),binary base64)
    end

    if @SPLITS is not null
    begin
      exec dbo.USP_REVENUEBATCH_SPLITSWITHCHILDREN_UPDATEFROMXML @ID, @SPLITS, @DATE, @CHANGEAGENTID, @CURRENTDATE;
    end

    -- Sync the additional applications collection with the single application info field

    declare @ADDITIONALAPPLICATIONCOUNT int = isnull((select count(*) from @ADDITIONALAPPLICATIONSSTREAM.nodes('/ADDITIONALAPPLICATIONSSTREAM/ITEM') T(c)), 0)
    if @TYPECODE = 0 and len(@APPLICATIONINFO) = 3 and @ADDITIONALAPPLICATIONCOUNT = 0
    begin
      declare @APPTYPE integer = convert(integer, substring(@APPLICATIONINFO,3,1));

      set @ADDITIONALAPPLICATIONSSTREAM = (
        select
          ID, TYPECODE, APPLIED, DESIGNATIONID, OTHERTYPECODEID, DECLINESGIFTAID, OPPORTUNITYID, ISGIFTAIDSPONSORSHIP, CATEGORYCODEID, DIDCAMPAIGNSDEFAULT
        from (
          select
            NEWID() ID,
            @APPTYPE TYPECODE,
            @AMOUNT APPLIED,
            @SINGLEDESIGNATIONID DESIGNATIONID, 
            @OTHERTYPECODEID OTHERTYPECODEID,
            @DECLINESGIFTAID DECLINESGIFTAID, 
            @OPPORTUNITYID OPPORTUNITYID,
            coalesce(@ISGIFTAIDSPONSORSHIP, 0) ISGIFTAIDSPONSORSHIP,
            @CATEGORYCODEID CATEGORYCODEID,
            cast(0 as bit) as DIDCAMPAIGNSDEFAULT
        ) ADDITIONALAPPLICATION
        for xml raw('ITEM'),type,elements,root('ADDITIONALAPPLICATIONSSTREAM'),binary base64);         
    end

    if not @ADDITIONALAPPLICATIONSSTREAM is null
    begin
      exec dbo.USP_REVENUEBATCH_ADDITIONALAPPLICATIONWITHCHILDREN_UPDATEFROMXML @ID, @ADDITIONALAPPLICATIONSSTREAM, @DATE, @CHANGEAGENTID, @CURRENTDATE;
    end

    declare @APPLICATIONCODE tinyint;
    declare @SINGLEAPPLICATIONID uniqueidentifier;
    declare @APPLICATIONTYPECODE tinyint;

    -- @APPLICATIONINFO = 0:3 is for Sponsorship additional donation and the stored procedure "USP_DATALIST_RECOGNITIONDEFAULTS" is expecting type code 3 for default recognition credits in case of a matching gift claim,

    -- so passing type code 0 in case of Sponsorship Additional Donation so that recognitions get defaulted correctly.

    if len(@APPLICATIONINFO) = 3   
      set @APPLICATIONCODE = case when cast(substring(@APPLICATIONINFO, 3, 1) as tinyint) = 3 then 0 else cast(substring(@APPLICATIONINFO, 3, 1) as tinyint) end;
    else if len(@APPLICATIONINFO) > 3
      begin try
        select  
          @SINGLEAPPLICATIONID = SINGLEAPPLICATIONID,
          @APPLICATIONTYPECODE = APPLICATIONTYPECODE
        from dbo.UFN_REVENUEBATCH_PARSEAPPLICATIONINFO(@APPLICATIONINFO)
      end try
      begin catch
        set @InfoMsg='BBERR_ORIGINAL_ERROR:50001';
        raiserror(@InfoMsg,1,11);

        select
          @ErrorMessage = ERROR_MESSAGE(),
          @ErrorSeverity = ERROR_SEVERITY(),
          @ErrorState = ERROR_STATE();

        raiserror(@ErrorMessage,@ErrorSeverity,@ErrorState);
        return 1;
      end catch


    --JamesWilliams If this application was generated by the generate payments process, mark it pending

    if @SINGLEAPPLICATIONID is not null and @SINGLEAPPLICATIONID <> '00000000-0000-0000-0000-000000000000' and @SINGLEAPPLICATIONID = @PAYINGPENDINGREVENUEID
      update dbo.REVENUESCHEDULE
        set REVENUESCHEDULE.ISPENDING = 1,
          REVENUESCHEDULE.CHANGEDBYID = @CHANGEAGENTID,
          REVENUESCHEDULE.DATECHANGED = @CURRENTDATE
      where ID = @PAYINGPENDINGREVENUEID;

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

    -- Default additional application recognition credits if they were not specified.

    -- Note that additional applications are in the transaction currency of the row.

    if @APPLICATIONRECOGNITIONS is null and @ADDITIONALAPPLICATIONCOUNT > 0
    begin
        exec dbo.USP_REVENUEBATCH_DEFAULTAPPLICATIONRECOGNITIONS @APPLICATIONRECOGNITIONS output, @ADDITIONALAPPLICATIONSSTREAM, @GIVENANONYMOUSLY, @CONSTITUENTID, @DATE, @BASECURRENCYID, @BASEEXCHANGERATEID, @TRANSACTIONCURRENCYID, @BASECURRENCYDECIMALDIGITS, @BASECURRENCYROUNDINGTYPECODE, @EXCHANGERATE
    end

    declare @ISEXCEPTIONBATCH bit = 0
    if exists (select top 1 1 from BATCH where ID = @BATCHID and ORIGINATINGBATCHID != null)
    set @ISEXCEPTIONBATCH = 1;

    -- deal with payment payment application recognitions/solicitors

    -- don't add these if this is an exception batch

    if @REVENUESTREAMS.exist('/REVENUESTREAMS/ITEM') <> 0 and @TYPECODE = 0 and @ISEXCEPTIONBATCH = 0
      exec dbo.USP_REVENUEBATCH_ADDPAYMENTRECOGNITIONSANDSOLICITORS @ID, @APPLICATIONRECOGNITIONS, @APPLICATIONSOLICITORS, @REVENUESTREAMS, @CHANGEAGENTID, @CURRENTDATE   

    if @APPLICATIONCODE is not null and @SPLITCOUNT > 1 and @TYPECODE = 0
      exec dbo.USP_REVENUEBATCH_ADDRECOGNITIONSANDSOLICITORS @ID, @APPLICATIONRECOGNITIONS, @APPLICATIONSOLICITORS, @SPLITS, @APPLICATIONCODE, NULL, @CHANGEAGENTID, @CURRENTDATE
    else if @APPLICATIONCODE is null and @SINGLEAPPLICATIONID is null and @ADDITIONALAPPLICATIONCOUNT >= 1 and @TYPECODE = 0
      exec dbo.USP_REVENUEBATCH_ADDRECOGNITIONSANDSOLICITORS @ID, @APPLICATIONRECOGNITIONS, @APPLICATIONSOLICITORS, NULL, NULL, @ADDITIONALAPPLICATIONSSTREAM, @CHANGEAGENTID, @CURRENTDATE
    else if @APPLICATIONCODE = 0 and @APPLICATIONRECOGNITIONS is not null and @ADDITIONALAPPLICATIONCOUNT >= 1 
          and @TYPECODE = 0 and @APPLICATIONINFO <> '0:3' and (@IMPORT = 1 or @BBNCTRANID > 0)  -- Don't execute this if not an import or BBIS row. 

          -- WI#748335 - the code below was being executed for exception batches and recognitions were defaulted.

        begin
            -- WI#603655: Recognition credits are not being created correctly on import by batch templates with application defaulted to 'Donation'

            -- @APPLICATIONRECOGNITIONS should be used to create recognitions when there are multiple additional applications

            -- APPLICATIONINFO was also being incorrectly set to '0:0' instead of a blank string

            exec dbo. USP_REVENUEBATCH_ADDRECOGNITIONSANDSOLICITORS @ID, @APPLICATIONRECOGNITIONS, @APPLICATIONSOLICITORS, NULL, NULL, @ADDITIONALAPPLICATIONSSTREAM, @CHANGEAGENTID, @CURRENTDATE; 
            update BATCHREVENUE set APPLICATIONINFO = '' where ID = @ID;
         end
    else
    begin
      if not @RECOGNITIONS is null and @RECOGNITIONS.exist('RECOGNITIONS/ITEM') <> 0
        exec dbo.USP_REVENUEBATCH_GETRECOGNITIONS_ADDFROMXML @ID, @RECOGNITIONS, @CHANGEAGENTID;
      else
        if @DIDRECOGNITIONSDEFAULT = 0
        begin
          -- Create default recognitions

          declare @SCAMOUNT money

          set @SCAMOUNT = @AMOUNT 

          -- Convert to base currency.

          set @SCAMOUNT = case
            when @BASECURRENCYID = @TRANSACTIONCURRENCYID
              then @SCAMOUNT
            when @BASEEXCHANGERATEID = '00000000-0000-0000-0000-000000000001'
              then dbo.UFN_CURRENCY_ROUND(dbo.UFN_CURRENCY_APPLYRATE(@SCAMOUNT, @EXCHANGERATE), @BASECURRENCYDECIMALDIGITS, @BASECURRENCYROUNDINGTYPECODE)
            else
              dbo.UFN_CURRENCY_CONVERT(@SCAMOUNT, @BASEEXCHANGERATEID)
          end

          --APPLICATIONTYPECODE 8 in APPLICATIONINFO is for Matching gift and UFN_REVENUEBATCH_GETRECOGNITIONDEFAULTS_2 expects type code 3 for matching gift.

          --APPLICATIONTYPECODE 4 in APPLICATIONINFO is for Recurring Gift and UFN_REVENUEBATCH_GETRECOGNITIONDEFAULTS_2 expects 2.

          --APPLICATIONTYPECODE 1 in APPLICATIONINFO is for Sponsorship Recurring Gift and UFN_REVENUEBATCH_GETRECOGNITIONDEFAULTS_2 expects 2.

   declare @APPTYPECODE tinyint = 
            case @APPLICATIONTYPECODE
              when 8 then 3 
              when 4 then 2 
              when 1 then 2 
              else coalesce(@APPLICATIONTYPECODE, @APPLICATIONCODE) 
           end; 

          if @SCAMOUNT >= 0
          begin
            insert into dbo.BATCHREVENUERECOGNITION
            (
              BATCHREVENUEID, 
              CONSTITUENTID, 
              REVENUERECOGNITIONTYPECODEID,
              AMOUNT,
              EFFECTIVEDATE,
              ADDEDBYID, 
              CHANGEDBYID, 
              DATEADDED, 
              DATECHANGED
            )
            select
              @ID,
              RECOGNITIONS.CONSTITUENTID,
              RECOGNITIONS.REVENUERECOGNITIONTYPECODEID,
              RECOGNITIONS.AMOUNT,
              @date,
              @CHANGEAGENTID,
              @CHANGEAGENTID,
              @CURRENTDATE, 
              @CURRENTDATE
            from dbo.UFN_REVENUEBATCH_GETRECOGNITIONDEFAULTS_3(@GIVENANONYMOUSLY, @CONSTITUENTID, @SCAMOUNT, @DATE, null, @APPTYPECODE, @SINGLEAPPLICATIONID, @ID) as RECOGNITIONS where RECOGNITIONS.AMOUNT > 0;
          end
        end

        if not @SOLICITORS is null
          exec dbo.USP_REVENUEBATCH_GETSOLICITORS_ADDFROMXML @ID, @SOLICITORS, @CHANGEAGENTID;
    end

    if not @BENEFITS is null
      exec dbo.USP_REVENUEBATCH_GETBENEFITS_ADDFROMXML @ID, @BENEFITS, @CHANGEAGENTID;

    if not @PERCENTAGEBENEFITS is null
      exec dbo.USP_REVENUEBATCH_GETBENEFITSPCT_ADDFROMXML @ID, @PERCENTAGEBENEFITS, @CHANGEAGENTID;

    if not @INSTALLMENTS is null
    begin
     -- first loop through and set the ID field if it hasn't been set bug 119652

    set @INSTALLMENTS = (select
          case when T.c.value('(ID)[1]','uniqueidentifier') is null or T.c.value('(ID)[1]','uniqueidentifier') = '00000000-0000-0000-0000-000000000000' then newid() else T.c.value('(ID)[1]','uniqueidentifier') end as ID,
          T.c.value('(INSTALLMENTID)[1]','uniqueidentifier') AS 'INSTALLMENTID',
          T.c.value('(DATE)[1]','datetime') AS 'DATE',
          T.c.value('(AMOUNT)[1]','money') AS 'AMOUNT',
          T.c.value('(RECEIPTAMOUNT)[1]','money') AS 'RECEIPTAMOUNT',
          T.c.value('(BALANCE)[1]','money') AS 'BALANCE',
          T.c.value('(APPLIED)[1]','money') AS 'APPLIED',
          T.c.value('(SEQUENCE)[1]','int') AS 'SEQUENCE',
          (select 
              ID,
              INSTALLMENTSPLITID,
              AMOUNT,
              APPLIED,
              DESIGNATIONID
              from dbo.UFN_REVENUEUPDATEBATCH_GETINSTALLMENTSPLITS_FROMITEMLISTXML(T.c.query('(INSTALLMENTSPLITS)[1]'))
              for xml raw('ITEM'),type,elements,BINARY BASE64) as 'INSTALLMENTSPLITS'
        from @INSTALLMENTS.nodes('/INSTALLMENTS/ITEM') T(c)
        for xml raw('ITEM'),type,elements,root('INSTALLMENTS'),binary base64);

      exec dbo.USP_REVENUEBATCH_GETINSTALLMENTS_ADDFROMXML @ID, @INSTALLMENTS, @CHANGEAGENTID;
      --insert into temp_test(test) values(convert(nvarchar(max),@INSTALLMENTS));

      declare @BATCHINSTALLMENTID uniqueidentifier;
      declare @INSTALLMENTSPLITS xml;
      declare INSTALLMENTSPLITS cursor local fast_forward for 
      select 
        T.c.value('(ID)[1]','uniqueidentifier') as BATCHINSTALLMENTID,
        cast(T.c.query('INSTALLMENTSPLITS') as xml) as INSTALLMENTSPLITS
      from @INSTALLMENTS.nodes('/INSTALLMENTS/ITEM') T(c)
      open INSTALLMENTSPLITS
      fetch next from INSTALLMENTSPLITS into @BATCHINSTALLMENTID, @INSTALLMENTSPLITS

      while @@FETCH_STATUS = 0
      begin

        exec dbo.USP_REVENUEUPDATEBATCH_GETINSTALLMENTSPLITS_ADDFROMXML @BATCHINSTALLMENTID, @INSTALLMENTSPLITS, @CHANGEAGENTID;

      fetch next from INSTALLMENTSPLITS into @BATCHINSTALLMENTID, @INSTALLMENTSPLITS
      end
      close INSTALLMENTSPLITS
      deallocate INSTALLMENTSPLITS
    end

if not @UNAPPLIEDMATCHINGGIFTSPLITS is null
      exec dbo.USP_REVENUEBATCH_GETUNAPPLIEDMATCHINGGIFTSPLITS_ADDFROMXML @ID, @UNAPPLIEDMATCHINGGIFTSPLITS, @CHANGEAGENTID;

    if not @TRIBUTES is null
      exec dbo.USP_REVENUEBATCH_GETTRIBUTES_ADDFROMXML @ID, @TRIBUTES, @CHANGEAGENTID;

    if not @TAXDECLARATIONS is null
      exec dbo.USP_REVENUEBATCH_GETTAXDECLARATIONS_ADDFROMXML @ID, @TAXDECLARATIONS, @CHANGEAGENTID;


    if not @REVENUESTREAMS is null
    begin 
      exec dbo.USP_REVENUEBATCH_REVENUEAPPLICATIONS_ADDFROMXML_2 @ID, @REVENUESTREAMS, @CHANGEAGENTID, @CURRENTDATE, @CURRENTAPPUSERID;

      /*JamesWill 2006-10-06 Changed update from using an inner join to using a where ID in (select...) */
      /*JamesWill 2007-03-02 CR268756-030207 only mark gifts pending if they were automatically generated */
      update dbo.REVENUESCHEDULE
        set REVENUESCHEDULE.ISPENDING = 1,
          REVENUESCHEDULE.CHANGEDBYID = @CHANGEAGENTID,
          REVENUESCHEDULE.DATECHANGED = @CURRENTDATE
      where ID in (select REVENUEID from dbo.BATCHREVENUEAPPLICATION where BATCHREVENUEID = @ID and APPLIED > 0 and WASGENERATED = 1);
    end

    --If Transaction is of type Payment and MatchingGift collection is empty - do generate the Matching Gifts

    if @TYPECODE = 0 and @MGALTERED = 0 and @MATCHINGGIFTS.exist('MATCHINGGIFTS/ITEM') = 0
        begin
            if @REVENUESTREAMS.exist('/REVENUESTREAMS/ITEM') = 0 and len(@APPLICATIONINFO) > 3
                begin
                    set @REVENUESTREAMS = 
                        (
                            select
                            newid() as ID,
                            @SINGLEAPPLICATIONID as APPLICATIONID,
                            (
                                case @APPLICATIONTYPECODE 
                                when 5 then 1 --Pledge Payment

                                when 8 then 3 --MGPledge Payment

                                when 4 then 2 --Recurring Gift Payment

                                when 7 then 6 --Event Registration Payment

                                when 6 then 4 --Planned gift

                                when 2 then 5 --Membership

                                when 10 then 10 -- Donor challenge payment

                                when 1 then 33 --Sponsorship Payment

                                when 9 then 9 --Grant Award Payment

                                else -1
                                end
                            ) as TYPECODE,
                            @AMOUNT as APPLIED,
                            @DECLINESGIFTAID as DECLINESGIFTAID,
                            @OPPORTUNITYID as OPPORTUNITYID,
                            @ISGIFTAIDSPONSORSHIP as ISGIFTAIDSPONSORSHIP,
                            @SINGLEDESIGNATIONID as DESIGNATIONID,
                            @TRANSACTIONCURRENCYID as TRANSACTIONCURRENCYID
                            for xml raw('ITEM'),type,elements,root('REVENUESTREAMS'),binary base64
                        );
                end

            set @MATCHINGGIFTS =
                 (  
                    select
                    newid(),
                    DEFAULTMATCHINGGIFTS.[MATCHEDCONSTITUENTID] MATCHINGCONSTITUENTID,
                    DEFAULTMATCHINGGIFTS.[ORGANIZATIONID], 
                    DEFAULTMATCHINGGIFTS.[RELATIONSHIPID],
                    DEFAULTMATCHINGGIFTS.[MATCHINGGIFTCONDITIONTYPEID] MATCHINGGIFTCONDITIONID,
                    DEFAULTMATCHINGGIFTS.[AMOUNT],
                    DEFAULTMATCHINGGIFTS.[DATE],
                    case when DEFAULTMATCHINGGIFTS.[SPLITS] is null then null else DEFAULTMATCHINGGIFTS.[SPLITS].query('(SPLITS/ITEM)') end as SPLITS,
                    DEFAULTMATCHINGGIFTS.[COULDNOTDEFAULTMATCHINGGIFT],
                    @TRANSACTIONCURRENCYID,
                    @BASEEXCHANGERATEID, --Use the same exchange rate as the payment

                    null,
                    0                   
                  from dbo.UFN_REVENUEBATCH_GETDEFAULTMATCHINGGIFTS_4(
                    @CONSTITUENTID, 
                    @DATE, 
                    @AMOUNT, 
                    @CALCULATEDRECEIPTAMOUNT, 
                    null, 
                    @TRANSACTIONCURRENCYID, 
                    @REVENUESTREAMS, 
                    @ADDITIONALAPPLICATIONSSTREAM
                  ) as [DEFAULTMATCHINGGIFTS]
                 for xml raw('ITEM'),type,elements,root('MATCHINGGIFTS'),BINARY BASE64 
                );

            if @MATCHINGGIFTS is not null
            begin
                update dbo.BATCHREVENUE
                set MGALTERED = 1
                where ID = @ID;
            end 

        end 

    if exists (
      select 
        1 
      from
        @MATCHINGGIFTS.nodes('MATCHINGGIFTS/ITEM') as MATCHINGGIFTSXML([MATCHINGGIFT])
      where 
        MATCHINGGIFTSXML.[MATCHINGGIFT].value('MATCHINGCONSTITUENTID[1]', 'uniqueidentifier') is null
    )
    raiserror('BBERR_MATCHINGCONSTITUENTIDREQUIRED', 13, 1);

    exec dbo.USP_REVENUEBATCH_ENHANCEDMATCHINGGIFTSWITHCHILDREN_UPDATEFROMXML @ID, @MATCHINGGIFTS, @CHANGEAGENTID, @CURRENTDATE;

    if @APPLICATIONBUSINESSUNITS is not null
      exec dbo.USP_REVENUEBATCH_ADDBUSINESSUNITS @ID, @APPLICATIONBUSINESSUNITS, @TYPECODE

  --Add BBIS fields to batch

  if @BBNCTRANSACTIONPROCESSORID is not null and @BBNCTRANID > 0
    begin
      insert into dbo.BBNCDOWNLOADEDTRANSACTION ([ID])
      values (@BBNCID);

      insert into dbo.BATCHREVENUEBBNCINFO
      (
        BATCHREVENUEID,
        NETCOMMUNITYTRANSACTIONPROCESSORID,
        ORIGINAPPEALID,
        BBNCTRANID,
        BBNCID,
        PAGENAME,
        PAGEID,
        ADDEDBYID,
        CHANGEDBYID,
        DATEADDED,
        DATECHANGED,
        MARKGIFTASRECEIPTED,
        EMAILID,
        EMAILSUBJECT,
        EMAILNAME,
        FIRSTPAYMENTPROCESSED
      )
      values
      (
        @ID,
        @BBNCTRANSACTIONPROCESSORID,
        @BBNCORIGINAPPEALID,
        @BBNCTRANID,
        @BBNCID,
        @BBNCORIGINPAGENAME,
        @BBNCORIGINPAGEID,
        @CHANGEAGENTID,
        @CHANGEAGENTID,
        @CURRENTDATE,
        @CURRENTDATE,
        coalesce(@MARKGIFTASRECEIPTED, 0),
        coalesce(@EMAILID, 0),
        coalesce(@EMAILSUBJECT, ''),
        coalesce(@EMAILNAME, ''),
        coalesce(@FIRSTPAYMENTPROCESSED, '')
      );

      delete from dbo.BBNCUNSUCCESSFULDOWNLOADTRANSACTION
      where ID = @BBNCID;
    end

    if @SOLICITCODES is not null
    begin
     exec dbo.USP_BATCHREVENUE_GETCONSTITUENTSOLICITCODES_ADDFROMXML_DEFAULTID  @ID, @SOLICITCODES, @CHANGEAGENTID;
    end

    -- add solicit codes for matched constituent

    if @IMPORT = 1 or @BBNCTRANID > 0
    begin
        insert into [dbo].[BATCHREVENUECONSTITUENTSOLICITCODE]
        (
          [ID],
          [BATCHREVENUEID],
          [SOLICITCODEID],
          [CONSTITUENTSOLICITCODEID],
          [STARTDATE],
          [ENDDATE],
          [COMMENTS],
          [SEQUENCE],
          [CONSENTPREFERENCECODE],
          [SOURCECODEID],
          [SOURCEFILEPATH],
          [PRIVACYPOLICYFILEPATH],
          [SUPPORTINGINFORMATION],
          [CONSENTSTATEMENT],
          [ADDEDBYID],
          [CHANGEDBYID],
          [DATEADDED],
          [DATECHANGED]
        )
        select
          newid() ID,
          @ID [BATCHREVENUEID],
          CONSTITUENTSOLICITCODE.[SOLICITCODEID],
          CONSTITUENTSOLICITCODE.[ID] [CONSTITUENTSOLICITCODEID],
          CONSTITUENTSOLICITCODE.[STARTDATE],
          CONSTITUENTSOLICITCODE.[ENDDATE],
          CONSTITUENTSOLICITCODE.[COMMENTS],
          CONSTITUENTSOLICITCODE.[SEQUENCE],
          CONSTITUENTSOLICITCODE.[CONSENTPREFERENCECODE],
          CONSTITUENTSOLICITCODE.[SOURCECODEID],
          CONSTITUENTSOLICITCODE.[SOURCEFILEPATH],
          CONSTITUENTSOLICITCODE.[PRIVACYPOLICYFILEPATH],
          CONSTITUENTSOLICITCODE.[SUPPORTINGINFORMATION],
          CONSTITUENTSOLICITCODE.[CONSENTSTATEMENT],
          @CHANGEAGENTID,
          @CHANGEAGENTID,
          @CURRENTDATE,
          @CURRENTDATE
        from dbo.CONSTITUENTSOLICITCODE
        left join BATCHREVENUECONSTITUENTSOLICITCODE on CONSTITUENTSOLICITCODE.ID = BATCHREVENUECONSTITUENTSOLICITCODE.CONSTITUENTSOLICITCODEID and
         BATCHREVENUEID = @ID
        where
          CONSTITUENTSOLICITCODE.[CONSTITUENTID] = @CONSTITUENTID and
          BATCHREVENUECONSTITUENTSOLICITCODE.ID is null;

    end

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

    if @SOLICITCODES is not null
    begin
      -- address the auto end date issues for consent based solicit codes

      exec dbo.USP_BATCHREVENUE_ADJUSTSOLICITCODEDATERANGES @CONSTITUENTID, @SOLICITCODES, @CHANGEAGENTID;
    end

    -- Get rid of any constituent edits for a constituent other than the one we ended up saving.

    delete from dbo.BATCHCONSTITUENTUPDATE
    where ID = @ID
    and PRIMARYRECORDID <> @CONSTITUENTID; 

    --this is a constituent update coming from import or BBIS - business rules are applied as well

    if @NEWCONSTITUENT.exist('NEWCONSTITUENT/ITEM') = 1 and exists (select ID from dbo.CONSTITUENT where ID = @CONSTITUENTID)          
      begin
        exec USP_REVENUEBATCH_EDITCONSTITUENTFROMXML_1
              @NEWCONSTITUENT, 
              @CONSTITUENTID, 
              @BATCHID, 
              @ID, 
              @CHANGEAGENTID, 
              @CURRENTAPPUSERID, 
              @NAMECODE,
              @SIMILARADDRESSCODE,
              @UNSIMILARADDRESSCODE,
              @NEWADDRESSPRIMARYCODE,
              @BIRTHDATERULECODE,
              @DIFFERENTPHONECODE,
              @NEWPHONEPRIMARYCODE,
              @DIFFERENTEMAILCODE,
              @NEWEMAILPRIMARYCODE;              

          if exists (select ID from dbo.BATCHREVENUECONSTITUENT where ID = @CONSTITUENTID)
          --constituent was resolved by DUPLICATE check

          --delete the temporary batch version of the constituent

          exec dbo.USP_REVENUEBATCH_CONSTITUENT_DELETE @BATCHREVENUECONSTITUENTID = @CONSTITUENTID, @CHANGEAGENTID = @CHANGEAGENTID              
      end              

  end try

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

  return 0;
end