USP_DATAFORMTEMPLATE_EDIT_REVENUEUPDATEBATCHROW_9

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

  @SOLICITORS xml,
  @BENEFITS xml,
  @FINDERNUMBER bigint,
  @SOURCECODE nvarchar(60),
  @APPEALID uniqueidentifier,
  @FINDERNUMBERISVALID bit,
  @USERMODIFIEDBENEFITS bit,
  @BENEFITSWAIVED bit,
  @POSTDATE datetime,
  @POSTSTATUSCODE tinyint,
  @SENDPLEDGEREMINDER bit,
  @SALEDATE datetime,
  @SALEAMOUNT money,
  @BROKERFEE money,
  @SALEPOSTSTATUSCODE tinyint,
  @SALEPOSTDATE datetime,
  @NOTETITLE nvarchar(50),
  @NOTEAUTHORID uniqueidentifier,
  @NOTEDATEENTERED datetime,
  @NOTETYPECODEID uniqueidentifier,
  @NOTETEXTNOTE nvarchar(max),
  @GIVENANONYMOUSLY bit,
  @GIVENANONYMOUSLYDEFAULTEDFORCONSTITUENTID uniqueidentifier,
  @USERMODIFIEDRECEIPTAMOUNT bit,
  @PLEDGESUBTYPEID uniqueidentifier,
  @REJECTIONCODEID uniqueidentifier,
  @CONSTITUENTLOOKUPID uniqueidentifier,
  @MAILINGID uniqueidentifier,
  @CHANNELCODEID uniqueidentifier,
  @INSTALLMENTS xml,
  @PAYMENTFORPLEDGEAMOUNT money,
  --Recognitions field is no longer in use (APPLICATIONRECOGNITIONS is current)

  @RECOGNITIONS xml,
  @DIDRECOGNITIONSDEFAULT bit,
  @TRIBUTES xml,
  @UNAPPLIEDMATCHINGGIFTSPLITS xml,   
  @UNAPPLIEDMATCHINGGIFTAMOUNT money,
  @RECEIPTTYPECODE tinyint,
  @NEWCONSTITUENT xml,
  @OTHERPAYMENTMETHODCODEID uniqueidentifier,
  @LETTERCODEID uniqueidentifier,
  @ACKNOWLEDGEDATE datetime,
  @REFERENCE nvarchar(255),
  @CATEGORYCODEID uniqueidentifier,
  @ACKNOWLEDGEEID uniqueidentifier,
  @APPLICATIONINFO nvarchar(60),
  @OTHERTYPECODEID uniqueidentifier,
  @OPPORTUNITYID uniqueidentifier,
  @DIRECTDEBITRESULTCODE nvarchar(10),
  @LOWPRICE decimal(19,4),
  @HIGHPRICE decimal(19,4),
  @NUMBEROFUNITSSOLD decimal(20,3),
  @USERMODIFIEDNUMBEROFUNITSSOLD bit,
  @CREDITCARDTOKEN uniqueidentifier,
  @REJECTIONMESSAGE nvarchar(250),
  -- PARTIALCREDITCARDNUMBER is used to support import and 

  -- credit card - last 4 digits recurring gifts

  @PARTIALCREDITCARDNUMBER nvarchar(4),
  @TAXDECLARATIONS xml,
  @STANDINGORDERCONSTITUENTACCOUNTID uniqueidentifier, 
  @STANDINGORDERREFERENCENUMBER nvarchar(20), 
  @STANDINGORDERREFERENCEDATE dbo.UDT_FUZZYDATE,
  @STANDINGORDERSETUP bit,
  @STANDINGORDERSETUPDATE datetime,
  @TRANSACTIONID uniqueidentifier,
  @ISTRANSIENTCARD bit,
  @DECLINESGIFTAID bit,
  @DDISOURCECODEID uniqueidentifier,
  @DDISOURCEDATE date,
  @ISCOVENANT bit,
  @AMOUNTFORVAT money,
  @VATTAXRATEID uniqueidentifier,
  @VATAMOUNT money,
  @ADDITIONALAPPLICATIONSSTREAM xml,
  @REVENUELOOKUPID nvarchar(100),
  @APPLICATIONSOLICITORS xml,
  @APPLICATIONRECOGNITIONS xml,
  @CURRENTAPPUSERID uniqueidentifier = null,
  @MATCHINGGIFTS xml,
  @MGGENERATED bit,
  @MGALTERED bit,
  @PAYINGPENDINGREVENUEID uniqueidentifier,
  @GIFTINKINDITEMNAME nvarchar(100),
  @GIFTINKINDDISPOSITIONCODE tinyint,
  @GIFTINKINDNUMBEROFUNITS int,
  @GIFTINKINDFAIRMARKETVALUE money,
  @DIRECTDEBITISREJECTED bit,
  @PERCENTAGEBENEFITS xml,
  @ISGIFTAIDSPONSORSHIP bit,
  @LOCKBOXID uniqueidentifier,
  @LOCKBOXBATCHNUMBER nvarchar(100),
  @LOCKBOXBATCHSEQUENCE int,
  @PDACCOUNTSYSTEMID uniqueidentifier,
  @ISADJUSTMENT bit,
  @ADJUSTMENTDATE datetime ,
  @ADJUSTMENTPOSTDATE datetime,
  @ADJUSTMENTREASON nvarchar(300),
  @ADJUSTMENTREASONCODEID uniqueidentifier,
  @ADJUSTMENTPOSTSTATUSCODE tinyint,
  @REVENUEDEVELOPMENTFUNCTIONCODEID uniqueidentifier,
  @ORIGINALAPPLICATIONINFO nvarchar(60),
  @RELOADRECOGNITION bit,
  @RELOADSOLICITORS bit,
  @LETTERS xml,
  @APPLICATIONBUSINESSUNITS xml,
  @NOTES xml,
  @TRANSACTIONCURRENCYID uniqueidentifier,
  @BASECURRENCYID uniqueidentifier,
  @BASEEXCHANGERATEID uniqueidentifier,
  @EXCHANGERATE decimal(20,8),
  @GENERATEREFERENCENUMBER bit,
  @UPDATEMATCHINGGIFTCLAIMS tinyint,
  @UPDATEGIFTFEES bit,
  @UPDATETRIBUTES bit,
  @INSTALLMENTSCHEDULESEEDDATE datetime,
  @APPLYBYPERCENT bit,
  @ADJPAYMENT_DATE datetime,
  @ADJPAYMENT_POSTDATE datetime,
  @ADJPAYMENT_REASONCODEID uniqueidentifier,
  @ADJPAYMENT_DETAILS nvarchar(255),
  @SALE_LOWPRICE decimal(19,4),
  @SALE_MEDIANPRICE decimal(19,4),
  @SALE_HIGHPRICE decimal(19,4),
  @VENDORID nvarchar(50),
  @INSTALLMENTAMOUNT money
)
as
  set nocount on;
  declare @CURRENTDATE datetime;
  declare @BATCHID uniqueidentifier;

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

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

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

  if not @CHANGEAGENTID is null
    set CONTEXT_INFO @CHANGEAGENTID;

  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
      if @GENERATEREFERENCENUMBER is null
        select 
          @STANDINGORDERREFERENCENUMBER = '',
          @GENERATEREFERENCENUMBER = 0
      if @GENERATEREFERENCENUMBER = 1
        select 
          @STANDINGORDERREFERENCENUMBER = ''

      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;
    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 @USERMODIFIEDNUMBEROFUNITSSOLD is null
      set @USERMODIFIEDNUMBEROFUNITSSOLD = 0;
    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 @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 @PAYMENTFORPLEDGEAMOUNT is null
      set @PAYMENTFORPLEDGEAMOUNT = 0;
    if @DIDRECOGNITIONSDEFAULT is null
      set @DIDRECOGNITIONSDEFAULT = 0;
    if @STANDINGORDERSETUP is null
      set @STANDINGORDERSETUP = 0;
    if @AMOUNTFORVAT is null
      set @AMOUNTFORVAT = 0;
    if @VATAMOUNT is null
      set @VATAMOUNT = 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 @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 @FINDERNUMBER is null
      set @FINDERNUMBER = 0;

    if cast(@SPLITS as nvarchar(max)) = '' 
      set @SPLITS = null;

    if not @SPLITS is null
      set @SINGLEDESIGNATIONID = null;
    if not @APPLICATIONINFO is null and len(@APPLICATIONINFO) > 3      
      set @SINGLEDESIGNATIONID = null;

    if @PAYMENTMETHODCODE is null
      set @PAYMENTMETHODCODE = 255;
    if @UPDATETRIBUTES is null
      set @UPDATETRIBUTES = 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 @RECEIPTTYPECODE is null
  set @RECEIPTTYPECODE = 255;

    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 @STANDINGORDERSETUP is null
      set @STANDINGORDERSETUP = 0;
    if @GENERATEREFERENCENUMBER is null
      set @GENERATEREFERENCENUMBER = 1;

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

    /* Lookup and set all possible marketing data (via output params) from the data that was specified. */
    select
      @BATCHID = [BATCHID]
    from
      dbo.[BATCHREVENUE]
    where
      [ID] = @ID;

    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;

    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

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

    --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 @EXPIRESON = '00000000';
    end

    update dbo.BATCHREVENUE
      set REVENUEID = @REVENUEID,
        REVENUESPLITID = @REVENUESPLITID,
        CONSTITUENTID = @CONSTITUENTID,
        TYPECODE = @TYPECODE,
        DATE = @DATE,
        PAYMENTMETHODCODE = @PAYMENTMETHODCODE,
        DONOTACKNOWLEDGE = @DONOTACKNOWLEDGE,
        CHECKDATE = @CHECKDATE,
        CHECKNUMBER = @CHECKNUMBER,
        REFERENCEDATE = @REFERENCEDATE,
        REFERENCENUMBER = @REFERENCENUMBER,
        CREDITCARDID = @CREDITCARDID,
        AUTHORIZATIONCODE = @AUTHORIZATIONCODE,
        CONSTITUENTACCOUNTID = @CONSTITUENTACCOUNTID, 
        AMOUNT = @AMOUNT, 
        RECEIPTAMOUNT = @RECEIPTAMOUNT, 
        DONOTRECEIPT = @DONOTRECEIPT,
        SEQUENCE = @SEQUENCE,
        INSTALLMENTFREQUENCYCODE = @INSTALLMENTFREQUENCYCODE,
        INSTALLMENTSTARTDATE = @INSTALLMENTSTARTDATE,
        INSTALLMENTENDDATE = @INSTALLMENTENDDATE,
        NUMBEROFINSTALLMENTS = @NUMBEROFINSTALLMENTS,
        APPLYTOSHOWNFORCONSTITUENTID = @APPLYTOSHOWNFORCONSTITUENTID,
        FINDERNUMBER = @FINDERNUMBER,
        SOURCECODE = @SOURCECODE,
        APPEALID = @APPEALID,
        FINDERNUMBERISVALID = @FINDERNUMBERISVALID,
        USERMODIFIEDBENEFITS = @USERMODIFIEDBENEFITS,
        BENEFITSWAIVED = @BENEFITSWAIVED,
        POSTDATE = @POSTDATE,
        POSTSTATUSCODE = @POSTSTATUSCODE,
        SENDPLEDGEREMINDER = @SENDPLEDGEREMINDER,
        SALEDATE = @SALEDATE,
        SALEAMOUNT = @SALEAMOUNT,
        BROKERFEE = @BROKERFEE,
        SALEPOSTSTATUSCODE = @SALEPOSTSTATUSCODE,
        SALEPOSTDATE = @SALEPOSTDATE,
        ISSUER = @ISSUER,
        NUMBEROFUNITS = @NUMBEROFUNITS,
        SYMBOL = @SYMBOL,
        MEDIANPRICE = @MEDIANPRICE,
        GIFTINKINDSUBTYPECODEID = @GIFTINKINDSUBTYPECODEID,
        PROPERTYSUBTYPECODEID = @PROPERTYSUBTYPECODEID,
        NOTETITLE = @NOTETITLE,
        NOTEAUTHORID = @NOTEAUTHORID,
        NOTEDATEENTERED = @NOTEDATEENTERED,
        NOTETYPECODEID = @NOTETYPECODEID,
        NOTETEXTNOTE = @NOTETEXTNOTE,
        GIVENANONYMOUSLY = @GIVENANONYMOUSLY,
        GIVENANONYMOUSLYDEFAULTEDFORCONSTITUENTID = @GIVENANONYMOUSLYDEFAULTEDFORCONSTITUENTID,
        USERMODIFIEDRECEIPTAMOUNT = @USERMODIFIEDRECEIPTAMOUNT,
        PLEDGESUBTYPEID = @PLEDGESUBTYPEID,
        REJECTIONMESSAGE = @REJECTIONMESSAGE,
        MAILINGID = @MAILINGID,
        CHANNELCODEID = @CHANNELCODEID,
        CHANGEDBYID = @CHANGEAGENTID,
        DATECHANGED = @CURRENTDATE,
        PAYMENTFORPLEDGEAMOUNT = @PAYMENTFORPLEDGEAMOUNT,
        RECEIPTTYPECODE = @RECEIPTTYPECODE,
        OTHERPAYMENTMETHODCODEID = @OTHERPAYMENTMETHODCODEID,
        LETTERCODEID = @LETTERCODEID,
        ACKNOWLEDGEDATE = @ACKNOWLEDGEDATE,
        REFERENCE = @REFERENCE,
        GLREVENUECATEGORYMAPPINGID = @CATEGORYCODEID,
        ACKNOWLEDGEEID = @ACKNOWLEDGEEID,
        OTHERTYPECODEID = @OTHERTYPECODEID,
        OPPORTUNITYID = @OPPORTUNITYID,
        DIRECTDEBITRESULTCODE = @DIRECTDEBITRESULTCODE,
        LOWPRICE = @LOWPRICE,
        HIGHPRICE = @HIGHPRICE,
        NUMBEROFUNITSSOLD = @NUMBEROFUNITSSOLD,
        USERMODIFIEDNUMBEROFUNITSSOLD = @USERMODIFIEDNUMBEROFUNITSSOLD,
        STANDINGORDERSETUP = @STANDINGORDERSETUP,
        STANDINGORDERSETUPDATE = @STANDINGORDERSETUPDATE,
        TRANSACTIONID = @TRANSACTIONID,
        DECLINESGIFTAID = coalesce(@DECLINESGIFTAID, 0),
        DDISOURCECODEID = @DDISOURCECODEID,
        DDISOURCEDATE = @DDISOURCEDATE,
        ISCOVENANT = @ISCOVENANT,
        AMOUNTFORVAT = @AMOUNTFORVAT,
        VATTAXRATEID = @VATTAXRATEID,
        VATAMOUNT = @VATAMOUNT,
        APPLICATIONINFO = @APPLICATIONINFO,
        REVENUELOOKUPID = coalesce(rtrim(ltrim(@REVENUELOOKUPID)),''),
        MGGENERATED = @MGGENERATED,
        MGALTERED = @MGALTERED,
        PAYINGPENDINGREVENUEID = @PAYINGPENDINGREVENUEID,
        GIFTINKINDITEMNAME = @GIFTINKINDITEMNAME,
        GIFTINKINDDISPOSITIONCODE = @GIFTINKINDDISPOSITIONCODE,
        GIFTINKINDNUMBEROFUNITS = @GIFTINKINDNUMBEROFUNITS,
        GIFTINKINDFAIRMARKETVALUE = @GIFTINKINDFAIRMARKETVALUE,
        DIRECTDEBITISREJECTED = coalesce(@DIRECTDEBITISREJECTED,0),
        ISGIFTAIDSPONSORSHIP = coalesce(@ISGIFTAIDSPONSORSHIP, 0),
        LOCKBOXID = @LOCKBOXID,
        LOCKBOXBATCHNUMBER = @LOCKBOXBATCHNUMBER,
        LOCKBOXBATCHSEQUENCE = @LOCKBOXBATCHSEQUENCE,
        PDACCOUNTSYSTEMID = @PDACCOUNTSYSTEMID,
        ISADJUSTMENT = @ISADJUSTMENT,
        ADJUSTMENTDATE = @ADJUSTMENTDATE ,
        ADJUSTMENTPOSTDATE = @ADJUSTMENTPOSTDATE,
        ADJUSTMENTREASON = @ADJUSTMENTREASON,
        ADJUSTMENTREASONCODEID = @ADJUSTMENTREASONCODEID,
        ADJUSTMENTPOSTSTATUSCODE = @ADJUSTMENTPOSTSTATUSCODE,
        REVENUEDEVELOPMENTFUNCTIONCODEID = @REVENUEDEVELOPMENTFUNCTIONCODEID,
        ORIGINALAPPLICATIONINFO = @ORIGINALAPPLICATIONINFO,
        RELOADRECOGNITION = @RELOADRECOGNITION,
        RELOADSOLICITORS = @RELOADSOLICITORS,
        BASECURRENCYID = @BASECURRENCYID,
        TRANSACTIONCURRENCYID = @TRANSACTIONCURRENCYID,
        BASEEXCHANGERATEID = @BASEEXCHANGERATEID,
        EXCHANGERATE = @EXCHANGERATE,
        USESYSTEMGENERATEDREFERENCENUMBER = @GENERATEREFERENCENUMBER,
        UPDATEMATCHINGGIFTCLAIMSCODE = @UPDATEMATCHINGGIFTCLAIMS,
        UPDATEGIFTFEES = @UPDATEGIFTFEES,
        UPDATETRIBUTES = @UPDATETRIBUTES,
        INSTALLMENTSCHEDULESEEDDATE = @INSTALLMENTSCHEDULESEEDDATE,
        APPLYBYPERCENT=@APPLYBYPERCENT,
        ADJPAYMENT_DATE=@ADJPAYMENT_DATE, 
        ADJPAYMENT_POSTDATE=@ADJPAYMENT_POSTDATE, 
        ADJPAYMENT_REASONCODEID=@ADJPAYMENT_REASONCODEID, 
        ADJPAYMENT_DETAILS=@ADJPAYMENT_DETAILS,
        SALE_LOWPRICE = @SALE_LOWPRICE,
        SALE_MEDIANPRICE = @SALE_MEDIANPRICE,
        SALE_HIGHPRICE = @SALE_HIGHPRICE,
        VENDORID = isnull(@VENDORID, ''),
        INSTALLMENTAMOUNT = @INSTALLMENTAMOUNT,
        CARDHOLDERNAME = @CARDHOLDERNAME,
        CREDITTYPECODEID = @CREDITTYPECODEID,
        CREDITCARDPARTIALNUMBER = @PARTIALCREDITCARDNUMBER,
        EXPIRESON= @EXPIRESON,
        STANDINGORDERREFERENCENUMBER = @STANDINGORDERREFERENCENUMBER
      where ID = @ID

    if @SPLITS is not null
      begin
        exec dbo.USP_REVENUEBATCH_SPLITSWITHCHILDREN_UPDATEFROMXML @ID, @SPLITS, @DATE, @CHANGEAGENTID, @CURRENTDATE;
      end
    else
      delete from dbo.BATCHREVENUESPLIT where BATCHREVENUEID = @ID;

    delete from dbo.BATCHREVENUERECOGNITION where BATCHREVENUEID = @ID;
    delete from dbo.BATCHREVENUESOLICITOR where BATCHREVENUEID = @ID;
    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);
    declare @ADDITIONALAPPLICATIONCOUNT int
    select @ADDITIONALAPPLICATIONCOUNT = count(*) from @ADDITIONALAPPLICATIONSSTREAM.nodes('/ADDITIONALAPPLICATIONSSTREAM/ITEM') T(c)

    --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)  
    declare @REVENUESTREAMSCOUNT int;
    select @REVENUESTREAMSCOUNT = count(*) from @REVENUESTREAMS.nodes('/REVENUESTREAMS/ITEM') T(c)  

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

    exec dbo.USP_REVENUEBATCH_ADDITIONALAPPLICATIONWITHCHILDREN_UPDATEFROMXML @ID, @ADDITIONALAPPLICATIONSSTREAM, @DATE, @CHANGEAGENTID
    exec dbo.USP_REVENUEBATCH_GETBENEFITS_UPDATEFROMXML @ID, @BENEFITS, @CHANGEAGENTID;
    exec dbo.USP_REVENUEBATCH_GETBENEFITSPCT_UPDATEFROMXML @ID, @PERCENTAGEBENEFITS, @CHANGEAGENTID;  
    exec dbo.USP_REVENUEBATCH_GETUNAPPLIEDMATCHINGGIFTSPLITS_UPDATEFROMXML @ID, @UNAPPLIEDMATCHINGGIFTSPLITS, @CHANGEAGENTID;
    exec dbo.USP_REVENUEUPDATEBATCH_GETLETTERS_UPDATEFROMXML @ID, @LETTERS, @CHANGEAGENTID;
    exec dbo.USP_REVENUEUPDATEBATCH_GETNOTES_UPDATEFROMXML @ID, @NOTES, @CHANGEAGENTID;

    --exec dbo.USP_REVENUEUPDATEBATCH_GETINSTALLMENTS_UPDATEFROMXML @ID, @INSTALLMENTS, @CHANGEAGENTID;

    if not @INSTALLMENTS is null
    begin
      exec dbo.USP_REVENUEUPDATEBATCH_GETINSTALLMENTS_UPDATEFROMXML @ID, @INSTALLMENTS, @CHANGEAGENTID;
      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_UPDATEFROMXML @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
        @INSTALLMENTS.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
        @INSTALLMENTS.nodes('/INSTALLMENTS/ITEM/INSTALLMENTSPLITS/ITEM/INSTALLMENTSPLITWRITEOFFS/ITEM') T(c)
    end

    exec dbo.USP_REVENUEBATCH_GETTRIBUTES_UPDATEFROMXML @ID, @TRIBUTES, @CHANGEAGENTID;
    exec dbo.USP_REVENUEBATCH_GETTAXDECLARATIONS_UPDATEFROMXML @ID, @TAXDECLARATIONS, @CHANGEAGENTID;

    --JamesWill CR268756-030207 2007/03/02

    --Unset the ISPENDING flag for all of the gifts we were previously applying to from a generated transaction

    update dbo.REVENUESCHEDULE
      set REVENUESCHEDULE.ISPENDING = 0,
        REVENUESCHEDULE.CHANGEDBYID = @CHANGEAGENTID,
        REVENUESCHEDULE.DATECHANGED = @CURRENTDATE
    where REVENUESCHEDULE.ID in (select REVENUEID from dbo.BATCHREVENUEAPPLICATION where BATCHREVENUEID = @ID and APPLIED > 0 and WASGENERATED = 1)

    delete from dbo.BATCHREVENUEAPPLICATIONPLEDGE where ID in (select BATCHREVENUEAPPLICATIONPLEDGEID from BATCHREVENUEAPPLICATION where BATCHREVENUEID = @ID);
    delete from dbo.BATCHREVENUEAPPLICATIONMEMBERSHIP where ID in (select BATCHREVENUEAPPLICATIONMEMBERSHIPID from BATCHREVENUEAPPLICATION where BATCHREVENUEID = @ID);
    delete from BATCHREVENUEAPPLICATION where BATCHREVENUEID = @ID

    exec dbo.USP_REVENUEBATCH_REVENUEAPPLICATIONS_ADDFROMXML_2 @ID, @REVENUESTREAMS, @CHANGEAGENTID, @CURRENTDATE, @CURRENTAPPUSERID;

    --JamesWill CR268756-030207 2007/03/02

    --Set the ISPENDING flag for all gifts that we are currently applying to which were from a generated transaction

    update dbo.REVENUESCHEDULE
      set REVENUESCHEDULE.ISPENDING = 1,
        REVENUESCHEDULE.CHANGEDBYID = @CHANGEAGENTID,
        REVENUESCHEDULE.DATECHANGED = @CURRENTDATE
    where REVENUESCHEDULE.ID in (select REVENUEID from dbo.BATCHREVENUEAPPLICATION where BATCHREVENUEID = @ID and APPLIED > 0 and WASGENERATED = 1)

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

    if not @contextCache is null
      set CONTEXT_INFO @contextCache; 

    /* Apply business units */
    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;