USP_DATAFORMTEMPLATE_EDIT_BATCHEVENTSPONSORSHIPROW_4

CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_BATCHEVENTSPONSORSHIPROW_4 (
    @ID uniqueidentifier,
  @EVENTID uniqueidentifier,
  @SEQUENCE int,
  @USERID nvarchar(100),
  @PASSWORD nvarchar(510),
  @LASTNAME nvarchar(100),
  @FIRSTNAME nvarchar(100),
  @MIDDLENAME nvarchar(100),
  @TITLECODEID uniqueidentifier,
  @SUFFIXCODEID uniqueidentifier,
  @NICKNAME nvarchar(100),
  @MAIDENNAME nvarchar(200),
  @BIRTHDATE UDT_FUZZYDATE,
  @GENDERCODE int,
  @MARITALSTATUSCODEID uniqueidentifier,
  @ADDRESSTYPECODEID uniqueidentifier,
  @COUNTRYID uniqueidentifier,
  @ADDRESS nvarchar(300),
  @CITY nvarchar(100),
  @STATEID uniqueidentifier,
  @POSTCODE nvarchar(24),
  @DONOTMAIL bit,
  @DONOTMAILREASONCODEID uniqueidentifier,
  @PHONETYPECODEID uniqueidentifier,
  @PHONENUMBER nvarchar(200),
  @EMAILADDRESS UDT_EMAILADDRESS,

  @SUBSCRIBEDNEWSLETTERIDS xml,
  @MERCHANTACCOUNTID uniqueidentifier,

  @ISCOMPANYSPONSOR bit,
  @ORGANIZATIONCONSTITUENTID uniqueidentifier,
  @COMPANYNAME nvarchar(200),
  @COMPANYURL UDT_WEBADDRESS,
  @COMPANY_ADDRESSTYPECODEID uniqueidentifier,
  @COMPANY_COUNTRYID uniqueidentifier,
  @COMPANY_ADDRESS nvarchar(300),
  @COMPANY_CITY nvarchar(100),
  @COMPANY_STATEID uniqueidentifier,
  @COMPANY_POSTCODE nvarchar(24),
  @COMPANY_DONOTMAIL bit,
  @COMPANY_DONOTMAILREASONCODEID uniqueidentifier,
  @COMPANY_PHONETYPECODEID uniqueidentifier,
  @COMPANY_PHONENUMBER nvarchar(200),
  @COMPANY_EMAILADDRESS UDT_EMAILADDRESS,

  @SPONSORSHIPTYPEID uniqueidentifier,
  @DECLINEBENEFITS bit,
  @SPONSORSHIPURL UDT_WEBADDRESS,
  @SPONSORSHIPMESSAGE nvarchar(2000),

  @ADDITIONALDONATIONAMOUNT money,
  @PAYMENTMETHODCODE nvarchar(20),  
  @CREDITCARDHOLDER nvarchar(200),
  @CREDITCARDNUMBER nvarchar(20),
  @CREDITCARDTYPE uniqueidentifier,
  @EXPIRATIONDATE UDT_FUZZYDATE,
  @CREDITCARDTOKEN uniqueidentifier,
  @CREDITCARDID uniqueidentifier,
  @AUTHORIZATIONCODE nvarchar(20),
  @TRANSACTIONID uniqueidentifier,
  @REJECTIONMESSAGE nvarchar(250),
  @TYPECODE tinyint,
  @AMOUNT money,
  @CONSTITUENTID uniqueidentifier,

  @CHECKNUMBER nvarchar(20),
  @CHECKDATE UDT_FUZZYDATE,
  @BANKACCOUNTTYPECODE nvarchar(20),
  @BANKACCOUNTHOLDER nvarchar(200),
  @BANKACCOUNTNUMBER nvarchar(200),
  @BANKACCOUNTROUTINGCODE nvarchar(200),

  @SAMEASPRIMARYADDRESS bit,
  @BILLING_ADDRESSTYPECODEID uniqueidentifier,
  @BILLING_COUNTRYID uniqueidentifier,
  @BILLING_ADDRESSBLOCK nvarchar(300),
  @BILLING_CITY nvarchar(50),
  @BILLING_STATEID uniqueidentifier,
  @BILLING_POSTCODE nvarchar(24),
  @BILLING_STATE nvarchar(10),
  @BILLING_COUNTRY nvarchar(4),

  @ORIGINALCLIENTUSERID integer,
  @ORIGINALCONSTITUENTID uniqueidentifier,
  @PHONEID uniqueidentifier,
  @ADDRESSID uniqueidentifier,
  @EMAILADDRESSID uniqueidentifier,

  @CHANGEAGENTID uniqueidentifier,

  @SENDNOTIFICATIONS bit,
  @BNUMBER nvarchar(100),
  @VENDORID nvarchar(50)
)
as

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

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

    declare @CURRENTDATE datetime    
    set @CURRENTDATE = getdate()    

  if @PAYMENTMETHODCODE is null
    set @PAYMENTMETHODCODE = '0'

  if @BANKACCOUNTTYPECODE is null
    set @BANKACCOUNTTYPECODE = '0'

  if @ORIGINALCLIENTUSERID = 0
    set @ORIGINALCLIENTUSERID = null

  if @ORIGINALCONSTITUENTID = '00000000-0000-0000-0000-000000000000'
    set @ORIGINALCONSTITUENTID = null

  if @PHONEID = '00000000-0000-0000-0000-000000000000'
    set @PHONEID = null

  if @ADDRESSID = '00000000-0000-0000-0000-000000000000'
    set @ADDRESSID = null

  if @EMAILADDRESSID = '00000000-0000-0000-0000-000000000000'
    set @EMAILADDRESSID = null

  if @ORGANIZATIONCONSTITUENTID = '00000000-0000-0000-0000-000000000000'
    set @ORGANIZATIONCONSTITUENTID = null

  if @PAYMENTMETHODCODE = 2 and @EXPIRATIONDATE <> '00000000' and convert(date, @EXPIRATIONDATE, 112) < convert(date, getdate())
  begin
      raiserror('INVALIDEXPIRATIONDATE', 13, 2)
      return 1
  end

  exec dbo.USP_BATCHEVENTSPONSORSHIP_ADD_EDIT

    @ID = @ID output,
    @BATCHID = null,
    @EVENTID = @EVENTID,
    @SEQUENCE = @SEQUENCE,
    @USERID = @USERID,
    @PASSWORD = @PASSWORD,
    @LASTNAME = @LASTNAME,
    @FIRSTNAME = @FIRSTNAME,
    @MIDDLENAME = @MIDDLENAME,
    @TITLECODEID = @TITLECODEID,
    @SUFFIXCODEID = @SUFFIXCODEID,
    @NICKNAME = @NICKNAME,
    @MAIDENNAME = @MAIDENNAME,
    @BIRTHDATE = @BIRTHDATE,
    @GENDERCODE = @GENDERCODE,
    @MARITALSTATUSCODEID = @MARITALSTATUSCODEID,
    @ADDRESSTYPECODEID = @ADDRESSTYPECODEID,
    @COUNTRYID = @COUNTRYID,
    @ADDRESS = @ADDRESS,
    @CITY = @CITY,
    @STATEID = @STATEID,
    @POSTCODE = @POSTCODE,
    @DONOTMAIL = @DONOTMAIL,
    @DONOTMAILREASONCODEID = @DONOTMAILREASONCODEID,
    @PHONETYPECODEID = @PHONETYPECODEID,
    @PHONENUMBER = @PHONENUMBER,
    @EMAILADDRESS = @EMAILADDRESS,

    @ISCOMPANYSPONSOR = @ISCOMPANYSPONSOR,
    @ORGANIZATIONCONSTITUENTID = @ORGANIZATIONCONSTITUENTID,
    @COMPANYNAME = @COMPANYNAME,
    @COMPANYURL = @COMPANYURL,
    @COMPANY_ADDRESSTYPECODEID =  @COMPANY_ADDRESSTYPECODEID,
    @COMPANY_COUNTRYID = @COMPANY_COUNTRYID,
    @COMPANY_ADDRESS = @COMPANY_ADDRESS,
    @COMPANY_CITY = @COMPANY_CITY,
    @COMPANY_STATEID = @COMPANY_STATEID,
    @COMPANY_POSTCODE = @COMPANY_POSTCODE,
    @COMPANY_DONOTMAIL = @COMPANY_DONOTMAIL,
    @COMPANY_DONOTMAILREASONCODEID = @COMPANY_DONOTMAILREASONCODEID,
    @COMPANY_PHONETYPECODEID = @COMPANY_PHONETYPECODEID,
    @COMPANY_PHONENUMBER = @COMPANY_PHONENUMBER,
    @COMPANY_EMAILADDRESS = @COMPANY_EMAILADDRESS,

    @SPONSORSHIPTYPEID = @SPONSORSHIPTYPEID,
    @DECLINEBENEFITS = @DECLINEBENEFITS,
    @SPONSORSHIPURL = @SPONSORSHIPURL,
    @SPONSORSHIPMESSAGE = @SPONSORSHIPMESSAGE,

    @ADDITIONALDONATIONAMOUNT = @ADDITIONALDONATIONAMOUNT,
    @PAYMENTMETHODCODE = @PAYMENTMETHODCODE,  
    @CREDITCARDHOLDER = @CREDITCARDHOLDER,
    @CREDITCARDNUMBER = @CREDITCARDNUMBER,
    @CREDITCARDTYPE = @CREDITCARDTYPE,
    @EXPIRATIONDATE = @EXPIRATIONDATE,
    @CREDITCARDTOKEN = @CREDITCARDTOKEN,
    @AUTHORIZATIONCODE = @AUTHORIZATIONCODE,
    @TRANSACTIONID = @TRANSACTIONID,
    @REJECTIONMESSAGE = @REJECTIONMESSAGE,
    @CHECKNUMBER = @CHECKNUMBER,
    @CHECKDATE = @CHECKDATE,
    @BANKACCOUNTTYPECODE = @BANKACCOUNTTYPECODE,
    @BANKACCOUNTHOLDER = @BANKACCOUNTHOLDER,
    @BANKACCOUNTNUMBER = @BANKACCOUNTNUMBER,
    @BANKACCOUNTROUTINGCODE = @BANKACCOUNTROUTINGCODE,

    @SAMEASPRIMARYADDRESS = @SAMEASPRIMARYADDRESS,
    @BILLING_ADDRESSTYPECODEID = @BILLING_ADDRESSTYPECODEID,
    @BILLING_COUNTRYID = @BILLING_COUNTRYID,
    @BILLING_ADDRESSBLOCK = @BILLING_ADDRESSBLOCK,
    @BILLING_CITY = @BILLING_CITY,
    @BILLING_STATEID = @BILLING_STATEID,
    @BILLING_POSTCODE = @BILLING_POSTCODE,

    @ORIGINALCLIENTUSERID = @ORIGINALCLIENTUSERID,
    @ORIGINALCONSTITUENTID = @ORIGINALCONSTITUENTID,
    @PHONEID = @PHONEID,
    @ADDRESSID = @ADDRESSID,
    @EMAILADDRESSID = @EMAILADDRESSID,

    @CHANGEAGENTID = @CHANGEAGENTID,

    @SENDNOTIFICATIONS = @SENDNOTIFICATIONS,
    @VENDORID = @VENDORID

 -- exec dbo.USP_BATCHEVENTSPONSORSHIP_GETEMAILLIST_ADDFROMXML @ID, @SUBSCRIBEDNEWSLETTERIDS, @CHANGEAGENTID;

return 0;