BATCHEVENTSPONSORSHIP

Stores Sponsorship batch information

Primary Key

Primary Key Field Type
ID uniqueidentifier

Fields

Field Field Type Null Notes Description
SEQUENCE int Default = 0
USERID nvarchar(50) Default = ''
PASSWORD nvarchar(255) Default = ''
LASTNAME nvarchar(50) Default = ''
FIRSTNAME nvarchar(50) Default = ''
MIDDLENAME nvarchar(50) Default = ''
NICKNAME nvarchar(50) Default = ''
MAIDENNAME nvarchar(100) Default = ''
BIRTHDATE UDT_FUZZYDATE Default = '00000000'
GENDERCODE tinyint Default = 0 0=Unknown, 1=Male, 2=Female
GENDER nvarchar(7) (Computed) yes CASE [GENDERCODE] WHEN 0 THEN N'Unknown' WHEN 1 THEN N'Male' WHEN 2 THEN N'Female' END Provides a translation for the 'GENDERCODE' field.
ADDRESS nvarchar(150) Default = ''
CITY nvarchar(50) Default = ''
POSTCODE nvarchar(12) Default = ''
DONOTMAIL bit Default = 0
PHONENUMBER nvarchar(100) Default = ''
EMAILADDRESS UDT_EMAILADDRESS Default = ''
COMPANYNAME nvarchar(100) Default = ''
COMPANYURL UDT_WEBADDRESS Default = ''
COMPANY_ADDRESS nvarchar(150) Default = ''
COMPANY_CITY nvarchar(50) Default = ''
COMPANY_POSTCODE nvarchar(12) Default = ''
COMPANY_DONOTMAIL bit Default = 0
COMPANY_PHONENUMBER nvarchar(100) Default = ''
COMPANY_EMAILADDRESS UDT_EMAILADDRESS Default = ''
DECLINEBENEFITS bit Default = 0
SPONSORSHIPURL UDT_WEBADDRESS Default = ''
SPONSORSHIPMESSAGE nvarchar(1000) Default = ''
ADDITIONALDONATIONAMOUNT money Default = 0
PAYMENTMETHODCODE tinyint Default = 0 0=Cash, 1=Offline Check, 2=Credit/Debit Cards, 3=Online Check (ACH)
PAYMENTMETHOD nvarchar(18) (Computed) yes CASE [PAYMENTMETHODCODE] WHEN 0 THEN N'Cash' WHEN 1 THEN N'Offline Check' WHEN 2 THEN N'Credit/Debit Cards' WHEN 3 THEN N'Online Check (ACH)' END Provides a translation for the 'PAYMENTMETHODCODE' field.
CREDITCARDTYPE uniqueidentifier yes
CHECKDATE UDT_FUZZYDATE Default = '00000000'
BANKACCOUNTTYPECODE tinyint Default = 0 0=Checking Account, 1=Saving Account
BANKACCOUNTTYPE nvarchar(16) (Computed) yes CASE [BANKACCOUNTTYPECODE] WHEN 0 THEN N'Checking Account' WHEN 1 THEN N'Saving Account' END Provides a translation for the 'BANKACCOUNTTYPECODE' field.
BANKACCOUNTHOLDER nvarchar(100) Default = ''
BANKACCOUNTNUMBER nvarchar(100) Default = ''
BANKACCOUNTROUTINGCODE nvarchar(100) Default = ''
SAMEASPRIMARYADDRESS bit Default = 1
BILLING_ADDRESSBLOCK nvarchar(150) Default = ''
BILLING_CITY nvarchar(50) Default = ''
BILLING_POSTCODE nvarchar(12) Default = ''
DATEADDED datetime Default = getdate() Indicates the date this record was added.
DATECHANGED datetime Default = getdate() Indicates the date this record was last changed.
TS timestamp Timestamp.
TSLONG bigint (Computed) yes CONVERT(bigint, TS) Numeric representation of the timestamp.
CHECKNUMBER nvarchar(20) Default = ''
ISCOMPANYSPONSOR bit Default = 0
AUTHORIZATIONCODE nvarchar(20) Default = '' The authorization code for a credit card payment, if applicable.
TRANSACTIONID uniqueidentifier yes The BBPS transaction ID for a card not present transaction.
REJECTIONMESSAGE nvarchar(250) Default = '' The reason the credit card couldn't be authorized.
SENDNOTIFICATIONS bit Default = 1
VENDORID nvarchar(50) Default = ''

Foreign Keys

Foreign Key Field Type Null Notes Description
BATCHID uniqueidentifier BATCH.ID FK to BATCH
EVENTID uniqueidentifier EVENT.ID FK to EVENT
TITLECODEID uniqueidentifier yes TITLECODE.LOCALID FK to TITLECODE
SUFFIXCODEID uniqueidentifier yes SUFFIXCODE.LOCALID FK to SUFFIXCODE
MARITALSTATUSCODEID uniqueidentifier yes MARITALSTATUSCODE.LOCALID FK to MARITALSTATUSCODE
ADDRESSTYPECODEID uniqueidentifier yes ADDRESSTYPECODE.ID FK to ADDRESSTYPECODE
COUNTRYID uniqueidentifier yes COUNTRY.LOCALID FK to COUNTRY
STATEID uniqueidentifier yes STATE.LOCALID FK to STATE
DONOTMAILREASONCODEID uniqueidentifier yes DONOTMAILREASONCODE.ID FK to DONOTMAILREASONCODE
PHONETYPECODEID uniqueidentifier yes PHONETYPECODE.LOCALID FK to PHONETYPECODE
COMPANY_ADDRESSTYPECODEID uniqueidentifier yes ADDRESSTYPECODE.ID FK to ADDRESSTYPECODE
COMPANY_COUNTRYID uniqueidentifier yes COUNTRY.LOCALID FK to COUNTRY
COMPANY_STATEID uniqueidentifier yes STATE.LOCALID FK to STATE
COMPANY_DONOTMAILREASONCODEID uniqueidentifier yes DONOTMAILREASONCODE.ID FK to DONOTMAILREASONCODE
COMPANY_PHONETYPECODEID uniqueidentifier yes PHONETYPECODE.LOCALID FK to PHONETYPECODE
SPONSORSHIPTYPEID uniqueidentifier yes EVENTSPONSORSHIPTYPE.ID FK to EVENTSPONSORSHIPTYPE
BILLING_ADDRESSTYPECODEID uniqueidentifier yes ADDRESSTYPECODE.ID FK to ADDRESSTYPECODE
BILLING_COUNTRYID uniqueidentifier yes COUNTRY.LOCALID FK to COUNTRY
BILLING_STATEID uniqueidentifier yes STATE.LOCALID FK to STATE
ADDEDBYID uniqueidentifier CHANGEAGENT.ID FK to CHANGEAGENT.
CHANGEDBYID uniqueidentifier CHANGEAGENT.ID FK to CHANGEAGENT.
ORIGINALCLIENTUSERID int yes ClientUsers.ID FK to CLIENTUSERS
ORIGINALCONSTITUENTID uniqueidentifier yes CONSTITUENT.LOCALID FK to CONSTITUENT
PHONEID uniqueidentifier yes PHONE.LOCALID FK to PHONE
ADDRESSID uniqueidentifier yes ADDRESS.LOCALID FK to ADDRESS
EMAILADDRESSID uniqueidentifier yes EMAILADDRESS.ID FK to EMAILADDRESS
ORGANIZATIONCONSTITUENTID uniqueidentifier yes CONSTITUENT.LOCALID FK to CONSTITUENT
CREDITCARDID uniqueidentifier yes CREDITCARD.ID Stores the credit card details.

Indexes

Index Name Fields Unique Primary Clustered
IX_BATCHEVENTSPONSORSHIP_ADDRESSID ADDRESSID
IX_BATCHEVENTSPONSORSHIP_BATCHID BATCHID
IX_BATCHEVENTSPONSORSHIP_DATEADDED DATEADDED yes
IX_BATCHEVENTSPONSORSHIP_DATECHANGED DATECHANGED
IX_BATCHEVENTSPONSORSHIP_ORGANIZATIONCONSTITUENTID ORGANIZATIONCONSTITUENTID
IX_BATCHEVENTSPONSORSHIP_ORIGINALCONSTITUENTID ORIGINALCONSTITUENTID
PK_BATCHEVENTSPONSORSHIP ID yes yes

Triggers

Trigger Name Description
TR_BATCHEVENTSPONSORSHIP_AUDIT_UPDATE
TR_BATCHEVENTSPONSORSHIP_AUDIT_DELETE

Referenced by

Referenced by Field
BATCHEVENTSPONSORSHIPBATCHSYSTEMMESSAGES BATCHEVENTSPONSORSHIPID
BATCHEVENTSPONSORSHIPBATCHUSERMESSAGE ID
BATCHEVENTSPONSORSHIPENEWSLETTER BATCHEVENTSPONSORSHIPID