USP_DATAFORMTEMPLATE_EDIT_SPONSORSHIPBATCH_6

create procedure dbo.USP_DATAFORMTEMPLATE_EDIT_SPONSORSHIPBATCH_6
(
  @ID uniqueidentifier,
  @CHANGEAGENTID uniqueidentifier = null,
  @SEQUENCE int,
  @CONSTITUENTID uniqueidentifier,
  @DONORID uniqueidentifier,
  @PLANNEDENDDATE datetime,
  @SPONSORSHIPPROGRAMID uniqueidentifier,
  @SPONSORSHIPLOCATIONID uniqueidentifier,
  @GENDERCODE int,
  @SPROPPAGERANGEID uniqueidentifier,
  @ISHIVPOSITIVECODE int,
  @HASCONDITIONCODE int,
  @ISORPHANEDCODE int,
  @SPROPPPROJECTCATEGORYCODEID uniqueidentifier,
  @SPONSORSHIPOPPORTUNITYIDCHILD uniqueidentifier,
  @SPONSORSHIPOPPORTUNITYIDPROJECT uniqueidentifier,
  @SPONSORSHIPOPPORTUNITYTYPECODE tinyint,
  @ISSOLESPONSORSHIP bit ,
  @STARTDATE datetime,
  @ENDDATE datetime,
  @AMOUNT money,
  @PAYMENTMETHODCODE tinyint,
  @FREQUENCYCODE tinyint,
  @AUTOPAY bit,
  @REFERENCEDATE dbo.UDT_FUZZYDATE,
  @REFERENCENUMBER nvarchar(20),
  @CARDHOLDERNAME nvarchar(255),
  @CREDITCARDNUMBER nvarchar(20),
  @CREDITTYPECODEID uniqueidentifier,
  @EXPIRESON dbo.UDT_FUZZYDATE,
  @CONSTITUENTACCOUNTID uniqueidentifier,
  @REVENUESCHEDULEENDDATE datetime,
  @REVENUESCHEDULESTARTDATE datetime, 
  @CREDITCARDTOKEN uniqueidentifier,
  @ISTRANSIENTCARD bit,
  @RESERVATIONKEYID uniqueidentifier,
  @RESERVEDOPPORTUNITYID uniqueidentifier,
  @SENDREMINDER bit,
  @FINDERNUMBER bigint,
  @SOURCECODE nvarchar(50),
  @APPEALID uniqueidentifier,
  @MAILINGID uniqueidentifier,
  @CHANNELCODEID uniqueidentifier,
  @REFERENCE nvarchar(255),
  @REVENUECATEGORYCODEID uniqueidentifier,
  @ISGIFTSPONSORSHIP bit,
  @EXPIRATIONREASONID uniqueidentifier,
  @BID uniqueidentifier,
  @FINANCIALSPONSORLOOKUPID uniqueidentifier,
  @CORRESPONDINGSPONSORLOOKUPID  uniqueidentifier,
  @PARTIALCREDITCARDNUMBER nvarchar(4),
  @NEWCONSTITUENT xml,
  @NEWDONORCONSTITUENT xml,
  @TRANSACTIONCURRENCYID uniqueidentifier,
  @BASECURRENCYID uniqueidentifier,
  @BASEEXCHANGERATEID uniqueidentifier,
  @EXCHANGERATE decimal(20,8),
  @COMMITMENTID nvarchar(100),
  @CURRENTAPPUSERID uniqueidentifier = null,
  @SEPAMANDATEID uniqueidentifier,
  @ADDSEPAMANDATE bit,
  @SEPAMANDATECUSTOMIDENTIFIER nvarchar(35),
  @SEPAMANDATESIGNATUREDATE date,
  @SEPAMANDATETYPECODE tinyint,
  @OTHERPAYMENTMETHODCODEID uniqueidentifier,
  @IMPORT bit
) 
as
  set nocount on;

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

  declare @CURRENTDATE datetime
  set @CURRENTDATE = getdate()

  declare @BATCHID uniqueidentifier;

  begin try
    if @FINDERNUMBER is null
      set @FINDERNUMBER = 0;

    -- handle updating the data

    declare @CREDITCARDID uniqueidentifier

    if @CONSTITUENTID is null
      set @CONSTITUENTID = @CORRESPONDINGSPONSORLOOKUPID


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

    exec dbo.[USP_REVENUEBATCH_GETMARKETINGDATA]
      @FINDERNUMBER = @FINDERNUMBER,
      @CHANGEAGENTID = @CHANGEAGENTID,
      @CURRENTAPPUSERID = @CURRENTAPPUSERID,
      @SOURCECODE = @SOURCECODE output,
      @MAILINGID = @MAILINGID output,
      @APPEALID = @APPEALID output,
      @CONSTITUENTID = @CONSTITUENTID output,
      @BATCHID = @BATCHID,
      @IMPORT = @IMPORT;

    if @DONORID is null
      set @DONORID = @FINANCIALSPONSORLOOKUPID

    if @DONORID is null
      raiserror('BBERR_FINANCIALSPONSORREQUIRED',13,1)

    --set the corresponding sponsor id if it's not filled in.

    if @CONSTITUENTID is null and @ISGIFTSPONSORSHIP=0
      set @CONSTITUENTID = @DONORID

    if @CONSTITUENTID is null and @ISGIFTSPONSORSHIP=1
      raiserror('BBERR_CORRESPONDINGSPONSORREQUIRED',13,1)

    if @SPONSORSHIPOPPORTUNITYIDCHILD is not null and @SPONSORSHIPOPPORTUNITYIDPROJECT is not null
      raiserror('BBERR_CHILDANDPROJECTSPECIFIED',13,1)
    if @PAYMENTMETHODCODE = 2 -- Credit Card

      begin
        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
    --Clear the credit card fields before inserting them into the batch table if the payment method is not credit card - last 4 digits (98)

    --They only need to be saved to the BATCHSPONSORSHIP table if the payment method is 98 - Credit card - last 4 digits only

    if @PAYMENTMETHODCODE <> 98
    begin
      set @CARDHOLDERNAME = '';
      set @CREDITTYPECODEID = null;
      set @EXPIRESON = '00000000';
    end

    update dbo.BATCHSPONSORSHIP set
      SEQUENCE = @SEQUENCE,
      SPONSORSHIPPROGRAMID =  @SPONSORSHIPPROGRAMID,
      CONSTITUENTID = @CONSTITUENTID,
      DONORID = @DONORID,
      PLANNEDENDDATE = @PLANNEDENDDATE,
      STARTDATE= @STARTDATE,
      ENDDATE = @ENDDATE,
      SPONSORSHIPLOCATIONID = @SPONSORSHIPLOCATIONID,
      SPONSORSHIPOPPORTUNITYAGERANGEID= @SPROPPAGERANGEID,
      CHILDGENDERCODE = @GENDERCODE,
      ISHIVPOSITIVECODE = @ISHIVPOSITIVECODE,
      ISORPHANEDCODE = @ISORPHANEDCODE,
      HASCONDITIONCODE = @HASCONDITIONCODE,
      SPROPPPROJECTCATEGORYCODEID = @SPROPPPROJECTCATEGORYCODEID,
      ISSOLESPONSORSHIP= @ISSOLESPONSORSHIP,
      AMOUNT = @AMOUNT,
      PAYMENTMETHODCODE = @PAYMENTMETHODCODE,
      FREQUENCYCODE = @FREQUENCYCODE,
      AUTOPAY= @AUTOPAY,
      SPONSORSHIPOPPORTUNITYTYPECODE= @SPONSORSHIPOPPORTUNITYTYPECODE,
      CREDITCARDID = @CREDITCARDID,
      CHANGEDBYID = @CHANGEAGENTID,
      DATECHANGED = @CURRENTDATE,
      RESERVATIONKEYID =  @RESERVATIONKEYID,
      RESERVEDOPPORTUNITYID = @RESERVEDOPPORTUNITYID,
      SENDREMINDER = @SENDREMINDER,
      FINDERNUMBER = @FINDERNUMBER,
      SOURCECODE = @SOURCECODE,
      APPEALID = @APPEALID,
      CHANNELCODEID = @CHANNELCODEID,
      MAILINGID = @MAILINGID,
      REFERENCE = @REFERENCE,
      REVENUECATEGORYID = @REVENUECATEGORYCODEID,
      ISGIFTSPONSORSHIP = @ISGIFTSPONSORSHIP,
      EXPIRATIONREASONID = @EXPIRATIONREASONID,
      BASECURRENCYID = @BASECURRENCYID,
      TRANSACTIONCURRENCYID = @TRANSACTIONCURRENCYID,
      BASEEXCHANGERATEID = @BASEEXCHANGERATEID,
      EXCHANGERATE = @EXCHANGERATE,
      COMMITMENTID = coalesce(rtrim(ltrim(@COMMITMENTID)),''),
      CONSTITUENTACCOUNTID = @CONSTITUENTACCOUNTID,
      SEPAMANDATEID = @SEPAMANDATEID,
      CARDHOLDERNAME = @CARDHOLDERNAME,
      CREDITTYPECODEID = @CREDITTYPECODEID,
      CREDITCARDPARTIALNUMBER = @PARTIALCREDITCARDNUMBER,
      EXPIRESON = @EXPIRESON,
      OTHERPAYMENTMETHODCODEID = @OTHERPAYMENTMETHODCODEID
    where ID = @ID

    select @SPONSORSHIPOPPORTUNITYTYPECODE = SPONSORSHIPOPPORTUNITYTYPECODE from dbo.BATCHSPONSORSHIP where ID=@ID
    if @SPONSORSHIPOPPORTUNITYTYPECODE= 1
      if @RESERVEDOPPORTUNITYID is not null
        update dbo.BATCHSPONSORSHIP set SPONSORSHIPOPPORTUNITYID = @RESERVEDOPPORTUNITYID where ID= @ID
      else
        update dbo.BATCHSPONSORSHIP set SPONSORSHIPOPPORTUNITYID = @SPONSORSHIPOPPORTUNITYIDCHILD where ID= @ID
    else
      update dbo.BATCHSPONSORSHIP set 
        SPONSORSHIPOPPORTUNITYID = @SPONSORSHIPOPPORTUNITYIDPROJECT
      where ID= @ID
  end try

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

  return 0;