USP_DATAFORMTEMPLATE_ADD_REVENUEUPDATEBATCHROW

CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_REVENUEUPDATEBATCHROW
(
  @ID uniqueidentifier = null output,  
  @REVENUEID uniqueidentifier,
  @REVENUESPLITID uniqueidentifier = null,
  @BATCHID uniqueidentifier,
  @CHANGEAGENTID uniqueidentifier,
  @CONSTITUENTID uniqueidentifier = null,
  @TYPECODE tinyint,
  @DATE datetime,
  @AMOUNT money,
  @PAYMENTMETHODCODE tinyint = 0,
  @DONOTACKNOWLEDGE bit = 0,
  @CHECKDATE dbo.UDT_FUZZYDATE = '00000000',
  @CHECKNUMBER nvarchar(20) = '',
  @REFERENCEDATE dbo.UDT_FUZZYDATE = '00000000',
  @REFERENCENUMBER nvarchar(20) = '',
  @CARDHOLDERNAME nvarchar(255) = '',
  @CREDITCARDNUMBER nvarchar(20) = '',
  @CREDITTYPECODEID uniqueidentifier = null,
  @AUTHORIZATIONCODE nvarchar(20) = '',
  @EXPIRESON dbo.UDT_FUZZYDATE = '00000000',
  @ISSUER nvarchar(100) = '',
  @NUMBEROFUNITS decimal(20,3) = 0,
  @SYMBOL nvarchar(25) = '',
  @MEDIANPRICE decimal(19,4) = 0,
  @PROPERTYSUBTYPECODEID uniqueidentifier = null,
  @GIFTINKINDSUBTYPECODEID uniqueidentifier = null,
  @RECEIPTAMOUNT money = 0,
  @DONOTRECEIPT bit = 0,
  @CONSTITUENTACCOUNTID uniqueidentifier = null,
  @SPLITS xml = null,
  @SINGLEDESIGNATIONID uniqueidentifier = null,
  @REVENUESTREAMS xml = null,
  @APPLYTOSHOWNFORCONSTITUENTID uniqueidentifier= null,
  @SEQUENCE int,
  @INSTALLMENTFREQUENCYCODE tinyint = 5,
  @INSTALLMENTSTARTDATE datetime = null,
  @INSTALLMENTENDDATE datetime = null,
  @NUMBEROFINSTALLMENTS int = 1,
  --Solicitors field is no longer in use (APPLICATIONSOLICITORS is current) 

  @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 field is no longer in use (APPLICATIONRECOGNITIONS is current)

  @RECOGNITIONS xml = null,
  @DIDRECOGNITIONSDEFAULT bit = 0,
  @TRIBUTES xml = null,
  @UNAPPLIEDMATCHINGGIFTSPLITS xml = null,
  @UNAPPLIEDMATCHINGGIFTAMOUNT money = 0,
  @RECEIPTTYPECODE tinyint = 0,
  @NEWCONSTITUENT xml = null,
  @OTHERPAYMENTMETHODCODEID uniqueidentifier = null,
  @LETTERCODEID uniqueidentifier = null,
  @ACKNOWLEDGEDATE datetime = null,
  @REFERENCE nvarchar(255) = null,
  @CATEGORYCODEID uniqueidentifier = null,
  @ACKNOWLEDGEEID uniqueidentifier = null,
  @APPLICATIONINFO nvarchar(60) = null,
  @OTHERTYPECODEID uniqueidentifier = null,
  @OPPORTUNITYID uniqueidentifier = null,
  @DIRECTDEBITRESULTCODE nvarchar(10) = '',
  @LOWPRICE decimal(19,4) = 0,
  @HIGHPRICE decimal(19,4) = 0,
  @NUMBEROFUNITSSOLD decimal(20,3) = 0,
  @USERMODIFIEDNUMBEROFUNITSSOLD bit = 0,
  @CREDITCARDTOKEN uniqueidentifier = null,
  @REJECTIONMESSAGE nvarchar(250) = '',
  @PARTIALCREDITCARDNUMBER nvarchar(4) = '',
  @STANDINGORDERCONSTITUENTACCOUNTID uniqueidentifier = null,
  @STANDINGORDERREFERENCEDATE dbo.UDT_FUZZYDATE = '00000000',
  @STANDINGORDERREFERENCENUMBER nvarchar(20) = '',
  @STANDINGORDERSETUP bit = 0,
  @STANDINGORDERSETUPDATE datetime = null,
  @TAXDECLARATIONS xml = null,
  @TRANSACTIONID uniqueidentifier = null,
  @ISTRANSIENTCARD bit = null,
  @DECLINESGIFTAID bit = null,
  @DDISOURCECODEID uniqueidentifier = null,
  @DDISOURCEDATE date = null,
  @ISCOVENANT bit = null,
  @AMOUNTFORVAT money = 0,
  @VATTAXRATEID uniqueidentifier = null,
  @VATAMOUNT money = 0,
  @ADDITIONALAPPLICATIONSSTREAM xml = null,
  @REVENUELOOKUPID nvarchar(100) = '',
  @APPLICATIONSOLICITORS xml = null,
  @APPLICATIONRECOGNITIONS xml = null,
  @CURRENTAPPUSERID uniqueidentifier = null,
  @MATCHINGGIFTS xml = null,
  @MGGENERATED bit = null,
  @MGALTERED bit = null,
  @PAYINGPENDINGREVENUEID uniqueidentifier = null,
  @GIFTINKINDITEMNAME nvarchar(100) = '',
  @GIFTINKINDDISPOSITIONCODE tinyint = 0,
  @GIFTINKINDNUMBEROFUNITS int = 0,
  @GIFTINKINDFAIRMARKETVALUE money = 0,
  @DIRECTDEBITISREJECTED bit = 0,
  @PERCENTAGEBENEFITS xml = null,
  @ISGIFTAIDSPONSORSHIP bit = null,
  @LOCKBOXID uniqueidentifier = null,
  @LOCKBOXBATCHNUMBER nvarchar(100) = '',
  @LOCKBOXBATCHSEQUENCE int = 0,
  @PDACCOUNTSYSTEMID uniqueidentifier = null,
  @ISADJUSTMENT bit = null,
  @ADJUSTMENTDATE datetime = null,
  @ADJUSTMENTPOSTDATE datetime = null,
  @ADJUSTMENTREASON nvarchar(300) = null,
  @ADJUSTMENTREASONCODEID uniqueidentifier = null,
  @ADJUSTMENTPOSTSTATUSCODE tinyint = null,
  @REVENUEDEVELOPMENTFUNCTIONCODEID uniqueidentifier = null,
  @ORIGINALAPPLICATIONINFO nvarchar(60) = null,
  @RELOADRECOGNITION bit = 0,
  @RELOADSOLICITORS bit = 0,
  @LETTERS xml = null,
  @APPLICATIONBUSINESSUNITS xml = null,
  @NOTES xml = null,
  @TRANSACTIONCURRENCYID uniqueidentifier = null,
  @BASECURRENCYID uniqueidentifier = null,
  @BASEEXCHANGERATEID uniqueidentifier = null,
  @EXCHANGERATE decimal(20,8) = null,
  @GENERATEREFERENCENUMBER bit = 1,
  @UPDATEMATCHINGGIFTCLAIMS tinyint = 0,
  @UPDATEGIFTFEES bit = 0,
  @UPDATETRIBUTES bit = 0,
  @INSTALLMENTSCHEDULESEEDDATE datetime = null,
  @APPLYBYPERCENT bit= 0,
  @ADJPAYMENT_DATE datetime = null,
  @ADJPAYMENT_POSTDATE datetime = null,
  @ADJPAYMENT_REASONCODEID uniqueidentifier = null,
  @ADJPAYMENT_DETAILS nvarchar(255) = '',
  @SALE_LOWPRICE decimal(19,4) = 0,
  @SALE_MEDIANPRICE decimal(19,4) = 0,
  @SALE_HIGHPRICE decimal(19,4) = 0,
  @VENDORID nvarchar(50) = '',
  @INSTALLMENTAMOUNT money = 0
) 
as
begin
  set nocount on;
  declare @CURRENTDATE datetime;

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

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

  set @CURRENTDATE = GetDate();

  begin try

    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
        select 
          @STANDINGORDERREFERENCENUMBER = '',
          @GENERATEREFERENCENUMBER = 0
      if @GENERATEREFERENCENUMBER = 1
        select 
          @STANDINGORDERREFERENCENUMBER = ''      
    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 @TYPECODE <> 0
      set @LOCKBOXID = null;

    if not @LOCKBOXID is null
      if (@LOCKBOXBATCHNUMBER is null) or len(@LOCKBOXBATCHNUMBER)=0 or (@LOCKBOXBATCHSEQUENCE 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 @ISADJUSTMENT is null 
      set @ISADJUSTMENT = 0;
    if @ADJUSTMENTPOSTSTATUSCODE is null and (@ISADJUSTMENT <> 1 or @TYPECODE <> 0)
      set @ADJUSTMENTPOSTSTATUSCODE = 1;
    if @ADJPAYMENT_DETAILS is null
      set @ADJPAYMENT_DETAILS = '';

    declare @DEFAULTRECEIPTAMOUNT money

    if @BENEFITSWAIVED = 0
    begin
      declare @BENEFITAMOUNT money;
      select
        @BENEFITAMOUNT = coalesce(sum(
          case
            when BASECURRENCYID <> @TRANSACTIONCURRENCYID
              then dbo.UFN_CURRENCY_CONVERT(BASETOTALVALUE, BASEEXCHANGERATEID)
            else BASETOTALVALUE
          end
        ), 0)
      from
      (
        select
          REVBENEFITSFROMXML.QUANTITY * REVBENEFITSFROMXML.UNITVALUE as BASETOTALVALUE,
          BENEFIT.BASECURRENCYID as BASECURRENCYID,
          case
            when REVBENEFITSFROMXML.BASECURRENCYID <> @TRANSACTIONCURRENCYID
              then dbo.UFN_CURRENCYEXCHANGERATE_GETLATEST(REVBENEFITSFROMXML.BASECURRENCYID,@TRANSACTIONCURRENCYID, @DATE, 1, null)
            else null
          end as BASEEXCHANGERATEID
        from dbo.UFN_REVENUE_GETBENEFITS_3_FROMITEMLISTXML(@BENEFITS) REVBENEFITSFROMXML
        inner join dbo.BENEFIT on REVBENEFITSFROMXML.BENEFITID = BENEFIT.ID
      ) BENEFITSFROMXML;

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

      set @DEFAULTRECEIPTAMOUNT = @AMOUNT - @BENEFITAMOUNT;
    end
    else
      set @DEFAULTRECEIPTAMOUNT = @AMOUNT

    select @INSTALLMENTAMOUNT = PLEDGEINSTALLMENTOPTION.INSTALLMENTAMOUNT
    from dbo.FINANCIALTRANSACTION
    left outer join dbo.PLEDGEINSTALLMENTOPTION on FINANCIALTRANSACTION.ID = PLEDGEINSTALLMENTOPTION.ID 
    where FINANCIALTRANSACTION.ID = @ID and FINANCIALTRANSACTION.DELETEDON is null; 

    -- Check if the user has modified the receipt amount

    if @RECEIPTAMOUNT > 0 and @RECEIPTAMOUNT <> @DEFAULTRECEIPTAMOUNT
      set @USERMODIFIEDRECEIPTAMOUNT = 1
    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 @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 @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 or @INSTALLMENTFREQUENCYCODE = 5
    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 @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 not @APPLICATIONINFO is null and len(@APPLICATIONINFO) > 3      
      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 @STANDINGORDERSETUP is null
      set @STANDINGORDERSETUP = 0;
    if @GENERATEREFERENCENUMBER is null
      set @GENERATEREFERENCENUMBER = 1;
    if @UPDATETRIBUTES is null
      set @UPDATETRIBUTES = 0;

    if @CONSTITUENTID is null 
      select @CONSTITUENTID = @CONSTITUENTLOOKUPID;
    if @NEWCONSTITUENT is not null 
    begin
      declare @NEWCONSTITUENTID uniqueidentifier;
      if not exists(select ID from dbo.CONSTITUENT where ID = @CONSTITUENTID)
      begin
        exec USP_REVENUEBATCH_ADDNEWCONSTITUENTFROMXML @NEWCONSTITUENT, @CHANGEAGENTID, @NEWCONSTITUENTID output;
        set @CONSTITUENTID = @NEWCONSTITUENTID;
      end;
    end;

    /* 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 = @CONSTITUENTID output,
      @FINDERNUMBERISVALID = @FINDERNUMBERISVALID output,
      @SINGLEDESIGNATIONID = @SINGLEDESIGNATIONID output,
      @BATCHID = @BATCHID;

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

    -- Don't save an account system for a recurring gift

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

    -- 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 item is adjustable set ADJUSTMENTPOSTSTATUSCODE AND ADJUSTMENTREASONCODEID to required

    if @ISADJUSTMENT = 1
      begin
        if @ADJUSTMENTDATE is null
           raiserror('BBERR_ADJUSTMENTDATEREQUIRED', 13, 1);
        if @ADJUSTMENTPOSTDATE is null and @ADJUSTMENTPOSTSTATUSCODE <> 2
           raiserror('BBERR_ADJUSTMENTPOSTDATEREQUIRED', 13, 1); 
        if @ADJUSTMENTREASONCODEID is null
           raiserror('BBERR_ADJUSTMENTREASONCODEIDREQUIRED', 13, 1);
        if @ADJUSTMENTPOSTSTATUSCODE is null and @TYPECODE = 0
           raiserror('BBERR_ADJUSTMENTPOSTSTATUSCODEREQUIRED', 13, 1);          
      end    

    -- if this is a single split payment and the user changes it

    declare @REVENUESPLITID1 uniqueidentifier;
    set @REVENUESPLITID1 = @REVENUESPLITID
    declare @APPLICATIONINFO1 nvarchar(60);
    if not @ORIGINALAPPLICATIONINFO is null 
     begin
      if len(@APPLICATIONINFO) > 3
         begin
          set @APPLICATIONINFO1 = substring(@APPLICATIONINFO, 1, 36)  
         end
      else
        begin
          set @APPLICATIONINFO1 = @APPLICATIONINFO
        end
      if @APPLICATIONINFO1 <> @ORIGINALAPPLICATIONINFO           
         set @REVENUESPLITID = null      
     end  

     if @UPDATEMATCHINGGIFTCLAIMS is null
      set @UPDATEMATCHINGGIFTCLAIMS = 0; 

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

    insert into dbo.BATCHREVENUE
    (
      ID,BATCHID, REVENUEID, REVENUESPLITID, 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, ISADJUSTMENT, ADJUSTMENTDATE, ADJUSTMENTPOSTDATE, ADJUSTMENTREASON, ADJUSTMENTREASONCODEID, ADJUSTMENTPOSTSTATUSCODE, 
      REVENUEDEVELOPMENTFUNCTIONCODEID, ORIGINALAPPLICATIONINFO, RELOADRECOGNITION, RELOADSOLICITORS, BASECURRENCYID, TRANSACTIONCURRENCYID,
      BASEEXCHANGERATEID, EXCHANGERATE, USESYSTEMGENERATEDREFERENCENUMBER, UPDATEMATCHINGGIFTCLAIMSCODE, UPDATEGIFTFEES, UPDATETRIBUTES, INSTALLMENTSCHEDULESEEDDATE,APPLYBYPERCENT,
      ADJPAYMENT_DATE, ADJPAYMENT_POSTDATE, ADJPAYMENT_REASONCODEID, ADJPAYMENT_DETAILS, SALE_LOWPRICE, SALE_MEDIANPRICE, SALE_HIGHPRICE, VENDORID, INSTALLMENTAMOUNT,
      CARDHOLDERNAME, CREDITTYPECODEID, CREDITCARDPARTIALNUMBER, EXPIRESON, STANDINGORDERREFERENCENUMBER
    )
    values 
    (
      @ID,@BATCHID, @REVENUEID, @REVENUESPLITID, @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, coalesce(@DECLINESGIFTAID, 0), @DDISOURCECODEID, @DDISOURCEDATE, 
      @ISCOVENANT, @AMOUNTFORVAT, @VATTAXRATEID, @VATAMOUNT, @APPLICATIONINFO, coalesce(rtrim(ltrim(@REVENUELOOKUPID)),''), @MGGENERATED, @MGALTERED, @PAYINGPENDINGREVENUEID,
      @GIFTINKINDITEMNAME, @GIFTINKINDDISPOSITIONCODE, @GIFTINKINDNUMBEROFUNITS, @GIFTINKINDFAIRMARKETVALUE, coalesce(@DIRECTDEBITISREJECTED,0), coalesce(@ISGIFTAIDSPONSORSHIP, 0),
      @LOCKBOXID, @LOCKBOXBATCHNUMBER, @LOCKBOXBATCHSEQUENCE, @PDACCOUNTSYSTEMID, @ISADJUSTMENT, @ADJUSTMENTDATE, @ADJUSTMENTPOSTDATE, @ADJUSTMENTREASON, @ADJUSTMENTREASONCODEID, @ADJUSTMENTPOSTSTATUSCODE, 
      @REVENUEDEVELOPMENTFUNCTIONCODEID, @ORIGINALAPPLICATIONINFO, @RELOADRECOGNITION, @RELOADSOLICITORS, @BASECURRENCYID, @TRANSACTIONCURRENCYID,
      @BASEEXCHANGERATEID, @EXCHANGERATE, @GENERATEREFERENCENUMBER,@UPDATEMATCHINGGIFTCLAIMS, @UPDATEGIFTFEES, @UPDATETRIBUTES, @INSTALLMENTSCHEDULESEEDDATE,@APPLYBYPERCENT,
      @ADJPAYMENT_DATE, @ADJPAYMENT_POSTDATE, @ADJPAYMENT_REASONCODEID, @ADJPAYMENT_DETAILS, @SALE_LOWPRICE, @SALE_MEDIANPRICE, @SALE_HIGHPRICE, isnull(@VENDORID, ''), @INSTALLMENTAMOUNT,
      @CARDHOLDERNAME, @CREDITTYPECODEID, @CREDITCARDNUMBER, @EXPIRESON, @STANDINGORDERREFERENCENUMBER
    );

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

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

    declare @APPLICATIONCODE tinyint;
    declare @SINGLEAPPLICATIONID uniqueidentifier;
    if @APPLICATIONINFO is not null
      if len(@APPLICATIONINFO) > 0
        if len(@APPLICATIONINFO) = 3
          set @APPLICATIONCODE = cast(substring(@APPLICATIONINFO, 3, 1) as tinyint);
        else if len(@APPLICATIONINFO) > 3
          set @SINGLEAPPLICATIONID = cast(substring(@APPLICATIONINFO, 1, 36) as uniqueidentifier);

    --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 @ADDITIONALAPPLICATIONCOUNT int;
    select @ADDITIONALAPPLICATIONCOUNT = count(*) from @ADDITIONALAPPLICATIONSSTREAM.nodes('/ADDITIONALAPPLICATIONSSTREAM/ITEM') T(c)                
    declare @SPLITCOUNT int;
    select @SPLITCOUNT = count(*) from @SPLITS.nodes('/SPLITS/ITEM') T(c)  
    declare @REVENUESTREAMSCOUNT int;
    select @REVENUESTREAMSCOUNT = count(*) from @REVENUESTREAMS.nodes('/REVENUESTREAMS/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
      declare @TEMPAPPLICATIONRECOGNITIONS table
      (
        APPLICATIONID uniqueidentifier,
        APPLICATIONTYPECODE tinyint,
        ADDITIONALAPPLICATIONTYPECODE tinyint,
        ADDITIONALAPPLICATIONDESIGNATIONID uniqueidentifier,
        APPLICATIONAMOUNT money,
        RECOGNITIONS xml,
        ADDITIONALAPPLICATIONDECLINESGIFTAID bit,
        ADDITIONALAPPLICATIONSPONSORSHIPID uniqueidentifier
      );

      declare @TEMPADDITIONALAPPLICATIONS table
      (
        ID uniqueidentifier,
        TYPECODE tinyint,
        DESIGNATIONID uniqueidentifier,
        APPLIED money,
        DECLINESGIFTAID bit,
        SPONSORSHIPID uniqueidentifier
      )

      insert into @TEMPADDITIONALAPPLICATIONS(ID,TYPECODE,DESIGNATIONID,APPLIED,DECLINESGIFTAID,SPONSORSHIPID)
        select 
          T.c.value('(ID)[1]', 'uniqueidentifier') ID,
          T.c.value('(TYPECODE)[1]','tinyint') TYPECODE, 
          T.c.value('(DESIGNATIONID)[1]','uniqueidentifier') DESIGNATIONID,
          T.c.value('(APPLIED)[1]','money') APPLIED,
          T.c.value('(DECLINESGIFTAID)[1]','bit') DECLINESGIFTAID,
          coalesce(T.c.value('(SPONSORSHIPID)[1]','uniqueidentifier'),T.c.value('(REVENUESPLITID)[1]','uniqueidentifier'))SPONSORSHIPID
        from @ADDITIONALAPPLICATIONSSTREAM.nodes('/ADDITIONALAPPLICATIONSSTREAM/ITEM') T(c)

      declare @RECOGNITIONSDEFAULT_APPLICATIONID uniqueidentifier
      declare @RECOGNITIONSDEFAULT_ADDITIONALAPPLICATIONTYPECODE tinyint
      declare @RECOGNITIONSDEFAULT_ADDITIONALAPPLICATIONDESIGNATIONID uniqueidentifier
      declare @RECOGNITIONSDEFAULT_APPLICATIONAMOUNT money
      declare @RECOGNITIONSDEFAULT_APPLICATIONDESCRIPTION nvarchar
      declare @RECOGNITIONSDEFAULT_COLLECTIONDESCRIPTION nvarchar
      declare @RECOGNITIONSDEFAULT_RECOGNITIONS xml
      declare @RECOGNITIONSDEFAULT_ADDITIONALAPPLICATIONDECLINESGIFTAID bit
      declare @RECOGNITIONSDEFAULT_ADDITIONALAPPLICATIONSPONSORSHIPID uniqueidentifier

      declare @TEMPRECOGNITIONS xml;

      declare ADDITIONALAPPLICATIONS_CURSOR cursor local fast_forward for 
          select ID, TYPECODE, DESIGNATIONID, APPLIED, DECLINESGIFTAID,SPONSORSHIPID from @TEMPADDITIONALAPPLICATIONS

      open ADDITIONALAPPLICATIONS_CURSOR;
      fetch next from ADDITIONALAPPLICATIONS_CURSOR into @RECOGNITIONSDEFAULT_APPLICATIONID, 
                                                         @RECOGNITIONSDEFAULT_ADDITIONALAPPLICATIONTYPECODE, 
                                                         @RECOGNITIONSDEFAULT_ADDITIONALAPPLICATIONDESIGNATIONID, 
                                                         @RECOGNITIONSDEFAULT_APPLICATIONAMOUNT,
                                                         @RECOGNITIONSDEFAULT_ADDITIONALAPPLICATIONDECLINESGIFTAID,
                                                         @RECOGNITIONSDEFAULT_ADDITIONALAPPLICATIONSPONSORSHIPID;
      while @@FETCH_STATUS = 0
      begin
        select @TEMPRECOGNITIONS =
        (
          select CONSTITUENTID, REVENUERECOGNITIONTYPECODEID, AMOUNT, @DATE as [EFFECTIVEDATE]
          from UFN_REVENUEBATCH_GETRECOGNITIONDEFAULTS(@GIVENANONYMOUSLY, @CONSTITUENTID, @RECOGNITIONSDEFAULT_APPLICATIONAMOUNT, @DATE, null)
          for xml raw('ITEM'), type, elements,  binary base64
        );

        insert into @TEMPAPPLICATIONRECOGNITIONS(APPLICATIONID, APPLICATIONTYPECODE, ADDITIONALAPPLICATIONTYPECODE, ADDITIONALAPPLICATIONDESIGNATIONID, APPLICATIONAMOUNT, RECOGNITIONS, ADDITIONALAPPLICATIONDECLINESGIFTAID, ADDITIONALAPPLICATIONSPONSORSHIPID)
        values(@RECOGNITIONSDEFAULT_APPLICATIONID, null, @RECOGNITIONSDEFAULT_ADDITIONALAPPLICATIONTYPECODE, @RECOGNITIONSDEFAULT_ADDITIONALAPPLICATIONDESIGNATIONID, @RECOGNITIONSDEFAULT_APPLICATIONAMOUNT, @TEMPRECOGNITIONS, @RECOGNITIONSDEFAULT_ADDITIONALAPPLICATIONDECLINESGIFTAID, @RECOGNITIONSDEFAULT_ADDITIONALAPPLICATIONSPONSORSHIPID );

        fetch next from ADDITIONALAPPLICATIONS_CURSOR into @RECOGNITIONSDEFAULT_APPLICATIONID, 
                                                           @RECOGNITIONSDEFAULT_ADDITIONALAPPLICATIONTYPECODE, 
                                                           @RECOGNITIONSDEFAULT_ADDITIONALAPPLICATIONDESIGNATIONID, 
                                                           @RECOGNITIONSDEFAULT_APPLICATIONAMOUNT,
                                                           @RECOGNITIONSDEFAULT_ADDITIONALAPPLICATIONDECLINESGIFTAID,
                                                           @RECOGNITIONSDEFAULT_ADDITIONALAPPLICATIONSPONSORSHIPID;
      end

      close ADDITIONALAPPLICATIONS_CURSOR;
      deallocate ADDITIONALAPPLICATIONS_CURSOR;

      set @APPLICATIONRECOGNITIONS = 
      (
         select APPLICATIONID, APPLICATIONTYPECODE, ADDITIONALAPPLICATIONTYPECODE,
                ADDITIONALAPPLICATIONDESIGNATIONID, APPLICATIONAMOUNT, RECOGNITIONS,
                ADDITIONALAPPLICATIONDECLINESGIFTAID, ADDITIONALAPPLICATIONSPONSORSHIPID
         from @TEMPAPPLICATIONRECOGNITIONS
         for xml raw('ITEM'), type, elements, root('APPLICATIONRECOGNITIONS'), binary base64
      );
    end

    -- deal with payment payment application recognitions/solicitors

    if @REVENUESTREAMSCOUNT > 0
       exec dbo.USP_REVENUEUPDATEBATCH_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 not null and @ADDITIONALAPPLICATIONCOUNT >= 1 and @TYPECODE = 0
      exec dbo.USP_REVENUEUPDATEBATCH_ADDRECOGNITIONSANDSOLICITORS @ID, @APPLICATIONRECOGNITIONS, @APPLICATIONSOLICITORS, @ADDITIONALAPPLICATIONSSTREAM, @APPLICATIONCODE, @CHANGEAGENTID, @CURRENTDATE;
    else if @APPLICATIONCODE is null and @SINGLEAPPLICATIONID is null and @ADDITIONALAPPLICATIONCOUNT >= 1 and @TYPECODE = 0                      
      exec dbo.USP_REVENUEUPDATEBATCH_ADDRECOGNITIONSANDSOLICITORS @ID, @APPLICATIONRECOGNITIONS, @APPLICATIONSOLICITORS, @ADDITIONALAPPLICATIONSSTREAM, NULL, @CHANGEAGENTID, @CURRENTDATE;
    else
      exec dbo.USP_REVENUEUPDATEBATCH_ADDRECOGNITIONSANDSOLICITORS @ID, @APPLICATIONRECOGNITIONS, @APPLICATIONSOLICITORS, @SPLITS, 0, @CHANGEAGENTID, @CURRENTDATE;

    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 @LETTERS is null
      exec dbo.USP_REVENUEUPDATEBATCH_GETLETTERS_ADDFROMXML @ID, @LETTERS, @CHANGEAGENTID;

    if not @NOTES is null
      exec dbo.USP_REVENUEUPDATEBATCH_GETNOTES_ADDFROMXML @ID, @NOTES, @CHANGEAGENTID;

    if @BENEFITS is null and @PERCENTAGEBENEFITS is null and @USERMODIFIEDBENEFITS = 0 and not @APPEALID is null
    begin
      declare @BENEFITID uniqueidentifier;
      declare @QUANTITY int;
      declare @BENSEQUENCE int;
      declare @UNITVALUE int;
      declare @VALUEPERCENT numeric(20, 2);
      declare @USEPERCENT bit;
      declare @BENEFITCURRENCYID uniqueidentifier;
      declare @BENEFITADDED bit = 0;

      -- TODO:  Rework some of this functionality when APPEALBENEFIT is updated for multicurrency.

      declare BENEFITCURSOR cursor local fast_forward for
      select 
          BENEFITDETAILS.[BENEFITID],
          BENEFITDETAILS.[QUANTITY],
          BENEFITDETAILS.[SEQUENCE],
          BENEFITDETAILS.[VALUE],
          BENEFITDETAILS.[VALUEPERCENT],
          BENEFITDETAILS.[USEPERCENT],
          BENEFIT.BASECURRENCYID [BENEFITCURRENCYID]
        from dbo.UFN_APPEAL_GETBENEFITDETAILS_ALL_2(@APPEALID, @AMOUNT, @TRANSACTIONCURRENCYID, @BASECURRENCYID) BENEFITDETAILS
        left join dbo.BENEFIT on BENEFITDETAILS.BENEFITID = BENEFIT.ID
        order by USEPERCENT, SEQUENCE

      open BENEFITCURSOR;
      fetch next from BENEFITCURSOR into @BENEFITID, @QUANTITY, @BENSEQUENCE, @UNITVALUE, @VALUEPERCENT, @USEPERCENT, @BENEFITCURRENCYID

      while (@@FETCH_STATUS = 0)
      begin
        if @USEPERCENT = 0
        begin
          insert into [BATCHREVENUEBENEFIT]
          (
            [BATCHREVENUEID],
            [BENEFITID],
            [QUANTITY],
            [SEQUENCE],
            [UNITVALUE],
            [BENEFITCURRENCYID],
            ADDEDBYID,
            CHANGEDBYID,
            DATEADDED,
            DATECHANGED
          )
          values
          (
            @ID,
            @BENEFITID,
            @QUANTITY,
            @BENSEQUENCE,
            @UNITVALUE,
            @BENEFITCURRENCYID,
            @CHANGEAGENTID,
            @CHANGEAGENTID,
            @CURRENTDATE,
            @CURRENTDATE
          );

          if @BENEFITCURRENCYID <> @TRANSACTIONCURRENCYID
          begin
              declare @BENEFITEXCHANGERATEID uniqueidentifier;
              set @BENEFITEXCHANGERATEID = dbo.UFN_CURRENCYEXCHANGERATE_GETLATEST(@BENEFITCURRENCYID,@TRANSACTIONCURRENCYID, @DATE, 1, null)

              set @DEFAULTRECEIPTAMOUNT = @DEFAULTRECEIPTAMOUNT - dbo.UFN_CURRENCY_CONVERT(@QUANTITY * @UNITVALUE, @BENEFITEXCHANGERATEID);
          end
          else
              set @DEFAULTRECEIPTAMOUNT = @DEFAULTRECEIPTAMOUNT - (@QUANTITY * @UNITVALUE)

        end
        else
        begin
          insert into [BATCHREVENUEBENEFITPCT]
          (
            [BATCHREVENUEID],
            [BENEFITID],
            [SEQUENCE],
            [VALUEPERCENT],
            [PERCENTAPPLICABLEAMOUNT],
            ADDEDBYID,
            CHANGEDBYID,
            DATEADDED,
            DATECHANGED
          )
          values
          (
            @ID,
            @BENEFITID,
            @BENSEQUENCE,
            @VALUEPERCENT,
            @DEFAULTRECEIPTAMOUNT,
            @CHANGEAGENTID,
            @CHANGEAGENTID,
            @CURRENTDATE,
            @CURRENTDATE
          )

          set @DEFAULTRECEIPTAMOUNT = @DEFAULTRECEIPTAMOUNT - (@DEFAULTRECEIPTAMOUNT * @VALUEPERCENT/100);
        end

        set @BENEFITADDED = 1;

        fetch next from BENEFITCURSOR into @BENEFITID, @QUANTITY, @SEQUENCE, @UNITVALUE, @VALUEPERCENT, @USEPERCENT, @BENEFITCURRENCYID;
      end

      close BENEFITCURSOR;
      deallocate BENEFITCURSOR;

      if @USERMODIFIEDRECEIPTAMOUNT = 0 and @BENEFITADDED = 1
        update dbo.BATCHREVENUE 
        set RECEIPTAMOUNT = @DEFAULTRECEIPTAMOUNT, 
          CHANGEDBYID = @CHANGEAGENTID, 
          DATECHANGED = @CURRENTDATE
        where ID = @ID;
    end

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

    declare @INSTALLMENTSBEFORETRIM xml = @INSTALLMENTS
    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_REVENUEUPDATEBATCH_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

      insert into dbo.BATCHREVENUEINSTALLMENTSPLITPAYMENT
      (
        ID,
        BATCHREVENUEINSTALLMENTSPLITID,
        AMOUNT,
        PAYMENTID,
        DATEADDED,
        DATECHANGED,
        ADDEDBYID,
        CHANGEDBYID
      )
      select
        isnull(T.c.value('(ID)[1]','uniqueidentifier'), NEWID()),
        T.c.value('(../../ID)[1]','uniqueidentifier') as BATCHINSTALLMENTSPLITID,
        T.c.value('(AMOUNT)[1]','money') as AMOUNT,
        T.c.value('(LINEITEMID)[1]','uniqueidentifier') as PAYMENTID,
        @CURRENTDATE,
        @CURRENTDATE,
        @CHANGEAGENTID,
        @CHANGEAGENTID
      from
        @INSTALLMENTSBEFORETRIM.nodes('/INSTALLMENTS/ITEM/INSTALLMENTSPLITS/ITEM/INSTALLMENTSPLITPAYMENTS/ITEM') T(c)

      insert into dbo.BATCHREVENUEINSTALLMENTSPLITWRITEOFF
      (
        ID,
        BATCHREVENUEINSTALLMENTSPLITID,
        AMOUNT,
        WRITEOFFID,
        DATEADDED,
        DATECHANGED,
        ADDEDBYID,
        CHANGEDBYID
      )
      select
        isnull(T.c.value('(ID)[1]','uniqueidentifier'), NEWID()),
        T.c.value('(../../ID)[1]','uniqueidentifier') as BATCHINSTALLMENTSPLITID,
        T.c.value('(AMOUNT)[1]','money') as AMOUNT,
        T.c.value('(TRANSACTIONID)[1]','uniqueidentifier') as WRITEOFFID,
        @CURRENTDATE,
        @CURRENTDATE,
        @CHANGEAGENTID,
        @CHANGEAGENTID
      from
        @INSTALLMENTSBEFORETRIM.nodes('/INSTALLMENTS/ITEM/INSTALLMENTSPLITS/ITEM/INSTALLMENTSPLITWRITEOFFS/ITEM') T(c)
    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

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

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

  end try

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

  return 0;
end