USP_DATAFORMTEMPLATE_ADD_BATCHEVENTSPONSORSHIPROW

The save procedure used by the add dataform template "BatchEventSponsorship Add Data Form 2".

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	INOUT	The output parameter indicating the ID of the record added.
@BATCHID	uniqueidentifier	IN	Input parameter indicating the context ID for the record being added.
@EVENTID	uniqueidentifier	IN	Event
@SEQUENCE	int	IN	Sequence
@USERID	nvarchar(100)	IN	Username
@PASSWORD	nvarchar(510)	IN	Password
@LASTNAME	nvarchar(100)	IN	Last name
@FIRSTNAME	nvarchar(100)	IN	First name
@MIDDLENAME	nvarchar(100)	IN	Middle name
@TITLECODEID	uniqueidentifier	IN	Title
@SUFFIXCODEID	uniqueidentifier	IN	Suffix
@NICKNAME	nvarchar(100)	IN	Nickname
@MAIDENNAME	nvarchar(200)	IN	Maiden name
@BIRTHDATE	UDT_FUZZYDATE	IN	Birth date
@GENDERCODE	int	IN	Gender
@MARITALSTATUSCODEID	uniqueidentifier	IN	Marital status
@ADDRESSTYPECODEID	uniqueidentifier	IN	Address type
@COUNTRYID	uniqueidentifier	IN	Country
@ADDRESS	nvarchar(300)	IN	Address
@CITY	nvarchar(100)	IN	City
@STATEID	uniqueidentifier	IN	State
@POSTCODE	nvarchar(24)	IN	Postcode
@DONOTMAIL	bit	IN	Do not send mail to this address
@DONOTMAILREASONCODEID	uniqueidentifier	IN	Do not mail reason
@PHONETYPECODEID	uniqueidentifier	IN	Phone type
@PHONENUMBER	nvarchar(200)	IN	Phone number
@EMAILADDRESS	UDT_EMAILADDRESS	IN	Email address
@SUBSCRIBEDNEWSLETTERIDS	xml	IN	eNewsletter
@MERCHANTACCOUNTID	uniqueidentifier	IN	Merchant Account ID
@ISCOMPANYSPONSOR	bit	IN	This is an organization sponsor
@ORGANIZATIONCONSTITUENTID	uniqueidentifier	IN	Search existing organization constituents
@COMPANYNAME	nvarchar(200)	IN	Organization name
@COMPANYURL	UDT_WEBADDRESS	IN	Organization URL
@COMPANY_ADDRESSTYPECODEID	uniqueidentifier	IN	Organization address type
@COMPANY_COUNTRYID	uniqueidentifier	IN	Organization country
@COMPANY_ADDRESS	nvarchar(300)	IN	Organization address
@COMPANY_CITY	nvarchar(100)	IN	Organization city
@COMPANY_STATEID	uniqueidentifier	IN	Organization state
@COMPANY_POSTCODE	nvarchar(24)	IN	Organization postcode
@COMPANY_DONOTMAIL	bit	IN	Organization: Do not send mail to this address
@COMPANY_DONOTMAILREASONCODEID	uniqueidentifier	IN	Organization: Reason
@COMPANY_PHONETYPECODEID	uniqueidentifier	IN	Organization phone type
@COMPANY_PHONENUMBER	nvarchar(200)	IN	Organization phone number
@COMPANY_EMAILADDRESS	UDT_EMAILADDRESS	IN	Organization email address
@SPONSORSHIPTYPEID	uniqueidentifier	IN	Sponsorship options
@DECLINEBENEFITS	bit	IN	Waive benefits
@SPONSORSHIPURL	UDT_WEBADDRESS	IN	Sponsor URL
@SPONSORSHIPMESSAGE	nvarchar(2000)	IN	Sponsor message
@ADDITIONALDONATIONAMOUNT	money	IN	Additional donation amount
@PAYMENTMETHODCODE	nvarchar(20)	IN	Payment method
@CREDITCARDHOLDER	nvarchar(200)	IN	Card holder name
@CREDITCARDNUMBER	nvarchar(20)	IN	Credit card number
@CREDITCARDTYPE	uniqueidentifier	IN	Credit card type
@EXPIRATIONDATE	UDT_FUZZYDATE	IN	Credit card expiration
@CREDITCARDTOKEN	uniqueidentifier	IN	Credit card token
@CREDITCARDID	uniqueidentifier	IN	Credit card ID
@AUTHORIZATIONCODE	nvarchar(20)	IN	Authorization code
@TRANSACTIONID	uniqueidentifier	IN	Transaction ID
@REJECTIONMESSAGE	nvarchar(250)	IN	Rejection message
@TYPECODE	tinyint	IN	Type code
@AMOUNT	money	IN	Amount
@CONSTITUENTID	uniqueidentifier	IN	Constituent ID
@CHECKNUMBER	nvarchar(20)	IN	Check number
@CHECKDATE	UDT_FUZZYDATE	IN	Check date
@BANKACCOUNTTYPECODE	nvarchar(20)	IN	Bank account type
@BANKACCOUNTHOLDER	nvarchar(200)	IN	Bank account holder
@BANKACCOUNTNUMBER	nvarchar(200)	IN	Bank account number
@BANKACCOUNTROUTINGCODE	nvarchar(200)	IN	Bank account routing code
@SAMEASPRIMARYADDRESS	bit	IN	Billing address same as primary
@BILLING_ADDRESSTYPECODEID	uniqueidentifier	IN	Billing address type
@BILLING_COUNTRYID	uniqueidentifier	IN	Billing country
@BILLING_ADDRESSBLOCK	nvarchar(300)	IN	Billing address
@BILLING_CITY	nvarchar(50)	IN	Billing city
@BILLING_STATEID	uniqueidentifier	IN	Billing state
@BILLING_POSTCODE	nvarchar(24)	IN	Billing postcode
@BILLING_STATE	nvarchar(10)	IN	Billing state
@BILLING_COUNTRY	nvarchar(4)	IN	Billing country
@ORIGINALCLIENTUSERID	int	IN	Original client user id
@ORIGINALCONSTITUENTID	uniqueidentifier	IN	Search existing constituents
@PHONEID	uniqueidentifier	IN	Phone id
@ADDRESSID	uniqueidentifier	IN	Address id
@EMAILADDRESSID	uniqueidentifier	IN	Email address id
@CHANGEAGENTID	uniqueidentifier	IN	Input parameter indicating the ID of the change agent invoking the procedure.
@SENDNOTIFICATIONS	bit	IN
@BNUMBER	nvarchar(100)	IN
@VENDORID	nvarchar(50)	IN

Definition

Copy


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

  @SUBSCRIBEDNEWSLETTERIDS xml = null,
  @MERCHANTACCOUNTID uniqueidentifier = null,

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

  @SPONSORSHIPTYPEID uniqueidentifier = null,
  @DECLINEBENEFITS bit = 0,
  @SPONSORSHIPURL UDT_WEBADDRESS = '',
  @SPONSORSHIPMESSAGE nvarchar(2000) = '',

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

  @CHECKNUMBER nvarchar(20) = '',
  @CHECKDATE UDT_FUZZYDATE = '00000000',
  @BANKACCOUNTTYPECODE nvarchar(20) = '0',
  @BANKACCOUNTHOLDER nvarchar(200) = '',
  @BANKACCOUNTNUMBER nvarchar(200) = '',
  @BANKACCOUNTROUTINGCODE nvarchar(200) = '',

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

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

  @CHANGEAGENTID uniqueidentifier = null,

  @SENDNOTIFICATIONS bit = 1,
  @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 = @BATCHID,
  @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