USP_DATAFORMTEMPLATE_EDITLOAD_DONATIONBATCHROW

The load procedure used by the edit dataform template "Donation Batch 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.
@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.
@SEQUENCE	int	INOUT	Sequence
@EVENTID	uniqueidentifier	INOUT	Event
@USERID	nvarchar(100)	INOUT	Username
@PASSWORD	nvarchar(500)	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	Nick name
@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/Province
@POSTCODE	nvarchar(24)	INOUT	Postcode
@DONOTMAIL	bit	INOUT	Do not send mail to this address
@DONOTMAILREASONCODEID	uniqueidentifier	INOUT	Reason
@PHONETYPECODEID	uniqueidentifier	INOUT	Phone type
@PHONENUMBER	nvarchar(200)	INOUT	Phone number
@EMAILADDRESS	UDT_EMAILADDRESS	INOUT	Email address
@ISCOMPANYGIFT	bit	INOUT	This is an organization donation
@COMPANYNAME	nvarchar(200)	INOUT	Organization name
@COMPANYURL	UDT_WEBADDRESS	INOUT	Organization website
@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
@GIFTTYPE	nvarchar(100)	INOUT	Gift type
@GIFTAMOUNT	money	INOUT	Gift amount
@NUMPAYMENTS	int	INOUT	Number of payments
@FREQUENCY	nvarchar(200)	INOUT	Frequency
@AUTOINSTALLMENTS	bit	INOUT	Automatic installments
@ANONYMOUS	bit	INOUT	Gift is anonymous
@STARTDATE	UDT_FUZZYDATE	INOUT	Start date
@INFINITEGIFT	bit	INOUT	Gift is infinite
@PAYMENTMETHODCODE	int	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	Expiration on date
@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
@CHECKDATE	UDT_FUZZYDATE	INOUT	Check date
@CHECKNUMBER	nvarchar(40)	INOUT	Check number
@BANKACCOUNTTYPECODE	int	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(100)	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
@SAVETRIBUTEEASCONSTITUENT	bit	INOUT	Save tributee as constituent
@SHOWTRIBUTE	bit	INOUT	I would like to make a donation to honor
@TRIBUTEFIRSTNAME	nvarchar(400)	INOUT	Tributee first name
@TRIBUTELASTNAME	nvarchar(400)	INOUT	Tributee last name
@TRIBUTEEMAIL	UDT_EMAILADDRESS	INOUT	Tributee email address
@TRIBUTEADDRESS	nvarchar(600)	INOUT	Tributee address
@TRIBUTECITY	nvarchar(200)	INOUT	Tributee city
@TRIBUTEPOSTCODE	nvarchar(48)	INOUT	Tributee postcode
@TRIBUTESTATEID	uniqueidentifier	INOUT	Tributee state
@TRIBUTETYPECODEID	uniqueidentifier	INOUT	Tribute type
@TRIBUTETEXT	nvarchar(500)	INOUT	Tribute message
@TRIBUTEANONYMOUS	bit	INOUT	Do not display tribute information
@ACKNOWLEDGEFIRSTNAME	nvarchar(400)	INOUT	Acknowledgee first name
@ACKNOWLEDGELASTNAME	nvarchar(400)	INOUT	Acknowledgee last name
@ACKNOWLEDGEEMAIL	UDT_EMAILADDRESS	INOUT	Acknowledgee email address
@MESSAGETOACKNOWLEDGE	nvarchar(2000)	INOUT	Message about my donation
@RECOGNITIONCREDIT	nvarchar(100)	INOUT	Recognition credit
@MATCHINGCOMPANYNAME	nvarchar(400)	INOUT	Matching gift company name
@MATCHINGAMOUNT	money	INOUT	Matching Amount
@RECIPIENTGROUPID	uniqueidentifier	INOUT	Recognition group
@RECIPIENTREGISTRANTID	uniqueidentifier	INOUT	Recognition individual
@RECIPIENTROLE	nvarchar(200)	INOUT	Recognition credit
@SENDTOACKNOWLEDGE	bit	INOUT	Send email to acknowledgee
@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
@ORGANIZATIONCONSTITUENTID	uniqueidentifier	INOUT	Search existing organization constituents
@MERCHANTACCOUNTID	uniqueidentifier	INOUT	Merchant account ID
@SUBSCRIBEDNEWSLETTERIDS	xml	INOUT	eNewsletter
@SENDNOTIFICATIONS	bit	INOUT
@BNUMBER	nvarchar(100)	INOUT
@VENDORID	nvarchar(50)	INOUT

Definition

Copy


CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDITLOAD_DONATIONBATCHROW(
  @ID uniqueidentifier,
    @DATALOADED bit = 0 output,
  @TSLONG bigint = 0 output,

  @SEQUENCE int = null output,
    @EVENTID uniqueidentifier = null output,
    @USERID nvarchar(100) = null output,
    @PASSWORD nvarchar(500) = 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,
    @ISCOMPANYGIFT bit = 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,
    @GIFTTYPE nvarchar(100) = null output,
    @GIFTAMOUNT money = null output,
    @NUMPAYMENTS int = null output,
    @FREQUENCY nvarchar(200) = null output,
    @AUTOINSTALLMENTS bit = NULL output,
    @ANONYMOUS bit = NULL output,
    @STARTDATE UDT_FUZZYDATE = null  output,
    @INFINITEGIFT bit = NULL output,
    @PAYMENTMETHODCODE int = 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,
  @CHECKDATE UDT_FUZZYDATE = null output,
  @CHECKNUMBER nvarchar(40) = null output,
    @BANKACCOUNTTYPECODE int = 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(100) = 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,
  @SAVETRIBUTEEASCONSTITUENT bit = NULL output,
    @SHOWTRIBUTE bit = NULL output,
    @TRIBUTEFIRSTNAME nvarchar(400) = null output,
    @TRIBUTELASTNAME nvarchar(400) = null output,
    @TRIBUTEEMAIL UDT_EMAILADDRESS = null output,
    @TRIBUTEADDRESS nvarchar(600) = null output,
    @TRIBUTECITY nvarchar(200) = null output,
    @TRIBUTEPOSTCODE nvarchar(48) = null output,
    @TRIBUTESTATEID uniqueidentifier = null output,
    @TRIBUTETYPECODEID uniqueidentifier = null output,
    @TRIBUTETEXT nvarchar(500) = null output,
    @TRIBUTEANONYMOUS bit = NULL output,
    @ACKNOWLEDGEFIRSTNAME nvarchar(400) = null output,
    @ACKNOWLEDGELASTNAME nvarchar(400) = null output,
    @ACKNOWLEDGEEMAIL UDT_EMAILADDRESS = null output,
    @MESSAGETOACKNOWLEDGE nvarchar(2000) = null output,
    @RECOGNITIONCREDIT nvarchar(100) = null output,
    @MATCHINGCOMPANYNAME nvarchar(400) = null output,
    @MATCHINGAMOUNT money = null output,
    @RECIPIENTGROUPID uniqueidentifier = null output,
  @RECIPIENTREGISTRANTID uniqueidentifier = null output,
    @RECIPIENTROLE nvarchar(200) = null output,
    @SENDTOACKNOWLEDGE bit = NULL output,

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

  @ORGANIZATIONCONSTITUENTID uniqueidentifier = null output,

  @MERCHANTACCOUNTID uniqueidentifier = null output,

  @SUBSCRIBEDNEWSLETTERIDS xml = null output,

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

    set nocount on;

  set @DATALOADED = 0;
  set @TSLONG = 0;

    select
    @DATALOADED = 1,
    @SEQUENCE = DONATIONBATCH.SEQUENCE,
    @EVENTID = EVENTID,
    @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 = DONATIONBATCH.COUNTRYID,
    @ADDRESS = ADDRESS,
    @CITY = CITY,
    @STATEID = STATEID,
    @POSTCODE = POSTCODE,
    @DONOTMAIL = DONOTMAIL,
      @DONOTMAILREASONCODEID = DONOTMAILREASONCODEID,
    @PHONETYPECODEID = PHONETYPECODEID,
    @PHONENUMBER = PHONENUMBER,
    @EMAILADDRESS = EMAILADDRESS,
    @ISCOMPANYGIFT = ISCOMPANYGIFT,
    @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,
    @GIFTTYPE = GIFTTYPE,
    @GIFTAMOUNT = GIFTAMOUNT,
    @NUMPAYMENTS = NUMPAYMENTS,
    @FREQUENCY = FREQUENCY,
    @AUTOINSTALLMENTS = AUTOINSTALLMENTS,
      @ANONYMOUS = ANONYMOUS,
    @STARTDATE = STARTDATE,
    @INFINITEGIFT = INFINITEGIFT,
    @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 = case GIFTTYPE when 'one_time_gift' then 0 else 1 end,
    @AMOUNT = case GIFTTYPE when 'one_time_gift' then GIFTAMOUNT else 0 end,
    @CONSTITUENTID = null,
    @CHECKDATE = CHECKDATE,
    @CHECKNUMBER = CHECKNUMBER,
    @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,
    @SHOWTRIBUTE = SHOWTRIBUTE,
      @TRIBUTEFIRSTNAME = TRIBUTEFIRSTNAME,
    @TRIBUTELASTNAME = TRIBUTELASTNAME,
    @TRIBUTEEMAIL = TRIBUTEEMAIL,
    @TRIBUTEADDRESS = TRIBUTEADDRESS,
    @TRIBUTECITY = TRIBUTECITY,
    @TRIBUTEPOSTCODE = TRIBUTEPOSTCODE,
    @TRIBUTESTATEID = TRIBUTESTATEID,
      @TRIBUTETYPECODEID = TRIBUTETYPECODEID,
    @TRIBUTETEXT = TRIBUTETEXT,
    @TRIBUTEANONYMOUS = TRIBUTEANONYMOUS,
    @ACKNOWLEDGEFIRSTNAME = ACKNOWLEDGEFIRSTNAME,
    @ACKNOWLEDGELASTNAME = ACKNOWLEDGELASTNAME,
    @ACKNOWLEDGEEMAIL = ACKNOWLEDGEEMAIL,
      @MESSAGETOACKNOWLEDGE = MESSAGETOACKNOWLEDGE,
    @RECOGNITIONCREDIT = RECOGNITIONCREDIT,
    @MATCHINGCOMPANYNAME = MATCHINGCOMPANYNAME,
    @MATCHINGAMOUNT = MATCHINGAMOUNT,
    @RECIPIENTGROUPID = RECIPIENTGROUPID,
    @RECIPIENTREGISTRANTID = RECIPIENTREGISTRANTID,
    @RECIPIENTROLE = RECIPIENTROLE,
    @SENDTOACKNOWLEDGE = SENDTOACKNOWLEDGE,
    @SAVETRIBUTEEASCONSTITUENT = SAVETRIBUTEEASCONSTITUENT, 
    @ORIGINALCLIENTUSERID = ORIGINALCLIENTUSERID, 
    @ORIGINALCONSTITUENTID = ORIGINALCONSTITUENTID, 
    @PHONEID = PHONEID, 
    @ADDRESSID = ADDRESSID, 
    @EMAILADDRESSID = EMAILADDRESSID,
    @ORGANIZATIONCONSTITUENTID = ORGANIZATIONCONSTITUENTID,

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

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

    return 0;