USP_DATAFORMTEMPLATE_ADD_SPONSORSHIPBATCHINDIVIDUAL

The save procedure used by the add dataform template "Sponsorship Batch Individual Add Form".

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	INOUT	The output parameter indicating the ID of the record added.
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
@CHANGEAGENTID	uniqueidentifier	IN	Input parameter indicating the ID of the change agent invoking the procedure.
@LASTNAME	nvarchar(100)	IN	Last name
@FIRSTNAME	nvarchar(50)	IN	First name
@MIDDLENAME	nvarchar(50)	IN	Middle name
@MAIDENNAME	nvarchar(100)	IN	Maiden name
@NICKNAME	nvarchar(50)	IN	Nickname
@TITLECODEID	uniqueidentifier	IN	Title
@SUFFIXCODEID	uniqueidentifier	IN	Suffix
@GENDERCODE	tinyint	IN	Gender
@BIRTHDATE	UDT_FUZZYDATE	IN	Birth date
@ADDRESS_ADDRESSTYPECODEID	uniqueidentifier	IN	Address type
@ADDRESS_DONOTMAIL	bit	IN	Do not send mail to this address
@ADDRESS_COUNTRYID	uniqueidentifier	IN	Country
@ADDRESS_STATEID	uniqueidentifier	IN	State
@ADDRESS_ADDRESSBLOCK	nvarchar(150)	IN	Address
@ADDRESS_CITY	nvarchar(50)	IN	City
@ADDRESS_POSTCODE	nvarchar(12)	IN	ZIP
@ADDRESS_OMITFROMVALIDATION	bit	IN	Omit from validation
@ADDRESS_CART	nvarchar(10)	IN
@ADDRESS_DPC	nvarchar(8)	IN
@ADDRESS_LOT	nvarchar(5)	IN
@ADDRESS_COUNTYCODEID	uniqueidentifier	IN
@ADDRESS_CONGRESSIONALDISTRICTCODEID	uniqueidentifier	IN
@ADDRESS_LASTVALIDATIONATTEMPTDATE	datetime	IN
@ADDRESS_VALIDATIONMESSAGE	nvarchar(100)	IN
@ADDRESS_CERTIFICATIONDATA	int	IN
@PHONE_PHONETYPECODEID	uniqueidentifier	IN	Phone type
@PHONE_NUMBER	nvarchar(100)	IN	Phone number
@EMAILADDRESS_EMAILADDRESSTYPECODEID	uniqueidentifier	IN	Email type
@EMAILADDRESS_EMAILADDRESS	UDT_EMAILADDRESS	IN	Email address
@MARITALSTATUSCODEID	uniqueidentifier	IN	Marital status
@SPOUSEID	uniqueidentifier	IN	Full name
@EXISTINGSPOUSE	bit	IN	Existing constituent
@SPOUSE_LASTNAME	nvarchar(100)	IN	Last name
@SPOUSE_FIRSTNAME	nvarchar(50)	IN	First name
@SPOUSE_MIDDLENAME	nvarchar(50)	IN	Middle name
@SPOUSE_MAIDENNAME	nvarchar(100)	IN	Maiden name
@SPOUSE_NICKNAME	nvarchar(50)	IN	Nickname
@SPOUSE_TITLECODEID	uniqueidentifier	IN	Title
@SPOUSE_SUFFIXCODEID	uniqueidentifier	IN	Suffix
@SPOUSE_GENDERCODE	tinyint	IN	Gender
@SPOUSE_BIRTHDATE	UDT_FUZZYDATE	IN	Birth date
@SPOUSE_RECIPROCALTYPECODEID	uniqueidentifier	IN	Reciprocal relationship type
@SPOUSE_RELATIONSHIPTYPECODEID	uniqueidentifier	IN	Relationship type
@COPYPRIMARYINFORMATION	bit	IN	Copy primary information
@SPOUSE_STARTDATE	datetime	IN	Start date
@PRIMARYRELATIONSHIPEXISTS	bit	IN	Apply recognition credit to individual for constituent's payments
@PRIMARYMATCHFACTOR	decimal(5, 2)	IN	Primary recognition credit factor
@RECIPROCALRELATIONSHIPEXISTS	bit	IN	Apply recognition credit to constituent for individual's payments
@RECIPROCALMATCHFACTOR	decimal(5, 2)	IN	Reciprocal recognition credit factor
@BUSINESSID	uniqueidentifier	IN	Org. name
@EXISTINGBUSINESS	bit	IN	Existing constituent
@BUSINESS_NAME	nvarchar(100)	IN	Org. name
@BUSINESS_ADDRESSTYPECODEID	uniqueidentifier	IN	Address type
@BUSINESS_COUNTRYID	uniqueidentifier	IN	Country
@BUSINESS_STATEID	uniqueidentifier	IN	State
@BUSINESS_ADDRESSBLOCK	nvarchar(150)	IN	Address
@BUSINESS_CITY	nvarchar(50)	IN	City
@BUSINESS_POSTCODE	nvarchar(12)	IN	ZIP
@BUSINESS_DONOTMAIL	bit	IN	Do not send mail to this address
@BUSINESS_OMITFROMVALIDATION	bit	IN	Omit from validation
@BUSINESS_CART	nvarchar(10)	IN
@BUSINESS_DPC	nvarchar(8)	IN
@BUSINESS_LOT	nvarchar(5)	IN
@BUSINESS_COUNTYCODEID	uniqueidentifier	IN
@BUSINESS_CONGRESSIONALDISTRICTCODEID	uniqueidentifier	IN
@BUSINESS_LASTVALIDATIONATTEMPTDATE	datetime	IN
@BUSINESS_VALIDATIONMESSAGE	nvarchar(100)	IN
@BUSINESS_CERTIFICATIONDATA	int	IN
@BUSINESS_PHONETYPECODEID	uniqueidentifier	IN	Phone type
@BUSINESS_NUMBER	nvarchar(100)	IN	Phone number
@BUSINESS_RECIPROCALTYPECODEID	uniqueidentifier	IN	Reciprocal relationship type
@BUSINESS_RELATIONSHIPTYPECODEID	uniqueidentifier	IN	Relationship type
@BUSINESS_STARTDATE	datetime	IN	Start date
@ISCONTACT	bit	IN
@ISPRIMARYCONTACT	bit	IN	Primary contact
@CONTACTTYPECODEID	uniqueidentifier	IN	Contact type
@POSITION	nvarchar(50)	IN	Position
@ISMATCHINGGIFTRELATIONSHIP	bit	IN	This organization will match individual's contributions
@ISSPOUSERELATIONSHIP	bit	IN	Is spouse relationship
@HOUSEHOLDCOPYPRIMARYCONTACTINFO	bit	IN	Copy primary contact information to household
@ADDRESS_DONOTMAILREASONCODEID	uniqueidentifier	IN	Reason
@BUSINESS_DONOTMAILREASONCODEID	uniqueidentifier	IN	Reason
@BUSINESS_PRIMARYRELATIONSHIPEXISTS	bit	IN
@BUSINESS_PRIMARYMATCHFACTOR	decimal(5, 2)	IN
@BUSINESS_RECIPROCALRELATIONSHIPEXISTS	bit	IN
@BUSINESS_RECIPROCALMATCHFACTOR	decimal(5, 2)	IN
@JOBCATEGORYCODEID	uniqueidentifier	IN
@CAREERLEVELCODEID	uniqueidentifier	IN
@BUSINESS_PRIMARYRECOGNITIONTYPECODEID	uniqueidentifier	IN
@BUSINESS_RECIPROCALRECOGNITIONTYPECODEID	uniqueidentifier	IN
@ADDRESS_INFOSOURCECODEID	uniqueidentifier	IN

Definition

Copy



                    CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_SPONSORSHIPBATCHINDIVIDUAL
                    (
                        @ID uniqueidentifier = null output,
                        @CURRENTAPPUSERID uniqueidentifier,
                        @CHANGEAGENTID uniqueidentifier = null,
                        @LASTNAME nvarchar(100),
                        @FIRSTNAME nvarchar(50) = '',
                        @MIDDLENAME nvarchar(50) = '',
                        @MAIDENNAME nvarchar(100) = '',
                        @NICKNAME nvarchar(50) = '',
                        @TITLECODEID uniqueidentifier = null,
                        @SUFFIXCODEID uniqueidentifier = null,
                        @GENDERCODE tinyint = 0,
                        @BIRTHDATE dbo.UDT_FUZZYDATE = '00000000',
                        @ADDRESS_ADDRESSTYPECODEID uniqueidentifier = null,
                        @ADDRESS_DONOTMAIL bit = 0,
                        @ADDRESS_COUNTRYID uniqueidentifier = null,
                        @ADDRESS_STATEID uniqueidentifier = null,
                        @ADDRESS_ADDRESSBLOCK nvarchar(150) = '',
                        @ADDRESS_CITY nvarchar(50) = '',
                        @ADDRESS_POSTCODE nvarchar(12) = '',
                        -- Address Validation

                        @ADDRESS_OMITFROMVALIDATION bit = 0,
                        @ADDRESS_CART nvarchar(10) = '',
                        @ADDRESS_DPC nvarchar(8) = '',
                        @ADDRESS_LOT nvarchar(5) = '',
                        @ADDRESS_COUNTYCODEID uniqueidentifier = null,
                        @ADDRESS_CONGRESSIONALDISTRICTCODEID uniqueidentifier = null,
                        @ADDRESS_LASTVALIDATIONATTEMPTDATE datetime = null,
                        @ADDRESS_VALIDATIONMESSAGE nvarchar(100) = '',
                        @ADDRESS_CERTIFICATIONDATA integer = 0,

                        @PHONE_PHONETYPECODEID uniqueidentifier = null,
                        @PHONE_NUMBER nvarchar(100) = '',
                        @EMAILADDRESS_EMAILADDRESSTYPECODEID uniqueidentifier = null,
                        @EMAILADDRESS_EMAILADDRESS dbo.UDT_EMAILADDRESS = '',                        
                        @MARITALSTATUSCODEID uniqueidentifier = null,                        

                        --Individual's relationship Variables

                        @SPOUSEID uniqueidentifier = null,
                        @EXISTINGSPOUSE bit = 0,
                        @SPOUSE_LASTNAME nvarchar(100) = '',
                        @SPOUSE_FIRSTNAME nvarchar(50) = '',
                        @SPOUSE_MIDDLENAME nvarchar(50) = '',
                        @SPOUSE_MAIDENNAME nvarchar(100) = '',
                        @SPOUSE_NICKNAME nvarchar(50) = '',
                        @SPOUSE_TITLECODEID uniqueidentifier = null,
                        @SPOUSE_SUFFIXCODEID uniqueidentifier = null,
                        @SPOUSE_GENDERCODE tinyint = 0,
                        @SPOUSE_BIRTHDATE dbo.UDT_FUZZYDATE = '00000000',
                        @SPOUSE_RECIPROCALTYPECODEID uniqueidentifier = null,
                        @SPOUSE_RELATIONSHIPTYPECODEID uniqueidentifier = null,
                        @COPYPRIMARYINFORMATION bit = 1,
                        @SPOUSE_STARTDATE datetime = null,
                        @PRIMARYRELATIONSHIPEXISTS bit = 0,
                        @PRIMARYMATCHFACTOR decimal(5,2) = 100,
                        @RECIPROCALRELATIONSHIPEXISTS bit = 0,
                        @RECIPROCALMATCHFACTOR decimal(5,2) = 100,

                        --Organization variables

                        @BUSINESSID uniqueidentifier = null,
                        @EXISTINGBUSINESS bit = 0,
                        @BUSINESS_NAME nvarchar(100) = '',
                        @BUSINESS_ADDRESSTYPECODEID uniqueidentifier = null,
                        @BUSINESS_COUNTRYID uniqueidentifier = null,
                        @BUSINESS_STATEID uniqueidentifier = null,
                        @BUSINESS_ADDRESSBLOCK nvarchar(150) = '',
                        @BUSINESS_CITY nvarchar(50) = '',
                        @BUSINESS_POSTCODE nvarchar(12) = '',
                        @BUSINESS_DONOTMAIL bit = 0,

                        -- Address Validation

                        @BUSINESS_OMITFROMVALIDATION bit = 0,
                        @BUSINESS_CART nvarchar(10) = '',
                        @BUSINESS_DPC nvarchar(8) = '',
                        @BUSINESS_LOT nvarchar(5) = '',
                        @BUSINESS_COUNTYCODEID uniqueidentifier = null,
                        @BUSINESS_CONGRESSIONALDISTRICTCODEID uniqueidentifier = null,
                        @BUSINESS_LASTVALIDATIONATTEMPTDATE datetime = null,
                        @BUSINESS_VALIDATIONMESSAGE nvarchar(100) = '',
                        @BUSINESS_CERTIFICATIONDATA integer = 0,

                        @BUSINESS_PHONETYPECODEID uniqueidentifier = null,
                        @BUSINESS_NUMBER nvarchar(100) = '',                    
                        @BUSINESS_RECIPROCALTYPECODEID uniqueidentifier = null,
                        @BUSINESS_RELATIONSHIPTYPECODEID uniqueidentifier = null,
                        @BUSINESS_STARTDATE datetime = null,                        
                        @ISCONTACT bit = 0,
                        @ISPRIMARYCONTACT bit = 0,
                        @CONTACTTYPECODEID uniqueidentifier = null,
                        @POSITION nvarchar(50) = '',    
                        @ISMATCHINGGIFTRELATIONSHIP bit = 0,

                        --Individual's relationship variable

                        @ISSPOUSERELATIONSHIP bit = 1,

                        -- Household variables

                        @HOUSEHOLDCOPYPRIMARYCONTACTINFO bit = 0,
                        @ADDRESS_DONOTMAILREASONCODEID uniqueidentifier = null,
                        @BUSINESS_DONOTMAILREASONCODEID uniqueidentifier = null,

                        --Additional fields to make spec compatible with RevenueBatchIndividualAddFormUIModel

                       @BUSINESS_PRIMARYRELATIONSHIPEXISTS bit = 0,
                        @BUSINESS_PRIMARYMATCHFACTOR decimal(5,2) = 100,
                        @BUSINESS_RECIPROCALRELATIONSHIPEXISTS bit = 0,
                        @BUSINESS_RECIPROCALMATCHFACTOR decimal(5,2) = 100,
                        @JOBCATEGORYCODEID uniqueidentifier = null,
                        @CAREERLEVELCODEID uniqueidentifier = null,
                        @BUSINESS_PRIMARYRECOGNITIONTYPECODEID uniqueidentifier = null,
                        @BUSINESS_RECIPROCALRECOGNITIONTYPECODEID uniqueidentifier = null,
            @ADDRESS_INFOSOURCECODEID uniqueidentifier = null            
                    ) as begin

                        set nocount on;

                        declare @CURRENTDATE                datetime;

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

            if @ADDRESS_DONOTMAIL = 0 
              set @ADDRESS_DONOTMAILREASONCODEID = null

            if @BUSINESS_DONOTMAIL = 0 
              set @BUSINESS_DONOTMAILREASONCODEID = null

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

                        set @CURRENTDATE = getdate();

                        begin try
                            --factored out so that import can make use of same routine in USP_DATAFORMTEMPLATE_ADD_SPONSORSHIPBATCHROW

                            exec dbo.USP_BATCHSPONSORSHIPCONSTITUENT_ADD @ID output,@CURRENTAPPUSERID,@CHANGEAGENTID,0,@LASTNAME,@FIRSTNAME,@MIDDLENAME,@MAIDENNAME,@NICKNAME,@TITLECODEID,@SUFFIXCODEID,@GENDERCODE,@BIRTHDATE,
                                @ADDRESS_ADDRESSTYPECODEID,@ADDRESS_DONOTMAIL,@ADDRESS_COUNTRYID,@ADDRESS_STATEID,@ADDRESS_ADDRESSBLOCK,@ADDRESS_CITY,@ADDRESS_POSTCODE,
                                @ADDRESS_OMITFROMVALIDATION,@ADDRESS_CART,@ADDRESS_DPC,@ADDRESS_LOT,@ADDRESS_COUNTYCODEID,@ADDRESS_CONGRESSIONALDISTRICTCODEID,@ADDRESS_LASTVALIDATIONATTEMPTDATE,@ADDRESS_VALIDATIONMESSAGE,@ADDRESS_CERTIFICATIONDATA,
                                @PHONE_PHONETYPECODEID,@PHONE_NUMBER,@EMAILADDRESS_EMAILADDRESSTYPECODEID,@EMAILADDRESS_EMAILADDRESS,
                                @MARITALSTATUSCODEID,'', null, 0,0,null,@SPOUSEID,@SPOUSE_LASTNAME,@SPOUSE_FIRSTNAME,@SPOUSE_MIDDLENAME,@SPOUSE_MAIDENNAME,@SPOUSE_NICKNAME,@SPOUSE_TITLECODEID,@SPOUSE_SUFFIXCODEID,@SPOUSE_GENDERCODE,@SPOUSE_BIRTHDATE,
                                @SPOUSE_RECIPROCALTYPECODEID,@SPOUSE_RELATIONSHIPTYPECODEID,@COPYPRIMARYINFORMATION,@SPOUSE_STARTDATE,@PRIMARYRELATIONSHIPEXISTS,@PRIMARYMATCHFACTOR,@RECIPROCALRELATIONSHIPEXISTS,
                                @RECIPROCALMATCHFACTOR,@BUSINESSID,@BUSINESS_NAME,@BUSINESS_ADDRESSTYPECODEID,@BUSINESS_COUNTRYID,@BUSINESS_STATEID,@BUSINESS_ADDRESSBLOCK,@BUSINESS_CITY,
                                @BUSINESS_POSTCODE,@BUSINESS_DONOTMAIL, @BUSINESS_OMITFROMVALIDATION,@BUSINESS_CART,@BUSINESS_DPC,@BUSINESS_LOT,@BUSINESS_COUNTYCODEID,@BUSINESS_CONGRESSIONALDISTRICTCODEID,@BUSINESS_LASTVALIDATIONATTEMPTDATE,@BUSINESS_VALIDATIONMESSAGE,@BUSINESS_CERTIFICATIONDATA,
                                @BUSINESS_PHONETYPECODEID,@BUSINESS_NUMBER,@BUSINESS_RECIPROCALTYPECODEID,@BUSINESS_RELATIONSHIPTYPECODEID,@BUSINESS_STARTDATE,
                                @ISCONTACT,@ISPRIMARYCONTACT,@CONTACTTYPECODEID,@POSITION,@ISMATCHINGGIFTRELATIONSHIP,@HOUSEHOLDCOPYPRIMARYCONTACTINFO,@ISSPOUSERELATIONSHIP,@ADDRESS_DONOTMAILREASONCODEID,@BUSINESS_DONOTMAILREASONCODEID,
                                @JOBCATEGORYCODEID,@CAREERLEVELCODEID,@BUSINESS_PRIMARYRECOGNITIONTYPECODEID,@BUSINESS_RECIPROCALRECOGNITIONTYPECODEID,@BUSINESS_PRIMARYRELATIONSHIPEXISTS,@BUSINESS_RECIPROCALRELATIONSHIPEXISTS,@BUSINESS_PRIMARYMATCHFACTOR,@BUSINESS_RECIPROCALMATCHFACTOR


                        end try                        

                        begin catch
                            exec dbo.USP_RAISE_ERROR;
                            return 1;
                        end catch

                        return 0;

                    end;