USP_DATAFORMTEMPLATE_EDITLOAD_BATCHEVENTSPONSORSHIPROW

The load procedure used by the edit dataform template "BatchEventSponsorship Edit Data Form 2"

Parameters

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

Definition

Copy


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

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

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

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

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

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

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

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

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

    set nocount on;

    -- be sure to set these, in case the select returns no rows
    set @DATALOADED = 0
    set @TSLONG = 0

    -- populate the output parameters, which correspond to fields on the form.  Note that
    -- we set @DATALOADED = 1 to indicate that the load was successful.  Otherwise, the system
    -- will display a "no data loaded" message.  Also note that we fetch the TSLONG so that concurrency
    -- can be considered.
    select
        @DATALOADED = 1,
    @EVENTID = EVENTID,
    @SEQUENCE = BATCHEVENTSPONSORSHIP.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 = BATCHEVENTSPONSORSHIP.COUNTRYID,
    @ADDRESS = ADDRESS,
    @CITY = CITY,
    @STATEID = STATEID,
    @POSTCODE = POSTCODE,
    @DONOTMAIL = DONOTMAIL,
    @DONOTMAILREASONCODEID = DONOTMAILREASONCODEID,
    @PHONETYPECODEID = PHONETYPECODEID,
    @PHONENUMBER = PHONENUMBER,
    @EMAILADDRESS = EMAILADDRESS,

    -- @SUBSCRIBEDNEWSLETTERIDS = dbo.UFN_BATCHEVENTSPONSORSHIP_GETEMAILLIST_TOITEMLISTXML(BATCHEVENTSPONSORSHIP.ID),
    @MERCHANTACCOUNTID = null,

    @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 = CREDITCARD.CARDHOLDERNAME,
    @CREDITCARDNUMBER = CREDITCARD.CREDITCARDPARTIALNUMBER, 
    @CREDITCARDTYPE = CREDITCARDTYPE,
    @EXPIRATIONDATE = CREDITCARD.EXPIRESON,
    @CREDITCARDTOKEN = CREDITCARD.CREDITCARDTOKEN,
    @CREDITCARDID = CREDITCARDID,
    @AUTHORIZATIONCODE = AUTHORIZATIONCODE,
    @TRANSACTIONID = TRANSACTIONID,
    @REJECTIONMESSAGE = REJECTIONMESSAGE,
    @TYPECODE = 0,
    @CONSTITUENTID = null,

    @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,
    @BILLING_STATE = STATE.ABBREVIATION,
    @BILLING_COUNTRY = COUNTRY.ISO3166,

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

    @SENDNOTIFICATIONS = SENDNOTIFICATIONS,
    @BNUMBER = BATCH.BATCHNUMBER,
    @VENDORID = BATCHEVENTSPONSORSHIP.VENDORID

    from BATCHEVENTSPONSORSHIP (nolock)
  inner join dbo.BATCH (nolock) on BATCHEVENTSPONSORSHIP.BATCHID = BATCH.ID
  left outer join dbo.CREDITCARD (nolock) on BATCHEVENTSPONSORSHIP.CREDITCARDID = CREDITCARD.ID
  left join dbo.STATE (nolock) on BATCHEVENTSPONSORSHIP.BILLING_STATEID = STATE.ID  
  left join dbo.COUNTRY (nolock) on BATCHEVENTSPONSORSHIP.BILLING_COUNTRYID = COUNTRY.ID  
  where BATCHEVENTSPONSORSHIP.ID = @ID

  exec dbo.USP_SPONSORSHIPTYPE_GETFEEBYID @ID = @SPONSORSHIPTYPEID, @FEE = @AMOUNT output
  set @AMOUNT = @AMOUNT + @ADDITIONALDONATIONAMOUNT;

    return 0;